How to Use ProxySQL as a Load Balancer for MySQL on Ubuntu 16.04

ProxySQL is an open-source MySQL proxy server, meaning it serves as an intermediary between a MySQL server and the applications that access its databases. ProxySQL can improve performance by distributing traffic among a pool of multiple database servers and also improve availability by automatically failing over to a standby if one or more of the database servers fail.

Prerequisites

3 ubuntu server
sql1 = 192.168.0.216
sql2 = 192.168.0.217
sql3 = 192.168.0.218

Install mysql on all servers.

# cd /tmp
# curl -OL https://dev.mysql.com/get/mysql-apt-config_0.8.3-1_all.deb
# dpkg -i mysql-apt-config*
# apt-get update
# mysql-apt-config*
# apt-get install mysql-server

check mysql is running on all servers.

# systemctl status mysql

Output:

  • mysql.service – MySQL Community Server

  Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)

  Active: active (running) since Wed 2018-02-28 14:14:55 +08; 40s ago

Main PID: 2921 (mysqld)

  CGroup: /system.slice/mysql.service

          └─2921 /usr/sbin/mysqld –daemonize –pid-file=/var/run/mysqld/mysqld.pid

Securing MySQL installation on all servers.

# mysql_secure_installation

*key in your mysql root password and follow the instruction to finish the secure installation

Testing MySQL

# mysqladmin -u root -p version

Output:

mysqladmin  Ver 8.42 Distrib 5.7.21, for Linux on x86_64

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Server version          5.7.21

Protocol version        10

Connection              Localhost via UNIX socket

UNIX socket             /var/run/mysqld/mysqld.sock

Generate a UUID to Identify the MySQL Group, only run on 1 server.

On sql1, use the uuidgen command to generate a valid UUID for the group:

uuidgen

d62ef9f3-37d2-48dd-8ea9-105a452d56ba

Set Up Group Replication

Open up the main MySQL configuration file on each MySQL server.

vim /etc/mysql/my.cnf

insert at the end of file:

[mysqld]
# General replication settings
gtid_mode = ON #same on all server
enforce_gtid_consistency = ON #same on all server
master_info_repository = TABLE #same on all server
relay_log_info_repository = TABLE #same on all server
binlog_checksum = NONE #same on all server
log_slave_updates = ON #same on all server
log_bin = binlog #same on all server
binlog_format = ROW #same on all server
transaction_write_set_extraction = XXHASH64 #same on all server
loose-group_replication_bootstrap_group = OFF #same on all server
loose-group_replication_start_on_boot = OFF #same on all server
loose-group_replication_ssl_mode = REQUIRED #same on all server
loose-group_replication_recovery_use_ssl = 1 #same on all server
# Shared replication group configuration
loose-group_replication_group_name = “d62ef9f3-37d2-48dd-8ea9-105a452d56ba” #the uid we generated just now,same on all server
loose-group_replication_ip_whitelist = “192.168.0.216,192.168.0.217,192.168.0.218” #same on all server
loose-group_replication_group_seeds = “192.168.0.216:33061,192.168.0.217:33061,192.168.0.218:33061” #same on all server
# Single or Multi-primary mode?
# Uncomment these two lines for multi-primary mode,
# where any host can accept writes
#loose-group_replication_single_primary_mode = OFF
#loose-group_replication_enforce_update_everywhere_checks = ON
# Host specific replication configuration
server_id = 1 #must be unique number on different server & increment number. Example: sql1=1, sql2=2, sql3=3
bind-address = “192.168.0.216” #current server ip address
report_host = “192.168.0.216” #current server ip address
loose-group_replication_local_address = “192.168.0.216:33061” #current server ip address
###remember the last section should be different on each servers###

Save & quit

systemctl restart mysql

Configure Replication User and Enable Group Replication Plugin on all servers.

mysql -u root -p
mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'password' REQUIRE SSL;
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
mysql> SHOW PLUGINS;

Output:

+—————————-+———-+——————–+———————-+———+

| Name                       | Status   | Type               | Library              | License |

+—————————-+———-+——————–+———————-+———+

|                            |          |                    |                      |         |

| . . .                      | . . .    | . . .              | . . .                | . . .   |

|                            |          |                    |                      |         |

| group_replication          | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |

+—————————-+———-+——————–+———————-+———+

45 rows in set (0.00 sec)

Start Group Replication

To start up the group, complete the following steps on a single member of the group

mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
mysql> SELECT * FROM performance_schema.replication_group_members;

Output：

+—————————+————————————–+————–+————-+————–+

| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE |

+—————————+————————————–+————–+————-+————–+

| group_replication_applier | 13324ab7-1b01-11e7-9dd1-22b78adaa992 | 203.0.113.1  |        3306 | ONLINE       |

+—————————+————————————–+————–+————-+————–+

1 row in set (0.00 sec)

Next, create a test database and table to test our replication:

mysql> CREATE DATABASE playground;
mysql> CREATE TABLE playground.equipment ( id INT NOT NULL AUTO_INCREMENT, type VARCHAR(50), quant INT, color VARCHAR(25), PRIMARY KEY(id));
mysql> INSERT INTO playground.equipment (type, quant, color) VALUES ("slide", 2, "blue");
Check out the content
mysql > SELECT * FROM playground.equipment;

Output

+—-+——-+——-+——-+

| id | type  | quant | color |

+—-+——-+——-+——-+

|  1 | slide |     2 | blue  |

+—-+——-+——-+——-+

1 row in set (0.00 sec)

Start Up the Remaining Nodes

on the second server, start the group replication.

sql2:

mysql> START GROUP_REPLICATION;

sql3:

mysql> START GROUP_REPLICATION;

Check the membership list again. You should see three servers now:

sql2‘s output:

mysql> SELECT * FROM performance_schema.replication_group_members;

+—————————+————————————–+—————+————-+————–+

| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE |

+—————————+————————————–+—————+————-+————–+

| group_replication_applier | ad217f0d-1c4e-11e8-88ff-08002741a6cd | 192.168.0.216 |        3306 | ONLINE       |

| group_replication_applier | ae721361-1c4e-11e8-85bc-080027da78ae | 192.168.0.217 |        3306 | ONLINE       |

| group_replication_applier | b056d844-1c4e-11e8-8564-080027b9a9e0 | 192.168.0.218 |        3306 | ONLINE       |

+—————————+————————————–+—————+————-+————–+

3 rows in set (0.00 sec)

Check to see whether the test database information has been replicated over on the new members:

sql2:

output:

+—-+——-+——-+——-+

| id | type  | quant | color |

+—-+——-+——-+——-+

|  1 | slide |     2 | blue  |

+—-+——-+——-+——-+

1 row in set (0.01 sec)

Testing Write Capabilities of New Group Members

With Single Primary Environment, we may only could write on the main server(sql1), if we write on others server, it will show a error:

sql2:

output:

mysql> INSERT INTO playground.equipment (type, quant, color) VALUES ("swing", 10, "yellow");

ERROR 1290 (HY000): The MySQL server is running with the –super-read-only option so it cannot execute this statement

Once the group is bootstrapped, individual members can join and leave without affecting availability, so long as there are enough members to elect primary servers. However, if certain configuration changes are made (like switching between single and multi-primary environments), or all members of the group leave, you might need to re-bootstrap the group. You do this in exactly the same way that you did initially.

Installing ProxySQL

To install ProxySQL we need another server, create a new ubuntu server.

cd /tmp
curl -OL https://github.com/sysown/proxysql/releases/download/v1.4.4/proxysql_1.4.4-ubuntu16_amd64.deb
dpkg -i proxysql_*
apt-get update
apt-get install mysql-client
systemctl start proxysql

Setting the ProxySQL Administrator Password

Access the administration interface. You’ll be prompted for password which, on a default installation, is admin.

mysql -u admin -p -h 127.0.0.1 -P 6032 --prompt='ProxySQLAdmin> '

default password = admin

Update your proxysql password

ProxySQLAdmin> UPDATE global_variables SET variable_value='admin:password' WHERE variable_name='admin-admin_credentials';
ProxySQLAdmin> LOAD ADMIN VARIABLES TO RUNTIME;
ProxySQLAdmin> SAVE ADMIN VARIABLES TO DISK;

Configuring Monitoring in MySQL

Log into a server with one of the MySQL nodes.At here, i log on to sql1 server.

Download the SQL file containing some necessary functions for ProxySQL group replication support to work.

cd /opt
curl -OL https://gist.github.com/lefred/77ddbde301c72535381ae7af9f968322/raw/5e40b03333a3c148b78aa348fd2cd5b5dbb36e4d/addition_to_sys.sql
mysql -u root -p < addition_to_sys.sql
mysql> CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitorpassword';
mysql> GRANT SELECT on sys.* to 'monitor'@'%';
mysql> FLUSH PRIVILEGES;

Configuring Monitoring in ProxySQL

Back in the ProxySQL admin interface, update the mysql-monitor_username variable to the username of the new account.

ProxySQLAdmin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
ProxySQLAdmin> LOAD MYSQL VARIABLES TO RUNTIME;
ProxySQLAdmin> SAVE MYSQL VARIABLES TO DISK;

Adding MySQL Nodes to the ProxySQL Server Pool

we need to tell ProxySQL which identifiers it should use for each state. Here, we use 1 for the offline host group, 2 for the writer host group, 3 for the reader host group, and 4 for the backup writer host group.

ProxySQLAdmin> INSERT INTO mysql_group_replication_hostgroups (writer_hostgroup, backup_writer_hostgroup, reader_hostgroup, offline_hostgroup, active, max_writers, writer_is_also_reader, max_transactions_behind) VALUES (2, 4, 3, 1, 1, 3, 1, 100);

Add each of the three MySQL servers, making sure to replace the example IP addresses in the commands below.

ProxySQLAdmin> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (2, '192.168.0.216', 3306);
ProxySQLAdmin> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (2, '192.168.0.217', 3306);
ProxySQLAdmin> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (2, '192.168.0.218', 3306);
ProxySQLAdmin> LOAD MYSQL VARIABLES TO RUNTIME;
ProxySQLAdmin> SAVE MYSQL VARIABLES TO DISK;
ProxySQL should now distribute our nodes across the host groups as specified.Check that by executing a SELECT query against the runtim330e_mysql_servers table
ProxySQLAdmin> SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers;

+————–+—————+——–+

| hostgroup_id | hostname      | status |

+————–+—————+——–+

| 2            | 192.168.0.216 | ONLINE |

| 2            | 192.168.0.218 | ONLINE |

| 2            | 192.168.0.217 | ONLINE |

+————–+—————+——–+

3 rows in set (0.00 sec)

Creating the MySQL Users

The following steps must be performed only on a single member of the group. You can choose any one member. I am using sql2 server to do it.

sql2 server:

mysql> CREATE USER 'playgrounduser'@'%' IDENTIFIED BY 'playgroundpassword';
mysql> GRANT ALL PRIVILEGES on playground.* to 'playgrounduser'@'%';
mysql> FLUSH PRIVILEGES;
mysql> Exit

Re-open the MySQL interface with the new user, which will prompt you for the password.

mysql -u playgrounduser -p
mysql> SHOW TABLES FROM playground;

+———————-+

| Tables_in_playground |

+———————-+

| equipment            |

+———————-+

1 row in set (0.00 sec)

The visible list of tables in the database showing the equipment table created in the original replication tutorial confirms that the user has been created correctly on the nodes.

Creating the ProxySQL User

The final configuration step is to allow connections to ProxySQL with the playgrounduser user, and pass those connections through to the nodes.

Back to proxysql server.

ProxySQLAdmin> INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('playgrounduser', 'playgroundpassword', 2);
ProxySQLAdmin> LOAD MYSQL VARIABLES TO RUNTIME;
ProxySQLAdmin> SAVE MYSQL VARIABLES TO DISK;

To verify that we can connect to the database nodes using these credentials, open another terminal window and SSH to the ProxySQL server. We’ll still need the administration prompt later, so don’t close it just yet.

INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('playgrounduser', 'playgroundpassword', 2);

***Log in to playgrounduser, as we set above, the password is playgroundpassword.***

output:

proxysql:~# mysql -u playgrounduser -p -h 127.0.0.1 -P 6033 –prompt=’ProxySQLClient> ‘

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or g.

Your MySQL connection id is 9

Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the current input statement.

ProxySQLClient>

Let’s execute a simple statement to verify if ProxySQL will connect to one of the nodes. This command queries the database for the hostname of the server it’s running on and returns the server hostname as the only output.

ProxySQLClient> SELECT @@hostname;

+————+

| @@hostname |

+————+

| sql2       |

+————+

1 row in set (0.00 sec)

Verifying the ProxySQL Configuration

We know that connectivity between ProxySQL and the MySQL nodes is working, so the final tests are to ensure that the database permissions allow both read and write statements from ProxySQL, and to make sure that these statements will still be executed when some of the nodes in the group fail.

Execute a SELECT statement in the ProxySQL client prompt to verify that we can read the data from the playground database.

ProxySQLClient> SELECT * FROM playground.equipment;

+—-+——-+——-+——–+

| id | type  | quant | color  |

+—-+——-+——-+——–+

|  1 | slide |     2 | blue   |

|  2 | swing |    10 | yellow |

+—-+——-+——-+——–+

2 rows in set (0.00 sec)

Next, try writing by inserting some new data into the table representing 5 red drills.

ProxySQLClient> INSERT INTO playground.equipment (type, quant, color) VALUES (“drill”, 5, “red”);

Query OK, 1 row affected (1.00 sec)

Then re-execute the previous SELECT command to verify that the data has been inserted.

ProxySQLClient> SELECT * FROM playground.equipment;

ProxySQLClient> SELECT * FROM playground.equipment;

+—-+——-+——-+——–+

| id | type  | quant | color  |

+—-+——-+——-+——–+

|  1 | slide |     2 | blue   |

|  2 | swing |    10 | yellow |

|  3 | drill |     5 | red    |

+—-+——-+——-+——–+

3 rows in set (0.01 sec)

From the command line of one of the MySQL servers, stop the MySQL process to simulate a failure.

systemctl stop mysql

We can check that by querying the runtime_mysql_servers table from the ProxySQL administration prompt

ProxySQLAdmin> SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers;

+————–+—————+———+

| hostgroup_id | hostname      | status  |

+————–+—————+———+

| 2            | 192.168.0.216 | ONLINE  |

| 2            | 192.168.0.218 | SHUNNED |

| 2            | 192.168.0.217 | ONLINE  |

+————–+—————+———+

3 rows in set (0.00 sec)

TEXT

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *