mysql调优和数据库高可用集群

最新推荐文章于 2023-06-24 16:47:15 发布

秋yun

最新推荐文章于 2023-06-24 16:47:15 发布

阅读量302

点赞数 1

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qgw5205220/article/details/108870869

版权

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 install

masterha_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  mysqld

56管理主机的过程：
管理服务命令自动停止，删除宕机的主库在配置文件中的数据，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.在监控服务器上运行管理服务

秋yun

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
mysql调优和数据库高可用集群

mysql调优和数据库高可用集群性能调优1.升级硬件（cpu，内存，存储）2.优化数据库服务运行参数3.让程序员优化访问数据的sql命令4.网络带宽5.优化服务架构（是否由数据传输瓶颈）优化数据库服务运行参数:show variables ；显示所有变量show variables like “%%”；模糊匹配set global 变量=值临时修改变量，立即生效永久有效以 ”变量名=值“ 的方式写入/etc/my.cnf中，重起服务show
复制链接

扫一扫