MySQL实现读写分离

读写分离

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

环境搭建

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

1.配置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同步连接成功

2.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 

##表示大于两个就启动读写分离

3.启动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-05-16 00:17:11: (message) chassis-unix-daemon.c:136: [angel] we try to keep PID=10413 alive
2019-05-16 00:17:11: (debug) chassis-unix-daemon.c:157: waiting for 10413
2019-05-16 00:17:11: (debug) chassis-unix-daemon.c:121: we are the child: 10413
2019-05-16 00:17:11: (critical) plugin proxy 0.8.5 started
2019-05-16 00:17:11: (debug) max open file-descriptors = 1024
2019-05-16 00:17:11: (message) proxy listening on port 0.0.0.0:3306
2019-05-16 00:17:11: (message) added read/write backend: 172.25.61.1:3306
2019-05-16 00:17:11: (message) added read-only backend: 172.25.61.2:330

4.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)

5.远程登陆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)

6.测试读写分离

1.开启三个客户端远程连接mysql-proxy

[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)]>

2.关闭server2主从复制，添加数据

3.添加数据

数据添加成功却查不到，因为主从复制关闭后，数据写在了server1上，读的时候读server2的数据，server2没有同步server1的数据

查看server1和server2的数据

数据添加到了server1上

server2上没有数据

实现读写分离