mysql5.6搭建主从复制

2018-06-18 01:18:16来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

使用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. mysql> grant replication slave on *.* to 'slave'@'1.1.1.2' identified by 'root';
    2. Query OK, 0 rows affected (0.00 sec)

 

  1. 编辑my.cnf配置文件,设置主库server-id=1,定义需要复制的库为test,忽略mysql数据库
  1. [root@vm-kvm10000-app mysql]# vi /etc/my.cnf
  2. [mysqld]
  3. server-id=1
  4. log-bin=mysql-bin
  5. binlog_do_db=test
  6. binlog_ignore_db=mysql

 

  1. 重启主库MySQL服务,
  1. [root@vm-kvm10000-app mysql]# service mysql restart
  2. Shutting down MySQL.. SUCCESS!
  3. Starting MySQL. SUCCESS!

 

  1. 看一下主库状态,
  1. mysql> show master status;
  2. +------------------+----------+--------------+------------------+-------------------+
  3. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  4. +------------------+----------+--------------+------------------+-------------------+
  5. | mysql-bin.000002 |      120 | test         | mysql            |                   |
  6. +------------------+----------+--------------+------------------+-------------------+
  7. 1 row in set (0.00 sec)

 

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

 

  1. 从库,编辑my.cnf配置,设置server-id=2,区别于主库,
  1. [root@vm-kvm10001-app mysql]# vi /etc/my.cnf
  2. [mysqld]
  3. server-id=2

 

  1. 重启MySQL服务,
  1. [root@vm-kvm10001-app mysql]# service mysql restart
  2. Shutting down MySQL.. SUCCESS!
  3. Starting MySQL. SUCCESS!

 

  1. 设置主库信息,
  1. 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;
  2. Query OK, 0 rows affected, 2 warnings (0.03 sec)

 

  1. 检索从库状态,
  1. mysql> show slave status \G;
  2. *************************** 1. row ***************************
  3. Slave_IO_State:
  4. Master_Host: 10.221.3.129
  5. Master_User: slave
  6. Master_Port: 3306
  7. Connect_Retry: 10
  8. Master_Log_File: mysql-bin.000001
  9. Read_Master_Log_Pos: 120
  10. Relay_Log_File: vm-kvm11853-app-relay-bin.000001
  11. Relay_Log_Pos: 4
  12. Relay_Master_Log_File: mysql-bin.000001
  13. Slave_IO_Running: No
  14. Slave_SQL_Running: No
  15. Replicate_Do_DB:
  16. Replicate_Ignore_DB:
  17. Replicate_Do_Table:
  18. Replicate_Ignore_Table:
  19. Replicate_Wild_Do_Table:
  20. Replicate_Wild_Ignore_Table:
  21. Last_Errno: 0
  22. Last_Error:
  23. Skip_Counter: 0
  24. Exec_Master_Log_Pos: 120
  25. Relay_Log_Space: 120
  26. Until_Condition: None
  27. Until_Log_File:
  28. Until_Log_Pos: 0
  29. Master_SSL_Allowed: No
  30. Master_SSL_CA_File:
  31. Master_SSL_CA_Path:
  32. Master_SSL_Cert:
  33. Master_SSL_Cipher:
  34. Master_SSL_Key:
  35. Seconds_Behind_Master: NULL
  36. Master_SSL_Verify_Server_Cert: No
  37. Last_IO_Errno: 0
  38. Last_IO_Error:
  39. Last_SQL_Errno: 0
  40. Last_SQL_Error:
  41. Replicate_Ignore_Server_Ids:
  42. Master_Server_Id: 0
  43. Master_UUID:
  44. Master_Info_File: /var/lib/mysql/master.info
  45. SQL_Delay: 0
  46. SQL_Remaining_Delay: NULL
  47. Slave_SQL_Running_State:
  48. Master_Retry_Count: 86400
  49. Master_Bind:
  50. Last_IO_Error_Timestamp:
  51. Last_SQL_Error_Timestamp:
  52. Master_SSL_Crl:
  53. Master_SSL_Crlpath:
  54. Retrieved_Gtid_Set:
  55. Executed_Gtid_Set:
  56. Auto_Position: 0
  57. 1 row in set (0.00 sec)

 

  1. 其中最重要的就是,这两个参数,要求YES,但此处为NO,
  1. Slave_IO_Running: No
  2. Slave_SQL_Running: No

 

  1. 检索错误日志,提示无法找见./performance_schema/cond_instances.frm文件,
  1. [root@vm-kvm10001-app mysql]# vm-kvm10001-app.err
  2. 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. [root@vm-kvm10001-app mysql]# service mysql restart
  2. Shutting down MySQL.... SUCCESS!
  3. Starting MySQL. SUCCESS!

 

  1. 再看从库状态,
  1. mysql> show slave status \G
  2. *************************** 1. row ***************************
  3. ...
  4. Slave_IO_Running: Yes
  5. Slave_SQL_Running: Yes
  6. ...

 

  1. 主库导出数据,用于导入从库,首先需要设置读锁,避免数据不一致,
  1. mysql> flush tables with read lock;

 

  1. mysql> show master logs;
  2. +------------------+-----------+
  3. | Log_name         | File_size |
  4. +------------------+-----------+
  5. | mysql-bin.000001 |       143 |
  6. | mysql-bin.000002 |       222 |
  7. | mysql-bin.000003 |       545 |
  8. +------------------+-----------+
  9. 3 rows in set (0.00 sec)

 

  1. 执行mysqldump将test库,导出test.sql文件,
  1. [root@vm-kvm10000-app mysql]# mysqldump -uroot -p -B test > test.sql

 

  1. 然后解锁表,
  1. mysql> unlock tables;

 

  1. 从库执行导入,
  1. [root@vm-kvm10001-app mysql]# mysql -uroot -p < test.sql

 

  1. 要确保从库,这两个值正确,
  1. Slave_IO_Running: Yes    
  2. Slave_SQL_Running: Yes

 

  1. 此时就完成了主从复制,向主库插入一条记录,
  1. mysql> INSERT INTO test
  2.     -> (id, name)
  3.     -> VALUES
  4.     -> (1, "a");
  5. Query OK, 1 rows affected (0.00 sec)

 

  1. 从库中可以检索出来,
  1. mysql> select * from test;
  2. +-------+--------+
  3. |     id    |  name  |
  4. +-------+--------+
  5. |      1    |     a      |
  6. +-------+--------+

 

 

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

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:连接mysql数据库报错java.sql.SQLException: The server time zo

下一篇:MySQL的常用命令