mysql的主从复制
MySQL之间数据复制的基础是二进制日志文件(binary log file)。一台MySQL数据库一旦启用二进制日志后,其作为master,它的数据库中所有操作都会以“事件”的方式记录在二进制日志中,其他数据库 作为slave通过一个I/O线程与主服务器保持通信,并监控master的二进制日志文件的变化,如果发现master二进制日志文件发生变化,则会把 变化复制到自己的中继日志中,然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中,以此实现从数据库和主数据库的一致性,也就实现了 主从复制。
实验环境:
Linux系统版本:Redhat 6.5版本
系统防火墙关闭:selinux=disabled
server2:172.25.68.2(Master)
server3:172.25.68.3(Slave)
1.master数据库的搭建
[root@server2 ~]# tar xf mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar
[root@server2 ~]# yum install mysql-community-client-5.7.17-1.el6.x86_64.rpm mysql-community-common-5.7.17-1.el6.x86_64.rpm mysql-community-libs-5.7.17-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm mysql-community-server-5.7.17-1.el6.x86_64.rpm -y
[root@server2 ~]# /etc/init.d/mysqld start ##开启数据库
[root@server2 ~]# grep password /var/log/mysqld.log ##查找数据库的原始密码
[root@server2 ~]# mysql_secure_installation ##初始化数据库,密码设定为WESTOS.com123
[root@server2 ~]# vim /etc/my.cnf ##server-id为服务器的默认id,master和slave不能相同,log-bin表示二进制日志
[root@server2 ~]# /etc/init.d/mysqld restart
[root@server2 ~]# mysql -p
可以看出,master的id为154
mysql> grant replication slave on *.* to repl@'172.25.68.%' identified by 'WESTOS.com123'; ##授权slave以repl身份从172.25.68.0/24网段登陆,对任何数据库的任何表进行操作,密码‘WESTOS.com123‘
授权后,id为447
2.slave数据库的搭建
[root@server3 ~]# tar xf mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar
[root@server3 ~]# yum install mysql-community-client-5.7.17-1.el6.x86_64.rpm mysql-community-common-5.7.17-1.el6.x86_64.rpm mysql-community-libs-5.7.17-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm mysql-community-server-5.7.17-1.el6.x86_64.rpm -y
[root@server3 ~]# /etc/init.d/mysqld start ##开启数据库
[root@server3 ~]# grep password /var/log/mysqld.log ##查找数据库的原始密码
[root@server3 ~]# mysql_secure_installation ##初始化数据库,密码设定为WESTOS.com123
[root@server3 ~]# vim /etc/my.cnf
[root@server3 ~]# /etc/init.d/mysqld restart
[root@server3 ~]# mysql -p
mysql> change master to master_host='172.25.68.2',master_user='repl',master_password='WESTOS.com123',master_log_file='mysql-bin.000001',master_log_pos=447;
mysql> start slave;
mysql> show slave status\G; ##开启slave,并查看slave状态
IO线程和sql线程已开启
创建westos库,在westos库中创建表linux并写入数据
查看linux表中信息
在slave中登陆mysql,查看自动同步过来的linux表中的信息
3.GTID主从复制
1.配置master
[root@server2 ~]# vim /etc/my.cnf ##添加的内容表示开启gtid模块
[root@server2 ~]# /etc/init.d/mysqld restart
[root@server2 ~]# mysql -p
2.配置slave
[root@server3 ~]# vim /etc/my.cnf
[root@server3 ~]# /etc/init.d/mysqld restart
[root@server3 ~]# mysql -p
测试:
在master的westos库中的linux表中写入数据
在slave中查看同步到的数据
4.半同步复制
异步复制(Asynchronous replication)
MySQL默认的复制即是异步的,主库在执行完客户端提交的事务后会立即将结果返给给客户端,并不关心从库是否已经接收并处理,这样就会有一个问题,主如果crash掉了,此时主上已经提交的事务可能并没有传到从上,如果此时,强行将从提升为主,可能导致新主上的数据不完整。
全同步复制(Fully synchronous replication)
指当主库执行完一个事务,所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回,所以全同步复制的性能必然会收到严重的影响。
半同步复制(Semisynchronous replication)
介于异步复制和全同步复制之间,主库在执行完客户端提交的事务后不是立刻返回给客户端,而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制,半同步复制提高了数据的安全性,同时它也造成了一定程度的延迟,这个延迟最少是一个TCP/IP往返的时间。所以,半同步复制最好在低延时的网络中使用。
1.master配置
[root@server2 ~]# mysql -p ##安装半同步模块
以上设定为临时的,永久设定如下:
[root@server2 ~]# vim /etc/my.cnf
[root@server2 ~]# /etc/init.d/mysqld restart
2.slave配置
[root@server3 ~]# mysql -p ##安装半同步模块并启动
重新连接主服务器(半同步才会生效)
3.server2(slave)配置:配置同server2相同
Master上查看是否启用了半同步:
主要看Rpl_semi_sync_master_clients是否为1,Rpl_semi_sync_master_status是否为ON。若符合则表示班同步复制机制已经正常工作,如果Rpl_semi_sync_master_status为OFF,说明出现了网络延迟或Slave IO线程延迟。
延时测试:
关掉server2的IO线程
在server1写入数据
在server2和3查看是否同步
未同步过来,因为设置的延时为10秒,当server1等待10秒后,若还没有反应则会转成异步模式
开启server2的io线程
server3又自动同步过来
当server2的IO线程关闭后,server2和server3的数据都不会与server1同步
只关闭server3的IO线程后,只有server3与server1不同步
5.并行复制
思考:
一般主从复制,有三个线程参与,都是单线程:Binlog Dump(主) ----->IO Thread (从) -----> SQL Thread(从)。复制出现延迟一般出在两个地方
1)SQL线程忙不过来(可能需要应用数据量较大,可能和从库本身的一些操作有锁和资源的冲突;主库可以并发写,SQL线程不可以;主要原因)
2)网络抖动导致IO线程复制延迟(次要原因)。
解决办法:
MySQL从5.6开始有了SQL Thread多个的概念,可以并发还原数据,即并行复制技术。
MySQL 5.6中,设置参数slave_parallel_workers = 4(>1),即可有4个SQL Thread(coordinator线程)来进行并行复制,其状态为:Waiting for an evant from Coordinator。
但是其并行只是基于Schema的,也就是基于库的。如果数据库实例中存在多个Schema,这样设置对于Slave复制的速度可以有比较大的提升。通常情况下单库多表是更常见的一种情形,
那基于库的并发就没有卵用。其核心思想是:不同schema下的表并发提交时的数据不会相互影响,即slave节点可以用对relay log中不同的schema各分配一个类似SQL功能的线程,
来重放relay log中主库已经提交的事务,保持数据与主库一致。
在MySQL 5.7中,引入了基于组提交的并行复制(Enhanced Multi-threaded Slaves),设置参数slave_parallel_workers>0并且 global.slave_parallel_type=‘LOGICAL_CLOCK’,
即可支持一个schema下,slave_parallel_workers个的worker线程并发执行relay log中主库提交的事务。其核心思想:一个组提交的事务都是可以并行回放(配合binary log group commit);
slave机器的relay log中 last_committed相同的事务(sequence_num不同)可以并发执行。
[root@server2 ~]# vim /etc/my.cnf
[root@server2 ~]# /etc/init.d/mysqld restart
[root@server2 ~]# mysql -p
其状态:Waiting for an evant from Coordinator已经存在,说明并行复制已经配置成功!
6.断电后数据库的恢复
mysql> show binlog events\G可以查看到binlog和pos点
方案一:
mysqldump -p test > test.sql ## 将库的内容导入到test.sql文件中
scp test.sql server3: ##传到server3
mysql -p < test.sql ##在server3上恢复test库
ps:这种方法会DROP之前的内容重新建,可能会导致信息丢失
方案二:
mysqlbinlog 命令,可以使用start time 和stop time进行限定,或--start-position
--help查看用法