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;

#由于從庫隨時會提升成主庫，不能寫在配置文件里

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主從添加新從庫的方法”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！