mysql，DBA面试题——2024年12月整理

一.mysql主从复制涉及到那几个线程？

1.主线程（Main Thread）：也称为主服务器线程，负责处理客户端的连接请求，并管理其他复制相关的线程。

2.二进制日志读取线程（Binary Log Reader Thread）：该线程在主服务器上运行，负责读取主服务器上的二进制日志，并将读取到的日志事件发送给从服务器。

3.I/O 线程（I/O Thread）：从服务器上的 I/O 线程包括两个子线程：复制 I/O 线程和复制 SQL 线程。

复制 I/O 线程（Replication I/O Thread）：该线程在从服务器上运行，负责与主服务器建立连接，并从主服务器上读取二进制日志事件数据。它将这些事件写入从服务器的中继日志文件（Relay Log）中。

复制 SQL 线程（Replication SQL Thread）：该线程在从服务器上运行，负责读取中继日志文件中的日志事件，并在从服务器上执行这些事件，以完成对数据的复制操作。

状态线程（Status Thread）：负责收集并报告主从复制的状态信息，例如复制延迟、同步状态等。

这些线程在主从复制过程中各自承担不同的任务，通过协同工作，实现了主服务器上数据的复制到从服务器的功能。主从复制是一种常用的数据库架构方式，可以提高数据的可用性、容灾性和读写分离能力。

二.列出一些mysql备份常用的工具？
1.mysqldump：这是 MySQL 自带的命令行工具，可以通过导出 SQL 语句的方式备份数据库。它可以备份整个数据库、单个表或者特定的数据。使用简单，适用于小规模的数据库备份。

2.Percona XtraBackup：这是一个免费开源的物理备份工具，它可以实现 MySQL 数据文件级别的增量备份和恢复。它不会对数据库进行锁定，因此可以在备份的同时进行数据库的正常读写操作。

3.MySQL Enterprise Backup：这是 MySQL 官方提供的商业备份工具，支持物理备份和逻辑备份。它提供了诸如并行备份、增量备份、压缩等高级功能，并且与 MySQL 服务器无缝集成。

4.Percona Toolkit：这是 Percona 公司提供的一套开源工具集，其中包含了多个 MySQL 数据库备份和恢复工具，如 innobackupex（基于 XtraBackup）、pt-mysql-summary、pt-duplicate-key-checker 等。这些工具提供了丰富的功能和选项，可用于快速备份、恢复和管理 MySQL 数据库。

5.LVM（Logical Volume Manager）：这是一种 Linux 系统层面的备份方法，可以通过创建逻辑卷快照（LVM Snapshot）的方式来备份 MySQL 数据库。它具有快速、高效的特点，适用于大规模数据库。

三. 列出mysql包含那些日志，并简述每种日志的作用？
1.重做日志（Redo Log）：重做日志记录了对数据库所做的修改操作，包括插入、更新和删除操作。其主要作用是提供崩溃恢复机制，当数据库发生崩溃时，可以使用重做日志将数据恢复到崩溃前的状态。重做日志是循环写入的，在事务提交之前，修改的操作会首先被写入重做日志，然后再写入磁盘上的数据文件。
2.回滚日志（Undo Log）：回滚日志记录了对于事务进行的修改操作，可以用于事务的回滚或者回滚段的恢复过程。当某个事务需要回滚时，可以使用回滚日志来撤销该事务已经做出的修改操作，以保证数据的完整性。回滚日志也是循环写入的，只在事务执行期间存在，并在事务提交或回滚后释放。
3.二进制日志（Binlog）：二进制日志记录了对数据库的修改操作，包括数据更改语句和数据定义语句。其主要作用是用于数据库的备份、复制和恢复。通过解析和执行二进制日志，可以将数据库在不同服务器之间进行数据复制或同步，并且可以使用二进制日志进行点播恢复（例如将数据库恢复到指定的时间点）。
4.慢查询日志（Slow Query Log）：慢查询日志记录了执行时间超过一定阈值的SQL语句。它可以帮助开发人员和管理员识别并优化执行缓慢的查询，提高数据库的性能和响应速度。
5.错误日志（Error Log）：错误日志记录了MySQL服务器在运行过程中出现的重要错误和警告信息，如启动、关闭、数据库错误等。通过查看错误日志，可以快速发现并解决数据库的问题

四  简述mysql崩溃恢复的机制
1.重做日志（Redo Log）：MySQL使用重做日志来记录在事务提交之前对数据库所做的更改。当发生崩溃时，MySQL可以通过重做日志来重新应用这些更改，以使数据库恢复到崩溃之前的状态。重做日志是一种持久性的记录，在崩溃后可以保持数据一致性。
2.回滚日志（Undo Log）：MySQL使用回滚日志来记录对于事务进行了哪些修改，以便在事务回滚或回滚段的恢复过程中使用。回滚日志可以撤销未提交的事务的更改，从而保护数据的完整性。
3.检查点（Checkpoint）：MySQL定期创建检查点，将内存中的脏数据刷新到磁盘上的数据文件。检查点允许在崩溃发生时，从检查点开始进行恢复，而不是从日志的开始部分进行重做。这样可以加快恢复速度。
4.崩溃恢复：当MySQL发生崩溃时，它会根据存储引擎的不同，执行相应的崩溃恢复操作。通常情况下，MySQL首先会检查重做日志，并根据其中的信息来重做未完成的事务。然后，MySQL会检查回滚日志，并使用它来回滚未提交的事务的更改。最后，MySQL会执行检查点恢复，将内存中的脏数据刷新到磁盘上的数据文件，以确保数据的一致性。

通过这些机制，MySQL能够在发生崩溃时，保证数据的完整性和一致性，并尽可能地恢复到崩溃之前的状态。同时，合理的配置和管理崩溃恢复相关的参数，可以进一步提高MySQL的可靠性和性能。

五  误删表后，如何将表恢复到drop前的时刻？
1.使用备份：如果在删除表之前进行了备份，则可以从备份文件中还原被删除的表。首先，找到最近的有效备份，并将其恢复到另一个数据库或不同的表名。然后，使用INSERT INTO ... SELECT语句从恢复的表中选择数据，并将其插入到原来的表中。

2.使用Binlog：MySQL的二进制日志（binlog）记录了对数据库所做的所有修改操作，包括删除表。可以通过解析和执行binlog来还原被删除的表。首先，找到删除表之前的binlog文件和位置。然后，使用mysqlbinlog工具解析binlog文件并生成SQL语句。找到删除表的SQL语句，并将其修改为CREATE TABLE语句，然后执行该语句来重新创建表。

3.使用第三方工具：有一些第三方工具可以帮助恢复被删除的表，如Undrop for InnoDB和Recover MySQL Tables。这些工具可以解析数据库文件，查找被删除的表的数据，并尝试将其恢复。使用这些工具需要小心，确保在操作之前进行适当的备份，以免造成进一步的数据损失。

无论使用哪种方法，都建议在操作之前创建数据库的备份，以防止进一步的数据丢失。此外，及时采取措施非常重要，以避免过多的写操作导致数据被覆盖。如果没有备份或无法成功恢复表，则可能需要依赖专业的数据库恢复服务来尝试从物理介质上恢复数据。

六  简述一句sql查询语句在mysql中的执行过程？
在 MySQL 中，一条 SQL 查询语句的执行过程通常包括：查询解析、查询优化、执行计划生成、数据读取和返回结果。

七 列出mysql的系统库有哪些？
1.information_schema：这是一个存储关于数据库、表、列、索引等元数据信息的系统数据库。可以通过查询该库的表获取数据库结构和统计信息。

2.mysql：这是存储 MySQL 用户和权限相关信息的系统数据库。其中包含了用户表、权限表等，用于管理和控制数据库的访问权限。

3.performance_schema：这是一个用于收集和展示 MySQL 数据库性能指标的系统数据库。它提供了各种性能监控表，可以用于分析和优化数据库性能。

4.sys：这是一个用于展示和分析 MySQL 实例状态和性能的系统库。它提供了一系列视图和函数，可以方便地查询和监控数据库的运行情况。

除了上述常见的系统库之外，还有一些其他的系统库，如ndbinfo（适用于 MySQL Cluster）、performance_schema（MySQL 8.0 版本以前的版本中使用）、sysbench（性能测试工具所使用的库）等。每个系统库都有其特定的功能和用途，用于支持数据库的运行、管理和性能分析。

七  列出mysql常见的高可用架构？
1.主从复制（Master-Slave Replication）：这是MySQL的基本高可用方案，通过设置一个主数据库（Master）和一个或多个从数据库（Slaves）来实现。主数据库负责处理写操作，并将数据同步到从数据库，从数据库用于读取查询。主从复制可以提高读取性能和冗余备份，但主数据库宕机时需要手动切换。

2.主主复制（Master-Master Replication）：在主从复制的基础上进一步扩展，允许多个主数据库进行互相复制。主主复制可以实现读写分离、负载均衡和故障转移，但需要注意解决双写冲突和数据同步延迟的问题。

3.MySQL Cluster：MySQL Cluster是一种基于共享存储的分布式数据库解决方案，通过将数据分片存储在多个节点上实现数据的高可用和并行处理。MySQL Cluster提供了自动故障检测和自动恢复机制，支持高并发和高可扩展性。

4.视图集群（Clustered Views）：这是一种通过将多个MySQL实例组成一个逻辑集群来实现高可用性。视图集群使用代理软件处理数据库连接和请求，将请求路由到可用的实例。当一个实例宕机时，代理会将请求转发到其他可用实例。视图集群可以提供故障转移和负载均衡，但需要额外的配置和管理。

5.MySQL组复制（MySQL Group Replication）：这是MySQL官方推出的一种高可用解决方案，基于半同步复制和一致性哈希等技术。MySQL组复制可以将多个MySQL实例组成一个组，并自动选举一个主节点进行写操作，其他节点作为备份。如果主节点宕机，系统会自动选出新的主节点，实现故障转移和数据同步。

这些高可用架构各有优劣，选择适合自己需求的架构需要考虑数据一致性、写入性能、读取性能、故障转移时间以及维护复杂度等因素。同时，为了保证数据的完整性和一致性，还应该配合使用备份、监控和容灾等措施。

八 简述root@localhost用户和root@‘%’用户的区别
root@localhost：这是一个本地用户账户，只能通过本地连接（即在本机上访问 MySQL）进行登录。该用户账户仅限于本地使用，无法通过网络连接进行访问。

root@'%'：这是一个通配符用户账户，可以从任意主机（包括本地和远程主机）通过网络连接进行登录。% 符号表示可以匹配任意 IP 地址或主机名，因此该用户允许从任何主机上的任何地址进行访问。

总结来说，root@localhost 用户只能在本地进行登录，而 root@'%' 用户可以在本地和远程主机上通过网络连接进行登录。这两个用户账户适用于不同的访问场景，需要根据具体情况来选择使用哪个账户。如果只需要在本地进行管理操作，建议使用 root@localhost 账户以增加安全性；如果需要允许远程访问，可以考虑创建 root@'%' 账户并设置相应的安全控制措施。

九  MySQL中有哪些类型的锁?
在 MySQL 中，主要有以下几种类型的锁：

共享锁（Shared Lock）：也称为读锁（Read Lock），多个事务可以同时获取共享锁。共享锁允许多个事务同时读取被锁定的资源，但阻止其他事务获取独占锁。共享锁不会阻塞其他事务的共享锁。

独占锁（Exclusive Lock）：也称为写锁（Write Lock），只允许一个事务获取独占锁。独占锁用于修改和更新资源，当事务持有独占锁时，其他事务无法获取共享锁或独占锁。

记录锁（Record Lock）：也称为行锁（Row Lock），在事务对表中的某一行进行操作时自动添加的锁。当事务对一行进行修改时，会自动获取该行的记录锁，其他事务需要修改同一行时会被阻塞。

表锁（Table Lock）：也称为全局锁（Global Lock），在对整个表进行操作时自动添加的锁。表锁会对整个表加锁，其他事务无法对该表进行修改或读取操作。

间隙锁（Gap Lock）：间隙锁用于防止幻读（Phantom Read）问题，在索引上的间隙（两个索引记录之间的区域）上加锁，以阻止其他事务在同一范围内插入新记录。

排他锁（Exclusive Next-Key Lock）：排他锁是记录锁和间隙锁的组合，它锁定了一个范围，包括记录和间隙。排他锁用于解决幻读问题，同时保证了锁的完整性和一致性。

MySQL 根据并发控制需求和隔离级别的不同，会自动选择适当的锁来保证数据的一致性、隔离性和并发性。具体的锁机制会根据具体的查询操作和事务隔离级别而有所不同。

十  简述redis的持久化有哪些方式?
RDB（Redis Database）持久化：RDB持久化是将Redis在某个时间点的数据状态快照写入硬盘。它是通过将内存中的数据以二进制格式写入到硬盘上的RDB文件中来实现的。RDB持久化是一种紧凑且高效的持久化方式，适用于备份数据和灾难恢复。可以手动触发、定时触发或者在Redis关闭时自动触发。

AOF（Append Only File）持久化：AOF持久化记录了所有对Redis服务器进行写操作的指令，在Redis重新启动时，会重新执行这些指令来还原数据。AOF持久化以追加的方式将指令追加到AOF文件末尾，因此AOF文件会记录完整的操作历史。AOF持久化相对于RDB持久化来说，对数据的损失更小，但相应的文件体积会更大。可以选择将AOF持久化配置为每秒钟同步（fsync）一次或者每个写操作同步一次。

除了以上两种主要的持久化方式外，Redis还提供了混合持久化的方式，即同时使用RDB持久化和AOF持久化。混合持久化可以在Redis重新启动时快速加载RDB文件进行数据恢复，同时通过AOF文件来还原最近的操作记录。

为了灵活性和安全性，Redis还提供了一些其他的持久化配置选项，如压缩RDB文件、自动重写AOF文件、AOF重写等。这些选项可以根据具体需求进行配置，以满足不同的持久化需求和性能要求。

十一    MongoDB中oplog的作用是什么? 写满后会怎样?
在MongoDB中，oplog（操作日志）是一个特殊的集合，用于记录MongoDB的所有写操作。oplog的作用是支持复制和故障恢复。

具体来说，oplog通过记录主节点（Primary）上的所有写操作，将这些写操作传播到备份节点（Secondary），从而实现数据的复制和同步。当主节点宕机或发生故障时，备份节点可以使用oplog中的操作记录进行故障恢复，快速将自己切换为新的主节点。

oplog的写满会导致什么情况取决于副本集的配置和版本。

在MongoDB 4.0及以上版本中，oplog采用的是固定大小（默认为5%）的循环缓冲区。当oplog写满时，最旧的操作记录将被覆盖，这意味着较旧的操作记录将不再可用。这不会影响复制和故障恢复的正常操作，因为备份节点只需要保留与主节点保持同步的最新操作记录即可。

但是，在一些特殊情况下，如长时间主节点不可用、备份节点长时间离线等，如果备份节点无法及时同步主节点的oplog，可能会导致备份节点的oplog落后于主节点，超过了可容忍的范围，这时候备份节点可能无法正常恢复并成为主节点，需要手动进行故障恢复。

在MongoDB 4.2及以上版本中，引入了可配置大小的oplog（永久的oplog）来解决上述问题。这样，即使备份节点离线一段时间，它仍然能够保存足够长的操作记录，以便在重新连接时进行快速同步和故障恢复。

总之，oplog在MongoDB中起着关键作用，支持数据复制和故障恢复。适当配置和管理oplog对于确保副本集的性能和可靠性非常重要。