MHA基础概念

MHA

MHA（MasterHigh Availability）是一套优秀的MySQL高可用环境下故障切换和主从复制的软件。

MHA 的出现就是解决MySQL 单点的问题。

MySQL故障切换过程中，MHA能做到0-30秒内自动完成故障切换操作。

MHA能在故障切换的过程中最大程度上保证数据的一致性，以达到真正意义上的高可用。

MHA的组成

MHA Node（数据节点）

MHA Node 运行在每台 MySQL 服务器上。

MHA Manager（管理节点）

MHA Manager 可以单独部署在一台独立的机器上，管理多个 master-slave 集群；也可以部署在一台 slave 节点上。

MHA Manager 会定时探测集群中的 master 节点。当 master 出现故障时，它可以自动将最新数据的 slave 提升为新的 master，然后将所有其他的 slave 重新指向新的 master。整个故障转移过程对应用程序完全透明。

MHA的特点

自动故障切换过程中，MHA试图从宕机的主服务器上保存二进制日志，最大程度的保证数据不丢失
使用半同步复制，可以大大降低数据丢失的风险，如果只有一个slave已经收到了最新的二进制日志，MHA可以将最新的二进制日志应用于其他所有的slave服务器上，因此可以保证所有节点的数据一致性
目前MHA支持一主多从架构，最少三台服务，即一主两从

搭建MySQL + MHA

MySQL安装（3台）

下载

wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.28-.el7.x86_64.rpm-bundle.tar

解压

tar xvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar

安装

要移除CentOS自带的mariadb-libs，不然会提示冲突

rpm -qa|grep mariadb
rpm -e mariadb-libs-5.5.41-2.el7_0.x86_64 --nodeps

由于MySQL的server服务依赖了common、libs、client，所以需要按照以下顺序依次安装。

RPM是Red Hat公司随Redhat Linux推出的一个软件包管理器，通过它能够更加方便地实现软件的安装。rpm常用的命令有以下几个:

-i, --install 安装软件包
-v, --verbose 可视化，提供更多的详细信息的输出
-h, --hash 显示安装进度
-U, --upgrade=<packagefile>+ 升级软件包
-e, --erase=<package>+ 卸载软件包
--nodeps 不验证软件包的依赖

组合可得到几个常用命令：

安装软件：rpm -ivh rpm包名
升级软件：rpm -Uvh rpm包名
卸载软件：rpm -e rpm包名
查看某个包是否被安装 rpm -qa | grep 软件名称

下面就利用安装命令来安装MySQL：

rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-devel-5.7.28-1.el7.x86_64.rpm

启动

初始化用户

mysqld --initialize --user=mysql

查看初始密码

cat /var/log/mysqld.log | grep password

修改默认配置文件，支持远程连接

vim /etc/my.cnf

bind-address=0.0.0.0

启动MySQL服务

systemctl start mysqld

配置为开机启动

systemctl enable mysqld

接下来登录MySQL，修改默认密码并支持远程连接

mysql -uroot -p
xxxxxx输入初始密码
SET PASSWORD = PASSWORD('123456');
 # 所有用户都能连接服务器
mysql> grant all privileges on *.* to 'root'@'%' identified by '123456' mysql> with grant option;
flush privileges;

关闭防火墙

不同的MySQL直接要互相访问，需要关闭Linux的防火墙，否则就要在配置/etc/sysconfig/iptables中增加规则。

systemctl stop firewalld

MySQL主从配置

Master节点

使用vi /etc/my.cnf命令修改Master配置文件

# bin_log配置
log_bin=mysql-bin	# 开启二进制binlog日志
server-id=1
sync-binlog=1		# 表示每次提交事务都要发生 fsync 磁盘同步指令

# 表示不记录自带的数据库的二进制日志
binlog-ignore-db=information_schema	
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
# relay_log配置
relay_log=mysql-relay-bin
log_slave_updates=1	# 控制master是否把所有的操作写入到binary log，
relay_log_purge=0	#旧relay logs会在SQL线程执行完毕后不会自动删除

重启服务

systemctl restart mysqld

主库给从库授权

登录MySQL，在MySQL命令行执行如下命令：

# 授权从服务器有复制功能
mysql> grant replication slave on *.* to root@'%' identified by '密码';
mysql> grant all privileges on *.* to root@'%' identified by '密码';
mysql> flush privileges;
//查看主库状态信息，例如master_log_file='mysql-in.000007',master_log_pos=154
mysql> show master status;

Slave节点

修改Slave的MySQL配置文件my.cnf，两台Slave的server-id分别设置为2和3

# bin_log配置
log_bin=mysql-bin
# 服务器ID,从库1是2,从库2是3
server-id=2
sync-binlog=1
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
#relay_log配置
relay_log=mysql-relay-bin
log_slave_updates=1
relay_log_purge=0
read_only=1		# 设置为 只读模式

重启服务

systemctl restart mysqld

开启同步

登录MySQL，在Slave节点的MySQL命令行执行同步操作，例如下面命令（注意参数与上面show master status操作显示的参数一致）：

mysql> change master to
 master_host='192.168.31.199',master_port=3306,master_user='root',master_password='123456',master_log_file='mysql-bin.000007',master_log_pos=154;

mysql> start slave; // 开启同步

mysql -uroot -p123123 -e "show slave status\G" | awk '/Running:/{print}'

             Slave_IO_Running: Yes	# IO线程是否启动并连接服务器
            Slave_SQL_Running: Yes	# sql线程是否启动

配置半同步复制

Master节点

登录MySQL，在MySQL命令行执行下面命令安装插件

mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
mysql> show variables like '%semi%';

使用vi /etc/my.cnf，修改MySQL配置文件

# 自动开启半同步复制
rpl_semi_sync_master_enabled=ON		#表示主上是否开启半同步复制功能，
rpl_semi_sync_master_timeout=1000	# 控制主库等待备库响应的消息时间
#如果超过该阀值1000ms，则认为备库存在问题，将半同步复制修改为异步复制，避免主库的执行事务长时间等待。

重启MySQL服务

systemctl restart mysqld

Slave节点

两台Slave节点都执行以下步骤。
登录MySQL，在MySQL命令行执行下面命令安装插件

install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

使用vi /etc/my.cnf，修改MySQL配置文件

# 自动开启半同步复制
rpl_semi_sync_slave_enabled=ON

重启服务

systemctl restart mysqld

测试半同步状态

首先通过MySQL命令行检查参数的方式，查看半同步是否开启。

mysql> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.00 sec)

然后通过MySQL日志再次确认。

cat /var/log/mysqld.log

可以看到日志中已经启动半同步信息，例如：

Start semi-sync binlog_dump to slave (server_id: 2), pos(mysql-bin.000005, 154)

MySQL并行复制

Master节点

使用vi /etc/my.cnf，修改MySQL配置文件

binlog_group_commit_sync_delay=1000	# 等待1000ms时间后才进行组提交
binlog_group_commit_sync_no_delay_count=100
# 控制MySQL写binlog时使用组提交的机制.MySQL会累计100个事务后再将它们一起写入binlog文件，这可以减少磁盘IO操作，提升写入性能。

重启服务

systemctl restart mysqld

Slave节点

使用vi /etc/my.cnf，修改MySQL配置文件

# 值：DATABASE;基于数据库的并发，每一个数据库都有一个线程去同步，如果只有一个数据库，那其它线程不工作
# 值：LOGICAL_CLOCK时，则可以一个数据库一个多线程同步
slave_parallel_type='LOGICAL_CLOCK'
slave_parallel_workers=8	# 启动8个线程用于同步
relay_log_info_repository='TABLE'
relay_log_recovery=1
#当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性。

重启服务

systemctl restart mysqld

查看中继日志设置，看是否生效

show variables like '%relay_log%';

MHA高可用搭建

四台机器ssh互通

在四台服务器上分别执行下面命令，生成公钥和私钥（注意：连续按换行回车采用默认值）

ssh-keygen -t rsa

在三台MySQL服务器分别执行下面命令，密码输入系统密码，将公钥拷到MHA Manager服务器上

ssh-copy-id 192.168.31.126

之后可以在MHA Manager服务器上检查下，看看.ssh/authorized_keys文件是否包含3个公钥

cat /root/.ssh/authorized_keys

执行下面命令，将MHA Manager的公钥添加到authorized_keys文件中（此时应该包含4个公钥）

cat /root/.ssh/id_rsa.pub >> /root/.ssh/authorized_keys

从MHA Manager服务器执行下面命令，向其他三台MySQL服务器分发公钥信息

scp /root/.ssh/authorized_keys root@192.168.31.199:/root/.ssh/authorized_keys
scp /root/.ssh/authorized_keys root@192.168.31.165:/root/.ssh/authorized_keys
scp /root/.ssh/authorized_keys root@192.168.31.142:/root/.ssh/authorized_keys

可以MHA Manager执行下面命令，检测下与三台MySQL是否实现ssh互通

ssh 192.168.31.199
exit
ssh 192.168.31.165
exit
ssh 192.168.31.142
exit

MHA下载安装

MHA下载

MySQL5.7对应的MHA版本是0.5.8，所以在GitHub上找到对应的rpm包进行下载，MHA manager和node的安装包需要分别下载：

https://github.com/yoshinorim/mha4mysql-manager/releases/tag/v0.58
https://github.com/yoshinorim/mha4mysql-node/releases/tag/v0.58

下载后，将Manager和Node的安装包分别上传到对应的服务器。（可使用WinSCP等工具）。也可以使用wget命令在linux系统直接下载获取，例如

wget https://github.com/yoshinorim/mha4mysql-
manager/releases/download/v0.58/mha4mysql-manager-0.58-
0.el7.centos.noarch.rpm

三台MySQL服务器需要安装node
MHA Manager服务器需要安装manager和node

MHA node安装

MHA的Node依赖于perl-DBD-MySQL，所以要先安装perl-DBD-MySQL

yum install perl-DBD-MySQL -y

wget https://github.com/yoshinorim/mha4mysql-
node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm

rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm

MHA manager安装

MHA的manager又依赖了perl-Config-Tiny、perl-Log-Dispatch、perl-Parallel-ForkManager，分别进行安装

wget http://dl.fedoraproject.org/pub/epel/epel-release-latest-.noarch.rpm

rpm -ivh epel-release-latest-7.noarch.rpm

yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager -y

wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm

rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm

wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58-.el7.centos.noarch.rpm

rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

MHA 配置文件

MHA Manager服务器需要为每个监控的 Master/Slave 集群提供一个专用的配置文件，而所有的Master/Slave 集群也可共享全局配置

初始化配置目录

# 目录说明
# /var/log                      (CentOS目录)
#        /mha                  (MHA监控根目录)
#            /app1             (MHA监控实例根目录)
#                 /manager.log (MHA监控实例日志文件)
mkdir -p /var/log/mha/app1
touch /var/log/mha/app1/manager.log

配置监控全局配置文件

vim /etc/masterha_default.cnf
[server default]
# 主库用户名，在master mysql的主库执行下列命令建一个新用户
# create user 'mha'@'%' identified by '123123';
# grant all on *.* to mha@'%' identified by '123123';
# flush privileges;
user=mha
password=123123
port=3306
#ssh登录账号
ssh_user=root
# 从库复制账号和密码
repl_user=root
repl_password=123456
port=3306
# ping次数
ping_interval=1
# 二次检查的主机
secondary_check_script=masterha_secondary_check -s 192.168.31.199 -s
 192.168.31.165 -s 192.168.31.142

配置监控实例配置文件

先使用 mkdir -p /etc/mha 命令创建目录，然后使用 vim /etc/mha/app1.cnf 命令编辑文件

[server default]
# MHA监控实例根目录
manager_workdir=/var/log/mha/app1
# MHA监控实例日志文件
manager_log=/var/log/mha/app1/manager.log
#[serverx] 服务器编号
# hostname 主机名
# candidate_master 可以做主库
# master_binlog_dir binlog日志文件目录
[server1]
hostname=192.168.31.199
candidate_master=1
master_binlog_dir="/var/lib/mysql"
[server2]
hostname=192.168.31.165
candidate_master=1
master_binlog_dir="/var/lib/mysql"
[server3]
hostname=192.168.31.142
candidate_master=1
master_binlog_dir="/var/lib/mysql"

MHA 配置检测

执行ssh通信检测

在MHA Manager服务器上执行：

masterha_check_ssh --conf=/etc/mha/app1.cnf

检测MySQL主从复制

在MHA Manager服务器上执行：

masterha_check_repl --conf=/etc/mha/app1.cnf

出现“MySQL Replication Health is OK.”证明MySQL复制集群没有问题。

MHA Manager启动

在MHA Manager服务器上执行：

nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

查看监控状态命令如下：

masterha_check_status --conf=/etc/mha/app1.cnf

查看监控日志命令如下：

tail -f /var/log/mha/app1/manager.log

测试MHA故障转移

模拟主节点崩溃

在MHA Manager服务器执行打开日志命令：

tail -200f /var/log/mha/app1/manager.log

关闭Master MySQL服务器服务，模拟主节点崩溃

systemctl stop mysqld

查看MHA日志，可以看到哪台slave切换成了master

show master status;

故障修复

重启宕机服务

systemctl restart mysqld

修复主从

现在主库服务器MySQL(192.168.200.27)查看二进制文件和同步点

# mysql -uroot -p123123 -e "show master status;"

+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000003 |      829 |              | information_schema,mysql,performance_schema,sys |                   |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)

在原master节点执行同步，二进制日志名和偏移量使用刚刚从节点，现变master获取到的

mysql> change master to 
    -> master_host='192.168.200.27',
    -> master_user='root',
    -> master_password='123123',
    -> master_port=3306,
    -> master_log_file='mysql-bin.000003',
    -> master_log_pos=829;
# 开启同步
mysql> start slave;

在 manager 节点上修改配置文件app1.cnf（再把这个记录添加进去，因为它检测掉失效时候会自动消失）

vim /etc/mha/app1.cnf

[server1]
candidate_master=1
hostname=192.168.200.26
master_binlog_dir="/var/lib/mysql"

在manager节点上启动MHA

masterha_stop --conf=/etc/mha/app1.cnf	#停止

# 启动
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

# 查看MHA状态，当前master是slave01 --> master
masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:3550) is running(0:PING_OK), master:192.168.200.27

一键安装MySQL

下载mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz并将其放入/opt目录下。执行脚本

#!/bin/bash
echo '==开始安装mysql=='
# 得到当前脚本所在绝对路径
DIR=/opt
useradd mysql -s /sbin/nologin -M
tar -zxvf $DIR/mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz -C $DIR/
mv $DIR/mysql-5.7.31-linux-glibc2.12-x86_64 /usr/local/mysql-5.7.30
ln -s /usr/local/mysql-5.7.30  /usr/local/mysql
rpm -e --nodeps mariadb-libs
cat >/etc/my.cnf<<EOF
[mysqld]
character-set-server=utf8mb4
basedir = /usr/local/mysql/
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
server_id = 1
port = 13306
log_error = /usr/local/mysql/data/dcs_mysql.err
lower_case_table_names = 1
max_allowed_packet=64M
log_bin_trust_function_creators = ON
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
wait_timeout= 180
interactive_timeout=  180

# 开启慢查询1为启用，0为禁用  
slow_query_log = 1
# 开启慢查询时间，此处为1秒，达到此值才记录数据
long_query_time = 3
# 检索行数达到此数值，才记录慢查询日志中
min_examined_row_limit = 100
# 设定每分钟记录到日志的未使用索引的语句数目，超过这个数目后只记录语句数量和花费的总时间
log_throttle_queries_not_using_indexes = 10
# 慢查询日志文件地址
slow_query_log_file = /usr/local/mysql/logs/mysql-slow.log
# 开启记录没有使用索引查询语句
log-queries-not-using-indexes = 1
#5.7版本新增时间戳所属时区参数，默认记录UTC时区的时间戳到慢查询日志，应修改为记录系统时区 
log_timestamps=system

[client]
default-character-set=utf8mb4
socket = /tmp/mysql.sock


[mysql]
socket = /tmp/mysql.sock
prompt = mysql>		# 定义mysql控制台名
EOF
yum install libaio-devel net-tools -y
# rpm -ivh /opt/libaio-devel-0.3.12-1.i386.rpm
mkdir -p /usr/local/mysql/data
chown -R mysql.mysql /usr/local/mysql/
/usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data
cat >/etc/systemd/system/mysqld.service<<EOF
[Unit]
Description=MySQL Server by dcs
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF
systemctl start mysqld
systemctl enable mysqld
netstat -lntup|grep mysql
ps -ef|grep mysql|grep -v grep
echo 'export PATH=/usr/local/mysql/bin:$PATH' >>/etc/profile
source  /etc/profile
mysqladmin -u root password '123123'
echo '==结束安装mysql=='

搭建主从架构：

systemctl stop firewalld && systemctl disable firewalld
setenforce 0

==========master主服务器====================
vim /etc/my.cnf
[mysqld]
server-id = 20	# 三台mysql服务器不一样即可
log_bin = master-bin	#开启二进制日志
log-slave-updates = true	#允许从服务器进行复制更新二进制文件

===========slave从节点=======================
vim /etc/my.cnf
server-id = 30  
#server-id = 40  mysql3则为40，三台服务器 server-id 不能一样
log_bin = master-bin
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index

===============================================


# 修改完配置文件后，重启服务并将mysql命令和mysql日志文件软链接到/usr/sbin，便于系统识别
systemctl restart mysqld
ln -s /usr/local/mysql/bin/mysql /usr/sbin/
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/

主库给从库授权：

登录MySQL，在MySQL命令行执行如下命令：

mysql> grant replication slave on *.* to root@'%' identified by '密码';
# 授权从服务器有复制功能
mysql> grant all privileges on *.* to root@'%' identified by '密码';
 # 所有用户都能连接服务器
mysql> flush privileges;
//查看主库状态信息，例如master_log_file='mysql-bin.000007',master_log_pos=154
mysql> show master status;

从库开启同步：

登录MySQL，在Slave节点的MySQL命令行执行同步操作，例如下面命令（注意参数与上面show master status操作显示的参数一致）：

change master to
 master_host='192.168.200.26',master_port=3306,master_user='root',master_password
='123456',master_log_file='mysql-bin.000007',master_log_pos=154;

start slave; // 开启同步

mysql -uroot -p123123 -e "show slave status\G" | awk '/Running:/{print}'

             Slave_IO_Running: Yes	# IO线程是否启动并连接服务器
            Slave_SQL_Running: Yes	# sql线程是否启动
    
mysql -p123123 -e "set global read_only=1;"		# 从节点设置为 只读模式

MySQL优化

show profiles

是MySQL提供可以用来分析当前会话中SQL语句执行的资源消耗情况。可以用于SQL的调优测量。默认情况下，参数处于关闭状态，并保存最近 15 次的运行结果

开启 profiles

查看是否开启

show variables like "%profiling%";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set (0.00 sec)

开启

set profiling = 1;

开始分析

先执行要分析的SQL语句
执行show profiles;会出现如下结果

mysql> show profiles;
+----------+------------+-----------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                                 |
+----------+------------+-----------------------------------------------------------------------------------------------------------------------+
|        5 | 1.03400375 | create database test1                                                                                                 |
|        6 | 0.00030675 | show databases                                                                                                        |
|        7 | 0.00035725 | SELECT DATABASE()                                                                                                     |
|        8 | 0.00011200 | SELECT DATABASE()                                                                                                     |
|        9 | 0.00025950 | show databases                                                                                                        |
|       10 | 0.00012000 | show tables                                                                                                           |
|       11 | 0.14616200 | create table student(id int not null auto_increment primary key,
name varchar(10) not null,
age int,
tel varchar(11)) |
|       12 | 0.00022225 | show tables                                                                                                           |
|       13 | 0.00059125 | desc students                                                                                                         |
|       14 | 0.01117450 | desc student                                                                                                          |
|       15 | 0.15021075 | insert into student (id,name,age,tel) values (2015,'jagger',23,'12345678901')                                         |
|       16 | 0.00030200 | select * from student                                                                                                 |
|       17 | 0.00003825 | update table student add addr varchar(10)                                                                             |
|       18 | 0.16433725 | alter table student add addr varchar(10)                                                                              |
|       19 | 0.00034325 | desc student                                                                                                          |
+----------+------------+-----------------------------------------------------------------------------------------------------------------------+
15 rows in set, 1 warning (0.01 sec)

比如我们分析截图中的第5条SQL语句

mysql> show profile cpu,block io for query 11;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting                       | 0.000113 | 0.000036 |   0.000075 |            0 |             0 |
| checking permissions           | 0.000008 | 0.000002 |   0.000004 |            0 |             0 |
| Opening tables                 | 0.000189 | 0.000062 |   0.000129 |            0 |             0 |
| creating table                 | 0.005309 | 0.003243 |   0.000000 |            0 |           288 |
| After create                   | 0.000032 | 0.000029 |   0.000000 |            0 |             0 |
| query end                      | 0.140427 | 0.001127 |   0.000000 |            0 |            16 |
| Waiting for semi-sync ACK from | 0.000017 | 0.000009 |   0.000000 |            0 |             0 |
| query end                      | 0.000013 | 0.000014 |   0.000000 |            0 |             0 |
| closing tables                 | 0.000015 | 0.000015 |   0.000000 |            0 |             0 |
| freeing items                  | 0.000023 | 0.000023 |   0.000000 |            0 |             0 |
| cleaning up                    | 0.000018 | 0.000018 |   0.000000 |            0 |             0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
11 rows in set, 1 warning (0.00 sec)

show profile返回结果字段含义
Status ： sql 语句执行的状态
Duration: sql 执行过程中每一个步骤的耗时
CPU_user: 当前用户占有的 cpu
CPU_system: 系统占有的 cpu
Block_ops_in : I/O 输入
Block_ops_out : I/O 输出
show profile type 选项
all：显示所有的性能开销信息
block io：显示块 IO 相关的开销信息
context switches: 上下文切换相关开销
cpu：显示 CPU 相关的信息
ipc：显示发送和接收相关的开销信息
memory：显示内存相关的开销信息
page faults：显示页面错误相关开销信息
source：显示和 Source_function、Source_file、Source_line 相关的开销信息
swaps：显示交换次数的相关信息

status出现以下情况的建议

System lock
#确认是由于哪个锁引起的，通常是因为MySQL或InnoDB内核级的锁引起的。建议：如果耗时较大再关注即可，一般情况下都还好

Sending data
#解释：从server端发送数据到客户端，也有可能是接收存储引擎层返回的数据，再发送给客户端，数据量很大时尤其经常能看见。
#备注：Sending Data不是网络发送，是从硬盘读取，发送到网络是Writing to net。建议：通过索引或加上LIMIT，减少需要扫描并且发送给客户端的数据量

Sorting result
正在对结果进行排序，类似Creating sort index，不过是正常表，而不是在内存表中进行排序。
建议：创建适当的索引

Table lock
#表级锁，没什么好说的，要么是因为MyISAM引擎表级锁，要么是其他情况显式锁表

create sort index
#当前的SELECT中需要用到临时表在进行ORDER BY排序
#建议：创建适当的索引

Creating tmp table
#创建临时表。先拷贝数据到临时表，用完后再删除临时表。消耗内存，数据来回拷贝删除，消耗时间，建议：优化索引

converting HEAP to MyISAM
#查询结果太大，内存不够，数据往磁盘上搬了。建议：优化索引，可以调整max_heap_table_size

Copying to tmp table on disk
#把内存中临时表复制到磁盘上，危险！！！建议：优化索引，可以调整tmp_table_size参数，增大内存临时表大小

EXPLAIN用法和结果分析

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

➤ 通过EXPLAIN，我们可以分析出以下结果：

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

➤ 使用方式如下：

EXPLAIN +SQL语句

mysql> explain select * from student;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)