MYSQL 主從添加新從庫

MySQL 主從復制，不停機添加新從節點

1、主庫創建賬號：

show master status;
GRANT REPLICATION SLAVE ON . to 'reader'@'%' identified by 'readerpwd';
flush privilegs

2、從庫配置

開啟binlog

log-bin=/var/lib/mysql/mysql-bin
server-id=3 //參照原從庫配置+1

3、備份主庫

mysqldump -uroot -p123 --routines --single_transaction --master-data=2 --databases testdb > testdb.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> grant all pricileges on *.* to testdb.* identified by 'testdb';
mysql> create database testdb;
mysql> source /tmp/testdb.sql

5、查看備份文件的binlog 和 pos值

# head -25 testdb.sql
root@mysql20151:/tmp# head -25 /tmp/0907.sql 
-- MySQL dump 10.13  Distrib 5.5.46, for debian-linux-gnu (x86_64)
--
-- Host: localhost    Database: vphotos
-- ------------------------------------------------------
-- Server version       5.5.46-0ubuntu0.14.04.2-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.003789', MASTER_LOG_POS=49778941;

可以看到 MASTER_LOG_FILE='mysql-bin.003789', MASTER_LOG_POS=49778941;

6、啟動從庫

mysql> change master to master_host='10.*.*.*',master_user='reader',master_password='readerpwd',master_log_file='mysql-bin.003789',master_log_pos=49778941;
// 驗證從庫狀態
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.3.16.7
                  Master_User: slave02
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.003791
          Read_Master_Log_Pos: 99002276
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.003789
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
          ..................

注:看到IO和SQL線程均為YES，說明主從配置成功。

參考：

https://yq.aliyun.com/articles/38826