ProxySQL Admin Interface Is Not Your Typical MySQL Server! Discover the Unique Features and Challeng

If enabled (default), after ProxySQL Cluster synchronizes MySQL Servers tables from a remote node, the configuration is also locally persisted to disk.See admin-checksum_mysql_servers and ProxySQL Cluster for further details.

ProxySQL Admin Interface Is Not Your Typical MySQL Server!

Download

Semicolon-separated list of hostname:port entries for interfaces on which the admin interface should listen. It also supports UNIX domain sockets for the cases where the connection is done from an application on the same machine: in this case the full path of the socket must be specified.For example: SET admin-mysql_ifaces='127.0.0.1:6032;/tmp/proxysql_admin.sock'.Please note that the default admin user can only connect locally for security reasons. In order to connect remotely a secondary user needs to be created by defining this in the admin-admin_credentials variable, for example admin-admin_credentials="admin:admin;radminuser:radminpass".

This is a list of semi-colon separated user:password pairs that defines the read-only credentials for connecting to the admin interface. These are not allowed to update internal data structures such as the list of MySQL backend servers (or hostgroups), query rules, etc., neither to read configuration tables. They are only allowed to read from the statistics and monitoring tables.Note: there are several restrictions on use of users in admin-stats_credentials : the restrictions are the same as those listed in admin-admin_credentials

ProxySQL embeds an HTTP web server from where it is possible to gather certain metrics.Credentials to access the web interfaces are the same as those defined in admin-stats_credentials.When Web UI plugin is enabled, the HTTP web server can be used to reconfigure ProxySQL itself, and the credentials configured in admin-admin_credentials can be used to access the web interface.

To connect to the ProxySQL admin interface, use the mysql client. Youcan either connect to the admin interface from a Percona XtraDB Cluster node that already has themysql client installed (Node 1, Node 2, Node 3) or install the client onNode 4 and connect locally. For this tutorial, install Percona XtraDB Cluster on Node 4:

Replace the galera_cluster_name with a name you choose for your cluster. Replace mysql1_ip , mysql2_ip and arbitrator_ip with the actual IPs of those servers. Replace this_server_ip and this_server_name with the IP and name of the server you are configuring.

On the admin interface you use a SQL like language. You can connect to it with the mysql command line tool. HeidiSQL however fails to connect to the admin interface, because it relies on certain MySQL features. I get the message "SQL error (1045): no such function: CONNECTION_ID" when trying to connect.

The MySQL server has gone away error, which means that the MySQL server (mysqld) timed out and closed the connection. By default, MySQL will close connections after eight hours (28800 seconds) if nothing happens. However, in some cases, your web host, DBA, or app developer may have decreased this timeout setting, as discussed below.

• Remember this value since we will use it later.Add the following section at the end of /etc/mysql/my.cnf for group replication settings:[mysqld]# General Group replication settingsgtid_mode = ONenforce_gtid_consistency = ONmaster_info_repository = TABLErelay_log_info_repository = TABLEbinlog_checksum = NONElog_slave_updates = ONlog_bin = binlogbinlog_format = ROWtransaction_write_set_extraction = XXHASH64loose-group_replication_bootstrap_group = OFFloose-group_replication_start_on_boot = OFFloose-group_replication_ssl_mode = REQUIREDloose-group_replication_recovery_use_ssl = 1# TODO: Shared replication group configurationloose-group_replication_group_name = "2777fc39-ef8e-458f-b8fc-af74f398331f"loose-group_replication_ip_whitelist = "SERVER_2_IP, SERVER_3_IP, SERVER_4_IP"loose-group_replication_group_seeds = "SERVER_2_IP:33060, SERVER_3_IP:33060, SERVER_4_IP:33060"# TODO: Configurations for Single-primary (DEFAULT) or Multi-primary / multi-master mode? #loose-group_replication_single_primary_mode = OFF#loose-group_replication_enforce_update_everywhere_checks = ON# TODO: Host-specific configurationsserver_id = YOUR_SERVER_NUMBERbind-address = "YOUR_SERVER_IP"report_host = "YOUR_SERVER_IP"loose-group_replication_local_address = "YOUR_SERVER_IP:33060". Remember that you need to fill in your own information for SERVER_X_IP, YOUR_SERVER_IP, and YOUR_SERVER_NUMBER. Also, you will need to manually REJOIN the group after the service is restarted with the above setting (loose-group_replication_start_on_boot = OFF).

• Restart MySQL Service:systemctl restart mysqlRemember to open ports for MySQL service and MySQL Group Replication service.

• Login to MySQL console on each server to create a new user and grant replication permission:# mysql -u root -pmysql> SET SQL_LOG_BIN=0;mysql> CREATE USER 'REP_USER'@'%' IDENTIFIED BY 'REP_PASSWORD' REQUIRE SSL;mysql> GRANT REPLICATION SLAVE ON *.* TO 'rep'@'%';mysql> FLUSH PRIVILEGES;mysql> SET SQL_LOG_BIN=1;mysql> CHANGE MASTER TO MASTER_USER='REP_USER', MASTER_PASSWORD='REP_PASSWORD' FOR CHANNEL 'group_replication_recovery';mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';. Remember to choose your user (e.g. rep) and password (e.g. [email protected]) in the above configuration.

• Verify that the group replication plugin is on with the command SHOW PLUGINS; in MySQL console.

Start MySQL Group Replication serviceTo start replication, we will need to start a bootstrapped node (the first node), and then start replication services on remaining nodes.

• Configuring ProxySQL as the DB Connection proxyInstall and start ProxySQL on SERVER_1apt-get install -y lsb-releasewget -O - ' _pub_key' apt-key add -echo deb -1.4.x/$(lsb_release -sc)/ ./ tee /etc/apt/sources.list.d/proxysql.listapt-get update -yapt-get install proxysqlservice proxysql start

• Login to the ProxySQL Admin Interface with MySQL client via the port 6032 and change the admin password (if you want):mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> 'Admin> SHOW DATABASES;Admin> UPDATE global_variables SET variable_value='admin:password' WHERE variable_name='admin-admin_credentials';Admin> LOAD ADMIN VARIABLES TO RUNTIME; Admin> SAVE ADMIN VARIABLES TO DISK;As you might see, I need to copy the memory settings to the runtime realm, then save them to disk to make them persist. The reason is that updating operations here only affect the memory layer.

Add 3 MySQL nodes to the ProxySQL backends:Admin> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (2, 'SERVER_2_IP', 3306), (2, 'SERVER_3_IP', 3306), (2, 'SERVER_4_IP', 3306);>Admin> SAVE MYSQL SERVERS TO DISK;>Admin> LOAD MYSQL SERVERS TO RUNTIME;Admin> SELECT * FROM mysql_servers;Admin> SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers;Check the above result to be sure that all 3 nodes are in ONLINE status. 2ff7e9595c