数据读写分离 、 MySQL多实例
RDMS2_DAY02
数据读写分离介绍
把客户端查询数据的select 访问和 存储数据insert 访问
分别给不同的数据库服务器处理。
目的减轻单台数据库服务器的工作压力，
但是得保证负责处理select访问请求数据库服务器的数据要和处理insert访问请求的数据库服务器的数据一致。 所以要想实现数据的读写分离 存储数据的数据库服务器之间必须是主从结构。
实现数据读写分离的方式？
在客户端实现： 开发网站的程序员在写访问数据库服务器的脚本时， 在脚本里定义连接的
数据库服务器的ip地址
执行查询访问命令必须连接 slave服务器的ip地址
执行insert访问的命令必须连接 master服务器的ip地址
搭建读写分离服务器实现: 客户端查看数据和存储数据库的时候，连接的不是数据库服务器
而是读写分离服务器，由读写分离服务器根据客户端的访问类型，把请求给后端数据库服务器处理：
把查询请求select 命令 给slave服务器处理
把存储请求insert 命令 给master服务器处理
mysql中间件，
是统称 指的是架设在数据库服务器和客户端之间的软件，中间件功能各有不同
提供数据读写分离功能的中间件软件有： mysql-proxy maxscale mycat
案例 使用maxscale 提供数据读写服务 ，
把客户端的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 Master_Host: 192.168.4.51
第3步 配置读写分离服务器
准备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.rpm
2 修改主配置文件
[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 #端口号 :wq
3 配置数据库服务器(在数据库服务器上添加监控用户和路由用户)
注意：因为是主从结构 ，所以只需要在主服务器添加，从服务器会自动同步
[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 #启动服务
在56本机访问管理服务查看数据库服务的监控信息
[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@maxscale56 ~]#
排错方法 : 查看日志里的报错信息 vim /var/log/maxscale/maxscale.log
第四步：测试配置读写分离服务的配置
第1步： 客户端能够连接读写分离服务器访问数据库服务
#首先在主数据库服务器host51 添加客户端连接使用的用户
[root@host51 ~]# mysql -uroot -pNSD2107...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连接读写分离服务器host56访问数据库服务
mysql -h读写分离服务器的ip -P读写分离服务的端口号 -u数据库授权用户名 -p密码
[root@host50 ~]# mysql -h192.168.4.56 -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@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@host50 ~]# mysql -h192.168.4.56 -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 ~]# mysql -h192.168.4.56 -P4006 -uyaya -p123qqq...A -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@host50 ~]# mysql -h192.168.4.56 -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 | +------+
说明：如果主从结构中的从服务器宕机了，就实现不了读写分离了，
会把读写请求都给主服务器处理。
如果主从结构中的主服务器宕机了，读写分离服务无法访问
读写分离服务器 只有1台 单点故障问题无法避免。
~~~~~~~多实例~~~~~~~~~
什么多实例：在一台 服务器上允许多个数据库服务
为什么要使用多实例？ 节约运维成本 提高硬件利用率
使用最新安装的模板机器克隆1台新虚拟机 配置IP 192.168.4.58
不需要安装mysql服务软件
把软件拷贝到虚拟机里 mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
配置多实例
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)创建并编辑主配置文件
]# 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@host58 ~]# mysqld_multi start 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'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@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 ([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 ~]#
停止实例服务
（需要知道 数据库管理员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
over
