MySQL5.6主从复制最佳实践
MySQL5.6 主从复制的配置
环境
操作系统:CentOS-6.6-x86_64
MySQL 版本:mysql-5.6.26.tar.gz
主节点 IP:192.168.31.57 主机名:edu-mysql-01
从节点 IP:192.168.31.59 主机名:edu-mysql-02
MySQL 主从复制官方文档
http://dev.mysql.com/doc/refman/5.6/en/replication.htm l
MySQL 主从复制(也称 A/B 复制)的原理
- Master 将数据改变记录到二进制日志(binary log)中,也就是配置文件 log-bin 指定的文件,这些记录叫做二进制日志事件(binary log events);
- Slave 通过 I/O 线程读取 Master 中的 binary log events 并写入到它的 中继日志(relay log);
- Slave 重做中继日志中的事件,把中继日志中的事件信息一条一条的在 本地执行一次,完成数据在本地的存储,从而实现将改变反映到它自己的 数据(数据重放)。
主从配置需要注意的点
- 主从服务器操作系统版本和位数一致;主从服务器的 hostname 不要一致。
- Master 和 Slave 数据库的版本要一致;
- Master 和 Slave 数据库中的数据要一致;
- Master 开启二进制日志,Master 和 Slave 的 server_id 在局域网内必 须唯一;
- Master 和 Slave 都创建数据库 mbank,表 testuser ;
Master 配置
1、Master(192.168.31.57)和 Slave(192.168.31.59) 注意:两台数据库服务器的的 selinux 都要 disable(永久关闭 selinux,请 修改/etc/selinux/config,将 SELINUX 改为 disabled)
2、修改 Master 的配置文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/etc/my
.cnf [root@edu-mysql-01 ~]
# vi /etc/my.cnf
# 在 [mysqld] 中增加以下配置项
# 设置 server_id,一般设置为 IP
server_id=57
# 复制过滤:需要备份的数据库,输出 binlog
binlog-
do
-db=mbank
# 复制过滤:不需要备份的数据库,不输出(mysql 库一般不同步)
binlog-ignore-db=mysql
# 开启二进制日志功能,可以随便取,最好有含义
log-bin=edu-mysql-bin
# 为每个 session 分配的内存,在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
# 主从复制的格式(mixed,statement,row,默认格式是 statement)
binlog_format=mixed
# 二进制日志自动删除/过期的天数。默认值为 0,表示不自动删除。
expire_logs_days=7
# 跳过主从复制中遇到的所有错误或指定类型的错误,避免 slave 端复制中断。
# 如:1062 错误是指一些主键重复,1032 错误是因为主从数据库数据不一 致
slave_skip_errors=1062
# 如果需要同步函数或者存储过程
log_bin_trust_function_creators=
true
(如想了解以上参数的更多详细解析,可以直接百度参数名)
2.1 复制过滤可以让你只复制服务器中的一部分数据,有两种复制过滤:
- 在 Master 上过滤二进制日志中的事件;
- 在 Slave 上过滤中继日志中的事件。
2.2 MySQL 对于二进制日志 (binlog)的复制类型
- 基于语句的复制:在 Master 上执行的 SQL 语句,在 Slave 上执行同样的语句。MySQL 默认采用基于语句的复制,效率比较高。一旦发现没法精 确复制时,会自动选着基于行的复制。
- 基于行的复制:把改变的内容复制到 Slave,而不是把命令在 Slave 上 执行一遍。从 MySQL5.0 开始支持。
- 混合类型的复制:默认采用基于语句的复制,一旦发现基于语句的无法 精确的复制时,就会采用基于行的复制。
3、启动/重启 Master 数据库服务,登录数据库,创建数据同步用户,并授予相应的权限
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
[root@edu-mysql-01 ~]
# service mysql restart
Shutting down MySQL..[ OK ] Starting MySQL..[ OK ]
[root@edu-mysql-01 ~]
# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection
id
is 1
Server version: 5.6.26-log Source distribution
Copyright (c) 2000, 2015, Oracle and
/or
its affiliates. All rights reser ved.
Oracle is a registered trademark of Oracle Corporation and
/or
its affiliates. Other names may be trademarks of their respective owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear
the current input stat ement.
#创建数据同步用户,并授予相应的权限
mysql> grant replication slave, replication client on *.* to
'repl'
@
'192. 168.31.59'
identified by
'123456'
;
Query OK, 0 rows affected (0.00 sec)
# 刷新授权表信息
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
# 查看 MySQL 现在有哪些用户及对应的 IP 权限
mysql>
select
user,host from mysql.user;
# 查看 position 号,记下 position 号(从机上需要用到这个 position 号和现 在的日志文件)
mysql> show master status;
Master 重启后会修改 mysql-bin 文件名(序号加 1)
4、接下来处理 Slave(192.168.31.59),配置文件只需修改一项,其余配 置用命令来操作
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
[root@edu-mysql-02 ~]
# vi /etc/my.cnf
## 在 [mysqld] 中增加以下配置项
# 设置 server_id,一般设置为 IP
server_id=59
# 复制过滤:需要备份的数据库,输出 binlog
binlog-
do
-db=mbank
#复制过滤:不需要备份的数据库,不输出(mysql 库一般不同步)
binlog-ignore-db=mysql
# 开启二进制日志,以备 Slave 作为其它 Slave 的 Master 时使用
log-bin=edu-mysql-slave1-bin
# 为每个 session 分配的内存,在事务过程中用来存储二进制日志的缓存
binlog_cache_size = 1M
# 主从复制的格式(mixed,statement,row,默认格式是 statement)
binlog_format=mixed
# 二进制日志自动删除/过期的天数。默认值为 0,表示不自动删除。
expire_logs_days=7
# 跳过主从复制中遇到的所有错误或指定类型的错误,避免 slave 端复制中 断。
# 如:1062 错误是指一些主键重复,1032 错误是因为主从数据库数据不一 致
slave_skip_errors=1062
# relay_log 配置中继日志
relay_log=edu-mysql-relay-bin
# log_slave_updates 表示 slave 将复制事件写进自己的二进制日志
log_slave_updates=1
# 防止改变数据(除了特殊的线程)
read_only=1
如果 Slave 为其它 Slave 的 Master 时,必须设置 bin_log。在这里,我们 开启了二进制日志,而且显式的命名(默认名称为 hostname,但是,如果 h ostname 改变则会出现问题)。
relay_log 配置中继日志,log_slave_updates 表示 slave 将复制事件写进自 己的二进制日志。
当设置 log_slave_updates 时,你可以让 slave 扮演其它 slave 的 master。 此时,slave 把 SQL 线程执行的事件写进行自己的二进制日志 (binary log), 然后,它的 slave 可以获取这些事件并执行它.
5、保存后重启 MySQL 服务,还原备份数据
1
2
[root@edu-mysql-02 ~]
# service mysql restart
Shutting down MySQL..[ OK ] Starting MySQL..[ OK ]
6、登录 Slave 数据库,添加相关参数
###Master 的 IP、端口、同步用户、密码、position 号、读取哪个日志文件
1
2
3
4
5
6
7
8
9
[root@edu-mysql-02 ~]
# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection
id
is 3
Server version: 5.6.26-log Source distribution
Copyright (c) 2000, 2015, Oracle and
/or
its affiliates. All rights rese rved.
Oracle is a registered trademark of Oracle Corporation and
/or
its affiliates. Other names may be trademarks of their respective owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear
the current input stat ement.
mysql> change master to master_host=
'192.168.31.57'
, master_user=
' repl'
, master_password=
'123456'
, master_port=3306, master_log_file=
' edu-mysql-bin.000001'
, master_log_pos=429, master_connect_retry=3 0;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
上面执行的命令的解释:
master_host='192.168.31.57' ## Master 的 IP 地址
master_user='repl' ## 用于同步数据的用户(在 Master 中授权的用户)
master_password='123456' ## 同步数据用户的密码
master_port=3306 ## Master 数据库服务的端口
master_log_file='edu-mysql-bin.000001' ##指定 Slave 从哪个日志文 件开始读复制数据(可在 Master 上使用 show master status 查看到日志 文件名)
master_log_pos=429 ## 从哪个 POSITION 号开始读
master_connect_retry=30 ##当重新建立主从连接时,如果连接 建立失败,间隔多久后重试。单位为秒,默认设置为 60 秒,同步延迟调优 参数。
## 查看主从同步状态
1
mysql> show slave status\G;
可看到 Slave_IO_State 为空, Slave_IO_Running 和 Slave_SQL_Runnin g 是 No,表明 Slave 还没有开始复制过程。
## 开启主从同步
1
2
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
## 再查看主从同步状态
1
mysql> show slave status\G;
主要看以下两个参数,这两个参数如果是 Yes 就表示主从同步正常
Slave_IO_Running: Yes Slave_SQL_Running: Yes
由截图中的主从同步状态信息可以看出,我们配置的主从同步是正常的。
可查看 master 和 slave 上线程的状态。在 master 上,可以看到 slave 的 I/O 线程创建的连接:
Master : mysql> show processlist\G;
(1)row 为处理 slave 的 I/O 线程的连接。
(2)row 为处理 MySQL 客户端连接线程。
(3)row 为处理本地命令行的线程。
Slave :
1
mysql> show processlist\G;
(1) row 为 I/O 线程状态。
(2) row 为 SQL 线程状态。
(3) row 为处理本地命令行的线程。
7、主从数据复制同步测试
(1) 在 Master 中的 mbank 库上变更数据的同步测试;
1
mysql> INSERT INTO `testuser`(`usercode`,`username`) VALUES (`1`,
'同步测试 1'
),( `2`,
'同步测试 2'
);
Master 中添加完之后,登录 Slave 中查看数据是否已同步。
8、测试过程中,如果遇到同步出错,可在 Slave 上重置主从复制设置(选 操作):
1
2
3
4
mysql> reset slave;
mysql> change master to master_host=
'192.168.31.57'
, master_user=
'repl'
,
master_password=
'123456'
, master_port=3306,
master_log_file=
'edu-mysql-bin.00000x'
, master_log_pos=xx, master_connect_retry=30;
(此时,master_log_file 和 master_log_pos 要在 Master 中用 show master status 命令查看)
注意:如果在 Slave 没做只读控制的情况下,千万不要在 Slave 中手动插 入数据,那样数据就会不一致,主从就会断开,就需要重新配置了。
上面所搭建的是单向复制的主从,也是用的比较多的,而双向主从其实 就是 Master 和 Slave 都开启日志功能,然后在 Master 执行授权用户
(这 里授权的是自己作为从服务器,也就是这里的 IP 地址是 Master 的 IP 地址), 然后再在 Master 上进行 chang master 操作。
基于局域网的 Master/Slave 机制在通常情况下已经可以满足“实时”备份 的要求了。如果延迟比较大,可以从以下几个因素进行排查:
- 网络延迟;
- Master 负载过高;
- Slave 负载过高;
一般的做法是使用多台 Slave 来分摊读请求,再单独配置一台 Slave 只作 为备份用,不进行其他任何操作,就能相对最大限度地达到“实时”的要 求了。
我在部署的时候遇到的问题: 由于我的从服务器是主服务器复制过来的,mysql 的 server_id 一样,在同步的时候出错了:
Fatal error: The slave I/O thread stops because master and slave have equal MySQ L server UUIDs; these UUIDs must be different for replication to work.
(1)首先检查:
1
2
3
4
5
6
mysql> show variables like ‘server_id';
+—————+——-+
| Variable_name | Value |
+—————+——-+
| server_id | 3 |
+—————+——-+
主从不能不一样。
(2)如果还不行,继续排查。进入 mysql 的 data 目录,打开 auto.cnf 文件,里面记录了 数据库的 uuid,每个库的 uuid 应该是不一样的。
[auto]
server-uuid=6dcee5be-8cdb-11e2-9408-90e2ba2e2ea6
解决办法,按照这个 16 进制格式,随便改下,重启 mysql 即可。