MySQL/MariaDB读写分离配置

Z时代
2024-01-10
分类：综合

database

实现数据库读写分离技术是有很多方法的，在这里我就用一个比较简单的mysql-proxy这个中间件来实现数据库的读写分离；

使用mysql-proxy实现mysql的读写分离，mysql-proxy实际上是作为后端mysql主从服务器的代理，它直接接受客户端的请求，对SQL语句进行分析，判断出是读操作还是写操作，然后分发至对应的mysql服务器上。

数据库读写分离比较实用的还有Amoeba等相关程序。

基本环境

此环境需要三台主机（可以是虚拟主机）
Linux 操作系统版本： CentOS8.0
软件版本：
数据库： mariadb
lua： lua.X86_64
mysql-proxy： mysql-proxy-0.8.5

这里因为需要用三台主机，电脑配置有点上愁，所以我这里就使用容器（docker）代替三台虚拟主机了

root@uduntu:~# docker ps -a

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES

8aea2ebdf1a5 centos "/sbin/init" 12 minutes ago Up 11 minutes mysql-proxy

9a24bbeec012 centos "/sbin/init" 13 minutes ago Up 12 minutes DB2

5495e5cf36c3 centos "/sbin/init" 37 minutes ago Up 37 minutes DB1

root@uduntu:~#

这三个容器，IP地址分别是：

①DB1： 172.18.0.2

②DB2： 172.18.0.3

③mysql-proxy： 172.18.0.4

好了基础环境已经介绍完成了，接下来开始真正的部署操作吧！

数据库部署

MariaDB描述：

MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可 MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。

MariaDB基于事务的Maria存储引擎，替换了MySQL的MyISAM存储引擎，它使用了Percona的 XtraDB，InnoDB的变体，分支的开发者希望提供访问即将到来的MySQL 5.4 InnoDB性能。

这个版本还包括了 PrimeBase XT (PBXT) 和 FederatedX存储引擎。

安装

[root@DB1 /]#    apt -y install mariadb-server
    Setting up mariadb-client-10.3 (1:10.3.17-1) ...
    Setting up libdbd-mysql-perl:amd64 (4.050-2build1) ...
    Setting up libhtml-parser-perl (3.72-3build2) ...
    Setting up mariadb-server-10.3 (1:10.3.17-1) ...
    Created symlink /etc/systemd/system/mysql.service → /lib/systemd/system/mariadb.service.
    Created symlink /etc/systemd/system/mysqld.service → /lib/systemd/system/mariadb.service.
    Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /lib/systemd/system/mariadb.service.
    Setting up libhttp-message-perl (6.18-1) ...
    Setting up libcgi-pm-perl (4.44-1) ...
    Setting up libhtml-template-perl (2.97-1) ...
    Setting up mariadb-server (1:10.3.17-1) ...
    Setting up libcgi-fast-perl (1:2.15-1) ...
    Processing triggers for systemd (242-7ubuntu3) ...
    Processing triggers for man-db (2.8.7-3) ...
    Processing triggers for libc-bin (2.30-0ubuntu2) ...
jia@uduntu:~$            \出现上面代码表示安装成功

启动

[root@DB1 /]#  systemctl start mariadb
    ==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ===
    Authentication is required to start "mariadb.service".
    Authenticating as: jia
    Password:         \此处输入密码
    ==== AUTHENTICATION COMPLETE ===
 [root@DB1 /]#

查看是否启动成功：

[root@DB1 /]# 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@DB1 /]#

初始化完成后，可直接使用自带的mysql客户端进行连接

[root@DB1 /]#    mysql -u root -p
    Enter password: 
    Welcome to the MariaDB monitor.  Commands end with ; or g.
    Your MariaDB connection id is 56
    Server version: 10.3.17-MariaDB-1 Ubuntu 19.10
    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           |
    +--------------------+
    | ifnormation_schema |
    | mysql              |
    | performance_schema |
    +--------------------+
rows in set (0.000 sec)
     MariaDB [(none)]>

DB2数据库同DB1数据库一样，同样方法安装mariadb数据库就可以哦！我这里就省略不写了

MySQL-Proxy部署

MySQL-Proxy软件需要部署到单独的mysql-proxy服务器上的。

安装依赖软件

MySQL-Proxy中含有一个读写分离的lua文件，这也是我们使用mysql-proxy实现读写分离必用的文件，它需要lua解析器进行解析。

因此我们需要安装一个lua解析器。

[root@mysql-proxy /]# yum -y install lua*

mdadm-4.1-4.el8.x86_64

mozjs52-52.9.0-1.el8.x86_64

mtools-4.0.18-14.el8.x86_64

nfs-utils-1:2.3.3-14.el8_0.2.x86_64

numactl-libs-2.0.12-2.el8.x86_64

numad-0.5-26.20150602git.el8.x86_64

parted-3.2-36.el8.x86_64

pciutils-3.5.6-4.el8.x86_64

pciutils-libs-3.5.6-4.el8.x86_64

policycoreutils-2.8-16.1.el8.x86_64

polkit-0.115-6.el8.x86_64

polkit-libs-0.115-6.el8.x86_64

polkit-pkla-compat-0.1-12.el8.x86_64

psmisc-23.1-3.el8.x86_64

python3-dateutil-1:2.6.1-6.el8.noarch

python3-dnf-plugins-core-4.0.2.2-3.el8.noarch

quota-1:4.04-10.el8.x86_64

quota-nls-1:4.04-10.el8.noarch

rdma-core-22-2.el8.x86_64

rpcbind-1.2.5-3.el8.x86_64

syslinux-6.04-1.el8.x86_64

syslinux-extlinux-6.04-1.el8.x86_64

syslinux-extlinux-nonlinux-6.04-1.el8.noarch

syslinux-nonlinux-6.04-1.el8.noarch

systemd-container-239-13.el8.x86_64

userspace-rcu-0.10.1-2.el8.x86_64

xml-common-0.6.3-50.el8.noarch

Complete!

[root@mysql-proxy /]# \出现以上输出表示安装成功

安装Mysql-Proxy

在安装完成lua之后，我们就可以安装今天的主角软件了

[root@mysql-proxy opt]# ls
    mysql-proxy-0.8.5-linux-rhel5-x86-64bit.tar.gz
[root@mysql-proxy opt]#         //上面软件就是我们的主角软件

安装软件包

#安装软件包

#将安装包解压

[root@mysql-proxy opt]# tar zxf mysql-proxy-0.8.5-linux-rhel5-x86-64bit.tar.gz

#解压后的目录

[root@mysql-proxy mysql-proxy-0.8.5-linux-rhel5-x86-64bit]# ls

bin include lib libexec licenses share

# 更改名称，方便之后对其进行操作

[root@mysql-proxy opt]# mv mysql-proxy-0.8.5-linux-rhel5-x86-64bit mysql-proxy

#将mysql-proxy中lua配置文件cp到软件根目录

[root@mysql-proxy mysql-proxy]# cp share/doc/mysql-proxy/rw-splitting.lua .

#再次查看目录结构

[root@mysql-proxy mysql-proxy]# ls

bin include lib libexec licenses rw-splitting.lua share

#修改lua配置文件，只需要修改下面代码就可以

if not proxy.global.config.rwsplit then

proxy.global.config.rwsplit = {

min_idle_connections = 4, //此处值改为1

max_idle_connections = 8, //此处值改为1

is_debug = false

}

#修改完成后保存退出，启动mysql-proxy

[root@mysql-proxy mysql-proxy]# cd bin/ //切换目录

[root@mysql-proxy bin]# ./mysql-proxy --proxy-read-only-backend-addresses=172.18.0.3:3306 --proxy-backend-addresses=172.18.0.2:3306 --proxy-lua-script=/opt/mysql-proxy/rw-splitting.lua &

[1] 5680

[root@mysql-proxy bin]# 2019-11-20 09:26:54: (critical) plugin proxy 0.8.5 started

[root@mysql-proxy bin]#

#启动成功

注意：

–proxy-read-only-backend-addresses //指定执行读取操作的数据库IP地址以及端口
–proxy-backend-addresses //指定执行写入操作的数据库IP地址以及端口
–proxy-lua-script //指定mysql-proxy软件中lua配置文件路径

#查看是否启动成功

[root@mysql-proxy bin]# ps aux | grep mysql-proxy

root 5680 0.0 0.5 38428 3696 pts/1 S 09:26 0:00 /opt/mysql-proxy/libexec/mysql-proxy --proxy-read-only-backend-addresses=172.18.0.3:3306 --proxy-backend-addresses=172.18.0.2:3306 --proxy-lua-script=/opt/mysql-proxy/rw-splitting.lua

#出现第一行表示启动成功

接下来需要为mysql-proxy程序创建可以读取和写入数据库的用户

[root@DB2 /]# 
[root@DB2 /]# mysql -u root -p            //登陆数据库
Enter password:                         //输入数据库密码
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 16
Server version: 10.3.11-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)]> grant all on *.* to "proxy"@"%" identified by "redhat";        //创建用户并设置权限
Query OK, 0 rows affected (0.105 sec)
MariaDB [(none)]> flush privileges;                //更新权限列表            
Query OK, 0 rows affected (0.090 sec)
MariaDB [(none)]>

注意：用户需要在两个数据库上创建，名称密码要一致；

继续看：测试

好了到这里，数据库的部署，以及使用代理对数据库的读写进行限制已经做好了其实也并没有复杂，接下来让我们测试一下。

#找一个客户端，宿主机也可以，安装一个数据库客户端或则数据库的连接工具进行连接，我这里就使用mysql客户端进行测试了

root@uduntu:~# mysql -uproxy -predhat -h 172.18.0.4 -P4040

Welcome to the MariaDB monitor. Commands end with ; or g.

Your MariaDB connection id is 21

Server version: 10.3.11-MariaDB MariaDB Server

Type "help;" or "h" for help. Type "c" to clear the current input statement.

MariaDB [(none)]>

#查看到登陆数据库表示连接成功

注意:

①mysql -uproxy -predhat -h 172.18.0.4 -P4040

②其中-u指的是数据库的用户名；

③-p指的是密码；

④-h指定mysql-proxy的服务器地址；

⑤-P表示mysql-proxy的端口

接下来我们试着创建一个数据库看下效果：

DB1数据库默认所拥有的数据库

[root@DB1 /]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 22
Server version: 10.3.11-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 |
+--------------------+
rows in set (0.197 sec)
MariaDB [(none)]>

DB2数据库默认所拥有的数据库：

[root@DB2 /]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 17
Server version: 10.3.11-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 |
+--------------------+
rows in set (0.275 sec)
MariaDB [(none)]>

使用代理mysql-proxy创建新数据库：

root@uduntu:~# 
root@uduntu:~# mysql -uproxy -predhat -h 172.18.0.4 -P4040
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 21
Server version: 10.3.11-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)]> create database test;            //创建test数据库
Query OK, 1 row affected (0.056 sec)
MariaDB [(none)]>

查看DB1数据库：发现多了test数据库

MariaDB [(none)]> show databases ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
rows in set (0.001 sec)
MariaDB [(none)]>

查看DB2数据库：发现没有新的数据库；

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
rows in set (0.000 sec)
MariaDB [(none)]>

OK搞定，说明数据库只是往DB1里面写数据库并不往DB2中写入数据

优化MySQL-Proxy

你会发现在启动mysql-proxy时需要写那么多东西，需要指定两个参数，每次启动都要写太麻烦了，为了解决这个问题，在这里写了一个启动脚本，希望可以帮到大家。

#!/bin/sh
     # chkconfig: - 98 44
     # processname: mysql-proxy
    # description: mysql-proxy is a proxy daemon to mysql
 . /etc/rc.d/init.d/functions
  PROXY_PATH=/opt/mysql-proxy/bin
  prog="mysql-proxy"
 . /etc/sysconfig/network
 [ ${NETWORKING} = "no" ] && exit 0
  PROXY_OPTIONS="--proxy-read-only-backend-addresses=172.18.0.3:3306 --proxy-backend-addresses=172.18.0.2:3306 --proxy-lua-script=/opt/mysql-proxy/rw-splitting.lua"
  PROXY_PID=/opt/mysql-proxy/run/mysql-proxy.pid
 if [ -f /etc/sysconfig/mysql-proxy ]; then
         . /etc/sysconfig/mysql-proxy
 fi
 PATH=$PATH:/usr/bin:/opt/bin:$PROXY_PATH
 RETVAL=0
 case "$1" in
   start)
         echo -n $"Starting $prog: "
         $NICELEVEL $PROXY_PATH/mysql-proxy $PROXY_OPTIONS --daemon --pid-file=$PROXY_PID --user=root --log-level=debug --log-file=/opt/mysql-proxy/log/mysql-proxy.log
         RETVAL=$?
         echo
         if [ $RETVAL = 0 ]; then
                 touch /var/lock/subsys/mysql-proxy]
                 echo "ok"
         fi
        ;;
   stop)
          echo -n $"Stopping $prog: "
         killproc $prog
         RETVAL=$?
         echo
         if [ $RETVAL = 0 ]; then
                 rm -f /var/lock/subsys/mysql-proxy
                 rm -f $PROXY_PID
         fi
        ;;
   restart)
         $0 stop
         sleep 3
         $0 start
        ;;
   condrestart)
        [ -e /var/lock/subsys/mysql-proxy ] && $0 restart
       ;;
   status)
         status mysql-proxy
         RETVAL=$?
        ;;
   *)
         echo "Usage: $0 {start|stop|restart|status|condrestart}"
         RETVAL=1
        ;;
 esac
 exit $RETVAL

将mysql-proxy服务管理脚本放在了/opt/mysql-proxy/init.d/文件夹里

给执行权限，建立相应目录

　　[root@mysql-proxy ~]chmod +x /opt/mysql-proxy/init.d/mysql-proxy
　　[root@mysql-proxy ~]mkdir /opt/mysql-proxy/run
　　[root@mysql-proxy ~]mkdir /opt/mysql-proxy/log
　　[root@mysql-proxy ~]cd /opt/mysql-proxy/init.d/
启动mysql-proxy
　　[root@mysql-proxy init.d ~]./mysql-proxy start
停止mysql-proxy
　　[root@mysql-proxy init.d ~]./mysql-proxy stop
重启mysql-proxy
　　[root@mysql-proxy init.d~]./mysql-proxy restart

这样启动停止mysql-proxy就很简单了，在这里提醒大家一下，单独的数据库读写分离是没有任何作用的，只有结合数据主从复制来进行才有意义。

以上是 MySQL/MariaDB读写分离配置的全部内容，来源链接： utcz.com/z/533428.html