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

本篇文章为大家展示了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数据库中怎么实现双向同步热备，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注创新互联行业资讯频道。