【MySQL】MySQL5.6数据库基于binlog主从(Master/Slave)同步安装与配置详解

主从数据库同步原理

image.png

主从数据库同步工作原理(流程):
当主库的数据发生修改时,数据更改的记录将写入到主库的二进制文件中,从库此时将会调用一个IO线程读取主库的二进制文件,并与中继日志作对比,并将存在差异的事件写入到中继日志中(当两日志内容事件一致时,IO线程将处于睡眠状态),然后从库再调用SQL线程去读取中继日志,并将刚写入的事件数据放入到从库中以保持主从数据库数据同步。

配置步骤:

安装环境:

操作系统:Cent0S 6.9
数据库版本:MySQL 5.6
主机A:192.168.206.134(Master)
主机B:192.168.206.201(Slave)

首先,需要注意几点问题:

1、互相同步的两台mysql的版本必须保证大版本号一致。比如5.5+和5.6+之间同步数据,5.6的数据同步到5.5就会出现问题。保证大版本号一致很重要。
2、每台服务器必须开启binlog,不开启binlog则根本无法开始数据同步。
3、每台服务器必须配置不同的server-id,范围在1到(2^32-1)之间。

以上三点都可能造成很多奇怪错误,请一定注意。

这里强调的数据库的版本,是因为MySQL在5.6之前和之后的配置方式是不一样的,后面将会提示到。

首先,要保证防火墙对3306端口的开启,为了学习数据库的主从配置,可以使用service iptables stop 命令直接关闭防火墙。

#service iptables stop

然后可以在两台机子之间进行 ping操作,确保两台机器之间能够想通

Master部分的配置

在Linux环境下MySQL的配置文件的位置是在 /etc/my.cnf
,在该文件下指定Master的完整配置如下:

mastercnf文件1.png
mastercnf文件2.png

其中,server-id用于标识唯一的数据库,取值为1到(2^32-1)
binlog-ignore-db:表示不需要同步的数据库
binlog-do-db:表示需要同步的数据库

然后重启MySQL

service mysqld restart

输入用户名密码进入MySQL

#mysql -uroot -p

赋予从库权限帐号,允许用户在主库上读取日志,赋予192.168.206.201也就是Slave机器有File权限,只赋予Slave机器有File权限还不行,还要给它REPLICATION SLAVE的权限才可以
在Master数据库命令行中输入:

mysql>GRANT FILE ON *.* TO '数据库用户名'@'Slave地址' IDENTIFIED BY '数据库登录密码';//赋予File权限格式
 mysql>GRANT FILE ON *.* TO 'root'@'192.168.206.201' IDENTIFIED BY 'mysql password';//赋予File权限
mysql>GRANT REPLICATION SLAVE ON *.* TO '数据库用户名'@‘Slave地址’ IDENTIFIED BY '数据库登录密码';//赋予REPLICATION SLAVE权限格式
 mysql>GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.206.201' IDENTIFIED BY 'mysql password';//赋予REPLICATION SLAVE权限
mysql>FLUSH PRIVILEGES;//刷新权限

重启mysql,登录mysql,显示主库信息:

mysql>show master status;
3查看master库状态.png

这里的File、Position是在后面配置Slave的时候需要用到的,Binlog_Do_DB表示需要同步的数据库,Binlog_Ignore_DB 表示Ignore的数据库

另外提示:如果执行这个步骤始终为Empty set(0.00 sec),那说明前面的my.cnf没配置对,需要仔细检查。

Slave部分的配置

与Master的配置一样,首先需要配置my.cnf文件,如下

4slave配置文件1.png
5slave配置文件2.png

相关的字段的含义可以参考另一篇文章:http://www.jianshu.com/p/ac8b0892d621

在配置文件中,MySQL5.6之后的版本中没有指定:

master-host=192.168.206.134 #Master的主机IP
master-user=root
master-password=mysql password #Master的MySQL密码

新版本的配置这种方式是不适用的。如果,在MySQL5.6和之后的版本中配置从库的时候,设置到了上边的内容,即指定了master-host、master-user等信息的话,重启MySQL的时候就会报错,查看数据库的报错信息(数据库的目录, /data/mysqldb/VM_128_194_centos.err ),可以看到master-host 被检测数是一个未知的变量,因此会出现错误

在5.6以及后续版本的配置如下:
修改完/etc/my.cnf 文件之后,重启一下MySQL

service mysqld restart

登录进入mysql控制台

#mysql -uroot -p

在控制台输入

mysql> stop slave;  //关闭Slave
mysql> change master to master_host='192.168.206.134',master_user='root',master_password='123456',master_log_file='mysql-bin.000003',  master_log_pos=120;//指定master信息 
mysql> start slave;  //开启Slave

在这里指定Master的信息,master_log_file是在配置Master的时候的File选项, master_log_pos是在配置Master的Position 选项,这里要进行对应。
更多关于change master语句的信息可参考:http://www.jianshu.com/p/ada9f34d8563
然后可以通过mysql> show slave status \G; 查看配置的信息:

6slave状态文件.png

7slave状态文件.png

在途中的Slave_IO_Running和Slave_SQL_Running都为yes,那么表示配置成功
各个字段含义可参考这篇文章:http://www.jianshu.com/p/3c4d7c6c6205

当完成配置后
查看master的状态:

mysql >show master status;  //Position不应该为0
mysql>show processlist;  

//state状态应该为Has sent all binlog to slave; waiting for binlog to be updated

image.png

查看slave状态:

mysql>show slave status;

//Slave_IO_Running 与 Slave_SQL_Running 状态都要为Yes

mysql>show processlist;

//应该有两行state值为:
Has read all relay log; waiting for the slave I/O thread to update it
Waiting for master to send event

image.png

下面开始真机测试:
测试环境:windows10+Navicat11
以下测试是使用真机连接虚拟机中的主从库
首先在Navicat中创建连接(物理机连接虚拟机的方法参考:http://www.jianshu.com/p/fa4db03db9ca):

7创建连接.png

7创建连接2.png

连接的地址为虚拟机中主/从库的地址
连接上去之后,分别在Master和Slave中创建数据库,该数据库名字为刚刚配置的需要同步的数据库名字
接着开始在该数据库中新建数据表
首先在主库中创建


9刷新从表之后从表数据表自动创建.png

创建好之后右键刷新从库,从库自动创建表
8Navicat中新建数据表.png

同样的,在主表添加数据


10主表添加数据.png

添加完数据后有点刷新从表,从表的数据也自动刷新

以上是一个完整的配置过程,是查找过多篇资料和通过实践之后的总结。
同时感谢启发我的两个作者:
http://blog.csdn.net/xlgen157387/article/details/51331244/
http://www.jianshu.com/p/67d8355315c6

另:
从主服务器得到一个快照版本

如果你的是MYISAM或者既有MYISAM又有INNODB的话就在主服务器上使用如下命令导出服务器的一个快照:
mysqldump -uroot -p --lock-tables --events --triggers --routines --flush-logs --master-data=2 --databases test > db.sql
试过只有INNODB的话就是用如下命令:
mysqldump -uroot -p --single-transaction --events --triggers --routines --flush-logs --master-data=2 --databases test > db.sql

将快照版本还原到从服务器上
mysqldump -uroot -p -h 10.1.1.76 test < db.sql

关于一些常见问题及解决方案
一、主从数据库不同步问题

可以先查看一下进程是否sleep太多

mysql> show processlist;

然后可以看看master的状态

mysql>show master status;

如果都为正常,那就到slave上看看状态

mysql>show slave status \G;

1、可能slave服务未开启
数据库控制台输入show slave status \G;打印出slave的状态

mysql> show slave status \G;

主要看slave_IO_Running和Slave_SQL_Running,结果都需要为yes,如果不为yes,需要重启slave服务

mysql> service slave restart;//重启

如果服务未开启,则开启:

mysql> service slave start;

2、从数据库连接失败
在正常情况下,配置好服务器后,从库不进行修改操作,即从库只有读的权限,如果修改了从库数据表,则会造成同步失败,如发现失败,用show slave status \G;查看服务器状态信息:

mysql> show slave status \G;

查看Slave_SQL_Running状态,如果出现slave_SQL_Running为NO时,表示从库连接失败,在Last_Error处会打印错误日志信息。出现该问题主要是事务回滚问题,解决方法有两种。
1、

mysql> slave stop;//停止从服务
mysql>set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;//不要滥用
mysql>slave start;//启动从服务

2、手动重设从服务器

mysql5.6之后的版本(包含5.6)
mysql> stop slave; #关闭Slavemysql> change master to master_host='主服务器IP地址',master_user='主库用户名',master_password='主库登录密码',
master_log_file='mysql-bin.000004', master_log_pos=28125;//master_log_file是在配置master的时候的file选项,master_log_pos是在配置master时候position选项的  mysql> show master status;可查看
mysql> start slave;  #开启Slave
mysql5.6之前的版本:

mysql>change master to
master_host='主库IP地址',
master_user='主库用户名',
master_password='主库登录密码',
master_log_file='主库文件信息',//同上
master_log_pos=' 主库的position信息 ';//同上

如果数据库相差较大,或者要求数据完全统一的情况:
1、先进入主库,进行锁表,防止数据写入

mysql>flush tables with read lock;//此处是锁定为只读状态

2、进行数据备份
将数据备份到mysql.bak.sql文件

# mysqldump -uroot -p -hlocalhost > mysql.bak.sql

3.查看master 状态

mysql> show master status;
+-------------------+----------+--------------+-------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB              |
+-------------------+----------+--------------+-------------------------------+
| mysqld-bin.000001 |     3260 |              | mysql,test,information_schema |
+-------------------+----------+--------------+-------------------------------+
1 row in set (0.00 sec)

4.把mysql备份文件传到从库机器,进行数据恢复

使用scp命令

[root@server01 mysql]# scp mysql.bak.sql root@192.168.128.101:/tmp/

5.停止从库的状态

mysql> stop slave;

6.然后到从库执行mysql命令,导入数据备份

mysql> source /tmp/mysql.bak.sql

7.设置从库同步,注意该处的同步点,就是主库show master status信息里的| File| Position两项

#change master to master_host = '192.168.128.100', master_user = 'rsync', master_port=3306, master_password='', master_log_file = 'mysqld-bin.000001', master_log_pos=3260;

8.重新开启从同步

mysql> start slave;

9.查看同步状态

mysql> show slave status;  查看:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
二、主从复制,中继日志不断增长

解决方案:
设置中继日志自动删除
vi 配置文件my.cnf,在mysqld下添加(位置:/etc/my.cnf)
relay_log-purge=1//(自动清除中继日志打开)
然后重启MySQL, 这样SQL Thread每执行完一个events时才会判断该relay-log是否需要,已经不再需要则自动删除

三、主从同步失败,如何快速同步

跳过错误,继续同步。设置SQL_slave_skip_counter=1;来快速恢复主从架构,但是此时主从架构的数据可能已经不一致了。set global sql_slave_skip_counter=N; 当N等于1时,表示跳过若干个event,直到当前事务结束,而当N大于1时,每跳过一个event,都要N--设置--slave-skip-errors=[ err_code1[,err_code2][,all]] 跳过出现指定错误的SQL.如果要断开主从架构,应先stop slave io_thread;等待执行完relay log里的内容再stop slave;
如果有与字符编码问题:
先停止slave

mysql>stop slave;

跳过slave上的一个错误:

mysql>set global sql_slave_skip_counter=1;//不要滥用,用之前最好上网查找资料

启动slave

mysql>start slave;

使用此方法需要注意的问题:

  1. 检查跳过的event是否在一个事物中
  2. 跳过slave上的event进行后续处理后要检查数据的一致性。
  3. 最好能在master的binglog上查看一下跳过的evnet到底做了写什么。
四、IO线程(Slave_IO_Running)始终保持为connecting状态

主从架构中,从库的io_thread一直保持connecting状态。先理解Slave_IO_Running 为connecting,的含义。造成的主要有三个:
1、网络不通 (是否打开防火墙)
2、复制用户的密码不对 (主从同步指定的用户密码主机名限制)
3、pos不对 (指定的position不正确

五、主键冲突,报1062错误

主从架构中,从库复制报1062错误,主键冲突。如果binlog是基于语句级复制,很容易出现上面的问题。设置innodb_autoincr_lock_mode=0或是1或修改binlog_format=mixed|row

六、从库同步慢

主从架构中,从库的同步数据非常慢。出现主从同步慢的原因有:

  1. 主从同步延迟与系统时间的关系,查看主从两台机器间系统时间差
  2. 主从同步延迟与压力、网络、机器性能的关系,查看从库的io,cpu,mem及网络压力
  3. 主从同步延迟与lock锁的关系(myisam表读时会堵塞写),尽量避免使用myisam表。一个实例里面尽量减少数据库的数量。
七、change master时报错ERROR 1201(HY000)

表现:在搭建主从时,报1201错误 。ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log
解决方法:出现这个问题的原因是之前曾做过主从复制!需要reset slave后再change

八、关于在主从的时候使用触发器的问题

1 主从都存在trigger时,主库会记录sql语句,不包含trigger的操作,从库上数据和主库一致..
2 主有trigger,从库上没有trigger时,从库上没有trigger时,触发器不会被执行
3 主上无trigger,从上有trigger时 ,主从数据不一致,从库上的trigger被触发

后记:
关于配置时导数据:
http://www.jianshu.com/p/5233202ff307
关于SECONDS_BEHIND_MASTER:
http://www.jianshu.com/p/56fad1fbe08a
需要注意的一些问题和一些常见故障(先贴出参考,后面有空再整理总结)
http://www.cnblogs.com/wangxin37/p/6398755.html
http://www.educity.cn/shujuku/1178774.html
https://www.douban.com/note/354334490/
http://www.3lian.com/edu/2014/01-23/126344.html

推荐阅读更多精彩内容