mysql数据库主从复制添加第N个从库

发布时间:2020-02-11编辑:脚本学堂
本文介绍下,在mysql主从复制中,为mysql数据库添加第N个从库的方法,感兴趣的朋友可以参考下。

本节内容:
添加mysql从库

mysql主从复制服务器 1主 1从 ,现在要添加一个mysql从服务器,要求主库不能停止服务,以前由于不是线上的服务器,可以在主服务器上 执行 flush tables with read lock 语句(锁表,只读),所有的表只能读不能写,然后再拷贝主库数据到新的从库服务器上,并保持数据一致性,现在只能换一种方法了,思路 新从库2拷贝老的从库1的数据!

老从库1 相关操作:
 

复制代码 代码示例:

#1 停止 mysql数据库的从库,锁表,
     记住 Read_Master_Log_Pos: 与 Master_Log_File: (红色字)
mysql> stop slave;
mysql> flush tables with read lock;
mysql> show slave statusG;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.6.53
                  Master_User: dongnan
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000097
          Read_Master_Log_Pos: 19157395
               Relay_Log_File: zabbix-slave-relay-bin.000185
                Relay_Log_Pos: 11573578
        Relay_Master_Log_File: mysql-bin.000097
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 19157395
              Relay_Log_Space: 19142103
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)
 
ERROR:
No query specified

#2 打包数据并发送到新从库2服务器上
cd /usr/local/mysql/var/
tar czvf zabbix_20110805.tar.gz zabbix/
scp zabbix_20110805.tar.gz root@192.168.6.54:/root

新从库2相关操作:
 

复制代码 代码示例:
#1 更改 server-id 值不能为1,因为master 的 server-id=1
vim /etc/my.cnf 
server-id = 3
#2 停止mysql数据库并解压缩到/usr/local/mysql/var/ 
tar zxvf zabbix_20110805.tar.gz -C /usr/local/mysql/var/
#3 启动mysql数据库并change master
Read_Master_Log_Pos 值  19157395
Master_Log_File 值 mysql-bin.000097
 
mysql> change master to master_host='192.168.6.53',master_user='dongnan',master_password='password',master_log_file='mysql-bin.000097',master_log_pos=19157395;
 
mysql> slave start;                    #启动slave
 
mysql> show slave statusG;            #显示slave 状态
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.6.53
                  Master_User: dongnan
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000097
          Read_Master_Log_Pos: 21194359
               Relay_Log_File: db1-relay-bin.000002
                Relay_Log_Pos: 2037215
        Relay_Master_Log_File: mysql-bin.000097
             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: 21194359
              Relay_Log_Space: 2037368
              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:
1 row in set (0.00 sec)
 
ERROR:
No query specified

验证重库是否同步:

老从库1
 

复制代码 代码示例:
mysql> slave start;
 
mysql> show slave statusG;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.6.53
                  Master_User: dongnan
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000097
          Read_Master_Log_Pos: 21194359
               Relay_Log_File: db1-relay-bin.000002
                Relay_Log_Pos: 2037215
        Relay_Master_Log_File: mysql-bin.000097
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

新从库2
 

复制代码 代码示例:
mysql> show slave statusG;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.6.53
                  Master_User: dongnan
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000097
          Read_Master_Log_Pos: 21194359
               Relay_Log_File: db1-relay-bin.000002
                Relay_Log_Pos: 2037215
        Relay_Master_Log_File: mysql-bin.000097
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
 

说明:
既然主库不能动,那就去操作从库,新从库2拷贝老的从库1的数据。

您可能感兴趣的文章:
mysql主从配置的三个错误及解决方法(图文)
mysql双机实现主从同步与双向同步
mysql 内存表在主从同步时的注意事项
mysql主从复制与负载均衡问题解答
配置mysql-5.1.35 主从复制