使用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)

 

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

 

1. 编辑my.cnf配置文件，设置主库server-id=1，定义需要复制的库为test，忽略mysql数据库

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

 

1. 重启主库MySQL服务，

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

 

1. 看一下主库状态，

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

 

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

 

1. 从库，编辑my.cnf配置，设置server-id=2，区别于主库，

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

 

1. 重启MySQL服务，

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

 

1. 设置主库信息，

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;
3.  
4. Query OK, 0 rows affected, 2 warnings (0.03 sec)

 

1. 检索从库状态，

1.  
2. mysql> show slave status \G;
3.  
4. *************************** 1. row ***************************
5.  
6. Slave_IO_State:
7.  
8. Master_Host: 10.221.3.129
9.  
10. Master_User: slave
11.  
12. Master_Port: 3306
13.  
14. Connect_Retry: 10
15.  
16. Master_Log_File: mysql-bin.000001
17.  
18. Read_Master_Log_Pos: 120
19.  
20. Relay_Log_File: vm-kvm11853-app-relay-bin.000001
21.  
22. Relay_Log_Pos: 4
23.  
24. Relay_Master_Log_File: mysql-bin.000001
25.  
26. Slave_IO_Running: No
27.  
28. Slave_SQL_Running: No
29.  
30. Replicate_Do_DB:
31.  
32. Replicate_Ignore_DB:
33.  
34. Replicate_Do_Table:
35.  
36. Replicate_Ignore_Table:
37.  
38. Replicate_Wild_Do_Table:
39.  
40. Replicate_Wild_Ignore_Table:
41.  
42. Last_Errno: 0
43.  
44. Last_Error:
45.  
46. Skip_Counter: 0
47.  
48. Exec_Master_Log_Pos: 120
49.  
50. Relay_Log_Space: 120
51.  
52. Until_Condition: None
53.  
54. Until_Log_File:
55.  
56. Until_Log_Pos: 0
57.  
58. Master_SSL_Allowed: No
59.  
60. Master_SSL_CA_File:
61.  
62. Master_SSL_CA_Path:
63.  
64. Master_SSL_Cert:
65.  
66. Master_SSL_Cipher:
67.  
68. Master_SSL_Key:
69.  
70. Seconds_Behind_Master: NULL
71.  
72. Master_SSL_Verify_Server_Cert: No
73.  
74. Last_IO_Errno: 0
75.  
76. Last_IO_Error:
77.  
78. Last_SQL_Errno: 0
79.  
80. Last_SQL_Error:
81.  
82. Replicate_Ignore_Server_Ids:
83.  
84. Master_Server_Id: 0
85.  
86. Master_UUID:
87.  
88. Master_Info_File: /var/lib/mysql/master.info
89.  
90. SQL_Delay: 0
91.  
92. SQL_Remaining_Delay: NULL
93.  
94. Slave_SQL_Running_State:
95.  
96. Master_Retry_Count: 86400
97.  
98. Master_Bind:
99.  
100. Last_IO_Error_Timestamp:
101.  
102. Last_SQL_Error_Timestamp:
103.  
104. Master_SSL_Crl:
105.  
106. Master_SSL_Crlpath:
107.  
108. Retrieved_Gtid_Set:
109.  
110. Executed_Gtid_Set:
111.  
112. Auto_Position: 0
113.  
114. 1 row in set (0.00 sec)

 

1. 其中最重要的就是，这两个参数，要求YES，但此处为NO，

1.  
2. Slave_IO_Running: No
3.  
4. Slave_SQL_Running: No

 

1. 检索错误日志，提示无法找见./performance_schema/cond_instances.frm文件，

1.  
2. [root@vm-kvm10001-app mysql]# vm-kvm10001-app.err
3.  
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. 重启服务，

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

 

1. 再看从库状态，

1.  
2. mysql> show slave status \G
3.  
4. *************************** 1. row ***************************
5.  
6. ...
7.  
8. Slave_IO_Running: Yes
9.  
10. Slave_SQL_Running: Yes
11.  
12. ...
13.  

 

1. 主库导出数据，用于导入从库，首先需要设置读锁，避免数据不一致，

1.  
2. mysql> flush tables with read lock;
3.  

 

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

 

1. 执行mysqldump将test库，导出test.sql文件，

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

 

1. 然后解锁表，

1.  
2. mysql> unlock tables;
3.  

 

1. 从库执行导入，

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

 

1. 要确保从库，这两个值正确，

1.  
2. Slave_IO_Running: Yes    
3. Slave_SQL_Running: Yes
4.  

 

1. 此时就完成了主从复制，向主库插入一条记录，

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

 

1. 从库中可以检索出来，

1.  
2. mysql> select * from test;
3.  
4. +-------+--------+
5.  
6. |     id    |  name  |
7.  
8. +-------+--------+
9.  
10. |      1    |     a      |
11.  
12. +-------+--------+
13.  

 

 

1. 总结：
2. 1. MySQL相关文件、文件夹需要的权限，可能会因为不正确，例如需要mysql权限，但却是root，导致数据库异常。
3. 2. 主从复制，需要关注从库，这两个参数值，需要均为YES，出现NO，则可以检索错误日志，进一步定位。
4. Slave_IO_Running: Yes
5. Slave_SQL_Running: Yes