Mysql主从复制（三）主主复制配置

1、环境

系统：Ubuntu 16.04 serverMysql：8.0.17主master （server-1）：192.168.50.5主master （server-2）：192.168.50.6特点：两个库可写可读，一个库修改会写入另一个库

2、主主复制原理

主主复制是将两个主从复制有机合并起来就好了。

3、准备工作

启动服务器和mysql，使用ps -ef|grep mysql检查mysql是否启动

防火墙配置主服务器只允许特定 IP 访问数据库的端口，避免不必要的攻击

主库1防火墙配置（登录主库服务器192.168.50.5）

# iptables -A INPUT -p tcp -s slave_ip --dport 3306 -j ACCEPT # 检查当前ip列表 sudo iptables -L -n # 删除可能已经存在的配置，避免出现多条重复记录 # sudo iptables -D INPUT -p tcp -s 127.0.0.1 --dport 3306 -j ACCEPT # 增加配置，只允许特定地址访问数据库端口 sudo iptables -I INPUT -p tcp --dport 3306 -j DROP #关闭所有3306端口外部访问 sudo iptables -I INPUT -s 192.168.50.0/24 -p tcp --dport 3306 -j ACCEPT #允许IP段访问3306 sudo iptables -I INPUT -s 192.168.1.102 -p tcp --dport 3306 -j ACCEPT #允许主机访问3306 sudo iptables -A OUTPUT -p tcp -d 192.168.50.6 --dport 3306 -j ACCEPT sudo iptables -L -n # 保存配置 sudo apt-get install iptables-persistent sudo netfilter-persistent save # 配置被保存到/etc/iptables/rules.v4 /etc/iptables/rules.v6这两个文件下面， # 最好确认一下实际保存的内容，尤其是安装了denyhosts等其他安全软件的情况下， # 可能会记录了多余的规则，需要手工删除

主库2防火墙配置（登录从库服务器192.168.50.6）

# iptables -A OUTPUT -p tcp -d master_ip --dport 3306 -j ACCEPT # 删除可能已经存在的配置，避免出现多条重复记录 # sudo iptables -D OUTPUT -p tcp -d 192.168.50.5 --dport 3306 -j ACCEPT # 增加配置 sudo iptables -I INPUT -p tcp --dport 3306 -j DROP #关闭所有3306端口外部访问 sudo iptables -I INPUT -s 192.168.50.0/24 -p tcp --dport 3306 -j ACCEPT #允许IP段访问3306 sudo iptables -I INPUT -s 192.168.1.102 -p tcp --dport 3306 -j ACCEPT #允许主机访问3306 sudo iptables -A OUTPUT -p tcp -d 192.168.50.5 --dport 3306 -j ACCEPT sudo iptables -L -n #保存配置 sudo apt-get install iptables-persistent sudo netfilter-persistent save #配置被保存到/etc/iptables/rules.v4 /etc/iptables/rules.v6这两个文件下面， #最好确认一下实际保存的内容，尤其是安装了denyhosts等其他安全软件的情况下， #可能会记录了多余的规则，需要手工删除

检查主从是否可以相互ping通

4、主数据库master-1配置（192.168.50.5）

修改mysql配置增加以下配置，命令sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld] server-id=5 #服务器唯一标识（可以使用IP地址最后一位） bind-address = 0.0.0.0 #默认是127.0.0.1，此处我们设置为任意地址，放开远程访问，这么操作之前一定要确保防火墙配置正确，否则会产生安全风险 log-bin=/var/log/mysql/mysql-bin.log #开启binlog二进制日志 log_bin_index=/var/log/mysql/master-bin.index #日志索引 expire_logs_days=10 #日志的缓存时间 max_binlog_size=200M #日志的最大大小 binlog_do_db=mybatis #同步的数据库名称 binlog_ignore_db=mysql #忽略同步的数据库 replicate-do-db=mybatis #要同步的数据库，默认所有库 replicate_ignore_db=mysql #忽略同步的数据库 auto_increment_increment=2 #步进值auto_imcrement。一般有n台主MySQL就填n，防止插入主键冲突 auto_increment_offset=1 #起始值。一般填第n台主MySQL。此时为第一台主MySQL

重启mysql在/var/log/mysql/下会生成2个文件，重启命令service mysql restart

创建用于同步的用户账号，比如用户名repl，密码repl

# 登录mysql主库 mysql -uroot -p # 创建用户 $ CREATE USER 'repl'@'192.168.50.6' IDENTIFIED WITH mysql_native_password BY 'repl'; # 分配权限 $ GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.50.6'; # 刷新权限 $ flush privileges;

查看master-1状态，记录二进制文件名（mysql-bin.000001）和位置（1168），后面有用

# 登录mysql主库 mysql -uroot -p # 查看主库状态 SHOW MASTER STATUS;

创建mybatis数据库和emploee表，用于测试

create database mybatis; use mybatis; CREATE TABLE `employee` ( `id` bigint(20) NOT NULL auto_increment COMMENT '编号', `first_name` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '姓', `last_name` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '名', `age` int COMMENT '年龄', `mobile_phone` varchar(20) COLLATE utf8_bin DEFAULT '' COMMENT '联系电话', `email` varchar(30) COLLATE utf8_bin DEFAULT '' COMMENT '邮箱', `birthday` DATE COMMENT '生日', `create_date` DATETIME COMMENT '创建日期', `update_date` DATETIME COMMENT '更新日期', primary key(id) ) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='员工信息表';

5、主数据库master-2配置（192.168.50.6）

修改mysql配置增加以下配置，命令sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld] server-id=6 #服务器唯一标识（可以使用IP地址最后一位） bind-address = 0.0.0.0 #默认是127.0.0.1，此处我们设置为任意地址，放开远程访问，这么操作之前一定要确保防火墙配置正确，否则会产生安全风险 log-bin=/var/log/mysql/mysql-bin.log #开启binlog二进制日志 log_bin_index=/var/log/mysql/master-bin.index #日志索引 expire_logs_days=10 #日志的缓存时间 max_binlog_size=200M #日志的最大大小 binlog_do_db=mybatis #同步的数据库名称 binlog_ignore_db=mysql #忽略同步的数据库 replicate-do-db=mybatis #要同步的数据库，默认所有库 replicate_ignore_db=mysql #忽略同步的数据库 auto_increment_increment=2 #步进值auto_imcrement。一般有n台主MySQL就填n，防止插入主键冲突 auto_increment_offset=2 #起始值。一般填第n台主MySQL。此时为第一台主MySQL

重启mysql在/var/log/mysql/下会生成2个文件，重启命令service mysql restart

创建用于同步的用户账号，比如用户名repl，密码repl

# 登录mysql主库 mysql -uroot -p # 创建用户 $ CREATE USER 'repl'@'192.168.50.5' IDENTIFIED WITH mysql_native_password BY 'repl'; # 分配权限 $ GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.50.5'; # 刷新权限 $ flush privileges;

查看master-2状态，记录二进制文件名（mysql-bin.000001）和位置（883），后面有用

# 登录mysql主库 mysql -uroot -p # 查看主库状态 SHOW MASTER STATUS;

创建mybatis数据库和emploee表，用于测试

create database mybatis; use mybatis; CREATE TABLE `employee` ( `id` bigint(20) NOT NULL auto_increment COMMENT '编号', `first_name` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '姓', `last_name` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '名', `age` int COMMENT '年龄', `mobile_phone` varchar(20) COLLATE utf8_bin DEFAULT '' COMMENT '联系电话', `email` varchar(30) COLLATE utf8_bin DEFAULT '' COMMENT '邮箱', `birthday` DATE COMMENT '生日', `create_date` DATETIME COMMENT '创建日期', `update_date` DATETIME COMMENT '更新日期', primary key(id) ) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='员工信息表';

6、构建主主复制

登录master-1（192.168.50.5）

# 登录mysql主库master-1 mysql -uroot -p # 执行同步SQL语句，复制master-2 CHANGE MASTER TO MASTER_HOST='192.168.50.6', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=1896; # 启动同步线程 start slave; # 查看slave状态 show slave status\G;

当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了

登录master-2（192.168.50.6）

# 登录mysql主库master-2 mysql -uroot -p # 执行同步SQL语句，复制master-1 CHANGE MASTER TO MASTER_HOST='192.168.50.5', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=1168; # 启动同步线程 start slave; # 查看slave状态 show slave status\G;

当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了

7、测试主主复制

登录master-1（192.168.50.5）

# 登录mysql主库master-1 mysql -uroot -p # 切换库 use mybatis; # 插入一条数据 INSERT INTO employee(first_name, last_name, age, mobile_phone, email, birthday, create_date, update_date) VALUES('主从','复制测试',26,'13533965228','13533965228@139.com',SYSDATE(), SYSDATE(),SYSDATE()); # 查询 select * from employee limit 10;

登录master-2（192.168.50.6）

# 登录mysql主库master-2 mysql -uroot -p # 切换库 use mybatis; # 查询 select * from employee limit 10; # master-2再插入一条 INSERT INTO employee(first_name, last_name, age, mobile_phone, email, birthday, create_date, update_date) VALUES('master-2','复制测试',26,'13533955228','13533955228@139.com',SYSDATE(), SYSDATE(),SYSDATE());

两个主机数据结果一样，主主复制配置成功。

8、注意事项

主主复制配置文件中auto_increment_increment和auto_increment_offset只能保证主键不重复，却不能保证主键有序。当配置完成Slave_IO_Running、Slave_SQL_Running不全为YES时，show slave status\G信息中有错误提示（也可以查看日志，一般在/var/log/mysql/error.log），可根据错误提示进行更正。如果执行start slave;失败，可以尝试停止stop slave;或重置reset slave。