Mysql database bidirectional synchronization hot standby bitsCN.com
Environment:
Master server: 10.224.194.239
Slave server: 10.224.194.237
Steps:
1. create backup users in Master/Slaver mysql db respectively:
Grant file on *. * TO backup@10.224.194.239 identified by 'pass ';
Grant replication slave on *. * TO backup@10.224.194.239 identified by 'pass ';
Grant file on *. * TO backup@10.224.194.237 identified by 'pass ';
Grant replication slave on *. * TO backup@10.224.194.237 identified by 'pass ';
2. configure the/etc/my. cf file on the Master server:
Server-id = 1
Binlog-do-db = test
Binlog-ignore-db = mysql
Replicate-do-db = test
Replicate-ignore-db = mysql
Master-host = 10.224.194.237
Master-user = backup
Master-password = pass
Master-port = 3306
Master-connect-retry = 60
Slave-skip-errors = all
3. configure the/etc/my. cf file on the Master server:
Server-id = 2
Binlog-do-db = test
Binlog-ignore-db = mysql
Replicate-do-db = test
Replicate-ignore-db = mysql
Master-host = 10.224.194.239
Master-user = backup
Master-password = pass
Master-port = 3306
Master-connect-retry = 60
Slave-skip-errors = all
4. restart the mysql database. the verification command is as follows:
View Master status
Show master status;
Mysql> show master status;
+ ----------------- + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ----------------- + ---------- + -------------- + ------------------ +
| Mysqllog.000003 | 301 | test | mysql |
+ ----------------- + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)
View Slave status
Show slave status/G;
Mysql> show slave status/G;
* *************************** 1. row ***************************
Slave_IO_State: Reconnecting after a failed master event read
Master_Host: 10.224.194.239
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqllog.000003
Read_Master_Log_Pos: 301
Relay_Log_File: mysqlgsb-relay-bin.000082
Relay_Log_Pos: 348
Relay_Master_Log_File: mysqllog.000003
Slave_IO_Running: No
Slave_ SQL _Running: Yes
Replicate_Do_DB: test
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 301
Relay_Log_Space: 650
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_ SQL _Errno: 0
Last_ SQL _Error:
1 row in set (0.00 sec)
ERROR:
No query specified
View the synchronization process:
Mysql> show processlist/G;
* *************************** 1. row ***************************
Id: 1
User: system user
Host:
Db: NULL
Command: Connect
Time: 4186
State: Waiting for master to send event
Info: NULL
* *************************** 2. row ***************************
Id: 2
User: system user
Host:
Db: NULL
Command: Connect
Time: 3745
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
* *************************** 3. row ***************************
Id: 5
User: root
Host: mysqlpri.webex.com: 28293
Db: NULL
Command: Query
Time: 0
State: NULL
Info: show processlist
* *************************** 4. row ***************************
Id: 6
User: backup
Host: 10.224.194.237: 41729
Db: NULL
Command: Binlog Dump
Time: 135
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
4 rows in set (0.00 sec)
ERROR:
No query specified
This article is from "small steps and continuous steps !" Blog
BitsCN.com