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 ~]# hostnameslave2[root@slave2 ~]# ifconfig eth0|grep addreth0      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 restartStopping mysqld:                                           [  OK  ]Starting mysqld:                                           [  OK  ]

3.对slave1操作

停止slave1，并备份数据

##停止slavemysql> 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:/tmpThe 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)? yesWarning: 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 到 mastermysql> 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)##启动slavemysql> 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是否都同步成功。