Mha+proxysql read/write separation high Availability

The document structure is as follows:

1, Proxysql description

Proxysql is a MySQL middleware product, is a flexible MySQL proxy layer, can realize the read-write separation, support the function of the query router, support dynamically specify SQL for caching, support dynamic load configuration, failover and some SQL filtering functions.

Other products: Dbproxy,mycat,oneproxy and so on.

2. Mha+proxysql reading and writing separation experiment 2.1. Installation steps

Ip	Role	Operating system	Version
172.16.10.21	Proxysql	Redhat6.7	1.4.9
172.16.10.32	Master	Redhat6.7	5.7.20
172.16.10.34	Slave1	Redhat6.7	5.7.20
172.16.10.36	Salve2	Redhat6.7	5.7.20
172.16.10.30	Vip

Open Read_only=1 from library, main library read_only=0

Proxysql Install the source package:

Yum-y Install Perl-dbd-mysql perl-dbi perl-time-hires Perl-io-socket-ssl

Or simply rude: yum-y install perl*

Proxysql Package:

https://www.percona.com/downloads/proxysql/

Installing Proxysql

RPM-IVH proxysql-1.4.9-1.1.el6.x86_64.rpm

The configuration file path is:/etc/proxysql.cnf

Start Proxysql

Service Proxysql Start

NETSTAT-ANLP |grep Proxysql

6032 is the management port, 6033 is the port number of the external service

User name and password are admin by default

Use Help as follows:

To view the Proxysql installation library:

mysql-uadmin-padmin-h127.0.0.1-p6032

2.2. Proxysql Library Description

Proxysql version 1.4.9-percona-1.1 instance:

Main: Memory configuration database, which is storage, the table stores the backend DB instances, user authentication, routing rules and other information. The main library has the following information:

Mysql_servers--Backend can connect to MySQL server list

Mysql_users--Configure the backend database account and monitor the account

Mysql_query_rules--Specify a list of rules for query routing to different servers on the backend

Disk Library: The configuration of persistent disks.

Stats Library: Summary of statistical information.

Monitor Library: Some of the monitored information collected, including the health status of the database.

2.3. Configuring Proxysql Monitoring

Https://github.com/sysown/proxysql/wiki/Configuring-ProxySQL

The top layer is runtime, the middle layer is memory, the underlying is the persistent layer disk and config file.

Runtime: Represents the Proxysql currently in use configuration, cannot directly modify the configuration here, must be from the next layer of load come in.

The memory:memory layer is connected to the runtime layer, and the persistence layer is connected below. In this layer can be normal operation Proxysql configuration, random modification, will not affect the production environment. Modifying a configuration is generally now done by the memory layer, confirming normal after loading to runtime and persisting to disk.

Disk and config file: Persistent configuration information, memory configuration information is lost after reboot, so configuration information needs to be kept on disk. When you restart, you can quickly load it back from disk.

1 is the write group, and 2 is the read group.

Insert into Mysql_servers (Hostgroup_id,hostname,port) VALUES (10, ' 172.16.10.32 ', 3307);

Insert into Mysql_servers (Hostgroup_id,hostname,port) VALUES (10, ' 172.16.10.34 ', 3307);

Insert into Mysql_servers (Hostgroup_id,hostname,port) VALUES (10, ' 172.16.10.36 ', 3307);

SELECT * from Mysql_servers;

Configure the Monitoring account:

Create user ' mon ' @ ' 172.16.10.% ' identified by ' mon ';

GRANT all privileges on *. * to ' mon ' @ ' 172.16.10.% ' with GRANT option;

External access Account:

Create user ' wr ' @ ' 172.16.10.% ' identified by ' WR ';

GRANT all privileges on * * to ON *. * to ' WR ' @ ' 172.16.10.% ' with GRANT option;

Configuring Proxysql Monitoring:

Set mysql-monitor_username= ' Mon ';

Set mysql-monitor_password= ' Mon ';

Load MySQL servers to runtime;

Save MySQL servers to disk;

Then verify the monitoring information:

SELECT * from Monitor.mysql_server_connect_log limit 6;

SELECT * from Monitor.mysql_server_ping_log ORDER by Time_start_us limit 6;

The monitoring information prompt is normal.

2.4. Configuring Proxysql Master-Slave partition information

Configuring the master-slave partition requires the use of mysql_replication_hostgroups

Show CREATE TABLE mysql_replication_hostgroups\g;

Writer_hostgroup Number of Write groups

Reader_hostgroup the number of the Read group

The experiment uses 10 as the write group and 20 as the read group.

INSERT into mysql_replication_hostgroups values (10,20, ' proxy ');

Load MySQL servers to runtime;

Save MySQL servers to disk;

SELECT * from Mysql_replication_hostgroups;

Proxysql services are grouped according to the value of the server's read_only, Read_only=0 's server,master is divided into groups numbered 10, Read_only=1 Server,slave is divided into read groups numbered 20.

SELECT * from Mysql_servers;

The Transaction_persistent field in the Mysql_users table defaults to 0, and it is recommended that you set it to 1 after the user has been created to avoid dirty read Phantom reads such as:

Insert into Mysql_users (Username,password,default_hostgroup) VALUES (' WR ', ' WR ', 10);

Update mysql_users set transaction_persistent=1 where username= ' WR ';

Load MySQL users to runtime;

Save MySQL users to disk;

Test login (Port 6033):

Mysql-uwr-pwr-h 172.16.10.34-p3307-e "show Slave Status\g"

2.5. Configure the read-write separation policy

Configure table Mysql_query_rules for read-write separation:

Match_pattern: The field is the rule that represents the setting.

Destination_hostgroup: field represents the default specified grouping.

Apply represents the actual execution of application rules.

Insert into Mysql_query_rules (active,match_pattern,destination_hostgroup,apply) VALUES (1, ' ^select.*for UPDATE$ ', 10,1);

Insert into Mysql_query_rules (active,match_pattern,destination_hostgroup,apply) VALUES (1, ' ^select ', 20, 1);

LOAD MYSQL QUERY RULES to RUNTIME;

SAVE MYSQL QUERY RULES to DISK;

2.6. Test read/write separation

Connect the Proxysql login database via the account created by WR.

mysql-uwr-pwr-h172.16.10.21-p6033

Login via management port to view:

mysql-uadmin-padmin-h127.0.0.1-p6032

SELECT * from Stats_mysql_query_digest;

You can learn that select COUNT (*) from T; This statement is automatically numbered on the 20 read group, which is slave.

Test the update.

Test the UPDATE statement on a write group of 10.

2.7. Read and write Separation weight adjustment

After the read and write separation settings are successful, you can adjust the weights, such as Slave2 (172.16.10.36) for more read operations.

Update mysql_servers set weight=10 where hostname= ' 172.16.10.36 ';

Load MySQL servers to runtime;

Load MySQL variables to runtime;

Load MySQL users to runtime;

Save MySQL servers to disk;

Save MySQL variables to disk;

Save MySQL users to disk;

SELECT * from Mysql_servers;

2.8. MHA Failover Test

Before testing:

Master 172.16.10.32 is master, the number of groups is 10, and the group is written.

After failover:

The new master is 172.16.10.34 (formerly Slave1)

SELECT * from Runtime_mysql_servers;

The new master is a write group (10), originally a 20-read group.

To perform a read-write separation test:

Read and write separations are still successful (and successful after failback).

Mha+proxysql read/write separation high Availability