`
sillycat
  • 浏览: 2539472 次
  • 性别: Icon_minigender_1
  • 来自: 成都
社区版块
存档分类
最新评论

MySQL HA Solution 2019(2)ProxySQL

 
阅读更多
MySQL HA Solution 2019(2)ProxySQL

Check the Office the website, currently latest version
https://github.com/sysown/proxysql/releases

Currently latest version is 2.0.6
> wget https://codeload.github.com/sysown/proxysql/tar.gz/2.0.6 -O proxysql-2.0.6.tar.gz

Unzip the file and make and make install
> tar zxvf proxysql-2.0.6.tar.gz

Some Exceptions may accur
/bin/sh: 1: cmake: not found
mysql_data_stream.cpp:3:10: fatal error: zlib.h: No such file or directory

Solution:
> sudo apt-get install cmake
> sudo apt-get install libz-dev

> make
> sudo make install

Check version
> proxysql --version
ProxySQL version , codename Truls

Start Service
> sudo service proxysql start

Check the start file and find the configuration file
> vi /etc/init.d/proxysql
OPTS="-c /etc/proxysql.cnf -D $DATADIR"

> sudo vi /etc/proxysql.cnf
admin_variables=
{
        admin_credentials="admin:admin"
#       mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
        mysql_ifaces="0.0.0.0:6032"
#       refresh_interval=2000
#       debug=true
}
mysql_variables=
{
        threads=4
        max_connections=2048
        default_query_delay=0
        default_query_timeout=36000000
        have_compress=true
        poll_timeout=2000
#       interfaces="0.0.0.0:6033;/tmp/proxysql.sock"
        interfaces="0.0.0.0:6033"
        default_schema="information_schema"

Some error when I start the service
> sudo /etc/init.d/proxysql start
Starting ProxySQL: No directory, logging in with HOME=/
2019-07-27 13:51:21 main.cpp:720:ProxySQL_Main_process_global_variables(): [WARNING] Unable to open config file /etc/proxysql.cnf
2019-07-27 13:51:21 main.cpp:722:ProxySQL_Main_process_global_variables(): [ERROR] Unable to open config file /etc/proxysql.cnf specified in the command line. Aborting!
DONE!

Try another installation, find all the release here
https://github.com/sysown/proxysql/releases
>wget https://github.com/sysown/proxysql/releases/download/v2.0.5/proxysql_2.0.5-ubuntu16_amd64.deb
> sudo dpkg -i proxysql_2.0.5-ubuntu16_amd64.deb
Check Version is right this time
> proxysql --version
ProxySQL version 2.0.5-37-gc8e32ee, codename Truls

That make me think maybe I should use the source installation 2.0.5
> wget https://github.com/sysown/proxysql/archive/v2.0.5.tar.gz
> tar zxvf v2.0.5.tar.gz
> cd proxysql-2.0.5/
> sudo apt-get install automake bzip2 cmake make g++ gcc git openssl debconf-utils
> make
> sudo make install
No, it seems compile from the source, there is no version
> proxysql --version
ProxySQL version , codename Truls

Start the service again
> sudo service proxysql start

It works this time, I can access from the admin console
> mysql -uadmin -padmin -h127.0.0.1 -P6032

Check status
> show databases;
+-----+---------------+-------------------------------------+
| seq | name          | file                                |
+-----+---------------+-------------------------------------+
| 0   | main          |                                     |
| 2   | disk          | /var/lib/proxysql/proxysql.db       |
| 3   | stats         |                                     |
| 4   | monitor       |                                     |
| 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |

Database - main:
> use main
> show tables;
+--------------------------------------------+
| tables                                     |
+--------------------------------------------+
| global_variables                           |
| mysql_aws_aurora_hostgroups                |
| mysql_collations                           |
| mysql_galera_hostgroups                    |
| mysql_group_replication_hostgroups         |
| mysql_query_rules                          |
| mysql_query_rules_fast_routing             |
| mysql_replication_hostgroups               |
| mysql_servers                              |
| mysql_users                                |
| proxysql_servers                           |
| runtime_checksums_values                   |
| runtime_global_variables                   |
| runtime_mysql_aws_aurora_hostgroups        |
| runtime_mysql_galera_hostgroups            |
| runtime_mysql_group_replication_hostgroups |
| runtime_mysql_query_rules                  |
| runtime_mysql_query_rules_fast_routing     |
| runtime_mysql_replication_hostgroups       |
| runtime_mysql_servers                      |
| runtime_mysql_users                        |
| runtime_proxysql_servers                   |
| runtime_scheduler                          |
| scheduler                                  |
+--------------------------------------------+

Several important tables:
mysql_servers — the list of MySQL servers
mysql_users    — the accounts
mysql_query_rules — router rules

Database - disk
Database - stats
Database - monitor

ProxySQL Configuration
Create monitor account on Master Server
> mysql -u debian-sys-maint -pG1FEbrOMSORmcaUK
> create user 'monitor'@'192.168.56.%' identified by 'monitor';
> grant all privileges on *.* to 'monitor'@'192.168.56.%' with grant option;

> create user 'proxysql'@'192.168.56.%' identified by 'proxysql';
> grant all privileges on *.* to 'proxysql'@'192.168.56.%' with grant option;

> flush privileges;

RUNTIME: currently using configuration
MEMORY:
DISK and CONFIG FILE

Connect to the ProxySQL Server
> mysql -uadmin -padmin -h127.0.0.1 -P6032

> insert into mysql_servers(hostgroup_id, hostname, port) values (1, 'ubuntu-master', 3306);
> insert into mysql_servers(hostgroup_id, hostname, port) values (1, 'ubuntu-dev5', 3306);
> insert into mysql_servers(hostgroup_id, hostname, port) values (1, 'ubuntu-dev6', 3306);

Put the Configures into the runtime
> load mysql servers to runtime;

Persist the Configurations into the Disk
> save mysql servers to disk;

> select * from mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname      | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | ubuntu-master | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | ubuntu-dev5   | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | ubuntu-dev6   | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+-----------

Configure the Monitor Accounts
> set mysql-monitor_username='monitor';
> set mysql-monitor_password='monitor';

> load mysql variables to runtime;
> save mysql variables to disk;

Check monitor working well
> select * from monitor.mysql_server_connect_log order by time_start_us desc limit 6;
+---------------+------+------------------+-------------------------+---------------+
| hostname      | port | time_start_us    | connect_success_time_us | connect_error |
+---------------+------+------------------+-------------------------+---------------+
| ubuntu-dev6   | 3306 | 1564360460364818 | 1504                    | NULL          |
| ubuntu-master | 3306 | 1564360401929277 | 1170                    | NULL          |
| ubuntu-dev5   | 3306 | 1564360401146812 | 10975                   | NULL          |

Set ProxySQL Master/Slave Configuration
Check table mysql_replication_hostgroups
> show create table mysql_replication_hostgroups\G;
*************************** 1. row ***************************
       table: mysql_replication_hostgroups
Create Table: CREATE TABLE mysql_replication_hostgroups (
    writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
    reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>=0),
    check_type VARCHAR CHECK (LOWER(check_type) IN ('read_only','innodb_read_only','super_read_only')) NOT NULL DEFAULT 'read_only',
    comment VARCHAR NOT NULL DEFAULT '', UNIQUE (reader_hostgroup))
1 row in set (0.00 sec)

> insert into mysql_replication_hostgroups values (1,2,'read_only','proxy');
ProxySQL will decide the groups from read_only values, read_only =0, master will be in group 1, read_only=1 servers, salves will e in group 2.

> load mysql servers to runtime;
> save mysql servers to disk;

> select * from mysql_replication_hostgroups;
+------------------+------------------+------------+---------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+---------+
| 1                | 2                | read_only  | proxy   |

Turn the read only on on 2 slaves ubuntu-dev5, ubuntu-dev6
On ubuntu-dev5
> mysql -u debian-sys-maint -pddHulNSEFzhUFvS1
> FLUSH TABLES WITH READ LOCK;
> SET GLOBAL read_only = ON;

On ubuntu-dev6
> mysql -u debian-sys-maint -pCdnke4PtlCsfJ0sV
> FLUSH TABLES WITH READ LOCK;
> SET GLOBAL read_only = ON;

Check the server informations
> select * from mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname      | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | ubuntu-master | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | ubuntu-dev5   | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | ubuntu-dev6   | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

Group 1  ubuntu-master
Group 2  ubuntu-dev5, ubuntu-dev6

Set Up users in mysql_users
> insert into mysql_users(username, password, default_hostgroup) values ('proxysql', 'proxysql', 1);
> update mysql_users set transaction_persistent=1 where username = 'proxysql';

> load mysql users to runtime;
> save mysql users to disk;

Connect to the ProxySQL
> mysql -uproxysql -pproxysql -h ubuntu-master -P 6033
> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|        11 |      | 3306 |         1 | d8f68bb0-abcc-11e9-a261-080027c70ba2 |
|        12 |      | 3306 |         1 | 8461547b-afa9-11e9-a337-080027918cb1 |
+-----------+------+------+-----------+--------------------------------------+

Set Up the Route Rules
> insert into mysql_query_rules(active,match_pattern,destination_hostgroup, apply) VALUES(1,'^SELECT.*FOR UPDATE$',1,1);
> insert into mysql_query_rules(active,match_pattern,destination_hostgroup, apply) VALUES(1,'^SELECT',2,1);

> load mysql query rules to runtime;
> save mysql query rules to disk;

Try the SELECT and Other commands

> use mysql;
> select * from user;

Check the logging
> select * from stats_mysql_query_digest limit 2;
+-----------+------------+----------+----------------+--------------------+---------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname | username | client_address | digest             | digest_text         | count_star | first_seen | last_seen  | sum_time | min_time | max_time |
+-----------+------------+----------+----------------+--------------------+---------------------+------------+------------+------------+----------+----------+----------+
| 2         | mysql      | proxysql |                | 0x7B1927E677262C21 | select * from user  | 2          | 1564364034 | 1564364055 | 2074     | 927      | 1147     |
| 2         | mysql      | proxysql |                | 0x8937C7F52767EAB4 | SELECT * FROM users | 1          | 1564363984 | 1564363984 | 1258     | 1258     | 1258     |

It will all route to the read group if we use select xxxxxx.


References:
https://github.com/sysown/proxysql
https://dev.mysql.com/doc/refman/5.5/en/replication-solutions-backups-read-only.html





分享到:
评论

相关推荐

    mysql8.0.28版本和vc2019依赖

    MySQL 8.0.28 版本可能包含了一些使用 VC2019 编译的动态链接库,因此在运行时需要这些库的支持。 首先,我们来详细了解一下 MySQL 8.0.28 的主要特性: 1. **性能优化**:此版本对查询执行引擎进行了优化,特别是...

    RoseHA关于linux下mysql配置

    RoseHA是在Linux环境下配置MySQL高可用性解决方案的必备工具。RoseHA可以帮助数据库管理员快速搭建MySQL的高可用集群环境,保证业务连续性,当一台服务器发生故障时,另一台服务器能够立即接管服务,减少或消除系统...

    Mysql HA Scale-Out

    例如,MySQL Router和ProxySQL等工具可以帮助管理分片。 5. **读写分离(Read-Write Splitting)** 在大规模系统中,通常会遇到读操作远多于写操作的情况。通过设置专门的读取服务器,可以缓解主服务器的压力,...

    docker-mysql-proxysql:用于使用ProxySQL和MySQL进行测试的Docker集群(docker-compose)

    Orchestrator / MySQL Docker测试该存储库包含一个docker-compose.yml,用于启动一个小型集群,该集群由3个MySQL 5.7容器(1个主服务器和2个从属服务器),3个通过RAFT连接的Orchestrator节点以及一个ProxySQL 1.4.x...

    cpp-ProxySQL是一个高性能MySQL代理

    ProxySQL是中国程序员熟知的一款开源、高性能的MySQL数据库代理软件,主要用C++语言编写,因此在"开发-数据库相关"的领域中具有显著地位。它为数据库管理提供了强大的中间层,能够有效地提升数据库系统的可用性、...

    VS2019+EF+Mysql生成实体数据模型(解决闪退的坑)VS2019+mysql+vs-mysql.rar

    最近使用VS2019连接数据库生成模型,生成实体,遇到的坑,找了好多资料在这里做个总结。 一、安装环境 mysql-installer-community-5.6.49.0.msi Visual Studio 2019专业版 mysql-for-visualstudio-1.2.9.msi

    MySQL 的 HA、读写分离、均衡负载 中文PDF版.rar

    本书主要讲述MySQL 的 HA、读写分离、均衡负载;感兴趣的朋友可以过来看看 截图: 标签:MySQL 负载均衡 读写分离 MySQLHA 人气书籍...

    mysql8.0.25安装包和依赖vc2019,关注我免费下载!

    在安装 MySQL 8.0.25 时,通常需要依赖 Microsoft Visual C++(VC)运行库,如 VC2019,因为 MySQL 的安装程序可能需要这些组件来正确运行。 **MySQL 8.0.25 的主要特点:** 1. **增强的性能**:MySQL 8.0.25 包含...

    mysql-ha-双主安装

    ### MySQL-HA 双主安装知识点详解 #### 一、MySQL-HA 双主复制原理与实践背景 MySQL-HA(High Availability)双主安装旨在通过构建一个高可用性架构来确保MySQL服务即使在一个节点发生故障的情况下仍能不间断地...

    proxysql 的安装配置

    proxysql 是一个高性能的 MySQL 代理服务器,能够提供高可用性、负载均衡、查询分析和缓存等功能。下面是 proxysql 的安装配置知识点: 一、下载和安装 1. 下载 proxysql 的 rpm 包从官网 ...

    MYSQL集群+HA高可用【MySQL性能调优】.pdf

    本文详细介绍了如何通过MySQL集群加上高可用性(HA)配置来实现MySQL性能调优的实践案例,文档中涉及到的操作步骤,适用于希望提高数据库性能与稳定性的用户。 知识点1:MySQL Cluster简介 MySQL Cluster是一种多主机...

    mysql基于keepalived的HA搭建.pdf

    ### MySQL基于Keepalived的HA搭建知识点 #### 1. MySQL双主架构 在MySQL的高可用性架构中,双主架构可以提供数据的备份和故障转移能力。通常,双主架构意味着两个MySQL服务器相互作为对方的主服务器和从服务器,...

    mysql+heartbeat+共享存储实施方案(mysql高可用集群).doc

    【MySQL+Heartbeat+共享存储实施方案】是一种实现MySQL高可用集群的方法,旨在确保数据库服务的稳定性和连续性。该方案采用HA(High Availability)双机热备软件Heartbeat,配合共享存储来实现实时的数据同步和故障...

    ProxySQL离线安装包CENTOS6.5

    ProxySQL是一款高性能、高可用的MySQL中间件,它主要用于负载均衡、读写分离以及数据库管理。在本场景中,我们关注的是在CENTOS 6.5操作系统上进行离线安装的过程。由于是离线安装,这意味着我们需要手动下载并准备...

    mysql集群按照proxysql-基于MGR组复制集群

    ProxySQL是一个高性能、高可用的MySQL中间件,而MGR是MySQL的一种多主复制模式,提供了强大的数据一致性保证。 **ProxySQL的角色与功能:** 1. **负载均衡:** ProxySQL可以智能地将客户端请求分发到不同的MySQL...

    ProxySQL简单读写分离.zip

    2. **ProxySQL的组件** - SQL Router:负责接收客户端请求并决定将请求转发到哪个MySQL服务器。 - Monitor:收集MySQL服务器的运行状态,如连接数、QPS等,用于健康检查和负载计算。 - Administrator:提供一个...

    ProxySQL最新官方文档

    ProxySQL 是一款高性能的 MySQL 代理服务器,它能够实现 MySQL 数据库的负载均衡、读写分离、故障转移等功能,为用户提供稳定高效的数据库访问服务。本文档将详细介绍 ProxySQL 的安装过程和服务管理方法。 #### 二...

    mysql5.7.29双主HA部署方案.docx

    捣鼓了一个周末--20200920,完整的整理出了这个mysql5.7.29双主HA最清晰最详细最易读的部署方案。本人辛苦的原创,照着我这个文档部署,你肯定可以完美部署。 有需要 的话,加我微信:fage0161

    ProxySQL简单读写分离.docx

    其中,ProxySQL作为一款高性能、可扩展性强的数据库代理服务,能够帮助我们轻松实现MySQL的读写分离功能。 #### 二、环境准备 本示例中的环境配置如下: - ProxySQL 服务器地址:192.168.65.2 - 主数据库服务器...

Global site tag (gtag.js) - Google Analytics