MySQL異步復制

準備：主備庫版本一致，正常安裝軟件。

1、主庫上設置一個復制使用的賬戶：

mysql> grant replication slave on *.* to 'rep1'@'192.168.100.136' identified by 'dbking';

Query OK, 0 rows affected (0.18 sec)

mysql> select user,host,password from mysql.user where user='rep1';

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

| user | host??????????? | password????????????????????????????????? |

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

| rep1 | 192.168.100.136 | *0E5B9DDCEF035D1E653099F4143C0F36061F7653 |

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

1 row in set (0.08 sec)

2、修改主庫參數文件，開啟binlog并設置slave id：

log_bin=/usr/local/mysql/data/dbking-bin.log

server_id=1

3、主數據庫上設置讀鎖定有效：

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

4、然后得到主數據庫上當前二進制日志文件及偏移量，為了在從庫復制起點：

mysql> show master status;

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

| File????????????? | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| dbking-bin.000001 |????? 120 |????????????? |????????????????? |?????????????????? |

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

1 row in set (0.00 sec)

5、主數據庫目前已停止更新操作，現在要得到主數據庫備份到從庫上恢復：

[root@chavinking mysql]# tar -cvf data.dir data

6、解鎖主庫：

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

7、將主庫備份恢復到從庫：

[root@chavinking mysql]# mv data data1204

[root@chavinking mysql]# tar -xvf data.dir

8、修改從庫my.cnf文件，設置server_id參數，server_id參數必須唯一：

server_id=2

9、從庫使用--skip-slave-start啟動數據庫：

[root@chavinking mysql]# bin/mysqld_safe --skip-slave-start &

[1] 18389

[root@chavinking mysql]# 161202 08:50:16 mysqld_safe Logging to '/usr/local/mysql/data/chavinking.err'.

161202 08:50:16 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

10、對從庫數據庫服務器進行相應設置，制定復制使用賬戶，主庫ip，端口以及開始進行復制的日志文件和位置等，具體如下：

本次試驗腳本如下：

mysql> change master to

??? -> master_host='192.168.80.133',

??? -> master_user='rep1',

??? -> master_password='dbking',

??? -> master_port=3306,

??? -> master_log_file='dbking-bin.000001',

??? -> master_log_pos=120;

Query OK, 0 rows affected, 2 warnings (0.11 sec)

11、從庫啟動slave線程：

mysql> start slave;

Query OK, 0 rows affected (0.11 sec)

12、從庫上查看配置信息：

mysql> show slave status \G;

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

?????????????? Slave_IO_State: Waiting for master to send event

????????????????? Master_Host: 192.168.80.133

????????????????? Master_User: rep1

????????????????? Master_Port: 3306

??????????????? Connect_Retry: 60

????????????? Master_Log_File: dbking-bin.000004

????????? Read_Master_Log_Pos: 120

?????????????? Relay_Log_File: chavinking-relay-bin.000008

??????????????? Relay_Log_Pos: 284

??????? Relay_Master_Log_File: dbking-bin.000004

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

????????????? Relay_Log_Space: 510

????????????? 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: 1

????????????????? Master_UUID: 9b92b2a8-b7e0-11e6-81e4-000c29fa5a95

???????????? Master_Info_File: /usr/local/software/mysql-5.6.24-linux-glibc2.5-x86_64/data/master.info

??????????????????? SQL_Delay: 0

????????? SQL_Remaining_Delay: NULL

????? Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

?????????? Master_Retry_Count: 86400

????????????????? Master_Bind:

????? Last_IO_Error_Timestamp:

???? Last_SQL_Error_Timestamp:

?????????????? Master_SSL_Crl:

?????????? Master_SSL_Crlpath:

?????????? Retrieved_Gtid_Set:

??????????? Executed_Gtid_Set:

??????????????? Auto_Position: 0

1 row in set (0.00 sec)

如果其中Slave_IO_Running: NO，并且日志文件報錯：

2016-12-02 11:19:37 19637 [ERROR] Slave I/O: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. Error_code: 1593

那么修改data目錄下的auto.cnf文件后重啟數據庫即可解決。