MySQL主从同步
实验环境：CentOS_7，MySQL_5.7
一、安装mysql
参考前篇：CentOS7 yum 安装 MySQL
二、配置主从服务器
主服务器配置
1.vi /etc/mycnf
修改参数如下
1.log_bin=mysql_bin 开启二进制人间
2.server_id=1 设置服务器ID
重启master
1.systemctl restart mysqld
1.mysql> create user backup@'192.168.121.22' identified by '775120@Lai';
2.mysql> grant replication slave on *.* to backup@'192.168.121.22';
3.mysql> flush privileges; (这一步是必须的）
* 查看配置状况*
1.mysql> show grants for backup@192.168.121.22;
2.+-------------------------------------------------------------+
3.| Grants for backup@192.168.121.22 |
4.+-------------------------------------------------------------+
5.| GRANT REPLICATION SLAVE ON *.* TO 'backup'@'192.168.121.22' |
6.+-------------------------------------------------------------+
7.1 row in set (0.00 sec)
1.show msater status;
1.mysql> show master status;
2.+------------------+----------+--------------+------------------+-------------------+
3.| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
4.+------------------+----------+--------------+------------------+-------------------+
5.| mysql-bin.000006 | 154 | | | |
6.+------------------+----------+--------------+------------------+-------------------+
7.1 row in set (0.00 sec)
1.vim /etc/mycnf
修改如下参数
1.log_bin=mysql-bin
2.server-id=2
3.relay_log=mysql-relay-bin 中继日志
4.log_slave_updates=1 将复制事件写进自己的二进制日志
5.read_only=1 只读
说明：
1. slave没有必要开启二进制日志，但是在一些情况下，必须设置，例如，如果slave为其它slave的master，必须设置bin_log
2. 开启了slave的二进制日志，却没有设置log_slave_updates，然后查看slave的数据是否改变，这是一种错误的配置
3. read_only，它防止改变数据(除了特殊的线程)
重启slave
1.systemctl restart mysqld
1.mysql> change master to master_host='192.168.121.21',
2. -> master_user='backup',
3. -> master_password='775120@Lai',
4. -> master_log_file='mysql-bin.000006',
5. -> master_log_pos=0;
6.Query OK, 0 rows affected, 2 warnings (0.01 sec)
注意：MASTER_LOG_POS的值为0，因为它是日志的开始位置。
1.mysql> start slave;
2.Query OK, 0 rows affected (0.00 sec)
1.mysql> show slave status\G;
2.*************************** 1. row ***************************
3. Slave_IO_State: Connecting to master
4. Master_Host: 192.168.121.21
5. Master_User: backup
6. Master_Port: 3306
7. Connect_Retry: 60
8. Master_Log_File: mysql-bin.000006
9. Read_Master_Log_Pos: 4
10. Relay_Log_File: mysql-relay-bin.000001
11. Relay_Log_Pos: 4
12. Relay_Master_Log_File: mysql-bin.000006
13. Slave_IO_Running: Connecting
14. Slave_SQL_Running: Yes
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: 4
25. Relay_Log_Space: 154
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: 2003
38. Last_IO_Error: error connecting to master 'backup@192.168.121.21:3306' - retry-time: 60 retries: 1
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: Slave has read all relay log; waiting for more updates
48. Master_Retry_Count: 86400
49. Master_Bind:
50. Last_IO_Error_Timestamp: 170904 11:22:33
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. Replicate_Rewrite_DB:
58. Channel_Name:
59. Master_TLS_Version:
60.1 row in set (0.00 sec)
1.Slave_IO_Running: Connecting
此处显示连不上master
排除账号，密码问题，应该是网络问题
设置iptables,开放局域网访问权限
1.iptables -I INPUT -s 192.168.121.0/24 -j ACCEPT
2.iptables -I OUTPUT -d 192.168.121.0/24 -j ACCEPT
其中192.168.121.0/24，这个是子网的表达方式，后期会更多的说明
再查看slave状态
1.mysql> show slave status\G;
2.*************************** 1. row ***************************
3. Slave_IO_State: Waiting for master to send event
4. Master_Host: 192.168.121.21
5. Master_User: backup
6. Master_Port: 3306
7. Connect_Retry: 60
8. Master_Log_File: mysql-bin.000006
9. Read_Master_Log_Pos: 154
10. Relay_Log_File: mysql-relay-bin.000002
11. Relay_Log_Pos: 367
12. Relay_Master_Log_File: mysql-bin.000006
13. Slave_IO_Running: Yes
14. Slave_SQL_Running: Yes
1.Slave_IO_Running: Yes
2.Slave_SQL_Running: Yes
表明配置成功
三、测试
1、在master上创建新的数据库
schllo
1.mysql> create database schllo;
2.Query OK, 1 row affected (0.00 sec)
slave上查看数据库状况
1.mysql> show databases;
2.+--------------------+
3.| Database |
4.+--------------------+
5.| information_schema |
6.| mysql |
7.| performance_schema |
8.| schllo |
9.| sys |
10.+--------------------+
11.5 rows in set (0.01 sec)
2、master上删除数据库
schllo
1.mysql> drop database schllo;
2.Query OK, 0 rows affected (0.00 sec)
1.slave上查看数据库状况
2.mysql> show databases;
3.+--------------------+
4.| Database |
5.+--------------------+
6.| information_schema |
7.| mysql |
8.| performance_schema |
9.| sys |
10.+--------------------+
11.4 rows in set (0.00 sec)