mysql主从复制

原理

​ MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。

​ MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

​ （1）master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中；

​ （2）slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件

​ （3）同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒。

也就是说：

• 从库会生成两个线程,一个I/O线程,一个SQL线程;
• I/O线程会去请求主库的binlog,并将得到的binlog写到本地的relay-log(中继日志)文件中;
• 主库会生成一个log dump线程,用来给从库I/O线程传binlog;
• SQL线程,会读取relay log文件中的日志,并解析成sql语句逐一执行;

注意：

• master将操作语句记录到binlog日志中，然后授予slave远程连接的权限（master一定要开启binlog二进制日志功能；通常为了数据安全考虑，slave也开启binlog功能）。
• slave开启两个线程：IO线程和SQL线程。其中：IO线程负责读取master的binlog内容到中继日志relay log里；SQL线程负责从relay log日志里读出binlog内容，并更新到slave的数据库里，这样就能保证slave数据和master数据保持一致了。
• Mysql复制至少需要两个Mysql的服务，当然Mysql服务可以分布在不同的服务器上，也可以在一台服务器上启动多个服务。
• Mysql复制最好确保master和slave服务器上的Mysql版本相同（如果不能满足版本一致，那么要保证master主节点的版本低于slave从节点的版本）
• master和slave两节点间时间需同步

具体步骤：

1、从库通过手工执行change master to 语句连接主库，提供了连接的用户一切条件（user 、password、port、ip），并且让从库知道，二进制日志的起点位置（file名 position 号）； start slave

2、从库的IO线程和主库的dump线程建立连接。

3、从库根据change master to 语句提供的file名和position号，IO线程向主库发起binlog的请求。

4、主库dump线程根据从库的请求，将本地binlog以events的方式发给从库IO线程。

5、从库IO线程接收binlog events，并存放到本地relay-log中，传送过来的信息，会记录到master.info中

6、从库SQL线程应用relay-log，并且把应用过的记录到relay-log.info中，默认情况下，已经应用过的relay 会自动被清理purge

---

主从同步延迟

延迟问题请点击跳转主从复制延迟专题

一主一从

修改配置

修改master配置文件vi /etc/my.cnf

#mysql服务唯一id，不同的mysql服务必须拥有全局唯一的id
server-id=1
#启动二进制日志
log-bin=mysql-bin
#设置不要复制的数据库
binlog-ignore-db=mysql
binlog-ignore-db=information-schema
#设置需要复制的数据库
binlog-do-db=mytest
#设置binlog的格式row（在binlog里面记录在哪个行上做了修改，逻辑语法）、statement（保存操作时的语句）、mixed（两种方式混合使用）
binlog_format=statement

修改slave配置文件vi /etc/my.cnf

#服务器唯一id
server-id=2
#启动中继日志
relay-log=mysql-relay

重启服务

重启master和slave的mysql服务

service mysql restart

授权账户

登录master的mysql服务，授权账户主从复制权限

-- 授权主从复制权限
grant replication slave on *.* to 'root'@'%' identified by 'Root_123';
-- 在进行授权的时候，如果提示密码的问题，把密码验证取消
set global validate_password_policy=0;
set global validate_password_length=1;

查看master状态

登录master的mysql服务中，执行show master status查询master状态

File和Position的值在下面的slave服务中会使用到；

可以通过start master、stop master、reset master等命令操作master，但是操作之后的master状态值可能发生变化

slave执行主从复制命令

登录slave的mysql服务中，执行以下命令

CHANGE MASTER TO MASTER_HOST='192.168.243.131',MASTER_USER='root',MASTER_PASSWORD='Root_123',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154;

查看slave状态

在slave的mysql服务中执行show slave status\G查看slave状态

这时可以看到Slave_IO_Running，Slave_SQL_Running这两个属性都是NO

启动slave

此时需要执行start slave命令，启动slave

当看到Slave_IO_Running，Slave_SQL_Running这两个属性都是yes的时候，表示主从复制已经准备好了，可以进行具体的操作了

关于slave，也可以通过start slave、stop slave、reset slave等命令操作slave

建库同步

-- 分别在master和slave查看数据库，都没有mytest这个库
-- 在master执行建库语句
create database mytest;

此时可以看到右边的slave出现了在master中创建的mytest库

建表同步

-- 分别在master和slave中进入mytest数据库，查看表格，都没有t1这个表
-- 在master执行建表语句
create table t1(id int,name varchar(10));

此时可以看到右边的slave出现了在master中创建的t1表

语句同步

-- 分别在master和slave中进入mytest数据库，查看t1表格数据，都为空
-- 在master执行insert语句
insert into t1 values(1,'zhangsan');

此时可以看到右边的slave出现了在master中执行insert命令插入的数据

注意

当master执行的语句被同步到slave时，如果slave存在对应的语句在约束上与master同步过来的语句冲突时，同步会失败，在/var/log/mysqld.log中可以看到错误。

所以一般来说从库不允许写入，只能读取。

---

多主多从

​ 在上述的一主一从的架构设计中，很容易出现单点的问题，所以我们要想让生产环境中的配置足够稳定，可以配置双主双从，解决单点的问题。

在此架构中，可以让一台主机用来处理所有写请求，此时，它的从机和备机，以及备机的从机复制所有读请求，当主机宕机之后，另一台主机负责写请求，两台主机互为备机。

主机分布如下：

编号	角色	ip	主机名
1	master-1	192.168.243.131	centos-1
2	slave-1	192.168.243.132	centos-2
3	master-2	192.168.243.133	centos-3
4	slave-2	192.168.243.134	centos-4

master-1与slave-1为主从关系、master-2与slave-2为主从关系、master-1与master-2互为主备关系

master-1

修改配置vi /etc/my.cnf

#主服务器唯一ID
server-id=1
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=my_multi_test
#设置binlog的格式row（生产推荐使用，在binlog里面记录在哪个行上做了修改，逻辑语法）、statement（保存操作时的语句）、mixed（两种方式混合使用）
binlog_format=STATEMENT
# 在作为从数据库的时候， 有写入操作也要更新二进制日志文件
log-slave-updates
#表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1， 取值范围是1 .. 65535
auto-increment-increment=2
# 表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535
auto-increment-offset=1

master-2

修改配置vi /etc/my.cnf

#主服务器唯一ID
server-id=3
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=my_multi_test
#设置binlog的格式row（生产推荐使用，在binlog里面记录在哪个行上做了修改，逻辑语法）、statement（保存操作时的语句）、mixed（两种方式混合使用）
binlog_format=STATEMENT
# 在作为从数据库的时候， 有写入操作也要更新二进制日志文件
log-slave-updates
#表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1， 取值范围是1 .. 65535
auto-increment-increment=2
# 表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535
auto-increment-offset=1

slave-1

修改配置vi /etc/my.cnf

#服务器唯一id
server-id=2
#启动中继日志
relay-log=mysql-relay

slave-2

修改配置vi /etc/my.cnf

#服务器唯一id
server-id=4
#启动中继日志
relay-log=mysql-relay

重启

重启启动4台mysql service musqld restart

授权账户

登录master-1、master-2的mysql服务，授权账户主从复制权限

-- 授权主从复制权限
grant replication slave on *.* to 'root'@'%' identified by 'Root_123';
-- 在进行授权的时候，如果提示密码的问题，把密码验证取消
set global validate_password_policy=0;
set global validate_password_length=1;

slave执行主从复制命令

因为master-1之前生成过bin log文件所以file为mysql-bin.000006，可以通过执行重置命令reset master解决，回归初始状态

登录slave的mysql服务中，执行以下命令

-- slave-1中执行命令，对master-1进行主从复制
-- MASTER_HOST为master-1的ip、MASTER_LOG_FILE为master-1的File名、MASTER_LOG_POS为master-1的Position值
CHANGE MASTER TO MASTER_HOST='192.168.243.131',MASTER_USER='root',MASTER_PASSWORD='Root_123',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154;

-- slave-2中执行命令，对master-2进行主从复制
-- MASTER_HOST为master-2的ip、MASTER_LOG_FILE为master-2的File名、MASTER_LOG_POS为master-2的Position值
CHANGE MASTER TO MASTER_HOST='192.168.243.133',MASTER_USER='root',MASTER_PASSWORD='Root_123',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=442;

启动slave

此时需要执行start slave命令，启动slave

查看slave状态

在slave的mysql服务中执行show slave status\G查看slave状态

当看到Slave_IO_Running，Slave_SQL_Running这两个属性都是yes的时候，表示主从复制已经准备好了，可以进行具体的操作了

主备同步

让master-1、master-2之间相互进行主从复制

-- master-1中执行命令，对master-2进行主从复制
-- MASTER_HOST为master-2的ip、MASTER_LOG_FILE为master-2的File名、MASTER_LOG_POS为master-2的Position值
CHANGE MASTER TO MASTER_HOST='192.168.243.133',MASTER_USER='root',MASTER_PASSWORD='Root_123',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=442;

-- master-2中执行命令，对master-1进行主从复制
-- MASTER_HOST为master-1的ip、MASTER_LOG_FILE为master-1的File名、MASTER_LOG_POS为master-1的Position值
CHANGE MASTER TO MASTER_HOST='192.168.243.131',MASTER_USER='root',MASTER_PASSWORD='Root_123',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154;

执行start slave命令，启动slave

当两台master执行show slave status\G都看到Slave_IO_Running，Slave_SQL_Running这两个属性都是yes的时候，表示互为主备成功

测试

在master-1中执行create database my_multi_test语句创建数据库，如下图可以看到master-1执行完之后其余三台mysql均能查询到my_multi_test库；在master-1进入该数据库创建表t1，并在其他mysql能够查到，证明主从和主备搭建成功

---

读写分离

使用mycat进行读写分离，具体细节请点击查看mycat-读写分离

---

注意

如果在运行时发现从库数据没有同步，则需要执行show slave status\G查看slave状态，如果看到Slave_IO_Running，Slave_SQL_Running这两个属性任一出现NO，则需要进行重置操作

-- master-1
	stop slave
	reset master
	reset slave
-- slave-1
	stop slave
	reset slave
-- master-2
	stop slave
	reset slave
	reset master
-- slave-2
	stop slave
	reset slave
-- 再执行主从命令	
CHANGE MASTER TO
-- 启动每个mysql的slave
start slave

此时再查看可以发现数据可以进行同步，但是之前的旧数据仍保持不变，之前同步失败的仍旧不会同步过来；

如果需要将之前的旧数据进行同步，那么slave节点的mysql需要清空数据，再重置，执行CHANGE MASTER TO主从命令；

但是需要注意的是主从命令参数中的MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154需要是主节点最初的对应的参数；

因此，建议搭建之后保存相关的参数命令以便以后操作。