IT码农

从存在的slave中，复制新的从库

2017/07/26 mysql

#从已经存在的从库中，复制新的从库

方案一：

1.关闭从库

shell> mysqladmin shutdown
or
mysql > stop slave;

2.记录从库的日志文件及位置（ Relay_Master_Log_file 和 Exec_Master_Log_Pos）

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.1.11.5
                  Master_User: mysync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log-bin.000643
          Read_Master_Log_Pos: 646546587
               Relay_Log_File: szwg-m91-db-webmedia08-relay-bin.003212
                Relay_Log_Pos: 17401462
        Relay_Master_Log_File: log-bin.000643
             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: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 646546587
              Relay_Log_Space: 331974369
              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: 0
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: 59
1 row in set (0.00 sec)

3.mysqldump导出数据

mysqldump  --all-databases > backup_all.sql

4.导入数据到新的slave

source backup_all.sql;

5.配置同步信息

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='master_host_name',
    ->     MASTER_USER='replication_user_name',
    ->     MASTER_PASSWORD='replication_password',
    ->     MASTER_LOG_FILE='recorded_log_file_name',
    ->     MASTER_LOG_POS=recorded_log_position;

6.启动新的slave

START SLAVE;

7.启动刚关闭的slave

START SLAVE;

方案二：

1.关闭从库

shell> mysqladmin shutdown
or
STOP SLAVE SQL_THREAD

2.拷贝数据到新的从库 可以使用tar,zip压缩后进行拷贝,如使用cp,scp,rsync等进行复制 确保可以复制日志文件,relay log中继日志等文件

3.指定日志文件的位置和原来一样 –relay-log –relay-log-index

4.配置新的server-id

5.启动新的从库 START SLAVE

Search

    Table of Contents