mysql学习笔记及备忘录

最新推荐文章于 2022-01-10 10:39:21 发布

leiax00

最新推荐文章于 2022-01-10 10:39:21 发布

阅读量324

点赞数

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/qq_35566365/article/details/107044590

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

苦海无边

A.mysql常用命令
B.mysql配置解析
- 配置参数
C.mysql知识点解析
D.mysql问题汇总

基于mysql8.0.2

A.mysql常用命令

查询版本
mysql -v 客户端未连接
select version();，\s，status; 客户端已连接
查询隔离级别
show variables like '%isolation%'; 通用方法
select @@transaction_isolation; v8.0.2
select @@tx_isolation; 老一点的版本，具体不清楚啊，略惆怅
修改隔离级别
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL { REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE }
查询当前数据库：SELECT database();
查询所有数据库：show databases;
使用数据库a： use a;
开启一个事务：START TRANSACTION WITH CONSISTENT SNAPSHOT;，begin;
查询当前会话是否自动提交：select @@autocommit;，show variables like 'autocommitt;'
设置自动提交状态：set autocommit=[0,1];，0：不自动；1：自动
新增列：alter table xxxx add column c_name c_type c_default;
设置内存临时表大小：set tmp_table_size=1024;
设置排序buffer的大小：set sort_buffer_size=32768;
设置排序字段长度：set max_length_for_sort_data=16;
打开optimizer_trace：SET optimizer_trace='enabled=on';
查看 OPTIMIZER_TRACE 输出：SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
显示线程情况（db锁上情况处理）：show [FULL] processlist;
查看当前事务状态：select * from information_schema.INNODB_TRX; – 查询结果列trx_mysql_thread_id：事务中的线程id
服务端断开连接：kill connection $id, 服务端断开需要客户端发起下一个请求才能感知；
慢日志开启状态查询：show variables like 'slow_query_log';
慢日志开启：

# sql修改：
mysql> set global slow_query_log=ON;
mysql> set global slow_query_log_file='/var/lib/mysql/slow.log';
# 设置超过5秒就记录
mysql> set global long_query_time=5

# 修改配置文件的方式：
> sudo vim /usr/local/mysql/my.cnf
# 添加慢查询日志
log_output=file
slow_query_log=on
slow_query_log_file = /tmp/mysql-slow.log
log_queries_not_using_indexes=on
long_query_time = 1
# reboot mysql
> service mysql restart
> /etc/inint.d/mysql restart

binlog日志格式：show global variables like '%binlog_format%'; / select @@binlog_format; – [statement, row, mixed]
将 master.000001 文件里面从第 2738 字节到第 2973 字节中间这段内容解析出来，放到 MySQL 去执行: mysqlbinlog master.000001 --start-position=2738 --stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;
查看备机状态：show slave status – seconds_behind_master:延迟于主机的时间（秒）
设置B为A的从库：

CHANGE MASTER TO 
MASTER_HOST=$host_name 
MASTER_PORT=$port 
MASTER_USER=$user_name 
MASTER_PASSWORD=$password 
master_auto_position=1 

# master_auto_position: 1表示主备关系使用 GTID协议

查询数据并保存结果到本地：mysql -h$host -P$port -u$user -p$pwd -e "select * from db1.t" > $target_file
立刻开始事务：START TRANSACTION WITH CONSISTENT SNAPSHOT;
导出表到csv：select * from db1.t where a>900 into outfile '/server_tmp/t.csv';, 保存在服务端，secure_file_priv生成的基础路径，为null,不允许导出；为empty，不限制导出位置。
csv导入表：load data infile '/server_tmp/t.csv' into table db2.t;

B.mysql配置解析

配置参数

binlog

1. write 和 fsync 时机控制 -- sync_binlog：
    write: 把binlog写入到文件系统的page-cache中，不持久化
    fsync：持久化binlog到磁盘
        a. sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；
        b. sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
        c. sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。
2. binlog组提交控制参数：
    a. binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用 fsync;
    b. binlog_group_commit_sync_no_delay_count 参数，表示累积多少次以后才调用 fsync。
    参数关系：条件a || 条件b

redo-log

1. 写入策略 -- innodb_flush_log_at_trx_commit：
    a. 设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;
    b. 设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；
    c. 设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。

备库执行 relay log 后生成 binlog: log_slave_updates=ON
slave_parallel_workers: relay log中转日志处理线程数，建议[8, 16]
slave_skip_errors: 主备切换控制跳过错误的参数，这个参数可以设置为“1032,1062”；1062 错误是插入数据时唯一键冲突；1032 错误是删除数据时找不到行。
开启GTID模式（global transaction identifier）:gtid_mode=on, enforce_gtid_consistency=on; (事务提交时分配id，通常是连续的)
恢复GTID的默认分配行为：set gtid_next=automatic
客户端可获取更新的GTID: session_track_gtids=OWN_GTID, 然后通过 API 接口 mysql_session_track_get_first 从返回包解析出 GTID 的值即可
控制Innodb的并发线程上限：innodb_thread_concurrency=N; eg: set global innodb_thread_concurrency=3;
innodb_buffer_pool_size: 设置InnoDB Buffer Pool 的大小，一般为物理内存的60% ~ 80%；

C.mysql知识点解析

1.count(*)工作原理

对于innodb，它是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。对于 count(*) 这样的操作，MySQL 优化器会找到最小的那棵树来遍历。

不记录表数据行数的原因：innodb的事务设计是通过多版本并发控制（MVCC）来实现的；每一行都需要判断自己是否对会话可见，因此需要一条一条的去读出判断

show table status命令查询，结果中有字段 TABLE_ROWS 来显示表当前行数。该命令查询很快，但注意：该值为采样估算值，不可直接使用；官方误差可能达到40%到50%

几种计数效率：count(字段)<count(主键 id)<count(1)≈count()
count()专门做了优化，并不会把全部字段取出来，count(*) 肯定不是 null，按行累加。
count(字段)需要对字段进行非空判断，不为空才累加；
count(1)不需要对数据进行解析，按行累加；

count(*)是一个耗时操作，获取计数的解决方案：

缓存系统计数：通过缓存来对数据的增减进行计数，缺点： 可能导致计数误差；— 多系统协调的弊端，当然在正常情况下，可以保证最终一致性；
数据库保存计数：建立专门的统计表通过事务来保证一致性；也可以通过触发器来完成该动作；

2.Order by工作原理

order by 分为 全字段排序 和 rowId排序：

全字段排序
会根据排序字段的索引，获取到要查询的字段，然后放入sort buffer中进行排序；当数据量较大时(由参数 sort_buffer_size 控制)会通过临时文件拆分的方式（分治，归并）来进行排序；
在索引建立时，可以联合查询字段进行索引创建，可以减少回表；同时可能让符合条件的数据本身即为有序的，则不需要排序；这极大地提升了性能；

-- 示例：
-- 索引 index(city, name, age)，天然有序
select city,name,age from t where city='杭州' order by name limit 1000  ;

rowId排序
当 排序行数据长度 > max_length_for_sort_data 时，采用排序字段 + id 的方式放入sort buffer排序；再通过回表的方式将最终结果返回客户端；

确认是否使用临时文件：

/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000; 

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
-- number_of_tmp_files 表示的是，排序过程中使用的临时文件数
-- examined_rows 表示参与排序的行数
-- sort_mode > packed_additional_fields 在排序过程中还是要按照实际长度来分配空间的。

/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 计算Innodb_rows_read差值:表示整个执行过程扫描行数， innodb值会比实际值多1 */
select @b-@a;

3. 共享锁与排它锁

共享锁(S锁):如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。
排它锁(X锁)：如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。
共享锁和排他锁都属于悲观锁。排他锁又可以可以分为行锁和表锁。
MySQL常用的两种引擎MyISAM和InnoDB，MyISAM默认使用表锁，InnoDB默认使用行锁。
注意：使用InnoDB引擎，如果筛选条件里面没有索引字段，就会锁住整张表，否则的话，锁住相应的行。

# 两种加锁（共享锁，排它锁）的方式：对于不存在的行查询，均会对查询行所在区间加上间隙锁；间隙锁之间不互斥，
# 间隙锁与插入行互斥；对于存在的行，排它锁下其他事务不能加锁查询、修改，共享锁允许加锁查询但不允许修改；
# 共享锁和共享锁不互斥，多个事务获取共享锁时可读不可写；排它锁和任何锁都互斥，获取到排它锁，能读能写；
select ... for update;
select ... lock in share mode;

4. 结果集的保存及取发

MySQL 是“边读边发的”，这个概念很重要。这就意味着，如果客户端接收得慢，会导致 MySQL 服务端由于结果发不出去，这个事务的执行时间变长。

server层的影响 -> 服务端并不需要保存一个完整的结果集。取数据和发数据的流程是这样的：

获取一行，写到 net_buffer 中。这块内存的大小是由参数 net_buffer_length 定义的，默认是 16k。
重复获取行，直到 net_buffer 写满，调用网络接口发出去。
如果发送成功，就清空 net_buffer，然后继续取下一行，并写入 net_buffer。
如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待。直到网络栈重新可写，再继续发送。

知识点：

一个全表查询在发送过程中，占用的 MySQL 内部的内存最大就是 net_buffer_length 这么大，并不会达到全表大小；
socket send buffer 也不可能达到全表大小（默认定义 /proc/sys/net/core/wmem_default），如果 socket send buffer 被写满，就会暂停读数据的流程。

引擎innodb层的影响 -> Buffer Pool加速查询，Buffer Pool淘汰策略为LRU(淘汰最久未使用的数据);
在 InnoDB 实现上，按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域。图中 LRU_old 指向的就是 old 区域的第一个位置，是整个链表的 5/8 处。也就是说，靠近链表头部的 5/8 是 young 区域，靠近链表尾部的 3/8 是 old 区域。
InnoDB淘汰old区域的页，同时新加入的页也放入old区域；下次访问时会进行判断该页存在时间超过一秒（控制参数：innodb_old_blocks_time），才移动到young区域；

5. join语句分析与优化

使用 join 语句，性能比强行拆成多个单表执行 SQL 语句的性能要好（减少了网络交互）；
如果使用 join 语句的话，需要让小表做驱动表。（驱动表需要遍历，而被驱动表可以通过索引快速定位，因此遍历小表更好）

第一个问题：能不能使用 join 语句？

如果可以使用 Index Nested-Loop Join 算法（NLJ），也就是说可以用上被驱动表上的索引，其实是没问题的；
如果使用 Block Nested-Loop Join 算法(BNL)，扫描行数就会过多。尤其是在大表上的 join 操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种 join 尽量不要用。所以你在判断要不要使用 join 语句时，就是看 explain 结果里面，Extra 字段里面有没有出现“Block Nested Loop”字样。

第二个问题是：如果要使用 join，应该选择大表做驱动表还是选择小表做驱动表？

如果是 Index Nested-Loop Join 算法（被驱动表命中索引），应该选择小表做驱动表；
如果是 Block Nested-Loop Join 算法（被驱动表未命中索引）：在 join_buffer_size 足够大的时候，是一样的；在 join_buffer_size 不够大的时候（这种情况更常见），应该选择小表做驱动表。所以，这个问题的结论就是，总是应该使用小表做驱动表。

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

Multi-Range Read 优化

Multi-Range Read 优化 (MRR)主要目的：尽量使用顺序读盘。
通过普通索引范围查询时，会进行回表；而回表的过程是一行一行读取的；这个过程即为随机读，性能较差；
MRR 优化的设计思路：将满足条件的id放入read_rnd_buffer中，然后进行排序，最后再进行记录查询，返回结果；
read_rnd_buffer 的大小是由 read_rnd_buffer_size 参数控制的;若buffer满了，会进行排序查询，然后清空继续重复操作；
想要稳定地使用 MRR 优化的话，需要设置set optimizer_switch="mrr_cost_based=off"；
启动优化，在explain语句时， Extra中会有Using MRR标记；

Batched Key Access算法 – NLJ算法优化

启动方法：set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
通过join buffer缓存驱动表的部分数据, 然后批量传给被驱动表进行匹配，从而提高效率；

BNL优化

BNL 算法对系统的影响主要包括三个方面：

可能会多次扫描被驱动表，占用磁盘 IO 资源；
判断 join 条件需要执行 M*N 次对比（M、N 分别是两张表的行数），如果是大表就会占用非常多的 CPU 资源；
可能会导致 Buffer Pool 的热数据被淘汰，影响内存命中率。（冷数据加载淘汰掉热数据）
优化方法：被驱动表，根据条件创建带索引的临时表，再进行join操作，转换为BKA算法；

hash-join优化

理论上，这个过程会比临时表方案的执行速度还要快一些.
mysql不支持hash join，但可以在业务中自己实现：

select * from t1;取得表 t1 的全部 1000 行数据，在业务端存入一个 hash 结构，比如 hashset, hashmap。
select * from t2 where b>=1 and b<=2000; 获取表 t2 中满足条件的 2000 行数据。
把这 2000 行数据，一行一行地取到业务端，到 hash 结构的数据表中寻找匹配的数据。满足匹配的条件的这行数据，就作为结果集的一行。

6. group by优化原则

如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null；
尽量让 group by 过程用上表的索引，确认方法是 explain 结果里没有 Using temporary 和 Using filesort；
如果 group by 需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大 tmp_table_size 参数，来避免用到磁盘临时表；
如果数据量实在太大，使用 SQL_BIG_RESULT 这个提示，来告诉优化器直接使用排序算法得到 group by 的结果。

内存临时表不够的情况下会使用磁盘临时表，磁盘临时表默认引擎是Innodb, 是B+树结构，有排序；

D.mysql问题汇总

1.解析语句：insert into `tableA` (A, B, C) values (B, A, 2) on duplicate key update C=C | 2;

insert … on duplicate 语句，确保了在事务内部，执行了这个 SQL 语句后，就强行占住了这个行锁，
确保了之后的操作是在行锁保护下的读操作。
当有两个事务同时插入时，保证当前事务的值为俩事务的按位或值；

2.为什么varchar字段长度常设置成功255

官方概念：VARCHAR类型的值存储格式为1到2个字节的长度前缀加上数据内容。长度前缀表示数据内容的字节数。当数据所需最大长度不超过255字节时，长度前缀为1个字节，当数据所需最大长度超过255个字节时，长度前缀为2个字节。
原因：我们知道 1byte = 8bit；刚好够用来表示[0, 255]，因此长度超过255时，需要多一个字节来表示长度；为了节省空间，常设置长度为不大于255的值；

3. 对索引字段做函数计算后，无法使用对应索引

以下表中t_modified类型为 datetime，是索引字段；表中插入数据1w条；有函数的sql，是使用覆盖索引遍历了所有值；而范围查询仅查询了一部分，运用了索引的搜索功能；

explain select count(*) from jk18_tradelog where month(t_modified) = 7;

结果如下：
在这里插入图片描述
对于范围查询的方式（无函数调用）：

explain
select count(*)
from jk18_tradelog
where (t_modified >= '2019-7-1' and t_modified < '2019-8-1')
   or (t_modified >= '2020-7-1' and t_modified < '2020-8-1');

结果如下：
在这里插入图片描述

4. 字符字段与数字比较的效率问题

当一个varchar字段和一个数字比较时，会讲字段转换为数字类型然后进行比较；这时如果字段有索引，则无法用上；等价于给字段进行了函数转换

select 10 > 9; # return 1
select '10' > 9;  # return 1
select '10' > '9';  # return 0

5. 关联表时未使用字段的索引

如果两个表的字符集不一致的话，可能导致这种情况：例如一个表字符集为utf8mb4，一个表字符集为utf8；utf8mb4 是 utf8 的超集。类似地，在程序设计语言里面，做自动类型转换的时候，为了避免数据在转换过程中由于截断导致数据错误，也都是“按数据长度增加的方向”进行转换的。
因此，连接过程中要求在被驱动表的索引字段上加函数操作，是直接导致对被驱动表做全表扫描的原因。
注：从A表获取关联字段，到B表去匹配查询，则A表为驱动表，B表未被驱动表

# $L2.tradeid.value 的字符集是 utf8mb4; trade_detail字符集是 utf8
select * from trade_detail where tradeid=$L2.tradeid.value; 
# 等价于
select * from trade_detail  where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;

6. 慢查询性能问题解决方案

索引没设计好
备库中执行set sql_log_bin=off(不写 binlog)，然后执行alter table语句添加索引，然后主备切换再重复操作；
sql语句没写好
重写sql

# 添加语句重写规则
mysql> insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) values ("select * from t where id + 1 = ?", "select * from t where id = ? - 1", "db1");
call query_rewrite.flush_rewrite_rules();

mysql选错索引
应急方案：添加force index，通过重写sql来引导mysql对索引的选取；

7. 并发连接与并发查询

innodb_thread_concurrency: 并发连接数控制参数； 0: 无限制；通常设置为[64, 128]，对应并发查询数；
并发连接：应用层获取的连接数，show processlist命令查询结果中的连接数；并发连接数达到几千个影响并不大，就是多占一些内存而已；
并发查询：当前正在执行的语句数量；
注：在线程进入锁等待状态时，并发查询的占有数量会减一；避免热点更新时的死锁。

8. 死锁怎么诊断

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

# sql1
begin;
select id from t where c in(5,20,10) lock in share mode;
# sql2
select id from t where c in(5,20,10) order by c desc for update;

死锁出现后，执行 show engine innodb status 可得到部分输出； LATESTDETECTED DEADLOCK部分记录了最后一次死锁的信息；
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gwJZVzLH-1595815847652)(./images/wait_lock_analysis.png)]
在这里插入图片描述
这个结果分成三部分：

TRANSACTION，是第一个事务的信息；
TRANSACTION，是第二个事务的信息；
WE ROLL BACK TRANSACTION (1)，是最终的处理结果，表示回滚了第一个事务。

第一个事务的信息中：

WAITING FOR THIS LOCK TO BE GRANTED，表示的是这个事务在等待的锁信息；
index c of table test.t，说明在等的是表 t 的索引 c 上面的锁；
lock mode S waiting 表示这个语句要自己加一个读锁，当前的状态是等待中；
Record lock 说明这是一个记录锁；
n_fields 2 表示这个记录是两列，也就是字段 c 和主键字段 id；
0: len 4; hex 0000000a; asc ;; 是第一个字段，也就是 c。值是十六进制 a，也就是 10；
1: len 4; hex 0000000a; asc ;; 是第二个字段，也就是主键 id，值也是 10；
这两行里面的 asc 表示的是，接下来要打印出值里面的“可打印字符”，但 10 不是可打印字符，因此就显示空格。
第一个事务信息就只显示出了等锁的状态，在等待 (c=10,id=10) 这一行的锁。
当然你是知道的，既然出现死锁了，就表示这个事务也占有别的锁，但是没有显示出来。别着急，我们从第二个事务的信息中推导出来。

第二个事务显示的信息要多一些:

“ HOLDS THE LOCK(S)”用来显示这个事务持有哪些锁；
index c of table test.t 表示锁是在表 t 的索引 c 上；
hex 0000000a 和 hex 00000014 表示这个事务持有 c=10 和 c=20 这两个记录锁；
WAITING FOR THIS LOCK TO BE GRANTED，表示在等 (c=5,id=5) 这个记录锁。

结论：

由于锁是一个个加的，要避免死锁，对同一组资源，要按照尽量相同的顺序访问；
在发生死锁的时刻，for update 这条语句占有的资源更多，回滚成本更大，所以 InnoDB 选择了回滚成本更小的 lock in share mode 语句，来回滚

9. 误删数据的解决方案

误删分类：
    1. 使用 delete 语句误删数据行；
    2. 使用 drop table 或者 truncate table 语句误删数据表；
    3. 使用 drop database 语句误删数据库；
    4. 使用 rm 命令误删整个 MySQL 实例。误删行

a.误删行

使用 Flashback 工具通过闪回把数据恢复回来；
原理：修改binlog，拿回原库重放。前提：需要确保 binlog_format=row 和 binlog_row_image=FULL;
操作方法：

对于 insert 语句，对应的 binlog event 类型是 Write_rows event，把它改成 Delete_rows event 即可；
对于 delete 语句，也是将 Delete_rows event 改为 Write_rows event；
对于 Update_rows 语句，binlog 里面记录了数据行修改前和修改后的值，对调这两行的位置即可。
注：如果要重放多个事务，事务顺序需要倒置，eg: A->B->C，重放则是：C->B->A; 同时，恢复操作不要直接在主库执行，可恢复一个备份，或者找一个从库作为临时库，在临时库上执行，待确认正确后再恢复回主库。

预防措施：

设置 sql_safe_updates=ON, 不允许 delete和update语句不写where条件，或者 where 条件里面没有包含索引字段；
上线前，做sql审计；

b.误删库/表

这种情况需要使用：全量备份 + 增量日志 的方式来恢复；
要求：线上有定期的全量备份，并实时备份binlog；
操作方法：

取最近一次全量备份，假设这个库是一天一备，上次备份是当天 0 点；
用备份恢复出一个临时库；
从日志备份里面，取出凌晨 0 点之后的日志；
把这些日志，除了误删除数据的语句外，全部应用到临时库。

使用说明：

为了加速数据恢复，如果这个临时库上有多个数据库，你可以在使用 mysqlbinlog 命令时，加上一个–database 参数，用来指定误删表所在的库。这样，就避免了在恢复数据时还要应用其他库日志的情况。
在应用日志的时候，需要跳过 12 点误操作的那个语句的 binlog：
a. 如果原实例没有使用 GTID 模式，只能在应用到包含 12 点的 binlog 文件的时候，先用–stop-position 参数执行到误操作之前的日志，然后再用–start-position 从误操作之后的日志继续执行；
b. 如果实例使用了 GTID 模式，就方便多了。假设误操作命令的 GTID 是 gtid1，那么只需要执行 set gtid_next=gtid1;begin;commit; 先把这个 GTID 加到临时实例的 GTID 集合，之后按顺序执行 binlog 的时候，就会自动跳过误操作的语句。

加速方法：在用备份恢复出临时实例之后，将这个临时实例设置成线上备库的从库，这样：

在 start slave 之前，先通过执行change replication filter replicate_do_table = (tbl_name) 命令，就可以让临时库只同步误操作的表；
这样做也可以用上并行复制技术，来加速整个数据恢复过程
当时间太久，备库上的binlog已经删除临时实例需要的信息时，需要从binlog备份系统中找到需要的binlog，再放回备库中；

binlog放回备库的操作方法：

从备份系统获取需要的备份文件，eg:master.000005，放回备库的日志目录；
打开日志目录下的 master.index 文件，在文件开头加入该文件：./master.000005;
重启备库，目的是要让备库重新识别这两个日志文件；
现在这个备库上就有了临时库需要的所有 binlog 了，建立主备关系，就可以正常同步了。

延迟复制备库：通过CHANGE MASTER TO MASTER_DELAY = N命令设置这个备库持续保持跟主库有 N 秒的延迟。在N秒内反应过来，到这个备库上执行 stop slave，再通过之前介绍的方法，跳过误操作命令，就可以恢复出需要的数据。

预防措施：

账号分离。这样做的目的是，避免写错命令。比如：
a. 只给业务开发同学 DML 权限，而不给 truncate/drop 权限。而如果业务开发人员有 DDL 需求的话，也可以通过开发管理系统得到支持。
b. DBA 团队成员，日常也都规定只使用只读账号，必要的时候才使用有更新权限的账号。
制定操作规范。这样做的目的，是避免写错要删除的表名。比如：
a. 在删除数据表之前，必须先对表做改名操作。然后，观察一段时间，确保对业务无影响以后再删除这张表。
b. 改表名的时候，要求给表名加固定的后缀（比如加 _to_be_deleted)，然后删除表的动作必须通过管理系统执行。并且，管理系删除表的时候，只能删除固定后缀的表

c. rm删除数据

对于粗粒度的删除数据可以轻松恢复，只要不是恶意地把整个集群删除，而只是删掉了其中某一个节点的数据的话，HA 系统就会开始工作，选出一个新的主库，从而保证整个集群的正常工作。
而由系统级别的原因导致整个 MySQL 集群的所有节点都全军覆没的情况，例如（SA（系统管理员）执行一个批量下线机器的操作），只能是说尽量把你的备份跨机房，或者最好是跨城市保存。

10. kill不掉语句

kill query  线程id 表示终止这个线程中正在执行的语句；
kill connection  线程id (connection可省略) 表示断开这个线程的连接，当然如果这个线程有语句正在执行，也是要先停止正在执行的语句的

kill语句，并没有强行停止目标线程的执行，而只是设置了个状态，并唤醒对应的线程。而被 kill 的线程，需要执行到判断状态的“埋点”，才会开始进入终止逻辑阶段。并且，终止逻辑本身也是需要耗费时间的。
eg：还要包括对事物的回滚，mysql最大连接线程数等等的限制；kill无效的情况：

线程没有执行到判断线程状态的逻辑
终止逻辑耗时较长
a. 超大事务执行期间被 kill。这时候，回滚操作需要对事务执行期间生成的所有新数据版本做回收操作，耗时很长。
b. 大查询回滚。如果查询过程中生成了比较大的临时文件，加上此时文件系统压力大，删除临时文件可能需要等待 IO 资源，导致耗时较长。
c. DDL 命令执行到最后阶段，如果被 kill，需要删除中间过程的临时文件，也可能受 IO 资源影响耗时较久。

11. 自增组件的连续性问题

自增组件不一定是连续的；可能导致的情况：

插入数据，表存在唯一索引，且插入数据唯一键冲突；（分配id不会回收）
插入数据，事务失败回滚；（分配id不会回收）
插入数据跳id指定固定值，maxId - 指定id之间的id值被跳过；
id申请策略：对于批量插入，同一个语句去申请自增 id，每次申请到的自增 id 个数都是上一次的两倍；

涉及参数：innodb_autoinc_lock_mode

这个参数的值被设置为 0 时，表示采用之前 MySQL 5.0 版本的策略，即语句执行结束后才释放锁；
这个参数的值被设置为 1 时：
a. 普通 insert 语句，自增锁在申请之后就马上释放；
b. 类似 insert … select、replace … select 和 load data 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；
这个参数的值被设置为 2 时，所有的申请自增主键的动作都是申请后就释放锁。在生产上，尤其是有 insert … select 这种批量插入数据的场景时，从并发插入数据性能的角度考虑，我建议你这样设置：innodb_autoinc_lock_mode=2 ，并且 binlog_format=row. 这样做，既能提升并发性，又不会出现数据一致性问题