Innobackupex實作mysql線上搭建master-slave主從複制

oracle、mysql、sqlserver這種使用實體備份做master-slave主從的，原理都是一樣，主庫不需要停機，主庫線上做好實體備份後，恢複實體備份到從庫，從庫以主庫實體備份開始的這個時刻點為基準點開始同步來自主庫的日志鍊，因為slave read only對root使用者不起作用，是以slave庫搭建好後記得禁用event或slave啟動之前設定event_scheduler=off

主庫為ibdcmsproddb11，從庫為ibdcmsproddb12和ibdcmsproddb13，使用innobackupex線上搭建mysql庫的master-slave主從複制

操作步驟

1、主庫的資料庫清單和binlog檔案名稱和位置，和主庫現有的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)

MariaDB [(none)]> show master status; + ------------------+-----------+--------------+------------------+ | File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | + ------------------+-----------+--------------+------------------+ | mysql-bin.000660 | 103351805 |              |                  | + ------------------+-----------+--------------+------------------+ 1 row  in set (0.00 sec)

MariaDB [(none)]>  select *  from information_schema.processlist  as p  where p.command =  'Binlog Dump' ; Empty  set (0.00 sec)

2、從庫12的資料庫清單

MariaDB [(none)]> show databases; + --------------------+ |  Database | + --------------------+ | ibdmarketing_wp    | | information_schema | | monitor            | | mysql              | | performance_schema | | sys                | + --------------------+

  從庫13的資料庫清單

MariaDB [(none)]> show databases; + --------------------+ |  Database | + --------------------+ | ibdcmsarchive_wp   | | ibdcmsprod_wp      | | information_schema | | mysql              | | performance_schema | | sys                | + --------------------+ 6  rows in set (0.00 sec)

3、主庫備份資料庫并建立用于同步的賬号

給機器12做的備份

innobackupex   -uroot -p 'pwd' --stream=tar   /tmp  | gzip >  /root/MySQLBackups/xtrabackup/ibdcmsproddb3_20210730_01.tar.gz

給機器13做的備份

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目錄下生成一個日期和時間命名的目錄來存放備份檔案

innobackupex  -uroot -p 'pwd' /root/MySQLBackups/xtrabackup>> /root/MySQLBackups/xtrabackup/ibdcmsproddb3_20210730_for13.log

建立用于同步的賬号

mysql>  GRANT REPLICATION SLAVE  ON *.*  TO 'slaveuser' @ '%' identified  by 'mima123456' ;

4、把innobackupex的實體備份檔案從主庫拷貝到從庫，關閉從庫的mysql服務，再在從庫恢複，再進行複制的配置

機器12

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關閉mysql服務 [root@ibdcmsproddb12 ~]# mv /var/lib/mysql /var/lib/mysql.bak --機器12原來的資料庫目錄備份 [root@ibdcmsproddb12 ~]# mkdir /var/lib/mysql [root@ibdcmsproddb12 ~]# chown mysql.mysql /var/lib/mysql  --機器12建立新的資料庫目錄 [root@ibdcmsproddb12 ~]# innobackupex  --apply-log /root/backup/ibdcmsproddb3_20210730_01

--對機器11的資料庫備份進行日志應用

[root@ibdcmsproddb12 ~]# innobackupex  --copy-back --datadir= /var/lib/mysql /root/backup/ibdcmsproddb3_20210730_01

--把機器11的資料庫備份拷貝回機器12的資料庫目錄

[root@ibdcmsproddb12 ~]# cat /root/backup/ibdcmsproddb3_20210730_01/xtrabackup_binlog_info

mysql-bin.000660        103353805       0-61-183782513

--記錄機器11的資料庫備份檔案的備份時刻點的binlog檔案名稱和位置，即機器12後面做CHANGE MASTER TO MASTER_LOG_FILE這一行資訊

機器12修改配置項

[root@ibdcmsproddb12 ~]# vi /etc/my.cnf.d/server.cnf read_only= true event_scheduler= off datadir=/var/lib/mysql server-id=102

機器12配置同步

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

[root@ibdcmsproddb13 ~]# scp -r root@Ibdcmsproddb11:/root/MySQLBackups/xtrabackup/ ibdcmsproddb3_20210730_02 /root/backup

--拷貝備份檔案到機器13

[root@ibdcmsproddb13 ~]# service mysql stop

--機器13關閉mysql服務

[root@ibdcmsproddb13 ~]# mv /var/lib/mysql /var/lib/mysql.bak

--機器13原來的資料庫目錄備份

[root@ibdcmsproddb13 ~]# mkdir /var/lib/mysql [root@ibdcmsproddb13 ~]# chown mysql.mysql /var/lib/mysql

--機器13建立新的資料庫目錄

[root@ibdcmsproddb13 ~]# innobackupex  --apply-log /root/backup/ibdcmsproddb3_20210730_02/

[root@ibdcmsproddb13 ~]# innobackupex  --copy-back --datadir=/var/lib/mysql  /root/backup/ibdcmsproddb3_20210730_02/

--把機器11的資料庫備份拷貝回機器13的資料庫目錄

[root@ibdcmsproddb13 ~]# cat /root/backup/ibdcmsproddb3_20210730_02/xtrabackup_binlog_info mysql-bin.000666        103789407       0-61-183782513

--記錄機器11的資料庫備份檔案的備份時刻點的binlog檔案名稱和位置，即機器13後面做CHANGE MASTER TO MASTER_LOG_FILE這一行資訊

機器13修改配置項

[root@ibdcmsproddb13 ~]# vi /etc/my.cnf.d/server.cnf read_only= true event_scheduler= off datadir=/var/lib/mysql server-id=103

機器13配置同步

[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