【MySQL高可用集群 七】ProxySQL & MySQL主从环境搭建

【MySQL高可用集群 一】MySQL主从搭建（传统方式）

【MySQL高可用集群 二】MySQL主从搭建（GTID方式）

【MySQL高可用集群 三】MySQL双主搭建

【MySQL高可用集群 四】MySQL多主多从

【MySQL高可用集群 五】MySQL的MGR集群

【MySQL高可用集群 六】ProxySQL安装部署

【MySQL高可用集群 七】ProxySQL&MySQL 主从环境搭建

【MySQL高可用集群 八】ProxySQL&MGR 环境搭建

【MySQL高可用集群 九】ProxySQL集群 环境搭建

【MySQL高可用集群 】相关SQL脚本

环境准备：

Ubuntu 22.04 LTS （五台虚拟机），MySQL 8.0.33-0ubuntu0.22.04.4，ProxySQL 2.5.4-58-gd15b40a 。

虚拟机环境搭建可以参考：https://www.cnblogs.com/hewei-blogs/articles/17569105.html。

主机信息：

10.53.207.20 （MySQL master）  
10.53.207.21 （MySQL master）
10.53.207.22 （MySQL slave）
10.53.207.23 （MySQL slave）
10.53.207.24 （ProxySQL 服务主机）

建议大家在ubuntu 系统环境准备时，顺便在各个节点的 /etc/hosts 文件中添加配置所有节点域名和ip的映射。因为MySQL的 dns 反向解析 可能会导致 ProxySQL 和MySQL 之间的连接问题。

本人映射配置如下：

10.53.207.20    10.53.207.20
10.53.207.21    10.53.207.21
10.53.207.22    10.53.207.22
10.53.207.23    10.53.207.23
10.53.207.24    10.53.207.24

一、安装配置mysql

su root
apt install mysql-server
netstat -anp | grep mysql
mysql -u root -p

安装成功后，要求密码时直接回车。如果回车登录不了，则执行以下命令，使用查到的默认生产密码登录。

cat /var/log/mysqld.log | grep 'temporary password'

重置root账号密码及权限（需要远程连接的账户建议设置密码校验方式为 mysql_native_password，密码可以自定义设置，我这里设置账号密码都为 root）

use mysql;
alter user 'root'@'localhost' identified with mysql_native_password by 'root';
#允许root远程访问
update user set host='%' where user='root';
#使授权信息生效
flush privileges;

或者直接新建用户后授权

#create user 'root'@'%' identified with mysql_native_password by 'root';
#grant all privileges on *.* to 'root'@'%';

查看更新后的用户信息并退出mysql

select Host,user,authentication_string,plugin from user;
quit;

修改mysqld.cnf 文件，将 bind-address = 127.0.0.1 改为 0.0.0.0 ，让mysql监听任何地址

vi /etc/mysql/mysql.conf.d/mysqld.cnf
#重启并使用重置的密码进行登录
service mysql restart
mysql -uroot -p

注意 mysql 密码插件，二者的混用可能导致一些认证问题：
如果远程登录mysql提示： The user specified as a definer ('mysql.infoschema'@'localhost') does not exist，可能是修改了用户的密码插件，执行下面语句然后重启mysql服务
update user set plugin = 'caching_sha2_password' where user='mysql.sys' or user='mysql.infoschema';

mysql_native_password    mysql8 以前的密码加密校验插件，加密的密码以 * 开头，远程连接建议使用此插件
caching_sha2_password   mysql8 及以后版本的默认加密插件，加密的密码以 $A$005$ 开头，连接需要公钥

二、安装配置 ProxySQL

ubuntu下添加源：

apt-get install -y --no-install-recommends lsb-release wget apt-transport-https ca-certificates gnupg
wget -O - 'https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/repo_pub_key' | apt-key add - 
echo deb https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/$(lsb_release -sc)/ ./ | tee /etc/apt/sources.list.d/proxysql.list

wget -nv -O /etc/apt/trusted.gpg.d/proxysql-2.5.x-keyring.gpg 'https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/repo_pub_key.gpg'

命令下载装：

apt-get update
apt-get install proxysql OR apt-get install proxysql=version

由于网络问题，我直接使用 下面 deb 包文件进行安装。

下载：

wget https://github.com/sysown/proxysql/releases/download/v2.5.4/proxysql_2.5.4-ubuntu22_amd64.deb

安装：

sudo dpkg -i proxysql_2.5.4-ubuntu22_amd64.deb

配置文件位置： /etc/proxysql.cnf
默认文件路径：
datadir="/var/lib/proxysql"
errorlog="/var/lib/proxysql/proxysql.log"

检查安装是否成功：

root@ubuntu-manager:/# proxysql --version
ProxySQL version 2.5.4-58-gd15b40a, codename Truls

服务启动&停止

service proxysql stop | service proxysql start | service proxysql restart

#安装mysql客户端

apt install mysql-client-core-8.0

当前主机登录ProxySQL：

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '

#查看数据库
show databases;
#查看公共变量
select * from global_variables;
#修改内置MySQL的版本(如果ProxySQL和MySQL版本不兼容，会导致很多问题，所以提前在这里设置成所安装的MySQL服务版本)
update global_variables set variable_value="8.0.33" where variable_name='mysql-server_version';
#查看管理员账户(console 入口的用户)
select @@admin-admin_credentials;
#设置管理员账户(admin账号只能在本地进行连接，添加远程连接的账户密码 hadmin:hpasswd) 
set admin-admin_credentials='admin:admin;hadmin:hpasswd'; 
#使修改立即生效 
load admin variables to runtime; 
#修改立即保存到磁盘 
save admin variables to disk;

 在外部主机登录新建的admin控制账号，如果出问题，命令尾尝试添加 --default-auth=mysql_native_password

远程连接可以执行下列任一命令

mysql -uhadmin -phpasswd -h10.53.207.24 -P6032
mysql -uhadmin -phpasswd -h10.53.207.24 -P6032 --prompt="ProxySQL> "
mysql -uhadmin -phpasswd -h10.53.207.24 -P6032 --default-auth=mysql_native_password --prompt="ProxySQL> "

经过本人长时间的远程连接尝试，上面的命令只有在命令行中通过mysql client 进行连接是可以成功的，但是在其他的图形工具例如navcat，dbeaver等中无法正常使用。并且 prompt 后面的字符一定要用双引号包围，使用单引号也无法连接，例如 --prompt='ProxySQL> ' 连接一直无返回操作台（可能ProxySQL解析有问题）。

开启proxySQL的web控制台：

#查看普通用户(web 入口的用户)
select @@admin-stats_credentials;
#设置普通用户
set admin-stats_credentials='stats:stats;sqlstats:sqlstats';
#开启web界面访问，访问地址：https://服务IP:6080/
set admin-web_enabled=true;
load admin variables to runtime;
save admin variables to disk;

三、MySQL&ProxySQL 读写分离配置（以下代码中注释的配置可以自定义配置，我这里不自定义，使用默认的）

 

大概的配置步骤如下：
1、配置MySQL主从集群。
2、ProxySQL的mysql_servers中添加MySQL Server所有服务节点。
3、配置ProxySQL监控，在全局变量表global_vairbles中和MySQL各个服务节点添加监控账户。ProxySQL相关的变量为mysql-monitor_开头的变量。
4、配置ProxySQL的读、写组。相关的表为mysql_replication_hostgroups。
5、配置MySQL用户，用于发送SQL语句。在MySQL各个服务节点创建用户，并在ProxySQL的mysql_users 表以进行用户配置。
6、配置ProxySQL的路由规则，实现读写分离。规则配置在ProxySQL的 mysql_query_rules 表中。

 对各个mysql服务节点配置文件 /etc/mysql/my.cnf 进行配置。MySQL中，1表示True，0表示False，配置文件中值0和OFF都是表示关闭，1和ON表示开启。用户的host字段中%表示地址通配符。

（一）配置Master（10.53.207.21）

[mysqld]
server-id=21
sync-binlog=1
#不开启binlog的数据库
binlog_ignore_db = mysql
#数据目录
#datadir=/data/mysql
#开启binlog，当前默认已开启（主库的binlog必须开启）
#log-bin=/data/mysql/master-bin 
#端口文件
#socket=/data/mysql/mysql.sock 
#log-error=/data/mysql/error.log
#pid-file=/data/mysql/mysqld.pid
#开启binlog的数据库
#binlog_do_db = my_data

master 配置完以后创建有数据复制权限的账户，从库将通过此账户进行数据同步，'repl'@'10.53.207.%' 表示 只有以 10.53.207 开头的主机可以通过账号 repl 进行连接。

create user 'repl'@'10.53.207.%' identified by 'P@repl';
grant replication slave on *.* to 'repl'@'10.53.207.%';
service mysql restart

（二）配置slave（10.53.207.22 | 10.53.207.23）, 注意 server-id和主机ip最后一个位段（也就是最后两位）保持相同

[mysqld]
server-id=22
#从库的只读属性 read_only 必须开启
read_only=1
#关闭binlog（参考很多教程从库也开启了binlog，我这里进行了关闭，经过测试从库可以正常同步主库。binlog日志用于主从同步，从库不需要被其他库同步，所以关闭）
skip-log-bin
#datadir=/data/mysql
#socket=/data/mysql/mysql.sock 
#relay_log=/data/mysql/relay-log
#log-error=/data/mysql/error.log
#pid-file=/data/mysql/mysqld.pid

在从库各节点MySQL命令行中执行以下命令，用来设置主从同步信息（master_log_file可以根据主库的日志情况设置，初始集群建议设置为未配置主从同步前最大序号的文件。可以通过MySQL的配置文件my.cnf来查看binlog日志文件的存储位置，我本地默认在 /var/lib/mysql 文件夹下。）
mysql8 由于repl账户加密默认使用 caching_sha2_password 插件，需要添加 get_master_public_key=1 选项，8以下的版本不需要加。

change master to 
    master_host='10.53.207.21',
    master_port=3306,
    master_user='repl',
    master_password='P@repl',
    master_log_file='binlog.000010',
    master_log_pos=0,
    get_master_public_key=1;

开启并查看主从同步情况

mysql> start slave;
mysql> show slave status\G;

注意：如果修改mysql默认的数据目录datadir，还需要做以下修改（由于修改比较繁琐，本人这里没有进行自定义配置，使用了默认目录）：
编辑linux下 apparmor安全的限制

vim /etc/apparmor.d/usr.sbin.mysqld

找到： 

# Allow data dir access    
  /var/lib/mysql/ r,
  /var/lib/mysql/** rwk,

修改为：

# Allow data dir access    
  /data/mysql/ r,
  /data/mysql/** rwk,

修改/usr/share/mysql/mysql-systemd-start文件，将类似"/var/lib/mysql"的内容全部修改为"/data/mysql"

#重启apparmor
service apparmor restart
#mysql初始化
sudo mysqld --initialize-insecure --user=mysql --datadir=/data/mysql

（三）配置ProxySQL

前期准备：由于mysql中对连接客户端进行dns反向解析，可能会导致proxysql和mysql连接异常（此处超级坑，害我排查好久）比如ProxySQL报错如下：
Server 10.53.207.21:3306 missed 3 heartbeats, shunning it and killing all the connections. Disabling other checks until the node comes back online.
解决方案如下（本人推荐方案一）：
方案一、在mysql服务各个节点的 /etc/hosts 文件进行域名和地址映射的配置，我的配置如下：

#域名            IP
10.53.207.20    10.53.207.20
10.53.207.21    10.53.207.21
10.53.207.22    10.53.207.22
10.53.207.23    10.53.207.23
10.53.207.24    10.53.207.24

方案二、可以在 my.cnf 中加入 skip-name-resolve ，这样一来mysql 将不支持域名连接的dns反向解析。

1、向proxysql 添加 mysql 服务节点

admin> insert into mysql_servers(hostgroup_id,hostname,port,comment) values(1,'10.53.207.21',3306,"master-21");
admin> insert into mysql_servers(hostgroup_id,hostname,port,comment) values(1,'10.53.207.22',3306,"slave-22");
admin> insert into mysql_servers(hostgroup_id,hostname,port,comment) values(1,'10.53.207.23',3306,"slave-23");

加载到runtime及时生效，并存储到disk持久化

admin> load mysql servers to runtime;
admin> save mysql servers to disk;

2、添加对MySQL节点的监控和分组信息

在 每个mysql服务节点上创建监控账户（由于本例的master节点binlog排除了 mysql 库，所以需要在每个节点都创建监控用户，如果mysql 库也产生binlog的话可以只在master上创建用户，mysql库中用户信息会同步至其他slave节点）

mysql> create user 'monitor'@'10.53.207.%' identified with mysql_native_password by 'P@monitor';

如果需要修改监控账户，执行命令

mysql> ALTER user 'monitor'@'10.53.207.%' identified with mysql_native_password by 'P@monitor';

对账户进行授权

mysql> grant replication client on *.* to 'monitor'@'10.53.207.%';
mysql> grant select on *.* to `monitor`@`10.53.207.%`; #mgr集群需要的授权，普通主从可不授权
mysql> flush privileges;

然后回到 proxysql 中添加已创建的监控账户

admin> set mysql-monitor_username='monitor';
admin> set mysql-monitor_password='P@monitor';
admin> load mysql variables to runtime;
admin> save mysql variables to disk;

验证监控结果：

ProxySQL监控模块的指标都保存在monitor库的log表中。

#连接监控
admin> select * from mysql_server_connect_log;
#心跳监控
admin> select * from mysql_server_ping_log;
#read_only监控
admin> select * from mysql_server_read_only_log;
#replication_lag监控
admin> select * from mysql_server_replication_lag_log;

如下图表示监控配置成功

 

在ProxySQL中添加主机分组信息：

insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,comment) values(1,2,'cluster1(master:1-slave:2)');

添加了监控和分组信息以后，ProxySQL会自动根据MySQL中的read_only 属性将节点移动至对应分组下，0 对应写组，1 对应读组。通过下面语句查看运行时节点动态分组：

select * from runtime_mysql_servers;

 

 发现主节点同时出现在读写组中，这是因为 主节点本身就是可以读写的，如果只允许主节点进行写入，需要在ProxySQL执行以下命令，设置属性 mysql-monitor_writer_is_also_reader 的值为 false。

#设置主节点只接受写操作
set mysql-monitor_writer_is_also_reader='false';
load mysql variables to runtime;
save mysql variables to disk;
load mysql servers to runtime;

3、添加mysql数据操作用户

在每个mysql服务节点创建数据操作账户

create user 'operator'@'%' identified with mysql_native_password by 'P@operator';
grant insert,delete,select,update on *.* to 'operator'@'%';
flush privileges;

返回ProxySQL，在 mysql_users 表中添加数据操作账户，root和operator账户default_hostgroup值为1，如果没有在 mysql_query_rules 表中添加路由规则，默认都路由到主机分组 1 上，也就是master上。

insert into mysql_users(username,password,default_hostgroup,comment) values('root','root',1,"root账户");
insert into mysql_users(username,password,default_hostgroup,comment) values('operator','P@operator',1,"数据操作账户");
load mysql users to runtime;
save mysql users to disk;

在远程MySQL客户端进行连接验证，server_id =10 是我们最初配置的 master 服务的 serverid，路由正确。

mysql -uoperator -pP@operator -P6033 -h10.53.207.24 -e "select @@server_id"

 4、ProxySQL读写分离的路由规则配置

ProxySQL中有两个规则路由表 mysql_query_rules和mysql_query_rules_fast_routing ，后者是前者的扩展表。ProxySQL可以基于用户、基于schema以及基于每个语句自定义路由规则，规则的优先级根据rule_id的顺序决定，rule_id越大优先级越高，rule_id是自增的，也可以在插入时指定。SELECT…FOR UPDATE它会申请写锁，所以路由到分组 1 上。

INSERT INTO mysql_query_rules
(active,match_digest,destination_hostgroup,apply,comment)
VALUES 
(1,'^SELECT.*FOR UPDATE$',1,1,'加锁的查询'),
(1,'^SELECT',2,1,'普通查询'),
(1,'^DELETE',1,1,'删除操作'),
(1,'^UPDATE',1,1,'更新操作'),
(1,'^INSERT',1,1,'新增操作');

使配置生效并持久化

load mysql query rules to runtime;
save mysql query rules to disk;

在ProxySQL中可以通过 stats_mysql_query_digest 表查询路由的统计信息。

SELECT hostgroup, sum_time,count_star,digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

远程通过mysql客户端连接ProxySQL进行验证。

如下图，查询语句都路由到了从节点。

 通过事务验证写操作，可见事务语句路由到了 server_id = 10 的主节点，查询语句路由到了 server_id = 21 的从节点。

mysql -uoperator -pP@operator -P6033 -h10.53.207.24 -e "start transaction;select @@server_id;commit;select @@server_id;"

 

 

原创文章，转载注明出处。

原文连接：https://www.cnblogs.com/hewei-blogs/articles/17620887.html