XtraBackup不停機不鎖表搭建MySQL主從同步實踐
前言
Percona XtraBackup可以說是一個相對完美的免費開源數據備份工具，支持在線無鎖表同步復制和可并行高效率的安全備份恢復機制相比mysqldump來說確實讓人眼前一亮，與MySQL Enterprise Backup(InnoDB Hot Backup)的功能對比可以參考擴展閱讀。當然我們在實際運維過程中都應針對不同的業務需求分析和選擇合適的備份恢復方案，這篇文章就是針對MySQL多實例且一個實例對應多個database的情況，實現MySQL在線不停機不鎖表的主從同步，日后再繼續更新分享基于XtraBackup的其它實用技能。
XtraBackup是目前首選的備份方案之一
</div>更新歷史
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的物理備份解決方案預研 - 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
</div>原理
MySQL主從同步原理
MySQL主從同步是在MySQL主從復制(Master-Slave Replication)基礎上實現的，通過設置在Master MySQL上的binlog(使其處于打開狀態)，Slave MySQL上通過一個I/O線程從Master MySQL上讀取binlog，然后傳輸到Slave MySQL的中繼日志中，然后Slave MySQL的SQL線程從中繼日志中讀取中繼日志，然后應用到Slave MySQL的數據庫中。這樣實現了主從數據同步功能。
XtraBackup備份原理
innobackupex在后臺線程不斷追蹤InnoDB的日志文件，然后復制InnoDB的數據文件。數據文件復制完成之后，日志的復制線程也 會結束。這樣就得到了不在同一時間點的數據副本和開始備份以后的事務日志。完成上面的步驟之后，就可以使用InnoDB崩潰恢復代碼執行事務日志 （redo log），以達到數據的一致性。備份分為兩個過程：
- backup，備份階段，追蹤事務日志和復制數據文件（物理備份）。
- preparing，重放事務日志，使所有的數據處于同一個時間點，達到一致性狀態。 </ol>
- 可以快速可靠的完成數據備份（復制數據文件和追蹤事務日志）
- 數據備份過程中不會中斷事務的處理（熱備份）
- 節約磁盤空間和網絡帶寬
- 自動完成備份鑒定
- 因更快的恢復時間而提高在線時間 </ol>
XtraBackup的優點
配置
準備工作
MySQL步驟和my.cnf配置參考LTMP - http://wsgzao.github.io/post/ltmp/
</div>#原有主數據庫版本 mysql -V mysql Ver 14.14 Distrib 5.5.31, for Linux (x86_64) using readline 5.1遷移從數據庫版本
mysql -V mysql Ver 14.14 Distrib 5.6.25, for linux-glibc2.5 (x86_64) using EditLine wrapper
檢查數據庫引擎
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 | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
主從數據庫同步注意點
[mysqld]
主從之間的id不能相同
server-id
啟用二進制日志
log-bin
一般在從庫開啟（可選）
read_only
推薦使用InnoDB并做好相關配置
檢查主從數據庫狀態
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 | +---------------+-------+</pre>
安裝percona-xtrabackup
一般推薦rpm安裝 - https://www.percona.com/downloads/XtraBackup/LATEST/
</div>yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQLrpm -ivh percona-xtrabackup-2.2.12-1.el6.x86_64.rpm
rpm -Uvh percona-xtrabackup-2.2.12-1.el6.x86_64.rpm</pre>
備份和恢復
通常一般都直接使用innobackupex，因為它能同時備份InnoDB和MyISAM引擎的表重點關注Slave_IO_Running和Slave_SQL_Runningd的狀態是否為YES
</div>#備份 innobackupex --socket=/usr/local/var/mysql2/mysql2.sock --user=root --password --defaults-file=/etc/mysqld_multi.cnf --parallel=4 --database=passport /tmp/backup保持事務一致性
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/
恢復
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/
還原權限
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
主庫授權同步帳號
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;
查看從庫狀態
show slave status\ G
從庫的檢查參數
Slave_IO_Running=Yes Slave_SQL_Running=Yes
主庫的檢查參數
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</pre>
MySQL主從切換
切換前斷開主庫訪問連接觀察進程狀態，無寫操作后再停止從庫IO_THREAD進行切換
</div>#查看主庫狀態 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</pre>
常見問題
Slave_SQL_Running:No
</div>#一般是事務回滾造成的 stop slave; set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; start slave;
本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！相關資訊