Windows下MySQL主从复制,配置参数详解，以及遇到的问题，保姆级教学！

 说明：博主用来记录在开发中遇到的问题，如果有更好的见解，请多指教~

转载请标明出处！       

目录

1.准备工作

1.1安装Mysql

1.2配置my.ini文件

         1.3打开可视化工具执行命令（或者在Mysql命令行执行）

2.配置详解

2.1相同的配置参数：

2.2仅主服务器：以下配置只能在主服务器上配置

2.3仅从服务器（仅能在从服务器上配置）

3.目前遇到过的问题

4.总结

---

        简单来说主从复制就是把一个主服务器的数据同步到从服务器中，主服务器只用来写数据，从服务器只用来读取数据。使用场景一般是用于查询操作远大于写入操作时，为了缓解主服务的压力，主服务器上写入数据后，从服务器会同步更新数据（有延迟可忽略）。

1.准备工作

1.1安装Mysql

        推荐在主服务器和从服务器上安装相同的mysql版本，我用的是mysql安装版，直接在官网上下载，下一步下一步。说明：在主从复制中，主服务器和从服务器可以运行不同的MySQL版本，但需要注意以下几点：

1. 版本兼容性：主服务器和从服务器的MySQL版本应该具有一定的兼容性，以确保复制过程的稳定性和正确性。通常情况下，较新版本的MySQL可以作为主服务器，而较旧版本的MySQL可以作为从服务器。

2. 功能差异：不同版本的MySQL可能具有不同的功能和行为。在设置主从复制时，需要确保主服务器上使用的功能和语法在从服务器上也能正确执行。

3. 升级注意事项：如果你计划升级MySQL版本，请确保在升级主服务器之前先升级从服务器。这样可以避免由于主从版本不兼容而导致的复制中断或数据不一致的问题。

1.2配置my.ini文件

        这里我直接贴配置，详细解析移步到2.配置详解

主服务器配置

在[mysqld]下添加或修改这些参数，配置完之后需要重启Mysql服务，可以运行services.msc找到名为“Mysql 版本号” 的服务点击重启，或者以管理员权限打开黑窗口，执行net stop Mysql 停止mysql服务，提示成功后然后再执行net start mysql 启动Mysql服务

#主服务器唯一ID
server-id=3
#启用二进制日志
log-bin=master-bin
#设置不要复制的数据库（可设置多个）
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
#设置需要复制的数据库
binlog-do-db=test
#设置binlog格式，MIXED，ROW,STATEMENT
binlog_format=STATEMENT

 

从服务器配置

也是在同样的位置添加或修改，修改后同样需要重启Mysql服务，同上

#主服务器唯一ID，与主库不能相同
server-id=1
#设置不要复制的数据库（可设置多个）
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%
#设置需要复制的数据库
replicate_wild_do_table=test.%
#replicate-do-db=test

1.3打开可视化工具执行命令（或者在Mysql命令行执行）

连接主服务器：将其中的username，password换成你要创建的用户名密码，ip换成从服务器的ip

#创建用户，
CREATE USER 'username'@'ip' IDENTIFIED BY 'password';

#授权用户复制的权限（从服务器ip）
GRANT REPLICATION SLAVE ON *.* TO 'username'@'ip';

#刷新权限
FLUSH PRIVILEGES;

#显示二进制文件信息
show master status;

查到的这两个值需要复制出来，配置从服务器需要使用，如果不慎关掉的话再执行一次show master status 就行 

 连接从服务器：

将master_log_file换成上一步的File文件名，master_log_pos换成上一步的position位置。

说明：这句话可以多次执行，如果你发现其中一些信息填错了是可以再次执行该命令的，但是再次执行之前需要先执行停止和重置复制的命令STOP SLAVE;和RESET SLAVE;

#master_host 主数据库地址
CHANGE MASTER TO master_host ='主服务器ip',
#设置主数据库的端口号
master_port=3306,
#上一步中主数据库创建的从库用户
master_user ='username',
#上一步中主数据库创建的从库密码
master_password ='password',
#上一步中保存的file
master_log_file ='master-bin.000008',
#上一步中保存的position
master_log_pos = 1374;

#开启主从复制
start slave
#显示从服务器复制状态
show slave status

 当这两个值都为Yes时就已经完成了主从复制的配置，这时就可以在主库建表测试了

2.配置详解

2.1相同的配置参数：

server-id确保在主从服务器的my.ini中这个参数都是唯一的并且主从的ID不能相同

2.2仅主服务器：以下配置只能在主服务器上配置

1.log-bin

启用二进制日志，这个名字就是生成二进制文件的名字，从数据库也主要是依靠这个文件来解析主服务器进行的数据库操作，名字命名确保可读性就行

2.binlog-ignore-db

用于指定主服务器上要忽略写入二进制日志的数据库，一般用来忽略Mysql原有的数据库，可以在配置中同时写多个，也可以在一行中写多个数据用逗号分割例如：

binlog-ignore-db=mysql,information_schema,performance_schema

3.binlog-do-db

用于指定主服务器上要写入二进制日志的数据库，这个参数告诉主服务器只将指定的数据库的更改写入到二进制日志中。这个也可以配置多个同2
说明：2和3同时设置的时候以binlog-ignore-db为准，除了这里设置的数据库之外，其他数据库的操作都会被同步到二进制日志中

4.binlog_format

它有三个可选值：STATEMENT、ROW和MIXED。每个值适用于不同的情况，并具有各自的优缺点。

1）.STATEMENT格式：

• 适用情况：适用于大多数简单的查询和事务，特别是在没有涉及非确定性函数（如NOW()）和存储过程的情况下。
• 优点：
  • 生成的binlog较小，占用较少的磁盘空间。
  • 查询在从服务器上执行时，可以减少网络传输的数据量。
• 缺点：
  • 对于某些情况下的非确定性函数和存储过程，可能会导致主从数据不一致。
  • 无法复制使用了非确定性函数的语句，从服务器上的结果可能与主服务器不一致。

2).ROW格式：

• 适用情况：适用于复杂的查询和事务，包括使用了非确定性函数和存储过程的情况。
• 优点：
  • 生成的binlog可以精确地记录每一行数据的变化，可以确保主从数据的一致性。
  • 从服务器上执行查询时，不会受到非确定性函数和存储过程的影响。
• 缺点：
  • 生成的binlog较大，占用更多的磁盘空间。
  • 在复制过程中，需要传输更多的数据量到从服务器。

3).MIXED格式：

• 适用情况：适用于大多数情况，尤其是在既有简单查询又有复杂查询的混合环境中。
• 优点：
  • 根据具体的查询类型自动选择STATEMENT或ROW格式，以获得最佳的性能和数据一致性。
  • 在大多数情况下，生成的binlog较小，占用较少的磁盘空间。
• 缺点：
  • 对于某些情况下的非确定性函数和存储过程，可能会导致主从数据不一致。

2.3仅从服务器（仅能在从服务器上配置）

1.replicate-do-db

• replicate-do-db=db1,db2,db3：表示只复制数据库db1、db2和db3的操作。

2.replicate-ignore-db

• replicate-ignore-db=db4,db5：表示忽略不复制数据库db4和db5的操作。

3.replicate_wild_do_table

• replicate_wild_do_table=db1.table1,db2.table2：表示只复制数据库db1中的表table1和数据库db2中的表table2的操作。

4.replicate_wild_ignore_table

• replicate_wild_ignore_table=db3.%：表示忽略不复制数据库db3下所有表的操作。

说明：以上这四个配置在从数据库可以同时存在，按需选择即可，其中3和4可以使用%通配符选择数据库下的所有表，使用时尽量避免重复的设置

5.relay-log

relay-log=copy-log

一般不需要设置，服务器会自动创建并使用与主服务器相同的中继日志文件。如果想改从服务器中二进制文件的名字的话可以设置

主要用于设置从服务器的中继日志文件的名称和位置，就是从数据库将读到的主数据库的二进制日志转化为自己的日志文件，然后解析relay-log并执行sql语句。

3.目前遇到过的问题

3.1Slave_IO_Running一直显示connecting

分析：当你配置完成就遇到Slave_IO_Running或Slave_SQL_Running显示Connecting或者NO，只要是不为yes的情况，一般都是因为my.ini文件配置有误，也可能是ip不通。问就是乌龟的屁股

解决：一定要分清楚主服务器和从服务器的配置。检查配置文件以及当时执行的sql命令中的用户名、密码、主服务器ip、授权复制的ip（从服务器ip）,检查ip是不是不通，检查主服务器的防火墙是否允许Mysql的的端口通过。

3.2配置完成后一开始是双yes,后来SLave_IO_State显示Waiting for master to send event并且Slave_SQL_Running显示NO

分析：这种情况的话主从复制是已经设置好了，但是因为你插入数据的表是在你开启主从复制之前建的，当你执行插入操作的时候二进制文件只有insert或者update语句并没有create table 的语句，所以从服务器在读取二进制文件时是无法读到你之前的建表语句的。另外执行“show slave status”命令后
仔细观察的话会看到last_error提示Error 'Table '数据库.表名' doesn't exist' on query. Default database: '数据库'. Query: 'INSERT INTO `数据库`.`表名`(`**`) VALUES (**)'

解决：手动导出建表的SQL语句，并在从服务器执行该SQL语句，建表之后再进行插入或者更新操作就好了。

3.3last_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就是在my.ini配置文件中设置的id

解决：用MySQL命令行或者在可视化工具中执行SELECT @@server_id;查询你设置的ID，如果发现这个ID和你之前设置的不一样的话，还是在my.ini文件中搜索并确保设置的server-id是唯一的。如果还不行的话，用全局搜索电脑中的配置文件，看看是不是在别的地方也有my.ini设置冲突了。

补充：非免安装的MySQL在windows环境下的一般是以C:\ProgramData\MySQL\MySQL Server X.X\目录下的my.ini文件为准。

3.4用新建的账户连接mysql时提示Host "WIN-M8D74IVFLTB is not allowed to connect to this MySQL server（新建的用户无法登陆）

分析：这个想必不用多说了，建这个用户的目的就只是为了复制主服务器的数据，另外你可以看一下主服务器MySQL数据库下的user表中你刚刚新建的用户是不是只有一个数据项是Y（开启），而且这个用户和你之前正常建的用户共享的是相同的数据，所以不用想太多，用正常的用户登录进行建表，增删改查就行。

解决：为了满足你们热爱钻研的精神，执行SHOW GRANTS FOR 'username'@'hostname'可以查看目标用户已经被授权的权限，执行GRANT ALL PRIVILEGES ON database.* TO 'username'@'hostname';这个命令可以授予用户所有的权限。

3.5如果想用其他的ip和主服务器进行主从复制

一种方法是在主服务器上只用重新添加新用户，另外新的从服务器上也得重新配置；另外一种在主服务器上设置相对简单，在主服务器上只用选中mysql数据库并执行这句话设置所有ip都可以访问这个用户update user set host='%' where user ='username'  ，从数据库还是和之前一样配置。

4.总结

        相对来说主从复制的操作还是较为简单的，部署过程中主要确保Mysql的正确安装、配置文件的正确设置以及网络互通等。如果你在实操中遇到什么问题或者有什么不懂的，可以在评论区留言，大家一起讨论，文档也会持续更新大家的问题。