mysql数据库中怎么实现双向同步热备

共计 3129 个字符，预计需要花费 8 分钟才能阅读完成。

本篇文章为大家展示了 mysql 数据库中怎么实现双向同步热备，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。

环境：

Master server: 10.224.194.239

Slave  server: 10.224.194.237

步骤：

1. 分别在 Master/Slaver mysql db 创建 backup user：

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. 在 Master server 配置 /etc/my.cf 文件：

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. 在 Master server 配置 /etc/my.cf 文件：

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. 重启 mysql，验证命令如下：

查看 Master 状态

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)

查看 Slave 状态

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

查看同步进程：

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

上述内容就是 mysql 数据库中怎么实现双向同步热备，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注丸趣 TV 行业资讯频道。