第十四章 数据库读写分离
1.简介
数据库运行时,一些因素可能会导致服务运行不正常,用户访问数据受阻。对于互联网公司,尤其是购物网站而言,这种情况造成的损失是无法估量的。因此,对数据库进行“备份”也是必不可少的操作。当主要的数据库死机时,系统能够快速地切换到备用的数据库上。本章将详细介绍数据库集群中的主从复制原理和操作流程。

2.数据库代理
在单一的主从数据库架构中,前端应用通过数据库的IP地址或者指定端口向后端请求相应的数据。当面对较多的数据库服务器时,Web请求需要在这些服务器之间进行判断,以便找到哪一台服务上保存着自己想要的数据。面对数据请求高峰,这种判断不仅会带来大量的请求等待,而且有可能会造成整个系统的瘫痪。

3.基本原理
现实生活中,许多企业会通过在不同的地域招募代理商来提高品牌的宣传速度和市场占有率。随着互联网的不断发展,人们也会通过淘宝、京东这种“代理”平台来满足自己的消费需求。在互联网领域,系统管理员在面对数据库的多主集群时,也会使用代理服务器来实现数据的分发和资源的合理应用。代理服务器是网络信息的中转站,是信息“交流”的使者。常见的数据库代理架构如图所示。
图片 11.png
代理服务器提供统一的入口供用户访问, 当用户访问代理服务器时,代理服务器会将用户请求平均地分发到后端的服务器集群,并且其本身并不会做任何数据处理。这样一来,代理服务器不仅起到负载均衡的作用,还提供了独立的端口和IP地址。后端的服务器处理完请求后也会通过代理服务器将结果返回给用户。数据库代理网络拓扑如图所示。
图片 12.png
前端应用请求只需指定代理服务器的IP地址和端口即可访问后端数据文件,这种形式不仅提高了系统的数据处理能力,而且保证了后端数据库的安全性,使系统更加健壮。数据库代理(DB Proxy)又被称为数据库中间件,当面对大量的应用请求时,代理可以通过对数据进行分片以及自身的自动路由与聚合机制实现对不同请求的分发,以此来实现数据库的读写分离功能。

4.常见的数据库中间件
随着市场的发展和技术的更新,产生了许多不同的数据库中间件,国内企业中目前使用较多的数据库中间件有以下几种。
(1)MySQL Proxy:MySQL官方提供的数据库中间件。
(2)Atlas:奇虎360团队在MySQL Proxy的基础上进行的二次开发。
(3)DBProxy:美团点评在Atlas的基础上进行的二次开发。
(4)Amoeba:早期阿里巴巴使用的数据库中间件。
(5)Cobar:由阿里巴巴团队进行维护和开发。
(6)Mycat:由阿里巴巴团队进行维护和开发。
各个数据库中间件的应用场景和使用方式不同,其他数据库中间件的使用方式读者可以自行查阅。

MySQL读写分离
一、实验环境
主机	系统	IP地址	数据库版本
Master	Redhat8.2	192.168.101.120	Mariadb-10.3.17
Slave	Redhat8.2	192.168.101.130	Mariadb-10.3.17
Mysql-proxy	Redhat8.2	192.168.101.140	Mariadb-10.3.17
二、实验步骤
1.分别给三台设备进行命名,并关闭防火墙和selinux
Master配置:
[root@localhost ~]# hostnamectl set-hostname master
[root@localhost ~]# bash
[root@master ~]# systemctl stop firewalld.service 
[root@master ~]# systemctl  disable firewalld.service 
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@master ~]# setenforce 0
[root@master ~]#

Slave配置:
[root@localhost ~]# hostnamectl set-hostname slave
[root@localhost ~]# bash
[root@slave ~]# systemctl stop firewalld.service 
[root@slave ~]# systemctl  disable firewalld.service 
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@slave ~]# setenforce 0
[root@slave ~]#

Mysql-proxy配置:
[root@localhost ~]# hostnamectl set-hostname mysql-proxy
[root@localhost ~]# bash
[root@mysql-proxy ~]# systemctl stop firewalld.service 
[root@mysql-proxy ~]# systemctl  disable firewalld.service 
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@mysql-proxy ~]# setenforce 0
[root@mysql-proxy ~]#
2.给master和slave安装数据库软件
Master配置:
[root@master ~]# dnf -y install mariadb-server
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
上次元数据过期检查:0:01:07 前,执行于 2021年01月19日 星期二 21时41分22秒。
依赖关系解决。
=============================================================
 软件包        架构       版本              仓库           大小
=============================================================
安装:
 mariadb-server                 x86_64     3:10.3.17-1.module+el8.1.0+3974+90eded84       AppStream      16 M
。。。。。。。。。省略                                
  perl-DBD-MySQL-4.046-3.module+el8.1.0+2938+301254e2.x86_64                                                  
完毕!
[root@master ~]#

Slave配置:
[root@slave ~]# dnf -y install mariadb-server
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
上次元数据过期检查:0:05:13 前,执行于 2021年01月19日 星期二 21时37分48秒。
依赖关系解决。
=============================================================
 软件包         架构       版本              仓库           大小
=============================================================
安装:
 mariadb-server                 x86_64     3:10.3.17-1.module+el8.1.0+3974+90eded84       AppStream      16 M
。。。。。。。。。。省略                                      
  perl-DBD-MySQL-4.046-3.module+el8.1.0+2938+301254e2.x86_64                                                  
完毕!
[root@slave ~]#
3.给master和slave进行启动mariadb服务操作并设置为开机自启动同时进行数据库的安全设置
Master配置:
[root@master ~]# systemctl  start mariadb.service 
[root@master ~]# systemctl  enable mariadb.service 
Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.
[root@master ~]# mysql_secure_installation 
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y
 ... Success!
Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] y
 ... Success!
By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y
 ... Success!
Cleaning up...
All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
[root@master ~]#

Slave配置:
[root@slave ~]# systemctl start mariadb.service 
[root@slave ~]# systemctl enable mariadb.service 
Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.
[root@slave ~]# mysql_secure_installation 
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none): 
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y
 ... Success!
Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] y
 ... Success!
By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y
 ... Success!
Cleaning up...
All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
[root@slave ~]#
4.对master和slave安装和设置完成的数据库进行登录查看
Master配置:
[root@master ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 10.3.17-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
MariaDB [(none)]> exit
Bye
[root@master ~]#

Slave配置:
[root@slave ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 10.3.17-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
MariaDB [(none)]> exit
Bye
[root@slave ~]#
5.随后对master和slave两台设备进行主从同步设置
Master配置:
[root@master ~]# vim /etc/my.cnf.d/mariadb-server.cnf 
     1  #
     2  # These groups are read by MariaDB server.
     3  # Use it for options that only the server (but not clients) should see
     4  #
     5  # See the examples of server my.cnf files in /usr/share/mysql/
     6  #
     7  
     8  # this is read by the standalone daemon and embedded servers
     9  [server]
    10  
    11  # this is only for the mysqld standalone daemon
    12  # Settings user and group are ignored when systemd is used.
    13  # If you need to run mysqld under a different user or group,
    14  # customize your systemd unit file for mysqld/mariadb according to the
    15  # instructions in http://fedoraproject.org/wiki/Systemd
    16  [mysqld]
    17  datadir=/var/lib/mysql
    18  socket=/var/lib/mysql/mysql.sock
    19  log-error=/var/log/mariadb/mariadb.log
    20  pid-file=/run/mariadb/mariadb.pid
21  log_bin=mysql-bin                     //手动添加
22  binlog_ignore_db=mysql                     //手动添加
23  server_id=200                      //手动添加
    24  
    25  #
    …………………………省略
    55  # use this group for options that older servers don't understand
    56  [mariadb-10.3]
57  
:wq!
[root@master ~]# systemctl  restart mariadb.service
[root@master ~]#

Slave配置:
[root@salve ~]# vim /etc/my.cnf.d/mariadb-server.cnf 
     1  #
     2  # These groups are read by MariaDB server.
     3  # Use it for options that only the server (but not clients) should see
     4  #
     5  # See the examples of server my.cnf files in /usr/share/mysql/
     6  #
     7  
     8  # this is read by the standalone daemon and embedded servers
     9  [server]
    10  
    11  # this is only for the mysqld standalone daemon
    12  # Settings user and group are ignored when systemd is used.
    13  # If you need to run mysqld under a different user or group,
    14  # customize your systemd unit file for mysqld/mariadb according to the
    15  # instructions in http://fedoraproject.org/wiki/Systemd
    16  [mysqld]
    17  datadir=/var/lib/mysql
    18  socket=/var/lib/mysql/mysql.sock
    19  log-error=/var/log/mariadb/mariadb.log
    20  pid-file=/run/mariadb/mariadb.pid
    21  log_bin=mysql-bin                     //手动添加
    22  binlog_ignore_db=mysql                     //手动添加
    23  server_id=201                     //手动添加
    24  
    25  
    26  #
。。。。。。。。。。。。。。。。。。省略
    55  # If you use the same .cnf file for MariaDB of different versions,
    56  # use this group for options that older servers don't understand
    57  [mariadb-10.3]
58  
:wq!
[root@salve ~]# systemctl  restart mariadb.service 
[root@salve ~]#
6.在master上对salve用户进行授权操作,使其能够访问数据库
Master配置:
[root@master ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]>  GRANT REPLICATION SLAVE  ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
MariaDB [(none)]>
7.在salve上开启同步设置
[root@slave ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>  change master to master_host='192.168.101.120',master_user='slave',master_password='123456';
Query OK, 0 rows affected (0.003 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.101.120
                   Master_User: slave
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000001
           Read_Master_Log_Pos: 669
                Relay_Log_File: mariadb-relay-bin.000002
                 Relay_Log_Pos: 968
         Relay_Master_Log_File: mysql-bin.000001
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
               Replicate_Do_DB: 
           Replicate_Ignore_DB: 
            Replicate_Do_Table: 
        Replicate_Ignore_Table: 
       Replicate_Wild_Do_Table: 
   Replicate_Wild_Ignore_Table: 
                    Last_Errno: 0
                    Last_Error: 
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 669
               Relay_Log_Space: 1279
               Until_Condition: None
                Until_Log_File: 
                 Until_Log_Pos: 0
            Master_SSL_Allowed: No
            Master_SSL_CA_File: 
            Master_SSL_CA_Path: 
               Master_SSL_Cert: 
             Master_SSL_Cipher: 
                Master_SSL_Key: 
         Seconds_Behind_Master: 0
 Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error: 
                Last_SQL_Errno: 0
                Last_SQL_Error: 
   Replicate_Ignore_Server_Ids: 
              Master_Server_Id: 200
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 2
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
ERROR: No query specified

MariaDB [(none)]>
8.测试主从效果
Master配置:
MariaDB [(none)]> create database runtimeedu;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> use runtimeedu;
Database changed
MariaDB [runtimeedu]> create table company(id int not null primary key,name varchar(50),addr varchar(255));
Query OK, 0 rows affected (0.002 sec)
MariaDB [runtimeedu]> show tables;
+-------------------+
| Tables_in_runtimeedu |
+-------------------+
| company           |
+-------------------+
1 row in set (0.000 sec)
MariaDB [runtimeedu]> insert into company values(1,"alibaba","chain");
Query OK, 1 row affected (0.001 sec)
MariaDB [runtimeedu]> select * from company;
+----+---------+-------+
| id | name    | addr  |
+----+---------+-------+
|  1 | alibaba | chain |
+----+---------+-------+
1 row in set (0.000 sec)
MariaDB [runtimeedu]>

Salve配置:
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runtimeedu         |
| test               |
+--------------------+
5 rows in set (0.001 sec)
MariaDB [(none)]> use runtimeedu;
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
MariaDB [runtimeedu]> show tables;
+-------------------+
| Tables_in_runtimeedu |
+-------------------+
| company           |
+-------------------+
1 row in set (0.000 sec)
MariaDB [runtimeedu]> select * from company;
+----+---------+-------+
| id | name    | addr  |
+----+---------+-------+
|  1 | alibaba | chain |
+----+---------+-------+
1 row in set (0.000 sec)
MariaDB [runtimeedu]>
9.给Mysql-proxy主机进行安装lua解析器
[root@mysql-proxy ~]# dnf -y install lua*
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
上次元数据过期检查:0:32:09 前,执行于 2021年01月19日 星期二 21时34分21秒。
软件包 lua-5.3.4-11.el8.x86_64 已安装。
软件包 lua-expat-1.3.0-12.el8.1.x86_64 已安装。
软件包 lua-json-1.3.2-9.el8.noarch 已安装。
软件包 lua-libs-5.3.4-11.el8.x86_64 已安装。
软件包 lua-lpeg-1.0.1-6.el8.x86_64 已安装。
软件包 lua-socket-3.0-0.17.rc1.el8.x86_64 已安装。
依赖关系解决。
=============================================================
 软件包       架构       版本                   仓库           大小
=============================================================
安装:
 lua-guestfs                    x86_64     1:1.38.4-15.module+el8.2.0+5297+222a20af       AppStream     135 k
。。。。。。。。。。。省略                                                             
  syslinux-extlinux-nonlinux-6.04-4.el8.noarch                                                                
  syslinux-nonlinux-6.04-4.el8.noarch                                                                         
完毕!
[root@mysql-proxy ~]#
10.使用wget下载Mysql-proxy软件工具
[root@mysql-proxy ~]# wget https://cdn.mysql.com/archives/mysql-proxy/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
--2021-01-19 22:08:38--  https://cdn.mysql.com/archives/mysql-proxy/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
正在解析主机 cdn.mysql.com (cdn.mysql.com)... 23.36.53.20
正在连接 cdn.mysql.com (cdn.mysql.com)|23.36.53.20|:443... 已连接。
已发出 HTTP 请求,正在等待回应... 200 OK
长度:12067298 (12M) [application/x-tar-gz]
正在保存至: “mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz”

mysql-proxy-0.8.5-linux-el6 100%[=========================================>]  11.51M  2.19MB/s  用时 5.8s    

2021-01-19 22:08:45 (1.98 MB/s) - 已保存 “mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz” [12067298/12067298])
[root@mysql-proxy ~]#
[root@mysql-proxy ~]# ls
公共  视频  文档  音乐  anaconda-ks.cfg       mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
模板  图片  下载  桌面  initial-setup-ks.cfg
[root@mysql-proxy ~]#
11.对下载的工具进行解压
[root@mysql-proxy ~]# tar zxvf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz 
mysql-proxy-0.8.5-linux-el6-x86-64bit/
mysql-proxy-0.8.5-linux-el6-x86-64bit/bin/
mysql-proxy-0.8.5-linux-el6-x86-64bit/bin/mysql-myisam-dump
mysql-proxy-0.8.5-linux-el6-x86-64bit/bin/mysql-proxy
mysql-proxy-0.8.5-linux-el6-x86-64bit/bin/mysql-binlog-dump
mysql-proxy-0.8.5-linux-el6-x86-64bit/lib/
。。。。。。。。。。。。省略
mysql-proxy-0.8.5-linux-el6-x86-64bit/share/doc/mysql-proxy/tutorial-query-time.lua
mysql-proxy-0.8.5-linux-el6-x86-64bit/share/doc/mysql-proxy/tutorial-routing.lua
mysql-proxy-0.8.5-linux-el6-x86-64bit/share/doc/mysql-proxy/auditing.lua
mysql-proxy-0.8.5-linux-el6-x86-64bit/share/doc/mysql-proxy/tutorial-union.lua
mysql-proxy-0.8.5-linux-el6-x86-64bit/share/doc/mysql-proxy/tutorial-warnings.lua
mysql-proxy-0.8.5-linux-el6-x86-64bit/share/doc/mysql-proxy/xtab.lua
[root@mysql-proxy ~]#
12.将解压的文件进行移动到指定目录/usr/local/mysql-proxy
[root@mysql-proxy ~]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy
[root@mysql-proxy ~]# cd /usr/local/
[root@mysql-proxy local]# ls
bin  etc  games  include  lib  lib64  libexec  mysql-proxy  sbin  share  src
[root@mysql-proxy local]#
13.使用如下命令,编辑环境变量配置文件内容
[root@mysql-proxy ~]# export PATH=$PATH:$HOME/bin:/usr/local/mysql-proxy/bin
14.进入mysql-proxy目录,编辑配置文件,增加如下配置文件内容(手动添加)。(配置文件中不要有注释空格等其他内容,不然启动程序时会报错)
[root@mysql-proxy ~]# cd /usr/local/mysql-proxy/
[root@mysql-proxy mysql-proxy]# vim mysql-proxy.conf
     1  [mysql-proxy]
     2  plugins=proxy
     3  proxy-address=192.168.101.140:4040
     4  proxy-backend-addresses=192.168.101.120:3306
     5  proxy-read-only-backend-addresses=192.168.101.130:3306
     6  proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
     7  log-file=/usr/local/mysql-proxy/mysql-proxy.log
     8  log-level=debug
     9  keepalive=true
10  daemon=true
:wq!
[root@mysql-proxy mysql-proxy]#
15.启动mysql-proxy服务如果报错“(0660 or stricter required)”,如下,修改配置文件权限为660即可,随后启动服务
[root@mysql-proxy mysql-proxy]# mysql-proxy --defaults-file=/usr/local/mysql-proxy/mysql-proxy.conf
2021-01-19 22:16:49: (critical) mysql-proxy-cli.c:326: loading config from '/usr/local/mysql-proxy/mysql-proxy.conf' failed: permissions of /usr/local/mysql-proxy/mysql-proxy.conf aren't secure (0660 or stricter required)
2021-01-19 22:16:49: (message) Initiating shutdown, requested from mysql-proxy-cli.c:328
2021-01-19 22:16:49: (message) shutting down normally, exit code is: 1
[root@mysql-proxy mysql-proxy]# chmod   660   /usr/local/mysql-proxy/mysql-proxy.conf
[root@mysql-proxy mysql-proxy]# mysql-proxy   --defaults-file=/usr/local/mysql-proxy/mysql-proxy.conf
[root@mysql-proxy mysql-proxy]# netstat -antp |grep mysql-proxy
tcp        0      0 192.168.101.140:4040    0.0.0.0:*               LISTEN      154101/mysql-proxy
[root@mysql-proxy mysql-proxy]#

16.修改读写分离配置文件rw-splitting.lua,1表示只要有一个连接,就进行读写分离。重启mysql-proxy服务。
[root@mysql-proxy mysql-proxy]# find /  -name  rw-splitting.lua
/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
[root@mysql-proxy mysql-proxy]# vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
     1  --[[ $%BEGINLICENSE%$
     2   Copyright (c) 2007, 2012, Oracle and/or its affiliates. All rights reserved.
     3  
     4   This program is free software; you can redistribute it and/or
     5   modify it under the terms of the GNU General Public License as
     6   published by the Free Software Foundation; version 2 of the
     7   License.
     8  
     9   This program is distributed in the hope that it will be useful,
    10   but WITHOUT ANY WARRANTY; without even the implied warranty of
    11   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
    12   GNU General Public License for more details.
    13  
    14   You should have received a copy of the GNU General Public License
    15   along with this program; if not, write to the Free Software
    16   Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
    17   02110-1301  USA
    18  
    19   $%ENDLICENSE%$ --]]
    20  
    21  ---
    22  -- a flexible statement based load balancer with connection pooling
    23  --
    24  -- * build a connection pool of min_idle_connections for each backend and maintain
    25  --   its size
    26  -- * 
    27  -- 
    28  -- 
    29  
    30  local commands    = require("proxy.commands")
    31  local tokenizer   = require("proxy.tokenizer")
    32  local lb          = require("proxy.balance")
    33  local auto_config = require("proxy.auto-config")
    34  
    35  --- config
    36  --
    37  -- connection pool
    38  if not proxy.global.config.rwsplit then
    39          proxy.global.config.rwsplit = {
    40                  min_idle_connections = 1,
    41                  max_idle_connections = 8,
    42  
    43                  is_debug = false
    44          }
    45  end
。。。。。。。。。。省略
   373          -- make sure we are disconnection from the connection
   374          -- to move the connection into the pool
   375          proxy.connection.backend_ndx = 0
   376  end
   377  
:wq!
[root@mysql-proxy mysql-proxy]#
[root@mysql-proxy mysql-proxy]# pkill  mysql-proxy
[root@mysql-proxy mysql-proxy]# mysql-proxy   --defaults-file=/usr/local/mysql-proxy/mysql-proxy.conf
[root@mysql-proxy mysql-proxy]#
17.主从服务器master、slave,登录数据库授权账户mysqlproxy,密码123456,访问所有数据库所有权限
Master配置:
[root@master ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.3.17-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> grant all on *.*  to  'mysqlproxy'@'%'identified  by '123456';
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> exit
Bye
[root@master ~]#

Slave配置:
[root@slave ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 10.3.17-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> grant all on *.*  to  'mysqlproxy'@'%'identified  by '123456'; 
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> flush  privileges;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> exit
Bye
[root@slave ~]#
18.在mysql-proxy主机上安装mariadb-server服务
[root@mysql-proxy mysql-proxy]# dnf -y install mariadb-server
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
上次元数据过期检查:0:52:56 前,执行于 2021年01月19日 星期二 21时34分21秒。
依赖关系解决。
=============================================================
 软件包          架构       版本            仓库           大小
=============================================================
安装:
 mariadb-server                 x86_64     3:10.3.17-1.module+el8.1.0+3974+90eded84       AppStream      16 M
。。。。。。。。。。。省略                                      
  perl-DBD-MySQL-4.046-3.module+el8.1.0+2938+301254e2.x86_64                                                  
完毕!
[root@mysql-proxy mysql-proxy]# systemctl restart mariadb.service
[root@mysql-proxy mysql-proxy]#
19.进行读写分离测试
写测试:
Master配置:
[root@master ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.3.17-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runtimeedu         |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [(none)]> use runtimeedu;
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
MariaDB [runtimeedu]> show tables;
+----------------------+
| Tables_in_runtimeedu |
+----------------------+
| company              |
+----------------------+
1 row in set (0.000 sec)
MariaDB [runtimeedu]> exit
Bye
[root@master ~]#

Slave配置:
[root@slave ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 10.3.17-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runtimeedu         |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [(none)]> use runtimeedu;
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
MariaDB [runtimeedu]> show tables;
+----------------------+
| Tables_in_runtimeedu |
+----------------------+
| company              |
+----------------------+
1 row in set (0.000 sec)
MariaDB [runtimeedu]>

Mysqlproxy配置:
[root@mysql-proxy mysql-proxy]# mysql -umysqlproxy -p -P 4040 -h 192.168.101.140
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 10.3.17-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runtimeedu         |
+--------------------+
4 rows in set (0.001 sec)
MariaDB [(none)]> use runtimeedu;
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
MariaDB [runtimeedu]> show tables;
+----------------------+
| Tables_in_runtimeedu |
+----------------------+
| company              |
+----------------------+
1 row in set (0.001 sec)
MariaDB [runtimeedu]> create table company1(age int,name char(40));
Query OK, 0 rows affected (0.004 sec)
MariaDB [runtimeedu]> show tables;
+----------------------+
| Tables_in_runtimeedu |
+----------------------+
| company              |
| company1             |
+----------------------+
2 rows in set (0.001 sec)
MariaDB [runtimeedu]>

Slave配置:
MariaDB [runtimeedu]> stop slave;
Query OK, 0 rows affected (0.002 sec)
MariaDB [runtimeedu]>

Mysqlproxy配置:
MariaDB [runtimeedu]> insert into company1 values (120,'master');
Query OK, 1 row affected (0.013 sec)
MariaDB [runtimeedu]> 

Master配置:
MariaDB [runtimeedu]> select * from company1;
+------+--------+
| age  | name   |
+------+--------+
|  120 | master |
+------+--------+
1 row in set (0.000 sec)
MariaDB [runtimeedu]>

读取测试:
Mysqlproxy配置:
MariaDB [runtimeedu]> insert into company1 values (140,'proxy');
Query OK, 1 row affected (0.001 sec)
MariaDB [runtimeedu]> insert into company1 values (130,'slave');
Query OK, 1 row affected (0.001 sec)
MariaDB [runtimeedu]> insert into company1 values (120,'master');
Query OK, 1 row affected (0.001 sec)
MariaDB [runtimeedu]> 

Master配置:
MariaDB [runtimeedu]> select * from company1;
+------+--------+
| age  | name   |
+------+--------+
|  120 | master |
|  140 | proxy  |
|  130 | slave  |
|  120 | master |
+------+--------+
4 rows in set (0.000 sec)
MariaDB [runtimeedu]>

Slave配置:
MariaDB [runtimeedu]> start slave;
Query OK, 0 rows affected (0.001 sec)
MariaDB [runtimeedu]> select * from company1;
+------+--------+
| age  | name   |
+------+--------+
|  120 | master |
|  140 | proxy  |
|  130 | slave  |
|  120 | master |
+------+--------+
4 rows in set (0.000 sec)
MariaDB [runtimeedu]>

Mysqlproxy配置:
MariaDB [runtimeedu]> select * from company1;
+------+--------+
| age  | name   |
+------+--------+
|  120 | master |
|  140 | proxy  |
|  130 | slave  |
|  120 | master |
+------+--------+
4 rows in set (0.001 sec)
MariaDB [runtimeedu]>

Slave配置:
MariaDB [runtimeedu]> delete from company1 where age='130';
Query OK, 0 rows affected (0.000 sec)
MariaDB [runtimeedu]>

Master配置:
MariaDB [runtimeedu]> select * from company1;
+------+--------+
| age  | name   |
+------+--------+
|  120 | master |
|  140 | proxy  |
|  130 | slave  |
|  120 | master |
+------+--------+
4 rows in set (0.000 sec)
MariaDB [runtimeedu]>

Slave配置:
MariaDB [runtimeedu]> select * from company1;
+------+--------+
| age  | name   |
+------+--------+
|  120 | master |
|  140 | proxy  |
|  120 | master |
+------+--------+
3 rows in set (0.000 sec)
MariaDB [runtimeedu]>

Mysqlproxy配置:
MariaDB [runtimeedu]> select * from company1;
+------+--------+
| age  | name   |
+------+--------+
|  120 | master |
|  140 | proxy  |
|  130 | slave  |
|  120 | master |
+------+--------+
4 rows in set (0.000 sec)
MariaDB [runtimeedu]>
上一篇 下一篇