windows下MySQL数据库主从复制

关于MySQL数据库的主从复制，网上相关文章多数是基于Linux环境，笔者曾有 实施过Windows环境下MySQL数据库的主从复制。以下文章为笔者实施过程的原始记录，给需要的朋友参考，原创不易，你的点赞是我写作的动力，十分感谢！

架构1：一主一从

一.MySQL主从复制（也称A/B复制）的原理

(1) Master将数据改变记录到二进制日志(binary log)中，也就是配置文件log-bin指定的文件，这些记录叫做二进制日志事件(binary log events)；

(2) Slave通过I/O线程读取Master中的binary log events并写入到它的中继日志(relay log)；

(3) Slave重做中继日志中的事件，把中继日志中的事件信息一条一条的在本地执行一次，完成数据在本地的存储，从而实现将改变反映到它自己的数据(数据重放)。

二、主从配置需要注意的点

(1)主从服务器操作系统版本和位数一致；

(2) Master和Slave数据库的版本要一致；

(3) Master和Slave数据库中的数据要一致；

(4) Master开启二进制日志，Master和Slave的server_id在局域网内必须唯一；

三、主从配置的具体步骤

1.环境准备：

两台电脑/服务器

a.两台win10系统;

b.两台windows server 2012

c.一台win10,一台windows server 2012

以上三种搭配都可以

master IP:10.72.1.243

master Port:3306

slave IP:10.72.1.244

slave Port:3306

slave IP:10.72.1.245

slave Port:3306

2.主从服务器分别安装MySQL数据库

版本：MySQL Community 8.0.20

安装包下载地址：https://dev.mysql.com/downloads/mysql/

master user:michael

master password:michael123!

slave user:root

slave password:michael123!

【注意事项】：主从库版本必须一致

3.win10 mysql8 my.ini配置文件

（1）mysql的环境变量配置

（2）主从服务器my.ini的配置内容

我的配置路径就是：C:\Program Files\MySQL\MySQL Server 8.0，有的是在Mysql的bin目录下，这都没有关系，无关大局。

说明：在最新版本MySQL 8.0.20下，一般没有my.ini配置文件。要自己新建一个空白txt文档，更名为my.ini，放到该目录下。跟bin文件夹在同一目录级别。如下截图所示：

配置文件目录截图

主库my.ini配置如下：

[mysqld]

# 主要是加如下内容 log-bin、binlog-do-db、binlog-ignore-db、server-id

# 启用二进制日志

log-bin = mysql-bin

# 待同步的数据库

# 需要同步的数据库名，如果有多个数据库，可重复此参数，每个数据库一行

binlog-do-db = test

# 不同步mysql系统数据库

binlog-ignore-db = mysql

server-id = 1

skip-grant-tables

从库my.ini配置：

[mysqld]

# 启用二进制日志

log-bin=mysql-bin

# 需要同步的数据库名

binlog-do-db=test

# 不同步mysql系统数据库

binlog-ignore-db = mysql

port=3307

skip-errors=all

relay-log=mysql-relay-bin

【注意事项】：每次修改 my.ini 文件中的参数后，必须重新启动 MySQL 服务才会有效。

（3）查询主数据库状态，并记录下File和Position字段的值（命令行操作）

4.在主数据库上建立帐户并授权(Slave)

（1）. cd 到bin目录

C:\Program Files\MySQL\MySQL Server 8.0\bin

（2）.连接数据库

端口为默认端口3306的连接方式:

mysql -umichael -pmichael123!

（3）使用命令show global variables like 'port';查看端口号  

CREATE USER 'mytest'@'%' IDENTIFIED WITH mysql_native_password BY 'mytest123!';

GRANT REPLICATION SLAVE ON *.* TO 'mytest'@'10.72.1.244';

show grants for mytest;

flush privileges;

（4）查看主服务器状态，几下file和position的值，用于从库服务器的配置

show master status;

从库服务器配置截图

5.配置从数据库(Slave)

在从服务器上执行如下命令：

change master to master_host='10.72.1.243',master_user='test',

master_password='test123!',master_log_file='MICHAELZHOU-bin.000007',

master_log_pos=1142;

–参数解释：

-master_host : 设置要连接的主数据库的IP地址

-master_user : 设置要连接的主数据库的用户名

-master_password : 设置要连接的主数据库的密码

-master_log_file : 设置要连接的主数据库的bin日志的日志名称（show master status）;

-master_log_pos : 设置要连接的主数据库的bin日志的记录位置（show master status）(这里注意，最后项不需要加引号。否则配置失败)

6.检查从数据库复制功能状态

从库复制功能的状态描述

–参数解释：

-Slave_IO_Running=Yes

-Slave_SQL_Running=Yes

–如果都是Yes,则说明配置成功

–注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。

四、主从测试

在主数据库(Master)，建立数据库，并在这个库中建表增删修改数据

到从库中，查看新创建的库，是否出现了，新增的2条记录是否有出现，修改过的一条记录是否被修改了。

use test0;

select * from temp

在workbench中查看到的结果

结果，主从中改变的内容，全部同步到从库中了，到此说明主从复制成功完成了。Everything is OK.

【说明事项】

• 主从间的数据库不是实时同步，就算网络连接正常，也存在瞬间，主从数据不一致。
• 如果主从的网络断开，从会在网络正常后，批量同步。
• 如果对从进行修改数据，那么很可能从在执行主的bin-log时出现错误而停止同步，这个是很危险的操作。所以一般情况下，非常小心的修改从上的数据。

五、常见的异常处理

前导说明：数据库本身安装异常、局域网异常不在讨论之列。

mysql replication 中slave机器上有两个关键的进程，死一个都不行，一个是slave_sql_running，一个是Slave_IO_Running，一个负责与主机的io通信，一个负责自己的slave mysql进程。

1.处理Slave_IO_Running:No的问题

报错提示：

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

这段提示告诉我们：主库与从库具有相同的id,要实现主动复制，主从库必须有不同的id值。

【处理措施】：

到主从库分别执行查询语句，查看server_id的之是否相同

查看server_id的值

到从库修改server_id的值：

set global server_id=2;

再次执行上面的查询语句，查看server_id是否得到了修改。

【说明】如果server_id不同，再查看server_uuid是否 相同

show global variables like 'server_uuid';

mysq中server_id与server_uuid的区别是什么？

server_uuid是mysql的识别码，server_id是在集群里面的id号，防止server_id冲突的；

server_uuid可以替代server_id的绝大部分功能

server_uuid是自动生成的，不是很容易冲突

server_id是用户配置的

2.MySQL中Slave_IO_Running: Connecting问题

到主库上重新配置一个新的replication账户

原来账户：

CREATE USER 'test'@'%' IDENTIFIED WITH mysql_native_password BY 'test123!';

GRANT REPLICATION SLAVE ON *.* TO 'test'@'%';

配置一个新账户：

CREATE USER 'mytest'@'%' IDENTIFIED WITH mysql_native_password BY 'mytest123!';

GRANT REPLICATION SLAVE ON *.* TO 'mytest'@'10.72.1.244';

show grants for mytest;

flush privileges;

show master status;

3.Slave_SQL_Running: No问题

mysql> stop slave;

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; mysql> start slave;

查看从库状态后，两Yes状态，即表示成功了。

mysql> show slave status\G

从库状态信息

架构2：一主两从

示意图：

一主二从示意图

相比一主一从的区别：

1.在一主已从基础上，增加授权的从库

CREATE USER 'mytest'@'%' IDENTIFIED WITH mysql_native_password BY 'mytest123!';

GRANT REPLICATION SLAVE ON *.* TO 'mytest'@'10.72.1.244';

GRANT REPLICATION SLAVE ON *.* TO 'mytest'@'10.72.1.245';

show grants for mytest;

flush privileges;

2.从库的my.ini文件的配置：

server_id=3

3.在所有从库上执行以下命令：

change master to master_host='192.168.10.126',master_user='test',

master_password='test123!',master_log_file='MICHAELZHOU-bin.000007',

master_log_pos=1142;

start slave；

架构3：主主复制（双主架构）

示意图：

主主复制示意图

主主复制：互为主从

主主复制的配置步骤：

1、修改配置文件的内容：

① 各节点使用一个惟一server_id

② 都启动binary log和relay log

③ 定义自动增长id字段的数值范围，因为从节点上也需要配置为了避免冲突可以配置为奇偶数，起始点为1增长幅度是2，也就是 1 3 5 7....,另一个节点为2 4 6 8...

[mysqld]

# 主要是加如下内容 log-bin、binlog-do-db、binlog-ignore-db、server-id

log-bin = mysql-bin

# 待同步的数据库

# 需要同步的数据库名，如果有多个数据库，可重复此参数，每个数据库一行

binlog-do-db = test

# 不同步mysql系统数据库

binlog-ignore-db = mysql

server-id = 1

auto_increment_offset=1

auto_increment_increment=2

skip-grant-tables

注意：A节点 配置 server-id = 1，则B节点配置server-id = 2，id值不可以重复

2、 在A节点中创建拥有复制权限的用户账号，这里就使用上面创建的账号 ' tongbu '

3、在两个节点上各自查看当前pos点的位置，并把对方指定为主节点（互为主从关系，也就是主主），并启动复制线程

在从服务器上执行如下命令：

change master to master_host='10.72.1.244',master_user='test',

master_password='test123!',master_log_file='MICHAELZHOU-bin.000007',

master_log_pos=1142;

互相指定后查看同步状态：

show slave status\G

4、此时两个节点已经是主主关系，但是当同时在两个节点上创建表且有主键设置时就会产生冲突

两个节点同时插入表时，同步就会出错误【注意点】.

因为一个表中只能存在一个主键，，此时已经影响同步功能了，所以只好跳过这个错误以恢复同步状态继续同步.