mysql 添加第N个从库

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


老从库1 相关操作:

#1 停止 mysql从库,锁表,
     记住 Read_Master_Log_Pos: 与 Master_Log_File: (红色字)

1. mysql> stop slave; 
2. mysql> flush tables with read lock; 
3. mysql> show slave status\G; 
4. *************************** 1. row *************************** 
5.                Slave_IO_State: 
6.                   Master_Host: 192.168.6.53 
7.                   Master_User: dongnan 
8.                   Master_Port: 3306 
9.                 Connect_Retry: 60 
10.               Master_Log_File: mysql-bin.000097 
11.           Read_Master_Log_Pos: 19157395 
12.                Relay_Log_File: zabbix-slave-relay-bin.000185 
13.                 Relay_Log_Pos: 11573578 
14.         Relay_Master_Log_File: mysql-bin.000097 
15.              Slave_IO_Running: No 
16.             Slave_SQL_Running: No 
17.               Replicate_Do_DB: 
18.           Replicate_Ignore_DB: 
19.            Replicate_Do_Table: 
20.        Replicate_Ignore_Table: 
21.       Replicate_Wild_Do_Table: 
22.   Replicate_Wild_Ignore_Table: 
23.                    Last_Errno: 0 
24.                    Last_Error: 
25.                  Skip_Counter: 0 
26.           Exec_Master_Log_Pos: 19157395 
27.               Relay_Log_Space: 19142103 
28.               Until_Condition: None 
29.                Until_Log_File: 
30.                 Until_Log_Pos: 0 
31.            Master_SSL_Allowed: No 
32.            Master_SSL_CA_File: 
33.            Master_SSL_CA_Path: 
34.               Master_SSL_Cert: 
35.             Master_SSL_Cipher: 
36.                Master_SSL_Key: 
37.         Seconds_Behind_Master: NULL 
38. Master_SSL_Verify_Server_Cert: No 
39.                 Last_IO_Errno: 0 
40.                 Last_IO_Error: 
41.                Last_SQL_Errno: 0 
42.                Last_SQL_Error: 
43. 1 row in set (0.00 sec) 
44.  
45. ERROR: 
46. No query specified 

#2 打包数据并发送到新从库2服务器上

1. cd /usr/local/mysql/var/ 
2. tar czvf zabbix_20110805.tar.gz zabbix/ 
3. scp zabbix_20110805.tar.gz root@192.168.6.54:/root 

新从库2相关操作:

#1 更改 server-id 值不能为1,因为master 的 server-id=1 

1. vim /etc/my.cnf  
2. 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

1. 
   
2. 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; 
3.  
4. mysql> slave start;  #启动slave
5.  
6. mysql> show slave status\G;  #显示slave 状态
7. *************************** 1. row *************************** 
8.                Slave_IO_State: Waiting for master to send event 
9.                   Master_Host: 192.168.6.53 
10.                   Master_User: dongnan 
11.                   Master_Port: 3306 
12.                 Connect_Retry: 60 
13.               Master_Log_File: mysql-bin.000097 
14.           Read_Master_Log_Pos: 21194359 
15.                Relay_Log_File: db1-relay-bin.000002 
16.                 Relay_Log_Pos: 2037215 
17.         Relay_Master_Log_File: mysql-bin.000097 
18.              Slave_IO_Running: Yes 
19.             Slave_SQL_Running: Yes 
20.               Replicate_Do_DB: 
21.           Replicate_Ignore_DB: 
22.            Replicate_Do_Table: 
23.        Replicate_Ignore_Table: 
24.       Replicate_Wild_Do_Table: 
25.   Replicate_Wild_Ignore_Table: 
26.                    Last_Errno: 0 
27.                    Last_Error: 
28.                  Skip_Counter: 0 
29.           Exec_Master_Log_Pos: 21194359 
30.               Relay_Log_Space: 2037368 
31.               Until_Condition: None 
32.                Until_Log_File: 
33.                 Until_Log_Pos: 0 
34.            Master_SSL_Allowed: No 
35.            Master_SSL_CA_File: 
36.            Master_SSL_CA_Path: 
37.               Master_SSL_Cert: 
38.             Master_SSL_Cipher: 
39.                Master_SSL_Key: 
40.         Seconds_Behind_Master: 0 
41. Master_SSL_Verify_Server_Cert: No 
42.                 Last_IO_Errno: 0 
43.                 Last_IO_Error: 
44.                Last_SQL_Errno: 0 
45.                Last_SQL_Error: 
46. 1 row in set (0.00 sec) 
47.  
48. ERROR: 
49. No query specified 

验证重库是否同步:

老从库1

1. mysql> slave start; 
2.  
3. mysql> show slave status\G; 
4. *************************** 1. row *************************** 
5.                Slave_IO_State: Waiting for master to send event 
6.                   Master_Host: 192.168.6.53 
7.                   Master_User: dongnan 
8.                   Master_Port: 3306 
9.                 Connect_Retry: 60 
10.               Master_Log_File: mysql-bin.000097 
11.           Read_Master_Log_Pos: 21194359 
12.                Relay_Log_File: db1-relay-bin.000002 
13.                 Relay_Log_Pos: 2037215 
14.         Relay_Master_Log_File: mysql-bin.000097 
15.              Slave_IO_Running: Yes 
16.             Slave_SQL_Running: Yes 

新从库2

1. mysql> show slave status\G; 
2. *************************** 1. row *************************** 
3.                Slave_IO_State: Waiting for master to send event 
4.                   Master_Host: 192.168.6.53 
5.                   Master_User: dongnan 
6.                   Master_Port: 3306 
7.                 Connect_Retry: 60 
8.               Master_Log_File: mysql-bin.000097 
9.           Read_Master_Log_Pos: 21194359 
10.                Relay_Log_File: db1-relay-bin.000002 
11.                 Relay_Log_Pos: 2037215 
12.         Relay_Master_Log_File: mysql-bin.000097 
13.              Slave_IO_Running: Yes 
14.             Slave_SQL_Running: Yes 

结束

既然主库不能动，那就去操作从库吧，新从库2拷贝老的从库1的数据！

本文转自 dongnan 51CTO博客，原文链接：http://blog.51cto.com/dngood/742875