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

浅浅的花香味﹌ 2022-07-15 07:17 83阅读 0赞

在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>

[Establish Replication]: http://mysql.wingtiplabs.com/documentation/feigirh5/establish-replication