MySQL Dual master hot standby

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

[email protected]/]# mysql-u root-p
mysql> show databases;
mysql> CREATE DATABASE CDN;
Mysql> use CDN;

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

[email protected]/]# mysql-u root-p
mysql> show databases;
mysql> CREATE DATABASE CDN;
Mysql> use CDN;

3. On a and B servers, add a user account (backup) as the Sync user account.

a:mysql> Grant replication Slave on * * to [e-mail protected] ' 10.16.0.247 ' identified by ' 1234 ';

b:mysql> Grant Replication Slave on * * to [e-mail protected] ' 10.16.0.246 ' identified by ' 1234 ';

4. Test the backup user on Server B with the following command to connect to a server

[email protected]/]# mysql-u backup-p-H 10.16.0.246

5. Stop the MySQL service on the A and B servers.

6.A Server

[[email protected]/] #vi/etc/my.cnf

To edit or modify a file:
Server-id=1
Log-bin=mysql-bin
Binlog-do-db=cdn
Restart the database service.

B Server
[[email protected]/] #vi/etc/my.cnf
server-id=2
master-host=10.16.0.246
Master-user=backup
master-password=1234
master-port=3306
Master-connect-retry=60

Replicate-do-db=cdn

7.b server
 
  [[email protected]/] #vi/etc/my.cnf
 
  Edit or modify the file:
       log-bin=mysql-bin 
        BINLOG-DO-DB=CDN
 
   a server
   [[email protected]/] #vi/etc/my.cnf
       master-host=10.16.0.247
       master-user=backup     &NBSP
       master-password=1234
       master-port=3306       &NBSP,
       master-connect-retry=60        
      & NBSP;REPLICATE-DO-DB=CDN

8. Start A and B server MySQL service.

Enter show master status on 9.A;

Mysql> Show master status;

+---------------------+----------+--------------+------------------+
|  File |  Position | binlog_do_db | binlog_ignore_db |
+-----------------+----------+--------------+------------------+
|     mysql-bin.000008 | 106 |    CDN | Manual,mysql |
+-----------------+----------+--------------+------------------+

Records the values of file and Position items mysql-bin.000008 and 106.

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 |
+-----------------+----------+--------------+------------------+
Records the values of file and Position items mysql-bin.000008 and 106.

* Two server file name can be different, now on both sides of the MySQL service do not restart or the file name will change.

10. On the a server, stop the slave service and configure the connection B server parameter.
 
   mysql> slave stop;


   mysql> change MASTER to
master_host= "10.16.0.247"       
- > master_user= "Backup",  
, master_password= "1234",          
, Master_ Log_file= "mysql-bin.000008",             
, master_log_pos=106;
 
    slave service  
   mysql> slave start;

On the B server, stop the slave service and configure the connection a server parameter.

mysql> slave stop;


Mysql> Change MASTER to
-master_host= "10.16.0.246",
-Master_user= "Backup",
-master_password= "1234",
-master_log_file= "mysql-bin.000008",
master_log_pos=106;

Turn on slave services

mysql> slave start;

11. Enter show slave status\g on the A and B servers to view the status.

Slave_io_running:yes must be Yes
Slave_sql_running:yes must be Yes

MySQL Dual master hot standby