CentOS7下MySQL主从Master-Slaves同步配置
环境说明
Centos版本: CentOS Linux release 7.7.1908 (Core)
Linux连接工具:SecureCRT
MySQL Version: 5.7.28
主库主机地址:192.168.163.61
从库主机地址:192.168.163.62
参考文档:https://dev.mysql.com/doc/refman/5.7/en/replication.html
为什么要做主从同步
主从配置原理
配置主从同步
1.1 配置主服务器
- 修改主服务器配置文件/etc/my.cnf
#启用二进制日志记录 :主服务器基于二进制日志文件位置的复制
log-bin=mysql-bin
#每个服务器必须配置有唯一的服务器ID,实战中,通常配置为IP的最后一个数字
server-id=61
#指定可以主从同步的数据库,多数据库要配置多行
binlog-do-db=db_consumer
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=orders
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
- 重启MySQL服务
[root@localhost ~]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
- 查看主服务器当前二进制日志名和偏移量,从数据库启动的时候,从这个点开始进行数据恢复
#查看主服务器的状态
mysql> show master status \G;
*************************** 1. row ***************************
File: mysql-bin.000008 -------- 二进制日志名称
Position: 154 -------- 偏移量
Binlog_Do_DB: db_consumer -------- 要进行主从复制的数据库
Binlog_Ignore_DB: information_schema,mysql,orders,performance_schema,sys -------- 不进行主从复制的数据库
Executed_Gtid_Set: -------- #TODO
- 在主服务器上创建一个允许从数据库来访问的用户,并赋予权限
#创建用户
mysql>CREATE USER 'repl'@'192.168.163.62' IDENTIFIED BY 'Root2020.';
Query OK, 0 rows affected (0.02 sec)
#给用户赋予权限
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.163.62';
Query OK, 0 rows affected (0.02 sec)
#刷新
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
- 对主服务器的数据进行备份
第二步:从库数据库配置
- 修改从服务器配置文件/etc/my.cnf
#每个服务器必须配置有唯一的服务器ID,实战中,通常配置为IP的最后一个数字
server-id=62
- 重启MySQL服务
[root@localhost ~]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
- 在从服务器上设置主服务器配置信息
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.163.61',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='Root2020.',
-> MASTER_LOG_FILE='mysql-bin.000008',
-> MASTER_LOG_POS=515;
#错误原因是:当前slave进程没有停止需要先停止
ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL '' first.
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.163.61',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='Root2020.',
-> MASTER_LOG_FILE='mysql-bin.000008',
-> MASTER_LOG_POS=515;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
- 启动从服务器salve进程,查看从服务器slave的进程状态
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
# Slave_IO_Running: Yes 和 Slave_SQL_Running: Yes 状态都为YES才可以
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.163.61
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 515
Relay_Log_File: localhost-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
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: 515
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: 2003
Last_IO_Error: error connecting to master 'repl@192.168.163.61:3306' - retry-time: 60 retries: 1
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID: 6e8275ca-2708-11ea-b43e-000c29e7ba58
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: 200102 10:51:31
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)
第三步:从服务器同步主服务器上的历史数据
- 停止主数据的的更新操作,以保证数据的一致性
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.02 sec)
- 备份数据库
#用mysqldump命令备份数据库,参考:https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html文档
[root@localhost ~]# mysqldump -u root -p orders > /home/backup/mysql/orders-202012.sql
Enter password:
[root@localhost ~]# cd /home/backup/mysql/
[root@localhost mysql]# ls
orders-202012.sql
#将备份的文件发送到从服务器
[root@localhost mysql]# scp orders-202012.sql 'root'@'192.168.163.62':/home/backup/mysql/
root@192.168.163.62's password:
orders-202012.sql
- 在冲服务器上执行备份文件
[root@localhost mysql]# mysql -u root -p orders < orders-202012.sql
Enter password:
第四步:主库中添加数据库&表 查看从数据库是否同步显示
第五步:可能会遇到的问题
5.1 Could not find first log file name in binary log index file
- 查看从服务器上slave显示状态,查看报错信息
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.163.61
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000011
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000011
#这个地方显示状态是:NO
Slave_IO_Running: No
Slave_SQL_Running: Yes
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: 154
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: 1236
Last_IO_Error: Got fatal error 1236 from master when reading
#错误信息:不能再主服务器上找到日志文件
data from binary log: 'Could not find first log file name in binary log index file'
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 61
Master_UUID: 6e8275ca-2708-11ea-b43e-000c29e7ba58
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: 200102 11:54:18
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:
- 切换到从服务器,停止slave进程
mysql> stop slave;
Query OK, 0 rows affected (0.06 sec)
- 切换到主服务器查看主服务状态
mysql> show master status;
File:mysql-bin.000012
Position: 154
Binlog_Do_DB :db_consumer,orders
Binlog_Ignore_DB :information_schema,mysql,performance_schema,sys\
1 row in set (0.00 sec)
#刷新binlog日志
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)
再次查看master状态:
mysql> show master status;
File:mysql-bin.000013
Position: 154
Binlog_Do_DB :db_consumer,orders
Binlog_Ignore_DB :information_schema,mysql,performance_schema,sys\
1 row in set (0.00 sec)
-切换到从服务器,修改从服务器日志读取信息
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000013',MASTER_LOG_POS=154;
Query OK, 0 rows affected (0.01 sec)
#重启slave进程
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
#查看slave状态
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.163.61
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000013
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000013
#状态变成YES
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
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: 154
Relay_Log_Space: 531
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: 61
Master_UUID: 6e8275ca-2708-11ea-b43e-000c29e7ba58
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)