mysql读写分离与调优

mysql 读写分离
原理 ： 多台mysql 服务器
分别提供读、写服务、均衡流量
过主从复制保持数据一致
由mysql 代理面向客户端
收到sql 写请求时，交给服务器A处理
收到sql 读请求时，交给服务器B处理
具体区分策略由服务器

构建读写分离
1.搭建mysql 主从复制
其中slave 为只读
2.添加一台mysql 代理服务器
部署/启用 maxscale
3.客户端通过代理主机访问mysql 数据库
访问代理服务器

部署maxscale 服务
maxscale 代理软件 （由mariadb 开发）
rpm -ivh maxscale-2.1.2-1.rhel.7.x86
修改配置 /etc/maxscale.cnf 前备份先 cp /etc/maxscale.cnf /root
[maxscale]
threads=1 （线程个数 与cpu核数挂钩）
threads=auto
[server1] 定义数据库服务器主机名
type=server
address=192.168.4.53 master主机ip主机 (与实际情况改)
port=3306
protocol=mysqlbackend

[server2] 定义数据库服务器主机名
type=server
address=192.168.4.54 slave主机ip主机
port=3306
protocol=mysqlbackend

[MySQL Monitor] 指定监控的数据库节点
36 type=monitor
37 module=mysqlmon
38 servers=server1, server2 (主从主机名)
39 user=haha --------------监视拥户
40 passwd=123456 （注数据密码要求和密码要求原则一致）
41 monitor_interval=10000
#[Read-Only Service] (注释)
53 #type=service
54 #router=readconnroute
55 #servers=server1
56 #user=myuser
57 #passwd=mypwd
58 #router_options=slave

[Read-Write Service] 定义读写分离服务
64 type=service
65 router=readwritesplit
66 servers=server1, server2
67 user=myuser ----------------------路由用户（用户连数据库）
68 passwd=123456
69 max_slave_connections=100%
[MaxAdmin Service] （定义管理服务）
76 type=service
77 router=cli
#[Read-Only Listener] （要注释） 不定以只读服务
86 #type=listener
87 #service=Read-Only Service
88 #protocol=MySQLClient
89 #port=4008
[Read-Write Listener] 读写分离服务端口号
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4006

[MaxAdmin Listener] 定义管理服务器使用端口号
98 type=listener
99 service=MaxAdmin Service
100 protocol=maxscaled
101 socket=default
102 port=4016

在主从数据库服务器创建授权用户
监控用户授权
grant replication slave,replication client on . to haha@"%" identified by “123456”;
路由用户授权
grant select on mysql.* to myuser@"%" identified by “123456”;
查看
select user from mysql.user
在代理服务器安装
yum -y install mariadb
测试
mysql -h192.168.4.54 -uhaha -p123456
mysql -h192.168.4.53 -uhaha -p123456
mysql -h192.168.4.53 -umyuser -p123456
mysql -h192.168.4.54 -umyuser -p123456
show grants （查看权限）

maxscale -f /etc/maxscale.cnf （启动maxscale）
ss -nutlp | grep :4016 查看管理服务端口
ss -nutlp | grep :4006 读写分离服务端口号
ss -nutlp | grep maxscale （查看进程）
pkill -9 maxscale (进程号) （杀死进程）

在maxscale 本机连接管理端口
maxadmin -uadmin -pmariadb -P端口
maxadmin -uadmin -pmariadb -P4016
list servers
Servers.
-------------------±----------------±------±------------±-------------------
Server | Address | Port | Connections | Status
-------------------±----------------±------±------------±-------------------
server1 | 192.168.4.53 | 3306 | 0 | Master, Running
server2 | 192.168.4.54 | 3306 | 0 | Slave, Running
-------------------±----------------±------±------------±-------------------

用客户端访问
mysql -h192.168.4.56（代理服务器） -u (用户) -p密码 -P4006（读写端口）

mysql 多事例 用到的包 mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
配置多事例说明
1.每个实例要有独立的数据库目录和监听端口号
2.每个实例要有独立的实例名称和独立的sock文件
system stop mysqld
systemctl disable mysqld (开机不启动)
tar -xzvf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.20-linux-glibc2.12-x86_64 /usr/local/mysql
mv /etc/my.cnf /root (将原来的mysql文件移走)
vim /etc/my.cnf (新的文件)
[mysqld_multi] 启动多事例
mysqld = /usr/local/mysql/bin/mysqld_safe 指定进程文件路径
mysqladmin = /usr/local/mysql/bin/mysqladmin 指定管理命令路径
user = root 指定进程用户
[mysqld1] 实例进程名称（1）
datadir=/data1 数据库目录，要手动创建
port=3307 端口号
pid-file=/data1/mysqld3307.pid 进程pid号文件位置
log-error=/data1/mysqld3307.err 错误日志文件
socket=/data1/mysqld3307.sock 指定sock文件的路径和名称
[mysqld2]
datadir=/data2
port=3308
pid-file=/data2/mysqld3308.pid
log-error=/data2/mysqld3308.err
socket=/data2/mysqld3308.sock
mkdir /data1
mkdir /data2

/usr/local/mysql/bin/mysqld_multi start 1 (启动多事例 1)
/usr/local/mysql/bin/mysql -uroot -p’/k#8C=h*Fh&!’ -S /data1/mysqld3307.sock (进库)
alter user user（） identified by “123456” (修改密码)
/usr/local/mysql/bin/mysqld_multi --user=root --password=密码 stop 1 (停止 多事例 1)

yum -y install perl-* （51—56）

数据库优化
提高mysql 系统的性能、响应速度
替换有问题的硬件 （cpu/磁盘/内存等）
服务程序的运行参数调整
对sql 查询进行优化

并发及连接控制

uptime （查看cpu）
09:10:17 up 3 days （持续多久）, 1:08, 2 users（几台连接）, load average: 0.00（一分钟）, 0.01（5分钟平均负载）, 0.05（15分钟）
free -m （看内存）
total used free shared buff/cache available
Mem: 992 155 464 13 371 641
Swap（交换分区）: 2047 0 2047
当交换分区使用过大就要换内存条
top （动态查看cpu）
Tasks: 114 total（总进程数）, 2 running, 112 sleeping, 0 stopped, 0 zombie（僵尸进程）
%Cpu(s): 0.3 us, 0.0 sy, 0.0 ni, 99.7 id, 0.0 wa（wait ，数越大等待磁盘处理慢）, 0.0 hi, 0.0 si, 0.0 st
65535 （pid 最大值）
show variables like “%conn%”;
±----------------------------------------------±----------------+
| Variable_name | Value |
±----------------------------------------------±----------------+
| character_set_connection | utf8 |
| collation_connection | utf8_general_ci |
| connect_timeout | 10 |
| disconnect_on_expired_password | ON |
| init_connect | |
| max_connect_errors | 100 |
| max_connections | 151 |
| max_user_connections | 0 |
| performance_schema_session_connect_attrs_size | 512 |
±----------------------------------------------±----------------+
set global max_connections = 200; (修改允许的最大并发连接数)
写到配置文件 ： max_connections = 200
全局变量 ； show global status like “%conn%”;
Max_used_connections | 1 (曾经最大连接)
并发 Max_used_connections/max_connections=0.85 x 100% = 85% (15% 空闲备用并发量)
show global status like “%time%”;
show global status like “%lock%”;
flush status （刷新运行状态）
show variables like “%timeout%”; (查看连接超时)
解决死锁 ： innodb_lock_wait_timeout | 50 （50秒自动释放写锁）

缓存参数控制
缓存区、线程数量、开发数量

show variables like “%key_buffer%”;
±----------------±--------+
| Variable_name | Value |
±----------------±--------+
| key_buffer_size | 8388608 |
±----------------±--------+
show variables like “%buffer%”;
show variables like “thread_cache_size”;
vim /etc/my.cn
general-log (记录客户机连接和查询操作) ----tail -f XX.log (动态查看)
slow-query-log (记录耗时较长或不使用索引的查询)
[root@m5 mysql]# ls
auto.cnf client-cert.pem ib_buffer_pool ib_logfile1 m5-slow.log mysql.sock.lock public_key.pem sys
ca-key.pem client-key.pem ibdata1 ibtmp1 mysql performance_schema server-cert.pem
ca.pem db1 ib_logfile0 m5.log mysql.sock private_key.pem server-key.pem
ls /var/lib/mysql/xxx.slow.log
tail -f m5-slow.log
/usr/sbin/mysqld, Version: 5.7.17-log (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument

Time: 2018-12-20T03:37:38.231397Z

User@Host: root[root] @ [192.168.4.20] Id: 7

Query_time: 11.000852 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0

SET timestamp=1545277058;
select sleep(11);

宁开一个主机测试 ： select sleep(11);
mysqldump slow m5-slow.log

Time: N-N-20T03:N:N.231397Z

User@Host: root[root] @ [N.N.N.N] Id: N

Query_time: N.N Lock_time: N.N Rows_sent: N Rows_examined: N

SET timestamp=N;
select sleep(N)

优化sql 查询

调整服务配置
show variables like “query_cache%”; (查看缓存大小)
| query_cache_size | 1048576/1024 | ----1M（默认）
| query_cache_type | OFF | （索引缓存未启动）

show global status like “qcache%”; (查看当前查询缓存统计)
| Qcache_hits | 0 | --------- 存 的当访问数据库，先查缓存，如果有就加1（在查询缓存中找到的）
| Qcache_inserts | 0 | ---------只要查询收到一次，就加一（查询总量）