MySQL主主同步詳細操作過程

MySQL主主同步詳細操作過程

此處是在公司線上部署MySQL雙主時所記錄的文檔，為安全，IP都改為內網IP。

版本資訊：

#mysql -V

mysql  Ver 14.14 Distrib 5.5.37, fordebian-linux-gnu (x86_64) using readline 6.2

 

1、主庫開啟bin-log功能，配置server-id

修改my.cf設定檔，開啟bin-log功能，配置server-id。

#cat /etc/mysql/my.cnf

[myqld]

server-id              = 1

log_bin                =/var/log/mysql/

slave-net-timeout      = 60 

#salve-net-timeout預設是3600秒，縮短時間是為了防止雙YES的假象

#（事實上我已遇到，參考地址：http://www.cnblogs.com/billyxp/p/3470376.html）

 

 

如果要指定同步或不同步哪些庫，可使用如下參數

#binlog-do-db=osyunweidb  #需要同步的資料庫名，如果有多個資料庫，可重複此參數，每個資料庫一行
#binlog-ignore-db=mysql    #不同步mysql系統資料庫

 

2、確認bin-log與server-id是否開啟：

查看命令 show variables like 'log_bin'; show variables like 'server_id';

mysql> show variables like 'log_bin';

+---------------+-------+

|Variable_name | Value |

+---------------+-------+

|log_bin      | ON  |

+---------------+-------+

1 rowin set (0.00 sec)

mysql>show variables like 'server_id';

+---------------+-------+

|Variable_name | Value |

+---------------+-------+

|server_id    | 1    |

+---------------+-------+

1 rowin set (0.00 sec)

 

3、建立複製授權使用者

mysql> grant replication slave on *.* to replication@'%'identified by '123456'; #授權該使用者對所有表都能進行複製

mysql>flush privileges;  #重新整理許可權

 

4、鎖表，記錄log-bin檔案名稱和位置

mysql>flush tables with read lock;    #鎖定所有表，此時資料庫不能寫入資料

QueryOK, 0 rows affected (0.05 sec)

 

mysql>show master status;      #查看最新bin-log檔案及位置

+------------------------+------------+-------------------+-------------------------+

|File                  | Position  | Binlog_Do_DB      | Binlog_Ignore_DB        |

+------------------------+------------+-------------------+-------------------------+

|mysql-bin.000001      |  26314    |                  |                        |

+------------------------+------------+-------------------+-------------------------+

1row in set (0.00 sec)

 

5、鎖表狀態全備mysql資料

由於退出當前mysql登陸視窗，鎖表功能就失效，需複製一個會話進行全備。

#mysqldump-uroot -p -A -B >/tmp/mysql_bak_2014_10_30.sql.gz

 

看下備份資料大小，確認備份成功。

#ls  -l /tmp/mysql_bak_2014_10_30.sql.gz

-rw-r--r--1 root root 339222 10月 3011:01 /tmp/mysql_bak_2014_10_30.sql.gz

 

6、解除鎖表

mysql>unlock tables;

或直接quit退出即可。

 

7、從庫開啟bin-log功能，配置server-id

從庫開啟bin-log功能後，待會在主上在配置同步，互為主從就完成了。

#cat /etc/mysql/my.cnf

[myqld]

server-id              = 2

log_bin                =/var/log/mysql/mysql-bin.log

slave-net-timeout      = 60 

#salve-net-timeout預設是3600秒，縮短時間是為了防止雙YES的假象

#（參考地址：http://www.cnblogs.com/billyxp/p/3470376.html）

 

 

#/etc/init.d/mysqlrestart

 

8、確認從庫bin-log與server-id是否開啟

查看命令 show variables like 'log_bin'; show variables like 'server_id';

mysql>show variables like 'log_bin';

+---------------+-------+

|Variable_name | Value |

+---------------+-------+

|log_bin      | ON    |

+---------------+-------+

1row in set (0.00 sec)

mysql>show variables like 'server_id';

+---------------+-------+

|Variable_name | Value |

+---------------+-------+

|server_id    | 2    |

+---------------+-------+

1row in set (0.00 sec)

 

9、從庫匯入主庫的全備資料

在從庫上解壓資料。

#gzip-d mysql_bak_2014_10_30.sql.gz

登陸mysql匯入資料

mysql>source /root/mysql_bak_2014_10_30.sql

 

10、記錄從庫bin-log資訊

因為在從庫匯入全備資料時，此時主庫與從庫的內容是一致的，但是bin-log位置不一定一致。

mysql>show master status;      #查看最新bin-log檔案及位置

+------------------------+------------+-------------------+-------------------------+

|File                  |Position  | Binlog_Do_DB      | Binlog_Ignore_DB        |

+------------------------+------------+-------------------+-------------------------+

|mysql-bin.000003      |  2328055  |                  |                        |

+------------------------+------------+-------------------+-------------------------+

1row in set (0.00 sec)

 

11、從庫設定同步主庫

此處binlog檔案與位置狀態，是主庫在步驟4鎖表時show master status查看的位置狀態。

 

CHANGE MASTER TO 

MASTER_HOST='10.0.0.2',

MASTER_PORT=8306,

MASTER_USER='replication',

MASTER_PASSWORD='123456',

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=26314;

 

12、開啟從庫同步並確認同步是否成功

使用start slave開啟同步功能，使用show slave status\G查看同步是否成功

mysql>start slave;

QueryOK, 0 rows affected (0.00 sec)

mysql>show slave status\G  #\G不按表格輸出

***************************1. row ***************************

              Slave_IO_State: Waiting formaster to send event

                  Master_Host: 10.0.0.2

                  Master_User: replication

                  Master_Port: 8306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 136270

              Relay_Log_File:mysqld-relay-bin.000002

                Relay_Log_Pos: 72697

        Relay_Master_Log_File: mysql-bin.000001

            Slave_IO_Running:Yes

            Slave_SQL_Running:Yes

              Replicate_Do_DB:

          Replicate_Ignore_DB:

          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: 98758

              Relay_Log_Space: 110366

              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: 622  #查看主從同步延遲，延遲大則可能需要最佳化

Master_SSL_Verify_Server_Cert:No

                Last_IO_Errno: 0

                Last_IO_Error:

              Last_SQL_Errno: 0

              Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

            Master_Server_Id: 1

1row in set (0.00 sec)

 

#sql線程與IO線程都是YES，slave配置成功。

 

13、主庫設定同步從庫

由於從庫是全備匯入，原先在主庫上配置的複製帳戶也同樣匯入，所以這裡不用在從庫上新授權複製使用者。

從庫上的binlog檔案與位置狀態，是從庫在剛匯入時show master status查看到的位置狀態。

 

CHANGEMASTER TO 

MASTER_HOST='172.16.0.2',

MASTER_PORT=3306,

MASTER_USER='replication',

MASTER_PASSWORD='123456',

MASTER_LOG_FILE='mysql-bin.000003',

MASTER_LOG_POS=2328055;

#修改相應資訊，直接把這些配置在mysql中粘貼即可。

 

 

14、開啟同步並確認同步是否成功

使用start slave開啟同步功能，使用show slave status\G查看同步是否成功

mysql>start slave;

QueryOK, 0 rows affected (0.00 sec)

 

mysql>show slave status\G

***************************1. row ***************************

              Slave_IO_State: Waiting formaster to send event

                  Master_Host: 172.16.0.2

                  Master_User: replication

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000007

          Read_Master_Log_Pos: 107

              Relay_Log_File:mysqld-relay-bin.000006

                Relay_Log_Pos: 253

        Relay_Master_Log_File: mysql-bin.000007

            Slave_IO_Running: Yes

            Slave_SQL_Running:Yes

              Replicate_Do_DB:

          Replicate_Ignore_DB:

          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: 107

              Relay_Log_Space: 556

              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: 0

Master_SSL_Verify_Server_Cert:No

                Last_IO_Errno: 0

                Last_IO_Error:

              Last_SQL_Errno: 0

              Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

            Master_Server_Id: 2

1row in set (0.00 sec)

 

#IO線程與sql線程都是正常。

 

15、互為主從測試

在兩台mysql各建立一個庫，看兩邊是否都能進行同步。

分別在主庫上執行 create database test01;

從庫上執行create database test02;

看兩台資料庫上執行show databases;，看是否都有test01表和test02表。

我的經過測試，雙主測試成功。

生產環境MySQL主主同步主鍵衝突處理

Ubuntu下Nginx做負載實現高效能WEB伺服器5---MySQL主主同步

MySQL 主主同步配置

本文永久更新連結地址：