mysql MHA和调优
MHA
原理
MHA是一套mysql高可用方案，在MySQL故障切换过程中，MHA能做到在0~30秒之内自动完成数据库的故障切换操作，并且 在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用。 MHA里有 两个角色一个是MHA Node（数据节点）另一个是MHA Manager（管理节点）。 MHA Manager可以单独部署 在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave节点上。MHA Node运行在每台 MySQL服务器上，MHA Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新 数据的slave提升为新的master，然后将所有其他的slave重新指向新的master。整个故障转移过程对应用程序完 全透明。
工作流程
- 把宕机的master二进制日志保存下来
- 找到binlog位置点最新的slave
- 用这个slave的relay log来修复其他的slave
- 把宕机的master上保存下的二进制日志恢复到其他最新位置点的slave上
- 把含有最新位置点的slave提拔为新的master
- 把其他slave指向新的master
MHA软件的包括manager和node两个工具包
manager
Manager工具包主要包括以下几个工具:
masterha_check_ssh 检查MHA的SSH配置状况
masterha_check_repl 检查MySQL复制状况
masterha_manger 启动MHA
masterha_check_status 检测当前MHA运行状态
masterha_master_monitor 检测master是否宕机
masterha_master_switch 控制故障转移（自动或者手动）
masterha_conf_host 添加或删除配置的server信息node
save_binary_logs 保存和复制master的二进制日志
apply_diff_relay_logs 识别差异的中继日志事件并将其差异的事件应用于其他的slave
purge_relay_logs 清除中继日志(不会阻塞SQL线程)配置
现准备四台机器，一台server做manager,21为主，22，23为从，其中22为候选，在21宕机的时候成为新的master
mha需要通过ssh来交互，所以要先创建ssh免密登录环境
manager和三台机子，三台机子之间互相
ssh-keygen
ssh-copy-id 192.168.226.149
ssh-copy-id 192.168.226.152
ssh-copy-id 192.168.226.153
在21，22，23上面先搭建一主两从
vim /etc/yum.repos.d/mariadb.repo
[mariadb]
name=MariaDB
baseurl=http://mirrors.ustc.edu.cn/mariadb/yum/10.2/centos7-amd64/
gpgkey=http://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1
yum install -y mariadb-server.x86_64
yum -y install mha4mysql-node-0.56-0.el6.noarch.rpm
systemctl start mysql
mysql_secure_installation #密码此处设置为mariadb123456
mysql -uroot -pmariadb123456
#在21，22上安装插件，开启半同步复制
install plugin rpl_semi_sync_master soname 'semisync_master.so';
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
set global rpl_semi_sync_master_enabled = 1;
set global rpl_semi_sync_slave_enabled = 1;
grant replication slave on *.* to 'repluser'@'192.168.226.%' identified by 'repluser123456';
flush privileges;
在23上只开启同步
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
set global rpl_semi_sync_slave_enabled = 1;修改三台机器配置
# MariaDB-21配置文件
cat /etc/my.cnf.d/server.cnf
[mysqld]
skip_name_resolve = ON
innodb_file_per_table = ON
server-id = 21
log-bin = master-bin
relay_log_purge = 0
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 3000
rpl_semi_sync_slave_enabled = 1
# MariaDB-22配置文件
cat /etc/my.cnf.d/server.cnf
[mysqld]
skip_name_resolve = ON
innodb_file_per_table = ON
server-id = 22
log-bin = master-bin
relay_log_purge = 0
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 3000
rpl_semi_sync_slave_enabled = 1
# MariaDB-23配置文件
cat /etc/my.cnf.d/server.cnf
[mysqld]
skip_name_resolve = ON
innodb_file_per_table = ON
server-id = 23
log-bin = master-bin
relay_log_purge = 0
rpl_semi_sync_slave_enabled = 1重启服务，加载配置文件,配置主从同步
[root@21 ~]# systemctl restart mariadb.service
[root@21 ~]# mysql -uroot -pmariadb123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.2.43-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show master status\G;
*************************** 1. row ***************************
File: master-bin.000001
Position: 329
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
ERROR: No query specified
#在22，23上配置主从同步
MariaDB [(none)]> show master status\G;
*************************** 1. row ***************************
File: master-bin.000001
Position: 329
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
change master to master_host='192.168.226.149',master_user='repluser',master_password='repluser123456',master_log_file='master-bin.000001',master_log_pos=329;
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.226.149
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 329
Relay_Log_File: server5-relay-bin.000002
Relay_Log_Pos: 556
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
在21上授权manager用户管理mha各节点，两台slave会同步这个用户
MariaDB [(none)]> grant all on *.* to 'mhauser'@'192.168.226.%' identified by 'mhauser123456';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
配置mha-manager
yum install -y epel-release.noarch
yum -y install mha4mysql-manager-0.56-0.el6.noarch.rpm \
mha4mysql-node-0.56-0.el6.noarch.rpm
mkdir /etc/mha
cat /etc/mha/app1.conf
[server default]
user=mhauser
password=mhauser123456
ssh_user=root
master_binlog_dir=/var/lib/mysql
remote_workdir=/data/log/masterha
manager_workdir=/usr/local/mha
manager_log=/usr/local/mha/manager.log
ping_interval=1
repl_user=repluser
repl_password=repluser123456
[server1]
hostname=192.168.226.149
candidate_master=1
[server2]
hostname=192.168.226.152
candidate_master=1
[server3]
hostname=192.168.226.153
no_master=1
# ssh检测,授权检测,启动MHA
chmod +x /etc/mha/app1.conf
mkdir /usr/local/mha
masterha_check_ssh --conf=/etc/mha/app1.conf
masterha_check_repl --conf=/etc/mha/app1.conf
masterha_manager --conf=/etc/mha/app1.conf 2>&1 &
masterha_check_status --conf=/etc/mha/app1.conf
app1 (pid:17140) is running(0:PING_OK), master:192.168.226.149
验证
21创库
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
MariaDB [(none)]> create database db1;
Query OK, 1 row affected (0.00 sec)
22同步
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
22创库
MariaDB [(none)]> create database db2;
Query OK, 1 row affected (3.01 sec)
23不同步
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
21关机
[root@21 ~]# mysqladmin -uroot -pmariadb123456 "shutdown"
22建库
MariaDB [(none)]> create database db3;
Query OK, 1 row affected (0.00 sec)
23同步，可见22已变成新的主
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| db3 |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)
切换复原
先让21指向22
MariaDB [(none)]> change master to master_host='192.168.226.152',master_user='repluser',master_password='repluser123456',master_log_file='master-bin.000001',master_log_pos=583;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> start slave;
再在mha拉起
masterha_master_switch --conf=/etc/mha/app1.conf --master_state=alive --new_master_host=10.0.0.21 --new_master_port=3306 --orig_master_is_new_slave
再在mha手动切换
MySQL调优
- 采用合适的存储引擎
- 高可用框架
- 创建阶段选择更合适的变量种类，如使用日期时间类型而不是字符串来保存时间，用枚举来代替字符串
- 适当把一些大而不常访问的字段拆分到别的表里面
- 开启缓存
- 建立合适的索引
- 优化查询语句，如count(*)-->count(某一列)，关联查询访问建有索引的列
- 避免冗余和重复的索引，比如有一个索引(A,B)，再创建索引(A)就是冗余索引
- 建立合适的视图，保障数据安全性，提高查询效率
评论已关闭