系列文章目录

第四篇：MySQL数据库实现主从同步https://blog.csdn.net/m0_51864249/article/details/120687861?spm=1001.2014.3001.5501https://blog.csdn.net/m0_51864249/article/details/120687861?spm=1001.2014.3001.5501

文章目录

目录​​​​​​​

前言

一、准备工作

二、使用步骤

1.准备ProxySQL软件

 2.安装配置

 3.启动服务并查看

 4.在mysql上配置账号并授权

 5.proxysql默认数据库说明

 6.配置proxysql管理用户

7.在master-SQL和slave-SQL上添加监控用户 

8.在proxysql主机端配置监控用户

 9.配置proxysql的转发规则

​​​​​​​10.配置proxysql

11.更新配置到RUNTIME中

12.将所有配置保存至磁盘上

13.测试读写分离

总结

---

---

前言

本文主要讲MySQL数据库实现主从同步后进行的读写分离，主从同步在上面的链接中。

一、准备工作

1.打开ProxySQL、MySQL-master、MySQL-slave这三台虚拟机

2.关闭防火墙

3.部署Keepalived双机热备+LVS负载均衡群集+MySql主从同步读写分离，网络拓扑结构如图：

二、使用步骤

1.准备ProxySQL软件

yum -y install wget
wget https://github.com/sysown/proxysql/releases/download/v1.4.8/proxysql-1.4.8-1-centos7.x86_64.rpm
ll proxysql-1.4.8-1-centos7.x86_64.rpm 

 2.安装配置

yum install -y proxysql-1.4.8-1-centos7.x86_64.rpm
rpm -ql  proxysql

 我这里之前安装过，所以不需要安装

 3.启动服务并查看

/etc/init.d/proxysql start
ss -lntup|grep proxy

 4.在mysql上配置账号并授权

注意：这里需要在主从两台服务器上的MySQL都进行配置

mysql -uroot -p123456
GRANT ALL ON *.* TO 'proxysql'@'192.168.163.%' IDENTIFIED BY '123456';
flush privileges;

 5.proxysql默认数据库说明

yum -y install mysql
mysql -uadmin -padmin -h127.0.0.1 -P6032
MySQL [(none)]> show databases;

 6.配置proxysql管理用户

MySQL [(none)]> use main;
MySQL [main]> show tables;
MySQL [main]> exit;

 重新进入proxySQL数据库，执行如下命令

mysql -uadmin -padmin -h127.0.0.1 -P6032

MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'db1','3306',1,'Write Group');

MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,'db2','3307',1,'Read Group');

select * from mysql_servers;
出现如下内容：
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment     |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
| 1            | db1      | 3306 | onLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | Write Group |
| 2            | db2      | 3307 | onLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | Read Group  |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+

MySQL [(none)]> use main;

MySQL [main]> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('proxysql','123456',1);

MySQL [main]> select * from mysql_users;

7.在master-SQL和slave-SQL上添加监控用户 

俩台都需要执行

 mysql -uroot -p123456   #登录数据库

mysql> GRANT SELECt ON *.* TO 'monitor'@'192.168.163.%' IDENTIFIED BY 'monitor';

flush privileges;

8.在proxysql主机端配置监控用户

MySQL [main]> set mysql-monitor_username='monitor';

MySQL [main]> set mysql-monitor_password='monitor';

 9.配置proxysql的转发规则

MySQL [main]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',1,1);

MySQL [main]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',2,1);

MySQL [main]> select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules;

MySQL [main]> exit；

10.配置proxysql

mysql -uadmin -padmin -h127.0.0.1 -P6032 

MySQL [(none)]> delete from mysql_servers;

MySQL [(none)]> insert into mysql_servers (hostgroup_id, hostname, port) values(1,'192.168.163.121',3306);

MySQL [(none)]> insert into mysql_servers (hostgroup_id, hostname, port) values(1,'192.168.163.122',3306);

MySQL [(none)]> insert into mysql_servers (hostgroup_id, hostname, port) values(2,'192.168.163.121',3306);

MySQL [(none)]> insert into mysql_servers (hostgroup_id, hostname, port) values(2,'192.168.163.121',3306);

MySQL [(none)]> select * from  mysql_servers ;

11.更新配置到RUNTIME中

MySQL [main]> delete from mysql_query_rules;

MySQL [main]> commit;

MySQL [main]> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)

MySQL [main]> load mysql servers to runtime;
Query OK, 0 rows affected (0.02 sec)

MySQL [main]> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)

MySQL [main]> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)

MySQL [main]> load admin variables to runtime;
Query OK, 0 rows affected (0.00 sec)

12.将所有配置保存至磁盘上

MySQL [main]> save mysql users to disk;
Query OK, 0 rows affected (0.03 sec)

MySQL [main]> save mysql servers to disk;
Query OK, 0 rows affected (0.04 sec)

MySQL [main]> save mysql query rules to disk;
Query OK, 0 rows affected (0.03 sec)

MySQL [main]> save mysql variables to disk;
Query OK, 94 rows affected (0.02 sec)

MySQL [main]> save admin variables to disk;
Query OK, 31 rows affected (0.02 sec)

MySQL [main]> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)

MySQL [main]> exit；

13.测试读写分离

mysql -uproxysql -p123456 -h 127.0.0.1 -P 6033

MySQL [(none)]> show databases;

完成！！！！！ 这里就是我们的数据库

在proxysql管理端查看读写分离

mysql -uadmin -padmin -h127.0.0.1 -P6032
MySQL [main]> use main；
MySQL [main]> select * from stats_mysql_query_digest;

---

总结

        不同版本在主从同步和读写分离中可能会遇到不同的问题，所有就需要我们及时的做快照，然后按照网上不同的教学来改正自己的问题。