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

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

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

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

主从复制，其实我前段时间也整理了一篇文章，那是在liux环境的设置，有兴趣的可以了解了解、其实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的多安装配置、主从同步、读写分离等知识点，有兴趣的小伙伴，可以持续关注，谢谢！