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