mysql调优和数据库高可用集群
性能调优
1.升级硬件（cpu，内存，存储）
2.优化数据库服务运行参数
3.让程序员优化访问数据的sql命令
4.网络带宽
5.优化服务架构（是否由数据传输瓶颈）
优化数据库服务运行参数:
show variables ； 显示所有变量
show variables like “%%”； 模糊匹配
set global 变量=值 临时修改变量，立即生效
永久有效以 ”变量名=值“ 的方式写入/etc/my.cnf中，重起服务
show status; 所有状态变量
show status like "%%";查询优化：
## 连接数，连接超时数：
max_connections 允许最大并发连接数
max_used_connections 数据库最大连接数（两个比值最好不超过0.85）
connect_timeout 等待连接超时，默认10秒
wait_timeout 不活动超时时间秒数，默认28800s## 缓存参数控制：
key_buffer_size 用于myisam引擎的关键索引缓存大小
sort_buffer_size 为每个要排序的线程分配此大小的缓存空间（影响order 和 group 的速度）
read_buffer_size 为顺序读取表记录保留的缓存大小（普通字段的读取方式，顺序读）
thread_cache_size 允许保存在缓存中被重用的线程数量（命令线程结束后，进行缓存，占用内存，提高执行速度）
table_open_cache 为所有线程缓存的打开的表的数量（从硬盘调用表到内存时，进行缓存。）查询优化：
优化服务查询缓存
memchache+mysql 缓存服务器+数据库服务器
mysql> show variables like "query_cache%";
query_cache_size 查询缓存大小
query_cache_type 查询缓存是否开启
mysql> show global status like "qcache%";
Qcache_hits 查询请求在缓存中执行的次数
Qcache_inserts 查询请求执行的次数日志类型启用
log-error=名字 错误日志
general-log 启用查询日志(比较占用资源)
general-log-file= 定义命名和存储位置
slow-query-log 记录耗时较长或不使用索引的查询操作 （默认超时时间为10s ，默认日志名为主机名-slow.log）
slow-query-log-file= 定义目录名称 ]# mysqldumpslow /var/lib/mysql/*-slow.log 用于统计查询较慢的命令，进行优化。
long-query-time= 5 查询时间超过5s的进行记录mysql集群
部署mysql集群，使用“MHA软件+mysql主从同步”实现
1.什么是集群？？
多台服务器提供相同的服务
2.集群分类？
高可用（LB），负载均衡（HA），高计算集群（HPC）。
3.配置集群？
安装软件实现：LVS haproxy nginx keppalived
MHA介绍：
实现数据库自动的故障切换，时间在30S以内
故障切换过程中保证数据的一致性，到达高可用
4.部署Mysql高可用集群
环境要求：
slave服务器2：192.168.4.55 (纯备份) slave服务器1：192.168.4.54（纯备份）
master主节点服务器：192.168.4.51 （配置vip） < ———————————— MHA_manager服务器：192.168.4.56
备用1节点：192.168.4.52 （备份：备选master） 备用2节点：192.168.4.53（备份：备选master）
5台独立数据库服务器
yum -y install perl-*
56管理主机要无密码ssh 51-55
mhs-soft-*目录下的包要在6服务器上都有
]#yum -y install perl-* 安装perl相关软件包184个
cd mha-soft-student/
ls
master_ip_failover
mha4mysql-manager-0.56.tar.gz
mha4mysql-node-0.56-0.el6.noarch.rpm
perl-Config-Tiny-2.14-7.el7.noarch.rpm
perl-Email-Date-Format-1.002-15.el7.noarch.rpm
perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm
perl-Mail-Sender-0.8.23-1.el7.noarch.rpm
perl-Mail-Sendmail-0.79-21.el7.art.noarch.rpm
perl-MIME-Lite-3.030-1.el7.noarch.rpm
perl-MIME-Types-1.38-2.el7.noarch.rpm
perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm
yum -y install perl-* 安装yum中没有的8个perl包
rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm 数据库服务器连接程序56中心节点服务器部署
]# tar -xvf mha4mysql-manager-0.56.tar.gz 再安装管理程序
]# cd mha4mysql-manager-0.56/
]# perl Makefile.PL
*** Module::AutoInstall version 1.03
*** Checking for Perl dependencies...
[Core Features]
- DBI ...loaded. (1.627)
- DBD::mysql ...loaded. (4.023)
- Time::HiRes ...loaded. (1.9725)
- Config::Tiny ...loaded. (2.14)
- Log::Dispatch ...loaded. (2.41)
- Parallel::ForkManager ...loaded. (1.18)
- MHA::NodeConst ...loaded. (0.56)
*** Module::AutoInstall configuration finished.
Writing Makefile for mha4mysql::manager
Writing MYMETA.yml and MYMETA.json
make && make installmasterha_check_ssh | 检查mha的ssh配置情况 |
masterha_check_repl | 检查mysql复制情况 |
masterha_manager | 启动mha |
masterha_check_status | 检查mha运行状态 |
masterha_master_monitor | 检查master是否宕机 |
配置集群：
1.配置mysql主从同步：
51主服务器
52，53备用主库
54，55纯从库
192.168.4.51:
192.168.4.52:
192.168.4.53:
]# vim /etc/my.cnf
server_id=52 指定数据库 id
log_bin=master52 开启binlog且设置日志名
plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" 装载master和slave模块
rpl_semi_sync_master_enabled=1 开启master半同步功能
rpl_semi_sync_slave_enabled=1 开启slave半同步功能
relay_log_purge=off 关闭中继日志自动删除
]# systemctl restart mysqld
54，55：
]# vim /etc/my.cnf
server_id=54
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1
relay_log_purge=off
51：
mysql> grant replication slave on *.* to plj@"%" identified by "123qqq...A";
mysql> show master status;
52，53,54,55：
mysql> change master to master_host="192.168.4.51", master_user="plj", master_password="123qqq...A", master_log_file="master51.000003", master_log_pos=154;
mysql> start slave;
mysql> show slave status\G;2.配置管理主机56.
编写配置文件
创建故障切换脚本
把VIP地址部署在当前主库上
用户授权
]# mkdir /etc/mha_manager
]# cp /root/mha-soft-student/mha4mysql-manager-0.56/samples/conf/app1.cnf /etc/mha_manager/app1.cnf
将模版配置文件cp到创建目录
]# vim /etc/mha_manager/app1.cnf
[server default]
manager_workdir=/etc/mha_manager 工作目录(指定在自己创建的目录里)
manager_log=/etc/mha_manager/manager.log 工作日志
master_ip_failover_script=/etc/mha_manager/master_ip_failover 自动监控的主机故障时切换脚本
ssh_user=root ssh远程连接用户
ssh_port=22 端口号
repl_user=plj 主库给的主从同步用户（51，52，53上都有）
repl_password=123qqq...A 密码
user=root 56使用的监控51-55的授权用户（在51主库上取创建就会同步到所有从库，保存后去创建）
password=123qqq...A 密码
[server1]
hostname=192.168.4.51
port=3306 默认可以不写
candidate_master=1 竞选主库
[server2]
hostname=192.168.4.52
candidate_master=1
[server3]
hostname=192.168.4.53
candidate_master=1
[server4]
hostname=192.168.4.54
no_master=1 不竞选主库
[server5]
hostname=192.168.4.55
no_master=1]# cp /root/mha-soft-student/master_ip_failover /etc/mha_manager/
将(老师修改过的脚本，原来地址在：/root/mha-soft-student/mha4mysql-manager-0.56/samples/scripts/)自动切换脚本移动戴上配置文件指定的位置
]# vim +35 /etc/mha_manager/master_ip_failover 修改vip号
my $vip = '192.168.4.100/24'; # Virtual IP
]# chmod +x /etc/mha_manager/master_ip_failover 给脚本 执行权限]# ifconfig eth0:1 192.168.4.100/24 临时添加虚拟ip号（只能使用临时添加）
]# ifconfig eth0:1 查看添加配置授权用户：服务器同步数据的用户plj 和 56监控的用户 root
mysql> show grants for plj@"%";
mysql> grant replication slave on *.* to plj@"%" identified by "123qqq...A";
51——56:
mysql> grant all on *.* to root@"%" identified by "123qqq...A"; 主库创建，其他机同步创建测试验证：在56机上
]# masterha_check_ssh --conf=/etc/mha_manager/app1.cnf 远程关系测试
]# masterha_check_repl --conf=/etc/mha_manager/app1.cnf 主从和集群可用测试启动管理服务
]# masterha_manager --conf=/etc/mha_manager/app1.cnf 启动监视命令
--remove_dead_master_conf 主库宕机自动在配置文件app1.cnf中删除
--ignore_last_failover 忽略最后的故障切换（只要主库宕机，都要故障切换）
]# masterha_check_status --conf=/etc/mha_manager/app1.cnf 查看监视进程pid和主库真实ip信息
]# ls /etc/mha_manager/
app1.cnf 调用主配置文件
app1.master_status.health 记录主库信息
manager.log 监控日志
master_ip_failover 故障切换脚本测试mysql高可用集群
在主数据库上添加访问用户
mysql> create database db9;
mysql> grant select,insert,update on db9.* to yaya99@"%" identified by "123qqq...A";在客户机50上登陆添加的vip地址访问
]# mysql -h192.168.4.100 -uyaya99 -p123qqq...A
mysql> use db9;
mysql> insert into t1 values (200); 可检验5台主从集群都有数据停止主库51的数据库服务，访问不会断开连接。
]# systemctl stop mysqld56管理主机的过程：
管理服务命令自动停止，删除宕机的主库在配置文件中的数据，51释放vip。
主库然后调用自动切换脚本，用算法选取出新的主库服务器赋予vip作为其他服务器主库。
]# vim /etc/mha_manager/app1.cnf 发现server1选项已经被删除
app1.master_status.health ——> app1.failover.complete 主库记录文件变化
一定要重新起动监控进程，重新生成app1.master_status.health 文件 当52再坏，53会变为主库把宕机的51恢复到集群中
1.重起51的数据库服务
2.把51设置成当前主服务器的从库
3.把51添加到 aap1.cnf文件里
4.在监控服务器上运行管理服务
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
