Win10系统实现Mysql主从复制

破风

已于 2024-12-30 10:54:17 修改

阅读量448

点赞数 6

文章标签： mysql 数据库

于 2024-12-30 10:51:36 首次发布

本文链接：https://blog.youkuaiyun.com/qq_38935605/article/details/144772749

版权

需求：将Mysql主数据库数据实时同步到从数据库，达到一个实时备份的效果。首先我们需要先准备好两台电脑，并且里面都安装好Mysql，一般默认安装位置在 C:\Program Files\MySQL目录，这里有一个需要特别注意的地方，就是数据库配置文件存放目录，以本机为例，电脑安装完成以后修改Mysql配置文件不生效。这个时候我们需要查看configurator_settings.xml 文件，这个地方才是Mysql实际应用的配置文件，还是以本机为例

前期准备：

我们需要手动将主库所有需要主从复制的数据库数据全部同步到从库，等待同步完成以后我们在依次进行下面配置文件配置，修改配置以后都需要重新启动Mysql服务，启动成功以后再进行下面SQL语句执行。

主库：

实现主从复制首先第一步我们需要修改我们主数据库配置文件，修改完成以后需要我们重启我们的Mysql服务，可以在任务管理器 --> 服务 里面找到我们的Mysql进行重启即可：

[mysqld]
...
# 开启二进制日志（必须）
log-bin = mysql-bin
# MySQL服务ID，保证整个集群环境中唯一，默认为1（必须）
server-id = 1
# 允许从任何IP地址连接（或者可以绑定特定的IP）
bind-address=0.0.0.0      

# binlog-do-db=your_database_name # 可选，指定需要复制的数据库名（不设置则复制所有）
# binlog-ignore-db=ignore_db     # 可选，忽略不需要复制的数据库

在主数据库执行下面SQL，创建了一个用户root001，密码是rootpassword,并且给了用户convert_dev 数据库的增删改查权限

#创建一个新用户
CREATE USER 'root001'@'%' IDENTIFIED BY 'rootpassword';
#给用户指定只能访问convert_dev数据库里面的全部表的增删改查
GRANT SELECT, INSERT, UPDATE, DELETE ON convert_dev.* TO 'root001'@'%';
#给改用户赋予所有数据库增删改查权限
#GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'root001'@'%' WITH GRANT OPTION;
#刷新权限
FLUSH PRIVILEGES;
#查看用户权限
SHOW GRANTS FOR 'root001'@'%';
#当然也可以撤销用户权限
#REVOKE ALL PRIVILEGES ON *.* FROM 'root001'@'%';

完成以后编辑下面的SQL，不要执行，编辑好以后去从库里面执行

#查询数据库坐标
show master status;

CHANGE REPLICATION SOURCE TO 
  SOURCE_HOST = '192.168.1.111', #主库IP
  SOURCE_USER = 'root001',       #数据库访问用户名
  SOURCE_PASSWORD = 'rootpassword',   #数据库访问密码
  SOURCE_LOG_FILE = 'mysql-bin.000001', #开始同步的文件
  SOURCE_LOG_POS = 1992;           #当前正在写入的二进制日志文件的位置

从库：

修改从库配置，修改完成以后重启从库数据库服务

[mysqld]
server-id=2               # 从服务器的唯一ID，必须与主服务器不同
relay-log=relay-bin       # 启用中继日志
log-bin=mysql-bin         # 启用二进制日志
read-only=1               # 设置从服务器为只读

然后依次运行下面的SQL

#先停止IO
STOP SLAVE IO_THREAD;
#进行同步主库配置
CHANGE REPLICATION SOURCE TO 
  SOURCE_HOST = '192.168.1.111', 
  SOURCE_USER = 'root001',      
  SOURCE_PASSWORD = 'rootpassword',
  SOURCE_LOG_FILE = 'mysql-bin.000001', 
  SOURCE_LOG_POS = 1992;          
#开启同步
start slave;
#查看从数据库同步状态
show slave status;

下面两项都为YES表示成功：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

错误：当进行同步主库配置错误时 1872 - Replica failed to initialize applier metadata structure from the repository，我们就执行下面这段同步的SQL配置。

#先停止IO
STOP SLAVE;
RESET SLAVE ALL;

#进行同步主库配置
CHANGE REPLICATION SOURCE TO 
  SOURCE_HOST = '192.168.1.111', 
  SOURCE_USER = 'root001',      
  SOURCE_PASSWORD = 'rootpassword',
  SOURCE_LOG_FILE = 'mysql-bin.000001', 
  SOURCE_LOG_POS = 1992;          
#开启同步
start slave;


#查看从数据库同步状态
show slave status;

下面两项都为YES表示成功：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

错误示例：

Slave_IO_Running 为 Connecting，检查日志发现无法连接到服务器，错误信息：

2024-12-30T02:15:55.544961Z 20 [ERROR] [MY-010584] [Repl] Replica I/O for channel '': Error connecting to source 'root002@192.168.1.111:3306'. This was attempt 1/86400, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061
2024-12-30T02:16:55.592273Z 20 [ERROR] [MY-010584] [Repl] Replica I/O for channel '': Error connecting to source 'root002@192.168.1.111:3306'. This was attempt 2/86400, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061
2024-12-30T02:17:55.650605Z 20 [ERROR] [MY-010584] [Repl] Replica I/O for channel '': Error connecting to source 'root002@192.168.1.111:3306'. This was attempt 3/86400, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061
2024-12-30T02:18:55.688862Z 20 [ERROR] [MY-010584] [Repl] Replica I/O for channel '': Error connecting to source 'root002@192.168.1.111:3306'. This was attempt 4/86400, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061
2024-12-30T02:19:55.736272Z 20 [ERROR] [MY-010584] [Repl] Replica I/O for channel '': Error connecting to source 'root002@192.168.1.111:3306'. This was attempt 5/86400, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061

从服务器报错表明在尝试连接主服务器时，认证插件 caching_sha2_password 要求使用安全连接（TLS/SSL），但当前连接未满足要求。执行下面SQL即可解决：

请在主数据库执行下面SQL语句：

ALTER USER 'root002'@'%' IDENTIFIED WITH mysql_native_password BY 'rootpassword';
FLUSH PRIVILEGES;

如果这篇文章在你一筹莫展的时候帮助到了你，可以请作者吃个棒棒糖🙂，如果有啥疑问或者需要完善的地方欢迎大家在下面留言或者私信作者优化改进。