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