MySQL5.7主从添加新从库的办法

        本篇内容介绍了“MySQL5.7主从添加新从库的方法”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
 
        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;
 
#由于从库随时会提升成主库，不能写在配置文件里。