登录不上云数据库 RDS 如何排查？

• Ping 云数据库 RDS 域名是否可通。
• 账号、密码是否有误。
• 白名单授权是否正确。
• 连接数是否打满。
• 如果返回错误提示如端口不可达（110、111、104错误码），请提交工单。

如何进行实例重启？

当云数据库 RDS 内存使用过高时或修改了mysql参数时需要进行实例重启。登录控制台进入实例的“基本信息”页面，点击右上角的“重启实例”，即可对实例执行重启操作。

如何修改云数据库 RDS for MySQL 的系统参数？

登录控制台进入实例的“参数配置”页签，对需要修改的参数进行编辑修改。部分参数可以不重启立即生效，部分参数需要重启后生效，是否需要重启参见页签内的描述。

升配和降配有什么要求和影响？

• 升配和降配支持情况：

  变配类型	预付费	后付费	内存	磁盘	实例状态
  升配	随时	随时	支持	支持	运行中
  降配	到期后支持	随时	支持	不支持	运行中

• 执行时间：与实例数据量相关，数据量平均传输速度为100G/半小时。
• 对服务影响：和升级过程写情况有关，会出现短暂停写，建议流量低峰期进行。

如何扩展实例？

• 创建只读实例、扩展读。
• 创建代理实例，实现读写分离、负载均衡和连接池。
• 创建分布式数据库，扩展写和单机容量限制。

如何恢复数据库？

注意：数据库恢复会覆盖数据且无法恢复，且恢复期间数据库服务会中断，请用户谨慎操作。

• 登录控制台进入实例的“备份管理”页签，在备份列表中选择要恢复的备份，点击后面的“恢复”链接，开始恢复任务，恢复时长取决于备份文件大小。
• 建议用户使用克隆实例功能恢复数据库，详情操作可参见克隆实例。

如何迁移数据，如将自建数据库同步到云数据库 RDS 上？

百度智能云提供DTS服务帮助实现数据迁移。数据迁移的详细操作请参见数据迁移。

如何对云数据库 RDS 的资源状况进行定期的全面检查？

您可开通“云顾问 Cloud Advisor”服务，定期获得云上资源在安全、可用性、性能、成本方面的检查报告。报告中包含若干RDS相关检查项目，如：RDS-实例停用、RDS-慢SQL报表/诊断、RDS-白名单访问风险、RDS-磁盘打满、RDS-闲置实例、RDS-高使用率、RDS-多可用区等。了解或开通云顾问服务，请前往云顾问主页。

错误信息提示“Too many connections in ...”如何处理？

该错误信息表示数据库的连接数打满，首先排查连接数打满原因是业务增长还是SQL阻塞：

• 如果是业务增长：建议升级套餐或扩展实例。
• 如果为SQL阻塞：找到导致阻塞的SQL，控制并发或SQL优化。

CPU达到 100% 或迅速飙升如何处理？

首先查看是否存在大量慢查询，找到数量最多的慢查询并进行SQL优化。

为什么数据库写入账号突然变成只读了？

• 产生原因：磁盘配额满了。为防止本地磁盘写满，去掉了部分写权限。
• 如何处理：1.删除无用数据释放资源。2.升级磁盘配额。
• 如何避免：添加磁盘报警监控。

数据库使用量如何计算？

数据库使用量包含Ibdata文件+数据文件，不包括binlog和全日志。

云数据库 RDS 的binlog 数据占用磁盘空间么，默认会保留几天？

binlog 数据占用磁盘空间，本地默认保留7天的 binlog。

为什么只读实例数据有延迟？

• 产生原因：主库并发写入，从库串行同步；造成从库同步速度慢于主库写入速度。
• 造成影响：读写分离后，在只读实例上查不到更新数据。
• 如何避免：控制主库写入并发（TPS不宜超过2000），从库配置不低于主库。

云数据库 RDS 默认隔离级别如何修改？

云数据库 RDS 默认隔离级别是REPEATABLE-READ，不能修改默认事务隔离级别，也不能修改GLOBAL事务隔离级别。

使用云数据库 RDS 执行数据迁移，为什么迁移后的数据都是乱码？

请检查目标云数据库 RDS 数据库和源自建数据库的字符集信息，通常此类问题是由于不同的字符集造成迁移数据时发生强制转码导致的。解决问题的方法是，创建与源数据库相同字符集的目标数据库后再次执行数据迁移。

MySQL 数据库表名都是大写但是导入云数据库 RDS 后表名都是小写，怎么处理？

如果在实例的“参数设置”中设置了“lower_case_table_names”即表名大小写敏感会出现这种情况。需要修改该参数取值并重新导入云数据库 RDS 。参数取值解释如下：

MySQL 通过 delete 方法删除数据后空间无法释放,怎么处理？

MySQL中的数据表在使用delete操作删除数据之后，磁盘空间是无法直接释放的，delete之后会在数据表中产生数据空洞，新来的数据可以复用部分空间，如果需要立刻释放磁盘空间，需要进行表整理或重建表。

1. 直接通过sql语句释放空间 方法一：执行OPTIMIZE TABLE 'table_name'，实现表空间的释放 方法二：查看表的存储引擎，重建表，如InnoDB表执行ALTER TABLE 'table_name' ENGINE = InnoDB。
2. 通过页面控制台释放空间。

1）选择云数据库 RDS 实例中的数据库管理工具：

2）进入管理工具页面。

3）点击需要释放空间的表。

4）点击操作。

5）点击整理表碎片或优化表：

怎么确保 MySQL 字符集无乱码？

数据库相的字符集相关设置，是用户在使用数据库的过程当中需要慎重考虑的一点。

可以通过执行下面这条命令，了解到字符集相关的配置：

mysql> show variables like '%character%';

当客户端连接服务器的时候，会将自己想要的字符集发给mysql服务器，随后服务器会使用这个字符集去设置character_set_client、character_set_connection、character_set_results这三个值；此外客户端也可以通过执行set names XX，来对这三个值进行赋值。

1. 如何保证写入正确？

   确保将要写入的数据实际字符集，character_set_client和该字段字符集三者保持一致；一些情况下，只需要保证前两者一致即可，mysql会对客户端字符集与字段字符集做转化，大部分情况下，utf8可以和gbk进行正常转化，但有无法转化而丢失字符的情况出现，因此推荐用户自行确保三个字符集保持一致。

2. 如何保证读取正确？

   在数据被正确写入的前提下，保证客户端字符集与数据所在的字段字符集一致即可。

3. 读到乱码应该怎么办？

   在2的前提下，仍然读到乱码，可以推测出数据的实际存储与字段的字符集出现了不一致，也就是写入时就引入了问题。这个时候需要首先确定数据的实际存储字符集是否正确；对乱码的字段使用mysql的hex()函数，可以查看到字段的实际存储；以中文为例，一个utf8字符（汉字）占用3个字节，如下：

   mysql> select hex('我们');
   +---------------+
   | hex('我们') |
   +---------------+
   | E68891E4BBAC  |
   +---------------+

   一个gbk汉字则占用2个字节，如下：

   mysql> select hex('我们');
   +-------------+
   | hex('我们') |
   +-------------+
   | CED2C3C7    |
   +-------------+
   1 row in set (0.00 sec)

   实际情况包括中英文混排等情况，较为复杂。

   确认了数据的实际存储结构之后，可以通过hex()导出数据，以unhex导入的方式，完成乱码的修复。

GTID 使用限制

GTID（Global Transaction Identifier）为全局事物ID，是Master上为每个事务分配的唯一的标识符，在一个Master/Slave的复制拓扑结构中依然具有唯一性。GTID的构成如下：

GTID = source_id:transaction_id source_id一般为server_id，transaction_id是一个由1递增的序列号。 GTID最显著的优势为，在基于GTID的Master/Slave复制场景下，CHANGE MASTER TO无需指定同步点（MASTER_LOG_FILE/MASTER_LOG_POS）即可进行“自动”复制。 但要注意，基于GTID的复制模式不支持以下场景：

• 事务中包含对非事务型引擎表的更新

  当在一个事务中混合对事务型引擎表的更新和非事务型引擎表的更新，会导致赋予这个事务多个GTID。

• CREATE TABLE … SELECT语句

  对于基于语句的复制（statement-based replication），CREATE TABLE … SELECT是不安全的。 对于基于行的复制（row-based replication），这个语句被记录为两个事件（event），一个是创建表，另一个是从源表中导入数据到刚刚创建的表。当在事务中执行这个语句时，存在某些场景使得这两个事件有一样的事务标识符，导致在Slave中“导入数据”过程被跳过。

• 临时表

  CREATE TEMPORARY TABLE和DROP TEMPORARY TABLE 不支持在基于GTID的复制模式下使用。只可以临时表的操作放在事务外，并且使得参数autocommit=1。

错误信息提示“The table 'XXXX' is full”如何处理？

在使用云数据库 RDS 的过程中，有时会碰到这样的出错信息： The table 'XXXX' is full

1. 出现这种错误的原因

   MySQL在进行聚合类操作(group by,count(distinct)等)、排序类操作(order by,distinct等)、union、group_concat、子查询或者多表关联时，可能会使用内部临时表。首先使用内存临时表，如果需要的内存超过了tmp_table_size、max_heap_table_size设定的内存，它就会把内存临时表转化为磁盘临时表，存储在tmpdir变量标示的目录下（当查询涉及到Blob或Text类型字段，MySQL会跳过内存临时表的步骤，而直接使用磁盘临时表）。 受限于物理磁盘的可用空间,当空间不足时,磁盘临时表对应的物理文件无法继续扩展,就会报告这类错误。 对应这种错误，查看“监控”中的“磁盘使用率”监控项，可能会看到磁盘瞬间打满的情况（打满情况和监控采样数据的频率有关，监控项可能无法采样到瞬间打满的情况）。

2. 对于这种错误的跟进，处理方法

• 在查询中，尽量避免使用Blob和Text类型字段。
• 优化查询逻辑，避免过大的中间结果集操作。比如说通过合理的使用索引优化该类查询，避免使用临时表等。

3. 如何确认查询使用了内部临时表

   使用explain查看执行计划，如果执行计划的Extra部分出现了Using temporary，可以说明查询使用了内部临时表。

MySQL 的事务执行时间较长，怎么处理？

概念

MySQL大事务指MySQL的事务执行时间较长，例如如下事务执行1000秒：

BEGIN;
SELECT sleep(1000);
COMMIT;

影响

• 大事务对MySQL的影响：大事务期间，MySQL性能下降，可能引起MySQL的连接数上涨、慢查询增多、锁等待增加、CPU及内存使用率过高等问题。
• 大事务对用户影响：读写访问变慢、访问超时报错、甚至不可用的风险。

起因

• 开启了事务，但由于程序问题一直未提交事务，使得这个事务耗时较久；也会因为这个事务产生的锁表，导致其他事务发生锁等待而变成大事务；
• DDL语句：大表的DDL语句会使得DDL SQL执行耗时较长，例如100G的表做加字段操作，会产生至少100分钟的大事务。
• 低效率的SQL语句：读写语句影响行数较多、联表查询、未按照索引的大量数据查询，都是低效率的SQL，都容易产生大事务。

处理

用户应尽量避免大事务，比如开启了事务及时提交、上线之前在线下测试是否存在低效率的SQL、DDL语句、尽量在业务低峰期操作。

如果大事务产生了，该如何处理，以下为处理方案：

• 终止会话，即kill掉大事务 通过show process list查找对应事务，得到进程id号（第一列），例如id=202020；执行kill 202020。
• 开启事务自动提交 开启自动提交：执行set autocommit = 1； 若需关闭自动提交：执行set autocommit = 0。
• 设置最大事务时间 若设置事务最长执行100秒，则执行如下命令： set global wait_timeout=100; set global interactive_timeout=100;

MySQL CPU 负载较高的原因和处理方法

MySQL CPU高可能是SQL不合理或者没有合适索引，导致rows_read过高；也可能是QPS非常高，或者热点数据读写导致锁争用等。

CPU使用率过高现象：

原因：

1. 应用负载高 有的表数据量不大，但是读写频率繁过高，并发量也很大（比如，热点数据），容易导致PCU使用率高。
2. 查询执行成本高 数据集当前运行的QPS不高，但存在查询执行效率低、执行需要扫描大量表中的数据，即，存在慢查询SQL。

解决方法：

1. 使用show processlist语句，查找负荷较重的SQL语句，对该语句进行优化，如建立适合的索引。
2. 打开慢查询配置，找到对性能有影响的SQL语句，然后使用EXPLAIN，找出相应的优化方案。
3. 在高并发情况下，是否可以添加缓存，如memcache/redis之类的，以缓解db的压力。
4. 修改数据集架构，适当添加从库，改善对热点数据的频繁读操作。
5. 查看tmp_table_size、max_heap_table_size的大小是否偏小，如果允许，适当增大表； MySQL的配置文件中，tmp_table_size的默认大小是32M，如果一张临时表超出该大小，MySQL将会产生一个The table tbl_name is full形式的错误；如果业务需要做很多高级group by查询，可以增加 tmp_table_size值。
6. 对WHERE、JOIN、MAX()、MIN()、ORDER BY等子句条件判断中用到的字段，应该根据其建立索引INDEX。 索引被用来快速找出在一列上用特定值的行。没有索引，MySQL不得不首先以第一条记录开始并读完整个表直到找出相关的行。表越大，花费时间越多。如果表对于查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要遍历所有数据。如果一个表有1000行，这比顺序读取至少快100倍。所有的MySQL索引（PRIMARY、UNIQUE和INDEX）在B树中存储。
7. 定时优化文件及索引，就MYSQL官方建议，一周或一月左右优化一次。

避免出现cpu使用高的准备：

1. 提前使用压测工具，对新上线功能、模块进行压测。
2. 应用设计与开发过程中，尽量遵守云数据库 RDS for MySQL 优化的原则，降低查询的逻辑IO，提高扩展性。

云数据库 RDS for MySQL Metadata lock 的产生和处理方法

1、 Metadata lock的含义和作用

在5.5版本，MySQL 引入了 Metadata lock 用于在并发环境中维护表结构的一致性，自 5.5 版本后一直沿用至今。

Metadata lock 是表结构的互斥锁。当事务对表结构进行修改（alter table）、删除（drop table）等操作前，需要获得Metadata lock才能执行。如果当前表上有未提交的事务，则该事务将持有 Metadata lock，对表结构进行修改和删除的事务需要等待未提交事务提交后才能获得Metadata lock。

Metadata lock 解决了两个问题：

• 事务隔离问题。当云数据库 RDS for MySQL 处于 Repeatable read 隔离级别时，事务A对同一个表进行两次查询，事务B要修改表结构，Metadata lock 保证了A的两次查询结果一致。
• 主从同步问题。未引入 Metadata lock 之前，事务A向表中插入数据，事务B要删除表，若事务B先提交，则 binary log 会先记录删除操作，后记录插入操作。当从库执行 relay log 中的操作时会出错。

2、 Metadata lock 相关的常见操作

需要获得 Metadata lock 才能执行的操作如下：

• 修改表结构（alter table）。
• 删除表（drop table）。
• 增加/删除索引（alter table add/drop index，create/drop index）。
• 维护表操作（optimize/repair table）。
• 获得表级写锁（lock table tblname write）。

如下图，通过 show processlist; 命令可以看到等待Metadata lock的会话。

当某一事务持有Metadata lock时，该表将被阻塞读写，后续对该表的读写操作将被迫等待锁。因而如果使用不当，可能会对RDS用户造成巨大损失。

下面介绍了使用云数据库 RDS 遇到 Waiting for table Metadata lock 的常见问题及解决办法。

3、 云数据库 RDS 上事务处于 Waiting for table Metadata lock 的常见问题

• 表上存在长时间查询。
• 表上存在未提交或回滚的事务。
• 表上有失败的查询事务，且该事务未提交或回滚。

4、 解决方案

1. 使用show processlist; 命令查看长时间查询：

然后使用kill命令终止该查询会话：

2. 使用如下语句从 MySQL 中查询持有该表metadata lock的未提交/回滚的事务。

   select i.trx_mysql_thread_id metadata_lock_thread_id from information_schema.innodb_trx i, (select id, time from information_schema.processlist where time = (select max(time) from information_schema.processlist where state = 'Waiting for table metadata lock' and substring(info, 1, 5) in ('alter' , 'optim', 'repai', 'lock ', 'drop ', 'creat'))) p where timestampdiff(second, i.trx_started, now()) > p.time and i.trx_mysql_thread_id not in (connection_id(),p.id);

直接将上述SQL语句粘贴运行，可得如下结果：

执行kill 语句即可关闭该会话：

3. 第三种情况可视为第一种情况的特例，但由于失败查询在执行前就返回，因而不会有查询正在执行，因而前述的show processlist;和 information_schema.innodb_trx 表中都无法找到相关信息。

此时，可使用下面的 SQL 语句查询持有metadata lock的会话ID。

	select p1.id metadata_lock_thread_id from information_schema.processlist p1, (select id, time from information_schema.processlist where time = (select max(time) from information_schema.processlist where state = 'Waiting for table metadata lock' and substring(info, 1, 5) in ('alter' , 'optim', 'repai', 'lock ', 'drop ', 'creat', 'trunc'))) p2 where p1.time >= p2.time and p1.command in ('Sleep' , 'Query') and p1.id not in (connection_id() , p2.id);

执行结果如下图：

执行kill语句关闭该会话：

5、 避免Metadata lock影响业务的注意事项

1. 在业务低峰期执行第2节的操作，比如修改/删除表、创建/删除索引。
2. 显式打开会话自动提交。在到云数据库 RDS 的数据库连接建立后，执行 set autocommit=1; 或 set autocommit=on;。
3. 设置SQL语句的锁等待超时参数，避免长时间等待元数据锁影响表上其他业务查询。比如 set lock_wait_timeout=30; 命令可以设置 metadata lock wait 的最长时间为 30 秒。

4. 使用事件来终止长时间运行的事务，比如下面的SQL语句会终止执行时间超过30分钟的事务。

   create event my_long_running_trx_monitor on schedule every 30 minute starts '2016-01-01 00:00:00' on completion preserve enable do begin declare v_sql varchar(500); declare no_more_long_running_trx integer default 0; declare c_tid cursor for select concat ('kill ',trx_mysql_thread_id,';') from information_schema.innodb_trx where timestampdiff(minute,trx_started,now()) >= 60; declare continue handler for not found set no_more_long_running_trx=1; open c_tid; repeat fetch c_tid into v_sql; set @v_sql=v_sql; prepare stmt from @v_sql; execute stmt; deallocate prepare stmt; until no_more_long_running_trx end repeat; close c_tid; end;

云数据库 RDS MySQL InnoDB锁分析

背景

关系型数据库中事务的隔离性是通过锁（Lock）来实现的。通常来说，锁可以分为读锁（也称为共享锁）和写锁（也称为互斥锁）两种类型。读锁可以与读锁兼容，而写锁不能与任何类型的锁兼容，也就是说当一个事务获取一个数据库对象的读锁之后，另一个事务也可以获取对该对象加读锁，但是不能加写锁。在出现锁冲突时，通常来说，后发起锁请求的事务会进入锁等待状态；一旦等待达到设置的超时时间，则该事务会由于等待超时而被回滚。

MySQL InnoDB引擎中的锁在遵循上述规则的同时，出于性能优化和提升并发度的考虑，对锁处理进行了优化，一方面通过实现行及锁，使事务的并发度最大化；另一方面通过使用MVCC（Multiple Version Concurrent Control，多版本并发控制）技术，使得读锁和写锁能够兼容，从而减少事务之间的锁等待。

InnoDB 中的锁超时配置

InnoDB中事务锁等待的超时时间通过系统配置参数innodb_lock_wait_timeout来指定，要查看和该参数的值，可以通过云数据库 RDS 的控制管理页面的“参数配置”页面来设置，也可以通过MySQL客户端执行如下命令：

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql> set global innodb_lock_wait_timeout = 100;
Query OK, 0 rows affected (0.00 sec)

mysql> set session innodb_lock_wait_timeout = 100;
Query OK, 0 rows affected (0.00 sec)

而该参数的默认值是50，单位是秒，也就是说，在默认情况下事务在等待写锁的时候，最多等50秒，如果在50秒之后还未能获取该锁，那么该事务会被回滚，应用程序将得到如下的错误消息： ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 一般来说，应用程序对于该错误的处理应该是重试事务。正常情况下，该默认锁超时时间不易达到，因此不应出现大量锁超时和重试。如果应用程序中确有执行时间超过该超时时间且带有写入操作的事务，则建议对应用程序进行优化，尽量缩短事务执行的时间，避免长时间持有写锁。

锁超时的处理

如果系统中持续发生了大量的等待和超时，则需要定位长时间持有写锁的事务，并考虑将其结束执行。要列出当前InnoDB中的锁等待的情况，可以执行如下SQL语句：

select l.* from
    (select 'Blocker' role, p.id, p.user,
            left(p.host, locate(':', p.host) - 1) host,
            tx.trx_id, tx.trx_state, tx.trx_started,
            timestampdiff(second, tx.trx_started, now()) duration,
            lo.lock_mode, lo.lock_type, lo.lock_table, lo.lock_index,
            tx.trx_query, lw.requested_lock_id Blockee_id,
            lw.requesting_trx_id Blockee_trx
      from information_schema.innodb_trx tx,
           information_schema.innodb_lock_waits lw,
           information_schema.innodb_locks lo,
           information_schema.processlist p
      where lw.blocking_trx_id = tx.trx_id
        and p.id = tx.trx_mysql_thread_id
        and lo.lock_id = lw.blocking_lock_id
      union
      select 'Blockee' role, p.id, p.user,
          left(p.host, locate(':', p.host) - 1) host,
          tx.trx_id, tx.trx_state, tx.trx_started,
          timestampdiff(second, tx.trx_started,
          now()) duration, lo.lock_mode, lo.lock_type,
          lo.lock_table, lo.lock_index, tx.trx_query,
          null, null
      from information_schema.innodb_trx tx,
           information_schema.innodb_lock_waits lw,
           information_schema.innodb_locks lo,
           information_schema.processlist p
      where lw.requesting_trx_id = tx.trx_id
        and p.id = tx.trx_mysql_thread_id
        and lo.lock_id = lw.requested_lock_id)
l order by role desc, trx_state desc;

该SQL的执行结果参考输出如下：

通过该输出，可以结束role为Blocker的事务，使其占用的互斥锁得到释放，其他role为Blockee的事务获取所需的互斥锁。要结束指定的事务，可以在MySQL客户端中使用kill connection命令：

mysql> kill connection 2;
Query OK, 0 rows affected (0.00 sec)

如果在结束长时间占有互斥锁的事务后，仍有大量事务出现锁等待超时，则考虑优化应用程序中的处理逻辑，减少事务持有互斥锁时间；同时对数据库进行优化，提高SQL执行速度。如果还无法解决该问题，请联系在线技术支持。

MySQL MyISAM 引擎锁分析

背景

MyISAM是MySQL中最古老的存储引擎之一，由于该引擎出现的早，因此有很多的先天缺陷和不足，其中最明显区别于InnoDB存储引擎的缺点是：

• 不支持事务。
• 只支持行级锁。

目前版本的MySQL中，如无特殊理由，则表都应该使用InnoDB，而不是用MyISAM表。然而考虑到向前兼容，目前仍有部分用户表的存储引擎是MyISAM。

由于MyISAM存储引擎只支持表级锁，对表中数据进行的任何操作都会对整张表加锁；同时由于MyISAM存储引擎没有类似于InnoDB中的MVCC技术，使得MyISAM表上的读锁和写锁不兼容。

MyIsam 中的锁超时设置

与InnoDB不同，MyISAM引擎中没有相应的lock_wait_timeout配置参数。也就是说，当一个事务在等待一张MyISAM表上的锁时，会无限等待。相应的，这时候应用程序会表现为挂起。因此，在使用MyISAM表类型的应用程序中，应该设有等待超时机制，防止程序被无限挂起。

MyISAM 中锁等待的处理

当使用MyISAM类型表的应用程序出现被挂起等待数据返回的情况，可能是由于数据库访问正处于锁等待状态。要检查事务的执行是否正处于锁等待状态，可以通过MySQL客户端运行show processlist命令，相应的输出如下：

由于MyISAM引擎并不支持事务，在SQL语句执行完毕后表级锁会立即释放，因此通常情况下很难出现表级锁被长时间占用。此处，通过一个特定的带有SLEEP的语句来模拟长时间占有MyISAM表级锁的情形。可以看到，既是是读锁也与写锁不相兼容，连接ID为5的会话正在等待表名为myisam_tab上的表级锁。

发现MySQL中有大量的事务都在等待表级锁，这时候可以通过结束拥有表级锁的事务来释放该锁。要结束指定的事务，可以在MySQL客户端中使用kill connection命令：

mysql> kill connection 2;
Query OK, 0 rows affected (0.00 sec)

如果在结束长时间占有表级的事务后，仍有大量事务在等待表级锁，则考虑优化应用程序中的处理逻辑，减少事务持有表级锁的时间；同时对数据库进行优化，提高SQL执行速度。如果还无法解决该问题，请联系在线技术支持。

只读实例同步延迟该如何处理？

什么是同步延迟？

MySQL的同步机制是异步进行的，其中IO thread负责接收从主库dump的binlog到从库上生成relay log，随后SQL thead负责解析relay log并在从库上进行重放来完成同步。这个机制导致了主从之间的同步是存在延迟的。

通常我们通过show slave status命令输出的seconds_behind_master值以及结合io thread和sql thread的状态，来对延迟程度进行量化。

导致同步延迟的原因以及对策

1. 主库存在较大的event

   主库上执行了较大的事务以及ddl操作，会在从库上执行相同的时间来完成该事务，从而导致了同步延迟。

   对策：优化业务，减小事务的规模；使用在线ddl。

2. 主库的高并发写入

   由于从库通过单线程的sql thread进行同步，当主库上出现高并发的写入时，单线程的同步性能往往会出现瓶颈，可以通过查看主库的并发写入情况对此进行判断。

   对策：进行集群拆分，优化写入；或者考虑使用多线程同步。

3. 从库性能瓶颈

   从库负载过高时，容易造成io压力过高，这个时候很容易出现从库延迟。

   对策：进行从库扩容，分担读取流量，降低从库负载。

4. myisam表

   对于myisam引擎的数据表，从库上一个较长时间的查询会导致该表的同步阻塞，从而引起主从延迟。

   对策：强烈建议使用innodb引擎代替myisam。

5. 网络抖动

   从库与主库之间出现网络抖动时，可能造成io线程频繁拉取binlog失败，造成同步延迟。这种延迟实际上seconds_behind_master是一直为0的。

   对策：重启从库的io线程，增加心跳机制来发现此类问题。

同步延迟排查流程

出现延迟时

1. 查看主库写入压力是否暴涨。
2. 查看从库的负载、io压力。
3. 查看主库是否之前出现过大事务、ddl操作。
4. 查看主从之间的写入qps差别。

MySQL 各 timeout 相关参数该如何设置？

MySQL的timeout参数主要是为控制连接鉴权、事务锁冲突等待等情况下的超时时间限制而设置的，根据所处网络环境和业务环境的不同，可以适当调整，以满足业务需求，下面对这些参数分别予以介绍。

connect_timeout

该参数控制的是客户端与mysql的tcp连接建立后，访问数据前进行权限验证进行交互的超时等待时间。

     mysql> show variables like '%timeout%';
	+-----------------------------+----------+
	| Variable_name               | Value    |
	+-----------------------------+----------+
	| connect_timeout             | 5        |

举例，可以进行简单的测试，time 、telnet、 mysqlip、 port ，

Connected to testserver1 (192.168.1.1).
Escape character is '^]'.
xxx_passwordConnection closed by foreign host.

real    0m5.009s
user    0m0.001s
sys     0m0.001s

在网络质量不是很好的不太稳定的环境中，可以调整该参数为一个 较大的值。
修改方法是set global connect_timeout = 10;

innodb_flush_log_at_timeout

刷新日志的时间，默认为1s。

InnoDB为了保证在线日志的刷写高效进行，使用了内存的log buffer，日志保存在log_buffer中，日志从log buffer刷到了linux文件系统的buffer， 与该参数配合使用的是innodb_flush_log_at_trx_commit，控制这两个缓存的刷新，innodb_flush_log_at_trx_commit=1，表示在每次事务提交的时候，都把log buffer刷到文件系统中(os buffer)去，并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。

innodb_flush_log_at_trx_commit=0，表示每隔一秒把log buffer刷到文件系统中(os buffer)去，并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。也就是说一秒之前的日志都保存在日志缓冲区，也就是内存上，如果机器宕掉，可能丢失1秒的事务数据。

innodb_flush_log_at_trx_commit=2，表示在每次事务提交的时候会把log buffer刷到文件系统中(os buffer)去，但是每隔一秒调用文件系统(os buffer)的“flush”操作将缓存刷新到磁盘上去。

innodb_lock_wait_timeout

事务锁等待的超时时间，锁等待时的Query超时时间。跟死锁不一样，InnoDB一旦检测到死锁立刻就会回滚代价小的那个事务，锁等待是没有死锁的情况下一个事务持有另一个事务需要的锁资源，被回滚的肯定是请求锁的那个Query，该参数为只读参数，只有重启才能生效；

例如

• t1表 id1是唯一索引键。
• 事务1中sql update t1 set t1.id2=100 where id1=10;但未提交。
• 事务2中sql update t1 set t1.id2=200 where id1=10;该sql将等待行锁的释放。
• 该参数缺省值是50，事务2等待50秒后如果事务1还未提交或者回滚，则事务2sql不再等待锁，而放弃执行sql。

innodb_rollback_on_timeout

该参数的缺省值是OFF，如果事务因为加锁超时，会回滚上一条语句执行的操作。如果设置ON，则整个事务都会回滚，该参数为只读参数，只有重启才能生效。

举例：innodb_rollback_on_timeout = 1

session1

	mysql> create table t1( id1 int ,id2 varchar(100));
	mysql> insert into t1 values(1,'1row');
	mysql> create index idx_t1 on t1(id1);
	mysql> insert into t1 values(2,'2row');
	mysql> select * from t1;
	+------+------+
	| id1  | id2  |
	+------+------+
	|    1 | 1row |
	|    2 | 2row |
	+------+------+
	2 rows in set (0.00 sec)

	mysql> begin;
	Query OK, 0 rows affected (0.00 sec)
	mysql> update t1 set id2='22row' where id1=2;
	Query OK, 1 row affected (0.00 sec)
	Rows matched: 1  Changed: 1  Warnings: 0
	mysql> select * from t1;
	+------+-------+
	| id1  | id2   |
	+------+-------+
	|    1 | 1row  |
	|    2 | 22row |
	+------+-------+
	2 rows in set (0.00 sec)
	mysql>

    **session2**
    mysql> use doctest;
	Database changed
	mysql> begin ;
	Query OK, 0 rows affected (0.00 sec)
	mysql> select * from t1;
	+------+------+
	| id1  | id2  |
	+------+------+
	|    1 | 1row |
	|    2 | 2row |
	+------+------+
	2 rows in set (0.00 sec)

	mysql> update t1 set id2='11row' where id1=1;
	Query OK, 1 row affected (0.00 sec)
	Rows matched: 1  Changed: 1  Warnings: 0

	mysql> select * from t1;
	+------+-------+
	| id1  | id2   |
	+------+-------+
	|    1 | 11row |
	|    2 | 2row  |
	+------+-------+
	2 rows in set (0.00 sec)

	mysql>  update t1 set id2='222row' where id1=2;
	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
	mysql> select * from t1;
	+------+------+
	| id1  | id2  |
	+------+------+
	|    1 | 1row |
	|    2 | 2row |
	+------+------+
	2 rows in set (0.00 sec)
	从以上结果看，它回滚了session2的整个事务；

interactive_timeout／wait_timeout

interactive_timeout／wait_timeout针对的是交互式／非交互式连接情况下的超时时间，如果超过这个时间的空闲连接将被释放掉，例如通过mysql客户端连接数据库是交互式连接,正在执行的sql不计算这个超时时间。

举例： mysql> set global interactivetimeout=2; Query OK, 0 rows affected (0.00 sec) 等待超过2秒以上... mysql> select sleep(1); ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 73 Current database: ** NONE _**

net_read_timeout /net_write_timeout

mysql服务器端等待从客户端读取数据和写入数据的最大时限，该参数在网络环境较差情况下，调大该参数可以缓解超时问题发生概率。

举例： set global net_read_timeout =60;

MySQL BINLOG 如何使用？

概述

MySQL的binlog用于记录数据库的变更操作/可能导致变更的操作（row-based格式的binlog则不会记录可能导致变更的操作），并记录了操作执行时间等其它信息。

使用binlog有以下两个重要的目的。

• 用于主从复制：由于binlog记录了所有的变更操作（称作events），主库通过将binlog发至从库，从库回放binlog中的events从而实现和主库一致的数据变更。
• 用于数据恢复：当存在一个全量备份，通过回放备份点之后的events，实现任意时间点的数据恢复。

虽然开启binlog会导致MySQL的性能下降，但是其所带来的主从复制、数据恢复等功能相对于其所引起的性能下降要有价值的多。

相关配置

1. binlog相关操作

• 启动MySQL时，设置--log-bin[=base_name]参数，开启binlog。
• MySQL在启动、执行flush logs语句或者当前binlog文件大小超过max_binlog_size的值时，会产生新的binlog文件，但是一个事务不会跨多个binlog文件，因此单个binlog文件的大小可能大于max_binlog_size。

• 当客户端有super权限时，通过如下语句可在当前连接禁用binlog（其它连接不受影响）。

  mysql> SET sql_log_bin=0;

• 从库默认不会在binlog中记录从主库同步的events，除非设置--log-slave-updates参数被设置。

2. binlog校验机制

• MySQL在记录event的同时会记录其长度；回放binlog时，默认使用长度来校验event是否被正确地写入。
• 通过设置binlog_checksum，MySQL会将event的checksum也写入binlog。如果主库同时设置master_verify_checksum，在回放binlog时，主库会用checksum来校验event的正确性；同理，如果从库设置了slave_sql_verify_checksum，I/O线程在回放event时，也会使用checksum进行校验。

3. binlog格式

• statement-based：binlog中events记录的是语句。配置文件中设置--binlog-format=STATEMENT，或执行如语句。

  mysql> SET GLOBAL/SESSION binlog_format = 'STATEMENT';

• row-based：binlog中events记录的是表中每行记录的变更。配置文件中设置设置--binlog-format=ROW，或执行如下语句。

  mysql> SET GLOBAL/SESSION binlog_format = 'ROW';

• mixed-base：默认使用statement-based格式，当遇到非确定的语句时，会自动转换为row-based格式。配置文件中设置--binlog-format=MIXED，或执行如下语句。

  mysql> SET GLOBAL/SESSION binlog_format = 'MIXED';

• 如果主库的binlog格式被设置为row-based，从库的binlog格式也应该相应地设置为row-based，否则主从会同步中断：Error executing row event: 'Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.'。
• 对于间接修改mysql数据库中表数据的操作（GRANT，REVOKE，SET PASSWORD，RENAME USER）以及表结构变更语句（CREATE，ALTER、DROP），即使binlog格式被设置为row-based，其对应的events仍然记录的是语句。
• 如果表的存储引擎是InnoDB，且事务的隔离级别为READ COMMITTED/READ UNCOMMITTED，或者含有二进制类型的数据，binlog格式只能被设置为row-base。

4. 删除binlog

• 删除所有的binlog。

  mysql> RESET MASTER;

• 删除部分binlog。

  mysql> PURGE BINARY LOGS TO 'mysql-bin.010'; mysql> PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

5. binlog缓存

• 当一个语句/事务完成后，在释放锁或提交之前，会将涉及到的变更操作写入binlog：对于非事务表中的变更，会在对应语句执行后立刻写入binlog；对于事务表中的变更，会先缓存对应的操作，直到MySQL收到commit后再写入binlog文件。
• 由于非事务表中的变更无法回滚，如果一个事务中涉及到了对非事务表的修改并且被回滚，那么整个事务也会被记录到binlog（包括ROLLBACK语句）。
• 当一个线程开启事务后，会给它分配binlog_cache_size大小的缓存；如果事务中的变更语句超过缓存大小，线程会使用临时文件来存储这些语句。参考状态变量Binlog_cache_use和Binlog_cache_disk_use来调整binlog_cache_size的大小，从而避免使用临时文件。
• 参数sync_binlog=N指定每N次事务提交会将binlog同步至磁盘（如果N=0，则由操作系统控制binlog同步）；最安全的值为1，否则每次操作系统或者机器崩溃时，可能会丢失N-1个已提交事务的binlog。

6. binlog与InnoDB故障恢复

• 对于InnoDB引擎的表，MySQL在收到commit语句后，先将处于prepared状态的事务顺序地写入binlog，然后将binlog同步至磁盘，最后将这些事务在存储引擎层面提交。如果MySQL在后两个操作之间崩溃，重启时prepared的事务会被回滚，但是事务中的变更语句却残留在binlog文件中。
• 为解决上述问题，需开启--innodb_support_xa以支持XA事务：MySQL在重启时，先回滚未处于prepared状态的事务，然后扫描最后一个binlog文件、收集事务的xid并计算binlog文件中最后的有效位置。MySQL会通知InnoDB提交那些已同步到binlog中的事务，并将binlog文件中最后有效位置后的events清除。
• 当主库重启后，如果错误日志中存在“The binary log file_name is shorter than its expected size”错误信息，说明binlog中缺少至少一个已成功提交的事务，可能导致从库没有同步对应的events；为保证主从数据的一致性，需要使用主库最新的数据快照来重做从库的数据。

MySQL binlog 工具

MySQL的binlog是以二进制形式存储的，要展示其文本形式，则需用到mysqlbinlog工具。对于statement-based格式的binlog，输出包含了SQL语句；对于row-based格式的binlog，输出包含每行的变更（需要加上-v参数）。

1、命令使用

shell> mysqlbinlog binlog.0000001

2、结果输出

	# at 106
	#171018 19:14:37 server id 1672120943  end_log_pos 182  Query   thread_id=64    exec_time=0     error_code=0

上述event中的时间和server id会从主库传送到从库，end_log_pos表示下一个event开始的位置（可用来校验event是否被正确写入），exec_time表示语句执行的时间（对于从库，该时间为语句在从库结束的时间减去语句在主库开始的时间，可反映出主从之间的延迟），error_code表示执行event的错误号（0表示没有错误）。

mysqlbinlog的输出同时也可作为mysql的输入，用于重新执行binlog中语句，从而实现任意时间点的数据恢复。

shell> mysqlbinlog binlog.0000001 | mysql -u root -p

3、重要参数

• --database=db_name, -d db_name：当binlog格式为statement-based时，mysqlbinlog仅输出默认数据库为db_name的条目（对于修改其它库数据的语句，如果默认数据库是db_name，也会被输出；同理，如果默认数据库不是db_name，即使修改db_name库中的数据，也不会被输出）。当binlog格式为row-based时，mysqlbinlog仅输出修改db_name库中数据的条目。
• --exclude-gtids=gtid_set：不输出gtid属于gtid_set的条目。
• --include-gtids=gtid_set：仅输出gtid属于gtid_set的条目。
• --local-load=dir_name, -l dir_name：指定LOAD DATA INFILE产生的临时文件存放的目录。
• --result-file=name, -r name：指定存储输出结果的文件。
• --short-form, -s：仅输出binlog中的语句，不输出额外的信息以及row-based的events。
• --skip-gtids[=(true|false)]：输出中不包含GTIDS。当需要生成dump文件时可以指定该参数。
• --start-datetime=datetime：从第一个时间戳等于/大于datetime的event开始读取binlog。
• --start-position=N, -j N：从第一个偏移量等于/大于N的event开始读取binlog。
• --stop-datetime=datetime：当第一个event的时间戳等于/大于datetime时停止读取binlog。
• --stop-position=N：当当第一个event的偏移量等于/大于N时停止读取binlog。
• --verbose, -v：重构row-based的events，并以注释SQL的形式显示其内容。

典型实践

1. 在任何条件下binlog都应该被开启，即使这会带来性能的下降；同时从库也应尽量设置--log-slave-updates参数，以保证在主库故障时，延迟的从库可以从其他从库补齐数据。
2. 尽量使用row-based格式的binlog，以保证主从的数据一致性；同时在某些场景下，row-base格式的binlog可以提升从库回放events的效率。在将binlog的格式为row-based前，需确保所有表都有主键（能够快速定位表中记录），并且单条语句/事务不会大量更新记录（否则会产生大量的events，影响SQL的执行以及同步效率）。
3. 当用mysqlbinlog工具解析binlog时，可使用TMPDIR环境变量来指定临时文件的存放目录，避免打满临时目录。
4. 如果binlog中含有LOAD DATA INFILE语句，mysqlbinlog会先将数据拷贝到临时文件，然后其重写成指向临时文件的LOAD DATA LOCAL INFILE语句。为避免临时目录打满，可使用--local-load参数指定存放临时文件的目录；此外，生成的临时文件不会自动删除，需手动处理。
5. 通过start-datetime，stop-datetime，start-position以及stop-position参数，结合全量备份，可实现任意时间点的数据恢复。

Percona Toolkit 工具集

概论

Percona Toolkit是一个维护MySQL数据库服务的工具集，包含很多的功能，比如pt-update可以比较不同版本MySQL下的sql执行的差异，pt-mysql-summary可以对接MySQL服务器生成一份详细的配置情况和status信息，pt-online-schame-change可以支持在线变更表结构等。

pt-online-schame-change 工作原理

使用限制

1. 被修改标必须有主键。
2. 被修改表不能针对insert/update/delete的触发器。
3. 如果被修改表有外键，需要使用--alter-foreign-keys-method选项。

工作原理

假设表名为tbl_name，clone表名_tbl_name_new

1. check，根据使用限制及其表的状态进行check。
2. 创建新表。根据原表定义创建新表，并跟alter语句alter _tbl_name_new新表（此时新表的已经符合修改后表的定义）。
3. 创建触发器。在原表的基础针对insert/update/delete分别建立1个after触发器。触发器的作用是在insert/update/delete存在的条件下，同步原表的操作到新表。
4. 导数据。将原表的数据导入到新表。通过insert low_priority ignore into _tbl_new (…) select (…) from tbl_old lock in share mode 的方式导数据。
5. 重命名表。rename table tbl_name to _tbl_name_old, _tbl_name_new to tbl_name。
6. 清理环境。清理_tbl_name_old表及其触发器。

出现“连接数满（too many connections）”提示，如何处理？

概念

应用端连接MySQL，当出现连接数满（too many connections）提示，说明当前mysql实例的连接数超过了其最大连接数（max_connections），服务已经开始产生流量拒绝。

对于连接打满通常有两种情形。空闲连接过多和活动连接过多。

空闲连接过多

浪费了mysql连接资源。

原因

• 对于长连接模式（比如 Java 应用），应用端配置了连接池。连接池的初始连接数设置过高（或者是每个实例的连接数不高，但采用多实例部署），应用启动后建立多个到 mysql 实例的空闲连接。
• 对于短连接模式（比如 PHP 应用），出现大量的空闲连接说明应用没有在查询执行完毕后显式的关闭连接。用户应该检查应用是否在页面查询结束后调用连接关闭方法主动显式关闭了到 mysql 实例的连接。

解决方法

• 通过 Kill 命令来终止当前空闲会话。
• 修改业务端配置，启用连接池复用连接功能（或者调低连接池的初始连接数），具体设置请参考对应服务业务端所用框架连接池的配置文档。
• 修改业务端程序，确保及时关闭不再使用的数据库连接。
• 参数优化：根据业务特点，对超时参数（wait_timeout、interactive_timeout）进行适当调整，设置其为较小值，可以通过mysql实例主动释放空闲连接，以减小空闲连接的数量。具体修改方式如下所示：

非交互式连接空闲时间（wait_timeout）：

交互模式连接空闲时间（interactive_timeout）：

建议与说明

• 在 MySQL 实例连接数完全打满的情况下，通过 mysql客户端无法连接到实例；因此对于长连接模式，强烈建议连接池的最大连接数要小于实例中的连接数上限。当发生连接数陡增的情形时，可以通过mysql客户端登录，然后手动对空闲连接进行kill操作。建议优选先在控制台修改 wait_timeout 参数为较小值，使mysql实例主动关闭空闲时间超过阈值的连接。
• 通常，应用到mysql实例采用的是非交互模式（当然，具体采用那种模式取决于应用的连接方式配置，如PHP 通过MYSQL_CLIENT_INTERACTIVE常量给 mysql_connect()来开启交互模式的连接）。
• wait_timeout和interactive_timeout这两个参数的修改，修改前已经存在的会话保持修改前的设置，修改后新创建的会话使用新的参数设置。

活动连接过多

系统资源消耗较高，通常与流量陡增、SQL性能、并发、锁有关。

原因

• 锁等待（包括 InnoDB 锁等待、MyISAM 表级锁等待、表元数据锁等待）。
• CPU使用率高。
• IOPS使用率高。

解决方法

• 锁等待处理，找到长时间未结束的查询，然后终止它。
• CPU使用率高，确定原因，进行优化，优化内容包括：CPU扩容、SQL优化、降低业务端请求频率、逻辑优化等。
• IOPS使用率高，具体分析导致IOPS使用率高的原因（通常为内存较小、SQL优化等），可以考虑扩容从库、SQL优化（增加索引）等。

MySQL查询缓存（Query Cache）如何设置和使用？

概述

MySQL的Query Cache用于缓存SELECT语句的文本及其对应的查询结果，如果之后接收到相同的查询语句，无需再次解析与执行该语句，MySQL会直接将缓存的结果返回给客户端。Query Cache不会返回过期的数据，当表发生更改时，所有与该表相关的缓存都会从Query Cache中删除。Query Cache在MySQL5.7.20版本中被弃用，在MySQL 8.0版本中被删除。

工作原理

MySQL在接收到查询语句时，首先会将其与Query Cache中缓存的语句逐字节地进行比较，因此语句必须完全一致才会命中缓存（包括大小写、空白字符等）。此外，如果查询使用的数据库、协议版本以及字符集不同时，都会被当做不同的语句；子查询以及存储过程、触发器、事件中的查询语句也不会被缓存。

当以下语句运行时，会导致相关表的缓存失效：INSERT，UPDATE，DELETE，TRUNCATE TABLE，ALTER TABLE，DROP TABLE，DROP DATABASE。

当以下情况发生时，查询也无法被缓存。

• 查询中涉及部分系统函数：UUID(), NOW(), ... 。
• 查询中涉及用户自定义函数（UDFs）或者存储过程。
• 查询中涉及用户变量或本地存储的程序变量。
• 查询中涉及mysql、information_schema、performance_schema库中的表。
• 查询中涉及分区表。
• 查询中使用临时表。
• 查询中没有使用任何表。
• 查询产生警告（warnings）。

• 查询是如下形式的一种。

  SELECT ... LOCK IN SHARE MODE。 SELECT ... FOR UPDATE。 SELECT ... INTO OUTFILE ...。 SELECT ... INTO DUMPFILE ...。 SELECT * FROM ... WHERE autoincrement_col IS NULL。

相关配置

1、 检查Query Cache是否可用

• 当have_query_cache变量的值为YES时表示Query Cache功能可用。

  mysql> SHOW VARIABLES LIKE 'have_query_cache'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | have_query_cache | YES | +------------------+-------+

2、 Query Cache相关的系统变量

• query_cache_size：用于指定Query Cache的大小；当query_cache_size被设置为非0值时，至少需要约40KB用于分配其数据结构，如果该值被设置的过小，则会产生如下所示的警告。

  mysql> SET GLOBAL query_cache_size = 40000; Query OK, 0 rows affected, 1 warning (0.00 sec)

  mysql> SHOW WARNINGS\G *** 1. row *** Level: Warning Code: 1282 Message: Query cache failed to set size 39936; new query cache size is 0

  mysql> SET GLOBAL query_cache_size = 41984; Query OK, 0 rows affected (0.00 sec)

  mysql> SHOW VARIABLES LIKE 'query_cache_size'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | query_cache_size | 41984 | +------------------+-------+

• query_cache_type：影响Query Cache的工作方式，可以被设置成如下值 - 0 or OFF：阻止Query Cache缓存结果以及从Query Cache检索结果 - 1 or ON：缓存查询语句及其结果，但是以SELECT SQL_NO_CACHE开头的语句除外 - 2 or DEMAND：仅缓存以SELECT SQL_CACHE开头的查询语句及其结果。
• query_cache_limit：控制能被缓存的单个结果集的最大值。
• query_cache_min_res_unit：指定Query Cache缓存结果时每次分配的内存块的大小。

3、 开启Query Cache功能

• 启动前在配置文件中设置query_cache_size=1M，query_cache_type=1或在命令行执行如下语句：

  mysql> SET GLOBAL query_cache_size = 1000000; mysql> SET GLOBAL query_cache_type = ON;

4、 关闭Query Cache功能

• 启动前在配置文件中设置query_cache_size=0，query_cache_type=0或在命令行执行如下语句：

  mysql> SET GLOBAL query_cache_size = 0; mysql> SET GLOBAL query_cache_type = OFF;

5、 其余操作

• 整理Query Cache碎片（不会删除缓存的语句及其结果）。

  mysql> FLUSH QUERY CACHE;

• 刷新Query Cache碎片（删除所有缓存的语句及其结果，FLUSH TABLES具有同样的效果）。

  mysql> RESET QUERY CACHE

6、 Query Cache状态变量

• Qcache_free_blocks：Query Cache中空闲内存块的个数。
• Qcache_free_memory：Query Cache中空闲内存的大小。
• Qcache_hits：命中Query Cache的查询个数。
• Qcache_inserts：Query Cache中插入的查询个数。
• Qcache_lowmem_prunes：Query Cache中删除的查询个数。
• Qcache_not_cached：没有命中Query Cache的查询个数。
• Qcache_queries_in_cache：Query Cache中注册的查询个数。

典型实践

1. 鉴于Query Cache在MySQL 8.0后会被删除，对于大多数应用场景开启Query Cache反而会带来额外的开销，建议关闭该功能（除非表的变更很少并且有很多相同的查询语句）。
2. 不要将Query Cache设置过大，过大的Query Cache会增加维护cache的开销，降低查询性能（几十MB的大小是合适的）。
3. 建议使用SELECT SQL_NO_CACHE/SELECT SQL_CACHE选择性地指定哪些查询不需要/需要被缓存，并测试开启Query Cache前后MySQL的性能变化。
4. 当查询结果被缓存时，最后一个分配的内存块会根据其使用的实际大小进行裁剪，将未使用的空间进行释放。如果查询的结果集很小，并且Qcache_free_blocks和Qcache_lowmem_prunes的值都比较大时，则意味着过多的内存碎片导致查询不断被从cache中删除，可以减小query_cache_min_res_unit的大小；相反，如果查询的结果集很大，可以增加query_cache_min_res_unit的大小。

云数据库 RDS 实例变成只读，无法写入数据（磁盘超限），如何处理？

云数据库 RDS 无法写入数据的原因

云数据库 RDS 实例变成只读无法写入数据可能是由于本地磁盘写满导致的。一般来说，云数据库 RDS 申请的物理空间会稍大于用户购买磁盘空间。目的是为了确保用户能够充分用满购买的磁盘空间，并且为实例日志和监控日志保留一定额外空间。但是本地磁盘空间也不是无限使用的，所以当用户用满购买空间后，为了避免用户数据继续增长导致服务不可用造成更大的影响，您的云数据库 RDS 实例会被锁定。

云数据库 RDS 无法写入数据的排查

当云数据库 RDS 实例被锁定后，控制台会显示实例锁定状态，如下图所示。

实例被锁定后，写权限账号会保留select、delete、drop和show view权限，insert、update等权限会被revoke掉。

云数据库 RDS 无法写入数据的解决方案

这种情况下，需要恢复到正常状态，您可以通过升级套餐获得更大磁盘空间，也可以通过drop table删除无用数据。当实例的数据空间小于您购买的磁盘空间后，系统在5分钟内完成解除锁定恢复到正常状态。

为了您服务的稳定和可用，可以通过添加磁盘监控策略的方式及时关注磁盘使用情况，当磁盘使用量快满的时候及时通过套餐升级或清除无用数据的方式，避免实例被锁定。

数据库前端连接报错的排查方法

在使用云数据库 RDS 数据库过程中，可能会因某些原因无法连接，你可以在前端自查常见错误号，定位问题原因。下面为您提供常见六种连接报错原因及解决方案。

2006: MySQL server has gone away 在执行SQL发起时连接已经断开，常见原因：

1. 空闲连接超时，当SQL发起时，连接已经被MySQL断开； 可以尝试在查询时检测连接是否依然存在，或者新建DB连接。
2. 网络情况不佳致使连接中断。
3. MySQL实例故障/人为重启致使连接中断。

2003： Can't connect to MySQL server on '10.26.211.22' (4)

前端连接MySQL超时，引起部分连接失败，常见原因：

1. 前端机器或者MySQL服务器负载过重导致网卡异常，可自查网卡速率监控以及tcp_overflow、tcp_retran监控项，并联系DBA。
2. 前端机器与MySQL之间网络状况不好，请提供前端机器IP以及访问的数据入口IP给DBA排查机器间网络情况。
3. 网络传输始终有一定丢包概率，建议添加连接重试，重试时间间隔为100ms，保障业务最终连接数据库成功。

Can't connect to MySQL server on '10.26.211.22' (110)

前端连接MySQL超时，常见原因为：

1. 配置的数据库入口错误，导致在规定时间内连接数据库失败，可检查配置信息是否正确。
2. 连接超时时间设置过短。
3. 前端机器与MySQL之间网络状况不好，请排查前端机器网卡速率及overflow等监控并联系DBA排查。

2013：Lost connection to MySQL server during query

SQL在执行过程中连接断开，常见原因：

1. 客户端超时设置，将连接断开。
2. SQL耗时过长，被MySQL服务器大事务监控脚本终止。
3. 前端机器与MySQL之间网络状况不好，请排查前端机器网卡速率及overflow等监控并联系DBA排查。
4. MySQL端连接打满或者账号密码有误，可联系DBA排查。

Lost connection to MySQL server at 'reading initial communication packet',system error: 111

监听队列溢出，常见原因：

1. 大结果集查询，使得监听队列溢出。
2. 网络或机器网卡状况不好，请排查前端机器网卡速率及overflow等监控并联系dba排查。

Lost connection to MySQL server at 'reading initial communication packet', system error: 104

MySQL对应服务器上无此端口，请检查数据库入口配置是否正确，使用MySQL客户端进行测试，若仍然失败请联系DBA。

MySQL 慢查询的排查方法

在用户使用数据库过程中，可能会出现非业务流量高峰导致的内存及CPU使用率激增的想象，这可能是由于某些SQL语句执行时间过长导致的。通过对慢查询日志的排查可以了解数据库系统运行异常，优化SQL语句，提升数据库性能。

什么是慢查询日志

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为1，意思是运行1s以上的语句。

慢查询性能低下的基本原因往往是访问的数据过多；它们执行过程中占用系统资源不释放，甚至由于并发较高、彼此争用资源，使得执行时间较业务低峰期更长，这使得cpu、网络和IO等都承受很大的压力，短时间内导致所有查询执行效率降低，严重的还会导致mysql的crash。

如何开启慢查询日志

重要参数如下：

• slow_query_log 慢查询开启状态。
• slow_query_log_file 慢查询日志存放路径。
• long_query_time 查询超过多少秒才记录。

建议配置如下： [mysqld] slow_query_log = ON slow_query_log_file = /home/mysql/mysql/log/slow.log long_query_time = 1

下面为慢查询日志的一个片段：

# Time: 101126 18:12:06
# User@Host: noah[noah] @ localhost []
# Query_time: 0.001778  Lock_time: 0.000128 Rows_sent: 0  Rows_examined: 235
SET timestamp=1290766326;
SELECT id FROM log_task  WHERE (state = "DONE") AND ( TIME_TO_SEC(TIMEDIFF(NOW(),`refreshTime`)) > 5)  LIMIT 0 , 99999999;

第一行显示了查询记录的时间，第二行显示执行查询的用户。 第三行显示了执行查询需要的时间、在MySQL服务器等待表锁的时间、查询返回的行数，以及查询检查的行数。而后则是所执行的查询。

辅助工具mysqldumpslow查看慢查询统计信息

Mysqldumpslow的用法：

常用的参数的含义如下： -s : 将日志记录进行排序; t,l,r,c和at,al,ar，ac分别是按照query time，lock time,返回的记录数,query次数来排序，前面加了a的是按平均值排序。降序排列。 -a: 不将查询语句中的数字用’N’代替，不将查询语句中的字符串用’S’代替。 -g：仅分析相匹配的query。 -t: 是top n的意思，即为返回前面多少条的数据。 -r：升序排列。

云数据库 RDS 磁盘使用率计算方法和处理方案

用户在使用 MySQL 实例时，会遇到空间使用告警甚至超过实例限额被锁定的情况。用户可以在云数据库 RDS 控制台的实例基本信息中，查看磁盘使用率，如下图所示。当云数据库 RDS 的磁盘使用率超过80%时，用户会收到相关的通知短信，当达到100%后实例被锁定。本文将介绍造成磁盘使用率过高的常见原因及其相应的解决方法。

磁盘使用率计算方法

云数据库 RDS 实例已用磁盘空间包括binlog和数据量。磁盘使用率的计算方法：（binlog大小+数据文件大小）/磁盘套餐大小。

常见原因

造成 MySQL 实例空间使用率过高，主要有如下几种原因： 1、 Binlog文件占用高。 Binlog 文件记录实例的事务信息，是 MySQL 实例同步架构以及高可用性、可恢复性的基础，是不可以关闭的。云数据库 RDS 实例会以一定时间间隔自动清理最近 7天外的 Binlog 文件。 2、数据文件占用高。

查看空间使用状况

您可以通过云数据库 RDS 控制台中的监控中查看实例磁盘空间的使用率曲线。

解决方法

1、升级实例规格 升级实例规格是解决磁盘使用率问题的有效方式。目前，云数据库 RDS 已支持最大存储空间可达到3000TB。建议您将实例规格升级至更高磁盘大小的套餐，升级实力规格的操作步骤如下。 （1）登录 云数据库 RDS 控制台 （2）选择目标实例所在地域。 （3）单击目标实例的ID，进入基本信息页面。 （4）在配置信息栏中，单击变更配置。

（5）选择升级到的磁盘规模，建议同时升级内存大小，单击确认变更。

2、清理数据 对于数据文件占用空间高的情况，可以通过清理数据的方式来减少空间占用情况，可以通过 drop table 和 truncate table 命令来清理不再需要的数据表，用 delete 命令来删除不再需要的数据。

错误信息提示“ERROR 1888……”如何处理？

错误信息提示“ERROR 1888 (HY000): Modify mysql.* tables with DML statement denied, please use other none DML commands instead.”如何处理？

主要原因：为了避免一些用户操作带来的不可恢复的mysql故障，我们没有授予super账号直接修改mysql.*表的权限，当用户开启super权限后，无法通过修改mysql.user表的方式来实现删除账号等操作。 解决方案：删除用户可以使用drop user语句，详细方法见链接。

不能使用语句 CREATE TABLE ... SELECT，怎么办？

因为云数据库 RDS 上面开启了基于GTID一致性检查。所以不能使用CREATE TABLE ... SELECT这样的语句，它是个DDL，不安全。

作为替换可以在创建出表结构后，使用同样功能的DML来插入数据：

• 创建表

    CREATE TABLE `common_house_bak` xxxxx;

• 插入数据

    INSERT INTO `common_house_bak` SELECT * FROM `common_house`;