1 主主复制简介
什么叫主主复制?即每台master既是master,又是另一台服务器的slave。这样,任何一方所做的变更,都会通过复制应用到另外一方的数据库中。就是两个 MySQL 都能读能写,数据记录通过二进制传达给对方从而保持数据的一致性。
2 环境:
系统:centos 7
mysql版本:8.0
生产环境共2台mysql服务器(以下对两台mysql分别简称为A B)
A:192.168.91.139 (master)
B:192.168.91.140 (master)
3 搭建
======================= 在两个完全一致的环境下====================
3.1初始化环境。
同步时间:
[root@localhost ~]# yum -y install ntp ntpdate
[root@localhost ~]# ntpdate cn.pool.ntp.org
[root@localhost ~]# hwclock --systohc
关闭防火墙
[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# systemctl disable firewalld
3.2 环境规划:
主机:
在主库上:
修改主库配置文件:开启log-bin功能,并指定server_id为1.这里log-bin必须指定,不然的话bin-log日志会根据主机名来命名,假如主库的主机名改变了,那主从复制就失效了。添加:
[root@localhost ~]#vim /etc/my.cnf
[mysqld]
server_id=10
log-bin=mysql-bin
重启服务:
[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# systemctl start mysqld
进入数据库,创建一个用于让从数据库连接的用户
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
mysql> create user 'copy'@'%' identified with mysql_native_password by 'Admin@123';
Query OK, 0 rows affected (0.03 sec)
mysql> grant replication slave on *.* to 'copy'@'%';
Query OK, 0 rows affected (0.32 sec)
mysql>
刷新授权表信息
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql>
获取主节点当前binary log文件名和位置(position)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 825 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
节点上设置主节点参数(master_logfile和master_log_pos均为另一台主库的)
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.91.140',
-> MASTER_USER='copy',
-> MASTER_PASSWORD='Admin@123',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=817;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql>
查看同步状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.91.140
Master_User: copy
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 817
Relay_Log_File: localhost-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
S