草稿：MySQL 5.7服务集群: MGR + MySQLRouter + MySQLshell + ProxySQL

MySQL：

定义变量：

generic_version=Percona-Server-5.7.29-32
generic_name=${generic_version}-Linux.x86_64.ssl101
generic_pack=${generic_name}.tar.gz

yum -y install autoconf libaio-devel.x86_64 numactl.x86_64 -y
useradd -M -s /sbin/nologin mysql

下载MySQL二进制包：

url=www.percona.com/downloads/Percona-Server-5.7
wget https://${url}/${generic_version}/binary/tarball/${generic_pack}

mv ${generic_name} /opt/${generic_version}
ln -s /opt/${generic_version} /usr/local/mysql
chown mysql.mysql /opt/${generic_name} -R

配置环境变量：

cat > /etc/profile.d/MY-BIN.sh <<EOF
#!/bin/bash
export MYSQL_BIN=/usr/local/mysql/bin
export PATH=\${PATH}:\${MYSQL_BIN}
EOF
source  /etc/profile.d/MY-BIN.sh
chmod 700 /etc/profile.d/MY-BIN.sh

myport=3306
mkdir -p /data/mysql_${myport}_core/{conf,data,log/{error_log,slow_log,general_log,binary_log,relay_log,audit_log,ibdata_log,redo_log,undo_log},run,tmp}
chown -R mysql.mysql /data/mysql_${myport}_core/

初始化数据库：

/usr/local/mysql/bin/mysqld  --defaults-file=/data/mysql_${myport}_core/conf/my.cnf  \
--initialize-insecure --basedir=/usr/local/mysql/  --datadir=/data/mysql_${myport}_core/data/  \
--user=mysql

运行数据库：

/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql_${myport}_core/conf/my.cnf &

创建相应的账号

create user 'monitor'@'%' identified by 'monitor';
grant all privileges on *.* to 'monitor'@'%' with grant option;
CREATE USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION; FLUSH PRIVILEGES;

MysqlShell:

下载并安装

wget https://downloads.mysql.com/archives/get/p/43/file/mysql-shell-1.0.11-1.el7.x86_64.rpm
yum install -y mysql-shell-1.0.11-1.el7.x86_64.rpm

进入shell 创建集群

shell.connect('root@aws-hongkong-669-tmp-mysql-all-node-01:3306')

MySQLRouter:

wget https://downloads.mysql.com/archives/get/p/41/file/mysql-router-2.1.6-1.el7.x86_64.rpm
yum install -y  mysql-router-2.1.6-1.el7.x86_64.rpm

配置MySQLRouter

myhost=192.168.1.116
mysqlrouter --bootstrap root@${myhost}:3306 --user=root

ProxySQL:

安装并启动服务

url=github.com/sysown/proxysql/releases/download
wget https://${url}/v2.0.10/proxysql-2.0.10-1-centos7.x86_64.rpm
yum install -y proxysql-2.0.10-1-centos7.x86_64.rpm 
systemctl  start proxysql
mysql -uadmin -padmin -h127.0.0.1 -P6032

进入proxysql终端并配置服务器：

insert into mysql_servers(hostgroup_id,hostname,port) values (1,'192.168.9.248',6446);
insert into mysql_servers(hostgroup_id,hostname,port) values (1,'192.168.9.248',6447);
load mysql servers to runtime;
save mysql servers to disk;

添加MySQL账号：

insert into mysql_users(username,password,default_hostgroup) values ('root','root',1);
update mysql_users set transaction_persistent=1 where username='root';
load mysql users to runtime;
save mysql users to disk;

配置读写分离:

insert into mysql_replication_hostgroups values (1,2,'super_read_only','proxy');
load mysql servers to runtime;
save mysql servers to disk;
select * from mysql_replication_hostgroups;

配置路由规则：

insert into mysql_query_rules(active,match_pattern,destination_hostgroup, apply) VALUES(1,'^SELECT.*FOR UPDATE$',1,1);
insert into mysql_query_rules(active,match_pattern,destination_hostgroup, apply) VALUES(1,'^SELECT',2,1);
load mysql query rules to runtime;
save mysql query rules to disk;

查看路由规则：

select rule_id,active,match_pattern,destination_hostgroup,apply from mysql_query_rules;

查看执行的SQL语句：

select * from stats_mysql_query_digest;

其它调整（MySQL客服端连接报错）：

set mysql-set_query_lock_on_hostgroup=0;
load mysql variables to runtime;
save mysql variables to disk;