image.png

主从复制配置

master

# /etc/my.cnf
[mysqld]
log-bin
server-id=12

# create accout for replication
mysql -e "grant replication slave on *.* to repluser@'192.168.80.%' identified by 'password'"

# 给proxysql创建监控账号monitor
grant replication client on *.* to monitor@'192.168.80.%' identified by 'password';

# 给proxysql访问mysql创建账号slquser
 grant all on *.* to sqluser@'192.168.80.%' identified by 'password';

slave

[mysqld]
server-id=13
read-only

MariaDB [(none)]> CHANGE MASTER TO
    ->   MASTER_HOST='192.168.80.12',
    ->   MASTER_USER='repluser',
    ->   MASTER_PASSWORD='password',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='mariadb-bin.000001',
    ->   MASTER_LOG_POS=245;

MariaDB [(none)]> start slave;

ProxySQL配置

安装

# 配置yum源
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
# 安装
[root@80_11 ~]# yum install proxysql mariadb
# 启动
systemctl start proxysql

配置

# 连接
mysql -uadmin -padmin -P6032 -h127.0.0.1
# 添加mysql server
insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.80.12',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.80.13',3306);
load mysql servers to runtime;
save mysql servers to disk;
# 配置监控
set mysql-monitor_username='monitor';
set mysql-monitor_password='password';
load mysql variables to runtime;
save mysql variables to disk;

# 设置分组
insert into mysql_replication_hostgroups values(10,20,"test");
load mysql servers to runtime;
save mysql servers to disk;
MySQL [(none)]> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+---------------+------+--------+--------+
| hostgroup_id | hostname      | port | status | weight |
+--------------+---------------+------+--------+--------+
| 10           | 192.168.80.12 | 3306 | ONLINE | 1      |
| 20           | 192.168.80.13 | 3306 | ONLINE | 1      |
+--------------+---------------+------+--------+--------+

# 定义读写规则
# 添加访问mysql账号
insert into mysql_users(username,password,default_hostgroup) values('sqluser','password',10);
load mysql users to runtime;
save mysql users to disk;
# 添加规则
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) VALUES(1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',20,1);
load mysql query rules to runtime;
save mysql query rules to disk;

# 测试
# 测试读操作是否路由给20的读组
mysql -usqluser -ppassword -P6033 -h127.0.0.1 -e 'select @@server_id' 
# 测试写操作
[root@80_11 ~]# mysql -usqluser -ppassword -P6033 -h127.0.0.1 -e 'create database db1'
[root@80_11 ~]# mysql -usqluser -ppassword -P6033 -h127.0.0.1 -e 'select @@server_id'
+-------------+
| @@server_id |
+-------------+
|          12 |
+-------------+

# 路由的信息：查询stats库中的stats_mysql_query_digest表
MySQL > SELECT hostgroup hg,sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;