2021/10/14_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
mysql: [Warning] Using a password on the command line interface can be insecure.
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
[root@host52 ~]# 
[root@host52 ~]# 
[root@host52 ~]# mysql -uroot -p123qqq...A -e 'show slave status \G' | grep -i master_host
mysql: [Warning] Using a password on the command line interface can be insecure.
                  Master_Host: 192.168.4.51
[root@host52 ~]#
 
 
 第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 ~]# rpm -ivh 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@maxscale56 ~]# which  mysql || yum -y install mariadb    #安装提供mysql命令的软件
[root@maxscale56 ~]# mysql -h192.168.4.51 -umysqla -p123qqq...A
[root@maxscale56 ~]# mysql -h192.168.4.52 -umysqla -p123qqq...A
[root@maxscale56 ~]# mysql -h192.168.4.51 -umysqlb -p123qqq...A
[root@maxscale56 ~]# 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@maxscale56 ~]# 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 								
MySQL>								
						第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 -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  -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.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@host51 ~]#
[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 |
+------+
[root@host50 ~]# 

				说明：如果主从结构中的从服务器宕机了，就实现不了读写分离了，
					  会把读写请求都给主服务器处理。
					  
					  如果主从结构中的主服务器宕机了，读写分离服务无法访问
					  
					  读写分离服务器 只有1台 单点故障问题无法避免。



~~~~~~~多实例~~~~~~~~~ 						
什么多实例：在一台 服务器上允许多个数据库服务
为什么要使用多实例？ 节约运维成本   提高硬件利用率

使用最新安装的模板机器克隆1台新虚拟机  配置IP 192.168.4.58 
不需要安装mysql服务软件
把软件拷贝到虚拟机里 mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
配置多实例
			1）安装软件   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

			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  （[mysqld1]） 
[root@host58 ~]# mysqld_multi start  1	 启动服务命令的输出信息的最后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
[root@host58 ~]# ls /dir1/gamedb/    库表存储在 实例1   [mysqld1]  的数据库目录下
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 ~]# 
[root@host58 ~]# mysql -uroot -p'spkb8>*iPpqi' -S /dir2/mysqld2.sock  #连接实例2
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
[root@host58 ~]# mysql -uroot -p654321 -S /dir2/mysqld2.sock  #连接实例2
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用户密码 才能停止服务）
[root@host58 ~]# mysqld_multi  --user=root --password=123456 stop 1        #停止实例1    对应  [mysqld1]
[root@host58 ~]# mysqld_multi  --user=root --password=654321 stop 2        #停止实例2   对应 [mysqld2 ]
[root@host58 ~]# 
[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