Xtrabackup实现数据的备份与恢复

Xtrabackup介绍

Xtrabackup 是由 percona 开源的免费数据库热备份软件，它能对 InnoDB 数据库和 XtraDB 存储引擎的数据库非阻塞地备份（对于 MyISAM 的备份同样需要加表锁）；mysqldump 备份方式是采用的逻辑备份，其最大的缺陷是备份和恢复速度较慢，如果数据库大于 50G ，mysqldump 备份就不太适合。

Xtrabackup 安装完成后有4个可执行文件，其中2个比较重要的备份工具是 innobackupex xtrabackup

• xtrabackup 是专门用来备份 InnoDB 表的，和 mysql server 没有交互；
• innobackupex 是一个封装 xtrabackup 的 Perl 脚本，支持同时备份 innodb 和myisam，但在对 myisam 备份时需要加一个全局的读锁。
• xbcrypt 加密解密备份工具
• xbstream 流传打包传输工具，类似 tar

Xtrabackup优点

• 备份速度快，物理备份可靠
• 备份过程不会打断正在执行的事务（无需锁表）
• 能够基于压缩等功能节约磁盘空间和流量
• 自动备份校验
• 还原速度快
• 可以流传将备份传输到另外一台机器上
• 在不增加服务器负载的情况备份数据

Xtrabackup备份原理

备份原理

备份开始时首先会开启一个后台检测进程，实时检测
mysq redo 的变化，一旦发现有新的日志写入，立刻将日志记入后台日志文件 xtrabackup_log 中，之后复制 innodb 的数据文件一系统表空间文件 ibdatax，复制结束后，将执行 flush tables with readlock ,然后复制 .frm MYI MYD 等文件，最后执行 unlock tables ,最终停止 xtrabackup_log

输出如下提示信息

xtrabackup: Transaction log of lsn (2543172) to (2543181) was copied.
171205 10:17:52 completed OK!

Xtrabackup增量备份介绍

xtrabackup增量备份的原理是：

• 首先完成一个完全备份，并记录下此时检查点LSN；
• 然后增量备份时，比较表空间中每个页的LSN是否大于上次备份的LSN，若是则备份该页并记录当前检查点的LSN。

增量备份优点：

• 数据库太大没有足够的空间全量备份，增量备份能有效节省空间，并且效率高；
• 支持热备份，备份过程不锁表（针对InnoDB而言），不阻塞数据库的读写；
• 每日备份只产生少量数据，也可采用远程备份，节省本地空间；
• 备份恢复基于文件操作，降低直接对数据库操作风险；
• 备份效率更高，恢复效率更高。

  Xtrabackup安装

  下载安装xtrabackup

  wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/6/x86_64/Percona-XtraBackup-2.4.9-ra467167cdd4-el6-x86_64-bundle.tar
  [root@centos ~]# ll
  total 703528
  -rw-r--r-- 1 root root 654007697 Sep 27 09:18 mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz
  -rw-r--r-- 1 root root  65689600 Nov 30 00:11 Percona-XtraBackup-2.4.9-ra467167cdd4-el6-x86_64-bundle.tar
  [root@centos ~]# tar xf Percona-XtraBackup-2.4.9-ra467167cdd4-el6-x86_64-bundle.tar
  [root@centos ~]# yum install percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm -y
  [root@centos ~]# which xtrabackup 
  /usr/bin/xtrabackup
  [root@centos ~]# innobackupex -v
  innobackupex version 2.4.9 Linux (x86_64) (revision id: a467167cdd4)

  已经安装完成

创建测试数据

mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test;
Database changed
mysql> create table T1 (name varchar(10) not null,sex varchar(10) not null);
Query OK, 0 rows affected (0.15 sec)
mysql> insert into T1 values('zhang','man');
Query OK, 1 row affected (0.01 sec)
mysql> insert into T1 values('zhan','man');
Query OK, 1 row affected (0.01 sec)
mysql> insert into T1 values('sun','woman');
Query OK, 1 row affected (0.00 sec)

Xtrabackup全量备份与恢复

[root@centos ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password="123456" --backup /root

从备份过程截图可以看出会创建一个时间的目录

[root@centos ~]# ll /root/2017-12-04_13-57-29/
total 12352
-rw-r----- 1 root root   425 Dec  4 13:57 backup-my.cnf
-rw-r----- 1 root root  322 Dec  4 13:57 ib_buffer_pool
-rw-r----- 1 root root 12582912 Dec  4 13:57 ibdata1
drwxr-x--- 2 root root  4096 Dec  4 13:57 mysql
drwxr-x--- 2 root root   4096 Dec  4 13:57 performance_schema
drwxr-x--- 2 root root   12288 Dec  4 13:57 sys
drwxr-x--- 2 root root   4096 Dec  4 13:57 test
-rw-r----- 1 root root    22 Dec  4 13:57 xtrabackup_binlog_info
-rw-r----- 1 root root    113 Dec  4 13:57 xtrabackup_checkpoints
-rw-r----- 1 root root    537 Dec  4 13:57 xtrabackup_info
-rw-r----- 1 root root   2560 Dec  4 13:57 xtrabackup_logfile

这里面就是相关的备份文件，同样也可以看到我们创建的库的名称

[root@centos ~]#innobackupex --apply-log /root/2017-12-04_13-57-29/

使用此参数使用相关数据性文件保持一致性状态

mysql> drop table T1;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from T1;
ERROR 1146 (42S02): Table 'test.T1' doesn't exist

接下来准备恢复误删除数据

恢复数据之前需要保证数据目录是空的状态

[root@centos ~]# innobackupex --defaults-file=/etc/my.cnf --copy-back /root/2017-12-04_13-57-29/

具体看日志截图

[root@centos ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS! 
[root@centos ~]# lsof -i :3306
COMMAND PID USER  FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 5935 mysql 21u  IPv6 21850  0t0  TCP *:mysql (LISTEN)
mysql> use test;
Database changed
mysql> select * from T1;
+-------+-------+
| name  | sex |
+-------+-------+
| zhang | man  |
| zhan  | man |
| sun  | woman |
+-------+-------+
3 rows in set (0.00 sec)

恢复成功

Xtrabackup增量备份与恢复

需要注意的是，增量备份仅能应用于InooDB或XtraDB表，对于MyISAM表，增量与全备相同

mysql> select * from T1;
+-------+-------+
| name | sex  |
+-------+-------+
| zhang | man  |
| zhan  | man  |
| sun  | woman |
| susun | woman |
| sige | man  |
| mgg  | man |
+-------+-------+
6 rows in set (0.00 sec)

创建用于增量备份的数据，用来模拟删除掉了全备后的数据，能否通过增量备份文件来恢复

[root@Vcentos ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --incremental /backup/ --incremental-basedir=/root/2017-12-04_13-57-29
#--incremental /backup/   指定增量备份文件备份的目录
#--incremental-basedir    指定上一次全备或增量备份的目录

[root@Vcentos ~]# ll /backup/2017-12-05_09-27-06/
total 312
-rw-r----- 1 root root    425 Dec  5 09:27 backup-my.cnf
-rw-r----- 1 root root    412 Dec  5 09:27 ib_buffer_pool
-rw-r----- 1 root root 262144 Dec  5 09:27 ibdata1.delta
-rw-r----- 1 root root     44 Dec  5 09:27 ibdata1.meta
drwxr-x--- 2 root root   4096 Dec  5 09:27 mysql
drwxr-x--- 2 root root   4096 Dec  5 09:27 performance_schema
drwxr-x--- 2 root root  12288 Dec  5 09:27 sys
drwxr-x--- 2 root root   4096 Dec  5 09:27 test
-rw-r----- 1 root root     21 Dec  5 09:27 xtrabackup_binlog_info
-rw-r----- 1 root root    117 Dec  5 09:27 xtrabackup_checkpoints
-rw-r----- 1 root root    560 Dec  5 09:27 xtrabackup_info
-rw-r----- 1 root root   2560 Dec  5 09:27 xtrabackup_logfile
[root@centos ~]# cd /backup/2017-12-05_09-27-06/
[root@centos 2017-12-05_09-27-06]# cat  xtrabackup_binlog_info
mysql-bin.000001    945
[root@centos 2017-12-05_09-27-06]# cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2542843
to_lsn = 2547308
last_lsn = 2547317
compact = 0
recover_binlog_info = 0

删除一条数据来测试增量恢复

mysql> delete  from T1 where name='susun';
Query OK, 1 row affected (0.06 sec)

增量恢复操作过程如下

[root@centos ~]# innobackupex --apply-log --redo-only /root/2017-12-04_13-57-29/
[root@centos ~]# innobackupex --apply-log --redo-only /root/2017-12-04_13-57-29/ --incremental-dir=/backup/2017-12-05_09-27-06/

恢复全部数据

[root@centos ~]#innobackupex --defaults-file=/etc/my.cnf --copy-back /root/2017-12-04_13-57-29/
[root@centos ~]# /etc/init.d/mysqld start
Starting MySQL.. SUCCESS! 
[root@centos ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  23217 mysql  21u  IPv6 283226  0t0  TCP *:mysql (LISTEN)

查看恢复的数据完整性

[root@VM_0_8_centos ~]#mysql -uroot -p -e "select * from test.T1;"

MySQL存储引擎

引擎（Engine）是电子平台上开发程序或系统的核心组件。利用引擎，开发者可迅速建立、铺设程序所需的功能，或利用其辅助程序的运转。一般而言，引擎是一个程序或一套系统的支持部分。常见的程序引擎有游戏引擎，搜索引擎，杀毒引擎等。

• Ok，我们知道了，引擎就是一个程序的核心组件。
• 简单来说，存储引擎就是指表的类型以及表在计算机上的存储方式。
• 存储引擎的概念是MySQL的特点，Oracle中没有专门的存储引擎的概念，Oracle有OLTP和OLAP模式的区分。不同的存储引擎决定了MySQL数据库中的表可以用不同的方式来存储。我们可以根据数据的特点来选择不同的存储引擎。
• 在MySQL中的存储引擎有很多种，可以通过“SHOW ENGINES”语句来查看。下面重点关注InnoDB、MyISAM、MEMORY这三种。

InnoDB存储引擎

• InnoDB给MySQL的表提供了事务处理、回滚、崩溃修复能力和多版本并发控制的事务安全。在MySQL从3.23.34a开始包含InnnoDB。它是MySQL上第一个提供外键约束的表引擎。而且InnoDB对事务处理的能力，也是其他存储引擎不能比拟的。靠后版本的MySQL的默认存储引擎就是InnoDB。
• InnoDB存储引擎总支持AUTO_INCREMENT。自动增长列的值不能为空，并且值必须唯一。MySQL中规定自增列必须为主键。在插入值的时候，如果自动增长列不输入值，则插入的值为自动增长后的值；如果输入的值为0或空（NULL），则插入的值也是自动增长后的值；如果插入某个确定的值，且该值在前面没有出现过，就可以直接插入。
• InnoDB还支持外键（FOREIGN KEY）。外键所在的表叫做子表，外键所依赖（REFERENCES）的表叫做父表。父表中被字表外键关联的字段必须为主键。当删除、更新父表中的某条信息时，子表也必须有相应的改变，这是数据库的参照完整性规则。
• InnoDB中，创建的表的表结构存储在.frm文件中（我觉得是frame的缩写吧）。数据和索引存储在innodb_data_home_dir和innodb_data_file_path定义的表空间中。
• InnoDB的优势在于提供了良好的事务处理、崩溃修复能力和并发控制。缺点是读写效率较差，占用的数据空间相对较大。

  MyISAM存储引擎

• MyISAM 是 MySQL 中常见的存储引擎，曾经是 MySQL的默认存储引擎。 MyISAM 是基于 ISAM 引擎发展起来的，增加了许多有用的扩展。
• MyISAM 的表存储成3个文件。文件的名字与表名相同。拓展名为 frm 、 MYD 、 MYI 。其实，frm 文件存储表的结构；MYD 文件存储数据，是 MYData 的缩写； MYI 文件存储索引，是 MYIndex 的缩写。
• 基于 MyISAM 存储引擎的表支持3种不同的存储格式。包括静态型、动态型和压缩型。其中，静态型是 MyISAM 的默认存储格式，它的字段是固定长度的；动态型包含变长字段，记录的长度不是固定的；压缩型需要用到 myisampack 工具，占用的磁盘空间较小。
• MyISAM 的优势在于占用空间小，处理速度快。缺点是不支持事务的完整性和并发性。

  MEMORY存储引擎

• MEMORY 是 MySQL 中一类特殊的存储引擎。它使用存储在内存中的内容来创建表，而且数据全部放在内存中。这些特性与前面的两个很不同。
• 每个基于 MEMORY 存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同，类型为 frm 类型。该文件中只存储表的结构。而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。值得注意的是，服务器需要有足够的内存来维持 MEMORY 存储引擎的表的使用。如果不需要了，可以释放内存，甚至删除不需要的表。
• MEMORY 默认使用哈希索引。速度比使用B型树索引快。当然如果你想用B型树索引，可以在创建索引时指定。
• 注意，MEMORY 用到的很少，因为它是把数据存到内存中，如果内存出现异常就会影响数据。如果重启或者关机，所有数据都会消失。因此，基于 MEMORY 的表的生命周期很短，一般是一次性的。

  怎样选择存储引擎

• InnoDB：支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择 InnoDB 有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择 InnoDB ，因为支持事务的提交（ commit ）和回滚（ rollback ）。
• MyISAM：插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择 MyISAM 能实现处理高效率。如果应用的完整性、并发性要求比 较低，也可以使用。
• MEMORY：所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择 MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。

注意，同一个数据库也可以使用多种存储引擎的表。如果一个表要求比较高的事务处理，可以选择 InnoDB。这个数据库中可以将查询要求比较高的表选择 MyISAM 存储。如果该数据库需要一个用于查询的临时表，可以选择 MEMORY 存储引擎。

MySQL 慢查询日志

MySQL中的日志包括：错误日志、二进制日志、通用查询日志、慢查询日志等等。这里主要介绍下比较常用的两个功能：通用查询日志和慢查询日志。

• 通用查询日志：记录建立的客户端连接和执行的语句。
• 慢查询日志：记录所有执行时间超过long_query_time秒的所有查询或者不使用索引的查询

通用查询日志

常用命令：

 show variables like '%version%';

show variables like ‘%general%’;

show variables like ‘%log_output%’;

查看当前慢查询日志输出的格式，可以是 FILE（存储在数数据库的数据文件中的 hostname.log ），也可以是 TABLE（存储在数据库中的 mysql.general_log ）

如何开启MySQL通用查询日志，以及如何设置要输出的通用日志输出格式呢？

• 开启通用日志查询： set global general_log=on;
• 关闭通用日志查询： set global general_log=off;
• 设置通用日志输出为表方式： set global log_output=’TABLE’;
• 设置通用日志输出为文件方式： set global log_output=’FILE’;
• 设置通用日志输出为表和文件方式：set global log_output=’FILE,TABLE’;
• 注意：上述命令只对当前生效，当MySQL重启失效，如果要永久生效，需要配置my.cnf

日志输出的效果图如下：

记录到mysql.general_log表结构如下：

mysql> desc general_log;
+--------------+---------------------+------+-----+-------------------+-----------------------------+
| Field        | Type                | Null | Key | Default           | Extra                       |
+--------------+---------------------+------+-----+-------------------+-----------------------------+
| event_time   | timestamp           | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| user_host    | mediumtext          | NO   |     | NULL              |                             |
| thread_id    | bigint(21) unsigned | NO   |     | NULL              |                             |
| server_id    | int(10) unsigned    | NO   |     | NULL              |                             |
| command_type | varchar(64)         | NO   |     | NULL              |                             |
| argument     | mediumtext          | NO   |     | NULL              |                             |
+--------------+---------------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.00 sec)

my.cnf文件的配置如下：

general_log=1  #为1表示开启通用日志查询，值为0表示关闭通用日志查询
log_output=FILE,TABLE#设置通用日志的输出格式为文件和表

慢查询日志

• MySQL 的慢查询日志是MySQL提供的一种日志记录，用来记录在 MySQL 中响应时间超过阈值的语句，具体指运行时间超过 long_query_time 值的 SQL ，则会被记录到慢查询日志中（日志可以写入文件或者数据库表，如果对性能要求高的话，建议写文件）。默认情况下， MySQL 数据库是不开启慢查询日志的，long_query_time 的默认值为10（即10秒，通常设置为1秒），即运行10秒以上的语句是慢查询语句。
• 一般来说，慢查询发生在大表（比如：一个表的数据量有几百万），且查询条件的字段没有建立索引，此时，要匹配查询条件的字段会进行全表扫描，耗时查过 long_query_time ，则为慢查询语句。

查看当前慢查询日志的开启情况

在MySQL中输入命令：
show variables like '%quer%';

mysql> show variables like '%quer%';
+----------------------------------------+-------------------------------+
| Variable_name                          | Value                         |
+----------------------------------------+-------------------------------+
| binlog_rows_query_log_events           | OFF                           |
| ft_query_expansion_limit               | 20                            |
| have_query_cache                       | YES                           |
| log_queries_not_using_indexes          | ON                            |
| log_throttle_queries_not_using_indexes | 0                             |
| long_query_time                        | 10.000000                     |
| query_alloc_block_size                 | 8192                          |
| query_cache_limit                      | 1048576                       |
| query_cache_min_res_unit               | 4096                          |
| query_cache_size                       | 1048576                       |
| query_cache_type                       | OFF                           |
| query_cache_wlock_invalidate           | OFF                           |
| query_prealloc_size                    | 8192                          |
| slow_query_log                         | ON                            |
| slow_query_log_file                    | /var/log/mysql/mysql_slow.log |
+----------------------------------------+-------------------------------+
15 rows in set (0.00 sec)
#主要掌握以下的几个参数：
（1）slow_query_log 的值为ON为开启慢查询日志，OFF则为关闭慢查询日志。
（2）slow_query_log_file 的值是记录的慢查询日志到文件中（注意：默认名为主机名.log，慢查询日志是否写入指定文件中，需要指定慢查询的输出日志格式为文件，相关命令为：show variables like ‘%log_output%’；去查看输出的格式）。
（3）long_query_time 指定了慢查询的阈值，即如果执行语句的时间超过该阈值则为慢查询语句，默认值为10秒。
（4）log_queries_not_using_indexes 如果值设置为ON，则会记录所有没有利用索引的查询（注意：如果只是将log_queries_not_using_indexes设置为ON，而将slow_query_log设置为OFF，此时该设置也不会生效，即该设置生效的前提是slow_query_log的值设置为ON），一般在性能调优的时候会暂时开启。

设置MySQL慢查询的输出日志格式为文件还是表，或者两者都有？

通过命令：show variables like ‘%log_output%’;

mysql> show variables like '%log_output%';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| log_output    | FILE,TABLE |
+---------------+------------+
1 row in set (0.00 sec)

通过log_output的值可以查看到输出的格式，上面的值为FILE,TABLE。当然，我们也可以设置输出的格式为文本，或者同时记录文本和数据库表中，设置的命令如下：

#慢查询日志输出到表中（即mysql.slow_log）
set globallog_output=’TABLE’;
#慢查询日志仅输出到文本中(即：slow_query_log_file指定的文件)
setglobal log_output=’FILE’;
#慢查询日志同时输出到文本和表中
setglobal log_output=’FILE,TABLE’;  

关于慢查询日志的表中的数据个文本中的数据格式分析：
慢查询的日志记录myql.slow_log表中

mysql> mysql> select * from mysql.slow_log limit 1;
+---------------------+--------------------------------+------------+-----------+-----------+---------------+------------+----------------+-----------+-----------+----------------------------------------------------------------------------------------+-----------+
| start_time          | user_host                      | query_time | lock_time | rows_sent | rows_examined | db         | last_insert_id | insert_id | server_id | sql_text                                                                               | thread_id |
+---------------------+--------------------------------+------------+-----------+-----------+---------------+------------+----------------+-----------+-----------+----------------------------------------------------------------------------------------+-----------+
| 2018-02-07 11:16:55 | root[root] @  [121.196.203.51] | 00:00:00   | 00:00:00  |        13 |            40 | jp_core_db |              0 |         0 |         0 | select pd.lastAuction from Product pd where pd.status = 'O' and pd.auctionStatus = 'A' |      1621 |
+---------------------+--------------------------------+------------+-----------+-----------+---------------+------------+----------------+-----------+-----------+----------------------------------------------------------------------------------------+-----------+
1 row in set (0.00 sec)

慢查询的日志记录到 mysql_slow.log 文件中

# Time: 180118 14:58:37
# User@Host: root[root] @ localhost []  Id:   150
# Query_time: 0.000270  Lock_time: 0.000109 Rows_sent: 0  Rows_examined: 6
SET timestamp=1516258717;
delete from user where User='app';
#可以看到，不管是表还是文件，都具体记录了：是那条语句导致慢查询（sql_text），该慢查询语句的查询时间（query_time），锁表时间（Lock_time），以及扫描过的行数（rows_examined）等信息。

如何查询当前慢查询的语句的个数？

在 MySQL 中有一个变量专门记录当前慢查询语句的个数：
输入命令：show global status like ‘%slow%’;

mysql> show global status like '%slow%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Slow_launch_threads | 132   |
| Slow_queries        | 1772  |
+---------------------+-------+
2 rows in set (0.00 sec)
（注意：上述所有命令，如果都是通过MySQL的shell将参数设置进去，如果重启MySQL，所有设置好的参数将失效，如果想要永久的生效，需要将配置参数写入my.cnf文件中）。

如何利用MySQL自带的慢查询日志分析工具mysqldumpslow分析日志？

mysqldumpslow –s c –t 10 slow-query.log

具体参数设置如下：

• -s 表示按何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；
• -t 表示top的意思，后面跟着的数据表示返回前面多少条；
• -g 后面可以写正则表达式匹配，大小写不敏感。

[root@nginx-test /var/log/mysql]# mysqldumpslow -s c -t 2 /var/log/mysql/mysql_slow.log 
Reading mysql slow query log from /var/log/mysql/mysql_slow.log
Count: 125448  Time=0.00s (131s)  Lock=0.00s (3s)  Rows=2.2 (272835), 2users@2hosts
  select productauc0_.productAuctionId as productA1_12_, productauc0_.auctionIndex as auctionI2_12_, productauc0_.bidCoins as bidCoins3_12_, productauc0_.bidPrice as bidPrice4_12_, productauc0_.bidStep as bidStep5_12_, productauc0_.bidTime as bidTime6_12_, productauc0_.bidder as bidder7_12_, productauc0_.buyFlag as buyFlag8_12_, productauc0_.categoryCode as category9_12_, productauc0_.createTime as createT10_12_, productauc0_.currentAuctionDetailId as current11_12_, productauc0_.currentBidPrice as current12_12_, productauc0_.currentBidTime as current13_12_, productauc0_.currentBidder as current14_12_, productauc0_.effectCoin as effectC15_12_, productauc0_.effetcPoint as effetcP16_12_, productauc0_.endTime as endTime17_12_, productauc0_.newUserFlag as newUser18_12_, productauc0_.productCode as product19_12_, productauc0_.productCost as product20_12_, productauc0_.productName as product21_12_, productauc0_.productPrice as product22_12_, productauc0_.refundRate as refundR23_12_, productauc0_.startPrice as startPr24_12_, productauc0_.startTime as startTi25_12_, productauc0_.status as status26_12_, productauc0_.updateTime as updateT27_12_ from ProductAuction productauc0_ where productauc0_.status='S'
Count: 66216  Time=0.00s (127s)  Lock=0.00s (2s)  Rows=1.7 (115074), root[root]@[121.196.203.51]
  select productauc0_.productAuctionId as productA1_12_, productauc0_.auctionInd

上述中的参数含义如下：
Count:125448       #语句出现了125448次；
Time=0.00s（131s）  #执行最长时间为0.00s，累计总耗费时间131s；
Lock=0.0s（3s）     #等待锁最长时间为0s，累计等待锁耗费时间为3s；
Rows=2.2（272835） #发送给客户端最多的行数为2.2，累计发送给客户端的函数为272835

如何得知设置的慢查询是有效的？

• 很简单，我们可以手动产生一条慢查询语句，比如，如果我们的慢查询 log_query_time 的值设置为1，则我们可以执行如下语句：
• select sleep(1) ;
• 该条语句即是慢查询语句，之后，便可以在相应的日志输出文件或表中去查看是否有该条语句。

mysql的应用场景

Mysql的使用非常普遍，跟mysql有关的话题也非常多，如性能优化、高可用性、强一致性、安全、备份、集群、横向扩展、纵向扩展、负载均衡、读写分离等。要想掌握其中的精髓，可得花费不少功力，虽然目前流行的mysql替代方案有很多，可是从最小成本最容易维护的角度而言，mysql还是首选。大致分为了六种

单Master

• 单 Master 的情况是普遍存在的，对于很多个人站点、初创公司、小型内部系统，考虑到成本、更新频率、系统重要性等问题，系统只依赖一个单例数据库提供服务，基本上已经满足需求。这种场景下我觉得重点应该关注的话题有上图所示的四点。
• 其中最重要的环节是数据备份，如果是交易量非常低，并且具有非常明确的服务时间段特性的话，简单的 mysqldump 是可以胜任的。但是这是有缺陷的，数据还原之后注定从备份点到还原点之间的数据会丢失。然而在极多数的情况下，备份的工作是没法马虎的，如下列举的几点小细节】
  • 1）冷备：停机，直接copy物理文件，InnoDB引擎（frm文件、共享表空间文件、独立表空间文件、重做日志文件、my.cnf）。
  • 恢复：把文件copy到对应目录。
  • 2）热备： Ibbackup或者XtraBackup工具，记录重做日志文件检查点的LSN，copy共享表空间文件以及独立表空间文件（不产生任何阻塞），记录copy后重做日志文件检查点的LSN，copy备份是产生的重做日志。
  • 恢复：恢复表空间文件，应用重做日志文件。
  • 3）温备：-mysqldump，--single-transaction参数进行事务管理保证数据一致性。备份时不能用DDL语句。 恢复：直接执行文件，mysql –uroot –p <文件名.sql> 二进制半同步复制，主从服务器增量复制
  • 恢复：mysqlbinlog

一主一从

• 考虑一主一从的多数初衷是系统性能和系统高可用性问题，除了单Master场景中的备份工作需要做好以外，还有性能优化、读写分离、负载均衡三项重点工作需要考虑。其中性能优化的内容比较多，也是一块大主题，要从系统的服务指标作为依据采取相应的动作，多数系统要求的是3秒内完成请求，总体换算下来，数据库大概可以有1.5秒的总执行时间，能满足这个性能要求就是合理的优化方案
• 读写分离和负载均衡的实现相对简单些，我目前维护的系统比较落后，没有做读写分离，因为是一套以报表类功能为主的系统，而负载均衡是依赖php代码来做的，从实际运维效果来看，不大理想，而且负载均衡的代码过分嵌入到业务逻辑代码中，给代码维护带来一定噪音

一主 n 从

• 一旦开始考虑一主多从的服务器架构，则证明你的系统对可用性、一致性、性能中一种或者多种的要求比较高。好多系统在开始搭建的时候都会往这个方向看齐，毕竟这样“看起来”系统会健壮很多。不过其实并不能单单依靠mysql的配置和mysql自带的中间件来解决可用性、一致性方面的问题。

横向集群

• 系统庞大到需要分库分表，其实是一件可喜可贺的事情，但是切记的是要前面提到性能优化工作做到极致之后才好考虑这些会增加系统复杂度的解决方案。横向集群主要是从业务特性的角度对系统进行切分，最彻底就是切分成了各个子系统，子系统之间通过一些数据同步的方案来把一些核心数据进行共享，以避免跨库调用跨库join。
• 然后是各种系统接口调用，把大事务拆成小事务，事务之间做好隔离和同步。上图中的三个问题在横向集群的架构体系中应属于很有特色的问题，在实际项目中其实是尽量去避免这些需求的存在的，不过如果确实需要了，也得有解决方案。

纵向集群

• 横向集群的切分思路最终是切分子系统，而纵向集群最后遇到的最棘手的问题是扩缩容，我运维的一个系统是提前对数据做了256个切片，256切片中0~127切片和128~255切片分别存在两个一主两从的数据库集群中，系统运维了3年多，目前还没有扩容需求。设计初衷应该是考虑得到，假设有一天数据量非常大，可以把256个切片分4大片，分别存储到4个一主两从的集群中，从而实现扩容。
• 这个思路的确是可取的，只是我们的分库逻辑当前是php代码实现，也有一定程度上影响了业务代码的逻辑，运维起来有点心惊胆战，还是保持业务代码清爽比较好。

混合模式

与其说这部分内容讨论上面5种场景的混合，不如说这部分内容是做总结。

mysql填坑

尽量少用负向条件查询

假设我们有一个Order表，表中有一个字段是Status，这个字段有4个值，分别是0=待支付、1=待发货、2=待收货、3=已完成。

这时，我们要查询所有已经支付的订单，很多人就会写这样的SQL：

select * from Order where Status != 0

这就是一个不好的习惯了。负向条件查询（例如：!=、not in、not exists）都是不能使用索引的，当Order表中的数据到达一定量级时，这个查询的效率会急剧的下降。

正确的写法应该是：

select * from Order where Status in (1,2,3)

尽量少用前导模糊查询

假设我们现在要根据用户的订单号（OrderNo）查询用户的订单，如果是直接通过SQL查询的话，尽量不要使用前导模糊查询，也就是：

select * from Order where OrderNo like '%param'

select * from Order where OrderNo like '%param%'

因为，前导模糊查询是无法命中索引的，所以，会整个数据库去检索，效率相当的差，而非前导模糊查询则是可以使用索引的。

因此，我们尽量不要把通配符放在前面，改成下面这样：

select * from Order where OrderNo like 'param%'

尽量不要在条件字段上进行运算

假设，现在有一个需求，是要查询2018年全年的订单数据，我们就需要通过创建时间（CreateTime）来进行检索，但是，有些程序员就喜欢这样写SQL：

select * from Order where Year(CreateTime)=2019

然后，每次执行时就会发现，查询的速度异常的慢，导致了大量的请求挂起甚至超时。这是因为，我们即使在CreateTime上建立了索引，但是，如果使用了运算函数，查询一样会进行全表的检索。

所以，我们可以改成这样

select * from Order where CreateTime > '2019-1-1 00:00:00'

当查询允许Null值的列时，需要特别注意

我们在创建表的字段时，如果这个字段需要作为索引时，尽量不要允许Null。因为，单列索引不会存Null值，复合索引不存所有索引列都为Null的值，所以如果列允许为Null，可能会得到“不符合预期”的结果集。

复合索引，使用时要注意顺序

登录，肯定是我们使用得最多的一个查询了，为了保证效率，我们为LoginID和Password加上了复合索引。

当我们使用

select * from User where LoginID = '{LoginID}' and Password = '{Password}'
select * from User where Password = '{Password}' and LoginID = '{LoginID}

查询时，都是能够准备的命中索引。当我们使用：

select * from User where LoginID = '{LoginID}' 

查询时，也是能够命中索引的。但是，当我们使用

select * from User where Password = '{Password}' 

查询时，确无法命中索引，这是什么原因呢？

• 这是由于，复合索引对于查询的顺序是非常的铭感的，所以，符合索引中包含了几种规则，其中就有全列匹配和最左前缀匹配。
• 当所有列都能够匹配时，虽然查询的顺序上有不同，但是查询优化器会将顺序进行调整，以满足适合索引的顺序，所以，顺序的颠倒是没有问题的。
• 但是，如果所有列不能匹配时，就必须满足最左前缀匹配了，也就是，必须按照从左到右的顺序进行排列。因此，当我们建立是索引是<LoginID, Password>时，where Password = '{Password}' 就不满足最左前缀规则，无法命中索引了。

结果唯一时

通常，我们设计User表时，并不会把LoginID作为主键，但是，LoginID确会在业务逻辑中验证唯一性，因此，如果使用

select * from User where LoginID = '{LoginID}'

查询时，结果一定只有一条。但是，数据库是不知道的，即使找到了这唯一的一条结果，他也会一直继续，直到扫描完所有的数据。

因此，在执行这样的查询时，我们可以优化一下，改成：

select * from User where LoginID = '{LoginID}' limit 1

这样，当查询到结果时，就不会再继续了。

关于MySQL误删数据

大概分为几种

• 误删文件
• 误删库、表
• 错误全表删除 / 更新
• 升级操作失误

  几点我平时预防误操作导致文件/数据丢失不成熟的建议：

• 1.欲删除文件时，将rm命令改成mv，可在系统层面将rm命令做个alias（或参考 Windows / Mac OSX做法，删除文件时先进回收站）。
• 删除数据库、表时，不要用drop命令，而是rename到一个专用归档库里；
• 2.删除表中数据时，不要直接用delete或truncate命令，尤其是truncate命令，目前不支持事务，无法回滚。
• 3.用delete命令删除数据时，应当先显式开启事务，这样误操作时，还有机会进行回滚。
• 4.要大批量删除数据时，可以将这些数据insert...select到一个新表，确认无误后再删除。或者反其道行之，把要保留的数据写到新表，然后将表重命名对掉。
• 5.执行重要命令之前，先准备好相关命令，再三确认无误才之行，对于新鸟而言，最好请你的boss坐你旁边镇场几次，否则极有可能会连累大家～

注: 一定要做好备份，不管是物理备份还是逻辑备份！

万一发生误操作时，怎么以最快速度进行补救

• 执行DROP DATABASE / DROP TABLE命令误删库表，如果碰巧采用共享表空间模式的话，还有恢复的机会。如果没有，请直接从备份文件恢复吧。神马，你连备份文件都没有？那麻烦退出DBA届吧，一个连备份都懒得做的人，不配成为DBA的。
• 接上，采用共享表空间模式下，误删后立刻杀掉（kill -9）mysql相关进程（mysqld_safe、mysqld），然后尝试从ibdataX文件中恢复数据。
• 误删除正在运行中的MySQL表ibd或ibdataX文件。请立即申请对该实例进行维护，当然，不是指把实例关闭，而是把业务暂停，或者把该实例从线上环境摘除，不再写入新数据，然后利用linux系统的proc文件特点，把该ibd文件从内存中拷出来，再进行恢复，因为此时mysqld实例在内存中是保持打开该文件的，切记这时不要把mysqld实例关闭了。
• 接上，把复制出来的ibdataX或ibd文件拷贝回datadir后，重启mysqld进入recovery模式，innodb_force_recovery 选项从 0 - 6 逐级测试，直至能备份出（整个实例或单表的）所有数据后，再重建实例（或单表），恢复数据。
• 未开启事务模式下，执行delete误删数据。意识到后立即将mysqld（以及mysqld_safe）进程杀掉（kill -9），不要任何犹豫，然后再用工具将表空间数据读取出来。因为执行delete删除后，实际数据并没被物理清除，只是先打上deleted-mark标签，后续再统一清理，因此还有时间差。
• 执行truncate误清整表。如果没使用共享表空间模式的话，基本别想了，走备份恢复+binlog吧。
• 执行不带where条件的update，或者update错数据。也别费劲了，走备份恢复+binlog吧。

十个MySQL经典错误

Too many connections（连接数过多，导致连接不上数据库，业务无法正常进行）

问题还原

mysql> show variables like '%max_connection%';
| Variable_name   | Value |
max_connections | 151   | 
mysql> set global max_connections=1;Query OK, 0 rows affected (0.00 sec)
[root@node4 ~]# mysql -uzs -p123456 -h 192.168.56.132
ERROR 1040 (00000): Too many connections

解决问题的思路：

首先先要考虑在我们 MySQL 数据库参数文件里面，对应的max_connections 这个参数值是不是设置的太小了，导致客户端连接数超过了数据库所承受的最大值。

• 该值默认大小是151，我们可以根据实际情况进行调整。
• 对应解决办法：set global max_connections=500

但这样调整会有隐患，因为我们无法确认数据库是否可以承担这么大的连接压力，就好比原来一个人只能吃一个馒头，但现在却非要让他吃 10 个，他肯定接受不了。反应到服务器上面，就有可能会出现宕机的可能。
所以这又反应出了，我们在新上线一个业务系统的时候，要做好压力测试。保证后期对数据库进行优化调整。

其次可以限制Innodb 的并发处理数量，如果 innodb_thread_concurrency = 0（这种代表不受限制） 可以先改成 16或是64 看服务器压力。如果非常大，可以先改的小一点让服务器的压力下来之后,然后再慢慢增大,根据自己的业务而定。个人建议可以先调整为 16 即可。

MySQL 随着连接数的增加性能是会下降的，可以让开发配合设置 thread pool，连接复用。在MySQL商业版中加入了thread pool这项功能,另外对于有的监控程序会读取 information_schema 下面的表，可以考虑关闭下面的参数

innodb_stats_on_metadata=0
set global innodb_stats_on_metadata=0

主从复制报错类型

Last_SQL_Errno: 1062 （从库与主库数据冲突）

Last_Errno: 1062
   Last_Error: Could not execute Write_rows event on table test.t; 
   Duplicate entry '4' for key 'PRIMARY', 
   Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; 
   the event's master log mysql-bin.000014, end_log_pos 1505

针对这个报错，我们首先要考虑是不是在从库中误操作导致的。结果发现，我们在从库中进行了一条针对有主键表的 sql 语句的插入，导致主库再插入相同 sql 的时候，主从状态出现异常。发生主键冲突的报错。

解决方法：

• 在确保主从数据一致性的前提下，可以在从库进行错误跳过。一般使用 percona-toolkit 中的 pt-slave-restart 进行。在从库完成如下操作

  [root@zs bin]# ./pt-slave-restart -uroot -proot123
  2017-07-20T14:05:30 p=...,u=root node4-relay-bin.000002   1506 1062 

  • 之后最好在从库中开启 read_only 参数，禁止在从库进行写入操作

Last_IO_Errno: 1593（server-id冲突）

Last_IO_Error: 
  Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; 
  these ids must be different for replication to work 
  (or the --replicate-same-server-id option must be used on slave but this 
  does not always make sense; please check the manual before using it)
#这个报错出现之后，就看一目了然看到两台机器的 server-id 是一样的。

在搭建主从复制的过程中，我们要确保两台机器的 server-id 是唯一的。这里再强调一下 server-id 的命名规则（服务器 ip 地址的最后一位+本 MySQL 服务的端口号）

解决方法:

• 在主从两台机器上设置不同的 server-id。

Last_SQL_Errno: 1032（从库少数据，主库更新的时候，从库报错）

Last_SQL_Error:
Could not execute Update_rows event on table test.t; Can't find record 
in 't', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the 
event's master log mysql-bin.000014, end_log_pos 1708

解决问题的办法：

根据报错信息，我们可以获取到报错日志和position号，然后就能找到主库执行的哪条sql，导致的主从报错。

在主库执行：

/usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=decode-rows /data/mysql/mysql-bin.000014 |grep -A 10 1708 > 1.log
cat 1.log
#170720 14:20:15 server id 3  end_log_pos 1708 CRC32 0x97b6bdec     Update_rows: table id 113 flags: STMT_END_F
### UPDATE `test`.`t`
### WHERE
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='dd' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### SET
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='ddd' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
# at 1708
#170720 14:20:15 server id 3  end_log_pos 1739 CRC32 0xecaf1922     Xid = 654
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

获取到 sql 语句之后，就可以在从库反向执行 sql 语句。把从库缺少的 sql 语句补全，解决报错信息。
在从库依次执行：

mysql> insert into t (b) values ('ddd');
Query OK, 1 row affected (0.01 sec)
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[root@node4 bin]# ./pt-slave-restart -uroot -proot123
2017-07-20T14:31:37 p=...,u=root node4-relay-bin.000005         283 1032 

MySQL安装过程中的报错

[root@zs data]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &[1] 3758
[root@zs data]# 170720 14:41:24 mysqld_safe Logging to '/data/mysql/error.log'.
170720 14:41:24 mysqld_safe Starting mysqld daemon with databases from /data/mysql170720 
14:41:25 mysqld_safe mysqld from pid file /data/mysql/node4.pid ended
170720 14:41:24 mysqld_safe Starting mysqld daemon with databases from /data/mysql2017-07-20 
14:41:25 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. 
Please use --explicit_defaults_for_timestamp server option 
(see documentation for more details)./usr/local/mysql/bin/mysqld: 
File '/data/mysql/mysql-bin.index' not found (Errcode: 13 - Permission denied)
2017-07-20 14:41:25 4388 [ERROR] Aborting

解决思路：
遇到这样的报错信息，我们要学会时时去关注错误日志 error log 里面的内容。看见了关键的报错点 Permission denied。证明当前 MySQL 数据库的数据目录没有权限。

解决方法：

[root@zs data]# chown mysql:mysql -R mysql
[root@zs data]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &
[1] 4402
[root@zs data]# 170720 14:45:56 mysqld_safe Logging to '/data/mysql/error.log'.
170720 14:45:56 mysqld_safe Starting mysqld daemon with databases from /data/mysql
#启动成功。

如何避免这类问题，个人建议在安装MySQL初始化的时候，一定加上--user=mysql，这样就可以避免权限问题。

./mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/ --defaults-file=/etc/my.cnf --user=mysql

数据库密码忘记的问题

[root@zs ~]# mysql -uroot -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@zs ~]# mysql -uroot -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
#我们有可能刚刚接手别人的 MySQL 数据库，而且没有完善的交接文档。root 密码可以丢失或者忘记了。

解决思路：

目前是进入不了数据库的情况，所以我们要考虑是不是可以跳过权限。因为在数据库中，mysql数据库中user表记录着我们用户的信息。

解决方法：
启动 MySQL 数据库的过程中，可以这样执行：

/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf  --skip-grant-tables &
这样启动，就可以不用输入密码，直接进入 mysql 数据库了。然后在修改你自己想要改的root密码即可。
update mysql.user set password=password('root123') where user='root';

truncate 删除数据，导致自动清空自增ID，前端返回报错 not found。

这个问题的出现，就要考虑下truncate 和 delete 的区别了。

看下实验演练：

#首先先创建一张表；
CREATE TABLE `t` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=300 DEFAULT CHARSET=utf8
#插入三条数据：
mysql> insert into t (b) values ('aa');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t (b) values ('bb');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t (b) values ('cc');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t;
+-----+------+
| a   | b    |
+-----+------+
| 300 | aa   |
| 301 | bb   |
| 302 | cc   |
+-----+------+
3 rows in set (0.00 sec)
#先用 delete 进行删除全表信息，再插入新值。

• 结果发现truncate把自增初始值重置了，自增属性从1开始记录了。当前端用主键id进行查询时，就会报没有这条数据的错误。
• 个人建议不要使用truncate对表进行删除操作，虽然可以回收表空间，但是会涉及自增属性问题。这些坑，我们不要轻易钻进去。

阿里云 MySQL 的配置文件中，需要注意一个参数设置就是：

lower_case_table_names = 0;默认情况
lower_case_table_names = 1;是不区分大小写 . 如果报你小写的表名找不到, 那你就把远端数据库的表名改成小写 , 反之亦然 . 注意 Mybatis 的 Mapper 文件的所有表名也要相应修改

数据库总会出现中文乱码的情况

解决思路：

对于中文乱码的情况，记住老师告诉你的三个统一就可以。还要知道在目前的mysql数据库中字符集编码都是默认的UTF8

处理办法：

1、数据终端，也就是我们连接数据库的工具设置为 utf8
2、操作系统层面；可以通过 cat /etc/sysconfig/i18n 查看；也要设置为 utf8
3、数据库层面；在参数文件中的 mysqld 下，加入 character-set-server=utf8。

Emoji 表情符号录入 mysql 数据库中报错

Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x97\xF0\x9F...' for column 'CONTENT' at row 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2734)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1379)

解决思路：针对表情插入的问题，一定还是字符集的问题。

处理方法：我们可以直接在参数文件中，加入

vim /etc/my.cnf
[mysqld]
init-connect='SET NAMES utf8mb4'
character-set-server=utf8mb4
注：utf8mb4 是 utf8 的超集。

使用 binlog_format=statement 这种格式，跨库操作，导致从库丢失数据，用户访问导致出现错误数据信息。

#当前数据库二进制日志的格式为：binlog_format=statement
在主库设置binlog-do-db=mydb1（只同步mydb1这一个库）
在主库执行use mydb2；
insert into mydb1.t1 values ('bb');这条语句不会同步到从库。
但是这样操作就可以；
use mydb1;
insert into mydb1.t1 values ('bb');因为这是在同一个库中完成的操作。
#在生产环境中建议使用binlog的格式为row，而且慎用binlog-do-db参数。

MySQL 数据库连接超时的报错

org.hibernate.util.JDBCExceptionReporter - SQL Error:0, SQLState: 08S01
org.hibernate.util.JDBCExceptionReporter - The last packet successfully received from the server was43200 milliseconds ago.The last packet sent successfully to the server was 43200 milliseconds ago, which is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection 'autoReconnect=true' to avoid this problem.
org.hibernate.event.def.AbstractFlushingEventListener - Could not synchronize database state with session
org.hibernate.exception.JDBCConnectionException: Could not execute JDBC batch update
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Connection.close() has already been called. Invalid operation in this state.
org.hibernate.util.JDBCExceptionReporter - SQL Error:0, SQLState: 08003
org.hibernate.util.JDBCExceptionReporter - No operations allowed after connection closed. Connection was implicitly closed due to underlying exception/error:
 ** BEGIN NESTED EXCEPTION **
#大多数做 DBA 的同学，可能都会被开发人员告知，你们的数据库报了这个错误了。赶紧看看是哪里的问题。

这个问题是由两个参数影响的，wait_timeout 和 interactive_timeout。数据默认的配置时间是28800（8小时）意味着，超过这个时间之后，MySQL 数据库为了节省资源，就会在数据库端断开这个连接，Mysql服务器端将其断开了，但是我们的程序再次使用这个连接时没有做任何判断，所以就挂了。

解决思路：

• 先要了解这两个参数的特性；这两个参数必须同时设置，而且必须要保证值一致才可以。
• 我们可以适当加大这个值，8小时太长了，不适用于生产环境。因为一个连接长时间不工作，还占用我们的连接数，会消耗我们的系统资源。

解决方法：

• 可以适当在程序中做判断；强烈建议在操作结束时更改应用程序逻辑以正确关闭连接；然后设置一个比较合理的timeout的值（根据业务情况来判断）

can't open file (errno:24)

有的时候，数据库跑得好好的，突然报不能打开数据库文件的错误了。

解决思路：

• 首先我们要先查看数据库的error log。然后判断是表损坏，还是权限问题。还有可能磁盘空间不足导致的不能正常访问表；操作系统的限制也要关注下；用 perror 工具查看具体错误！

  linux:/usr/local/mysql/bin # ./perror 24
  OS error code  24:  Too many open files

• 超出最大打开文件数限制！ulimit -n查看系统的最大打开文件数是65535，不可能超出！那必然是数据库的最大打开文件数超出限制！
• 在 MySQL 里查看最大打开文件数限制命令：show variables like 'open_files_limit';
• 发现该数值过小，改为2048，重启 MySQL，应用正常

处理方法：

 repair table ；
chown mysql权限
#清理磁盘中的垃圾数据

MySQL的索引

关于存储引擎

• 创建合适的索引是SQL性能调优中最重要的技术之一。在学习创建索引之前，要先了解MySql的架构细节，包括在硬盘上面如何组织的，索引和内存用法和操作方式，以及存储引擎的差异如何影响到索引的选择。
• MySQL 有很多种衍生版本，这些衍生版本支持更多不同种类的存储引擎。本文主要讨论三种MySQL引擎。
  • MyISAM 一种非事务性的存储引擎，是MySQL 5.5之前版本默认的存储引擎。
  • InnoDB 最流行的事务性存储引擎，从5.5版开始成为MySQL默认的引擎。
  • Memory 基于内存的，非事务性的以及非持久性的存储引擎。

从5.5版本开始，MySQL表的默认存储引擎从MyISAM换成InnoDB，将会使用户安装那些依赖默认设置或者专门为MyISAM编写的软件包时带来很大的影响。

MySQL索引类型

• MySQL支持在所有关系数据库表中创建主键、唯一键、不唯一的非主码索引等多种类型的索引。此外MySQL还支持纯文本和空间索引类型。
• MySQL内置的存储引擎对各种索引技术有不同的实现方式，包括：B-树，B+树，R-树以及散列类型。
• 索引数据结构理论：
  • B-树
    • B-树中有两种节点类型：索引节点和叶子节点。叶子节点是用来存储数据的，而索引节点则用来告诉用户存储在叶子节点中的数据顺序，并帮助用户找到相应的数据。
    • B-树的搜索，从根节点开始，对节点内的关键字有序进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子节点，重复。直到所对应的儿子指针为空，或已经是叶子节点。
    • B-树是一种多路搜索树：
      • (1). 定义任意非叶子节点最多有M个儿子，且M>2;
      • (2). 根节点的儿子数为[2,M];
      • (3). 除根节点以外的非叶子节点的儿子数为[M/2,M];
      • (4). 每个节点存放至少M/2-1(取上整)和至多M-1个关键字;
      • (5). 非叶子节点的关键字个数=指向儿子节点的指针的个数-1;
      • (6). 非叶子节点的关键字：k[i]<k[i+1];
      • (7). 非叶子节点的指针：p[1]，p[2]，·····，p[M]；其中p[1]指向的关键字小于k[1]的子树，p[M]指向的关键字大于K[m-1]的子树;
      • (8). 所有的叶子节点位于同一层;
  • B+树
    • B+树数据结构是B-树实现的增强版本。尽管B+树支持B-树索引的所有特性，它们之间最显著的不同点在于B+树中底层数据是根据被提及的索引列进行排序的。B+树还通过叶子节点之间的附加引用来优化扫描性能。
    • B+搜索和B-搜索不同，区别是B+树只有达到叶子节点才命中（B-树可以在非叶子节点命中），其性能等价于关键字全集做一次二分搜索。
    • B+树的特性：
      • （1）所有关键字都出现在叶子节点的链表中，叶子节点相当于存储数据的数据层。
      • (2）不可能在非叶子节点上命中。
      • （3）非叶子节点相当于是叶子节点的索引，叶子节点相当于数据层。
  • 散列
    • 散列表数据结构是一种很简单的概念，它将一种算法应用到给定值中以在底层数据存储系统中返回一个唯一的指针或位置。散列表的优点是始终以线性时间复杂度找到需要读取的行的位置，而不像B-树那样需要横跨多层节点来确定位置。
  • 通信R-树
    • R-树数据结构支持基于数据类型对几何数据进行管理。目前只有MyISAM使用R-树实现支持空间索引，使用空间索引也有很多限制，比如只支持唯一的NOT NULL列等。
  • 全文本
    • 全文本结构也是一种MySQL采用的基本数据结构。这种数据结构目前只有当前版本MySQL中的MyISAM存储引擎支持。5.6版本将要在InnoDB存储引擎中加入全文本功能。全文本索引在大型系统中并没有什么实用的价值，因为大规模系统有很多专门的文件检索产品。所以不用在介绍。

MySQL实现

对B-树，B+树和散列等数据结构的基本概念有了一些了解之后，我们就可以开始讨论MySQL通过支持它们的存储引擎如何实现不同的算法。同时每种实现也对磁盘和内存使用情况有不同的影响，这一点在大型数据库系统中是非常重要的考虑因素。

MyISAM的B-树

MyISAM存储引擎使用B-树数据结构来实现主码索引、唯一索引以及非主码索引。在 MyISAM 实现数据目录和数据库模式子目录中，用户可以找到和每个MySQL表对应的.MYD和.MYI文件。数据库表上定义的索引信息就存储在MYI文件中，该文件的块大小是1024字节。这个大小是可以通过myisam-block-size系统变量分配。

$  ls -1h /var/lib/mysql/book/source_words.MY*
-rw-rw---- 1 mysql mysql  9.2M 2015-05-07 19:08
source_words.MYD
-rw-rw---- 1 mysql mysql  7.8M 2015-05-07 19:08
source_words.MYI

• 这些文件结构的内部格式可以从MySQL免费源代码中找到，也可以查看MySQL内部手册。
• 在MyISAM中，非主码索引的B-树结构存储索引值和一个指向主码数据的指针，这是MyISAM和InnoDB的一个显著区别。这一点导致了两个存储引擎的索引的不同工作方式。
• MyISAM索引是在内存的一个公共缓存中管理的，这个缓存的大小可以通过key_buffer_size或者其他命名键缓存来定义。这是根据统计和规划的表索引的大小来设定缓存大小时主要的考虑因素。

InnoDB的B+树聚簇主码

• InnoDB存储引擎在它的主码索引（也被称为聚簇主码）中使用了B+树，这种结构把所有数据都和对应的主码组织在一起，并且在叶子节点这一层上添加额外的向前和向后的指针，这样就可以更方便地进行范围扫描。
• 在文件系统层面，所有InnoDB数据和索引信息都默认在公共InnoDB表空间中管理，否则管理员就通过innodb_data_file_path这个变量指定文件路径。这是一个叫ibdatal文件。
• 由于InnoDB用聚簇主码存储数据，底层信息占用的磁盘空间的大小很大程度上取决于页面的填充因子。对于按序排列的主码，InnoDB会用16K页面的15/16作为填充因子。对于不是按序排列的主码，默认情况下InnoDB会插入初始数据的时候为每一个页面分配50%作为填充因子。
• 在改索引的实现方式中B+树的叶子节点上是data就是数据本身，key为主键，如果是一般索引的话，data便会指向对应的主索引。在B+树的每一个叶子节点上面增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+树。其目的是提高区间访问的性能。

InnoDB的B-树非主码

InnoDB中的非主码索引使用了B-树数据结构，但InnoDB中的B-树结构实现和MyISAM中并不一样。在InnoDB中，非主码索引存储的是主码的实际值。而MyISAM中，非主码索引存储的包含主码值的数据指针。这一点很重要。首先，当定义很大的主码的时候，InnoDB的非主码索引可能回更大，随着非主码索引数量的增加，索引之间大小差别可能会变得很大。另一个不同点在于非主码索引当前可以包含主键的值，并且可以不是索引必须有的部分。

内存散列索引

在默认MySQL的引擎索引中，只有MEMORY引擎支持散列数据结构，散列结构的强度可以表示为直接键查找的简单性，散列索引的相似度模式匹配查询比直接查询慢。也可以为MEMORY引擎指定一个B-树索引实现。

内存B-树索引

对于大型MEMORY表来说，使用散列索引进行索引范围搜索的效率很低，B-树索引在执行直接键查询时确实比使用默认的散列索引快。根据B-树的不同深度，B-树索引在个别操作中的确可能比散列算法快。

InnoDB内部散列索引

InnoDB存储引擎在聚簇B+树索引中存储主码：但在InnoDB内部还是使用内存中的散列表来更高效地进行主码查询。这个机制有InnoDB存储引擎来管理，用户只能通过innodb_adaptive_hash_index配置项来选择是否启用这个唯一的配置选项。

感觉有点长了 ,还有个mysql连接池

本作品采用《CC 协议》，转载必须注明作者和本文链接