Mysql集群的HA原理及配置指南之添加slave（三）

在Mysql中，一个Master可以连接多个slave，这篇文章讲的就是在现有的master-slaves基础上通过拷贝slave的方式往集群中添加slave；

新添加的slave其实是挂在老的slave上的，这样减少了多台slave与master上的数据同步负载

One MySQL server can be the replication master to lots of slaves.

Having lots of slaves can be useful for scaling up your ability to handle lots of read traffic. The master replicates the same data out to all of the slaves, and you can load balance read traffic across all the slaves.

Note that this kind of replication doesn't help at all with scaling up your write capacity: the slaves shouldn't accept changes, because those changes won't propagate back to the master or to the other slaves. (Remember, replication is one-way.)

To Establish Replication we built our first slave using a backup from an existing master. That's not always practical, especially if the master is under heavy load.

In this procedure, we'll create a second slave using an existing slave. This is typical in very large deployments, especially if the slaves are all reached through a load balancer that can gracefully handle one slave being temporarily taken out of service.

Start:

1.先检测现有的slave和Master的通信是否正常

master ~ $ mysql -u root
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.29-log MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> INSERT INTO important.stuff SET details = "Replicating from master to old_slave";
Query OK, 1 row affected (0.03 sec)

mysql>

old_slave ~ $ mysql -u root
mysql> select * from important.stuff;
+----+--------------------------------------+---------------------+
| id | details                              | happened            |
+----+--------------------------------------+---------------------+
|  1 | Replicating from master to old_slave | 2013-04-15 22:19:07 |
+----+--------------------------------------+---------------------+
2 rows in set (0.00 sec)

mysql>

2.暂停slave上的mysql：

old_slave ~ $ sudo service mysqld stop
Stopping mysqld:                                           [  OK  ]
old_slave ~ $

3.对slave上的数据进行备份：

old_slave ~ $ sudo tar -czf /tmp/slave.tar.gz -C /var/lib/mysql/ .
old_slave ~ $ 

4.重启服务：

old_slave ~ $ sudo service mysqld start
Starting mysqld:                                           [  OK  ]
old_slave ~ $ 

5.登录新的一台slave，并配置mysql的配置文件：

new_slave ~ $ sudoedit /etc/my.cnf

[mysqld]
server_id = 30
log_bin = mysql-bin
log_slave_updates = 1
relay_log = mysql-relay-bin
read_only = 1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

6.重启mysql服务使配置生效

new_slave ~ $ sudo service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
new_slave ~ $ 

7.停止服务：

new_slave ~ $ sudo service mysqld stop
Stopping mysqld:                                           [  OK  ]
new_slave ~ $ 

8.导入mysql数据并重启服务：

new_slave ~ $ sudo tar -zxf /tmp/slave.tar.gz -C /var/lib/mysql/
new_slave ~ $ sudo service mysqld start
Starting mysqld:                                           [  OK  ]
new_slave ~ $ 

9.检查备份状态：

new_slave ~ $ mysql -u root
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master.example.com
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1306
               Relay_Log_File: mysql-relay-bin.000004
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000001
             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: 1306
              Relay_Log_Space: 409
              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:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 10
1 row in set (0.00 sec)

mysql>

10.在master上插入数据进行HA测试：

master ~ $ mysql -u root
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.29-log MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> INSERT INTO important.stuff SET details = "Replicating from master to both slaves";
Query OK, 1 row affected (0.03 sec)

mysql>

mysql> SELECT * FROM important.stuff ORDER BY id DESC LIMIT 1;
+----+----------------------------------------+---------------------+
| id | details                                | happened            |
+----+----------------------------------------+---------------------+
|  5 | Replicating from master to both slaves | 2013-04-15 22:34:39 |
+----+----------------------------------------+---------------------+
1 row in set (0.00 sec)

mysql>