Configuration of the "20180523" Proxysql+mha and some problem descriptions

PROXYSQL+MHA's respective structures are no longer described here. Concrete construction can be self-Google, but note that MHA in the failover of the time do not need to do VIP switching operations.

Environment configuration information

1. master:172.16.3.5:3307
2. Slave
   • 172.16.3.6:3307
   • 172.16.3.7:3306
   • 172.16.3.6:3307
3. proxysql:172.16.3.15

Proxysql Configuration

2. configures the user to link back-end MySQL instances. This user must be real, and the user will be [email protected] to access the backend MySQL instance.

     [email protected] 10:51: [(None)]> Select Username,password,active,default_hostgroup from Mysql_ users;+----------+-------------+--------+-------------------+| Username | password | active | Default_hostgroup |+----------+-------------+--------+-------------------+| RPL | redhat_2018 | 1 | |+----------+-------------+--------+-------------------+1 row in Set (0.01 sec)  

   • active: This user is enabled, this user is the active user
   • default_hostgroup: The default MySQL group, if the Mysql_query_rules does not match, then all operations will default to Default_ HostGroup is executed in this group.
   • transaction_persistent: Execution of the same transaction in the same instance
3. The
4. configures the MySQL instance on the back-end of the link.

     [email protected] 11:48: [(None)]> Select Hostgroup_id,hostname,port,weight from mysql_servers;+- -------------+------------+------+--------+| hostgroup_id | hostname | Port | Weight |+--------------+------------+------+--------+| 11 | 172.16.3.6 | 3307 | 100 | | 10 | 172.16.3.5 | 3307 | 100 | | 10 | 172.16.3.7 | 3307 | 100 | | 11 | 172.16.3.7 | 3306 | 100 | | 11 | 172.16.3.7 | 3307 | |+--------------+------------+------+--------+5 rows in Set (0.00 sec)  

   • hostgroup_id: based on group ID into different types of

6. To configure Mysql_replication_hostgroups. The primary purpose is to monitor whether the backend MySQL instance is master or slave.

     [email protected] 11:50: [(None)]> select * from mysql_replication_hostgroups;+-------------- ----+------------------+---------+| Writer_hostgroup | Reader_hostgroup | Comment |+------------------+------------------+---------+| 10 |         11 | |+------------------+------------------+---------+1 row in Set (0.00 sec)  

   • writer_hostgroup: Dedicated Writes, the MySQL instance is monitored to see if super_read_only and Read_Only are turned off.
   • Reader_hostgroup: A read-only operation that monitors whether the MySQL instance has super_read_only and Read_Only turned on.
8. Configuration Mysql_query_rules, this is the most serious.

   [email protected] 14:28:  [(none)]> select rule_id,active,match_pattern,apply from mysql_query_rules;+---------+--------+-----------------------+-------+| rule_id | active | match_pattern         | apply |+---------+--------+-----------------------+-------+| 1       | 1      | ^SELECT .*FOR UPDATE$ | 1     || 2       | 1      | ^SELECT               | 1     |+---------+--------+-----------------------+-------+2 rows in set (0.00 sec)[email protected] 14:29:  [(none)]>

   • RULE_ID: The ID of the filter rule is usually from small to large to match, so it is recommended not to start from 1, preferably from the middle of the value start, convenient for subsequent emergency situations can be inserted in the front.
10. Configure the monitoring user. This user must also be real, and have super,replication client privileges.

    set global mysql-monitor_username=‘username‘;set global mysql-monitor_password=‘new_password‘

Watch out.

2. The more routing rules, the worse the performance.
4. It takes a long time to switch the database using the use schema by Proxysql the backend's MySQL link.

   [email protected] 14:58:  [(none)]> use yeahDatabase changed[email protected] 14:58:  [yeah]> use zabbix

   • When using MySQL login with the parameter-A, that is, each link does not automatically refresh the table cache information, that is, the latest table information is not cached locally.
   • In this case, the premise is that when the Mysql_users table is configured to insert when the default default_hostgroup is not specified, resulting in a default default_hostgroup of 0, is Information_schema, This does not happen if you choose an existing hostgroup_id.
5. Insert the server information in Proxysql table Mysql_servers, set the hostgroup_id of master to 10,slave hostgroup_id to 11, but discover Hostgroup_ ID 10 This grouping appears with slave information.

   [email protected] 15:02: [(None)]> select * from mysql_servers;+--------------+------------+------+--------+ --------+-------------+-----------------+---------------------+---------+----------------+---------+| hostgroup_id | hostname | Port | Status | Weight | Compression | max_connections | Max_replication_lag | Use_ssl | Max_latency_ms | Comment |+--------------+------------+------+--------+--------+-------------+-----------------+----------------- ----+---------+----------------+---------+| 11 | 172.16.3.6 | 3307 | ONLINE | 100 | 0 | 1000 | 0 | 0 | 0 | Slave | | 10 | 172.16.3.5 | 3307 | ONLINE | 100 | 0 | 1000 | 0 | 0 | 0 | Master | | 10 | 172.16.3.7 | 3307 | ONLINE | 100 | 0 | 1000 | 0 | 0 | 0 | Slave | | 11 | 172.16.3.7 | 3306 | ONLINE | 100 |  0         | 1000 | 0 | 0 | 0 | Slave | | 11 | 172.16.3.7 | 3307 | ONLINE | 100 | 0 | 1000 | 0 | 0 | 0 | Slave |+--------------+------------+------+--------+--------+-------------+-----------------+------------------- --+---------+----------------+---------+5 rows in Set (0.00 sec) [email protected] 15:05: [(None)]>

   Can be clearly seen in the third row 172.16.3.7:3307 This instance appeared two times, but the reality is that I only insert 1 times, and insert it to hostgroup_id 11 of the group above, but the actual situation in the 10,11 two groups appear respectively.

   • This is because Proxysql will take the initiative to detect the parameters of the backend read_only and Super_read_only, when the two parameters are found to be off when it will think that the MySQL instance is master, so you need to open these two parameters, In the proxysql found that slave information does not appear in the hostgroup_id to 10 of the group inside.
7. After logging in, you cannot perform operations such as show Tables,create table.

   [email protected] 15:19:  [yeah]> show tables;ERROR 9001 (HY000): Max connect timeout reached while reaching hostgroup 0 after 10000ms[email protected] 15:20:  [yeah]>

   • Mainly because the table mysql_users Insert user information when the Default_hostgroup is not set, then when the routing rules do not match it will default to match hostgroup_id to 0,default_ HostGroup for INFORMATION_SCHEMA. This time only need to set the Default_hostgroup to 10.

Configuration of the "20180523" Proxysql+mha and some problem descriptions