mycat数据库集群系列之mysql主从同步设置

　　

　　最近在梳理数据库集群的相关操作，现在花点时间整理一下关于mysql数据库集群的操作总结，恰好你又在看这一块，供一份参考。本次系列终结大概包括以下内容：多数据库安装、mycat部署安装、数据库之读写分离主从复制、数据库之双主多重、数据库分库分表。每一个点，有可能会对应一篇或者多篇文章，由于还要继续上班工作，所以本系列分享预计持续时间需要10天左右，有兴趣的您可以持续关注。我是一个菜鸟，如果写的不好的地方，望多多指点和包涵。

　　好了，不逼逼了，直接进入本次的主题：mycat的数据集群方式有很多种，比如：主从复制、多主多重等等，本次先从最简单的主从复制开始说起。

　　主从复制，其实我前段时间也整理了一篇文章，那是在liux环境的设置，有兴趣的可以了解了解https://www.cnblogs.com/xiaoXuZhi/p/xyh_mysqlsynchro.html。其实liux和Windows的mysql主从设置在本质上一样的，只是有点细微的不同，本次主要是针对windows环境的一个完整的流程搭建，所以也在专门梳理一下主从复制。

一、mysql主从复制简介

原理：

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

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

• 同时主节点为每个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语句逐一执行;

概述：

• 从库会生成两个线程,一个I/O线程,一个SQL线程;

• I/O线程会去请求主库的binlog,并将得到的binlog写到本地的relay-log(中继日志)文件中;

• 主库会生成一个log dump线程,用来给从库I/O线程传binlog;

• SQL线程,会读取relay log文件中的日志,并解析成sql语句逐一执行;

• 

二、前期配置文件准备

服务器说明：

　　我在本机启动了多个数据库实例，具体的每一个实例如下：

• ²  主数据库：192.168.1.88:3306

• ²  从数据库1：192.168.1.88:3307

• ²  从数据库2：192.168.1.88:3308

　　通过前面的对主从复制的介绍，我们知道主从复制其实最终是通过日志文件来进行交互，那么具体的日志文件怎么开启？具体的同步策略如何设置？下面将简单的介绍，一步一步的走下去。

　　主从同步的配置文件都是在数据库安装目录的下的更目录的my.ini（liux是my.cnf）文件，在前一篇文章关于mysql多实例安装中也提到了这个文件，这个次是直接在上面做配置新增即可，具体的配置文件如下，具体的配置参数值可以根据实际需要进行修正：

主数据库的3306中的 my.ini配置文件如下：

 

[Client]
port = 3306
[mysqld]
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=C:\Program Files (x86)\MySQL\MySQL Server 5.7
# 设置mysql数据库的数据的存放目录
datadir=C:\Program Files (x86)\MySQL\MySQL Server 5.7\data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 主从复制设置
# 设置服务id,注意改id值在同一个数据库集群中要保持唯一
server-id = 3306
# 写入磁盘策略 该参数的有效值有 0、1、2
# 0：事务提交时，不将重做日志缓冲写入磁盘。因此如果 MySQL 发生宕机，那么就有可能丢失一部分事务。
# 1：事务提交时，会将重做日志缓冲写入磁盘，并且立即刷新（fsync()）。保证一定持久化到了硬盘中。
# 2：事务提交时，会将重做日志缓冲写入磁盘，但是不会立即进行刷新操作。此时若操作系统发生宕机，也可能会丢失一部分数据。
# 可以看到，只有1才能真正地保证事务的持久性，但是由于刷新操作 fsync() 是阻塞的，性能会明显地下降。如果不在乎事务丢失，，0和2能获得更高的性能。
# 默认值为1，在实际使用过程中，为了效率，我们一般会在设置为2
innodb_flush_log_at_trx_commit=2  
# 开启binlog日志同步功能
# 这个参数直接影响mysql的性能和完整性。
# sync_binlog=0 当事务提交后，Mysql仅仅是将binlog_cache中的数据写入binlog文件，但不执行fsync之类的磁盘，同步指令通知文件系统将缓存刷新到磁盘，而让Filesystem自行决定什么时候来做同步，这个是性能最好的。
# sync_binlog=n，在进行n次事务提交以后，Mysql将执行一次fsync之类的磁盘同步指令，通知文件系统将Binlog文件缓存刷新到磁盘。
# Mysql中默认的设置是sync_binlog=0，即不做任何强制性的磁盘刷新指令，这时性能是最好的，但风险也是最大的。一旦系统Crash，在文件系统缓存中的所有Binlog信息都会丢失。
# 可根据自身项目的数据要求，进行灵活的设置
sync_binlog=0
# 二进制日志保留天数
expire_logs_days=180
# binlog日志文件名（可以任意命名）
log-bin=mysql-bin

# 同步的数据库设置方式有两种：
# binlog_do_db：设置需要同步的数据库
# binlog-ignore-db：设置不需要同步的数据库
# 其中两者只需要设置一个即可，根据我们平时的项目情况，都是设置binlog-ignore-db 排除不需要同步的一些系统级数据
# 这样设置有一个好处就是：如果业务数据库有新增，那么也不需要再次来更新设置该配置
# 两种设置方式，都可以多条数据
#同步的数据库，除此之外别的不同步（和下面binlog-ignore-db二选一）
# binlog_do_db=testdb 
#不同步数据库，除此之外都同步
binlog-ignore-db = information_schema
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
 
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8

 

 

　　从数据库的my.ini配置都很简单了，只需要配置server-id，binlog-do-db，binlog-ignore-db即可。

其中的server-id必须配置

　　binlog-do-db，binlog-ignore-db可根据实际需要设置，如果是主设置的数据库全部同步到从，那么这两个值就没必要设置了，如果从只同步主的某一些库，那么需要设置，在数据项目中，绝大部分都不需要设置的。

 

从数据库的3307中的 my.ini配置文件如下：

 

[Client]
port = 3307
 
[mysqld]
#设置3307端口
port = 3307
server-id = 3307
# 设置mysql的安装目录
basedir=C:\Program Files (x86)\MySQL\MySQL Server 5.7.2
# 设置mysql数据库的数据的存放目录
datadir=C:\Program Files (x86)\MySQL\MySQL Server 5.7.2\data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
 
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8

 

从数据库的3308中的 my.ini配置文件如下：

[Client]
port = 3308

[mysqld]
#设置3308端口
port = 3308
server-id = 3308
# 设置mysql的安装目录
basedir=C:\Program Files (x86)\MySQL\MySQL Server 5.7.2
# 设置mysql数据库的数据的存放目录
datadir=C:\Program Files (x86)\MySQL\MySQL Server 5.7.2\data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
 
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8

 

三、主数据库创建同步账号

　　在处理前，由于上面刚刚设置了数据库的配置文件，为了能够生效，首先是重启一下主数据库。只需要执行如下两个命令即可：

　　net stop mysql

　　net start mysql

 

创建主数据用户同步使用的账号：

• 登录数据库：mysql -uroot -pxuyuanhong -P 3306

• 创建数据同步用户slaveData：

　　　　CREATE USER 'slaveData'@'%' IDENTIFIED BY '用户登录密码';

• 设置权限：

　　　　GRANT REPLICATION SLAVE ON *.* to 'slaveData'@'%' identified by '用户登录密码';

• 刷新权限：FLUSH PRIVILEGES;
• 查看主机状态：show master status;

 

四、从数据库同步设置

　　前置条件：从数据库同步设置，只以数据库3307为例来说明：

　　从数据设置前，也还是按照主数据设置一样，先重启一下。只需要执行如下两个命令即可：

　　 net stop mysql3307

　　net start mysql3307

登录从数据库：

　　mysql -uroot -pxuyuanhong -P 3307

执行手动同步命令：

　　CHANGE MASTER TO MASTER_HOST='192.168.3.205',MASTER_PORT=3306,MASTER_USER='slaveData',MASTER_PASSWORD='xuyuanhong',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=844;

 

其中的参数值说明：

• master_host：主数据库IP地址

• master_user：主数据库连接用户名

• master_password：主数据量连接密码

• master_port：主数据端口

• master_log_file：开始同步的主数据库日志名称，主数据库show master status

• 队列的Flie列数据

• master_log_pos：开始同步的主数据库日志开始位置，主数据库show master status

• 队列的Position列数据

启动主从同步：

　　start slave;

查看主从同步状态

　　show slave status\G;

　　主从同步状态结果图如下：如果Slave_IO_Running: Yes，Slave_SQL_Running: Yes时说明两个线程已启动，主从复制配置成功。

 

 

主从同步设置失败如何处理

　　主从状态的结果Slave_IO_Running: Yes，Slave_SQL_Running不都为true,那么需要根据提示结果，重新设置。重新设置的流程如下：

• 停止slave：stop slave;

• 重置slave：reset slave;

• 重复上面的：执行手动同步命令开始流程

五、sql同步执行失败后不再同步处理

　　主从同步在实际使用过程中，如果处理的不好，可能会导致同步失败，比如：从数据库也在做新增操作，如果有自增主键约束，那么就可能导致同步失败。因为主从同步最终目的是实现数据的一致性，所有当某一项同步失败，那么就不会再做后同步处理。那么如果出现同步失败后又该如何处理呢？

　　在实际处理过程中，大概有两种处理方式：

　　1、主从数据差异小，并且不管最终数据的一致性，那么可以设置直接跳过同步出差的日志，继续同步后续数据，具体操作命令如下：

　　第一步：停止主从同步

　　stop slave;
　　第二步：跳过错误的日志位置，继续后续同步，后面的数字可变
　　set global sql_slave_skip_counter =1;

　　第三步：继续开启主从同步
　　start slave;

　　2、如果主从数据差异大，并且要求主从数据完全一致，那么需要：重新做主从，完全同步。

　　第一步：删除从数据库，将主数据库备份到从数据库（确保主从数据完全一致）

　　第二步：按照上面的流程，重新配置主从同步

六、自我一些总结

　　1、 主从同步数据库，一定要注意读写分离。从数据库做读操作，不要做写操作，最好直接只给从数据库读权限，不给写权限

　　2、 数据库设置主从同步账号时，最好创建专门的账号用来做主从同步

　　有关mysql的主从同步就分享到这儿了，下一篇将和大家一起分享mycat的多安装配置、主从同步、读写分离等知识点，有兴趣的小伙伴，可以持续关注，谢谢！

END
为了更高的交流，欢迎大家关注我的公众号，扫描下面二维码即可关注，谢谢：