windows下mysql数据库设置主从同步
程序员文章站
2024-03-21 08:06:58
...
MySQL主从同步的机制
MYSQL主从同步是在MySQL主从复制(Master-Slave Replication)基础上实现的,通过设置在Master MySQL上的binlog(使其处于打开状态),Slave MySQL上通过一个I/O线程从Master MySQL上读取binlog,然后传输到Slave MySQL的中继日志中,然后Slave MySQL的SQL线程从中继日志中读取中继日志,然后应用到Slave MySQL的数据库中。这样实现了主从数据同步功能。
一、先安装好两个数据库,这里安装的是5.7.30 64位版本,解压安装步骤参照
这篇博客
二、主库配置
1.主数据库master修改my.ini配置
[mysqld]
#######主库配置START...#######
# 主数据库ID号
server_id=1
# 开启二进制日志
log_bin=master-bin
log_bin-index=master-bin.index
# 不同步哪些数据库
#binlog-ignore-db = information_schema
#binlog-ignore-db = mysql
#binlog-ignore-db = performance_schema
#binlog-ignore-db = sys
# 只同步哪些数据库,除此之外,其他不同步
#binlog-do-db = mytestdb
# 日志保留时间
expire_logs_days = 10
# 控制binlog的写入频率。每执行多少次事务写入一次
# 这个参数性能消耗很大,但可减小MySQL崩溃造成的损失
sync_binlog = 1
# 日志格式,建议mixed
# statement 保存SQL语句
# row 保存影响记录数据
# mixed 前面两种的结合
binlog_format = mixed
#######主库配置END...#######
2.重启mysql,创建用于同步的账户
# 创建slave帐号slave_account,密码123456
mysql>grant replication slave on *.* to 'slave_account'@'%' identified by '123456';
# 更新数据库权限
mysql>flush privileges;
3.查看master状态
mysql>show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000006 | 598 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
注:执行完这个步骤后不要再操作主数据库了,防止主数据库状态值变化
4.主库操作步骤截图
三、从库配置
1、从库修改my.ini配置
#######从库配置START...#######
# 从数据库ID号
server_id=2
#######主库配置END...#######
重启从库服务
net stop mysql3307
net start mysql3307
2、执行同步命令
# 执行同步命令,设置主数据库ip,同步帐号密码,同步位置
mysql>change master to master_host='127.0.0.1',master_user='slave_account',master_password='123456',master_log_file='master-bin.000006',master_log_pos=598;
# 开启同步功能
mysql>start slave;
3.查看从库状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: slave_account
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000006
Read_Master_Log_Pos: 598
Relay_Log_File: DESKTOP-0J67PTK-relay-bin.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: master-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 598
Relay_Log_Space: 538
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 9e9f9715-9b4a-11ea-967f-f875a4e2a56a
Master_Info_File: D:\Program Files\mysql-5.7.30-winx64_3307\data\master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
注:Slave_IO_Running及Slave_SQL_Running进程必须正常运行,即YES状态,否则说明同步失败。
到这里,主从数据库设置工作已经完成,自己可以新建数据库和表,插入和修改数据,测试一下是否成功
4.从库常用命令
# 停止主从同步
mysql> stop slave;
# 连接断开时,重新连接超时时间
mysql> change master to master_connect_retry=50;
# 开启主从同步
mysql> start slave;
5.从库操作步骤截图