mysql 面试总结1

背景

mysql复制相关

写在前面
记录部分内容，并且参考官方文档，链接为https://dev.mysql.com/doc/refman/5.7/en/replication-semisync.html
link

MySQL 复制的线程有几个及之间的关联？

MySQL 的复制是基于如下 3 个线程的交互（ 多线程复制里面应该是 4 类线程）：
1. Master 上面的 binlog dump 线程，该线程负责将 master 的 binlog event 传到 slave；
2. Slave 上面的 IO 线程，该线程负责接收 Master 传过来的 binlog，并写入 relay log；
3. Slave 上面的 SQL 线程，该线程负责读取 relay log 并执行；
4. 如果是多线程复制，无论是 5.6 库级别的假多线程还是 MariaDB 或者 5.7 的真正的多线程复制，SQL 线
程只做 coordinator，只负责把 relay log 中的 binlog 读出来然后交给 worker 线程， woker 线程负
责具体 binlog event 的执行；

MySQL 如何保证复制过程中数据一致性及减少数据同步延时？

一致性主要有以下几个方面：

1.在 MySQL5.5 以及之前， slave 的 SQL 线程执行的 relay log 的位置只能保存在文件
（ relay-log.info）里面，并且该文件默认每执行 10000 次事务做一次同步到磁盘， 
这意味着 slave 意 外 crash 重启时， SQL 线程执行到的位置和数据库的数据是不一致的，将导致复制报错，
如果不重搭复制，则
有可能会导致数据不一致。MySQL 5.6 引入参数 relay_log_info_repository，将该参数设置为 TABLE 时，MySQL
将 SQL 线程执行到的位置存到 mysql.slave_relay_log_info 表，这样更新该表的位置和 SQL 线程执行的
用户事务绑定成一个事务，这样 slave 意外宕机后， slave 通过 innodb 的崩溃
恢复可以把 SQL 线程执行到的位置和用户事务恢复到一致性的状态。[LT，注意relay_log_info_repository默认值为file]

2. MySQL 5.6 引入 GTID 复制，每个 GTID 对应的事务在每个实例上面最多执行一次， 这极大地提高了复制
的数据一致性；

3. MySQL 5.5 引入半同步复制， 用户安装半同步复制插件并且开启参数后，设置超时时间，可保证在超时（默认10s）时
间内如果 binlog 不传到 slave 上面，那么用户提交事务时不会返回，直到超时后切成异步复制，但是如果切
成异步之前用户线程提交时在 master 上面等待的时候，事务已经提交，该事务对 master
上面的其他 session 是可见的，如果这时 master 宕机，那么到 slave 上面该事务又不可见了，该问题直到
5.7 才解决；

4. MySQL 5.7 引入无损半同步复制，引入参 rpl_semi_sync_master_wait_point，该参数默认为
after_sync，指的是在切成半同步之前，事务不提交，而是接收到 slave 的 ACK 确认之后才提交该事务，从
此，复制真正可以做到无损的了。

插件运行状态查看：
show global status like 'rpl%';
Rpl_semi_sync_slave_status | ON |

补充

AFTER_SYNC（默认）：源将每个事务写入其二进制日志和副本，并将二进制日志同步到磁盘。
同步后，源将等待事务接收的副本确认。收到确认后，源将事务提交到存储引擎，并将结果返回给客户端，然后客户端可以继续进行。

AFTER_COMMIT：源将每个事务写入其二进制日志和副本中，同步二进制日志，然后将事务提交到存储引擎。
提交后，源将等待事务回执的副本确认。收到确认后，源将结果返回给客户端，然后客户端可以继续进行。

这些设置的复制特征不同，如下所示：

使用AFTER_SYNC，所有客户端将同时看到已提交的事务，这是副本已确认该事务并将其提交到源上的存储引擎之后。因此，所有客户端都在源上看到相同的数据。

如果发生源故障，则已将在源上提交的所有事务复制到副本（保存到其中继日志）。源的意外退出和故障转移到副本是无损的，因为副本是最新的。如上所述，在故障转移之后，不应重用源。

使用AFTER_COMMIT，只有在服务器提交到存储引擎并收到副本确认后，发出事务的客户端才会获得返回状态。在提交之后和副本确认之前，其他客户端可以在提交客户端之前看到提交的事务。

如果出现问题，导致副本无法处理事务，则在源意外退出并故障转移到副本的情况下，此类客户端可能会看到相对于他们在源上看到的数据丢失的数据。

源及其副本之间的半同步复制操作如下：

副本表示连接到源时是否具有半同步功能。

如果在源端启用了半同步复制，并且至少有一个半同步副本，则在源块上执行事务提交的线程将等待直到至少一个半同步副本确认已接收到该事务的所有事件，或者直到发生超时。

仅在事件已被写入其中继日志并刷新到磁盘之后，该副本才确认接收到事务事件。

如果在没有任何副本确认事务的情况下发生超时，则源将恢复为异步复制。赶上至少一个半同步副本时，源将返回半同步复制。

必须在源端和副本端都启用半同步复制。如果在源上禁用了半同步复制，或者在源上启用了半同步复制，但没有任何副本，则源使用异步复制。

延时性：

5.5 是单线程复制， 5.6 是多库复制（对于单库或者单表的并发操作是没用的）， 5.7 是真正意义的多线程
复制，它的原理是基于 group commit， 只要
master 上面的事务是 group commit 的，那 slave 上面也可以通过多个 worker 线程去并发执行。 和
MairaDB10.0.0.5 引入多线程复制的原理基本一样。

Seconds_Behind_Master：复制SQL线程落后于处理源的二进制日志的秒数。
几对字段提供有关副本在从源二进制日志中读取事件并在中继日志中处理事件的过程的信息：

（Master_Log_file， Read_Master_Log_Pos）：源的二进制日志中的坐标，指示复制I / O线程已从该日志读取事件的距离。

（Relay_Master_Log_File， Exec_Master_Log_Pos）：源的二进制日志中的坐标，指示复制SQL线程已执行从该日志接收的事件的距离。

（Relay_Log_File， Relay_Log_Pos）：副本的中继日志中的坐标，指示复制SQL线程已执行中继日志的距离。这些对应于先前的坐标，但以副本的中继日志坐标而不是源的二进制日志坐标表示。

事务是如何通过日志来实现的

基本流程如下：
因为事务在修改页时，要先记 undo，在记 undo 之前要记 undo 的 redo， 然后修改数据页，再记数据页修
改的 redo。 Redo（里面包括 undo 的修改） 一定要比数据页先持久化到磁盘。 当事务需要回滚时，因为有
undo，可以把数据页回滚到前镜像的
状态，崩溃恢复时，如果 redo log 中事务没有对应的 commit 记录，那么需要用 undo 把该事务的修改回滚
到事务开始之前。 如果有 commit 记录，就用 redo 前滚到该事务完成时并提交掉。

cpu 飙升

1.top 命令观察是不是 mysqld 占用导致的, iostat 命令，定位是什么进程在占用 cpu 和磁盘 io；
2.show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在
运行。找出消耗高的 sql，看看执行计划是否准确，index 是否缺失.或者实在是数据量太大造成。
3.一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降
4.有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的
调整，比如说限制连接数等
5、执行 show engine innodb status\G 命令，查看数据库是否有锁资源争用；
6、查看 mysql 慢查询日志，看是否有慢 sql；

mysqldump 和 xtrabackup 实现原理

mysqldump

mysqldump 属于逻辑备份。加入--single-transaction 选项可以进行一致性备份。后台进程会先设置
session 的事务隔离级别为 RR(SET SESSION TRANSACTION ISOLATION LEVELREPEATABLE READ)，
之后显式开启一个事务(START TRANSACTION /*!40100 WITH CONSISTENTSNAPSHOT */)，这样就保证了
该事务里读到的数据都是事务事务时候的快照。之后再把表的数据读取出来。如果加上--master-data=2 的话，
在刚开始的时候还会加一个数据库的读锁
(FLUSH TABLES WITH READ LOCK),等开启事务后，再记录下数据库此时 binlog 的位置(showmaster
status)，马上解锁，再读取表的数据。等所有的数据都已经导完，就可以结束事务

Xtrabackup:
xtrabackup 属于物理备份，直接拷贝表空间文件，同时不断扫描产生的 redo 日志并保存下来。最后完成
innodb 的备份后，会做一个 flush engine logs 的操作(老版本在有 bug，在 5.6 上不做此操作会丢数据)，
确保所有的 redo log 都已经落盘(涉及到事务的两阶段提交
概念，因为 xtrabackup 并不拷贝 binlog，所以必须保证所有的 redo log 都落盘，否则可能会丢最后一组
提交事务的数据)。这个时间点就是 innodb 完成备份的时间点，数据文件虽然不是一致性的，但是有这段时间
的 redo 就可以让数据文件达到一致性(恢复的时候做的事
情)。然后还需要 flush tables with read lock，把 myisam 等其他引擎的表给备份出来，备份完后解锁。

如何从 mysqldump 产生的全库备份中只恢复某一个库、某一张表？

全库备份
 mysqldump -uroot -p --single-transaction -A --master-data=2 >dump.sql
只还原 erp 库的内容
 mysql -uroot -pMANAGER erp --one-database <dump.sql
可以看出这里主要用到的参数是--one-database 简写-o 的参数，极大方便了我们的恢复灵活性

除了默认数据库是命令行上命名的数据库时发生的语句以外，请忽略其他语句。此选项是基本的，应谨慎使用。语句过滤仅基于 USE语句。(只会执行use erp之下的语句，建议使用正则导出单库)

从全库备份中抽取出 t 表的表结构
sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `processliststatus`/!d;q' dump.sql

从全库备份中抽取出 t 表的内容
grep 'INSERT INTO `processliststatus`' dump.sql

端口

检测端口是否运行
lsof -i:3306
netstat -tunlp|grep 3306
ss -tulnp|grep 3306

解决问题思路

一些必要准备：
1.gtid  为了高可用切换
2.Mysql5.6  5.7并行复制  区别，5.7更智能，配合组提交更容易解决延迟。因为5.6是库级别的
3.binlog server（不会重连） 在主库重启之后，需要检查是否中断。如果是从库会检查10次。
4.after commit和after sync
5.Mysql5.6主commit 程序看到，如果没有传到从。发生主从切换，程序会任务是数据丢失。5.7的sync,  主会有一个准备，sync，commit的过程。刷入磁盘时，先标记为待提交，发生到从库，得到ack确认后，才真正提交。真正提交之前程序是看不到数据的。
6.Mysql5.7的增加半同步+组提交+两阶段提交

本文说明，主要技术内容来自互联网技术大佬的分享，还有一些自我的加工(仅仅起到注释说明的作用)。如有相关疑问，请留言，将确认之后，执行侵权必删​