使用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
1
2

生成的文件如下：

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;
1
2
3

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
1
2
3

上面两个命令都可以用，挑一个能用的就行
使用–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'
1

记录了备份导出的时间，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
1
2
3
4
5
6

把主数据库备份好的数据scp传输到从数据库主机上，开始恢复

xbstream -x < /tmp/fullbak3306.xbstream -C /home/my3306 
# /home/my3306是数据目录
# 然后需要恢复一下应用日志
innobackupex --apply-log /home/my3306

# 文件夹权限
chown -R mysql.mysql /home/my3306
1
2
3
4
5
6
7

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

3.2 从数据库设置

[mysqld@3306]

server_id= 16  # 其他配置与主数据库相同，只要是这个server_id配置不同即可

relay_log=mysql-relay-bin  #设置中继日志
read_only = 1  #设置从库只读
1
2
3
4
5
6

3.3 启动同步

启动从库

systemctl start mysqld@3306
1

查看备份信息

cat /home/my3306/xtrabackup_info
1

注意下面这一段

binlog_pos = filename 'mysql-201-3306-binlog.000239', position '65809038', GTID of the last change '62a3d6f5-f042-11ec-8fc2-fa163edc78d8:1-31177517'
1

设置复制

# 开启主从复制
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;
1
2
3
4
5

下面两个必须要是“YES”

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
1
2

如果出现同步报错信息，注意查看提示，排除错误后，会自动同步。

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');
1
2
3
4
5
6
7

从库

use userinfo;
show tables;
select * from user;
1
2
3

可以看到数据已经同步了。

4. 部分数据同步设置

4.1 只同步部分数据库

参考：https://blog.csdn.net/u010587433/article/details/49246097

主库设置不需要变更，只需要改从库设置

从库修改my.cnf配置文件
例如只同步bbp库，从库配置文件添加如下

replicate_do_db=bbp  
1

修改配置后，重启从库，要重新设置主从同步

如果要同步多个库，则需要指定多个replicate_do_db，如同步bbp和lmis库

replicate_do_db=bbp  
replicate_do_db=lmis  
1
2

注意：只同步某些库，可能会涉及跨库操作无法同步的问题：

• 1）在其它库中或者没有选择数据库，则从库不会执行
  例如：主库中在mtms库中操作bbp库的数据

mysql> SELECT * from bbp.t_csp;  
+-------+--------------+-------+  
| cspid | title        | isdel |  
+-------+--------------+-------+  
| 1     | 喜马拉雅     |     0 |  
| 2     | aaa          |     1 |  
+-------+--------------+-------+  
1
2
3
4
5
6
7

主库

mysql> use mtms  
Database changed  
mysql> insert into bbp.t_csp values(3,'bbb',0);  
mysql> delete from bbp.t_csp where cspid = 2;  
1
2
3
4

最后查询，主库

mysql> SELECT * from bbp.t_csp;  
+-------+--------------+-------+  
| cspid | title        | isdel |  
+-------+--------------+-------+  
| 1     | 喜马拉雅     |     0 |  
| 3     | bbb          |     0 |  
+-------+--------------+-------+  
1
2
3
4
5
6
7

从库

mysql> SELECT * from bbp.t_csp;  
+-------+--------------+-------+  
| cspid | title        | isdel |  
+-------+--------------+-------+  
| 1     | 喜马拉雅     |     0 |  
| 2     | aaa          |     1 |  
+-------+--------------+-------+  
1
2
3
4
5
6
7

• 2）在bbp中操作其它库数据，则从库会报错
  主库在bbp库中操作其它库数据，若从库中库表存在，且数据同步，则不影响；若库表不存在或数据不同步，则报相关错误，并终止同步
  例如：主库

mysql> use bbp  
mysql> insert into mtms.crm_branch values('1','aa','aa')
1
2

从库同步报错

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')'  
1
2

解决以上问题，需将replicate_wild_do_table参数代替replicate_do_db设置为

replicate_wild_do_table=bbp.%
replicate_wild_do_table=lmis.%
1
2

这样就可以
1）同步跨库操作
2）忽略对其它库的操作，同步不再报错

4.2 不同步指定库

从库上修改配置文件 my.cnf

replicate-ignore-db = mysql
replicate-ignore-db = test
replicate-ignore-db = information_schema
1
2
3

4.3 只同步指定表

replicate-wild-do-table = tt.admin
replicate-wild-do-table = my_db.stu  # 所要同步的数据库的单个表  库名.表名
1
2

5. 取消主从设置

在从库mysql上运行命令

mysql> stop slave;
mysql> reset slave all;
1
2

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
1

注意上面的一些参数不要漏掉，尤其是–master-data，会在备份的时候记录binlog日志的位置，在恢复的时候要用到

6.2 从库恢复

从库在执行恢复之前，最好是全新的库，不要有多余数据，只做一个初始化即可。
从库执行命令：

mysql -uroot -S /home/my3306/mysql.sock -p < backup3306.sql 
1

其它的一些操作就一样了。数据导入完毕后，设置连接主库，主库的binlog信息，在导出的sql文件里面有记录。

可以看出两种方式备份出来的大小是不同的，mysqldump备份出来的更小一些。

两种备份恢复方式都可以用，看自己的需要就可以。
xtrabackup速度更快，恢复也快，mysqldump速度慢，恢复也慢。

---

innobackupex实际上是percona-xtrabackup的perl整合脚本,功能当然更强大一些.
xtrabackup备份实际上是在线的物理热备,为什么这么说呢,因为实际上他是以拷贝mysql物理文件来备份的方式,只是加入了一些锁和钩子来避免数据缺失,优势当然就是快了,物理拷贝始终是速度最快的备份方式,缺点就是占用空间大。