ProxySQL 简介和安装
参考文档
- https://www.jianshu.com/p/59ceba884a8d
- https://www.jianshu.com/p/41f0774226df
- https://www.jianshu.com/p/121e3196cf29
- https://www.cnblogs.com/f-ck-need-u/p/9278818.html
- https://www.cnblogs.com/f-ck-need-u/p/7586194.html#auto_id_4
- https://github.com/malongshuai/proxysql/wiki
- https://my.oschina.net/cart/blog/2907232?from=singlemessage&isappinstalled=0
创建主从复制的数据库
1# 创建 master 库
2mkdir -p /app/mysqldata/3306/{data,logs,tmp}
3chown -R mysql:mysql /app/mysqldata/3306
4mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/app/mysqldata/3306/data --initialize-insecure
5
6# 创建 slave 库
7mkdir -p /app/mysqldata/3307/{data,logs,tmp}
8chown -R mysql:mysql /app/mysqldata/3307
9mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/app/mysqldata/3307/data --initialize-insecure
10
11# 创建 slave 库
12mkdir -p /app/mysqldata/3308/{data,logs,tmp}
13chown -R mysql:mysql /app/mysqldata/3308
14mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/app/mysqldata/3308/data --initialize-insecure
创建数据库启动的配置文件
新建 master 的配置文件 /app/mysqldata/3306/my.cnf，内容如下：
1[client]
2port=3306
3socket=/app/mysqldata/3306/tmp/mysql.sock
4[mysqld]
5user=mysql
6server-id=3306
7port=3306
8mysqlx_port=33060
9basedir=/usr/local/mysql
10datadir=/app/mysqldata/3306/data
11plugin-dir=/usr/local/mysql/lib/plugin
12tmpdir=/app/mysqldata/3306/tmp
13socket=/app/mysqldata/3306/tmp/mysql.sock
14mysqlx_socket=/app/mysqldata/3306/tmp/mysqlx.sock
15pid-file=/app/mysqldata/3306/tmp/mysql.pid
16log-bin=/app/mysqldata/3306/logs/mysql-bin
17relay_log=/app/mysqldata/3306/logs/mysql-relay-bin
18log-error=/app/mysqldata/3306/tmp/mysql-error.log
19log_slave_updates=1
20read_only=0
21character-set-server=utf8mb4
22collation-server=utf8mb4_unicode_ci
再新建 slave 的配置文件： /app/mysqldata/3307/my.cnf 和 /app/mysqldata/3308/my.cnf，
其内容和master的配置文件大致相同，只要把所有出现3306的字符串替换为3307或3308，并且都把read_only的值设置为1
启动所有的数据库实例
1mysqld --defaults-file=/app/mysqldata/3306/my.cnf &
2mysqld --defaults-file=/app/mysqldata/3307/my.cnf &
3mysqld --defaults-file=/app/mysqldata/3308/my.cnf &
数据库的初始化
登录master：
1mysql -S /app/mysqldata/3306/tmp/mysql.sock
执行如下命令：
1-- 创建远程登录的root用户
2mysql> create user 'root'@'%' identified WITH mysql_native_password by 'root';
3mysql> grant all privileges on *.* to 'root'@'%' with grant option;
4
5-- 创建用于同步的用户账号
6mysql> CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'repl';
7mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
8
9-- 创建监控的账号
10mysql> CREATE USER 'monitor'@'%' IDENTIFIED WITH mysql_native_password BY 'monitor';
11mysql> GRANT REPLICATION CLIENT ON *.* TO 'monitor'@'%';
12
13-- 刷新权限
14mysql> flush privileges;
15
16-- 查看master状态，记录二进制文件名和位置
17mysql> SHOW MASTER STATUS;
18+------------------+----------+--------------+------------------+-------------------+
19| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
20+------------------+----------+--------------+------------------+-------------------+
21| mysql-bin.000003 | 2332 | | | |
22+------------------+----------+--------------+------------------+-------------------+
231 row in set (0.01 sec)
强烈建议：同时在slave中执行上述命令，那么主动切换的时候出错的概率要小很多。
启用主从同步
登录3307的slave：
1mysql -S /app/mysqldata/3307/tmp/mysql.sock
执行同步：
1-- 执行同步SQL语句
2mysql> CHANGE MASTER TO MASTER_HOST='localhost', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=2332;
3
4-- 启动slave同步进程
5mysql> start slave;
6
7-- 查看slave状态：当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了
8mysql> show slave status\G
3308的slave也执行上述操作。
创建 ProxySQL 的用户和用户组
1groupadd proxysql
2useradd -r -g proxysql -s /bin/false proxysql
安装 ProxySQL 的相关依赖，然后下载并安装 ProxySQL 2.0.5
1yum install -y perl-DBD-MySQL perl-IO-Socket-SSL
2wget https://github.com/sysown/proxysql/releases/download/v2.0.5/proxysql-2.0.5-1-centos7.x86_64.rpm
3rpm -ivh proxysql-2.0.5-1-centos7.x86_64.rpm
proxysql 管理
- 启动 ProxySQL：service proxysql start
- 停止 ProxySQL：service proxysql stop
- 查看 ProxySQL 状态：service proxysql status
- 查看ProxySQL的版本：proxysql –version
ProxySQL的配置
ProxySQL的配置文件位于：/etc/proxysql.cnf，
但是ProxySQL只有在首次运行的时候才加载配置文件，然后把配置持久化到数据库中；配置通过ProxySQL的管理接口(admin interface)进行修改。
说明：ProxySQL内置的数据库为sqlite，因此，可以使用sqlite的语法，如：
查询表的字段：PRAGMA table_info ('mysql_query_rules')
当然，ProxySQL也可以重置所有配置：proxysql --initial
ProxySQL启动后会监听两个端口，默认为6032和6033。
6032端口是ProxySQL的管理端口，6033是ProxySQL对外提供服务的端口。
在终端中输入如下内容以连接到ProxySQL：
1mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt='Admin> ' --default-auth=mysql_native_password
连接成功后的显示如下：
1-- 查询有哪些数据库
2Admin> SHOW DATABASES;
3
4-- 显示有哪些表
5admin> show tables from main;
6admin> show tables from monitor;
7
8-- 插入可读写的 mysql server (hostgroup_id 用于区分可读写、只读，其值可自行指定， 本例中hostgroup_id等于1时表示是读写，等于2时表示只读)
9Admin> insert into mysql_servers(hostgroup_id, hostname, port) values(1, '127.0.0.1', 3306), (2, '127.0.0.1', 3307), (2, '127.0.0.1', 3308);
10
11-- 查询 mysql server
12Admin> SELECT * FROM mysql_servers;
13
14-- 以下两行命令表示：修改后，加载到RUNTIME，并保存到disk
15Admin> load mysql servers to runtime;
16Admin> save mysql servers to disk;
17
18
19-- 添加主从账号
20Admin> insert into mysql_users(username, password, default_hostgroup, transaction_persistent) values('repl', 'repl', 1, 1);
21
22-- 查看主从账号：active=1的用户才是有效的用户；transaction_persisten=1时，表示事务持久化
23Admin> select * from mysql_users\G
24*************************** 1. row ***************************
25 username: repl
26 password: repl
27 active: 1
28 use_ssl: 0
29 default_hostgroup: 1
30 default_schema: NULL
31 schema_locked: 0
32transaction_persistent: 1
33 fast_forward: 0
34 backend: 1
35 frontend: 1
36 max_connections: 10000
37 comment:
381 row in set (0.00 sec)
39
40Admin> load mysql users to runtime;
41Admin> save mysql users to disk;
42
43
44-- 添加监控账号
45Admin> set mysql-monitor_username='monitor';
46Admin> set mysql-monitor_password='monitor';
47
48-- 以下两行命令表示：修改后，加载到RUNTIME，并保存到disk
49Admin> load mysql variables to runtime;
50Admin> save mysql variables to disk;
51
52-- 查看监控账号
53SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';
54
55-- 通过监控账号，检测连接是否正常：connect_error为NULL则表示正常
56Admin> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;
57+-----------+------+------------------+-------------------------+---------------+
58| hostname | port | time_start_us | connect_success_time_us | connect_error |
59+-----------+------+------------------+-------------------------+---------------+
60| 127.0.0.1 | 3308 | 1566547879975160 | 2480 | NULL |
61| 127.0.0.1 | 3306 | 1566547880528419 | 3341 | NULL |
62| 127.0.0.1 | 3307 | 1566547881082984 | 2337 | NULL |
63+-----------+------+------------------+-------------------------+---------------+
643 rows in set (0.00 sec)
65
66-- 通过监控账号，检测心跳是否正常：ping_error 字段为NULL则表示正常
67Admin> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;
68+-----------+------+------------------+----------------------+------------+
69| hostname | port | time_start_us | ping_success_time_us | ping_error |
70+-----------+------+------------------+----------------------+------------+
71| 127.0.0.1 | 3306 | 1566547828907348 | 966 | NULL |
72| 127.0.0.1 | 3307 | 1566547829008973 | 552 | NULL |
73| 127.0.0.1 | 3308 | 1566547829110779 | 346 | NULL |
74+-----------+------+------------------+----------------------+------------+
753 rows in set (0.00 sec)
76
77-- 配置主从自动切换，以保证高可用(必须互为主从，如果没有实现请跳过如下两天命令)
78Admin> insert into mysql_replication_hostgroups values(1,2,'HA');
79Admin> select * from mysql_replication_hostgroups;
80
81-- 读写分离规则，正则写法，也支持全匹配SQL
82Admin> insert into mysql_query_rules(rule_id, active, match_digest, destination_hostgroup, apply) values
83 (1, 1, '^UPDATE', 1, 1),
84 (2, 1, '^SELECT .* FOR UPDATE$', 1, 1),
85 (3, 1, '^SELECT', 2, 1);
86
87Admin> select * from mysql_query_rules\G
88
89Admin> load mysql query rules to runtime;
90Admin> save mysql query rules to disk;
测试
1[root@localhost app]# mysql -urepl -prepl -P6033 -h127.0.0.1 -e 'select @@server_id'
2mysql: [Warning] Using a password on the command line interface can be insecure.
3+-------------+
4| @@server_id |
5+-------------+
6| 3308 |
7+-------------+
8[root@localhost app]# mysql -urepl -prepl -P6033 -h127.0.0.1 -e 'select @@server_id'
9mysql: [Warning] Using a password on the command line interface can be insecure.
10+-------------+
11| @@server_id |
12+-------------+
13| 3307 |
14+-------------+
15[root@localhost app]# mysql -urepl -prepl -P6033 -h127.0.0.1 -e 'start transaction;select @@server_id;commit;'
16mysql: [Warning] Using a password on the command line interface can be insecure.
17+-------------+
18| @@server_id |
19+-------------+
20| 3306 |
21+-------------+
测试结果：
1mysql -urepl -prepl -P6033 -h127.0.0.1 -e 'select @@server_id' 模拟读请求，3307、3308随机出现
2
3mysql -urepl -prepl -P6033 -h127.0.0.1 -e 'start transaction;select @@server_id;commit;' 模拟写请求，只使用3306的数据库
然后回到ProxySQL上配置监控。
1Admin> set mysql-monitor_username='repl';
2Query OK, 1 row affected (0.06 sec)
3
4Admin> set mysql-monitor_password='repl';
5Query OK, 1 row affected (0.01 sec)
6
7Admin> insert into mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup) values (1, 2);
8Query OK, 1 row affected (0.00 sec)
9
10Admin> load mysql servers to runtime;
11Query OK, 0 rows affected (0.01 sec)
12
13Admin> save mysql servers to disk;
14Query OK, 0 rows affected (0.04 sec)
监控后端MySQL节点
在 ProxySQL 中 添加完 mysql server 后，还需要监控后端节点。
对于后端是主从复制的环境来说，这是必须的，因为ProxySQL需要通过每个节点的 read_only 值来自动调整它们是属于读组还是写组。
所以：
- 修改slave的 my.cnf, 添加 read_only=1, 然后重启slave使之生效。
- 首先在后端master节点上创建一个用于监控的用户名(只需在master上创建即可，因为会复制到slave上)，这个用户名只需具有USAGE权限即可。
如果还需要监控复制结构中slave是否严重延迟于master(术语叫做"replication lag")，则还需具备replication client权限。
登录master，给主从同步的用户授予 REPLICATION SLAVE 和 REPLICATION CLIENT 的权限。
1-- 主从同步的用户；如果存在，无需创建
2mysql> CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'repl';
3
4-- 授予主从同步、监控的权限
5mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%';
6
7-- 使配置生效
8mysql> flush privileges;
评论