MySQL5.6主从复制最佳实践

Wesley13
• 阅读 607

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 复制)的原理

  1.  Master 将数据改变记录到二进制日志(binary  log)中,也就是配置文件 log-bin 指定的文件,这些记录叫做二进制日志事件(binary  log  events);
  2.  Slave 通过 I/O 线程读取 Master 中的 binary  log  events 并写入到它的 中继日志(relay  log);
  3.  Slave 重做中继日志中的事件,把中继日志中的事件信息一条一条的在 本地执行一次,完成数据在本地的存储,从而实现将改变反映到它自己的 数据(数据重放)。

主从配置需要注意的点

  1. 主从服务器操作系统版本和位数一致;主从服务器的 hostname 不要一致。
  2. Master 和 Slave 数据库的版本要一致;
  3. Master 和 Slave 数据库中的数据要一致;
  4. Master 开启二进制日志,Master 和 Slave 的 server_id 在局域网内必 须唯一;
  5. 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 复制过滤可以让你只复制服务器中的一部分数据,有两种复制过滤:

  1. 在 Master 上过滤二进制日志中的事件;
  2. 在 Slave 上过滤中继日志中的事件。

2.2    MySQL 对于二进制日志 (binlog)的复制类型

  1. 基于语句的复制:在 Master 上执行的 SQL 语句,在 Slave 上执行同样的语句。MySQL 默认采用基于语句的复制,效率比较高。一旦发现没法精 确复制时,会自动选着基于行的复制。
  2. 基于行的复制:把改变的内容复制到 Slave,而不是把命令在 Slave 上 执行一遍。从 MySQL5.0 开始支持。
  3. 混合类型的复制:默认采用基于语句的复制,一旦发现基于语句的无法 精确的复制时,就会采用基于行的复制。

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 机制在通常情况下已经可以满足“实时”备份 的要求了。如果延迟比较大,可以从以下几个因素进行排查:

  1. 网络延迟;
  2. Master 负载过高;
  3. 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 即可。

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
待兔 待兔
4个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Stella981 Stella981
3年前
Android蓝牙连接汽车OBD设备
//设备连接public class BluetoothConnect implements Runnable {    private static final UUID CONNECT_UUID  UUID.fromString("0000110100001000800000805F9B34FB");
Stella981 Stella981
3年前
Django中Admin中的一些参数配置
设置在列表中显示的字段,id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
9个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这