Innobackupex实现mysql在线搭建master-slave主从复制
oracle、mysql、sqlserver這種使用物理備份做master-slave主從的，原理都是一樣，主庫不需要停機，主庫在線做好物理備份后，恢復(fù)物理備份到從庫，從庫以主庫物理備份開始的這個時刻點為基準(zhǔn)點開始同步來自主庫的日志鏈，因為slave read only對root用戶不起作用，所以slave庫搭建好后記得禁用event或slave啟動之前設(shè)置event_scheduler=off
主庫為ibdcmsproddb11，從庫為ibdcmsproddb12和ibdcmsproddb13，使用innobackupex在線搭建mysql庫的master-slave主從復(fù)制
操作步驟
1、主庫的數(shù)據(jù)庫列表和binlog文件名稱和位置，和主庫現(xiàn)有的slave實例
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | MariaDB?[(none)]>?show?databases; +--------------------+ |?Database???????????| +--------------------+ |?ibdcmsarchive_wp???| |?ibdcmsprod_wp??????| |?ibdmarketing_wp????| |?information_schema?| |?monitor????????????| |?mysql??????????????| |?performance_schema?| |?sys????????????????| +--------------------+ 8?rows?in?set?(0.07?sec) |
| 1 2 3 4 5 6 7 | MariaDB?[(none)]>?show?master?status; +------------------+-----------+--------------+------------------+ |?File?????????????|?Position??|?Binlog_Do_DB?|?Binlog_Ignore_DB?| +------------------+-----------+--------------+------------------+ |?mysql-bin.000660?|?103351805?|??????????????|??????????????????| +------------------+-----------+--------------+------------------+ 1?row?in?set?(0.00?sec) |
| 1 2 3 | MariaDB?[(none)]>?select?*?from?information_schema.processlist?as?p?where? p.command?=?'Binlog?Dump'; Empty?set?(0.00?sec) |
2、從庫12的數(shù)據(jù)庫列表
| 1 2 3 4 5 6 7 8 9 10 11 | MariaDB?[(none)]>?show?databases; +--------------------+ |?Database???????????| +--------------------+ |?ibdmarketing_wp????| |?information_schema?| |?monitor????????????| |?mysql??????????????| |?performance_schema?| |?sys????????????????| +--------------------+ |
? 從庫13的數(shù)據(jù)庫列表
| 1 2 3 4 5 6 7 8 9 10 11 12 | MariaDB?[(none)]>?show?databases; +--------------------+ |?Database???????????| +--------------------+ |?ibdcmsarchive_wp???| |?ibdcmsprod_wp??????| |?information_schema?| |?mysql??????????????| |?performance_schema?| |?sys????????????????| +--------------------+ 6?rows?in?set?(0.00?sec) |
3、主庫備份數(shù)據(jù)庫并創(chuàng)建用于同步的賬號
給機器12做的備份
| 1 2 | innobackupex???-uroot?-p'pwd'?--stream=tar???/tmp??|?gzip?>? /root/MySQLBackups/xtrabackup/ibdcmsproddb3_20210730_01.tar.gz |
給機器13做的備份
| 1 2 | innobackupex??-uroot?-p'pwd'?--no-timestamp? /root/MySQLBackups/xtrabackup/ibdcmsproddb3_20210730_02 |
備注：
--no-timestamp表示不生成日期和時間格式的備份目錄，備份文件直接放入/root/MySQLBackups/xtrabackup/ibdcmsproddb3_20210730_02目錄，如下沒有使用--no-timestamp表示在/root/MySQLBackups/xtrabackup目錄下生成一個日期和時間命名的目錄來存放備份文件
| 1 2 | innobackupex??-uroot?-p'pwd'?/root/MySQLBackups/xtrabackup>> /root/MySQLBackups/xtrabackup/ibdcmsproddb3_20210730_for13.log |
創(chuàng)建用于同步的賬號
| 1 | mysql>?GRANT?REPLICATION?SLAVE?ON?*.*?TO?'slaveuser'@'%'?identified?by?'mima123456'; |
4、把innobackupex的物理備份文件從主庫拷貝到從庫，關(guān)閉從庫的mysql服務(wù)，再在從庫恢復(fù)，再進行復(fù)制的配置
機器12
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | [root@ibdcmsproddb12?~]#?scp?root@Ibdcmsproddb11:/root/MySQLBackups/ xtrabackup/ibdcmsproddb3_20210730_01.tar.gz?/root/backup --拷貝備份文件到機器12 [root@ibdcmsproddb12?~]#?mkdir?/root/backup/ibdcmsproddb3_20210730_01 [root@ibdcmsproddb12?~]#?tar?-xzvf?/root/backup/ibdcmsproddb3_20210730_01.tar.gz?-C? /root/backup/ibdcmsproddb3_20210730_01 --對拷貝文件進行tar解包 [root@ibdcmsproddb12?~]#?service?mysql?stop --機器12關(guān)閉mysql服務(wù) [root@ibdcmsproddb12?~]#?mv?/var/lib/mysql?/var/lib/mysql.bak --機器12原來的數(shù)據(jù)庫目錄備份 [root@ibdcmsproddb12?~]#?mkdir?/var/lib/mysql [root@ibdcmsproddb12?~]#?chown?mysql.mysql?/var/lib/mysql? --機器12創(chuàng)建新的數(shù)據(jù)庫目錄 [root@ibdcmsproddb12?~]#?innobackupex?--apply-log?/root/backup/ibdcmsproddb3_20210730_01 |
--對機器11的數(shù)據(jù)庫備份進行日志應(yīng)用
| 1 2 | [root@ibdcmsproddb12?~]#?innobackupex?--copy-back?--datadir= /var/lib/mysql?/root/backup/ibdcmsproddb3_20210730_01 |
--把機器11的數(shù)據(jù)庫備份拷貝回機器12的數(shù)據(jù)庫目錄
| 1 | [root@ibdcmsproddb12?~]#?cat?/root/backup/ibdcmsproddb3_20210730_01/xtrabackup_binlog_info |
mysql-bin.000660??????? 103353805?????? 0-61-183782513
--記錄機器11的數(shù)據(jù)庫備份文件的備份時刻點的binlog文件名稱和位置，即機器12后面做CHANGE MASTER TO MASTER_LOG_FILE這一行信息
機器12修改配置項
| 1 2 3 4 5 | [root@ibdcmsproddb12?~]#?vi?/etc/my.cnf.d/server.cnf read_only=true event_scheduler=off datadir=/var/lib/mysql server-id=102 |
機器12配置同步
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | [root@ibdcmsproddb12?~]#?chown?-R?mysql.mysql?/var/lib/mysql [root@ibdcmsproddb12?~]#?service?mysql?start [root@ibdcmsproddb12?~]#?mysql?-uroot?-p mysql>?select?db,name,last_executed,status?from?mysql.event; mysql>?alter?event?dbname.eventname?disable; mysql>?show?slave?status\G mysql>?stop?slave; mysql>?change?master?to?master_host='ibdcmsproddb11',master_port=3306, master_user='slaveuser',master_password='mima123456',? MASTER_LOG_FILE='mysql-bin.000660',?MASTER_LOG_POS=103353805; --MASTER_LOG_FILE和MASTER_LOG_POS來自上面的/root/backup/ibdcmsproddb3_20210730_01/ xtrabackup_binlog_info mysql>?show?warnings; mysql>?show?slave?status\G mysql>?start?slave; mysql>?show?slave?status\G mysql>?show?databases; |
機器13
| 1 2 | [root@ibdcmsproddb13?~]#?scp?-r?root@Ibdcmsproddb11:/root/MySQLBackups/xtrabackup/ ibdcmsproddb3_20210730_02?/root/backup |
--拷貝備份文件到機器13
| 1 | [root@ibdcmsproddb13?~]#?service?mysql?stop |
--機器13關(guān)閉mysql服務(wù)
| 1 | [root@ibdcmsproddb13?~]#?mv?/var/lib/mysql?/var/lib/mysql.bak |
--機器13原來的數(shù)據(jù)庫目錄備份
| 1 2 | [root@ibdcmsproddb13?~]#?mkdir?/var/lib/mysql [root@ibdcmsproddb13?~]#?chown?mysql.mysql?/var/lib/mysql |
--機器13創(chuàng)建新的數(shù)據(jù)庫目錄
| 1 | [root@ibdcmsproddb13?~]#?innobackupex?--apply-log?/root/backup/ibdcmsproddb3_20210730_02/ |
--對機器11的數(shù)據(jù)庫備份進行日志應(yīng)用
| 1 2 | [root@ibdcmsproddb13?~]#?innobackupex?--copy-back?--datadir=/var/lib/mysql? /root/backup/ibdcmsproddb3_20210730_02/ |
--把機器11的數(shù)據(jù)庫備份拷貝回機器13的數(shù)據(jù)庫目錄
| 1 2 | [root@ibdcmsproddb13?~]#?cat?/root/backup/ibdcmsproddb3_20210730_02/xtrabackup_binlog_info mysql-bin.000666????????103789407???????0-61-183782513 |
--記錄機器11的數(shù)據(jù)庫備份文件的備份時刻點的binlog文件名稱和位置，即機器13后面做CHANGE MASTER TO MASTER_LOG_FILE這一行信息
機器13修改配置項
| 1 2 3 4 5 | [root@ibdcmsproddb13?~]#?vi?/etc/my.cnf.d/server.cnf read_only=true event_scheduler=off datadir=/var/lib/mysql server-id=103 |
機器13配置同步
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | [root@ibdcmsproddb13?~]#?chown?-R?mysql.mysql?/var/lib/mysql [root@ibdcmsproddb13?~]#?service?mysql?start [root@ibdcmsproddb13?~]#?mysql?-uroot?-p mysql>?select?db,name,last_executed,status?from?mysql.event; mysql>?alter?event?dbname.eventname?disable; mysql>?show?slave?status\G mysql>?stop?slave; mysql>?change?master?to?master_host='ibdcmsproddb11',master_port=3306, master_user='slaveuser',master_password='mima123456',? MASTER_LOG_FILE='mysql-bin.000666',?MASTER_LOG_POS=103789407; --MASTER_LOG_FILE和MASTER_LOG_POS來自上面的/root/backup/ibdcmsproddb3_20210730_02/ xtrabackup_binlog_info mysql>?show?warnings; mysql>?show?slave?status\G mysql>?start?slave; mysql>?show?slave?status\G mysql>?show?databases; |
總結(jié)
以上是生活随笔為你收集整理的Innobackupex实现mysql在线搭建master-slave主从复制的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： MySQL数据库InnoDB坏页处理修复
- 下一篇： MySQL触发器介绍