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例項

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

[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配置同步

[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/

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

[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
mysql> start slave;
mysql> show slave status\G
mysql> show databases;