Mysql集群的HA原理及配置指南之主备模式（一）

落日映苍穹つ 2022-07-15 06:44 93阅读 0赞

最近手头上项目很多，所以博客这边耽误了2个月实在抱歉。

先说说最近搭项目的感受吧：

给一家做互联网金融的集团公司上私有云，方案用的都是主流的HA高可用方案，没有什么特别的，第一期是十台服务器（7台计算节点），现在部署了线上服务的虚拟机差不多接近60台，总体来说比较稳定，遇到过几个问题基本上都是对面的运维配交换机的小问题（其实也不能说小，但是部署调试完之后那边IDC的网络硬件我们根本碰不到）。

方案的最大分歧实在mysql数据库节点的存放问题，我比较倾向于将mysql搭建在3台控制节点上做主备方案，但是最终定下来的方案是放在3台虚拟机里面，到现在我还是不认可的。在我平时的开发及调试环境的时候，发现大量的connectio会导致mysql排队过多的情况，这还是有ssd的情况下。我也理解mysql搭在虚拟机的原因，不就是成本嘛，这个大家都懂（现在的架构师动不动就拿成本来做借口），但是玩过私有云的都知道，基本上最重要的就是做数据永久存储的数据库，rabbitmq down了只不过服务停了，修复的成本并不高，但是数据库一旦down了那就是整个环境要不要重新搭的问题了。虽然有三台虚拟机做主备，但虚拟机毕竟是虚拟机，虚拟机之间的通信本身相对物理机就有性能的损耗，反正我还是希望看到这篇文章的博友之后在给人家设计私有云方案的时候多多重视Mysql的高可用，别台相信虚拟机，更别太相信那些开源的所谓的很稳定的HA组件。

上述就是我写今天这篇mysql主备配置文章的原因。

**正文：**

1.mysql数据备份，为什么要做备份：

**Survive Failures** - Replication gives you an up-to-the-moment copy of the master database, on a server that's ready to take over when the inevitable crash happens. It is typically much faster to restore service using a slave than restoring from a backup.

**Scale Reads** - Many modern applications have a write-once, read-many access pattern. For example, a blog post will be inserted once and read many times. You can use replication to spread the much higher SELECT load over many*read-slaves*. The smaller write load (INSERTs, UPDATEs, DELETES) still has to run on the master.

**Distribute Data** - A master can replicate to slaves anywhere on the globe. You can use slaves to improve network latency serving customers. You can also use a distant replicated slave as a Business Continuity Plan in case the entire location housing the master server is offline.

**Run Intensive Reports** - Some queries take a huge amount of processor, memory, or disk to execute. Dedicated read slaves can distribute the impact of heavy reports.

总结来说就是数据库的高可用，master可以与多个slave进行数据的同步，但这里和别的方案不同的是，它可以做到slave连接slave，而不是所有的slave全部挂在master上面。

2.Start:

我这里用的mysql是percona发行版，至于为什么用这个，我只能说我用过来感觉这个比较稳定一点，相对mariadb更高级一点，大家当然也可以用别的，反正都是支持的。

（Percona发行版的下载地址 ：https://www.percona.com/downloads/）

先讲讲数据库的master-slave主备吧，原理这里就不细说了，大家看下面的图也能明白：

![Center][]

接下去就是进数据库配置了，先是master节点：

master ~ $ mysql -u root
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 3
    Server version: 5.5.29 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> CREATE USER 'replicator'@'%' IDENTIFIED BY 'TileLowNilWife';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicator'@'%';
    Query OK, 0 rows affected (0.00 sec)
                    
    mysql> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql>

##### After this step is completed: #####

1.  `replicator` can log into MySQL on the **master** from the **slave** with password `TileLowNilWife`

第一步就是对刚搭的master数据库创建slave用户，让slave节点可以通过特定的用户进行连接。

In order for this server to be a replication master, it needs `log_bin` enabled, and a `server_id` that is not the default (0).

Set those values by editing `/etc/my.cnf`:

接下去这一步比较重要，因为slave是通过mater的log进行分析从而同步数据的，所以我们需要将这个参数在/etc/my.cnf打开：

[mysqld]
    log_bin = mysql-bin
    server_id = 10
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    ......some content not shown

然后再重启数据库：

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

进mysql检查刚才的配置是否成功：

master ~ $ mysql -u root
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2
    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> SHOW variables LIKE 'server_id';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | server_id     | 10    |
    +---------------+-------+
    1 row in set (0.01 sec)
    
    mysql> SHOW variables LIKE 'log_bin';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | log_bin       | ON    |
    +---------------+-------+
    1 row in set (0.00 sec)
    
    mysql>

如果之前数据库中有数据，需要对其备份一下：

mysql> exit
    master ~ $ mysqldump -u root --single-transaction --all-databases --master-data=1 > /tmp/master_backup.sql 
    -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
    master ~ $

将备份的sql数据传到slave节点上：

master ~ $ scp /tmp/master_backup.sql slave.example.com:/tmp/
    The authenticity of host 'slave.example.com (10.242.58.189)' can't be established.
    RSA key fingerprint is 0f:47:42:f4:71:51:4c:a3:70:94:db:83:03:4c:d2:48.
    Are you sure you want to continue connecting (yes/no)? yes
    Warning: Permanently added 'slave.example.com,10.242.58.189' (RSA) to the list of known hosts.
    anonymous@slave.example.com's password: (input your password)
    master_backup.sql                           100%  501KB 501.3KB/s   00:00
    master ~ $

可以看到备份的数据产生了一个log file，slave即是根据该log file进行数据同步的：

master ~ $ egrep "^CHANGE MASTER" /tmp/master_backup.sql
    CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
    master ~ $

然后就是在slave上进行配置：

第一步是修改/etc/my.cnf：

slave ~ $ sudoedit /etc/my.cnf

[mysqld]
    server_id = 20
    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

打开一些配置项，再进行数据库重启：

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

接下去进mysql检查刚才的配置是否生效：

slave ~ $ mysql -u root
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2
    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> SHOW variables WHERE Variable_Name IN ("server_id", "log_bin", "relay_log", "log_slave_updates", "read_only");
    +-------------------+-----------------+
    | Variable_name     | Value           |
    +-------------------+-----------------+
    | log_bin           | ON              |
    | log_slave_updates | ON              |
    | read_only         | ON              |
    | relay_log         | mysql-relay-bin |
    | server_id         | 20              |
    +-------------------+-----------------+
    5 rows in set (0.00 sec)
    
    mysql>

导入master上之前的数据：

mysql> source /tmp/master_backup.sql
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 508 rows affected (0.02 sec)
    Records: 508  Duplicates: 0  Warnings: 0

Configure the **slave** with the network name of the **master** and the username and password for the replicator account.

This information, combined with the log file and position in the backup file, are all the **slave** needs to establish replication with the **master**.

这一步很关键，slave通过刚才master上建的用户进行对master连接，产生一个备份映射关系：

mysql> CHANGE MASTER TO MASTER_HOST='master.example.com', 
    MASTER_USER='replicator', 
    MASTER_PASSWORD='TileLowNilWife';
    Query OK, 0 rows affected (0.05 sec)
    
    mysql>

成功后开启slave模式：

mysql> SLAVE START;
    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: 960
                   Relay_Log_File: mysql-relay-bin.000002
                    Relay_Log_Pos: 1106
            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: 960
                  Relay_Log_Space: 1262
                  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>

到这一步master -> slave的配置基本完成了，我们接下去可以test一下：

在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='Replication is running';
    Query OK, 1 row affected (0.04 sec)
    
    mysql>

在slave上进行查看：

mysql> SELECT * FROM important.stuff;
    +----+------------------------+---------------------+
    | id | details                | happened            |
    +----+------------------------+---------------------+
    |  1 | Replication is running | 2013-03-12 19:29:33 |
    +----+------------------------+---------------------+
    1 row in set (0.00 sec)
    
    mysql>

如果数据出来了，表示最最基本的mysql主备完成了，那些大集群的mysql备份方案都大同小异，原理就是这样没什么去吧，希望对博友认识mysql以及高可用有帮助，谢谢。

[Center]: /images/20220715/9fee338d179645e0a9b5635bffa016f9.png