1.ProxySQL 介绍和安装
ProxySQL 是一种高性能、高可用的开源中间件,适用于mysql和相关的数据库,如MariaDB
官网:http://www.proxysql.com
安装
发行版本下载链接:https://github.com/sysown/proxysql/releases
Ubuntu / Debian:
添加源
apt-get install -y lsb-release wget -O - 'http://repo.proxysql.com/ProxySQL/repo_pub_key' | apt-key add - echo deb http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/$(lsb_release -sc)/ ./ \ | tee /etc/apt/sources.list.d/proxysql.list
安装:
apt-get update apt-get install proxysql OR apt-get install proxysql=version
Red Hat / CentOS:
添加源
cat <<EOF | tee /etc/yum.repos.d/proxysql.repo [proxysql_repo] name= ProxySQL YUM repository baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever gpgcheck= gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key EOF
安装
yum install proxysql OR yum install proxysql-version
proxysql开启/关闭/重启
开启 service proxysql start 关闭 service proxysql stop 重启 service proxysql restart
管理员交互界面开启和关闭
进入管理界面 # mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> ' 开启 Admin> proxysql start; Query OK, rows affected (0.00 sec) 关闭 Admin> proxysql stop; ERROR (HY000): Lost connection to MySQL server during query 重启 Admin> proxysql restart; ERROR (HY000): Lost connection to MySQL server during query
Proxysql 架构以及在线修改配置
Proxysql有三层架构,最底层是disk和config file,第二层是memory,最顶层是runtime
当第一次启动时,proxysql会抓取本地配置文件proxy.cnf并且加载至内存z中,并且最终加载到runtime生效,后续如果在线修改了配置后,需要load至runtime层使其生效,如果需要持久化保存,则需要save至磁盘即可
搭建一个最简单的Proxysql
预先安装好proxysql和mysql
proxysql按照上文安装方法就好
mysql安装:(https://www.cnblogs.com/wang-li/p/7726304.html)
进入proxysql管理界面
# mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> ' mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection Server version: (ProxySQL Admin Module) Copyright (c) , , Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. Admin>
添加服务器的信息
Admin,); Query OK, row affected (0.00 sec)
将数据推到runtime和保存至本地磁盘
Admin> LOAD MYSQL SERVERS TO RUNTIME; Query OK, rows affected (0.00 sec) Admin> SAVE MYSQL SERVERS TO DISK; Query OK, rows affected (0.04 sec)
添加用户信息
Admin); Query OK, row affected (0.00 sec)
将数据推到runtime和保存至本地磁盘
Admin> LOAD MYSQL USERS TO RUNTIME; Query OK, rows affected (0.00 sec) Admin> save mysql users to disk; Query OK, rows affected (0.01 sec)
测试proxysql
# mysql -uroot -proot -h 127.0.0.1 -P6033 -e "SELECT @@port" mysql: [Warning] Using a password on the command line interface can be insecure. +--------+ | @@port | +--------+ | | +--------+ [root@MiWiFi-R3P-srv proxysql]#