:>/dev/null

ラガードエンジニアの不撓不屈の精神/unlearning/go beyond

XtraBackup(innobackupex)を使用したMySQLバックアップ(no-lock)

mysqldumpを使用したレプリケーション構築を作成するとします。このとき、mysqldumpに--master-dataを付与すると"FLUSH TABLES WITH READ LOCK" が発行され、一瞬のあいだ、全テーブルがロックが発生します。

mysqldump -uroot -pxxxxxxxx --single-transaction --master-data=2 --all-databases | gzip >  mysqldump.sql.gz  

2095627 Query FLUSH TABLES WITH READ LOCK
2095627 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2095627 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2095627 Query SHOW MASTER STATUS
2095627 Query UNLOCK TABLES

上記のような状況を回避して、MySQLマスターをロックせずレプリケーションを構築するためにXtraBackupを利用します。
通常、innobackupexを用いたバックアップを実行した場合、ロックを発行するのですが、 --no-lockで回避可能です。
--no-lock オプションを付与すると、binlogのポジションが記述されるはずの xtrabackup_binlog_info ファイルが作成されません。
しかし、--apply-logコマンドでリカバリ用のファイルを作成すると、ログにbinlogのポジションが出力されます。これを利用してレプリケーションを構築できます。

バックアップ

(1)ツールインストール

# rpm -Uhv http://www.percona.com/redir/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
# wget https://www.percona.com/redir/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm
# rpm -ivh percona-release-0.1-6.noarch.rpm
# yum install xtrabackup

(2)バックアップフォルダ作成

# mkdir -p /tmp/xtrabackup/

(3)既存データバックアップ

# innobackupex --ibbackup=xtrabackup --slave-info --safe-slave-backup --user root --password "" /tmp/xtrabackup/ --no-lock
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

innobackupex: Using mysql  Ver 14.14 Distrib 5.1.60, for unknown-linux-gnu (x86_64) using readline 5.1
innobackupex: Using mysql server version Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

.... snip ...

xtrabackup: Creating suspend file '/tmp/xtrabackup/2019-07-30_18-58-30/xtrabackup_suspended' with pid '3023'
xtrabackup: Transaction log of lsn (49192) to (49192) was copied.
innobackupex:: Starting slave SQL thread
^@190730 18:58:47  innobackupex: Connection to database server closed

innobackupex: Backup created in directory '/tmp/xtrabackup/2019-07-30_18-58-30'
innobackupex: MySQL slave binlog position: master host '10.131.10.99', filename 'master-bin.000022', position 2089
190730 18:58:47  innobackupex: completed OK!

下記エラーが発生した場合

xtrabackup: Error: Please set parameter 'datadir'
innobackupex: Error: ibbackup child process has died at /usr/bin/innobackupex line 386.

xtrabackupはmy.cnfを参照する仕様の為、「datadir」を記載する必要がある。

[mysqld]
datadir=/var/lib/mysql/

(3)データ転送

# tar zcvf xtrabackup.tar.gz xtrabackup/2019-07-30_18-58-30/
# su - test
$ scp -i ~/.ssh/private.pem /tmp/xtrabackup.tar.gz xxx.xxx.xxx.xxx:/tmp/

リストア

(1)MySQL停止/既存データバックアップ

# /etc/init.d/mysqld stop
# mv /var/lib/mysql /var/lib/mysql.old
# mkdir /var/lib/mysql

(2) バックアップしたファイルへログの適用

# tar zxvf xtrabackup.tar.gz
# /usr/bin/innobackupex --user root --password "" --apply-log /tmp/xtrabackup/2019-07-30_18-58-30/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".

.... snip ...

[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 1035, file name ./master-bin.000018

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
190730 19:08:45  InnoDB: Starting shutdown...
190730 19:08:45  InnoDB: Shutdown completed; log sequence number 50700
190730 19:08:45  innobackupex: completed OK!

(3)データファイルのリストア

#  /usr/bin/innobackupex --copy-back /tmp/xtrabackup/2019-07-30_18-58-30/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the copy-back run completes successfully.
           At the end of a successful copy-back run innobackupex
           prints "completed OK!".

.... snip ...

innobackupex: Starting to copy InnoDB log files
innobackupex: in '/tmp/xtrabackup/2019-07-30_18-58-30'
innobackupex: back to original InnoDB log directory '/var/lib/mysql/'
innobackupex: Copying '/tmp/xtrabackup/2019-07-30_18-58-30/ib_logfile1' to '/var/lib/mysql/'
innobackupex: Copying '/tmp/xtrabackup/2019-07-30_18-58-30/ib_logfile0' to '/var/lib/mysql/'
innobackupex: Finished copying back files.

190730 19:10:29  innobackupex: completed OK!

(4)MySQL起動

# chown -R mysql:mysql /var/lib/mysql
# /etc/init.d/mysql start

(5)レプリケーションの復旧
スレーブでのバックアップ--slave-infoオプション指定してバックアップを取得するとxtrabackup_slave_infoファイルが作成されます。
バックアップ時点のmaster_log_fileとmaster_log_positionが記述されたファイルが記録されています。
これを元にしてCHANGE MASTER構文を実行することでスレーブが復旧します。

# cat /var/lib/mysql/xtrabackup_slave_info
CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000022', MASTER_LOG_POS=2089

# mysql -uroot
mysql> change master to master_host='xxx.xxx.xxx.xxx', master_user='repl', master_password='******', master_log_file='master-bin.000022', master_log_pos=2089;

mysql> start slave;
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: xxx.xxx.xxx.xxx
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000022
          Read_Master_Log_Pos: 2089
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 252
        Relay_Master_Log_File: master-bin.000022
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:

「Slave_IO_Running」と「Slave_SQL_Running」がともに「Yes」となっていれば、レプリケーション設定完了。

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

ref)
https://www.submit.ne.jp/428
https://takeshiyako.blogspot.com/2015/03/mysql-innobackupex-no-lock.html
http://buta9999.hatenablog.jp/entry/20131120/1384972041
https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0059
https://qiita.com/isobecky74/items/eb38447e02f05ba4386b