MySQLHASolution2019(2)ProxySQL

ChecktheOfficethewebsite,currentlylatestversion

https://github.com/sysown/proxysql/releases

Currentlylatestversionis2.0.6

>wgethttps://codeload.github.com/sysown/proxysql/tar.gz/2.0.6-Oproxysql-2.0.6.tar.gz

Unzipthefileandmakeandmakeinstall

>tarzxvfproxysql-2.0.6.tar.gz

SomeExceptionsmayaccur

/bin/sh:1:cmake:notfound

mysql_data_stream.cpp:3:10:fatalerror:zlib.h:Nosuchfileordirectory

Solution:

>sudoapt-getinstallcmake

>sudoapt-getinstalllibz-dev

>make

>sudomakeinstall

Checkversion

>proxysql--version

ProxySQLversion,codenameTruls

StartService

>sudoserviceproxysqlstart

Checkthestartfileandfindtheconfigurationfile

>vi/etc/init.d/proxysql

OPTS="-c/etc/proxysql.cnf-D$DATADIR"

>sudovi/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"

SomeerrorwhenIstarttheservice

>sudo/etc/init.d/proxysqlstart

StartingProxySQL:Nodirectory,logginginwithHOME=/

2019-07-2713:51:21main.cpp:720:ProxySQL_Main_process_global_variables():[WARNING]Unabletoopenconfigfile/etc/proxysql.cnf

2019-07-2713:51:21main.cpp:722:ProxySQL_Main_process_global_variables():[ERROR]Unabletoopenconfigfile/etc/proxysql.cnfspecifiedinthecommandline.Aborting!

DONE!

Tryanotherinstallation,findallthereleasehere

https://github.com/sysown/proxysql/releases

>wgethttps://github.com/sysown/proxysql/releases/download/v2.0.5/proxysql_2.0.5-ubuntu16_amd64.deb

>sudodpkg-iproxysql_2.0.5-ubuntu16_amd64.deb

CheckVersionisrightthistime

>proxysql--version

ProxySQLversion2.0.5-37-gc8e32ee,codenameTruls

ThatmakemethinkmaybeIshouldusethesourceinstallation2.0.5

>wgethttps://github.com/sysown/proxysql/archive/v2.0.5.tar.gz

>tarzxvfv2.0.5.tar.gz

>cdproxysql-2.0.5/

>sudoapt-getinstallautomakebzip2cmakemakeg++gccgitopenssldebconf-utils

>make

>sudomakeinstall

No,itseemscompilefromthesource,thereisnoversion

>proxysql--version

ProxySQLversion,codenameTruls

Starttheserviceagain

>sudoserviceproxysqlstart

Itworksthistime,Icanaccessfromtheadminconsole

>mysql-uadmin-padmin-h127.0.0.1-P6032

Checkstatus

>showdatabases;

+-----+---------------+-------------------------------------+

|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:

>usemain

>showtables;

+--------------------------------------------+

|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|

+--------------------------------------------+

Severalimportanttables:

mysql_servers—thelistofMySQLservers

mysql_users—theaccounts

mysql_query_rules—routerrules

Database-disk

Database-stats

Database-monitor

ProxySQLConfiguration

CreatemonitoraccountonMasterServer

>mysql-udebian-sys-maint-pG1FEbrOMSORmcaUK

>createuser'monitor'@'192.168.56.%'identifiedby'monitor';

>grantallprivilegeson*.*to'monitor'@'192.168.56.%'withgrantoption;

>createuser'proxysql'@'192.168.56.%'identifiedby'proxysql';

>grantallprivilegeson*.*to'proxysql'@'192.168.56.%'withgrantoption;

>flushprivileges;

RUNTIME:currentlyusingconfiguration

MEMORY:

DISKandCONFIGFILE

ConnecttotheProxySQLServer

>mysql-uadmin-padmin-h127.0.0.1-P6032

>insertintomysql_servers(hostgroup_id,hostname,port)values(1,'ubuntu-master',3306);

>insertintomysql_servers(hostgroup_id,hostname,port)values(1,'ubuntu-dev5',3306);

>insertintomysql_servers(hostgroup_id,hostname,port)values(1,'ubuntu-dev6',3306);

PuttheConfiguresintotheruntime

>loadmysqlserverstoruntime;

PersisttheConfigurationsintotheDisk

>savemysqlserverstodisk;

>select*frommysql_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||

+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+-----------

ConfiguretheMonitorAccounts

>setmysql-monitor_username='monitor';

>setmysql-monitor_password='monitor';

>loadmysqlvariablestoruntime;

>savemysqlvariablestodisk;

Checkmonitorworkingwell

>select*frommonitor.mysql_server_connect_logorderbytime_start_usdesclimit6;

+---------------+------+------------------+-------------------------+---------------+

|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|

SetProxySQLMaster/SlaveConfiguration

Checktablemysql_replication_hostgroups

>showcreatetablemysql_replication_hostgroups\G;

***************************1.row***************************

table:mysql_replication_hostgroups

CreateTable:CREATETABLEmysql_replication_hostgroups(

writer_hostgroupINTCHECK(writer_hostgroup>=0)NOTNULLPRIMARYKEY,

reader_hostgroupINTNOTNULLCHECK(reader_hostgroup<>writer_hostgroupANDreader_hostgroup>=0),

check_typeVARCHARCHECK(LOWER(check_type)IN('read_only','innodb_read_only','super_read_only'))NOTNULLDEFAULT'read_only',

commentVARCHARNOTNULLDEFAULT'',UNIQUE(reader_hostgroup))

1rowinset(0.00sec)

>insertintomysql_replication_hostgroupsvalues(1,2,'read_only','proxy');

ProxySQLwilldecidethegroupsfromread_onlyvalues,read_only=0,masterwillbeingroup1,read_only=1servers,salveswilleingroup2.

>loadmysqlserverstoruntime;

>savemysqlserverstodisk;

>select*frommysql_replication_hostgroups;

+------------------+------------------+------------+---------+

|writer_hostgroup|reader_hostgroup|check_type|comment|

+------------------+------------------+------------+---------+

|1|2|read_only|proxy|

Turnthereadonlyonon2slavesubuntu-dev5,ubuntu-dev6

Onubuntu-dev5

>mysql-udebian-sys-maint-pddHulNSEFzhUFvS1

>FLUSHTABLESWITHREADLOCK;

>SETGLOBALread_only=ON;

Onubuntu-dev6

>mysql-udebian-sys-maint-pCdnke4PtlCsfJ0sV

>FLUSHTABLESWITHREADLOCK;

>SETGLOBALread_only=ON;

Checktheserverinformations

>select*frommysql_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||

Group1ubuntu-master

Group2ubuntu-dev5,ubuntu-dev6

SetUpusersinmysql_users

>insertintomysql_users(username,password,default_hostgroup)values('proxysql','proxysql',1);

>updatemysql_userssettransaction_persistent=1whereusername='proxysql';

>loadmysqluserstoruntime;

>savemysqluserstodisk;

ConnecttotheProxySQL

>mysql-uproxysql-pproxysql-hubuntu-master-P6033

>showslavehosts;

+-----------+------+------+-----------+--------------------------------------+

|Server_id|Host|Port|Master_id|Slave_UUID|

+-----------+------+------+-----------+--------------------------------------+

|11||3306|1|d8f68bb0-abcc-11e9-a261-080027c70ba2|

|12||3306|1|8461547b-afa9-11e9-a337-080027918cb1|

+-----------+------+------+-----------+--------------------------------------+

SetUptheRouteRules

>insertintomysql_query_rules(active,match_pattern,destination_hostgroup,apply)VALUES(1,'^SELECT.*FORUPDATE$',1,1);

>insertintomysql_query_rules(active,match_pattern,destination_hostgroup,apply)VALUES(1,'^SELECT',2,1);

>loadmysqlqueryrulestoruntime;

>savemysqlqueryrulestodisk;

TrytheSELECTandOthercommands

>usemysql;

>select*fromuser;

Checkthelogging

>select*fromstats_mysql_query_digestlimit2;

+-----------+------------+----------+----------------+--------------------+---------------------+------------+------------+------------+----------+----------+----------+

|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*fromuser|2|1564364034|1564364055|2074|927|1147|

|2|mysql|proxysql||0x8937C7F52767EAB4|SELECT*FROMusers|1|1564363984|1564363984|1258|1258|1258|

Itwillallroutetothereadgroupifweuseselectxxxxxx.

References:

https://github.com/sysown/proxysql

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