MySQL主从同步配置及遇到的问题总结

MySQL主从同步配置及遇到的问题总结

MySQL主从同步是基于Bin Log实现的，而Bin Log记录的是原始SQL语句。

主从配置，以Windows系统为例：

主数据库配置：
在主数据库的配置文件my.ini中，找到log-bin和server-id
修改配置
log-bin=D:\Mysql-binlog\mysql-bin #定义binlog文件目录
binlog-do-db=boot_smartlms_product # 选择的数据库，被同步的数据库
binlog-ignore-db=information_schema #忽略的初始数据库
binlog-ignore-db=performance_schema #忽略的初始数据库
binlog-ignore-db=sakila #忽略的初始数据库
binlog-ignore-db=mysql #忽略的初始数据库
binlog-ignore-db=world #忽略的初始数据库
binlog-ignore-db=sys
binlog-format=Row
server-id=1#定义服务id，也可以是2或者3、4、5……

重新启动服务
执行SQL语句
show variables like ‘%log_bin%’
如果
log_bin的值为on，就证明配置成功，已经开启binlog

建立用户，用于从数据库复制权限
grant replication slave on . to ‘copy’@‘%’ identified by ‘12345678’;

执行SQL查看主服务器状态
show master status;
默认初始值：
File：mysql-bin.000001 Position：154
如果初始值不是154，是因为设置了copy用户导致Position数据增加，需要执行以下SQL重置一下状态
reset master;

以下是线下主数据库的配置文件示例：

[client]
port=8085
[mysql]
no-beep
[mysqld]
port=8085
datadir=D:/mysql-5.7.23-winx64/data
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
log-output=FILE
general-log=0
general_log_file="MS-IXTFKRSRKAZT.log"
slow-query-log=1
slow_query_log_file="MS-IXTFKRSRKAZT-slow.log"
long_query_time=10
log-error="MS-IXTFKRSRKAZT.err"
#配置主数据库
log-bin=D:\Mysql-binlog\mysql-bin
#需要同步的数据库
binlog-do-db=boot_smartlms
#忽略的数据库
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sakila
binlog-ignore-db=mysql
binlog-ignore-db=sys
binlog-format=Row
#设置server-id
server-id=1
lower_case_table_names=1
secure-file-priv="D:/mysql-5.7.23-winx64/Uploads"
max_connections=151
table_open_cache=2000
tmp_table_size=84M
thread_cache_size=10
myisam_max_sort_file_size=100G

myisam_sort_buffer_size=158M

key_buffer_size=8M

read_buffer_size=64K

read_rnd_buffer_size=256K

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=1M

innodb_buffer_pool_size=128M

innodb_log_file_size=48M

innodb_thread_concurrency=17

innodb_autoextend_increment=64

innodb_buffer_pool_instances=8

innodb_concurrency_tickets=5000

innodb_old_blocks_time=1000

innodb_stats_on_metadata=0

innodb_file_per_table=1

innodb_checksum_algorithm=0

back_log=80

flush_time=0

join_buffer_size=256K

max_allowed_packet=4M

max_connect_errors=100

open_files_limit=4161

sort_buffer_size=256K

table_definition_cache=1400

binlog_row_event_max_size=8K

sync_master_info=10000

sync_relay_log=10000

sync_relay_log_info=10000
event_scheduler=ON

从数据库配置：
在从数据库的配置文件my.ini中，修改
server-id=2#也可以是3、4、5……

增加以下内容
master_info_repository=‘TABLE’
relay_log_info_repository=‘TABLE’

执行SQL，表示同步192.168.0.156的数据库，端口3306，从游标154开始同步
change master to master_host=‘192.168.0.156’,master_port=3306,master_user=‘copy’,master_password=‘12345678’,master_log_file=‘mysql-bin.000001’,master_log_pos=154;

开启主从复制
start slave;
查看主从复制是否搭建完成
show slave status;
返回Slave_IO_Running和Slave_SQL_Running的值都为YES证明搭建成功 。

以下是从数据库配置文件示例：

[client]
port=8085
[mysql]
no-beep
[mysqld]
port=8085
datadir=D:/mysql-5.7.23-winx64/data
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
log-output=FILE
general-log=0
general_log_file="MS-IXTFKRSRKAZT.log"
slow-query-log=1
slow_query_log_file="MS-IXTFKRSRKAZT-slow.log"
long_query_time=10
log-error="MS-IXTFKRSRKAZT.err"
#配置从数据库
server-id=22
master_info_repository='TABLE'
relay_log_info_repository='TABLE'

lower_case_table_names=1
secure-file-priv="D:/mysql-5.7.23-winx64/Uploads"
max_connections=151
table_open_cache=2000
tmp_table_size=84M
thread_cache_size=10
myisam_max_sort_file_size=100G

myisam_sort_buffer_size=158M

key_buffer_size=8M

read_buffer_size=64K

read_rnd_buffer_size=256K

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=1M

innodb_buffer_pool_size=128M

innodb_log_file_size=48M

innodb_thread_concurrency=17

innodb_autoextend_increment=64

innodb_buffer_pool_instances=8

innodb_concurrency_tickets=5000

innodb_old_blocks_time=1000

innodb_stats_on_metadata=0

innodb_file_per_table=1

innodb_checksum_algorithm=0

back_log=80

flush_time=0

join_buffer_size=256K

max_allowed_packet=4M

max_connect_errors=100

open_files_limit=4161

sort_buffer_size=256K

table_definition_cache=1400

binlog_row_event_max_size=8K

sync_master_info=10000

sync_relay_log=10000

sync_relay_log_info=10000

常用查询SQL：

#查询版本号
select version();

#查看配置是否生效
show variables like '%log_bin%';

#查看主从状态
SHOW MASTER STATUS;

#重置主从游标
reset master;

#新建用户用于从数据库同步
grant replication slave on *.* to 'copy'@'%' identified by 'hst12345';

#刷新权限
FLUSH PRIVILEGES;

#查看server-id
SELECT @@server_id

#从数据库停止同步
stop slave;

#重置从库配置
reset slave;

#设置同步
change master to master_host='192.168.0.138',master_port=3306,master_user='copy',master_password='hst12345',master_log_file='mysql-bin.000001',master_log_pos=154;

#开启同步
start slave;

#查看同步状态
show slave status;

注意事项：
1.修改配置文件my.ini时，切勿使用记事本，建议使用EmEditor工具修改，防止编码格式不一致，导致修改配置失败。

2.当主从数据库之间连接断开后，从数据库会每60秒尝试重新连接主数据库，直到达到最大连接错误次数停止尝试。
临时解决办法：
· 到安装bin目录下，使用mysqladmin -u root -p flush-hosts命里 输入密码后，即完成清除缓存。

· 进入mysql控制台，执行：flush hosts;

每次手动处理比较麻烦，建议增加mysql定时任务，定时刷新连接。

示例：

先在数据库创建存储过程，执行刷新连接的SQL语句

CREATE DEFINER=`root`@`%` PROCEDURE `boot_smartlms`.`flush_hosts_proce`()
begin
FLUSH HOSTS;
end

然后创建定时器，表示从23年11月6日11点33分开始，每600秒执行一次

CREATE EVENT flush_hosts_event
ON SCHEDULE EVERY 600 SECOND
STARTS '2023-11-06 11:33:22.000'
ON COMPLETION PRESERVE
ENABLE
DO call flush_hosts_proce()

参考文档：

https://zhuanlan.zhihu.com/p/533187002