環境:
ubuntu18.04.2
mysql5.7.21
---------------master1服務器操作記錄---------------
在my.cnf檔案的[mysqld]配置區域添加下面內容：
[root@master1 ~]# vim /etc/my.cnf
server-id = 1
log-bin = mysql-bin
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed
auto-increment-increment = 2
auto-increment-offset = 1
slave-skip-errors = all
[root@master1 ~]# cd /usr/local/mysql/support-files
[root@master1 ~]#./mysql.server restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
資料同步授權（iptables防火墻開啟3306埠）這樣I/O執行緒就可以以這個用戶的身份連接到主服務器，并且讀取它的二進制日志，
mysql>grant all privileges on *.* to root@'%' identified by "123456";
#grant replication slave,replication client on *.* to root@'192.168.85.%' identified by "123456";
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
最好將庫鎖住，僅僅允許讀，以保證資料一致性；待主主同步環境部署后再解鎖；
鎖住后，就不能往表里寫資料，但是重啟mysql服務后就會自動解鎖！
mysql> flush tables with read lock; //注意該引數設定后，如果自己同步對方資料，同步前一定要記得先解鎖！
Query OK, 0 rows affected (0.00 sec)
查看下log bin日志和pos值位置
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000004 | 430 | | mysql,information_schema | |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)
---------------master2服務器操作記錄---------------
在my.cnf檔案的[mysqld]配置區域添加下面內容：
[root@master2 ~]# vim /etc/my.cnf
server-id = 2
log-bin = mysql-bin
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed
auto-increment-increment = 2
auto-increment-offset = 2
slave-skip-errors = all
[root@master1 ~]# cd /usr/local/mysql/support-files
[root@master1 ~]#./mysql.server restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
mysql> grant replication slave,replication client on *.* to root@'192.168.85.%' identified by "123465";
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000003 | 430 | | mysql,information_schema | |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)
---------------master1服務器做同步操作---------------
mysql> unlock tables; //先解鎖，將對方資料同步到自己的資料庫中
mysql> slave stop；
mysql> change master to master_host='192.168.85.141',master_user='root',master_password='12346',master_log_file='mysql-bin.000003',master_log_pos=430;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
查看同步狀態，如下出現兩個“Yes”，表明同步成功！
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 182.148.15.237
Master_User: wang
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 430
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 279
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
.........................
Seconds_Behind_Master: 0
.........................
這樣，master1就和master2實作了主從同步，即master1同步master2的資料，
---------------master2服務器做同步操作---------------
mysql> unlock tables; //先解鎖，將對方資料同步到自己的資料庫中
mysql> slave stop；
mysql> change master to master_host='192.168.85.140',master_user='root',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=430;
Query OK, 0 rows affected, 2 warnings (0.06 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 182.148.15.238
Master_User: wang
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 430
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 279
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
........................
Seconds_Behind_Master: 0
........................
這樣，master2就和master1實作了主從同步，即master2也同步master1的資料，
以上表明雙方已經實作了mysql主主同步，
當運行一段時間后，要是發現同步有問題，比如只能單向同步，雙向同步失效，可以重新執行下上面的change master同步操作，只不過這樣同步后，只能同步在此之后的更新資料，下面開始進行資料驗證：
-----------------主主同步效果驗證---------------------
1）在master1資料庫上寫入新資料
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> create database huanqiu;
Query OK, 1 row affected (0.01 sec)
mysql> use huanqiu;
Database changed
mysql> create table if not exists haha (
-> id int(10) PRIMARY KEY AUTO_INCREMENT,
-> name varchar(50) NOT NULL);
Query OK, 0 rows affected (0.04 sec)
mysql> insert into haha values(1,"王士博");
Query OK, 1 row affected (0.00 sec)
mysql> insert into haha values(2,"郭慧慧");
Query OK, 1 row affected (0.00 sec)
mysql> select * from haha;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 王士博 |
| 2 | 郭慧慧 |
+----+-----------+
2 rows in set (0.00 sec)
然后在master2資料庫上查看，發現資料已經同步過來了!
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| huanqiu |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use huanqiu;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_huanqiu |
+-------------------+
| haha |
+-------------------+
1 row in set (0.00 sec)
mysql> select * from haha;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 王士博 |
| 2 | 郭慧慧 |
+----+-----------+
2 rows in set (0.00 sec)
2）在master2資料庫上寫入新資料
mysql> create database hehe;
Query OK, 1 row affected (0.00 sec)
mysql> insert into huanqiu.haha values(3,"周正"),(4,"李敏");
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
然后在master1資料庫上查看，發現資料也已經同步過來了!
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hehe |
| huanqiu |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
mysql> select * from huanqiu.haha;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 王士博 |
| 2 | 郭慧慧 |
| 3 | 周正 |
| 4 | 李敏 |
+----+-----------+
4 rows in set (0.00 sec)
至此，Mysql主主同步環境已經實作，
---恢復內容結束---
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/121246.html
標籤：MySQL
上一篇：編譯安裝msyql