数据读写分离介绍
把客户端查询数据的select 访问和 存储数据insert 访问 分别给不同的数据库服务器处理。
目的减轻单台数据库服务器的工作压力，但是得保证负责处理select访问请求数据库服务器的数据要和处理insert访问请求的数据库服务器的数据一致。 所以要想实现数据的读写分离 存储数据的数据库服务器之间必须是主从结构。
实现数据读写分离的方式？
在客户端实现： 开发网站的程序员在写访问数据库服务器的脚本时， 在脚本里定义连接的
数据库服务器的ip地址
执行查询访问命令必须连接 slave服务器的ip地址
执行insert访问的命令必须连接 master服务器的ip地址
搭建读写分离服务器实现:
而是读写分离服务器，由读写分离服务器根据客户端的访问类型，把请求给后端数据库服务器处理：
把查询请求select 命令 给slave服务器处理
把存储请求insert 命令 给master服务器处理mysql中间件，
是统称 指的是架设在数据库服务器和客户端之间的软件，中间件功能各有不同
提供数据读写分离功能的中间件软件有： mysql-proxy maxscale mycat
案例
把客户端的select请求给 主从结构中的slave服务器 192.168.4.52
把客户端的insert请求给 主从结构中的master 服务器 192.168.4.51
第1 步 ： 把host51 配置为master数据库服务器
第2 步 ： 把host52 配置为slave数据库服务器
步骤参考 RDBMS2_day01 的 一主一从配置 例子
第3步：检查从服务器的状态
[root@host52 ~]# mysql -uroot -p123qqq...A -e 'show slave status \G' | grep -i yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@host52 ~]# mysql -uroot -p123qqq...A -e 'show slave status \G' | grep -i master_host配置读写分离服务器
准备1 台虚拟机：
配置ip 192.168.4.57 配置 yum 源 关闭 firewalld 、 selinux
注意不需要安装mysql服务软件。如果安装了的话 服务不启动就可以。
拷贝maxscale 软件到 57 主机里
具体配置如下：
1 安装软件
[root@host57 ~]# ls
Desktop maxscale-2.1.2-1.rhel.7.x86_64.rpm
[root@host57 ~]#
[root@host57 ~]# yum -y install maxscale-2.1.2-1.rhel.7.x86_64.rpm2 修改主配置文件
[root@host57 ~]# cp /etc/maxscale.cnf /root/ 备份主配置文件
[root@host57 ~]# vim /etc/maxscale.cnf
#服务启动后线程的数量
[maxscale]
threads=auto
[server1] 指定第1台数据库服务器的ip地址
type=server
address=192.168.4.51
port=3306
protocol=MySQLBackend
[server2] 指定第2台数据库服务器的ip地址
type=server
address=192.168.4.52
port=3306
protocol=MySQLBackend
[MySQL Monitor] 定义监视的数据库服务器
type=monitor
module=mysqlmon
servers=server1,server2 监视server1和server2
user=mysqla 监控用户
passwd=123qqq...A 连接密码
monitor_interval=10000
#禁止只读服务
#[Read-Only Service]
#type=service
#router=readconnroute
#servers=server1
#user=myuser
#passwd=mypwd
#router_options=slave
[Read-Write Service] 启用读写分离服务
type=service
router=readwritesplit
servers=server1,server2 读写分离服务在server1和server2服务器之间进行
user=mysqlb 路由用户
passwd=123qqq...A 连接密码
max_slave_connections=100%
[MaxAdmin Service] 管理服务（通过访问管理服务可以查看监控信息）
type=service
router=cli
因为只读服务没有启用所有也不需要定义服务使用的端口号
#[Read-Only Listener]
#type=listener
#service=Read-Only Service
#protocol=MySQLClient
#port=4008
[Read-Write Listener] 定义读写分离服务使用端口号
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4006 端口号
[MaxAdmin Listener] 定义管理服务使用端口号
type=listener
service=MaxAdmin Service
protocol=maxscaled
socket=default
port=4016 端口号
:wq3 配置数据库服务器(在数据库服务器上添加监控用户和路由用户)
注意：因为是主从结构 ，所以只需要在主服务器添加，从服务器会自动同步
[root@host51 ~]# mysql -uroot -p123qqq...A
添加监控用户 mysqla 用户
mysql> grant replication slave , replication client on *.* to
mysqla@"%" identified by "123qqq...A";
权限说明：
replication client 监视数据库服务的运行状态
replication slave 数据库服务器的主从角色
添加路由用户 mysqlb 用户
mysql> grant select on mysql.* to mysqlb@"%" identified by "123qqq...A"; #对授权库下的表有查询权限
#在从服务器查看用户是否同步
[root@host52 ~]# mysql -uroot -p123qqq...A -e 'select user from mysql.user where user="mysqla"'
[root@host52 ~]# mysql -uroot -p123qqq...A -e 'select user from mysql.user where user="mysqlb"'4 启动读写分离服务
验证数据库服务器的授权用户 mysqla 和 mysqlb
[root@host57 ~]# which mysql || yum -y install mariadb #安装提供mysql命令的软件
[root@host57 ~]# mysql -h192.168.4.51 -umysqla -p123qqq...A
[root@host57 ~]# mysql -h192.168.4.52 -umysqla -p123qqq...A
[root@host57 ~]# mysql -h192.168.4.51 -umysqlb -p123qqq...A
[root@host57 ~]# mysql -h192.168.4.52 -umysqlb -p123qqq...A
说明：能连接成功才是对的，如果连接失败：执行如下操作
在主数据库服务器host51 把添加 mysqla用户 和 mysqlb 用户的命令再执行一遍
[root@host57 ~]# maxscale -f /etc/maxscale.cnf 启动服务
[root@host57 ~]#
[root@host57 ~]# ls /var/log/maxscale/ 查看日志文件
maxscale.log
[root@host57 ~]# netstat -utnlp | grep 4006 查看读写分离服务端口号
tcp6 0 0 :::4006 :::* LISTEN 1580/maxscale
[root@host57 ~]#
[root@host57 ~]# netstat -utnlp | grep 4016 查看读写分离服务端口号
tcp6 0 0 :::4016 :::* LISTEN 1580/maxscale
#把服务杀死 再启动 相当于重启服务 （修改了配置文件后要重启服务使其配置生效）
[root@host57 ~]# killall -9 maxscale 通过杀进程的方式停止服务
[root@host57 ~]# maxscale /etc/maxscale.cnf 启动服务在57本机访问管理服务查看数据库服务的监控信息
[root@host57 ~]# maxadmin -uadmin -pmariadb -P4016
MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server | Address | Port | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
server1 | 192.168.4.51 | 3306 | 0 | Master, Running
server2 | 192.168.4.52 | 3306 | 0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------
MaxScale> exit
[root@maxscale57 ~]#排错方法 : 查看日志里的报错信息 vim /var/log/maxscale/maxscale.log
第四步：测试配置读写分离服务的配置
第1步： 客户端能够连接读写分离服务器访问数据库服务
#首先在主数据库服务器host51 添加客户端连接使用的用户
[root@host51 ~]# mysql -uroot -p123qqq...A
create database bbsdb;
create table bbsdb.a(id int);
grant select,insert on bbsdb.* to yaya@"%" identified by "123qqq...A";
#在从服务器host52查看存储数据库表和添加用户
[root@host52 ~]# mysql -uroot -pNSD2107...a
desc bbsdb.a;
select user from mysql.user where user="yaya";客户端host50连接读写分离服务器host57访问数据库服务
mysql -h读写分离服务器的ip -P读写分离服务的端口号 -u数据库授权用户名 -p密码
[root@host50 ~]# mysql -h192.168.4.57 -P4006 -uyaya -p123qqq...A第2步： 连接读写分离服务后，可以对数据做查询和存储操作
mysql> select * from bbsdb.a;
Empty set (0.00 sec)
mysql> insert into bbsdb.a values(8888);
Query OK, 1 row affected (0.06 sec)
mysql> select * from bbsdb.a;
+------+
| id |
+------+
| 8888 |
+------+
1 row in set (0.00 sec)
mysql>怎么验证查询select 访问就在host52从服务器获取的数据呢？
在从服务本机向表里添加1条记录（在从服务添加的新数据主服务器不会同步）
怎么验证存储数据insert 访问 就是存储在了主机服务器host51上？
[root@host52 ~]# mysql -uroot -p123qqq...A -e 'insert into bbsdb.a values(5252)'
[root@host52 ~]# mysql -uroot -p123qqq...A -e 'select * from bbsdb.a'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| id |
+------+
| 8888 |
| 5252 |
+------+
[root@host52 ~]#
[root@host51 ~]# mysql -uroot -p123qqq...A -e 'select * from bbsdb.a'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| id |
+------+
| 8888 |
+------+
[root@host51 ~]#
#客户端访问读写分离服务器查询数据
[root@host50 ~]# mysql -h192.168.4.57 -P4006 -uyaya -p123qqq...A -e 'select * from bbsdb.a'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| id |
+------+
| 8888 |
| 5252 |
+------+
[root@host50 ~]#
[root@host50 ~]# mysql -h192.168.4.57 -P4006 -uyaya -p123qqq...A -e 'insert into bbsdb.a values(666)'在主服务器本机查看数据
[root@host51 ~]# mysql -uroot -p123qqq...A -e 'select * from bbsdb.a'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| id |
+------+
| 8888 |
| 666 |
+------+
[root@host51 ~]#
[root@host50 ~]# mysql -h192.168.4.57 -P4006 -uyaya -p123qqq...A -e 'select * from bbsdb.a'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| id |
+------+
| 8888 |
| 5252 |
| 666 |
+------+
[root@host50 ~]#~~~~~~~多实例~~~~~~~~~
什么多实例：在一台 服务器上允许多个数据库服务
为什么要使用多实例？ 节约运维成本 提高硬件利用率
使用最新安装的模板机器克隆1台新虚拟机 配置IP 192.168.4.58
不需要安装mysql服务软件
把软件拷贝到虚拟机里 mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
配置多实例
安装软件
]# rpm -q libaio || yum -y install libaio
]# tar -xf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
]# mv mysql-5.7.20-linux-glibc2.12-x86_64 /usr/local/mysql
]# PATH=/usr/local/mysql/bin:$PATH
]# vim /etc/bashrc
export PATH=/usr/local/mysql/bin:$PATH 添加在文件的末尾
:wq
[root@host58 ~]# id mysql || useradd mysql
id: mysql: no such user
[root@host57 ~]# grep mysql /etc/passwd
mysql:x:1000:1000::/home/mysql:/bin/bash2)创建并编辑主配置文件
]# ls /etc/my.cnf 如果文件有的话要删除 rm -rf /etc/my.cnf
]# vim /etc/my.cnf 创建文件并编辑
#管理多实例服务 的 运行配置
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe 服务启动的时候 执行的是那个命令
mysqladmin = /usr/local/mysql/bin/mysqladmin 修改数据库管理员密码使用的命令
user = root 管理服务的启动者
#定义实例1
[mysqld1]
datadir = /dir1 数据库目录
port = 3307 服务的端口号
log-error = /dir1/mysqld1.err 错误日志文件
pid-file = /dir1/mysqld1.pid pid号文件
socket = /dir1/mysqld1.sock socket文件 (在数据库服务器本机访问多实例时
通过socket区分连接的实例服务)
#定义实例2
[mysqld2]
datadir = /dir2 数据库目录
port = 3308 服务的端口号
log-error = /dir2/mysqld2.err 错误日志文件
pid-file = /dir2/mysqld2.pid pid号文件
socket = /dir2/mysqld2.sock socket文件 (在数据库服务器本机访问多实例时 通过socket区分连接的实例服务)3)启动实例
说明：首次启动服务 会初始数据 和 生成初始密码
启动服务命令的输出信息的最后1行 是管理员登录的初始密码
启动实实例1 （[mysqld1]）
[root@host57 ~]# mysqld_multi start 1
查看目录下的文件列表
[root@host57 ~]# ls /dir1/
auto.cnf ib_logfile0 mysql mysqld1.sock sys
ib_buffer_pool ib_logfile1 mysqld1.err mysqld1.sock.lock
ibdata1 ibtmp1 mysqld1.pid performance_schema
可以查看到端口
[root@host58 ~]# netstat -utnlp | grep 3307
#使用初始密码连接服务
[root@host58 ~]# mysql -uroot -p'avH,8dVtZnaq' -S /dir1/mysqld1.sock
强制修改密码没有密码策略要求
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql>
mysql> alter user root@"localhost" identified by "123456";
Query OK, 0 rows affected (0.00 sec)
查看 建库 表 插入记录
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> exit 断开连接
Bye
使用新密码连接服务
[root@host57 ~]# mysql -uroot -p123456 -S /dir1/mysqld1.sock
mysql> create database gamedb;
mysql> create table gamedb.a(name char(10));
mysql> exit
Bye
库表存储在 实例1 [mysqld1] 的数据库目录下
[root@host57 ~]# ls /dir1/gamedb/
a.frm a.ibd db.opt
[root@host58 ~]#启动实例2 ([mysqld2])
[root@host58 ~]# mysqld_multi start 2
Installing new database in /dir2
2021-10-20T06:26:05.184849Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-10-20T06:26:07.020542Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-10-20T06:26:07.334988Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-10-20T06:26:07.454909Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 9bf8530f-316e-11ec-b224-525400a978ee.
2021-10-20T06:26:07.487083Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-10-20T06:26:07.487809Z 1 [Note] A temporary password is generated for root@localhost: spkb8>*iPpqi
[root@host57 ~]# ls /dir2 #查看数据库目录
auto.cnf ib_logfile0 mysql mysqld2.sock sys
ib_buffer_pool ib_logfile1 mysqld2.err mysqld2.sock.lock
ibdata1 ibtmp1 mysqld2.pid performance_schema
[root@host58 ~]#
#连接实例2
[root@host57 ~]# mysql -uroot -p'spkb8>*iPpqi' -S /dir2/mysqld2.sock
mysql> create database bbsdb;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql>
修改密码
mysql> alter user root@"localhost" identified by "654321";
Query OK, 0 rows affected (0.00 sec)
mysql> create database bbsdb;
Query OK, 1 row affected (0.00 sec)
mysql> exit
Bye
#连接实例2
[root@host57 ~]# mysql -uroot -p654321 -S /dir2/mysqld2.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.20 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bbsdb |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql>
[root@host57 ~]# netstat -utnlp | grep mysqld
tcp6 0 0 :::3307 :::* LISTEN 1565/mysqld
tcp6 0 0 :::3308 :::* LISTEN 1781/mysqld停止实例服务
（需要知道 数据库管理员root用户密码 才能停止服务）
#停止实例1 对应 [mysqld1]
[root@host57 ~]# mysqld_multi --user=root --password=123456 stop 1
#停止实例2 对应 [mysqld2 ]
[root@host57 ~]# mysqld_multi --user=root --password=654321 stop 2
[root@host57 ~]# netstat -utnlp | grep mysqld
[root@host57 ~]# ls /dir1/mysqld1.sock
ls: 无法访问/dir1/mysqld1.sock: 没有那个文件或目录
[root@host57 ~]# ls /dir2/mysqld2.sock
ls: 无法访问/dir2/mysqld2.sock: 没有那个文件或目录再次启动服务查看端口
[root@host57 ~]# mysqld_multi start 2
[root@host57 ~]# mysqld_multi start 1
[root@host57 ~]# netstat -utnlp | grep mysqld
tcp6 0 0 :::3307 :::* LISTEN 2136/mysqld
tcp6 0 0 :::3308 :::* LISTEN 1976/mysqld
[root@host57 ~]#本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
