1. The way of separating read from write in MySQL
There are several ways for mysql to achieve read-write separation:
- The program modifies mysql operation, communicates with database directly, realizes load balance in a simple and quick way with separation of reading and writing and random way, and distributes permissions independently, which needs the assistance of developers.
- amoeba, which directly realizes the separation of reading and writing and load balancing, does not need to modify the code. It has a very flexible data solution. It allocates accounts by itself, is independent of the back-end database authority management, and the authority processing is not flexible enough.
- MySQL proxy, which can directly realize read-write separation and load balancing, does not need to modify the code. master and slave use the same account, which is inefficient
- mycat Middleware
- proxysql middleware (recommended)
2. Introduction to proxysql
ProxySQL is a MySQL middleware that can be used in production environment. It has two versions: official version and percona version. The percona version is modified on the basis of the official version, with several more practical tools added. The official version is recommended for production environment.
ProxySQL is developed in C + + language. Although it is also a lightweight product, its performance is very good (according to the test, it can process hundreds of billions of data), and its function is enough to meet most functions required by middleware, including:
- The most basic read / write separation, and there are many ways
- SQL statement routing can be customized based on user, schema and statement rules. In other words, the rules are flexible. Simple sharding can be realized based on schema and statement level rules
- The query results can be cached. Although the caching strategy of ProxySQL is relatively simple, it implements the basic caching function, and most of the time it is enough. In addition, the author has intended to implement a richer caching strategy
- Monitor back-end nodes. ProxySQL can monitor multiple indicators of backend nodes, including: ProxySQL and heartbeat information of backend nodes, read-only/read-write of backend nodes, replication lag of slave and master nodes
3. Official installation method of proxysql
3.1 configure yum source
[root@proxysql ~]# cat <<EOF | tee /etc/yum.repos.d/proxysql.repo [proxysql_repo] name= ProxySQL baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/7 gpgcheck=1 gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key EOF [root@proxysql ~]# yum -y install proxysql
4. Admin management interface of proxysql
When ProxySQL is started, two ports will be monitored:
- admin management interface, the default port is 6032. This port is used to view and configure ProxySQL
- The interface for receiving SQL statements. The default port is 6033. This interface is similar to the 3306 port of MySQL
ProxySQL's admin management interface is an interface using MySQL protocol. Therefore, you can directly use MySQL client, navicat and other tools to connect to this management interface. The default user name and password are admin
For example, use mysql client to connect to the management interface of ProxySQL:
[root@proxysql ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> show databases; +-----+---------------+-------------------------------------+ | seq | name | file | +-----+---------------+-------------------------------------+ | 0 | main | | | 2 | disk | /var/lib/proxysql/proxysql.db | | 3 | stats | | | 4 | monitor | | | 5 | stats_history | /var/lib/proxysql/proxysql_stats.db | +-----+---------------+-------------------------------------+ 5 rows in set (0.00 sec)
Because the configuration of ProxySQL is saved in several libraries, it is very convenient to modify the configuration of ProxySQL by sending some SQL commands through the management interface. ProxySQL will parse some specific commands sent through this interface that are valid for ProxySQL, and send them to the embedded SQLite3 database engine for running after reasonable conversion
ProxySQL configuration is almost operated through the management interface. Through the Admin management interface, almost all configurations can be modified online and made effective. Only the configuration of two variables can take effect if you restart ProxySQL. They are:
MySQL threads and MySQL stacksize
5. Variables related to admin management interface
5.1 admin-admin_credentials
The admin admin credentials variable controls the administrator account of the admin interface. The default administrator account and password are admin:admin, but this default user can only be used locally. If you want to connect to ProxySQL remotely, such as using navicat on windows to connect to ProxySQL management interface on Linux, you must customize an administrator account.
Add administrator account
MySQL [(none)]> select @@admin-admin_credentials; //View current user name and password +---------------------------+ | @@admin-admin_credentials | +---------------------------+ | admin:admin | +---------------------------+ 1 row in set (0.01 sec) //Set administrator account myadmin, password wangqing123! MySQL [(none)]> set admin-admin_credentials='admin:admin;myadmin:wangqing123!'; Query OK, 1 row affected (0.00 sec) MySQL [(none)]> select @@admin-admin_credentials; +----------------------------------+ | @@admin-admin_credentials | +----------------------------------+ | admin:admin;runtime:wangqing123! | +----------------------------------+ 1 row in set (0.00 sec) MySQL [(none)]> load admin variables to runtime; //Make changes effective immediately Query OK, 0 rows affected (0.01 sec) MySQL [(none)]> save admin variables to disk; //Make changes permanent to disk Query OK, 31 rows affected (0.00 sec)
After modification, the user name and password can be used to connect to the management interface
[root@proxysql ~]# mysql -umyadmin -pwangqing123! -h172.16.12.128 -P6032 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]>
All configuration operations are to modify the corresponding table in the main library
MySQL [(none)]> select * from global_variables where variable_name='admin-admin_credentials'; +-------------------------+----------------------------------+ | variable_name | variable_value | +-------------------------+----------------------------------+ | admin-admin_credentials | admin:admin;myadmin:wangqing123! | +-------------------------+----------------------------------+ 1 row in set (0.00 sec)
You must distinguish between the user name of the admin administration interface and the user name in mysql_users
- The user of admin management interface is connected to the management interface (default port 6032) to manage and configure ProxySQL
- The user name in mysql_users table is the user used by the application to connect to ProxySQL (default port 6033), and the user used by ProxySQL to connect to the backend MySQL Servers. Its function is to send and route SQL statements, similar to the 3306 port of MySQL Server. Therefore, the users in this table must already exist on the backend MySQL Server and be authorized
Users of the admin management interface must not exist in MySQL users. This is for security reasons to prevent users from guessing users in MySQL users through the admin management interface
5.2 admin-stats_credentials
The admin stats & credentials variable controls the ordinary user of the admin management interface. The user in this variable does not have super administrator permission. He can only view the statistics data in the monitor library and the main library. Other libraries are invisible and do not have any write permission
The default common user name and password are stats. Like admin, it can only be used for local login by default. If you want people to view remotely, you need to add a view specific user
MySQL [(none)]> select @@admin-stats_credentials; +---------------------------+ | @@admin-stats_credentials | +---------------------------+ | stats:stats | +---------------------------+ 1 row in set (0.00 sec) //Add exclusive viewing user MySQL [(none)]> set admin-stats_credentials='stats:stats;mystats:wangqing123!'; Query OK, 1 row affected (0.00 sec) MySQL [(none)]> select @@admin-stats_credentials; +----------------------------------+ | @@admin-stats_credentials | +----------------------------------+ | stats:stats;mystats:wangqing123! | +----------------------------------+ 1 row in set (0.00 sec) MySQL [(none)]> load admin variables to runtime; Query OK, 0 rows affected (0.00 sec) MySQL [(none)]> save admin variables to disk; Query OK, 31 rows affected (0.00 sec)
Similarly, users in this variable must not exist in the MySQL users table
Use mystats user remote connection to view
[root@proxysql ~]# mysql -umystats -pwangqing123! -h172.16.12.128 -P6032 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> MySQL [(none)]> show tables from main; +--------------------------------------+ | tables | +--------------------------------------+ | global_variables | | stats_memory_metrics | | stats_mysql_commands_counters | | stats_mysql_connection_pool | | stats_mysql_connection_pool_reset | | stats_mysql_global | | stats_mysql_prepared_statements_info | | stats_mysql_processlist | | stats_mysql_query_digest | | stats_mysql_query_digest_reset | | stats_mysql_query_rules | | stats_mysql_users | | stats_proxysql_servers_checksums | | stats_proxysql_servers_metrics | | stats_proxysql_servers_status | +--------------------------------------+ 15 rows in set (0.00 sec)
5.3 admin-mysql_ifaces
The admin MySQL ifaces variable specifies the listening address of the admin interface in the form of a colon separated hostname:port list. The default listening time is 0.0.0.0:6032
Note that UNIX domain socket is allowed to be used for listening, so that applications within the host can be processed directly.
For example:
MySQL [(none)]> SET admin-mysql_ifaces='0.0.0.0:6032;/tmp/proxysql_admin.sock'; Query OK, 1 row affected (0.00 sec) MySQL [(none)]> load admin variables to runtime; Query OK, 0 rows affected (0.00 sec) MySQL [(none)]> save admin variables to disk; Query OK, 31 rows affected (0.00 sec)
5.4 MySQL interfaces user connection port
The mysql interfaces variable specifies the listening address of the mysql user connection interface in the form of colon separated hostname:port list. The default listening time is 0.0.0.0:6033
Modify the port mode as follows:
[root@proxysql ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 //View current port (admin@127.0.0.1:6032) [(none)]> select @@mysql-interfaces; +--------------------+ | @@mysql-interfaces | +--------------------+ | 0.0.0.0:6603 | +--------------------+ 1 row in set (0.00 sec) //Modify port (admin@127.0.0.1:6032) [(none)]> set mysql-interfaces='0.0.0.0:6033'; Query OK, 1 row affected (0.00 sec) //Preservation (admin@127.0.0.1:6032) [(none)]> save mysql variables to disk; Query OK, 97 rows affected (0.01 sec) //Restart and take effect [root@proxysql ~]# systemctl restart proxysql [root@proxysql ~]# ss -anlt State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:6032 *:* LISTEN 0 128 *:6033 *:* LISTEN 0 128 *:6033 *:* LISTEN 0 128 *:6033 *:* LISTEN 0 128 *:6033 *:*
6. Multi tier configuration system
6.1 libraries in proxysql
Connect ProxySQL with the Admin management interface of ProxySQL to view the libraries owned by ProxySQL
[root@proxysql ~]# mysql -umyadmin -pwangqing123! -h172.16.12.128 -P6032 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 14 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> show databases; +-----+---------------+-------------------------------------+ | seq | name | file | +-----+---------------+-------------------------------------+ | 0 | main | | | 2 | disk | /var/lib/proxysql/proxysql.db | | 3 | stats | | | 4 | monitor | | | 5 | stats_history | /var/lib/proxysql/proxysql_stats.db | +-----+---------------+-------------------------------------+ 5 rows in set (0.00 sec)
Among them:
- The main library is the most important library of ProxySQL, which is used when the configuration needs to be modified. It is actually a memory database system. Therefore, after modifying the configuration - in the main library, it must be persisted to the disk to be permanently saved
- Disk database is a disk database, and its structure is completely consistent with that of memory database. When persisting the configuration in the memory database, it is actually written to the disk library. The default path for the disk database is $DATADIR/proxysql.db
- The stats library is the statistics library. The data in this library is usually temporarily filled when retrieving the data in it. It is saved in memory. No persistence is required because there are no related configuration items
- The monitor database is a database related to monitoring backend MySQL nodes. There are only a few log tables in the database. The monitoring information collected by the monitoring module is stored in the corresponding log table
- Stats history library is a new library in version 1.4.4, which is used to store historical statistics. The default path is $dataDir / proxysqlu stats.db
The internal use of ProxySQL is SQLite3 database. Both memory database and disk database are parsed and operated by SQLite3 engine. It may be slightly different from MySQL's syntax, but ProxySQL will automatically adjust the incompatible syntax to maximize the efficiency of MySQL statements.
When the main database is described above, it only says that the memory database needs to be persisted to the disk database to permanently save the configuration. But in fact, after the configuration in the main library is modified, it will not take effect immediately. It also needs to be load ed into the runtime data structure to take effect. Only the configuration in the runtime data structure is the current effective configuration for ProxySQL
6.2 ProxySQL multi tier configuration system
ProxySQL's configuration system is very powerful. It can modify almost all configurations online (the only two variables that need to be restarted to take effect are MySQL threads and MySQL stacksize), and it can take effect online and persist. This benefits from its multi-layer configuration system.
The multi-layer configuration system structure is as follows:
+-------------------------+
| RUNTIME |
+-------------------------+
/|\ |
| |
[1] | [2] |
| \|/
+-------------------------+
| MEMORY |
+-------------------------+ _
/|\ | |\
| | \
[3] | [4] | \ [5]
| \|/ \
+-------------------------+ +---------------+
| DISK | | CONFIG FILE |
+-------------------------+ +---------------+
The bottom layer is disk library and config file. It should be noted that the config file here is the traditional configuration file, which is / etc/proxysql.cnf by default. When ProxySQL starts, it mainly reads configuration from disk library and loads it into memory and finally loads it into runtime. Only a few specific configuration contents are loaded from config file, unless it is the first time to initialize the ProxySQL running environment (or disk library Empty).
The middle layer is memory, which means the memory database. In fact, it is the main library. All configurations modified through the management interface are saved in the main. When ProxySQL restarts or crashes, the data in the in memory database will be lost, so you need to save it to the disk database.
The top layer is runtime, which is the data structure read by ProxySQL about thread runtime. In other words, the configurations in this data structure are all valid configurations. Therefore, after the configuration in the main library is modified, it must be load ed into the runtime data structure for it to take effect.
In the above multi-layer configuration system diagram, the serial numbers of [1], [2], [3], [4], [5] are marked. Each sequence number has two operation directions from/to, in fact, it is only from different angles. The following are the operations corresponding to each serial number:
[1] : Load configuration from in memory database into RUNTIME In data structure LOAD XXX FROM MEMORY LOAD XXX TO RUNTIME [2] : take RUNTIME Persistent configuration in data structure to in memory database SAVE XXX FROM RUNTIME SAVE XXX TO MEMORY [3] : Load the configuration from the disk database into the memory database LOAD XXX FROM DISK LOAD XXX TO MEMORY [4] : Persist the configuration in the memory database to the disk database SAVE XXX FROM MEMORY SAVE XXX TO DISK [5] : Read configuration from traditional configuration file and load it into memory database LOAD XXX FROM CONFIG
DISK/MEMORY/RUNTIME/CONFIG can be abbreviated as long as it can be recognized. For example, MEMORY can be abbreviated to MEM, and runtime can be abbreviated to run
In addition, what is XXX above? This indicates what kind of configuration to load / save. Currently, ProxySQL supports the following:
- List item
- List item
- List item
- mysql users
- mysql servers
- mysql variables
- mysql query rules
- admin variables
- scheduler
-
ProxySQL [u servers: at present, the ProxySQL clustering function is still in the experimental stage, so this type of configuration should not be used
These can be viewed from the main library or disk library
MySQL [(none)]> show tables from disk; +------------------------------------+ | tables | +------------------------------------+ | global_variables | # (1) | mysql_collations | # (N) | mysql_group_replication_hostgroups | # (2) | mysql_query_rules | # (3) | mysql_query_rules_fast_routing | # (4) | mysql_replication_hostgroups | # (5) | mysql_servers | # (6) | mysql_users | # (7) | proxysql_servers | # (8) | scheduler | # (9) +------------------------------------+ 10 rows in set (0.00 sec)
In the above results, I have marked some serial numbers for these tables. The contents of the corresponding tables are as follows:
- (1) There are two types of variables in. Those starting with amdin - represent admin variables and those starting with mysql - represent mysql variables. What kind of variable is modified, and what kind of variable is represented by XXX in the previous article
- (2,5,6) corresponds to mysql servers
- (3,4) corresponds to mysql query rules
- (7) Corresponding mysql users
- (9) Corresponding scheduler
- (N) It's just a table. It stores the character set and collation supported by ProxySQL. It doesn't need to be modified
- (8) Is the cluster configuration table of ProxySQL, which is still in the experimental stage. If you want to configure this feature, load / save ProxySQL [servers to / from
6.3 how to load configuration when starting ProxySQL
If ProxySQL is just installed, or the disk database file is empty (even does not exist), or the option -- initial is used when starting ProxySQL, in these cases, the configuration will be read from the traditional configuration file config file and loaded into the memory database, and automatically loaded into the runtime data structure, save to the disk database. This is the initialization of proxysq L process of operating environment.
If ProxySQL is not started for the first time, because there are already disk database files, ProxySQL will read almost all configurations from the disk database (even if one item is configured in the traditional configuration file, it will not be parsed), but there are three items that must be read from the traditional configuration file. They are:
- datadir: when ProxySQL is started, its data directory must be determined from the configuration file, because the disk database files, logs and other files are stored in the data directory. If you use / etc/init.d/proxysql to manage ProxySQL, in addition to modifying the datadir of / etc/proxysql.cnf, you need to modify the datadir in the script.
- Restart'on'missing'heartbeat: how many heartbeats a MySQL thread has lost will kill the thread and restart it. The default is 10.
- Execute on exit failure: if this variable is set, the ProxySQL parent process will execute the defined script every time ProxySQL crashes. It is recommended to use it to generate some crash warnings and logs. Note that the restart speed of ProxySQL may only be a few milliseconds, so many other monitoring tools may not be able to detect a common failure of ProxySQL, and this variable can be used at this time
