采用xtrabackup部署主从同步(生产)
环境:
OS:Centos 7
DB:5.7.33
1.1 从库安装mysql
在从库上部署mysql，配置参数跟主库保持一致
1.2 安装xtrabackup
可以到官网下载安装介质
percona-xtrabackup-2.4.7-Linux-x86_64.tar.gz
主从库上都需要进行安装
解压安装
[root@localhost soft]# tar -xvf percona-xtrabackup-2.4.7-Linux-x86_64.tar.gz
[root@localhost soft]# mv percona-xtrabackup-2.4.7-Linux-x86_64 /opt/xtrabackup247
1.3 主库创建同步账号
grant replication slave, replication client on *.* to 'ureplsync'@'192.168.1.%' identified by 'mysql';
1.4 备份主库
[root@host01 bin]# /opt/xtrabackup247/bin/innobackupex --defaults-file=/opt/mysql5733/conf/my.cnf --user=root --password=mysql -P13306 --socket=/opt/mysql5733/mysql.sock /opt/xtrabackup_file
这里的文件存储路径/opt/xtrabackup_file需要提前创建好,备份完成后,会有ok的提示
210410 09:15:53 Executing UNLOCK TABLES
210410 09:15:53 All tables unlocked
210410 09:15:53 [00] Copying ib_buffer_pool to /opt/xtrabackup_file/2021-04-10_09-15-22/ib_buffer_pool
210410 09:15:53 [00] ...done
210410 09:15:53 Backup created in directory '/opt/xtrabackup_file/2021-04-10_09-15-22/'
MySQL binlog position: filename 'binlog.000004', position '1214', GTID of the last change 'fbf83c6c-9836-11eb-8ec4-52540051cd25:1-35'
210410 09:15:53 [00] Writing backup-my.cnf
210410 09:15:53 [00] ...done
210410 09:15:53 [00] Writing xtrabackup_info
210410 09:15:53 [00] ...done
xtrabackup: Transaction log of lsn (2798648) to (2798657) was copied.
210410 09:15:53 completed OK!
查看备份文件目录
[root@localhost xtrabackup_file]# ls -1 /opt/xtrabackup_file
2021-04-10_09-15-22
我们将该目录打包,传送到目标的机器上
[root@localhost xtrabackup_file]# tar -cvf 2021-04-10_09-15-22.tar ./2021-04-10_09-15-22
[root@localhost xtrabackup_file]#scp 2021-04-10_09-15-22.tar root@目标机器ip:/tmp/
1.5 备库恢复
1.5.1 从库tar文件解压缩
[root@localhost xtrabackup_file]# cd /tmp/
[root@localhost tmp]# tar -xvf 2021-04-10_09-15-22.tar
1.5.2 停掉备库(之前有主备环境的情况下)
[root@localhost mysql5733_slave]# /opt/mysql5733_slave/bin/mysqladmin -h localhost -uroot -P23306 -pmysql -S /opt/mysql5733_slave/mysql.sock shutdown
1.5.3 备份data目录并创建新的data目录
[root@localhost mysql5733_slave]#cd /opt/mysql5733_slave
[root@localhost mysql5733_slave]# mv data bakdata
同时创建新的目录
[root@localhost mysql5733_slave]# mkdir data
1.5.4 删除redolog(看实际需要)
redo路径不是在data目录下才需要这步骤,可以查看备库参数innodb_log_group_home_dir
[root@localhost mysql5733_slave]# cd redolog
[root@localhost mysql5733_slave]# ls
ib_logfile0 ib_logfile1 ib_logfile2 ib_logfile3 ib_logfile4
[root@localhost mysql5733_slave]# rm *
rm: remove regular file ‘ib_logfile0’? y
rm: remove regular file ‘ib_logfile1’? y
rm: remove regular file ‘ib_logfile2’? y
rm: remove regular file ‘ib_logfile3’? y
rm: remove regular file ‘ib_logfile4’? y
1.5.5 恢复(备库也事先安装好xtrabackup软件)
[root@localhost ]# /opt/xtrabackup247/bin/innobackupex --defaults-file=/opt/mysql5733_slave/conf/my.cnf --user=root --apply-log /tmp/2021-04-10_09-15-22
完成后会有ok提示
[root@localhost]# /opt/xtrabackup247/bin/innobackupex --defaults-file=/opt/mysql5733_slave/conf/my.cnf --user=root --copy-back --rsync /tmp/2021-04-10_09-15-22
完成后会有ok提示
1.5.6 修改权限
[root@localhost /]# cd /opt/
[root@localhost opt]# chown -R mysql:mysql ./mysql5733_slave
1.5.7 启动从库
[root@localhost opt]# su - mysql
[mysql@localhost ~]$ /opt/mysql5733_slave/bin/mysqld_safe --defaults-file=/opt/mysql5733_slave/conf/my.cnf --user=mysql &
1.6 主从同步
1.6.1 主库上创建同步账号(若已经创建则省略)
grant replication slave, replication client on *.* to 'ureplsync'@'192.168.1.%' identified by 'mysql';
1.6.2 从库部署同步
change master to master_host='192.168.1.135',
master_port=13306,
master_user='ureplsync',
master_password='mysql',
master_log_file='binlog.000004',
master_log_pos=1214;
找到主从同步的参数值
master_log_file和master_log_pos
这两个参数可以在xtrabackup备份目录下的文件中查找到
[root@localhost 2021-04-10_09-15-22]# more xtrabackup_binlog_info
binlog.000004 1214 fbf83c6c-9836-11eb-8ec4-52540051cd25:1-35
1.6.3 启动从库
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
1.6.4 查看同步情况
show slave status \G;
