MySQL实现读写分离

最新推荐文章于 2025-07-07 00:22:25 发布

原创最新推荐文章于 2025-07-07 00:22:25 发布 · 156 阅读

0 ·

CC 4.0 BY-SA版权

读写分离

读写分离其实就是将数据库分为了主从库，一个主库用于写数据，多个从库完成读数据的操作，主从库之间通过某种机制进行数据的同步，是一种常见的数据库架构。使用读写分离可以大大降低数据库服务器的负载。

环境搭建

rhel7.3
server1：172.25.61.1（读写）
server2：172.25.61.2（只读）
server3：172.25.61.3（调度）
server1和server2上已经安装了mysql

配置server1和server2主从复制

server1修改配置文件,初始化

 vim /etc/my.cnf
 添加以下内容
 log-bin=mysql-bin
server_id=1
gtid_mode=ON
enforce-gtid-consistency=ON
log_slave_updates=ON
log_bin=binlog

cd /var/lib/mysql			##进入到此目录中，如果有文件就删除，初始化
systemctl start mysqld
 cat /var/log/mysqld.log | grep password		##获取密码
 mysql_secure_installation								##安全初始化
 [root@server1 ~]# mysql_secure_installation 

Securing the MySQL server deployment.

Enter password for user root: 				##输入获取的密码

The existing password for the user account root has expired. Please set a new password.

New password: 									##设置密码

Re-enter new password:

登陆server1授权

 mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> grant replication slave on *.* to repl@'172.25.61.%' identified by 'Redhat+001';
Query OK, 0 rows affected, 1 warning (0.29 sec)

mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000002 |     1003 |              |                  | ab212727-7725-11e9-aee1-525400bacb9b:1-4 |
+---------------+----------+--------------+------------------+------------------------------------------+

server2设置与servar1同步

server2数据库初始化同server1一样

[root@server2 ~]# mysql -p
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
±-------------------+
4 rows in set (0.01 sec)

mysql> change master to master_host=‘172.25.61.1’, master_user=‘repl’, master_password=‘Redhat+001’,master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.96 sec)
mysql> start slave;
Query OK, 0 rows affected (0.48 sec)

mysql> show slave status\G;

     Slave_IO_Running: Yes
    Slave_SQL_Running: Yes

都为yes同步连接成功

server2安装调度器，编写调度规则

 [root@server3 ~]# ls
 mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
 [root@server3 ~]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/
 [root@server3 ~]# cd /usr/local/
 [root@server3 local]# ls
 bin  games    lib    libexec                                sbin   src
 etc  include  lib64  mysql-proxy-0.8.5-linux-el6-x86-64bit  share
 [root@server3 local]# ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy
 [root@server3 local]# cd mysql-proxy
 [root@server3 mysql-proxy]# mkdir conf
 [root@server3 mysql-proxy]# cd conf/
 [root@server3 ~]# vim /usr/local/mysql-proxy/conf/mysql-proxy.conf 
 
   1 [mysql-proxy]
   2 proxy-address=0.0.0.0:3306
   3 proxy-read-only-backend-addresses=172.25.61.2:3306  ##指定后端从slave读取数据
   4 proxy-backend-addresses=172.25.61.1:3306	  ##指定后端从master写入数据
   5 proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
   6 pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid
   7 log-file=/usr/local/mysql-proxy/log/mysql-proxy.log
   8 plugins=proxy
   9 log-level=debug
  10 keepalive=true
  11 daemon=true
   vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

##表示大于两个就启动读写分离
在这里插入图片描述

启动mysql-proxy

  [root@server3 ~]# mkdir /usr/local/mysql-proxy/log
 [root@server3 ~]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf

查看日志连接情况

[root@server3 conf]#  chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf

[root@server3 ~]# cat /usr/local/mysql-proxy/log/mysql-proxy.log 
2019-07-13 00:17:11: (message) chassis-unix-daemon.c:136: [angel] we try to keep PID=10413 alive
2019-07-13 00:17:11: (debug) chassis-unix-daemon.c:157: waiting for 10413
2019-07-13 00:17:11: (debug) chassis-unix-daemon.c:121: we are the child: 10413
2019-07-13 00:17:11: (critical) plugin proxy 0.8.5 started
2019-07-13 00:17:11: (debug) max open file-descriptors = 1024
2019-07-13 00:17:11: (message) proxy listening on port 0.0.0.0:3306
2019-07-13 00:17:11: (message) added read/write backend: 172.25.61.1:3306
2019-07-13 00:17:11: (message) added read-only backend: 172.25.61.2:330

server1（master）写入数据，配置用户权限

 mysql> show databases;
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | mysql              |
 | performance_schema |
 | sys                |
 +--------------------+
 4 rows in set (0.00 sec)
 
 mysql> grant insert,update,select on *.* to xin@'%' identified by 'Redhat+001';
 Query OK, 0 rows affected, 1 warning (0.29 sec)
 
 mysql> flush privileges;
 Query OK, 0 rows affected (0.40 sec)
 
 mysql> create database redhat;
 Query OK, 1 row affected (0.14 sec)
 
 mysql> show databases;
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | mysql              |
 | performance_schema |
 | redhat             |
 | sys                |
 +--------------------+
 5 rows in set (0.00 sec)
 
 mysql> use  redhat;
 Database changed
 
 mysql> create table usertb (
     ->  username varchar(10) not null,
     ->  password varchar(15) not null);
 Query OK, 0 rows affected (0.78 sec)
 
 mysql> desc usertb;
 +----------+-------------+------+-----+---------+-------+
 | Field    | Type        | Null | Key | Default | Extra |
 +----------+-------------+------+-----+---------+-------+
 | username | varchar(10) | NO   |     | NULL    |       |
 | password | varchar(15) | NO   |     | NULL    |       |
 +----------+-------------+------+-----+---------+-------+
 2 rows in set (0.00 sec)
 
 mysql> select * from usertb;
 +----------+----------+
 | username | password |
 +----------+----------+
 | user1    | 123      |
 +----------+----------+
 1 row in set (0.00 sec)

远程登陆mysql-proxy服务端

 [root@foundation61 yum.repos.d]# mysql -h 172.25.61.3 -uxin -pRedhat+001
 Welcome to the MariaDB monitor.  Commands end with ; or \g.
 Your MySQL connection id is 17
 Server version: 5.7.24-log MySQL Community Server (GPL)
 
 Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
 
 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 MySQL [(none)]> show databases;
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | mysql              |
 | performance_schema |
 | redhat             |
 | sys                |
 +--------------------+
 5 rows in set (0.00 sec)
 
 MySQL [(none)]> select * from usertab;
 ERROR 1046 (3D000): No database selected
 MySQL [(none)]> select * from usertb;
 ERROR 1046 (3D000): No database selected
 MySQL [(none)]> use redhat;
 Reading table information for completion of table and column names
 You can turn off this feature to get a quicker startup with -A
 
 Database changed
 MySQL [redhat]> select * from usertb;
 +----------+----------+
 | username | password |
 +----------+----------+
 | user1    | 123      |
 +----------+----------+
 1 row in set (0.00 sec)

在这里插入图片描述

测试读写分离

 [root@foundation61 ~]# mysql -h 172.25.61.3 -uxin -pRedhat+001
 Welcome to the MariaDB monitor. Commands end with ; or \g.
 Your MySQL connection id is 8
 Server version: 5.7.24-log MySQL Community Server (GPL)
 
 Copyright © 2000, 2016, Oracle, MariaDB Corporation Ab and others.
 
 Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
 
 MySQL [(none)]>

在这里插入图片描述
关闭server2主从复制，添加数据

添加数据

数据添加成功却查不到，因为主从复制关闭后，数据写在了server1上，读的时候读server2的数据，server2没有同步server1的数据
查看server1和server2的数据
数据添加到了server1上
在这里插入图片描述
server2上没有数据

实现读写分离