共计 6217 个字符，预计需要花费 16 分钟才能阅读完成。
本篇内容介绍了“MySQL5.7 主从添加新从库的方法”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
MySQL 主从复制，不停机添加新从节点：
1、主库创建账号：
修改主库 repl 密码：
show master status;
alter user repl@ % identified by 123456
grant replication slave,replication client on *.* to repl @ %
flush privilegs;
2、从库配置（创建从库数据库过程简略）：
开启 binlog
[root@centos_TP data1]# cat /etc/my.cnf
[mysqld]
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
#user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
basedir=/usr/local/mysql
datadir=/data1/data
socket=/tmp/mysql.sock
port=3306
server-id =60182
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=performance_schema.%
replicate-wild-ignore-table=information_schema.%
replicate-wild-ignore-table=sys.%
log-bin = /data1/log/mysql-bin
binlog_format = MIXED
skip-slave-start = 1
expire_logs_days=3
#validate_password_policy=0
#validate_password_length=3
relay-log-index=/data1/log/mysql-relay
relay-log=/data1/log/mysql-relay
log-bin=/data1/log/mysql-bin
#log-error=log.err
explicit_defaults_for_timestamp=true
[mysqld_safe]
log-error=/data1/log/mysql.err
pid-file=/data1/tmp/mysqld.pid
初始化数据库：
正常初始化：
[root@centos_TP bin]# ./mysqld –defaults-file=/etc/my.cnf –initialize –user=mysql
2020-01-14T08:48:27.965207Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-01-14T08:48:28.175008Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-01-14T08:48:28.270192Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: a2408f8d-36aa-11ea-a1c6-00505695cefc.
2020-01-14T08:48:28.273709Z 0 [Warning] Gtid table is not ready to be used. Table mysql.gtid_executed cannot be opened.
2020-01-14T08:48:28.278708Z 1 [Note] A temporary password is generated for root@localhost: (,%E6LnwWrrq
指定初始化配置文件：
/usr/local/mysql/bin/mysqld –defaults-file=/etc/my.cnf –initialize –user=mysql
# 开启数据库
/usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my.cnf
# 登录数据库，修改 root 密码
mysql -p
之前初始化的密码
set sql_log_bin=0;
mysql alter user root@ localhost identified by 123456
mysql flush privileges;
set sql_log_bin=1;
增加 root 远程登录用户：
mysql create user root@ % identified by 123456
Query OK, 0 rows affected (0.01 sec)
mysql grant all privileges on *.* to root@ %
mysql flush privileges;
# 创建 slave 账号
mysql grant replication slave,replication client on *.* to repl @ % identified by 123456
# 在 slave 节点上执行
mysql set global read_only=1;
# 由于从库随时会提升成主库，不能写在配置文件里
3、备份主库：
[root@localhost dbdata]# mysqldump -uroot -p –routines –single_transaction –master-data=2 -B cat qc_bh all.sql
参数说明：
–routines：导出存储过程和函数
–single_transaction：导出开始时设置事务隔离状态，并使用一致性快照开始事务，然后 unlock tables; 而 lock-tables 是锁住一张表不能写操作，直到 dump 完毕。
–master-data：默认等于 1，将 dump 起始（change master to）binlog 点和 pos 值写到结果中，等于 2 是将 change master to 写到结果中并注释。
4、从库创建数据库，并导入数据
将 dump 的数据拷贝到从库后开始导数据
mysql
create database cat;
create database qc_bh;
mysql source /data1/all.sql
…
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
5、查看备份文件的 binlog 和 pos 值
[root@centos_TP data1]# head -25 all.sql
— MySQL dump 10.13 Distrib 5.7.20, for linux-glibc2.12 (x86_64)
—
— Host: localhost Database: cat
— ——————————————————
— Server version 5.7.20-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE= +00:00 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE= NO_AUTO_VALUE_ON_ZERO */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
—
— Position to start replication or point-in-time recovery from
—
— CHANGE MASTER TO MASTER_LOG_FILE= mysql-bin.000037 , MASTER_LOG_POS=621697642;
—
— Current Database: `cat`
可以看到 MASTER_LOG_FILE= mysql-bin.000037 , MASTER_LOG_POS=621697642;
6、启动从库
mysql change master to
– master_host= 192.168.60.181 ,
– master_user= repl ,
– master_password= 123456 ,
– master_log_file= mysql-bin.000037 ,
– master_log_pos=621697642;
mysql start slave;
Query OK, 0 rows affected (0.01 sec)
mysql show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Queueing master event to the relay log
Master_Host: 192.168.60.181
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000037
Read_Master_Log_Pos: 677960018
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 24887
Relay_Master_Log_File: mysql-bin.000037
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: mysql.%,performance_schema.%,information_schema.%,sys.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 621722209
Relay_Log_Space: 56262899
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: 6606
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: 60181
Master_UUID: a524c954-c8a8-11e9-8082-00505697e9db
Master_Info_File: /data1/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Reading event from the relay log
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.01 sec)
ERROR:
No query specified
显示：
看到 IO 和 SQL 线程均为 YES，说明主从配置成功。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Read_Master_Log_Pos: 677960018 表示一直在追 binlog 日志。
“MySQL5.7 主从添加新从库的方法”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站，丸趣 TV 小编将为大家输出更多高质量的实用文章！
