MySQL的master上增加Slave（2）

基于上篇文章，在master上继续挂载一个slave2到master上，可以有两钟方式，一个是直接挂载到master上，二是先从slave1获得数据库备份和master的日志文件和节点，然后再配置slave2和master关联。正常情况下，master一直在运行，为了不影响master的业务运行，我这里选择方法二。

图片来自 OReilly.MySQL.High.Availability.Tools.for.Building.Robust.Data.Centers.2nd.Edition

---

1.准备slave2服务器

主机名改为slave2，IP 为 192.168.0.112

[root@slave2 ~]# hostname slave2 [root@slave2 ~]# ifconfig eth0|grep addr eth0 Link encap:Ethernet HWaddr 00:0C:29:35:E0:35 inet addr:192.168.0.112 Bcast:192.168.0.255 Mask:255.255.255.0 inet6 addr: fe80::20c:29ff:fe35:e035/64 Scope:Link [root@slave2 ~]#

2.配置slave2 的my.cnf

log_bin=mysql-bin server-id=112

重启mysql 服务

[root@slave2 ~]# /etc/init.d/mysqld restart Stopping mysqld: [ OK ] Starting mysqld: [ OK ]

3.对slave1操作

停止slave1，并备份数据

##停止slave mysql> stop slave; Query OK, 0 rows affected, 1 warning (0.00 sec) ##查询当前master的日志和pos位置 （Relay_Master_Log_File: mysql-bin.000006 Exec_Master_Log_Pos: 304） mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.0.110 Master_User: dbbackup Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000006 Read_Master_Log_Pos: 304 Relay_Log_File: slave1-relay-bin.000005 Relay_Log_Pos: 517 Relay_Master_Log_File: mysql-bin.000006 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: 304 Relay_Log_Space: 891 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: ## mysql> Flush tables with read lock; ##备份数据库 [root@slave1 tmp]# mysqldump --all-databases -uroot -p > backup.sql ## mysql> unlock tables; Query OK, 0 rows affected (0.00 sec) ##上传备份数据到slave2服务器/tmp下 [root@slave1 tmp]# scp backup.sql root@192.168.0.112:/tmp The authenticity of host '192.168.0.112 (192.168.0.112)' can't be established. RSA key fingerprint is 7a:ba:3c:38:67:1a:b5:1b:de:25:5e:6a:cc:c3:8b:ed. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.0.112' (RSA) to the list of known hosts. root@192.168.0.112's password: backup.sql 100% 758KB 757.9KB/s 00:00 [root@slave1 tmp]#

4.配置slave2

##还原数据库 [root@slave2 tmp]# mysqldump --all-databases -uroot -p < backup.sql 或者 [root@slave2 tmp]# mysql -uroot -p< backup.sql ##在slave1上show slave status\G 中找到 Relay_Master_Log_File: mysql-bin.000006 Exec_Master_Log_Pos: 304

然后在slave2上执行

##配置slave2 到 master mysql> change master to -> master_host='192.168.0.110', -> master_port=3306, -> master_user='dbbackup', -> master_password='mysql.password', -> master_log_file='mysql-bin.000006', -> master_log_pos=304; Query OK, 0 rows affected, 2 warnings (0.06 sec) ##启动slave mysql> start slave; Query OK, 0 rows affected (0.04 sec) ##查看slave状态 mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.110 Master_User: dbbackup Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000006 Read_Master_Log_Pos: 304 Relay_Log_File: slave2-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000006 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table:

5.测试

备注：第3步中我们把slave1停掉了，记得开启

mysql>start slave;

在master上对数据库进行操作，观察slave1和slave2是否都同步成功。