mysql 添加第N個從庫

某項目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 status\G; 
*************************** 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 
Empire CMS,phome.net
                                       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/ 
Empire CMS,phome.net
                         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 status\G;            #顯示slave 狀態
*************************** 1. row *************************** 
Empire CMS,phome.net
                         
               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: 
Empire CMS,phome.net
                                       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 status\G; 
*************************** 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 
Empire CMS,phome.net
                         
            Slave_SQL_Running: Yes 

新從庫2

mysql> show slave status\G; 
*************************** 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的數據！