Shulou(Shulou.com)05/31 Report--

This article shows you how to achieve two-way synchronous hot backup in mysql database, the content is concise and easy to understand, it can definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Environment:

Master server: 10.224.194.239

Slave server: 10.224.194.237

Steps:

1. Create a backup user in Master/Slaver mysql db:

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'

two。 In the Master server configuration / etc/my.cf file:

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. In the Master server configuration / etc/my.cf file:

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 mysql with the following verification command:

View Master status

Show master status

> 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

The above content is how to achieve two-way synchronous hot backup in mysql database. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.