Mysql数据库学习（七）mysql读写分离、多实例

数据库系统学习
数据读写分离 、 MySQL多实例
软件链接：https://pan.baidu.com/s/1wT2igR19Om1gQbQs6FnU8g
提取码：tty4
1 数据读写分离
介绍：也是一种数据存储结构，把客户端访问时的查询select访问和写(insert update delete)访问分别给不同的数据库服务器处理

为什么要使用读写分离结构提供数据存储服务：
把查询请求给 slave服务器 把写请求给master服务器处理 减轻master 服务器工作压力

如何实现数据的读写分离：
1 通过程序员实现 select ----> slave服务器ip地址 Insert -----> master服务器IP地址

2通过服务实现： 搭建读写分离服务器，实现数据的读写分离，访问数据库服务时，连接读写分离服务器的Ip地址，服务器根据访问的类型 做处理，把select访问 给slave服务器 ， 把insert 访问给master服务器。

能够实现数据读写分离服务的软件有哪些：软件的统称 叫 MySQL中间件（架设在数据库服务器和客户端之间的软件） 开源软件
mysql-proxy mycat maxscale

3 读写分离服务的拓扑结构
client 50 —> 读写分离服务器 master51 /slave52

4 配置读写分离存储结构 ，具体步骤如下：
4.1 配置MySQL 一主一从同步结构
51主数据库服务器
52从数据库服务器
要停止防火墙服务

4.2 配置读写分离服务器57
1.57主机不需要运行mysqld服务并停止防火墙服务
2.安装软件maxscale
]# yum -y install maxscale-2.1.2-1.rhel.7.x86_64.rpm

*** 安装软件后提供的： 命令 主配置文件 日志目录

3 修改主配置文件

[root@host57 ~]# cat /etc/maxscale.cnf
[maxscale]
threads=auto

[server1]
type=server
address=192.168.4.51
port=3306
protocol=MySQLBackend

[server2]
type=server
address=192.168.4.52
port=3306
protocol=MySQLBackend

[MySQL Monitor]
type=monitor
module=mysqlmon
servers=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
user=mysqlb
[root@host57 ~]# cat /etc/maxscale.cnf
[maxscale]
threads=auto

[server1]
type=server
address=192.168.4.51
port=3306
protocol=MySQLBackend

[server2]
type=server
address=192.168.4.52
port=3306
protocol=MySQLBackend

[MySQL Monitor]
type=monitor
module=mysqlmon
servers=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
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
[root@host57 ~]#

4 配置数据库服务器（ 51 he 52 都要配置）
（监控用户） 添加用户mysqla 监视数据库服务器时 57连接数据库服务器使用的用户
（路由用户）添加用户mysqlb ： 57 使用mysqlb用户连接数据库服务器51 和 52 查看 是否添加了客户连接服务使用的用户

因为51是主数据库服务器，所以只需要在51添加即可，52是从服务器可以自动同步。

在51添加

grant replication slave , replication client on . to mysqla@"%" identified by “123qqq…A”;
replication slave 获取主从角色的权限
replication client 获取数据库服务是否运行的权限
grant select on mysql.* to mysqlb@"%" identified by “123qqq…A”;

在52主机查看
select user,host from mysql.user where user in (“mysqla”,“mysqlb”);

5、 57 启动读写分离服务
5.1 停止 51 、52 、57 的防火墙服务

5.2 检查数据库服务器的授权用户mysqla 和 mysqlb

[root@host57 ~]# which mysql || yum -y install mariadb

[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

5.3 启动读写分离服务
[root@host57 ~]# maxscale -f /etc/maxscale.cnf

		    查看服务状态

[root@host57 ~]# ps -C maxscale #查看进程
PID TTY TIME CMD
82413 ? 00:00:00 maxscale
[root@host57 ~]#
[root@host57 ~]# ss -utnlp | grep maxscale #查看端口号
tcp LISTEN 0 128 :::4006 ::😗 users:((“maxscale”,pid=82413,fd=11))
tcp LISTEN 0 128 :::4016 ::😗 users:((“maxscale”,pid=82413,fd=12))
[root@host57 ~]#

		    测试配置
			在57本机访问管理服务，查看监控状态

[root@host57 ~]# maxadmin -P4016 -uadmin -pmariadb
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

2 连接读写分离服务，可以存储数据和查看数据
2.1 在数据库服务器添加用户，给连接使用
[root@host51 ~]# mysql -uroot -p123456 -e ‘create database db22’
[root@host51 ~]# mysql -uroot -p123456 -e ‘create table db22.t1(num int)’
[root@host51 ~]# mysql -uroot -p123456 -e ‘grant select,insert on db22.* to jing99@"%" identified by “123qqq…A”’

2.2 连接57 访问数据
[root@host50 ~]# mysql -h192.168.4.57 -P4006 -ujing99 -p123qqq…A
mysql> select * from db22.t1;
Empty set (0.07 sec)

mysql> insert into db22.t1 values(666);
Query OK, 1 row affected (0.03 sec)

mysql> select * from db22.t1;
±-----+
| num |
±-----+
| 666 |
±-----+
1 row in set (0.00 sec)

mysql>

3 测试57服务的读写分离功能

[root@host52 ~]# mysql -uroot -p123qqq…A -e ‘insert into db22.t1 values(9999)’
[root@host51 ~]# mysql -uroot -p123456 -e ‘select * from db22.t1’
±-----+
| num |
±-----+
| 666 |
±-----+

[root@host50 ~]# mysql -h192.168.4.57 -P4006 -ujing99 -p123qqq…A -e ‘select * from db22.t1’
±-----+
| num |
±-----+
| 666 |
| 9999 |
±-----+
[root@host50 ~]# mysql -h192.168.4.57 -P4006 -ujing99 -p123qqq…A -e ‘insert into db22.t1 values(333333)’

[root@host50 ~]# mysql -h192.168.4.57 -P4006 -ujing99 -p123qqq…A -e ‘select * from db22.t1’
±-------+
| num |
±-------+
| 666 |
| 9999 |
| 333333 |
±-------+

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
2 MySQL多实例
环境准备
创建1台新的虚拟机 不需要安装任何版本的MySQL服务软件
配置ip地址 192.168.4.58 配置好yum源
拷贝 mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz 到58 主机

相关概念：
 多实例介绍:在一台物理主机上运行多个数据库服务
配置多实例服务，具体步骤如下：

1 安装软件 mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
]# yum –y install libaio
]# useradd mysql
]# tar -zxvf 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

      2 创建并编辑主配置文件/etc/my.cnf

     ]# ls /etc/my.cnf
         ]# rm -rf /etc/my.cnf
     ]# vim /etc/my.cnf
		[mysqld_multi] #多实例服务的运行配置
			服务启动时调用的执行程序
			启动多实例服务的用户
			修改多实例管理员密码的命令

		[mysqld1] 实例1 （第1个数据库服务）
			数据库目录
			端口号
			错误日志文件
			pid号文件
			socket文件

                [mysqld2] 实例2 （第2个数据库服务）
			数据库目录
			端口号
			错误日志文件
			pid号文件
			socket文件
         :wq

例子
vim /etc/my.cnf
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user=root

[mysqld1]
datadir=/dir1
port=3307
pid-file=/dir1/mysqld1.pid
log-error=/dir1/mysqld1.err
socket=/dir1/mysqld1.sock

[mysqld2]
datadir=/dir2
port=3308
pid-file=/dir2/mysqld2.pid
log-error=/dir2/mysqld2.err
socket=/dir2/mysqld2.sock

:wq
启动实例1
[root@host58 etc]# mysqld_multi start 1 #首次启动会创建数据和设置管理员root 的登录密码

[root@host58 etc]# ls /dir1/
[root@host58 etc]# ss -utnlp | grep 3307 查看端口号

	使用初始密码连接本机的实例1 服务

]# mysql -uroot -p'2_)iwtw(:h+A' -S /dir1/mysqld1.sock

mysql> alter user root@“localhost” identified by “123456”;
mysql> show databases;
mysql> exit

[root@host58 etc]# mysql -uroot -p123456 -S /dir1/mysqld1.sock 使用修改后的密码连接

	启动实例2

[root@host58 etc]# mysqld_multi start 2
[root@host58 etc]# ls /dir2/
[root@host58 etc]# ss -utnlp | grep 3308 查看端口号

		停止实例服务

[root@host58 etc]# mysqld_multi --user root --password 123456 stop 1 #停止实例1
[root@host58 etc]# mysqld_multi --user root --password 123456 stop 2 #停止实例2

		统一排错方式：
			检查 /etc/my.cnf
			rm -rf /dir1
			rm -rf /dir2
			pkill -9  mysqld 多执行几遍 知道提示没有进程为止

                            再执行启动实例的命令

mysqld_multi start 1 能查看到端口号和数据库目录的文件为成功
mysqld_multi start 2 能查看到端口号和数据库目录的文件为成功