mysql实现主从同步,需开启binlog日志。
binlog日志开启方式
1、配置文件
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
#设置3306端口
port = 3306
[mysqld]
log-bin=E:/mysql/mysql-5.7.28-winx64/data/mysql-bin/mysql-bin #指定文件名和路径,这里是绝对路径
binlog-format=Row #文件类型
#设置binlog清理时间
expire_logs_days = 14
#binlog每个日志文件大小
max_binlog_size = 100m
#binlog缓存大小
binlog_cache_size = 4m
#最大binlog缓存大小
max_binlog_cache_size = 512m
#server_id 主从数据库不能相同
server_id=106
# 设置mysql的安装目录
basedir= E:/mysql/mysql-5.7.28-winx64
# 设置mysql数据库的数据的存放目录
datadir= E:/mysql/mysql-5.7.28-winx64/data
# 允许最大连接数
max_connections=20000
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 最大允许的数据包大小
max_allowed_packet=200M
#大小写不敏感
lower_case_table_names=2
此处配置时完成遇到的坑
(原因不明)如果在winserver 2019下修改配置文件会导致mysql服务无法启动。
解决方法通过本地电脑修改后,将my.ini配置文件直接复制覆盖再运行。
2、配置完成启动后,就要通过从服务器连接主服务
1)首先连接主数据库 mysql -hlocalhost -uroot -p123456
2)查询主数据库 ,记录file
mysql>show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 308 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
3)配置从数据库连接
mysql>change master to master_host='192.168.145.222',master_user='root',master_password='123456',
master_log_file='mysql-bin.000001',master_log_pos=308; //注意不要断开,308数字前后无单引号。
Mysql>start slave; //启动从服务器复制功能
4)查询从数据库运行状态。
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 110.41.47.15
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 70333047
Relay_Log_File: WIN-RQHQ79863LQ-relay-bin.000009
Relay_Log_Pos: 694
Relay_Master_Log_File: mysql-bin.000001
#这两个参数YES代表运行成功
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: 70333047
Relay_Log_Space: 2573
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: 106
Master_UUID: 9455ea88-bf5e-11ee-9c14-fa163e419979
Master_Info_File: D:\mysql\mysql-5.7.28-winx64\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:
3、配置可能遇到的坑
通常我们都是将正式环境的copy过来进行配置从数据库,当我们启动slave时可能会出现 Slave_IO_Running: NO
可能由于复制过来的主从两个数据库 UUID相同,可以使用函数uuid();重新生成,然后复制到DATA文件下 auto.cnf文件中。
#可能由于复制过来的主从两个数据库 UUID相同,
Slave_IO_Running: NO
mysql> select uuid();
+--------------------------------------+
| uuid() |
+--------------------------------------+
| 48d16720-1123-11ef-ad7e-6c3c8c747531 |
+--------------------------------------+
1 row in set (0.01 sec)
#文件内容,将UUID替换
[auto]
server-uuid=9455ea88-bf5e-11ee-9c14-fa163e419979
注意:从数据库开启后不能直接对从数据库进行写入操作,可能造成同步失败,表现为 Slave_SQL_Running: No
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 110.41.47.15
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 70330803
Relay_Log_File: WIN-RQHQ79863LQ-relay-bin.000007
Relay_Log_Pos: 68877181
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1032
Last_Error: Could not execute Update_rows event on table hxrdp.hxrdpsystem_tb_user; Can't find record in 'hxrdpsystem_tb_user', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000001, end_log_pos 69231846
Skip_Counter: 0
Exec_Master_Log_Pos: 69231216
Relay_Log_Space: 69977525
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Update_rows event on table hxrdp.hxrdpsystem_tb_user; Can't find record in 'hxrdpsystem_tb_user', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000001, end_log_pos 69231846
可以通过执行以下命令修复
mysql> stop slave ;
Query OK, 0 rows affected (0.01 sec)
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)