MYSQL数据库proxysql配置，实现读写分离

最新推荐文章于 2025-06-23 18:01:25 发布

云之巢

最新推荐文章于 2025-06-23 18:01:25 发布

阅读量2.8k

点赞数

CC 4.0 BY-SA版权

文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/weixin_38154393/article/details/75315220

MySQL数据库专栏收录该内容

3 篇文章

订阅专栏

本文介绍了一个使用三个节点进行MySQL主从复制及ProxySQL配置的实验案例。通过详细步骤展示了如何设置主从复制环境，并利用ProxySQL实现读写分离。实验包括配置各节点、数据库授权、设置ProxySQL等关键环节。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

我们用三个节点来做实验：
node1节点：172.16.251.132 主节点
node2节点：172.15.252.32 从节点
node3节点：172.16.251.49 配置proxysql
先配置主从复制：

node节点主节点：

#ntpdate 172.16.0.1 同步时间
#vim /etc/my.cnf.d/server.cnf
[mysqld]
innodb_file_per_table=ON
skip_name_resolve=ON
server_id=1
log-bin=master-log
#systemctl start mariadb

node2节点从节点：

#ntpdate 172.16.0.1 同步时间
#vim /etc/my.cnf.d/server.cnf
[mysqld]
innodb_file_per_table=ON
skip_name_resolve=ON
server_id=11
relay_log=relay-log
read_only=ON
#systemctl start mariadb

接下来数据库授权：

node1节点：

#mysql
>grant replication client,replication slave on *.* to 'repluser'@'172.16.252.%' identified by 'replpass';
>grant ALL on *.* to 'myadmin'@'172.16.251.%' identified by 'mypass';  （授权一个用户便于实验检测读写分离）
>FLUSH PRIVILEGES;
>show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000001 |      498 |              |                  |
+-------------------+----------+--------------+------------------+

node2节点：

#mysql
>change master to master_host='172.16.251.132',master_user='repluser',master_password='replpass',master_port=3306,master_log_file='master-log.000001',master_log_pos= 498;
>start slave;
>show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.251.132
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-log.000003
          Read_Master_Log_Pos: 498
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 530
        Relay_Master_Log_File: master-log.000003
             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: 498
              Relay_Log_Space: 818
              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: 1

node3节点上配置proxysql：

#lftp 172.16.0.1/pub    下载1.3.6-1版本的proxysql，也可以从网上直接下载
-->cd Sources/7.x86_64/proxysql/
-->mget proxysql-1.3.6-1-centos7.x86_64.rpm
#yum install ./proxysql-1.3.6-1-centos7.x86_64.rpm    安装
#vim /etc/proxysql.cnf    编辑配置文件
mysql_variables=
{
            ............
        interfaces="0.0.0.0:3306;/tmp/proxysql.sock"
        default_schema="base"
}
mysql_servers =
(
    {
        address = "172.16.251.132"
        port = 3306
        hostgroup = 0           
                status = "ONLINE"     
                weight = 1            
                compression = 0
    }，
    {
        address = "172.16.252.32"
        port = 3306
        hostgroup = 1           
                status = "ONLINE"     
                weight = 1            
                compression = 0
    }

）

mysql_users:
(
        {
         username = "myadmin"
         password = "mypass"
         default_hostgroup = 0
         default_schema="base"
         active = 1
        }
）
mysql_replication_hostgroups=
(
        {
                writer_hostgroup=0 写组，组号为0的可写
                reader_hostgroup=1 读组，组号为1的可读
                comment="test repl 1"
       }
）
#systemctl start proxysql
#ss -tnl 查看端口，6032和3306端口监听了
# mysql -umyadmin -pmypass -h172.16.251.49
>USE base;
>CREATE TABLE tbl1 (id INT);
>SHOW TABLES;
+----------------+
| Tables_in_base |
+----------------+
| tbl11          |
+----------------+

node1节点上：

#mysql;
>USE base;
>SHOW TABLES;
+----------------+
| Tables_in_base |
+----------------+
| tbl11          |
+----------------+

node2节点上：

#mysql;
>USE base;
>SHOW TABLES;
+----------------+
| Tables_in_base |
+----------------+
| tbl11          |
+----------------+

对于node1和node2节点来说，node3对base数据库的任何操作都能查看；node1作为写组成员，对base数据库的写操作，node2和node3也都能看到；作为读组成员node2对base数据库的操作，node1和node3都不能查看到。（node1、node2是主从复制，node3是proxysql）