Mysql dual-master Hot Standby, mysql

Mysql dual-master Hot Standby, mysql

1. Create A database on server A and import the database

[Root @ dns/] # mysql-u root-p
Mysql> show databases;
Mysql> create database cdn;
Mysql> use cdn;

2. Create a database on server B and import it to the database.

[Root @ dns1/] # mysql-u root-p
Mysql> show databases;
Mysql> create database cdn;
Mysql> use cdn;

3. on server A and server B, add A user account (backup) as the synchronous user account.

A: mysql> Grant replication slave on *. * to backup @ '10. 16.0.247 'identified by '123 ';

B: mysql> Grant replication slave on *. * to backup @ '10. 16.0.246 'identified by '123 ';

4. Run the following command on server B to test whether the backup user can connect to server.

[Root @ dns/] # mysql-u backup-p-h 10.16.0.246
 

5. Stop the mysql service on server A and server B.

6. server
 
[Root @ dns/] # vi/etc/my. cnf
 
Edit or modify an object:
Server-id = 1
Log-bin = mysql-bin
Binlog-do-db = cdn
Restart the database service.
 
Server B
[Root @ dns1/] # vi/etc/my. cnf
Server-id = 2
Master-host = 10.16.0.246
Master-user = backup
Masters-password = 1234
Master-port = 3306
Master-connect-retry = 60

Replicate-do-db = cdn

7. Server B
 
[Root @ dns/] # vi/etc/my. cnf
 
Edit or modify an object:
Log-bin = mysql-bin
Binlog-do-db = cdn
 
Server
[Root @ dns1/] # vi/etc/my. cnf
Master-host = 10.16.0.247
Master-user = backup
Masters-password = 1234
Master-port = 3306
Master-connect-retry = 60
Replicate-do-db = cdn

8. Start mysql services on server A and server B.
 
9. Enter show master status on;
 
Mysql> show master status;
 
+ --------------------- + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ----------------- + ---------- + -------------- + ------------------ +
| Mysql-bin.000008 | 106 | cdn | manual, mysql |
+ ----------------- + ---------- + -------------- + ------------------ +

Record the value mysql-bin.000008 and 106 for the File and Position item.

Enter show master status on B;

Mysql> show master status;
 
+ --------------------- + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ----------------- + ---------- + -------------- + ------------------ +
| Mysql-bin.000008 | 106 | cdn | manual, mysql |
+ ----------------- + ---------- + -------------- + ------------------ +
Record the value mysql-bin.000008 and 106 for the File and Position item.

* The File names of the two servers can be different. Do not restart the mysql service on both sides. Otherwise, the File name will change.

10. Stop the slave service on server A and configure the parameters for connecting to server B.
 
Mysql> slave stop;


Mysql> CHANGE MASTER
-> MASTER_HOST = "10.16.0.247 ",
-> MASTER_USER = "backup ",
-& Gt; MASTER_PASSWORD = "1234 ",
-> MASTER_LOG_FILE = "mysql-bin.000008 ",
-> MASTER_LOG_POS = 106;
 
Enable the slave Service
Mysql> slave start;

 
On server B, stop the slave service and configure the parameters for connecting to server.
 
Mysql> slave stop;


Mysql> CHANGE MASTER
-> MASTER_HOST = "10.16.0.246 ",
-> MASTER_USER = "backup ",
-& Gt; MASTER_PASSWORD = "1234 ",
-> MASTER_LOG_FILE = "mysql-bin.000008 ",
-> MASTER_LOG_POS = 106;
 
Enable the slave Service

Mysql> slave start;

11. On server A and server B, enter show slave status \ G to view the status.

Slave_IO_Running: Yes must be YES
Slave_ SQL _Running: Yes must be YES