MySQL自動バックアップリストアツールMySQL-AutoXtraBackup

9390 ワード
MYSQL バックアップリストア 常用ツール
MySQL-AutoXtraBackupはPerconaラボのオープンソースツールで、xtrabackupとautoxtrabackupバックアップに基づくバックアップツールです.AutoXtraBackupに詳しくない場合は、AutoXtraBackupもパラメータの構成と理解が必要なので、autoxtrabackupを使用するほうが便利だと思います.
 
MySQL-AutoXtraBackup 1.5インストール要件:
Percona Xtrabackup(>=2.3.5)Python 3(tested version 3.5.3 on CentOS 7)mysql-utilities(>=1.5.4)がサポートするデータベース:MySQL、Percona Server、MariaDBがサポートするオペレーティングシステム:Debian、Ubuntu、CentOS、RedHat
AutoXtraBackupのダウンロード:https://github.com/Percona-Lab/MySQL-AutoXtraBackupAutoXtraBackupドキュメント:https://mysql-autoxtrabackup.readthedocs.io/en/latest/index.htmlPerconaの公式例:https://www.percona.com/blog/2017/11/27/perconalab-autoxtrabackup-v1-5-0-release/
 
関連ツールのインストール:
# Python 3.5   
wget https://www.python.org/ftp/python/3.5.6/Python-3.5.6.tar.xz 
tar xvf Python-3.5.6.tar.xz -C /tmp/
cd /tmp/Python-3.5.6
./configure --prefix=/usr/local/python3
make && make install
ln -s /usr/local/python3/bin/python3 /usr/bin/python3
ln -s /usr/local/python3/bin/pip3 /usr/bin/pip3
/usr/local/python3/bin/pip3 install --upgrade pip
/usr/local/python3/bin/pip3 list


#      
yum install -y openssl openssl-devel zlib zlib-devel pigz


# mysql-connector-python   
wget https://dev.mysql.com/get/Downloads/Connector-Python/mysql-connector-python-2.1.5-1.el7.x86_64.rpm
yum install -y mysql-connector-python-2.1.5-1.el7.x86_64.rpm


# mysql-utilities   
wget https://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-utilities-1.6.5-1.el7.noarch.rpm
yum install -y mysql-utilities-1.6.5-1.el7.noarch.rpm


# percona-xtrabackup 2.4   
yum install http://www.percona.com/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm
yum list | grep percona
yum install -y percona-xtrabackup-24
xtrabackup --version


# MySQL-AutoXtraBackup   
pip3 install mysql-autoxtrabackup
ln -s /usr/local/python3/bin/autoxtrabackup /usr/bin/autoxtrabackup
autoxtrabackup --help

MySQLプロファイルに設定する変数:
# vim /etc/my.cnf
[mysqld]
innodb_file_per_table=1
binlog_format = row
log_bin=/usr/local/mysql/binlog/mysql-bin

Autoxtrabackupプロファイルの作成:
# vim /etc/bck.cnf
[MySQL]
#mysql           (      )
mysql_host=localhost
mysql_port=3306
mysql_user=root
mysql_password=mysql
mysql_socket=/tmp/mysql.sock
mycnf=/etc/my.cnf
datadir=/usr/local/mysql/data
mysql=/usr/local/mysql/bin/mysql
mysqladmin=/usr/local/mysql/bin/mysqladmin

[Backup]
#    
backupdir=/home/mysql/backup_dir
# data         
tmpdir=/home/mysql/mysql_datadirs
#        Percona xtrabackup          
backup_tool=/usr/bin/xtrabackup
#  （  ）    Percona xtrabackup          
#prepare_tool=/usr/bin/xtrabackup
#pid    
pid_dir=/tmp
#pid      
pid_runtime_warning=2 Hours
#xtrabackup   ，        
xtra_prepare=--apply-log-only
#backup    
#xtra_backup=--compact
#prepare    
#xtra_prepare_options=--rebuild-indexes
#backup prepare       (--binlog-info=ON --galera-info)
xtra_options=--no-version-check
#???
#prepare_archive=1
#          tar.gz    
#move_archive=0
#        
#full_backup_interval=1 day
#             
#max_archive_size=100GiB
#               
#max_archive_duration=4 Days
#      ,          
#partial_list=test.t1 test.t2 dbtest
#    
#archive_dir=/home/mysql/backup_archives

[Compress]
#    (     )
#compress=quicklz
#compress_chunk_size=65536
#compress_threads=4
#decompress=TRUE
#        .qp  .(Not available yet, will be released with XB 2.3.7 and 2.4.6)
#remove_original=FALSE

[Encrypt]
#      (     )
#xbcrypt=/usr/bin/xbcrypt
#encrypt=AES256
#   --encrypt-key   --encrypt-key-file     
#encrypt_key='VVTBwgM4UhwkTTV98fhuj+D1zyWoA89K'
#encrypt_key_file=/path/to/file/with_encrypt_key
#encrypt_threads=4
#encrypt_chunk_size=65536
#decrypt=AES256
#        .qp  .(Not available yet, will be released with XB 2.3.7 and 2.4.6)
#remove_original=FALSE

[Xbstream]
#  stream  (     )
#xbstream=/usr/bin/xbstream
#stream=xbstream
#stream=tar
#xbstream_options=-x --parallel=100
#xbs_decrypt=1
#remote_stream=ssh xxx.xxx.xxx.xxx

#[Remote]
#    (     )
#[email protected]
#remote_dir=/home/sh/Documents

[Commands]
#       MySQL        MySQL  & 
start_mysql_command=service mysqld start
stop_mysql_command=service mysqld stop
chown_command=chown -R mysql:mysql

まずバグを修正します.
#       bug ，         （        ）     ：
Traceback (most recent call last):
  File "/usr/bin/autoxtrabackup", line 11, in 
    load_entry_point('mysql-autoxtrabackup==1.5.3', 'console_scripts', 'autoxtrabackup')()
  File "/usr/local/python3/lib/python3.5/site-packages/click/core.py", line 764, in __call__
    return self.main(*args, **kwargs)
  File "/usr/local/python3/lib/python3.5/site-packages/click/core.py", line 717, in main
    rv = self.invoke(ctx)
  File "/usr/local/python3/lib/python3.5/site-packages/click/core.py", line 956, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/python3/lib/python3.5/site-packages/click/core.py", line 555, in invoke
    return callback(*args, **kwargs)
  File "/usr/local/python3/lib/python3.5/site-packages/click/decorators.py", line 17, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/usr/local/python3/lib/python3.5/site-packages/mysql_autoxtrabackup-1.5.3-py3.6.egg/autoxtrabackup.py", line 263, in all_procedure
    b.all_backup()
  File "/usr/local/python3/lib/python3.5/site-packages/master_backup_script/backuper.py", line 917, in all_backup
    self.inc_backup()
  File "/usr/local/python3/lib/python3.5/site-packages/master_backup_script/backuper.py", line 815, in inc_backup
    logger.debug("The following backup command will be executed {}".format(filteredargs))
UnboundLocalError: local variable 'filteredargs' referenced before assignment
2018-12-14 13:45:40 DEBUG     closing pidfile: /opt/MySQL-AutoXtraBackup-1.5.3/autoxtrabackup.pid


  ：
# vim /usr/local/python3/lib/python3.5/site-packages/master_backup_script/backuper.py

  813  ，         
812             # filter out password from argument list
813                 filteredargs = re.sub("--password='?\w+'?", "--password='*'", args)
814 
815             logger.debug("The following backup command will be executed {}".format(filteredargs))

  ：

812             # filter out password from argument list
813             filteredargs = re.sub("--password='?\w+'?", "--password='*'", args)
814 
815             logger.debug("The following backup command will be executed {}".format(filteredargs))

バックアップ:
#             (           full_backup_interval)
autoxtrabackup --tag="Full backup" --defaults_file=/etc/bck.cnf -v -l DEBUG -lf /home/mysql/autoxtrabackup.log --backup

autoxtrabackup --tag="First incremental backup" --defaults_file=/etc/bck.cnf -v -l DEBUG -lf /home/mysql/autoxtrabackup.log --backup

autoxtrabackup --tag="Second incremental backup" --defaults_file=/etc/bck.cnf -v -l DEBUG -lf /home/mysql/autoxtrabackup.log --backup

autoxtrabackup --tag="Third incremental backup" --defaults_file=/etc/bck.cnf -v -l DEBUG -lf /home/mysql/autoxtrabackup.log --backup

バックアップ情報の表示(2つの方法):
# cat /home/mysql/backup_dir/backup_tags.txt 
2018-12-27_21-46-13	FullOK	2018-12-27_21-46-25	237M	'Full backup'
2018-12-27_22-08-42	Inc	OK	2018-12-27_22-08-55	12M		'First incremental backup'
2018-12-28_10-42-32	Inc	OK	2018-12-28_10-42-44	14M		'Second incremental backup'
2018-12-28_10-43-59	Inc	OK	2018-12-28_10-44-10	8.6M	'Third incremental backup'

# autoxtrabackup --show_tags --defaults_file=/etc/bck.cnf
Backup             	Type	Status	Completion_time    	Size	TAG
----------------------------------------------------------------------------------
2018-12-27_21-46-13	FullOK	2018-12-27_21-46-25	237M	'Full backup'
2018-12-27_22-08-42	Inc	OK	2018-12-27_22-08-55	12M		'First incremental backup'
2018-12-28_10-42-32	Inc	OK	2018-12-28_10-42-44	14M		'Second incremental backup'
2018-12-28_10-43-59	Inc	OK	2018-12-28_10-44-10	8.6M	'Third incremental backup'

復元:
#            ：
# autoxtrabackup -v -lf /home/mysql/autoxtrabackup.log -l DEBUG --defaults_file=/etc/bck.cnf --prepare

Preparing full/inc backups!
What do you want to do?
1. Prepare Backups and keep for future usage. NOTE('Once Prepared Backups Can not be prepared Again')
2. Prepare Backups and restore/recover/copy-back immediately
3. Just copy-back previously prepared backups
Please Choose one of options and type 1 or 2 or 3:(    )

(2を選択し、最近のバックアップに復元)
 
データベースが複数回リストアされたため、ログのシリアル番号が一致せず、リストアエラーが発生しました.
InnoDB: Page [page id: space=0, page number=3]  log sequence number 3034169474 is in the future! Current system log sequence number 3031234808. InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files.  Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
解決:(まだ試したことがない)https://www.percona.com/blog/2013/09/11/how-to-move-the-innodb-log-sequence-number-lsn-forward/
[c言語]PATプログラミング問題intとlongの違いを引き出す
if文について理解しよう！