使用xtrabackup，mysqldump进行mysql5.7主从数据同步配置
前言
mysql主从同步，主要用xtrabackup或者mysqldump这两种工具先进行数据备份，然后还原数据到从库，开启数据同步。在进行主从配置的时候，一般都不想停业务，这时候就要锁表，或者是主库只读，或者是记录备份的位置，从指定位置恢复。下面主要是介绍使用xtrabackup这个工具进行数据备份和恢复，mysqldump简单说明一下。
以下主要探讨全量同步，在centos7主机，mysql5.7环境下
主数据库：172.16.0.1:3306
从数据库：172.16.0.4:3306
1. 初始准备
1.1 主从安装xtrabackup
wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.26/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.26-1.el7.x86_64.rpm
yum localinstall -y ./percona-xtrabackup-24-2.4.26-1.el7.x86_64.rpm
生成的文件如下：
xtrabackup可以在不加锁的情况下备份innodb数据表，不过此工具不能操作myisam。
innobackupex是一个封装了xtrabackup的脚本，能同时处理innodb和myisam，但在处理myisam时需要加一个读锁。
按如上的介绍，由于操作myisam时需要加读锁，这会堵塞线上服务的写操作，而innodb没有这样的限制，所以数据库中innodb表类型所占的比例越大，则越有利。实际应用中一般是直接使用innobackupex。
参考链接：https://blog.csdn.net/aspnet_lyc/article/details/102891987
1.2 同步账号设置
在主数据库上设置权限账号
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'172.16.0.%' IDENTIFIED BY 'Repl#2022';
create database repltest; #这个是要复制的测试数据库
flush privileges;
2. 主从数据库设置
2.1 导出备份
主数据库，xtrabackup数据导出备份
innobackupex -S /home/my3306/mysql.sock --host=localhost --port=3306 --user=root --password=Root@1234 --slave-info --stream=xbstream /tmp/ > /tmp/fullbak3306.xbstream
# 或者是
innobackupex --defaults-file=/etc/my.cnf --host=localhost --port=3306 --user=root --password=Root@1234 --slave-info --stream=xbstream /tmp/ > /tmp/fullbak3306.xbstream
上面两个命令都可以用，挑一个能用的就行
使用–defaults-file=/etc/my.cnf，可能会报错，就用另一个
备份结束，日志显示
下面这一行比较重要
MySQL binlog position: filename 'mysql-201-3306-binlog.000239', position '65809038', GTID of the last change '62a3d6f5-f042-11ec-8fc2-fa163edc78d8:1-31177517'
记录了备份导出的时间，binlog位置，方便数据恢复。
2.2 导入备份
从数据库，xtrabackup导入数据备份
systemctl stop mysqld-3306
ps -ef|grep mysql
cd /home/
mv my3306 my3306-bak
mkdir my3306
chown -R mysql.mysql my3306
把主数据库备份好的数据scp传输到从数据库主机上，开始恢复
xbstream -x < /tmp/fullbak3306.xbstream -C /home/my3306
# /home/my3306是数据目录
# 然后需要恢复一下应用日志
innobackupex --apply-log /home/my3306
# 文件夹权限
chown -R mysql.mysql /home/my3306
3. 设置主从同步
3.1 主数据库设置
[mysqld@3306]
server_id=20
port = 3306
datadir=/home/my3306
socket=/home/my3306/mysql.sock
log-error=/home/my3306/mysqld.log
pid-file=/home/my3306/mysqld.pid
log_bin=mysql-201-3306-binlog
# gtid开启
gtid_mode=ON
enforce-gtid-consistency=ON
max_connections=2001
slow_query_log=ON
binlog_format=row
skip-name-resolve
log-slave-updates=1
relay_log_purge=0
back_log=128
wait_timeout=60
interactive_timeout=7200
key_buffer_size=16M
#query_cache_size=64M
#query_cache_type=1
#query_cache_limit=50M
max_connect_errors=20
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
thread_cache_size=200
innodb_buffer_pool_size = 5000M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=3
innodb_print_all_deadlocks = 1
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
read_buffer_size=2M
read_rnd_buffer_size=2M
bulk_insert_buffer_size=8M
3.2 从数据库设置
[mysqld@3306]
server_id= 16 # 其他配置与主数据库相同，只要是这个server_id配置不同即可
relay_log=mysql-relay-bin #设置中继日志
read_only = 1 #设置从库只读
3.3 启动同步
启动从库
systemctl start mysqld@3306
查看备份信息
cat /home/my3306/xtrabackup_info
注意下面这一段
binlog_pos = filename 'mysql-201-3306-binlog.000239', position '65809038', GTID of the last change '62a3d6f5-f042-11ec-8fc2-fa163edc78d8:1-31177517'
设置复制
# 开启主从复制
show slave status;
change master to master_host='172.16.0.1',master_port=3306,master_user='repluser',master_password='Repl#2022',master_log_file='mysql-201-3306-binlog.000239',master_log_pos=65809038;
start slave;
show slave status;
下面两个必须要是“YES”
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果出现同步报错信息，注意查看提示，排除错误后，会自动同步。
3.4 验证同步
可以在主库新建数据库表，在从库查看
主库
create database userinfo;
use userinfo;
create table user(id int(3), name char(10) address char(32));
insert into user values(001,'zhangsan', 'beijing');
insert into user values(002,'lisi', 'zhejiang');
insert into user values(003,'wanger', 'shanghai');
insert into user values(004,'zhangxiaoming', 'henan');
从库
use userinfo;
show tables;
select * from user;
可以看到数据已经同步了。
4. 部分数据同步设置
4.1 只同步部分数据库
参考：https://blog.csdn.net/u010587433/article/details/49246097
主库设置不需要变更，只需要改从库设置
从库修改my.cnf配置文件
例如只同步bbp库，从库配置文件添加如下
replicate_do_db=bbp
修改配置后，重启从库，要重新设置主从同步
如果要同步多个库，则需要指定多个replicate_do_db，如同步bbp和lmis库
replicate_do_db=bbp
replicate_do_db=lmis
注意：只同步某些库，可能会涉及跨库操作无法同步的问题：
- 1）在其它库中或者没有选择数据库，则从库不会执行
例如：主库中在mtms库中操作bbp库的数据
mysql> SELECT * from bbp.t_csp;
+-------+--------------+-------+
| cspid | title | isdel |
+-------+--------------+-------+
| 1 | 喜马拉雅 | 0 |
| 2 | aaa | 1 |
+-------+--------------+-------+
主库
mysql> use mtms
Database changed
mysql> insert into bbp.t_csp values(3,'bbb',0);
mysql> delete from bbp.t_csp where cspid = 2;
最后查询，主库
mysql> SELECT * from bbp.t_csp;
+-------+--------------+-------+
| cspid | title | isdel |
+-------+--------------+-------+
| 1 | 喜马拉雅 | 0 |
| 3 | bbb | 0 |
+-------+--------------+-------+
从库
mysql> SELECT * from bbp.t_csp;
+-------+--------------+-------+
| cspid | title | isdel |
+-------+--------------+-------+
| 1 | 喜马拉雅 | 0 |
| 2 | aaa | 1 |
+-------+--------------+-------+
- 2）在bbp中操作其它库数据，则从库会报错
主库在bbp库中操作其它库数据，若从库中库表存在，且数据同步，则不影响；若库表不存在或数据不同步，则报相关错误，并终止同步
例如：主库
mysql> use bbp
mysql> insert into mtms.crm_branch values('1','aa','aa')
从库同步报错
Last_Errno: 1146
Last_Error: Error 'Table 'mtms.crm_branch' doesn't exist' on query. Default database: 'bbp'. Query: 'INSERT into mtms.crm_branch values('1','aa','aa')'
解决以上问题，需将replicate_wild_do_table参数代替replicate_do_db设置为
replicate_wild_do_table=bbp.%
replicate_wild_do_table=lmis.%
这样就可以
1）同步跨库操作
2）忽略对其它库的操作，同步不再报错
4.2 不同步指定库
从库上修改配置文件 my.cnf
replicate-ignore-db = mysql
replicate-ignore-db = test
replicate-ignore-db = information_schema
4.3 只同步指定表
replicate-wild-do-table = tt.admin
replicate-wild-do-table = my_db.stu # 所要同步的数据库的单个表 库名.表名
5. 取消主从设置
在从库mysql上运行命令
mysql> stop slave;
mysql> reset slave all;
6. 使用mysqldump进行数据备份和恢复
实际上不太建议直接用mysqldump进行整库的备份和恢复，尤其是数据量比较大的情况下。因为没有xtrabackup备份恢复方便一些。不过如果xtrabackup安装包下载不了，或者是不想用xtrabackup，可以作为一个备用方案。
6.1 主库备份
mysqldump -S /home/my3306/mysql.sock -uroot -p'Root@1234' --all-databases --master-data --set-gtid-purged=OFF --triggers --routines --events > backup3306.sql
注意上面的一些参数不要漏掉，尤其是–master-data，会在备份的时候记录binlog日志的位置，在恢复的时候要用到
6.2 从库恢复
从库在执行恢复之前，最好是全新的库，不要有多余数据，只做一个初始化即可。
从库执行命令：
mysql -uroot -S /home/my3306/mysql.sock -p < backup3306.sql
其它的一些操作就一样了。数据导入完毕后，设置连接主库，主库的binlog信息，在导出的sql文件里面有记录。
可以看出两种方式备份出来的大小是不同的，mysqldump备份出来的更小一些。
两种备份恢复方式都可以用，看自己的需要就可以。
xtrabackup速度更快，恢复也快，mysqldump速度慢，恢复也慢。
innobackupex实际上是percona-xtrabackup的perl整合脚本,功能当然更强大一些.
xtrabackup备份实际上是在线的物理热备,为什么这么说呢,因为实际上他是以拷贝mysql物理文件来备份的方式,只是加入了一些锁和钩子来避免数据缺失,优势当然就是快了,物理拷贝始终是速度最快的备份方式,缺点就是占用空间大。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
