MySQL middleware proxysql (1): Introduction and Installation

1.ProxySQL Introduction

In the previous article, a MySQL middleware was introduced: MySQL Router. For more information, see MySQL router for read/write separation of MySQL.

Here's a series of details about a MySQL middleware that can actually be used in a production environment: Proxysql. It has two versions: the official version and the Percona version, the Percona version is revised on the official version, adding a few more useful tools. In my series of articles, I use the official version.

Official site: Official site
Official GitHub: Official GitHub
Official Handbook: Official Handbook
Percona Proxysql Handbook: Percona proxysql Manual

I have translated the official Manual of Proxysql , please refer to my GitHub wiki:proxysql Chinese manual. I also export the translated version of the markdown file for PDF, you can download: Proxysql Chinese manual. pdf.

Proxysql is developed in the C + + language, although it is also a lightweight product, but the performance is very good (according to test, can process the according data), the function is sufficient, can meet the most functions required by the middleware, including:

• The most basic read/write separation, and there are many ways.
  
• You can customize the routing of SQL statements based on the user, schema-based, statement-based rules. In other words, the rules are flexible. Based on schema and statement-level rules, simple sharding can be implemented.
  
• Can cache query results. Although Proxysql's caching strategy is rudimentary, it implements the basic caching capabilities that are most often sufficient. In addition, the author has intended to implement a richer caching strategy.
  
• Monitor back-end nodes. The Proxysql can monitor multiple metrics for the backend node, including: Proxysql and back-end heartbeat information, read-only/read-write,slave of the backend node, and data synchronization latency for master (replication lag).

2. Installing Proxysql

Take the CentOS RPM package for example.

cat <<EOF | tee /etc/yum.repos.d/proxysql.repo[proxysql_repo]name= ProxySQLbaseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasevergpgcheck=1gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_keyEOF

You can then install it directly.

yum -y install proxysql

Or go directly to GitHub to download RPM packages or source packages: https://github.com/sysown/proxysql/releases/tag/v1.4.9

By default, RPM-installed Proxysql only provides SYSV-style service scripts /etc/init.d/proxysql . Therefore, the script can be used to manage the start, stop and other functions of proxysql.

[[email protected] ~]# /etc/init.d/proxysql --helpUsage: ProxySQL {start|stop|status|reload|restart|initial}

For example:

service proxysql start   # 启动proxysqlservice proxysql stop    # 停止proxysqlservice proxysql status  # 查看proxysql状态

If you want to manage proxysql through SYSTEMD, you can /usr/lib/systemd/system/proxysql.service write the following in:

[[email protected] ~]# vim /usr/lib/systemd/system/proxysql.service[Unit]Description=High Performance Advanced Proxy for MySQLAfter=network.target[Service]Type=simpleUser=mysqlGroup=mysqlPermissionsStartOnly=trueLimitNOFILE=102400LimitCORE=1073741824ExecStartPre=/bin/mkdir -p /var/lib/proxysqlExecStartPre=/bin/chown mysql:mysql -R /var/lib/proxysql /etc/proxysql.cnfExecStart=/usr/bin/proxysql -fRestart=always

In general, Proxysql rarely stops or restarts because most configurations can be modified online.

MySQL middleware proxysql (1): Introduction and Installation