一口气搭建完MySQL主从同步及读写分离

vlambda
2021-07-04

一口气搭建完MySQL主从同步及读写分离

前言

一个半月前，公司的一个生产环境上了MySQL的读写分离，目的是来提高系统的性能，然而没两天就出了问题。那是一个周五的上午，同事开始发现数据变来变去的，如果主库的数据没有同步到从库上去，并且两次读取的数据库不一致的话，的确会产生这种情况。当时以为正常情况，直到错误数据越来越多，事情开始变的严重起来了，主库挂了，从库切换为主库了，但是之前有些数据没有同步到从库上来。

好家伙，赶紧把读写分离给撤了，但是错误数据已经存在了。然后开始漫长的修补数据的过程了，负责人周五晚上就开始搞，周六又整了一天。

直到现在，还有些不是特别影响业务操作的数据是错的。

又想到之前去UU跑腿面试的时候，问了一次读写分离的情况，问我怎么能在写库写完之后立马读到数据？我说那就读写库，他说那这就不是读写分离了。问我有什么别的办法，我觉得是没办法的，又要保证读写分离，又要立马读到写库写入的数据（除非写入的数据和之前的一致，这肯定不会是答案吧）。当时面试官还问我有没有本地搭建过读写分离的环境，那当然是没有了。

上周趁着有不想工作的心情，耗费了大概一天的时间在centos上搭建了MySQL的读写分离环境，项目测试了下也是成功的，这里简要记录下搭建的过程。具体可以参考以下两篇文章

https://blog.csdn.net/M_y_y/article/details/106501840 https://blog.51cto.com/bigboss/2103290

环境说明：

服务器：CentOS7数据库：MySQL5.7.34端口：3306、33073306为主数据库3307为从数据库ProxySQL版本：proxysql-1.4.8-1-centos7.x86_64.rpm

第一步：删除数据库

1. 查询之前安装的mysql：

rpm -qa|grep -i mysql

2. 删除，一个一个删除：

rpm -e mysql-community-client-... --nodeps

3. 查询mysql残留文件

find / -name mysql

4. 继续删除：

使用rm -rf 文件路径，一个个删除

第二步：安装数据库实例

1. 下载tar源码包官网下载

https://dev.mysql.com/downloads/mysql/mysql-5.7.34-el7-x86_64.tar.gz

2. 解压安装包：

tar -zxvf mysql-5.7.34-el7-x86_64.tar.gz

3. 更改解压后的名称

mv mysql-5.7.34-el7-x86_64 mysql5.7.34

4. 创建一些必要文件夹

创建3306文件夹，一并创建data、logs两个文件夹用于存放数据和日志  位置随意

5. 生成my.cnf配置文件

touch my.cnf 同时 vi my.cnf 将以下内容复制到文件中

# For advice on how to change settings please see# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html[mysqld]## Remove leading # and set to the amount of RAM for the most important data# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.# innodb_buffer_pool_size = 128M## Remove leading # to turn on a very important data integrity option: logging# changes to the binary log between backups.# log_bin## Remove leading # to set options mainly useful for reporting servers.# The server defaults are faster for transactions and fast SELECTs.# Adjust sizes as needed, experiment to find the optimal values.# join_buffer_size = 128M# sort_buffer_size = 2M# read_rnd_buffer_size = 2Mdatadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

6. 修改配置文件中默认路径为自己服务器上的路径

7. 给3307实例也拷贝一份

cp -r mysql3306/ mysql3307/

8. 修改3307数据库配置文件

使用vi my.cnf打开文件之后，将全局的3306更改为3307

9. 进入之前解压的mysql文件中的bin目录，初始化mysql3306

./mysqld --defaults-file=/opt/k3/mysql/mysql3306/my.cnf --initialize --datadir=/opt/k3/mysql/mysql3306/data/ --basedir=/opt/k3/mysql/mysql5.7.34 --user=root

会有默认密码出现，不用管它

10. 启动mysql3306 跳过密码安全连接、

./mysqld_safe --defaults-extra-file=/opt/k3/mysql/mysql3306/my.cnf --skip-grant-tables &

11. 查看运行状态

这时3306mysql已启动成功，使用以下命令查看运行状态：

ps -ef|grep 3306

12. 连接到mysql3306

./mysql -S /opt/k3/mysql/mysql3306/data/mysql.sock -P 3306

13. 密码修改以及授权远程访问

flush privileges;set password for 'root'@'localhost' = password('root');grant all privileges on *.* to'root' @'%' identified by 'root' with grant option;flush privileges;

14. 本地测试连接

直接Navicat进行连接测试

15. 同理操作mysql3307

第三步：开始做主同步的配置

1.修改3306my.cnf配置文件

log-bin=/opt/k3/mysql/mysql3306/logs/mysql-bin.log //自己存储log的位置server-id=1 //设置数据库服务器唯一ID，这里我们指定为1。binlog-do-db=test_k3_wms //设置需要同步的数据库，如果需要设置多个，则加入多条这行语句。

2.启动3306数据库实例

/opt/k3/mysql/mysql5.7.34/bin/mysqld --defaults-file=/opt/k3/mysql/mysql3306/my.cnf --port=3306

3. 连接3306数据库

/opt/k3/mysql/mysql5.7.34/bin/mysql --socket=/opt/k3/mysql/mysql3306/data/mysql.sock --port=3306 -u root -p

4.添加一个用于备份的用户：名称为slave（可自定义），密码为1234（可自定义）

grant replication slave on *.* to 'slave'@'%' identified by '1234';

5.查看用户是否创建成功

use mysql;select user ,host from user;

6.查看主机配置状态

show master status;

7.修改3307my.cnf配置文件

log-bin=/opt/k3/mysql/mysql3307/logs/mysql-bin.logserver-id=2

8.启动3307数据库实例并连接(同上)

9.创建与主机的关系

master_log_file和master_log_pos对应主库查询到的信息值，见上面图片（此处和图片中不一致，是因为中间执行过很多其他操作）

change master to master_host='192.168.142.*',master_user='slave',master_password='1234',master_port=3306,master_log_file='mysql-bin.000007',master_log_pos=154;

10.启动从机同步

start slave;

11.查看是否成功（这两步都是在从库上执行的）

两个字段都为YES就表示配置成功了

12.测试

这时候可以往主库里添加一条数据，看看从库是否能同步成功

第四步：使用ProxySQL实现读写分离

1.安装ProxySQL

wget https://github.com/sysown/proxysql/releases/download/v1.4.8/proxysql-1.4.8-1-centos7.x86_64.rpm#proxysql需要依赖一些perl库，所以使用yum安装yum install -y proxysql-1.4.8-1-centos7.x86_64.rpm#安装生成的文件：[root@ProxySQL ~]#rpm -ql proxysql/etc/init.d/proxysql    #启动脚本/etc/proxysql.cnf #配置文件，仅在第一次(/var/lib/proxysql/proxysql.db文件不存在)启动时有效 #启动后可以在proxysql管理端中通过修改数据库的方式修改配置并生效(官方推荐方式。)/usr/bin/proxysql #主程序文件/usr/share/proxysql/tools/proxysql_galera_checker.sh/usr/share/proxysql/tools/proxysql_galera_writer.pl

2.启动ProxySQL

/etc/init.d/proxysql start#proxysql客户端监听在6033端口上，管理端监听6032端口[root@ProxySQL ~]#/etc/init.d/proxysql startStarting ProxySQL: DONE![root@ProxySQL ~]#ss -tanl 查看监听情况

3.连接proxysql管理端进行配置：

#默认的管理端账号密码都是admin，登录进去之后可以修改变量进行修改账号密码mysql -uadmin -padmin -h127.0.0.1 -P6032
[root@ProxySQL ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032

注意：如果直接执行此命令失败的话，需求到mysql解压目录的下的bin目录下执行 ./mysql ...

4.添加后端的mysql主机：

insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'192.168.142.x',3306,1,'Write Group');insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,'192.168.142.x',3307,1,'Read Group');#使用insert语句添加主机到mysql_servers表中，其中：hostgroup_id 1 表示写组，2表示读组。MySQL [(none)]> select * from mysql_servers;

5.添加可以访问后端主机的账号：

注意：此命令要在mysql中执行，不是上一步登录的mysql，是3306端口的mysql

#在后端mysql中添加可以增删改查的账号GRANT ALL ON *.* TO 'proxysql'@'192.168.142.%' IDENTIFIED BY '123456';#在proxysql主机的mysql_users表中添加刚才创建的账号，proxysql客户端需要使用这个账号来访问数据库。insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('proxysql','123456',1,1);#default_hostgroup默认组设置为写组，也就是1#当读写分离的路由规则不符合时，会访问默认组的数据库MySQL [(none)]> insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('proxysql','123456',1,1);Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> select * from mysql_users\G 查看新增的用户信息

6.添加健康监测的账号：

这个命令和上面一条的一样，都是在3306端口下的mysql中操作的

#在后端主机中添加可以访问数据库的账号,SELECT权限即可GRANT SELECT ON *.* TO 'monitor'@'192.168.100.%' IDENTIFIED BY 'monitor';

#在proxysql管理端中修改变量设置健康检测的账号set mysql-monitor_username='monitor'set mysql-monitor_password='monitor'

7.添加读写分离的路由规则：

insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',1,1);insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',2,1);
#将select语句全部路由至hostgroup_id=2的组(也就是读组)#但是select * from tb for update这样的语句是修改数据的，所以需要单独定义，将它路由至hostgroup_id=1的组(也就是写组)#其他没有被规则匹配到的组将会被路由至用户默认的组(mysql_users表中的default_hostgroup)
MySQL [(none)]> select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules;

8.将刚才我们修改的数据加载至RUNTIME中(参考ProxySQL的多层配置结构)：

#load进runtime，是配置生效load mysql users to runtime;load mysql servers to runtime;load mysql query rules to runtime;load mysql variables to runtime;load admin variables to runtime;
#save到磁盘(/var/lib/proxysql/proxysql.db)中，永久保存配置save mysql users to disk;save mysql servers to disk;save mysql query rules to disk;save mysql variables to disk;save admin variables to disk;

第四步：测试读写分离

1.连接proxysql客户端:

#登录用户是刚才我们在mysql_user表中创建的用户，端口为6033mysql -uproxysql -p123456 -h127.0.0.1 -P6033

2.尝试修改数据库和查询：

#创建两个数据库和查个表。create database test;select user,host from mysql.user;

3.验证读写分离是否成功：

#proxysql有个类似审计的功能，可以查看各类SQL的执行情况。在proxysql管理端执行(注意管理端是6032端口，而且用户是admin)：select * from stats_mysql_query_digest;#从下面的hostgroup和digest_text值来看，所有的写操作都被路由至1组，读操作都被路由至2组，#其中1组为写组，2组为读组！#读写分离成功！！！MySQL [(none)]> select * from stats_mysql_query_digest;