MySQL dual-master-slave Configuration

1) Two mysql servers A and B
1. edit/etc/my. cnf for server Parameters

[Server A]
Server-id = 1
Binlog-do-db = test
Binlog-ignore-db = mysql
Replicate-do-db = test
Replicate-ignore-db = mysql
Master-host = 192.168.20.195
Master-user = repl
Master-password = repl
Master-port = 3306
Master-connect-retry = 10
Sync-binlog = 1
Log-bin = mysql-bin

[Server B]
Server-id = 2
Binlog-do-db = test
Binlog-ignore-db = mysql
Replicate-do-db = test
Replicate-ignore-db = mysql
Master-host = 192.168.20.194
Master-user = repl
Master-password = repl
Master-port = 3306
Master-connect-retry = 10
Sync-binlog = 1
Log-bin = mysql-bin
 
2) perform database operations on A and B
A/B: Slave stop;
A/B: Reset master;
A: grant replication slave on *. * to repl@192.168.255.194 identified by 'repl ';
B: grant replication slave on *. * to repl@192.168.255.195 identified by 'repl ';
A/B: show master status;
A: change master to master_host = '2017. 168.20.194 ', master_user = 'repl', master_password = 'repl', master_port = 3306, master_log_file = 'mysql-bin.000001', master_log_pos = 98; (log Based on master status)
B:
Change master to master_host = '2017. 168.20.195 ', master_user = 'repl', master_password = 'repl', master_port = 3306, master_log_file = 'mysql-bin.000006', master_log_pos = 98; (log Based on master status)
A/B: slave start;
A/B: show slave status \ G; (view synchronization status)
If the following status is displayed, the synchronization is successful.
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Seconds_Behind_Master: 0
 
3) test Database Synchronization. Add A table in the test database of A and delete the table in the test database of B.
Use test;
A: Create table username (id int (15 ));
Check whether the table username exists in B.
B: drop table username
Check whether the table usernam has been deleted in table.
A/B: show slave status \ G; (check the synchronization status again. If there is no problem, the synchronization is successful)
 
4) mutual synchronization configuration instances
1. a B synchronizes the master and slave databases to each other and does not synchronize mysql:
Binlog-do-db = test, binlog-ignore-db = mysql, replicate-do-db = test, replicate-ignore-db = mysql
2. a B synchronizes the master and slave databases only to test, and does not synchronize other databases. Newly created databases are not synchronized.
Binlog-do-db = test, replicate-do-db = test
3. Master A and Master B never synchronize mysql and other databases. For example, the new database will also be synchronized.
Binlog-ignore-db = mysql, replicate-ignore-db = mysql
4. a B synchronizes the master and slave databases to each other, including the newly created database.
The preceding four items are not set in both database configurations.
 
5) automatically synchronized shell scripts
1. added the super and replication client on permissions.
Grant super on *. * to repl@192.168.255.194 identified by 'repl ';
Grant replication client on *. * to repl@192.168.255.194 identified by 'repl ';
 
2. automatic synchronization script autosync. sh
#! /Bin/bash
While true
Do
Status = '/usr/bin/mysql-uroot-e "show slave status \ G;" | grep Slave_ SQL _Running | cut-f2-d ": "| sed's ///''
If [$ status! = "Yes"]
Then
A = '/usr/bin/mysql-urepl-prepl-h192.168.20.194-e "show master status" | grep mysql-bin | awk' {print $2 }''
/Usr/bin/mysql-uroot-e "slave stop"
/Usr/bin/mysql-uroot-e "change master to master_host = '192. 168.20.194 ', master_user = 'repl', master_password = 'repl', master_port = 192,
Master_log_file = 'mysql-bin.000001 ', master_log_pos = $"
/Usr/bin/mysql-uroot-e "slave start"
Fi
Sleep 10
Done
 
3. Background execution
Nohup./autosync. sh>/dev/null 2> & 1 &

Author duanjiangong"