MySQL 学习六 读写分离 ，MySQL多实例，MySQL优化

一、读写分离
——把客户端访问数据时的查询请求和写请求分别给不同的数据库服务器处理。
1、MySQL 读写分离涉及的问题：
——主从复制的应用局限性？
——如何分离MySQL读、写流量？
——在客户端区分，还是在服务器端区分？
1.1.1 为要对数据做读写分离？
——减轻单台数据库服务器的并发访问压力
——提高数据库服务器硬件利用率
1.1.2 实现数据读写分离的方式？
——人肉分离： 做不同的数据操作时，访问指定的数据库服务器
——使用mysql中间件提供的服务实现：mycat mysql-proxy maxscale
1.1.3 使用中间件提供的服务做数据读写分离的缺点？
——单点故障
——当访问量大时，会成为网络瓶颈
1.2 读写分离的原理
——多台MySQL服务器
—分别提供读、写服务，均衡流量
—通过主从复制报错数据一致性
——由MySQL代理面向客户端
—收到SQL写请求时，交给服务器A处理
—收到SQL读请求时，交给服务器B处理
—具体区分策略由服务器设置

1.3 构建读写分离
——思路：已搭建好MYSQL主从复制 （其中slave为只读）
添加一台MySQL代理服务器（部署/启用 maxscale）
客户端通过代理服务器主机访问MySQL数据库（访问代理服务器）
二、 部署maxscale 服务
2.1 Maxscale软件：
——MaxScale是一个支持高可用，负载均衡，具有良好的可扩展性，高性能的基于事件驱动的同时具有代理和管理功能的中间件，MaxScale 是插件式结构，由mariadb 开发。
——MaxScale运行在clinet与server之间，起到一个”Exchange”的作用。
——由这几个模块组成：Protocol，Router，Monitor，Authentication，Filter and logging.
协议插件——包括客户端连接协议，和连接数据库的协议；
路由插件——把客户端的请求转发给后端数据库服务器，读写分离和负载均衡的功能就是由这个模块实现的；
监控插件——对各个数据库服务器进行监控；
认证插件——提供了登录认证功能，MaxScale 会读取并缓存数据库中 user 表中的信息，当有连接进来时，
先从缓存 信息中进行验证，如果没有此用户，会从后端数据库中更新信息，再次进行验证；
日志和过滤插件——提供简单的数据库防火墙功能，可以对SQL进行过滤和容错
——工作原理：client并不会直接与server建立连接，而是通过MaxScale来做连接创建和数据交换，MaxScale接收client的请求，经过（或不经过）Filter处理当前的Request，然后通过Router将client发送的request发送到后端server，server返回的数据经过（或不经过）处理之后再返回给client。MaxScale同时维护client请求的连接和到server的连接。

2.2 配置数据读写分离服务（192.168.4.54）
2.2.2 环境准备：
——setenforce 0
systemctl stop firewalld
yum repolist
ping -c 2 192.168.4.51 （51为主库）
ping -c 2 192.168.4.52 （52 为从库）
51]主库上授权用户访问数据权限：
mysql> grant all on webdb.* to webuser@"%" identified by “123456”;
2.2.2 安装软件： maxscale-2.3.6-1.centos.7.x86_64.rpm
#yum -y install openssl （这里要更新yum源，不然会出现openssl版本不对的问题）
#rpm -ivh maxscale-2.3.6-1.centos.7.x86_64.rpm
#rpm -qa | grep maxscale
#rpm -ql maxscale 查看安装文件的位置
#rpm -qc maxscale qc—查看配置文件放在哪里？
/etc/maxscale.cnf.template 主配置文件的模板文件

2.2.3 修改配置文件
– #ls /etc/maxscale.cnf
–# cp /etc/maxscale.cnf /etc/maxscale.cnf.bak
–# vim /etc/maxscale.cnf
–[maxscale] //服务运行后开启的线程数量
threads=auto //#开启线程个数，默认为1.设置为auto会同cpu核数相同
[server1] //定义数据库服务器的
type=server
address=192.168.4.51 //数据库服务器ip
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=scalemon 监视数据库服务器时连接的用户名
passwd=123456
monitor_interval=10000 监视频率
#不定义只读服务
53 #[Read-Only Service]
54 #type=service
55 #router=readconnroute
56 #servers=server1
57 #user=myuser
58 #passwd=mypwd
59 #router_options=slave
#定义读写分离服务
[Read-Write-Service]
type=service
router=readwritesplit
servers=server1,server2 数据库服务器列表
user=maxscale #验证连接代理服务器访问数据库服务器的用户是否存在
passwd=123456
max_slave_connections=100% (自己添加)
#定义管理服务
76 [MaxAdmin Service]
77 type=service
78 router=cli 命令行
#不指定只读服务使用的端口号
86 #[Read-Only Listener]
87 #type=listener
88 #service=Read-Only Service
89 #protocol=MySQLClient
90 #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=4018 #不使用的默认端口时指定使用的端口
（socket 与 port 不能同时启用，只能用其中一个，另一个#）

2.2.4 根据配置文件的设置，在2台数据库服务器上添加授权用户
监控数据库服务器时，连接数据库服务器的用户
51】mysql> grant replication slave,replication client on . to scalemon@’%’ （创建监控用户）
-> identified by “123456”;
52】mysql> grant replication slave,replication client on . to scalemon@’%’
-> identified by “123456”;

验证访问数据时，连接数据库服务器使用的用户，是否在数据库服务器上存在的连接用户；
51】mysql> grant select on mysql.* to maxscale@’%’ identified by “123456”; （创建路由用户）
52】mysql> grant select on mysql.* to maxscale@’%’ identified by “123456”;
51】mysql> select user ,host from mysql.user where user in (“scalemon”,“maxscale”);
52】mysql> select user ,host from mysql.user where user in (“scalemon”,“maxscale”);
启动服务
#maxscale --help
#maxscale -f /etc/maxscale.cnf -U maxscale
看服务进程和端口
#netstat -utnlp | grep :4006 查看端口
#netstat -utnlp | grep :4018 查看端口
#ps -C maxscale 查看进程
#kill -9 9350 停止服务

2.2.5 测试配置
a 在本机访问管理管端口查看监控状态
]#maxadmin -P端口 -u用户 -p密码
54]#maxadmin -P4018 -uadmin -pmariadb
MaxScale> 123456
Commands must consist of at least two words. Type help for a list of commands
MaxScale> list --help
MaxScale> list servers
server1 | 192.168.4.51 | 3306 | 0 | Master, Running
server2 | 192.168.4.52 | 3306 | 0 | Slave, Running
b 客户端192.168.4.53访问数据读写分离服务
53]#which mysql
]#mysql -h读写分离服务ip -P4006 -u用户名 -p密码
]# mysql -h192.168.4.54 -P4006 -uwebuser -p123456
mysql> select @@hostname 查询当前数据库主机名
mysql> 执行插入或查询 （ 在51 和 52 本机查看记录）
52】
————----————————————————————————————————
mysql多实例
2.1 多实例介绍
多实例是指：在一台物理主机上运行多个数据库服务
为什么使用多实例：—节约运维成本；—提供硬件利用率

2.2 配置多实例
（要求：在192.168.4.54 上配置mysql多实例，运行2个数据库服务，
第1个数据库服务 数据库目录 /dataone
服务端口号 3307
sock文件 /dataone/mysql.sock
日志文件 /datanone/mysqld.log
第2个数据库服务 数据库目录 /datatwo
服务端口号 3308
sock文件 /datatwo/mysql.sock
日志文件 /datantwo/mysqld.log)

1 环境准备
#ps -C maxscale
]# kill -9 9519
]# ls /etc/my.cnf
]# netstat -autnlp |grep :3306 （查看不能有这个端口）
]# mv /etc/my.cnf /etc/my.cnf.bak
查看3307 3308端口,是否被占用
]# netstat -antulp |grep :3307
]# netstat -antulp |grep :3308
2 安装提供多多实例服务的，mysql数据库服务软件
下载网站：http://mirrors.ustc.edu.cn/mysql-ftp/Downloads/MySQL-5.7/
mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
]# du -sh mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz
]# tar -zxvf mysql-5.7.24-linux-glibc2.12-i686.tar.gz （免安装的）
]# mv mysql-5.7.25-linux-glibc2.12-x86_64 /usr/local/mysql
]# ls /usr/local/mysql/
]# ls /usr/local/mysql/bin/
]# echo “export PATH=/usr/local/mysql/bin:$PATH” >> /etc/profile
]# source /etc/profile
]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
3、编辑配置文件 /etc/my.cnf
——每个实例要有独立的数据库目录和监听端口号；
——内阁实例要有独立的实例名称和独立的sock文件

]#rm -rf /etc/my.cnf
]#vim /etc/my.cnf
sock文件 是指服务自己访问自己的接口文件
[mysqld_multi] #启用多实例
mysqld = /usr/local/mysql/bin/mysqld_safe #服务启动调用的进程
mysqladmin = /usr/local/mysql/bin/mysqladmin #管理命令路径
user = root #调用启动程序的用户名

[mysqld1] #实例编号
port=3307 #监听端口
datadir=/dataone #数据库目录
socket=/dataone/mysqld.sock #sock文件
log-error=/dataone/mysqld.log #错误日志
pid-file=/dataone/mysqld.pid #pid号文件

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

4 根据配置文件的设置，做相应的配置
4.1创建数据库目录
]# mkdir -p /dataone
]# mkdir -p /datatwo
4.2创建进程运行的所有者和组 mysql
]# useradd mysql
]# chown mysql:mysql /data*
]# mysql
若是出现：报错：Can’t connect to local MySQL server through socket '/tmp/mysql.sock
建立新的软连接]# ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock

4.3 初始化授权库
]#mysqld --user=mysql --basedir=软件安装目录 --datadir=数据库目录 --initialize
】#man mysql或者]# mysql --help
#mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/dataone --initialize
2019-05-08T08:34:49.578294Z 1 [Note] A temporary password is generated for
root@localhost: M,TeK5Pr6r3o （第一个实例数据库管理员登录本机初始化密码）
]# ls /dataone （要是想重新初始化，需要把该目录下的文件删除）
#mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/datatwo --initialize
A temporary password is generated for root@localhost: JwLfv!/uC28t 第二个实例的数据库管理员本机登录初始化密码）
]# ls /datatwo

5 、启动多实例服务
]# mysqld_multi start 实例编号
]# mysqld_multi start 1
]# mysqld_multi start 2 （没有报错就成功）
]# netstat -antulp |grep :3307 查看端口是否存在
]# netstat -antulp |grep :3308
]# ls /dataone 查看服务文件
mysqld.log 、 mysqld.sock 、 mysqld.pid 出现该三个文件即成功
]# ls /datatwo

6、 访问多实例服务
]#mysql -uroot -p’密码’ -S sock文件 #首次登录，使用初始密码
访问实例1：]# mysql -uroot -p’M,TeK5Pr6r3o’ -S /dataone/mysqld.sock （注意一点不能写错）
mysql> show databases; （下面会提示使用初始密码登录后，要求修改登录密码）
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> ALTER USER user() identified by “新密码”;
mysql> ALTER USER user() identified by “123456”; 修改密码
mysql> show databases;

访问实例2：]# mysql -uroot -p’JwLfv!/uC28t’ -S /datatwo/mysqld.sock
mysql> show databases;
mysql> ALTER USER user() identified by “123456”; 修改密码

7、 停止多实例服务
]# mysqld_multi --user=root --password=密码 stop 实例编号
]# mysqld_multi --user=root --password=123456 stop 1
]# netstat -antulp | grep :3307
]# mysqld_multi --user=root --password=123456 stop 2
]# netstat -antulp | grep :3308
+++——————————————————————————————————————————————————
三、mysql调优
3.1 mysql体系结构 （由8个功能模块组成）：
管理工具： 安装服务软件后，提供的命令
mysqldump
mysqlbinlog
mysqladmin
连接池： 当接收到客户端的连接请求后，检查是否有资源响应客户端的连接请求。
SQL接口： 把sql命令传递给mysqld线程处理

分析器： 检查执行的sql命令是否有语法错误，和是否有访问权限。

优化器：优化执行的sql命令，已最节省资源的方式执行sql命令

查询缓存： 缓存曾经查找到的记录,缓存空间从物理内存划分出来的。

存储引擎： 是表的处理器，不同的存储引擎有不同的功能和数据存储方式。Myisam innodb

文件系统： 存储表中记录的磁盘

3.2mysql服务处理查询请求过程：
数据服务器接收到查询请求后，先从查询缓存里查找记录，若查询缓存里有查找记录，直接从缓存提取数据给客户端，
反之到表去查询记录，把查到的记录先存放到查询缓存里在返回给客户端

3.3mysql调优
——替换有问题的硬件（CPU 磁盘、内存）
——服务程序的运行参数调整
——对SQL查询进行优化
3.3.1 如何优化mysql数据库服务器（那些原因会导致数据库服务器处理客户端的连接请求慢？）
A、硬件配置低，导致处理速度慢。 CPU 内存 存储磁盘
uptime free -m top --> 0.0 wa
存储磁盘接口转速：不低于15000/s ; #uptime
查看交换分区：# free -m
查看磁盘 读写速度：#top

b 、网络带宽窄 网络测速软件

c 、提供服务软件的版本低，导致性能低下：
1 查看服务运行时的参数配置 my.cnf
mysql> show variables;
mysql> show variables like “%innodb%”;
** 常用参数：
并发连接数量**
Max_used_connections/max_connections=0.85
500/x=0.85 * 100% = 85% 理想比率<=85%
max_connnection 允许的最大并发连接数
connection_timeout 等待连接超时，默认10秒，仅登录时有效；
wait_timeout 等待关闭连接的不活动超时秒数，默认为28800秒（8小时）
show global status like “Max_used_connections”; 查看最大连接数
set global max_connections = 数字；

连接超时时间
show variables like “%timeout%”;
connect_timeout 客户端与服务器建立连接时tcp三次握手超时是时间
wait_timeout 户端与服务器建立连接后，等待执行sql命令的超时时间。

可以重复使用的线程的数量 thread
show variables like “%thread%”;
thread_cache_size = 9 线程缓存
所有线程同时打开表的数量
show variables like “%open%”;
table_open_cache
mysqld -----> disk ---->x.ibd ----> memory ----> disk 访问操作表的过程

与查询相关参数的设置 (字节) mysqld
**key_buffer_size 用于MyISAM 引擎的关键索引缓存大小
sort_buffer_size 为每个要排序的线程分配此大小的缓存空间（增大此值可提高order 和 group的速度）
read_buffer_size 为顺序读取表记录保留的缓存大小
thread_cache_zize 允许保存在缓存中被重用 的线程数量
table_open_cache 为所有线程缓存的打开的表的数量 **
select * from t1; read_buffer_size
select * from t1 order by 字段名;sort_buffer_size order排队
select * from t1 group by 字段名;read_rnd_buffer_size
name ----> index
select * from t1 where name=“jim”; key_buffer-size ；
当 key_reads /key_read_requests 较低时，可适当加大此缓存值
show variabled like “read_%_size” ; 此缓存值影响SQL查询的响应速度。
open_tables /table_open_cache 理想比率 <= 95%

与查询缓存相关参数的设置
show variables like “%cache%”;
show variables like “query_cache%”; 查看缓存大小

query_cache_wlock_invalidate | OFF 关
当对myisam存储引擎的表执行查询时，若检查到有对表做写de sql操作,不从查询缓存里查询数据返回给客户端，而是等写操作完成后，重新查询数据返回给客户端。
pc1 select name from t1 where name=“bob”;
cache —> name=bob
pc2 select name from t1 where name=“bob”;
mysqld-> name= bob;
pc3 update t1 set name=“jack” wehre name=“bob”;

查看查询缓存的统计信息：
show global status like “qcache%”; 查询当前的查询缓存统计
Qcache_hits 10 记录在查询缓存里查询到数据的次数
Qcache_inserts 100 记录在查询缓存里查找数据的次数
Qcache_lowmem_prunes 清理查询缓存空间的次数（越少越好）
Qcache_hits/Qcache_inserts= 比值越大越好！

3 修改服务运行时的参数：
3.1 命令行设置，临时生效。
mysql> set [global] 变量名=值；
3.2在配置文件里设置永久生效:
vim /etc/my.cnf
[mysqld]
变量名=值

4、程序编写sql查询语句太复杂导致，数据库服务器处理速度慢。
开启数据库服务器的慢查询日志，记录超过指定时间显示查询结果的sql命令。

4.1 mysql数据库服务日志类型：
错误日志 ——默认开启 ——记录服务在启动和运行过程中产生的错误信息——log-error[=name]
log-error=/var/log/mysqld.log

binlog日志 又被称作二进制日志：

慢查询日志： 记录耗时较长或不使用索引的查询操作
——slow-query-log 启用慢查询
——slow-query-log-file= 指定慢查询日志文件
——long-query-time= 超时时间（默认10秒）
——long-queries-not-using-indexes 记录未使用索引的查询

查看慢查询日志的工具：使用mysqldumpslow 工具
例子：# vim /etc/my.cnf
[mysqld] 添加这三行
slow-query-log
long-query-time=2
log-queries-not-using-indexes
]# systemctl restart mysqld
]# cat /var/lib/mysql/pc55-slow.log
测试： mysql> select 4);
]# cat /var/lib/mysql/pc55-slow.log 查看日志内容
]# mysqldumpslow /var/lib/mysql/pc55-slow.log 统计日志文件记录信息
]# mysqldumpslow /var/lib/mysql/pc55-slow.log > /tmp/sql.txt 另存日志文件用来分析

查询日志： 记录客户端连接和查询操作。
——general-log 启用查询
—— general-log-file= 查询日志文件
例子：]# vim /etc/my.cnf
general-log
]# systemctl restart mysqld
]# cd /var/lib/mysql
]# tail -f pc55.log （ -f 是动态查看日志）
]# cat /var/lib/mysql/pc55.log 查看所有日志信息

5、网络架构有问题（有数据传输瓶颈）

调优思路总结：
升级硬件：CPU 内存 硬盘
加大网络带宽： 付费加大带宽
调整mysql服务运行参数：并发连接数、连接超时时间、重复使用的线程数。。。。。
调整与查询相关的参数： 查询缓存、索引缓存。。。
启用慢查询日志： slow-query-log
网络架构不合理：调整网路架构

常用的调优方法：
1、选择合适的存储引擎：InnoDB
除非该数据表仅仅用来读或检索用Myisam
1.1 将现在的MYISAMs数据库转换成InnoDB
1.2 为每个表分别创建InnoDB file
innodb_file_per_table=1

2、保证从内存读取数据，将数据保存在内存中