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)

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的数据库列表

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的数据库列表

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、主库备份数据库并创建用于同步的账号
给机器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

创建用于同步的账号

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

4、把innobackupex的物理备份文件从主库拷贝到从库，关闭从库的mysql服务，再在从库恢复，再进行复制的配置
机器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关闭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的数据库备份进行日志应用

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

--把机器11的数据库备份拷贝回机器12的数据库目录

1	[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修改配置项

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关闭mysql服务

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

--机器13原来的数据库目录备份

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

--机器13创建新的数据库目录

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

--对机器11的数据库备份进行日志应用

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

--把机器11的数据库备份拷贝回机器13的数据库目录

1 2	[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修改配置项

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;