一、数据读写分离
1、读写分离概述
1.1 数据读写分离介绍
把客户端查询数据的（读）select 访问和存储数据（写）insert、delect、update 访问 分别给不同的数据库服务器处理。
目的减轻单台数据库服务器的工作压力，
但是得保证负责处理select访问请求数据库服务器的数据要和处理insert访问请求的数据库服务器的数据一致。 所以要想实现数据的读写分离 ，存储数据的数据库服务器之间必须是主从结构。
1.2 实现数据读写分离的方式？
在客户端实现： 开发网站的程序员在写访问数据库服务器的脚本时，
在脚本里定义连接的数据库服务器的ip地址,
执行查询访问命令必须连接 slave服务器的ip地址 ,
执行insert访问的命令必须连接 master服务器的ip地址.
搭建读写分离服务器实现: 客户端查看数据和存储数据库的时候，连接的不是数据库服务器,
而是读写分离服务器，由读写分离服务器根据客户端的访问类型，
把请求给后端数据库服务器处理。
原理：
把查询请求select 命令 给slave服务器处理；
把存储请求insert 命令 给master服务器处理。
mysql中间件：
是统称 ：指的是架设在数据库服务器和客户端之间的软件，中间件功能各有不同。
提供数据读写分离功能的中间件软件有： mysql-proxy maxscale mycat
2、案例拓扑图
添加一个mysql代理：
接收客户端的访问，为客户端提供访问数据接口
3、构建思路
案例：
使用maxscale 提供数据读写服务 ，
把客户端的select请求给 主从结构中的slave服务器 192.168.4.52
把客户端的insert请求给 主从结构中的master 服务器 192.168.4.51
3.1 部署mysql一主一从结构
第1 步 ： 把host51 配置为master数据库服务器
第2 步 ： 把host52 配置为slave数据库服务器
**第3步：**检查从服务器的状态：
[root@host52 ~]# mysql -uroot -p123456 -e 'show slave status \G' | grep -i yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@host52 ~]# mysql -uroot -p123456 -e 'show slave status \G' | grep -i master_host
Master_Host: 192.168.4.51
3.2 部署mysql代理服务器(读写分离服务器)
装包-- maxscale
配置–/etc/maxscale.cnf
起服务–maxscale -f /etc/maxscale.cnf
准备1 台虚拟机：
配置ip 192.168.4.57
配置 yum 源
关闭 firewalld、selinux
注意不需要安装mysql服务软件。如果安装了的话 服务不启动就可以。
具体配置如下：
（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.rpm
（2）修改主配置文件
[root@host57 ~]# cp /etc/maxscale.cnf /root/ # 备份主配置文件
[root@host57 ~]# vim /etc/maxscale.cnf
#服务启动后线程的数量
[maxscale]
threads=auto
#指定第1台数据库服务器的ip地址
[server1]
type=server
address=192.168.4.51
port=3306
protocol=MySQLBackend
#指定第2台数据库服务器的ip地址
[server2]
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=123456 #连接密码(要符合server1，2的密码策略)
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=123456 #连接密码
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 #端口号
:wq
（3）配置数据库服务器(在数据库服务器上添加监控用户和路由用户)
**注意：**因为是主从结构 ，所以只需要在主服务器添加，从服务器会自动同步
[root@host51 ~]# mysql -uroot -p123456
添加监控用户 mysqla 用户
mysql> grant replication slave , replication client on *.* to
mysqla@"%" identified by "123456";
权限说明：
replication client 监视数据库服务的运行状态
replication slave 数据库服务器的主从角色
添加路由用户 mysqlb 用户
mysql> grant select on mysql.* to mysqlb@"%" identified by "123456"; #对授权库下的表有查询权限
#在从服务器查看用户是否同步
[root@host52 ~]# mysql -uroot -p123456 -e 'select user from mysql.user where user="mysqla"'
[root@host52 ~]# mysql -uroot -p123456 -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 -p123456
[root@host57 ~]# mysql -h192.168.4.52 -umysqla -p123456
[root@host57 ~]# mysql -h192.168.4.51 -umysqlb -p123456
[root@host57 ~]# mysql -h192.168.4.52 -umysqlb -p123456
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
#说明：能连接成功才是对的，如果连接失败：执行如下操作
在主数据库服务器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 启动服务
在host57本机访问管理服务查看数据库服务的监控信息
[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
排错方法 : 查看日志里的报错信息 vim /var/log/maxscale/maxscale.log
3.3 测试配置
*测试配置读写分离服务的配置*
（1）第1步：客户端能够连接读写分离服务器访问数据库服务
#首先在主数据库服务器host51 添加客户端连接使用的用户
[root@host51 ~]# mysql -uroot -p123456
create database bbsdb;
create table bbsdb.a(id int);
grant select,insert on bbsdb.* to yaya@"%" identified by "123456";
#在从服务器host52查看存储数据库表和添加用户
[root@host52 ~]# mysql -uroot -p123456
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 -p123456
（2）第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 -p123456 -e 'insert into bbsdb.a values(5252)'
[root@host52 ~]# mysql -uroot -p123456 -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 -pNSD2107...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.56 -P4006 -uyaya -p123456 -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.56 -P4006 -uyaya -p123456 -e 'insert into bbsdb.a values(666)'
#在主服务器本机查看数据
[root@host51 ~]# mysql -uroot -pNSD2107...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.56 -P4006 -uyaya -p123456 -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台 单点故障问题无法避免。
二、多实例
1、概述
什么**多实例：**在一台 服务器上允许多个数据库服务
为什么要使用多实例: 节约运维成本 提高硬件利用率
2、配置多实例案例
使用最新安装的模板机器克隆1台新虚拟机 配置IP 192.168.4.58 ;
不需要安装mysql服务软件 ;
把软件拷贝到虚拟机里 mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz ;
2.1 安装软件
]# 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@host58 ~]# grep mysql /etc/passwd
mysql:x:1000:1000::/home/mysql:/bin/bash
2.2 创建并编辑主配置文件
主配置文件/etc/my.cnf
每个实例要独立：数据库目录、端口
socket文件、pid文件、错误日志文件
]# 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] # 实例进程名称，1是实例编号
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区分连接的实例服务)
2.3 启动实例
说明：首次启动服务 会初始数据 和 生成初始密码
启动服务命令的输出信息的最后1行 是管理员登录的初始密码
1) 启动实实例1 [mysqld1]
**mysqld_multi start 实例编号**
本机连接：
mysql -uroot -p'初始密码' -S sock文件路径
[root@host58 ~]# mysqld_multi start 1
Installing new database in /dir1
2022-06-20T03:19:01.723079Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-06-20T03:19:03.173699Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-06-20T03:19:03.562252Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-06-20T03:19:03.750739Z 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: bc803ae0-f047-11ec-9331-525400d07cfa.
2022-06-20T03:19:03.782485Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-06-20T03:19:03.786225Z 1 [Note] A temporary password is generated for root@localhost: qrLWGpRi(5r=
# 1.查看目录下的文件列表
[root@host58 ~]# 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'qrLWGpRi(5r=' -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@host58 ~]# 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@host58 ~]# ls /dir1/gamedb/
a.frm a.ibd db.opt
[root@host58 ~]#
2) 启动实例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@host58 ~]# 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@host58 ~]# 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@host58 ~]# 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
affiliates. Other names may be trademarks of their respective
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@host58 ~]# netstat -utnlp | grep mysqld
tcp6 0 0 :::3307 :::* LISTEN 1565/mysqld
tcp6 0 0 :::3308 :::* LISTEN 1781/mysqld
[root@host58 ~]#
2.4 停止实例服务
**mysqld_multi --user=root --password=123456 stop 进程编号**
（需要知道 数据库管理员root用户密码 才能停止服务）
#停止实例1对应 [mysqld1]
[root@host58 ~]# mysqld_multi --user=root --password=123456 stop 1
#停止实例2对应 [mysqld2 ]
[root@host58 ~]# mysqld_multi --user=root --password=654321 stop 2
[root@host58 ~]# netstat -utnlp | grep mysqld
[root@host58 ~]# ls /dir1/mysqld1.sock
ls: 无法访问/dir1/mysqld1.sock: 没有那个文件或目录
[root@host58 ~]# ls /dir2/mysqld2.sock
ls: 无法访问/dir2/mysqld2.sock: 没有那个文件或目录
再次启动服务查看端口
[root@host58 ~]# mysqld_multi start 2
[root@host58 ~]# mysqld_multi start 1
[root@host58 ~]# netstat -utnlp | grep mysqld
tcp6 0 0 :::3307 :::* LISTEN 2136/mysqld
tcp6 0 0 :::3308 :::* LISTEN 1976/mysqld
[root@host58 ~]#
添加实例3 在 /etc/my.cnf 文件末尾添加如下行
[mysqld3]
datadir = /dir3
port = 3309
socket = /dir3/mysqld3.sock
pid-file = /dir3/mysqld3.pid
log-error = /dir3/mysqld3.log
报错方法（有端口号的）：
1.查看实例所使用的端口号 ss -utnlp | grep 3307
2.pstree -p | grep mysqld # 查看进程树
3.杀死进程
kill -9 父进程
kill -9 子进程
4.删除数据库目录 rm -rf /dir1
5.排错–检查配置文件（/etc/my.cnf）
-utnlp | grep mysqld
tcp6 0 0 :::3307 ::😗 LISTEN 2136/mysqld
tcp6 0 0 :::3308 ::😗 LISTEN 1976/mysqld
[root@host58 ~]#
**添加实例3 在 /etc/my.cnf 文件末尾添加如下行**
```shell
[mysqld3]
datadir = /dir3
port = 3309
socket = /dir3/mysqld3.sock
pid-file = /dir3/mysqld3.pid
log-error = /dir3/mysqld3.log
报错方法（有端口号的）：
1.查看实例所使用的端口号 ss -utnlp | grep 3307
2.pstree -p | grep mysqld # 查看进程树
3.杀死进程
kill -9 父进程
kill -9 子进程
4.删除数据库目录 rm -rf /dir1
5.排错–检查配置文件（/etc/my.cnf）
6.重启服务
329
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
