MySQL主从复制配置
一路走来，因为不懂，一大堆问题，感谢网上帖子帮忙
环境CentOS 7.2, mysql 5.7
主 192.168.1.101
从 192.168.1.102
- 一. MySQL主服务器配置
1.建立用户
grant replication slave on *.* to rep1@192.168.1.102 identified by 'Welcome1';
# grant replication slave on *.* to ‘用户名’@'主机’ identified by ‘密码’;
# 可在B Slave上做连接测试: mysql -h 192.168.1.101 -urep1 -p
2.编辑配置文件/etc/my.cnf
# 确保有如下行
server-id = 1
log-bin=mysql-bin
binlog-do-db=test
binlog-ignore-db=mysql
#binlog-do-db=需要备份的数据库名，可写多行
#binlog-ignore-db=不需要备份的数据库名，可写多行
- 二.MySQL从服务器配置
编辑/etc/my.cnf
server-id=2
log-bin=mysql-bin
replicate-do-db=test
# replicate-do-db=test 需要备份的数据库名
# replicate-ignore-db=mysql 忽略的数据库
# master-connect-retry=60 如果从服务器发现主服务器断掉，重新连接的时间差(秒)
修改配置需要注意，需要删除从服务器上的/var/lib/mysql/master.info文件
[root@CentOS-2 ~]# rm -f /var/lib/mysql/master.info
[root@CentOS-2 ~]# /etc/init.d/mysql restart
如果启动失败，需要删除/var/lib/mysql(数据目录下)所有数据，然后用root登录mysql, 通过命令指定master
change master to master_host='192.168.1.101', master_user='rep1', master_password='Welcome1';
如果在从库上登录运行
mysql> show master status; ERROR 1227 (42000): Access denied; you need the SUPER,REPLICATION CLIENT privilege for this operation
可在主库上授权
mysql> select * from mysql.user where user='rep1';
grant super on *.* to rep1@192.168.1.102;
用root登录从服务器查看复制状态是否启动
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.1.101 Master_User: rep1 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: slaver1-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: test 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: 0 Relay_Log_Space: 154 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 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified
其中Slave_IO_Running 与 Slave_SQL_Running 的值都必须为YES，才表明状态正常。
在主库上查询
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 342 | test | mysql | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.09 sec)
在从库上执行
mysql> stop slave; Query OK, 0 rows affected, 1 warning (0.03 sec) mysql> change master to master_log_file='mysql-bin.000001',master_log_pos=342; Query OK, 0 rows affected (0.02 sec) mysql> start slave; Query OK, 0 rows affected (0.04 sec)
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.101 Master_User: rep1 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 342 Relay_Log_File: slaver1-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: test 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: 342 Relay_Log_Space: 529 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: 0 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: 1 Master_UUID: e373e210-c94c-11e7-aea0-080027e2ae0a Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates 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 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified
主库上建表测试
mysql> create database test; Query OK, 1 row affected (0.01 sec) mysql> use test; Database changed mysql> create table first_tb(id int(3),name char(10)); Query OK, 0 rows affected (0.04 sec) mysql> insert into first_tb values (001,'myself'); Query OK, 1 row affected (0.01 sec) mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | first_tb | +----------------+ 1 row in set (0.00 sec)
从库验证
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec) mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | first_tb | +----------------+ 1 row in set (0.00 sec) mysql> select * from first_tb; +------+--------+ | id | name | +------+--------+ | 1 | myself | +------+--------+ 1 row in set (0.00 sec)
