目录

一、MySQL架构概述

1.1、客户端连接器

1.2、连接层

1.3、可插拔存储引擎

1.4、文件系统与文件

二、配置文件

三、数据文件

四、日志文件（以MySQL5.7.32为例）

4.1、错误日志 Error Log

4.2、一般查询日志 General Query Log

4.3、二进制日志 Bin Log

4.4、慢查询日志 Slow Query Log

4.5、Undo Log日志

4.6、Redo Log日志

五、备份与恢复

5.1、使用mysqlbinlog + bin log恢复

5.2、使用mysqldump备份与恢复

---

一、MySQL架构概述

1.1、客户端连接器

1. Connectors，客户端连接器，支持主流开发语言的连接。

1.2、连接层

1. Connection Pool，连接池：身份认证、连接数、线程重用，提升并发性能。
2. Management Services & Utilities，管理工具：备份、恢复、安全、集群等。
3. SQL Interface，SQL接口：接收DML、DDL等SQL语句，并将结果返回给用户。
4. Parser，解析器：解析SQL语句，进行语法检查，生成解析树。
5. Optimizer，优化器：解析树优化生成执行计划。
6. Caches & Buffers，缓存：表、记录、引擎等缓存，提升性能。

1.3、可插拔存储引擎

1. Pluggable Storage Engines，存储引擎：MyISAM、InnoDB等不同存储引擎。可插拔替换。

1.4、文件系统与文件

1. FileSystem， Files & Logs，文件系统和文件：日志文件、数据文件、配置文件等。

二、配置文件

数据库配置文件放在/etc/my.cnf，和/etc/my.cnf.d目录里面。

三、数据文件

查看数据文件存放的位置，用datadir参数来查看。数据文件有多种格式，可能根据引擎不同有差异，默认是InnoDB引擎。

mysql> show variables like '%datadir%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.01 sec)mysql> show variables like '%engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set (0.00 sec)mysql>

数据文件包括以下几类

• db.opt文件：记录这个库默认使用的字符集和校验规则。
• frm文件：存储与表相关的元数据，包括表结构的定义，不管使用什么引擎每张表都有一个frm文件。
• MYD文件：MyISAM引擎专用，存放MyISAM表的数据，每张表都会有一个.MYD文件。
• MYI文件：MyISAM引擎专用，存放MyISAM表的索引相关信息，每张MyISAM表对应一个.MYI文件。
• ibd文件和ibdata文件：存放InnoDB的数据文件。InnoDB存储引擎有两种表空间方式：独享表空间和共享表空间，独享表空间使用.ibd文件来存储数据和索引，且每张InnoDB表对应一个.ibd文件；共享表空间使用ibdata文件，所有表共同使用一个.ibdata文件。
• ibdata1文件：系统表空间数据文件，存放表元数据、Undo日志等。
• ib_logfile0、ib_logfile1文件：Redo Log日志文件。

验证一下不同引擎的数据文件内容有什么不同，在test_file数据库里面创建dept1和dept2两张表，分别使用MyISAM和InnoDB引擎。

mysql> use test_file
Database changed
mysql> create table dept1(-> deptno int primary key,-> dname varchar(20)-> ) engine = myisam;
Query OK, 0 rows affected (0.01 sec)mysql> create table dept2(-> deptno int primary key,-> dname varchar(20)-> ) engine = innodb;
Query OK, 0 rows affected (0.01 sec)

我们到数据文件目录下查看。

• 数据库test_file有一个db.opt文件，用来记录字符集和校验规则。
• 两张表个有一个frm文件，记录表结构。
• dept1表使用MyISAM引擎，一个.MYD文件记录数据，一个.MYI文件记录索引。
• dept2表使用InnoDB引擎，独立表空间，所以有一个dept2.ibd文件存放数据和索引。

[root@toy ~]# ll /var/lib/mysql/
auto.cnf            client-key.pem      ib_logfile1         mysql.sock.lock     server-cert.pem     toy-bin.000001
ca-key.pem          ib_buffer_pool      ibtmp1              performance_schema/ server-key.pem      toy-bin.index
ca.pem              ibdata1             mysql/              private_key.pem     sys/                wordpress/
client-cert.pem     ib_logfile0         mysql.sock          public_key.pem      test_file/
[root@toy ~]# ll /var/lib/mysql/test_file/
total 128
-rw-r----- 1 mysql mysql    65 Dec  6 19:31 db.opt
-rw-r----- 1 mysql mysql  8596 Dec  6 19:33 dept1.frm
-rw-r----- 1 mysql mysql     0 Dec  6 19:33 dept1.MYD
-rw-r----- 1 mysql mysql  1024 Dec  6 19:33 dept1.MYI
-rw-r----- 1 mysql mysql  8596 Dec  6 19:34 dept2.frm
-rw-r----- 1 mysql mysql 98304 Dec  6 19:34 dept2.ibd

四、日志文件（以MySQL5.7.32为例）

4.1、错误日志 Error Log

默认开启，记录数据库中的错误信息。查看方法为

mysql> show variables like '%log_err%';
+---------------------+---------------------+
| Variable_name       | Value               |
+---------------------+---------------------+
| binlog_error_action | ABORT_SERVER        |
| log_error           | /var/log/mysqld.log |
| log_error_verbosity | 3                   |
+---------------------+---------------------+
3 rows in set (0.00 sec)mysql>

4.2、一般查询日志 General Query Log

默认关闭，记录一般查询语句。查看方法为

mysql> show variables like '%general_log%';
+------------------+------------------------+
| Variable_name    | Value                  |
+------------------+------------------------+
| general_log      | OFF                    |
| general_log_file | /var/lib/mysql/toy.log |
+------------------+------------------------+
2 rows in set (0.01 sec)mysql>

4.3、二进制日志 Bin Log

记录MySQL数据库执行的更改操作，记录发生时间、执行时长，不记录select、show等不修改数据库的操作。这个日志类是MySQL数据库的机制，和引擎无关，开启以后不管使用什么引擎，MySQL数据库都会写bin log。
show variables like '%log_bin%';是查看二进制日志是否开启以及存放位置。
show variables like '%binlog%';是查看二进制日志的配置情况。

mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+
6 rows in set (0.00 sec)mysql> show variables like '%binlog%';
+--------------------------------------------+----------------------+
| Variable_name                              | Value                |
+--------------------------------------------+----------------------+
| binlog_cache_size                          | 32768                |
| binlog_checksum                            | CRC32                |
| binlog_direct_non_transactional_updates    | OFF                  |
| binlog_error_action                        | ABORT_SERVER         |
| binlog_format                              | ROW                  |
| binlog_group_commit_sync_delay             | 0                    |
| binlog_group_commit_sync_no_delay_count    | 0                    |
| binlog_gtid_simple_recovery                | ON                   |
| binlog_max_flush_queue_time                | 0                    |
| binlog_order_commits                       | ON                   |
| binlog_row_image                           | FULL                 |
| binlog_rows_query_log_events               | OFF                  |
| binlog_stmt_cache_size                     | 32768                |
| binlog_transaction_dependency_history_size | 25000                |
| binlog_transaction_dependency_tracking     | COMMIT_ORDER         |
| innodb_api_enable_binlog                   | OFF                  |
| innodb_locks_unsafe_for_binlog             | OFF                  |
| log_statements_unsafe_for_binlog           | ON                   |
| max_binlog_cache_size                      | 18446744073709547520 |
| max_binlog_size                            | 1073741824           |
| max_binlog_stmt_cache_size                 | 18446744073709547520 |
| sync_binlog                                | 1                    |
+--------------------------------------------+----------------------+
22 rows in set (0.00 sec)mysql>

Bin Log类似Oracle的归档日志，写满后再生成新的Bin Log，文件名格式为“<主机名>-bin.<序列号>”，用于数据库的主从复制、数据恢复。Redo Log文件记录有三种模式，主从复制推荐使用ROW模式。

• ROW（row-based replication，RBR）。master端日志会记录每一行数据被修改的情况，然后在slave端对相同数据进行修改。优点：日志记录每一行数据的修改细节，能完全实现数据库的主从复制、数据恢复。缺点：批量操作会产生大量日志，尤其是alter table操作会让日志量暴涨。
• STATEMENT（statement-base replication，SBR）。master端日志会记录每一条修改数据的SQL，slave端复制的时候SQL进程会解析成和原来master端相同的SQL再次执行。优点：日志量小，减少磁盘IO。缺点：不可靠，不适用于主从复制，因为如果master使用了last_insert_id()、now()等函数，slave解析结果和master不一样，会导致数据不一致。
• MIXED（mixed-based replication，MBR）。ROW、STATEMENT混合使用。MySQL根据执行的SQL语句选择ROW、STATEMENT方式。

Bin Log文件记录的是对数据库修改操作，用来表示修改操作的是Log Event，不同的修改操作对应不同的Log Event。具体内容参看数据库手册。

我们前面看到log_bin默认是关闭的，直接开启会报错，开启需要修改配置文件/etc/my.cnf，然后重启数据库。

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.00 sec)mysql> set global log_bin=ON;
ERROR 1238 (HY000): Variable 'log_bin' is a read only variable

根据手册《refman-5.7-en.a4.pdf》 16.1.6.4 Binary Logging Options and Variables 节的描述，通过修改/etc/my.cnf配置文件，加入log-bin，server_id两个选项，开启log_bin、log_bin_basename、log_bin_index三个选项。默认情况，bin log的位置在数据文件目录，文件名是“<主机名>-bin.<序号>”。启用bin log必须指定server_id选项，否则数据库不能启动。通过修改log-bin=<abs_path>，可以指定bin log的位置，名称等。

[root@toy ~]# vim /etc/my.cnf
# bin log
log-bin
server_id=0
[root@toy ~]# systemctl restart mysqld
…………
[root@toy ~]# ll /var/lib/mysql/ | grep bin
-rw-r----- 1 mysql mysql      154 Dec  6 18:51 toy-bin.000001
-rw-r----- 1 mysql mysql       17 Dec  6 18:51 toy-bin.index

开启bin log后，使用show binary logs查看有多少个bin log文件，使用show binlog events查看有哪些事件，使用show binlog events in toy-bin.000001查看指定文件的时间。

mysql> show variables like '%log_bin%';
+---------------------------------+------------------------------+
| Variable_name                   | Value                        |
+---------------------------------+------------------------------+
| log_bin                         | ON                           |
| log_bin_basename                | /var/lib/mysql/toy-bin       |
| log_bin_index                   | /var/lib/mysql/toy-bin.index |
| log_bin_trust_function_creators | OFF                          |
| log_bin_use_v1_row_events       | OFF                          |
| sql_log_bin                     | ON                           |
+---------------------------------+------------------------------+
6 rows in set (0.01 sec)mysql> show binary logs;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
| toy-bin.000001 |       154 |
+----------------+-----------+
1 row in set (0.00 sec)mysql>
mysql> show binlog events;
+----------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name       | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+----------------+-----+----------------+-----------+-------------+---------------------------------------+
| toy-bin.000001 |   4 | Format_desc    |         0 |         123 | Server ver: 5.7.32-log, Binlog ver: 4 |
| toy-bin.000001 | 123 | Previous_gtids |         0 |         154 |                                       |
+----------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)mysql>

4.4、慢查询日志 Slow Query Log

执行时间比较长的查询语句执行过程记录在慢查询日志中。
执行时间长用long_query_time参数来设置，默认是10秒钟。

mysql> show variables like '%slow_query_log%';
+---------------------+-----------------------------+
| Variable_name       | Value                       |
+---------------------+-----------------------------+
| slow_query_log      | OFF                         |
| slow_query_log_file | /var/lib/mysql/toy-slow.log |
+---------------------+-----------------------------+
2 rows in set (0.00 sec)mysql> show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)mysql>
mysql> set long_query_time = 5;
mysql>
mysql> show variables like '%long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 5.000000 |
+-----------------+----------+
1 row in set (0.00 sec)mysql>

4.5、Undo Log日志

Undo Log实现了事务的原子性，执行rollback语句，可以借助Undo Log日志使数据库库恢复到事务开始前的状态。在事务开始前产生Undo Log，InnoDB会将修改的记录存放到Undo Log日志文件中。在事务提交时，不会立刻删除Undo Log，InnoDB会将该事务对应的undo log放入到删除列表中，后面会通过后台线程purge thread进行回收处理。因此，通过Undo Log回滚数据库状态是以事务计的，不是以时间计的。Undo Log属于逻辑日志，记录一个变化过程。例如执行一个delete，undo log记录一个insert。如果执行一个update，undo log记录一个相反的update。

查看innodb_undo。注意innodb_undo_tablespaces参数默认为0，表示使用系统表空间，也就是记录在ibdata1文件中。如果把这个参数改大，那么会生成用户指定的undo日志文件，undo001、undo002等。

mysql> show variables like '%innodb_undo%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_undo_directory    | ./    |
| innodb_undo_log_truncate | OFF   |
| innodb_undo_logs         | 128   |
| innodb_undo_tablespaces  | 0     |
+--------------------------+-------+
4 rows in set (0.00 sec)mysql>

4.6、Redo Log日志

和Bin Log不同，Redo Log是InnoDB引擎特有的防止数据丢失的机制，这就是前面提到的ib_logfile0和ib_logfile1。

数据发生更改时，不是立即写入磁盘的而是写入缓存的，如果此时发生了宕机，那么这部分非持久化的数据会丢失。为了解决这个问题，InnoDB引擎会把数据更改操作写入Redo Buffer，再通过后台机制写入 Redo Log文件。在下次数据库启动后，非持久化的数据按照Redo Log记录的内容写入数据库，不丢失数据。

Redo Buffer写入Redo Log日志文件的策略就很重要了，如果Redo Buffer没有及时写入，也会丢失数据。通过innodb_log参数，看到Redo Buffer大小是16MB，日志组有2个Redo Log文件，每个文件大小是50MB。通过模糊查询innodb_flush查看innodb_flush_log_at_trx_commit参数，也就是Redo Buffer持久化策略。innodb_flush_log_at_trx_commit有三个可选值0、1、2。

• 0表示后台线程每秒持久化一次，存在丢失1秒数据的风险。
• 1表示提交日志时将Redo Buffer写入OS cache，同时OS cache做持久化，这种方式不丢失数据，也是默认值。
• 2表示提交日志时将Redo Buffer写入OS cache，但是OS cache每秒持久化，还是有丢数据的风险。

mysql> show variables like '%innodb_log%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| innodb_log_buffer_size      | 16777216 |
| innodb_log_checksums        | ON       |
| innodb_log_compressed_pages | ON       |
| innodb_log_file_size        | 50331648 |
| innodb_log_files_in_group   | 2        |
| innodb_log_group_home_dir   | ./       |
| innodb_log_write_ahead_size | 8192     |
+-----------------------------+----------+
7 rows in set (0.00 sec)mysql>
mysql> show variables like '%innodb_flush%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_timeout    | 1     |
| innodb_flush_log_at_trx_commit | 1     |
| innodb_flush_method            |       |
| innodb_flush_neighbors         | 1     |
| innodb_flush_sync              | ON    |
| innodb_flushing_avg_loops      | 30    |
+--------------------------------+-------+
6 rows in set (0.00 sec)mysql>

五、备份与恢复

5.1、使用mysqlbinlog + bin log恢复

使用mysqlbinlog + bin log可以恢复数据库，格式如下。

mysqlbinlog --start-position=<start_pos> --stop-position=<stop_pos> <binlogfile> | mysql -u<user> -p
mysqlbinlog --start-datetime=<start_time> --stop-datetime=<stop_time> <binlogfile> | mysql -u<user> -p

现在假设误操作删掉了test_file数据库，马上用mysqlbinlog查看有哪些操作，需要恢复到哪个地方。

mysql> show binlog events;
+----------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------+
| Log_name       | Pos  | Event_type     | Server_id | End_log_pos | Info                  |
+----------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------+
| toy-bin.000001 |    4 | Format_desc    |         0 |         123 | Server ver: 5.7.32-log, Binlog ver: 4                  |
| toy-bin.000001 |  123 | Previous_gtids |         0 |         154 |                  |
| toy-bin.000001 |  154 | Anonymous_Gtid |         0 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                  |
| toy-bin.000001 |  219 | Query          |         0 |         328 | create database test_file                  |
| toy-bin.000001 |  328 | Anonymous_Gtid |         0 |         393 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                  |
| toy-bin.000001 |  393 | Query          |         0 |         556 | use `test_file`; create table dept1(
deptno int primary key,
dname varchar(20)
) engine = myisam |
| toy-bin.000001 |  556 | Anonymous_Gtid |         0 |         621 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                  |
| toy-bin.000001 |  621 | Query          |         0 |         784 | use `test_file`; create table dept2(
deptno int primary key,
dname varchar(20)
) engine = innodb |
| toy-bin.000001 |  784 | Anonymous_Gtid |         0 |         849 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                  |
| toy-bin.000001 |  849 | Query          |         0 |        1007 | use `test_file`; create table user( id int primary key, name varchar(20) ) charset = 'utf8'      |
| toy-bin.000001 | 1007 | Anonymous_Gtid |         0 |        1072 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                  |
| toy-bin.000001 | 1072 | Query          |         0 |        1149 | BEGIN                  |
| toy-bin.000001 | 1149 | Table_map      |         0 |        1204 | table_id: 109 (test_file.user)                  |
| toy-bin.000001 | 1204 | Write_rows     |         0 |        1251 | table_id: 109 flags: STMT_END_F                  |
| toy-bin.000001 | 1251 | Xid            |         0 |        1282 | COMMIT /* xid=36 */                  |
| toy-bin.000001 | 1282 | Anonymous_Gtid |         0 |        1347 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                  |
| toy-bin.000001 | 1347 | Query          |         0 |        1424 | BEGIN                  |
| toy-bin.000001 | 1424 | Table_map      |         0 |        1479 | table_id: 109 (test_file.user)                  |
| toy-bin.000001 | 1479 | Write_rows     |         0 |        1523 | table_id: 109 flags: STMT_END_F                  |
| toy-bin.000001 | 1523 | Xid            |         0 |        1554 | COMMIT /* xid=37 */                  |
| toy-bin.000001 | 1554 | Anonymous_Gtid |         0 |        1619 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                  |
| toy-bin.000001 | 1619 | Query          |         0 |        1696 | BEGIN                  |
| toy-bin.000001 | 1696 | Table_map      |         0 |        1751 | table_id: 109 (test_file.user)                  |
| toy-bin.000001 | 1751 | Update_rows    |         0 |        1810 | table_id: 109 flags: STMT_END_F                  |
| toy-bin.000001 | 1810 | Xid            |         0 |        1841 | COMMIT /* xid=39 */                  |
| toy-bin.000001 | 1841 | Anonymous_Gtid |         0 |        1906 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                  |
| toy-bin.000001 | 1906 | Query          |         0 |        1983 | BEGIN                  |
| toy-bin.000001 | 1983 | Table_map      |         0 |        2038 | table_id: 109 (test_file.user)                  |
| toy-bin.000001 | 2038 | Update_rows    |         0 |        2094 | table_id: 109 flags: STMT_END_F                  |
| toy-bin.000001 | 2094 | Xid            |         0 |        2125 | COMMIT /* xid=43 */                  |
| toy-bin.000001 | 2125 | Anonymous_Gtid |         0 |        2190 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                  |
| toy-bin.000001 | 2190 | Query          |         0 |        2297 | drop database test_file                  |
+----------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------+
32 rows in set (0.00 sec)mysql>

发现Pos=154开始创建了test_file数据库，Pos=2125删除test_file数据库。因此，恢复的参数--start-position=154，--stop-position=2125。然后管道连接到mysql，指定用户密码，就可以恢复到你想到的状态了。如果是使用时间参数，那么先用mysqlbinlog命令查询到时间，再相应调整命令参数。

[root@toy ~]# mysqlbinlog --start-position=154 --stop-position=2125 /var/lib/mysql/toy-bin.000001 | mysql -uroot -p
Enter password:
[root@toy ~]#

5.2、使用mysqldump备份与恢复

mysqldump -u -p db_name > db_backup.sql
mysql -u -p db_name < db_backup.sql