MySQL在Ubuntu上如何实现负载均衡-开发者知识库平台

在Ubuntu上实现MySQL负载均衡可以通过多种方式来实现，以下是使用Percona XtraDB Cluster (PXC)、ProxySQL和KeepAlived的方案：

1. 环境准备

准备三个节点：假设node1、node2、node3。
操作系统：CentOS Linux release 7.9.2009 (core)。
MySQL PXC版本：5.7.44-48-57-log。

2. 安装依赖包

wget http://repo.percona.com/yum/percona-release-7.9-3.noarch.rpm
yum localinstall percona-release-7.9-3.noarch.rpm -y

3. 安装PXC、KeepAlived和ProxySQL

yum install percona-xtrabackup-2.4.29-r2e6c0951-el7-x86_64-bundle.tar percona-xtradb-cluster-5.7.44-r32be3bd-el7-x86_64-bundle.tar qpress-11-1.el7.x86_64.rpm -y

4. 创建数据目录

mkdir -p /data/sdv1/mysql/

5. 配置MySQL PXC

在每个节点上配置/etc/percona-xtradb-cluster.conf.d/mysqld.cnf文件，注意修改以下配置：

[client]
port = 3306
socket = /data/sdv1/mysql/mysql.sock

[mysql]
prompt="\u@nodeX.domain \r:\m:\s [\d]> "
no-auto-rehash
server-id=157
user = mysql
port = 3306
bind-address=0.0.0.0
datadir = /data/sdv1/mysql/
socket = /data/sdv1/mysql/mysql.sock
pid-file = /data/sdv1/mysql/mysqldb.pid
character-set-server = utf8mb4
skip_name_resolve = 1
lower_case_table_names=1

6. 启动PXC集群

在每个节点上启动PXC集群：

cd /etc/percona-xtradb-cluster
./start-all.sh

7. 配置ProxySQL

ProxySQL可以作为负载均衡器和读写分离器。安装并配置ProxySQL：

wget https://www.percona.com/downloads/percona-proxy/2.10.2/percona-proxy-2.10.2-1.ubuntu20.04.1-x86_64.tar.gz
tar xvf percona-proxy-2.10.2-1.ubuntu20.04.1-x86_64.tar.gz
cd percona-proxy-2.10.2-1.ubuntu20.04.1
./configure --user=mysql --group=mysql --listen-host=127.0.0.1 --listen-port=6446 --proxy-host=127.0.0.1 --proxy-port=3306 --ssl-ca=/etc/ssl/certs/ca-cert.pem --ssl-cert=/etc/ssl/certs/proxy-cert.pem --ssl-key=/etc/ssl/private/proxy-key.pem
make
make install

配置/etc/percona-proxy/config.json文件：

{
  "admin-users": {
    "admin": {
      "host": "127.0.0.1",
      "port": 6446,
      "user": "admin",
      "password": "admin_password"
    }
  },
  "listen": {
    "host": "127.0.0.1",
    "port": 6446
  },
  "proxy": {
    "user": "mysql",
    "password": "mysql_password",
    "host": "127.0.0.1",
    "port": 3306,
    "schema": "mysql",
    "ssl": {
      "ca": "/etc/ssl/certs/ca-cert.pem",
      "cert": "/etc/ssl/certs/proxy-cert.pem",
      "key": "/etc/ssl/private/proxy-key.pem"
    },
    "rules": {
      "load_balancing_server_list": [
        {
          "host": "node1.domain",
          "port": 3306
        },
        {
          "host": "node2.domain",
          "port": 3306
        },
        {
          "host": "node3.domain",
          "port": 3306
        }
      ],
      "write_only_servers": [
        {
          "host": "node1.domain",
          "port": 3306
        },
        {
          "host": "node2.domain",
          "port": 3306
        }
      ]
    }
  }
}

启动ProxySQL：

systemctl start percona-proxy
systemctl enable percona-proxy

8. 配置KeepAlived

在每个节点上配置KeepAlived以实现高可用性：

wget https://www.percona.com/downloads/percona-xtrabackup/2.10.2/percona-xtrabackup-2.10.2-1.ubuntu20.04.1-x86_64.tar.gz
tar xvf percona-xtrabackup-2.10.2-1.ubuntu20.04.1-x86_64.tar.gz
cd percona-xtrabackup-2.10.2-1.ubuntu20.04.1
./configure --user=mysql --group=mysql --listen-host=127.0.0.1 --listen-port=6446 --proxy-host=127.0.0.1 --proxy-port=3306 --ssl-ca=/etc/ssl/certs/ca-cert.pem --ssl-cert=/etc/ssl/certs/proxy-cert.pem --ssl-key=/etc/ssl/private/proxy-key.pem
make
make install

配置/etc/keepalived/keepalived.conf文件：

vrrp_instance VI_1 {
    state MASTER
    interface eth0
    virtual_router_id 51
    priority 100
    advert_int 1

    authentication {
        auth_type PASS
        auth_pass 1234
    }

    virtual_ipaddress {
        192.168.1.100
    }
}

启动KeepAlived：

systemctl start keepalived
systemctl enable keepalived

通过以上步骤，你可以在Ubuntu上实现MySQL的负载均衡和高可用性。这种方式利用了Percona XtraDB Cluster进行数据分片，ProxySQL进行负载均衡和读写分离，以及KeepAlived保证高可用性。

辰迅云「云服务器」，即开即用、新一代英特尔至强铂金CPU、三副本存储NVMe SSD云盘，价格低至29元/月。点击查看>>

MySQL在Ubuntu上如何实现负载均衡