mysql5.6搭建主從複製

使用MySQL 5.6，搭建主從複製。關於5.6的安裝，可以參考《MySQL 5.6 rpm安裝方法和碰見的問題》。

主庫創建slave用戶，設置複製許可權，

mysql> create user 'slave'@'1.1.1.2' identified by 'root';

Query OK, 0 rows affected (0.00 sec)

• 2. mysql> grant replication slave on *.* to 'slave'@'1.1.1.2' identified by 'root';
  4. Query OK, 0 rows affected (0.00 sec)

1. 編輯my.cnf配置文件，設置主庫server-id=1，定義需要複製的庫為test，忽略mysql資料庫

2. [root@vm-kvm10000-app mysql]# vi /etc/my.cnf
4. [mysqld]
6. server-id=1
8. log-bin=mysql-bin
10. binlog_do_db=test
12. binlog_ignore_db=mysql

1. 重啟主庫MySQL服務，

2. [root@vm-kvm10000-app mysql]# service mysql restart
4. Shutting down MySQL.. SUCCESS!
6. Starting MySQL. SUCCESS!

1. 看一下主庫狀態，

2. mysql> show master status;
4. +------------------+----------+--------------+------------------+-------------------+
6. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
8. +------------------+----------+--------------+------------------+-------------------+
10. | mysql-bin.000002 |      120 | test         | mysql            |                   |
12. +------------------+----------+--------------+------------------+-------------------+
14. 1 row in set (0.00 sec)

 

2. 或者
4. mysql> show master status \G
6. *************************** 1. row ***************************
8. File: mysql-bin.000002
10. Position: 120
12. Binlog_Do_DB: test
14. Binlog_Ignore_DB: mysql
16. Executed_Gtid_Set:
18. 1 row in set (0.00 sec)

1. 從庫，編輯my.cnf配置，設置server-id=2，區別於主庫，

2. [root@vm-kvm10001-app mysql]# vi /etc/my.cnf
4. [mysqld]
6. server-id=2

1. 重啟MySQL服務，

2. [root@vm-kvm10001-app mysql]# service mysql restart
4. Shutting down MySQL.. SUCCESS!
6. Starting MySQL. SUCCESS!

1. 設置主庫信息，

2. mysql> change master to master_host='1.1.1.1',master_user='slave',master_password='root',master_log_file='mysql-bin.000001',master_log_pos=120,master_connect_retry=10;
4. Query OK, 0 rows affected, 2 warnings (0.03 sec)

1. 檢索從庫狀態，

2. mysql> show slave status \G;
4. *************************** 1. row ***************************
6. Slave_IO_State:
8. Master_Host: 10.221.3.129
10. Master_User: slave
12. Master_Port: 3306
14. Connect_Retry: 10
16. Master_Log_File: mysql-bin.000001
18. Read_Master_Log_Pos: 120
20. Relay_Log_File: vm-kvm11853-app-relay-bin.000001
22. Relay_Log_Pos: 4
24. Relay_Master_Log_File: mysql-bin.000001
26. Slave_IO_Running: No
28. Slave_SQL_Running: No
30. Replicate_Do_DB:
32. Replicate_Ignore_DB:
34. Replicate_Do_Table:
36. Replicate_Ignore_Table:
38. Replicate_Wild_Do_Table:
40. Replicate_Wild_Ignore_Table:
42. Last_Errno: 0
44. Last_Error:
46. Skip_Counter: 0
48. Exec_Master_Log_Pos: 120
50. Relay_Log_Space: 120
52. Until_Condition: None
54. Until_Log_File:
56. Until_Log_Pos: 0
58. Master_SSL_Allowed: No
60. Master_SSL_CA_File:
62. Master_SSL_CA_Path:
64. Master_SSL_Cert:
66. Master_SSL_Cipher:
68. Master_SSL_Key:
70. Seconds_Behind_Master: NULL
72. Master_SSL_Verify_Server_Cert: No
74. Last_IO_Errno: 0
76. Last_IO_Error:
78. Last_SQL_Errno: 0
80. Last_SQL_Error:
82. Replicate_Ignore_Server_Ids:
84. Master_Server_Id: 0
86. Master_UUID:
88. Master_Info_File: /var/lib/mysql/master.info
90. SQL_Delay: 0
92. SQL_Remaining_Delay: NULL
94. Slave_SQL_Running_State:
96. Master_Retry_Count: 86400
98. Master_Bind:
100. Last_IO_Error_Timestamp:
102. Last_SQL_Error_Timestamp:
104. Master_SSL_Crl:
106. Master_SSL_Crlpath:
108. Retrieved_Gtid_Set:
110. Executed_Gtid_Set:
112. Auto_Position: 0
114. 1 row in set (0.00 sec)

1. 其中最重要的就是，這兩個參數，要求YES，但此處為NO，

2. Slave_IO_Running: No
4. Slave_SQL_Running: No

1. 檢索錯誤日誌，提示無法找見./performance_schema/cond_instances.frm文件，

2. [root@vm-kvm10001-app mysql]# vm-kvm10001-app.err
4. 2017-08-29 16:10:37 22933 [ERROR] /usr/sbin/mysqld: Can't find file: './performance_schema/cond_instances.frm' (errno: 13 - Permission denied)

1. 看一下其目錄，發現performance_schema文件夾，是root許可權，mysql用戶無法訪問，因此需要修改一下其許可權，

1. [root@vm-kvm10001-app mysql]# ls -rlht
2. total 109M
3. ...
4. drwx------ 2 root  root  4.0K Aug 29 15:05 performance_schema
5. ...

 

1. [root@vm-kvm10001-app mysql]# chown -R mysql:mysql *

 

1. [root@vm-kvm10001-app mysql]# ls -rlht
2. total 109M
3. ...
4. drwx------ 2 mysql mysql 4.0K Aug 29 15:05 performance_schema

1. ...

1. 重啟服務，

2. [root@vm-kvm10001-app mysql]# service mysql restart
4. Shutting down MySQL.... SUCCESS!
6. Starting MySQL. SUCCESS!

1. 再看從庫狀態，

2. mysql> show slave status \G
4. *************************** 1. row ***************************
6. ...
8. Slave_IO_Running: Yes
10. Slave_SQL_Running: Yes
12. ...

1. 主庫導出數據，用於導入從庫，首先需要設置讀鎖，避免數據不一致，

2. mysql> flush tables with read lock;

 

2. mysql> show master logs;
4. +------------------+-----------+
6. | Log_name         | File_size |
8. +------------------+-----------+
10. | mysql-bin.000001 |       143 |
12. | mysql-bin.000002 |       222 |
14. | mysql-bin.000003 |       545 |
16. +------------------+-----------+
18. 3 rows in set (0.00 sec)

1. 執行mysqldump將test庫，導出test.sql文件，

2. [root@vm-kvm10000-app mysql]# mysqldump -uroot -p -B test > test.sql

1. 然後解鎖表，

2. mysql> unlock tables;

1. 從庫執行導入，

2. [root@vm-kvm10001-app mysql]# mysql -uroot -p < test.sql

1. 要確保從庫，這兩個值正確，

2. Slave_IO_Running: Yes    
3. Slave_SQL_Running: Yes

1. 此時就完成了主從複製，向主庫插入一條記錄，

2. mysql> INSERT INTO test
3.     -> (id, name)
4.     -> VALUES
5.     -> (1, "a");
6. Query OK, 1 rows affected (0.00 sec)

1. 從庫中可以檢索出來，

2. mysql> select * from test;
4. +-------+--------+
6. |     id    |  name  |
8. +-------+--------+
10. |      1    |     a      |
12. +-------+--------+

1. 總結：
2. 1. MySQL相關文件、文件夾需要的許可權，可能會因為不正確，例如需要mysql許可權，但卻是root，導致資料庫異常。
3. 2. 主從複製，需要關註從庫，這兩個參數值，需要均為YES，出現NO，則可以檢索錯誤日誌，進一步定位。
4. Slave_IO_Running: Yes
5. Slave_SQL_Running: Yes