前言
Percona XtraBackup可以說是一個相對完美的免費開源數(shù)據(jù)備份工具悦析，支持在線無鎖表同步復(fù)制和可并行高效率的安全備份恢復(fù)機制相比mysqldump來說確實讓人眼前一亮瓣戚，與MySQL Enterprise Backup(InnoDB Hot Backup)的功能對比可以參考擴展閱讀。當然我們在實際運維過程中都應(yīng)針對不同的業(yè)務(wù)需求分析和選擇合適的備份恢復(fù)方案畜埋，這篇文章就是針對MySQL多實例且一個實例對應(yīng)多個database的情況隅津，實現(xiàn)MySQL在線不停機不鎖表的主從同步辩撑，日后再繼續(xù)更新分享基于XtraBackup的其它實用技能蛹屿。
XtraBackup是目前首選的備份方案之一
更新歷史
2015年08月07日 - 初稿
閱讀原文 - http://wsgzao.github.io/post/xtrabackup/
擴展閱讀
Percona XtraBackup - https://www.percona.com/software/mysql-database/percona-xtrabackup
MySQL 5.6 Reference Manual :: 17 Replication - http://dev.mysql.com/doc/refman/5.6/en/replication.html
基于Xtrabackup的物理備份解決方案預(yù)研 - http://tencentdba.com/blog/pre-research-on-physical-backup-using-xtrabakcup/
xtrabackup 詳解 - http://www.cnblogs.com/gomysql/p/3650645.html
LTMP索引 - http://wsgzao.github.io/index/#LTMP
原理
MySQL主從同步原理
MySQL主從同步是在MySQL主從復(fù)制(Master-Slave Replication)基礎(chǔ)上實現(xiàn)的，通過設(shè)置在Master MySQL上的binlog(使其處于打開狀態(tài))放钦，Slave MySQL上通過一個I/O線程從Master MySQL上讀取binlog色徘，然后傳輸?shù)絊lave MySQL的中繼日志中，然后Slave MySQL的SQL線程從中繼日志中讀取中繼日志操禀，然后應(yīng)用到Slave MySQL的數(shù)據(jù)庫中褂策。這樣實現(xiàn)了主從數(shù)據(jù)同步功能。
XtraBackup備份原理
innobackupex在后臺線程不斷追蹤InnoDB的日志文件颓屑，然后復(fù)制InnoDB的數(shù)據(jù)文件斤寂。數(shù)據(jù)文件復(fù)制完成之后，日志的復(fù)制線程也會結(jié)束揪惦。這樣就得到了不在同一時間點的數(shù)據(jù)副本和開始備份以后的事務(wù)日志遍搞。完成上面的步驟之后，就可以使用InnoDB崩潰恢復(fù)代碼執(zhí)行事務(wù)日志（redo log）器腋，以達到數(shù)據(jù)的一致性溪猿。
備份分為兩個過程：
- backup，備份階段蒂培，追蹤事務(wù)日志和復(fù)制數(shù)據(jù)文件（物理備份）再愈。
- preparing，重放事務(wù)日志护戳，使所有的數(shù)據(jù)處于同一個時間點，達到一致性狀態(tài)垂睬。
XtraBackup的優(yōu)點
- 可以快速可靠的完成數(shù)據(jù)備份（復(fù)制數(shù)據(jù)文件和追蹤事務(wù)日志）
- 數(shù)據(jù)備份過程中不會中斷事務(wù)的處理（熱備份）
- 節(jié)約磁盤空間和網(wǎng)絡(luò)帶寬
- 自動完成備份鑒定
- 因更快的恢復(fù)時間而提高在線時間
配置
準備工作
MySQL步驟和my.cnf配置參考LTMP - http://wsgzao.github.io/post/ltmp/
#原有主數(shù)據(jù)庫版本
mysql -V
mysql Ver 14.14 Distrib 5.5.31, for Linux (x86_64) using readline 5.1
#遷移從數(shù)據(jù)庫版本
mysql -V
mysql Ver 14.14 Distrib 5.6.25, for linux-glibc2.5 (x86_64) using EditLine wrapper
#檢查數(shù)據(jù)庫引擎
show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
#主從數(shù)據(jù)庫同步注意點
[mysqld]
#主從之間的id不能相同
server-id
#啟用二進制日志
log-bin
#一般在從庫開啟（可選）
read_only
#推薦使用InnoDB并做好相關(guān)配置
#檢查主從數(shù)據(jù)庫狀態(tài)
mysql -S /tmp/mysql.sock -e "show global variables like 'server_id';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
mysql -S /tmp/mysql.sock -e "show global variables like 'log_bin';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
安裝percona-xtrabackup
一般推薦rpm安裝 - https://www.percona.com/downloads/XtraBackup/LATEST/
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
#rpm -ivh percona-xtrabackup-2.2.12-1.el6.x86_64.rpm
rpm -Uvh percona-xtrabackup-2.2.12-1.el6.x86_64.rpm
備份和恢復(fù)
通常一般都直接使用innobackupex媳荒，因為它能同時備份InnoDB和MyISAM引擎的表
重點關(guān)注Slave_IO_Running和Slave_SQL_Runningd的狀態(tài)是否為YES
#備份
innobackupex --socket=/usr/local/var/mysql2/mysql2.sock --user=root --password --defaults-file=/etc/mysqld_multi.cnf --parallel=4 --database=passport /tmp/backup
#保持事務(wù)一致性
innobackupex --socket=/usr/local/var/mysql2/mysql2.sock --user=root --password --defaults-file=/etc/mysqld_multi.cnf --database=passport --apply-log /tmp/backup/2015-08-05_16-08-14
#傳輸
scp -r /tmp/backup/2015-08-05_16-08-14 10.10.16.24:/tmp/backup/
#恢復(fù)
innobackupex --socket=/tmp/mysql.sock --user=root --password --defaults-file=/app/local/mysql/my.cnf --copy-back /tmp/backup/2015-08-05_16-08-14/
#還原權(quán)限
chown -R mysql:mysql /app/data/mysql/data
service mysqld start
/app/local/mysql/scripts/mysql_install_db --basedir=/app/local/mysql --datadir=/app/data/mysql/data --no-defaults --skip-name-resolve --user=mysql
#主庫授權(quán)同步帳號
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
GRANT REPLICATION SLAVE ON *.* TO 'slave_passport'@'10.10.16.24' IDENTIFIED BY 'slave_passport';
FLUSH PRIVILEGES;
#從庫開啟同步
cat /tmp/backup/2015-08-05_16-08-14/xtrabackup_binlog_info
mysql-bin.002599 804497686
CHANGE MASTER TO
MASTER_HOST='10.10.16.51',
MASTER_USER='slave_passport',
MASTER_PASSWORD='slave_passport',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.002599',
MASTER_LOG_POS=804497686;
#開啟主從同步
start slave;
#查看從庫狀態(tài)
show slave status\ G
#從庫的檢查參數(shù)
Slave_IO_Running=Yes
Slave_SQL_Running=Yes
#主庫的檢查參數(shù)
show master status \G
+------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.002600 | 454769337 | | |
+------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)
show processlist;
Master has sent all binlog to slave; waiting for binlog to be updated
MySQL主從切換
切換前斷開主庫訪問連接觀察進程狀態(tài)抗悍，無寫操作后再停止從庫IO_THREAD進行切換
#查看主庫狀態(tài)
show processlist;
Master has sent all binlog to slave; waiting for binlog to be updated
show master status \G
#從庫停止 IO_THREAD 線程
stop slave IO_THREAD;
show processlist;
Slave has read all relay log; waiting for the slave I/O thread to update it
show slave status \G
#從庫切換為主庫
stop slave;
reset master;
reset slave all;
show master status \G
#激活帳戶
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
GRANT REPLICATION SLAVE ON *.* TO 'slave_passport'@'10.10.16.51' IDENTIFIED BY 'slave_passport';
FLUSH PRIVILEGES;
#切換原有主庫為從庫
reset master;
reset slave all;
CHANGE MASTER TO
MASTER_HOST='10.10.16.24',
MASTER_USER='slave_passport',
MASTER_PASSWORD='slave_passport',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=804497686;
#檢查主庫
SHOW PROCESSLIST;
show master status \G
#啟動從庫
SHOW PROCESSLIST;
start slave;
show slave status \G
常見問題
Slave_SQL_Running:No
#一般是事務(wù)回滾造成的
stop slave;
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
start slave;
