使用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) mysql> grant replication slave on *.* to 'slave'@'1.1.1.2' identified by 'root'; Query OK, 0 rows affected (0.00 sec)
编辑my.cnf配置文件，设置主库server-id=1，定义需要复制的库为test，忽略mysql数据库
[root@vm-kvm10000-app mysql]# vi /etc/my.cnf [mysqld] server-id=1 log-bin=mysql-bin binlog_do_db=test binlog_ignore_db=mysql
重启主库MySQL服务，
[root@vm-kvm10000-app mysql]# service mysql restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS!
看一下主库状态，
mysql> show master status; ------------------ ---------- -------------- ------------------ ------------------- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | ------------------ ---------- -------------- ------------------ ------------------- | mysql-bin.000002 | 120 | test | mysql | | ------------------ ---------- -------------- ------------------ ------------------- 1 row in set (0.00 sec) 或者 mysql> show master status G *************************** 1. row *************************** File: mysql-bin.000002 Position: 120 Binlog_Do_DB: test Binlog_Ignore_DB: mysql Executed_Gtid_Set: 1 row in set (0.00 sec)
从库，编辑my.cnf配置，设置server-id=2，区别于主库，
[root@vm-kvm10001-app mysql]# vi /etc/my.cnf [mysqld] server-id=2
重启MySQL服务，
[root@vm-kvm10001-app mysql]# service mysql restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS!
设置主库信息，
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; Query OK, 0 rows affected, 2 warnings (0.03 sec)
检索从库状态，
mysql> show slave status G; *************************** 1. row *************************** Slave_IO_State: Master_Host: 10.221.3.129 Master_User: slave Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 120 Relay_Log_File: vm-kvm11853-app-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: No Slave_SQL_Running: No 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: 120 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: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 Master_UUID: Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: 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)
其中最重要的就是，这两个参数，要求YES，但此处为NO，
Slave_IO_Running: No Slave_SQL_Running: No
检索错误日志，提示无法找见./performance_schema/cond_instances.frm文件，
[root@vm-kvm10001-app mysql]# vm-kvm10001-app.err 2017-08-29 16:10:37 22933 [ERROR] /usr/sbin/mysqld: Can't find file: './performance_schema/cond_instances.frm' (errno: 13 - Permission denied)
看一下其目录，发现performance_schema文件夹，是root权限，mysql用户无法访问，因此需要修改一下其权限，
[root@vm-kvm10001-app mysql]# ls -rlht total 109M ... drwx------ 2 root root 4.0K Aug 29 15:05 performance_schema ... [root@vm-kvm10001-app mysql]# chown -R mysql:mysql * [root@vm-kvm10001-app mysql]# ls -rlht total 109M ... drwx------ 2 mysql mysql 4.0K Aug 29 15:05 performance_schema ...
重启服务，
[root@vm-kvm10001-app mysql]# service mysql restart Shutting down MySQL.... SUCCESS! Starting MySQL. SUCCESS!
再看从库状态，
mysql> show slave status G *************************** 1. row *************************** ... Slave_IO_Running: Yes Slave_SQL_Running: Yes ...
主库导出数据，用于导入从库，首先需要设置读锁，避免数据不一致，
mysql> flush tables with read lock; mysql> show master logs; ------------------ ----------- | Log_name | File_size | ------------------ ----------- | mysql-bin.000001 | 143 | | mysql-bin.000002 | 222 | | mysql-bin.000003 | 545 | ------------------ ----------- 3 rows in set (0.00 sec)
执行mysqldump将test库，导出test.sql文件，
[root@vm-kvm10000-app mysql]# mysqldump -uroot -p -B test > test.sql
然后解锁表，
mysql> unlock tables;
从库执行导入，
[root@vm-kvm10001-app mysql]# mysql -uroot -p < test.sql
要确保从库，这两个值正确，
Slave_IO_Running: Yes Slave_SQL_Running: Yes
此时就完成了主从复制，向主库插入一条记录，
mysql> INSERT INTO test -> (id, name) -> VALUES -> (1, "a"); Query OK, 1 rows affected (0.00 sec)
从库中可以检索出来，
mysql> select * from test; ------- -------- | id | name | ------- -------- | 1 | a | ------- --------
总结： 1. MySQL相关文件、文件夹需要的权限，可能会因为不正确，例如需要mysql权限，但却是root，导致数据库异常。
2. 主从复制，需要关注从库，这两个参数值，需要均为YES，出现NO，则可以检索错误日志，进一步定位。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
