mysql数据库双向同步热备_MySQL

bitsCN.com

环境：

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;

mysql> 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

本文出自 “迈小步、不停步！” 博客