MySQL5.6主从复制最佳实践

2023-08-24 16:38:40

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 的配置文件

/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 数据库服务，登录数据库，创建数据同步用户，并授予相应的权限

[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），配置文件只需修改一项，其余配置用命令来操作

[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 服务，还原备份数据

[root@edu-mysql-02  ~]# service  mysql  restart

 Shutting  down  MySQL..[  OK  ] Starting  MySQL..[  OK  ]

6、登录 Slave 数据库，添加相关参数

###Master 的 IP、端口、同步用户、密码、position 号、读取哪个日志文件

[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 秒，同步延迟调优参数。

## 查看主从同步状态

mysql> show  slave  status\G;

可看到 Slave_IO_State 为空， Slave_IO_Running 和 Slave_SQL_Runnin g 是 No，表明 Slave 还没有开始复制过程。

## 开启主从同步

mysql> start  slave;

Query  OK,  0  rows  affected  (0.00  sec)

## 再查看主从同步状态

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 :

mysql> show  processlist\G;

　 (1) row 为 I/O 线程状态。

　(2) row 为 SQL 线程状态。

(3) row 为处理本地命令行的线程。

7、主从数据复制同步测试

(1) 在 Master 中的 mbank 库上变更数据的同步测试；

mysql> INSERT  INTO  `testuser`(`usercode`,`username`)  VALUES  (`1`, '同步测试 1'),(  `2`,'同步测试 2');

Master 中添加完之后，登录 Slave 中查看数据是否已同步。

8、测试过程中，如果遇到同步出错，可在 Slave 上重置主从复制设置（选操作）：

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）首先检查：

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 即可。

码农公寓

相关文章