MySQL 5.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; #由于从库随时会提升成主库，不能写在配置文件里 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日志。 转自:http://blog.itpub.net/22996654/viewspace-2673361/

MySQL 5.7主从不停机添加新从库

标签：replicat   replicate   localhost   int   run   errno   tin   trie   delay