Mysql主从模式搭建

1，环境说明

OS：Centos 7

Mysql：5.7.38

主：10.4.7.41

备：10.4.7.42

2，Master配置

lower_case_table_names=1
character-set-server=utf8
max_connections=1024

server-id=104741  ###主从id一定要配置不同
log-bin=mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
expire_logs_days=7

3，Slave配置

lower_case_table_names=1
character-set-server=utf8
max_connections=1024


server-id=104742   ###主从id一定要配置不同
# log-bin=mysql-bin
# binlog-ignore-db=information_schema
# binlog-ignore-db=performance_schema
# binlog-ignore-db=mysql
# replicate-ignore-db=mysql.%
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60
expire_logs_days=7

配置说明：

binlog-ignore-db：忽略某个库binlog日志记录，一般在主库上配置

binlog-do-db：记录某个库binlog日志记录，一般在主库上配置

replicate-ignore-db：用来设置不需要同步的库，一般在从库上配置

replicate-do-db：用来设置需要同步的库，一般在从库上配置

4，创建数据同步用户

grant replication slave,reload,super on *.* to sync_slave@'%' identified by 'Aa123456?';
flush privileges;
show master status;

5，创建数据同步配置

changemasterto master_host='10.4.7.41',master_user='sync_slave',master_password='Aa123456?',master_log_file='mysql-bin.000002',master_log_pos=154;
showslavestatus \G;