mysql主主双向复制配置

1. 社区版安装包下载：https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.34-1.el7.x86_64.rpm-bundle.tar

2. 删除本机mariadb-libs 
# yum remove -y mariadb-lib

3. 安装社区版安装包 （test包不用安装，可以删除）
# yum install net-tools perl -y (最小化安装的centos需要安装依赖)
# mkdir mysql-5.7.34 && mv mysql-5.7.34-1.el7.x86_64.rpm-bundle.tar  mysql-5.7.34
# cd mysql-5.7.34 && tar xvf mysql-5.7.34-1.el7.x86_64.rpm-bundle.tar && rpm -ivh *.rpm

4. 更改主主配置： （问题参考"问题.txt"）
主：/etc/my.cnf
------------------------------------------------
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

character_set_server=utf8
max_connections=10000
log_timestamps=SYSTEM
lower_case_table_names=1
explicit_defaults_for_timestamp=true

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER

#主主复制配置:
auto_increment_offset=2
auto_increment_increment=2
log-bin=mysql-bin
binlog_format=row
log_bin_trust_function_creators=1
expire_logs_days=30
server-id=1
#配置从库上的更新操作是否写入二进制文件，如果这台从库，还要做其他从库的主库，那么就需要打这个参数，以便从库的从库能够进行日志同步
log-slave-updates
sync_binlog=1
#中继日志文件
relay_log = mysql-relay-bin
#需要复制的数据库名，如果有多个数据库，需要重复设置此参数，每个数据库一行
#replicate-do-db=ambari
#replicate-do-db=hive
#replicate-do-db=ranger
#不需要复制的数据库，如果有多个数据库，需要重复设置此参数，每个数据库一行
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=sys

另外一个主：/etc/my.cnf
------------------------------------------------
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
character_set_server=utf8
max_connections=10000
log_timestamps=SYSTEM
lower_case_table_names=1

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

#主主复制配置:
auto_increment_offset=1
auto_increment_increment=2
log-bin=mysql-bin
binlog_format=row
log_bin_trust_function_creators=1
expire_logs_days=30
server-id=2
#配置从库上的更新操作是否写入二进制文件，如果这台从库，还要做其他从库的主库，那么就需要打这个参数，以便从库的从库能够进行日志同步
log-slave-updates
sync_binlog=1
#中继日志文件
relay_log = mysql-relay-bin
#需要复制的数据库名，如果有多个数据库，需要重复设置此参数，每个数据库一行
#replicate-do-db=ambari
#replicate-do-db=hive
#replicate-do-db=ranger
#不需要复制的数据库，如果有多个数据库，需要重复设置此参数，每个数据库一行
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=sys


5. 启动mysqld服务
# systemctl start mysqld(两节点)



6. 配置mysql双向数据复制
登录mysql创建用于同步的用户账号:
192.168.128.128执行
grant replication slave on *.* to 'repuser'@'192.168.128.129' identified by 'Toor@1234';
flush privileges;
select user,host from mysql.user;
show grants for repuser@'192.168.128.129';

192.168.128.129执行show master status
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     6917 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
192.168.128.128执行
change master to master_host='192.168.128.129',master_user='repuser',master_password='Toor@1234',master_log_file='mysql-bin.000001',master_log_pos=6917,master_port=3306;



------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
192.168.128.129执行
grant replication slave on *.* to 'repuser'@'192.168.128.128' identified by 'Toor@1234';
flush privileges;
select user,host from mysql.user;
show grants for repuser@'192.168.128.128';

192.168.128.128执行show master status
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     8569 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
192.168.128.129执行
change master to master_host='192.168.128.128',master_user='repuser',master_password='Toor@1234',master_log_file='mysql-bin.000001',master_log_pos=8569,master_port=3306;




6. 修改密码策略：
SHOW VARIABLES LIKE 'validate_password%';
set global validate_password_policy=0;


复制