Using ProxySQL to validate MySQL updates

A quick look at ProxySQL

• Query rules based on Google's RE2-style regex
• Failover detection
• Connection multiplexing

• Read/write splitting between the write master and read slaves
• Query firewall, for those times you wish you could prevent queries from hitting the database. Perhaps you are susceptible to SQL Injection, but don't have the ability to change the apps right away.
• Sharding strategies based on query patterns. For example, adding comments to the SQL to indicate which schema or server the query should go to.
• Validating query changes without impacting the production server

Mirroring in ProxySQL

mysql> SELECT username, destination_hostgroup, mirror_hostgroup FROM mysql_query_rules 
 WHERE username='app_user'\G
 ****************** 1. row **********************
  username: app_user
 destination_hostgroup: 3
  mirror_hostgroup: 4
 

Validate application queries for MySQL Upgrade

• All queries succeed. For example, new MySQL versions often introduce new RESERVED words that can break queries unless they are quoted.
• Individual query performance.
• Queries return the right results.
• Overall database performance under real-life application traffic.

CREATE TABLE email_lead (
  `id` INT unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
  ....
  `generated` datetime NOT NULL
 )
 

INSERT INTO email_lead (generated) VALUES (NOW());
 

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your
 MySQL server version for the right syntax to use near 'generated) VALUES (NOW())' at line 1
 

mysql>INSERT INTO email_lead (generated) VALUES (NOW());
 Query OK, 1 row affected (0.02 sec)
 

mysq> SELECT * FROM email_lead\G
 *************************** 1. row ***************************
  id: 1
 generated: 2016-10-19 20:23:41
 1 row in set (0.00 sec)
 

mysql> SELECT * FROM email_lead\G
 Empty set (0.00 sec)
 

mysql> SELECT hostgroup, username, digest_text, count_star, sum_time FROM stats_mysql_query_digest 
 WHERE digest_text LIKE 'INSERT%'\G
 *************************** 1. row ***************************
  hostgroup: 3
  username: app_user
 digest_text: INSERT INTO email_lead (generated) VALUES (NOW())
  count_star: 1
  sum_time: 11412
 *************************** 2. row ***************************
  hostgroup: 4
  username: app_user
 digest_text: INSERT INTO email_lead (generated) VALUES (NOW())
  count_star: 1
  sum_time: 1130
 2 rows in set (0.00 sec)
 

MySQL_Session.cpp:1207:handler(): [WARNING] Error during query on (4,mysqlb,3306): 1064, You have an error
 in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax 
 to use near 'generated) VALUES (NOW())' at line 1
 

Conclusion