日志文件

日志是MySQL数据库的重要组成部分。日志文件中记录着MySQL数据库运行期间发生的变化，也就是说用来记录MySQL数据库的客户端连接状况、SQL语句的执行情况和错误信息等。等数据库遭到意外的损坏时，可以通过日志查看文件出错的原因，可以通过日志文件进行数据恢复。

日志文件主要包含：错误日志、通用查询日志、慢查询日志、二进制日志、中继日志和DDL日志。

管理错误日志

错误日志包含了mysqld的启动和宕机次数的记录，还包含一些诊断信息，例如：错误、警告，以及服务器在启动、运行及关闭期间发出的提示信息。

错误日志子系统由两个组件组成，它们筛选和写入日志事件，还有一个名为log_error_services的系统变量，这个系统变量对组件进行配置以实现所需要的日志记录结果。global.log_error_services的默认值为log_filter_internal;log_sink_internal。

  1. mysql> SELECT @@global.log_error_services;
  2. +----------------------------------------+
  3. | @@global.log_error_services            |
  4. +----------------------------------------+
  5. | log_filter_internal; log_sink_internal |
  6. +----------------------------------------+
  7. 1 row in set (0.00 sec)

该值表示日志事件首先穿过内置的筛选器组件log_filter_internal，然后穿过内置的日志写入器组件log_sink_internal。组件顺序是非常重要的，因为服务器是按照组件被列出的顺序执行的。在log_error_services的值中指定的任何可加载（非内置）组件都必须首先通过INSTALL COMPONENT安装。

如何操作

在某种程度上而言，错误日志很简单。

配置错误日志

错误日志记录由log_error变量控制（在启动脚本里为–log-error）控制。如果没有给出–log-error，默认的目标文件是控制台。

如果在没有命名文件的情况下给出了–log-error，则默认的目标文件是一个在数据目录中名为host_name.err的文件。

如果–log-error被指定来命名一个文件，默认的目标文件就是该文件（如果文件名没有后缀，则添加一个.err后缀），如果没有用一个绝对路径名来指定别的位置，那么这个文件就位于数据目录下。

系统变量log_error_verbosity控制着服务器将错误、警告和注释信息记录到错误日志的冗余情况。可以使用的值有，默认值为2。

  • 1：只输出错误
  • 2：输出错误和警告
  • 3：输出错误、警告和注释

要改变错误日志的位置，请修改配置文件并重新启动MySQL：

  1. [root@www ~]# mkdir /var/log/mysql
  2. [root@www ~]# chown -R mysql:mysql /var/log/mysql
  3. [root@www ~]# vim /etc/my.cnf
  4. [mysqld]
  5. log-error=/var/log/mysql/mysqld.log
  6. [root@www ~]# systemctl restart mysqld.service

验证错误日志的更改：

  1. mysql> show variables like 'log_error';
  2. +---------------+---------------------------+
  3. | Variable_name | Value                     |
  4. +---------------+---------------------------+
  5. | log_error     | /var/log/mysql/mysqld.log |
  6. +---------------+---------------------------+
  7. 1 row in set (0.01 sec)

要调整冗余信息，可以动态更改log_error_verbosity变量。建议采用3，这样错误、警告和注释消息都可以记录。

  1. mysql> SET @@global.log_error_verbosity=3;
  2. Query OK, 0 rows affected (0.00 sec)
  4. mysql> SELECT @@global.log_error_verbosity;
  5. +------------------------------+
  6. | @@global.log_error_verbosity |
  7. +------------------------------+
  8. |                            3 |
  9. +------------------------------+
  10. 1 row in set (0.00 sec)

轮转错误日志

假设错误日志文件变大了，你想要轮转(rotate)它。可以简单地移动文件并执行FLUSH LOGS命令：

  1. [root@www ~]# mv /var/log/mysql/mysqld.log /var/log/mysql/mysqld.log.0 
  2. [root@www ~]# mysqladmin -u root -pCom.123456 flush-logs
  3. mysqladmin: [Warning] Using a password on the command line interface can be insecure.
  4. [root@www ~]# ls -l /var/log/mysql
  5. total 4
  6. -rw-r----- 1 mysql mysql   0 Aug 24 22:53 mysqld.log
  7. -rw-r----- 1 mysql mysql 577 Aug 24 22:29 mysqld.log.0

可以使用一些脚本自动执行前面的步骤，并且把它们放入cron中。

管理通用查询日志和慢查询日志

有两种方法可以记录查询：

  • 一种方法是通过通用查询日志记录；
  • 一种方法是通过慢查询日志记录；

如何操作

下面的小节详细讨论操作步骤。

通用查询日志

通用查询日志一般都记录了mysqld在做的事情。当客户端连接或断开连接时，服务器会将这个信息写入日志中，并记录从客户端收到的每一条SQL语句。如果怀疑客户端存在错误，并且想知道客户端发送给mysqld的具体内容，通用查询日志是非常有用的。

  1. 指定记录日志的文件。如果你不指定的话，将会在数据目录中创建名为hostname.log的文件。如果没有给出一个绝对路径名来指定别的目录，服务器就会在数据目录中创建这个文件。
  1. mysql> SET @@GLOBAL.general_log_file='/var/log/mysql/general_query_log';
  2. Query OK, 0 rows affected (0.00 sec)
  1. 启用通用查询日志：
  1. mysql> SET GLOBAL general_log = 'ON';
  2. Query OK, 0 rows affected (0.05 sec)
  1. 可以看到被记录的一些查询内容：
  1. [root@www mysql]# tail /var/log/mysql/general_query_log 
  2. 2019-08-25T02:37:01.131498Z        8 Field List    employee_names 
  3. 2019-08-25T02:37:01.132737Z        8 Field List    employees 
  4. 2019-08-25T02:37:01.138693Z        8 Field List    employees_list1 
  5. 2019-08-25T02:37:01.140256Z        8 Field List    employees_list2 
  6. 2019-08-25T02:37:01.141683Z        8 Field List    salaries 
  7. 2019-08-25T02:37:01.145523Z        8 Field List    salary_audit 
  8. 2019-08-25T02:37:01.147216Z        8 Field List    salary_view 
  9. 2019-08-25T02:37:01.147989Z        8 Field List    titles 
  10. 2019-08-25T02:37:01.148646Z        8 Field List    titles_only 
  11. 2019-08-25T02:37:13.059029Z        8 Query    select * from employees limit 10

通用查询日志会产生一个非常大的日志文件。在生产服务器上启用它时要非常小心。它会极大地影响服务器的性能。

慢查询日志

慢查询日志包含了执行时间超过long_query_time秒，以及至少扫描了min_examined_row_limit行的SQL语句。

要记录所有查询，可以将long_query_time的值设置为0。long_query_time的默认值是1秒，min_examined_row_limit的默认值是0。

默认情况下，不使用索引的查询和管理语句（例如ALTER TABLE、ANALYZE TABLE、CHECK TABLE、CREATE INDEX、DROP INDEX、OPTIMIZE TABLE和REPAIR TABLE）是不会被记录的。不过，可以通过log_slow_admin_statements和log_queries_not_using_indexes来改变这种设置。

要启用慢查询日志，可以动态设置slow_query_log=1，你可以使用slow_query_log_file来设置文件名。要指定日志目标文件，可以使用–log-output。

  1. 验证long_query_time并按照你的需求调整它：
  1. mysql> SELECT @@GLOBAL.LONG_QUERY_TIME;
  2. +--------------------------+
  3. | @@GLOBAL.LONG_QUERY_TIME |
  4. +--------------------------+
  5. |                 1.000000 |
  6. +--------------------------+
  7. 1 row in set (0.00 sec)
  1. 验证慢查询文件。默认情况下，它应该是在数据目录中使用hostname-slow的日志：
  1. mysql> SELECT @@GLOBAL.slow_query_log_file;
  2. +------------------------------+
  3. | @@GLOBAL.slow_query_log_file |
  4. +------------------------------+
  5. | /var/lib/mysql/www-slow.log  |
  6. +------------------------------+
  7. 1 row in set (0.00 sec)
  8. mysql> SET @@GLOBAL.slow_query_log_file='/var/log/mysql/mysql_slow.log';
  9. Query OK, 0 rows affected (0.00 sec)
  11. mysql> SELECT @@GLOBAL.slow_query_log_file;
  12. +-------------------------------+
  13. | @@GLOBAL.slow_query_log_file  |
  14. +-------------------------------+
  15. | /var/log/mysql/mysql_slow.log |
  16. +-------------------------------+
  17. 1 row in set (0.00 sec)
  1. 启用慢查询日志：
  1. mysql> SELECT @@GLOBAL.slow_query_log;
  2. +-------------------------+
  3. | @@GLOBAL.slow_query_log |
  4. +-------------------------+
  5. |                       0 |
  6. +-------------------------+
  7. 1 row in set (0.00 sec)
  9. mysql> SET @@GLOBAL.slow_query_log=1;
  10. Query OK, 0 rows affected (0.05 sec)
  12. mysql> SELECT @@GLOBAL.slow_query_log;
  13. +-------------------------+
  14. | @@GLOBAL.slow_query_log |
  15. +-------------------------+
  16. |                       1 |
  17. +-------------------------+
  18. 1 row in set (0.00 sec)
  1. 验证这些查询确实已被记录（必须执行几个长时间运行的查询，以便在慢查询日志中看到他们）：
  1. mysql> SELECT SLEEP(2);
  2. +----------+
  3. | SLEEP(2) |
  4. +----------+
  5. |        0 |
  6. +----------+
  7. 1 row in set (2.00 sec)

查看是否记录：

  1. [root@www mysql]# tail /var/log/mysql/mysql_slow.log 
  2. /usr/sbin/mysqld, Version: 8.0.16 (MySQL Community Server - GPL). started with:
  3. Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
  4. Time                 Id Command    Argument
  5. # Time: 2019-08-25T02:58:43.707060Z
  6. # User@Host: root[root] @ localhost []  Id:     8
  7. # Query_time: 2.000949  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
  8. use employees;
  9. SET timestamp=1566701921;
  10. SELECT SLEEP(2);

选择查询日志的输出目标文件

可以通过指定变量log_output的值，将查询记录到MySQL的文件或者表中，log_output变量可以是FILE或者TABLE，也可以同时是FILE和TABLE。

如果将log_output指定为FILE，则通用查询日志和慢查询日志将分别被写入由general_log_file和slow_query_log_file指定的文件。

如果将log_output指定为TABLE，则通用查询日志和慢查询日志将分别被写入mysql.general_log和mysql.slow_log表中。日志内容可以通过SQL语句访问。例如：

  1. mysql> SELECT @@GLOBAL.log_output;
  2. +---------------------+
  3. | @@GLOBAL.log_output |
  4. +---------------------+
  5. | FILE                |
  6. +---------------------+
  7. 1 row in set (0.00 sec)
  9. mysql> SET @@GLOBAL.log_output='TABLE';
  10. Query OK, 0 rows affected (0.00 sec)
  12. mysql> SET @@GLOBAL.general_log='ON';
  13. Query OK, 0 rows affected (0.00 sec)

执行一些查询，然后查询mysql.general_log表；

  1. mysql> SELECT * FROM mysql.general_log WHERE command_type='Query' \G
  2. *************************** 1. row ***************************
  3.   event_time: 2019-08-25 11:12:05.472201
  4.    user_host: root[root] @ localhost []
  5.    thread_id: 8
  6.    server_id: 1
  7. command_type: Query
  8.     argument: SET @@GLOBAL.general_log='ON'
  9. *************************** 2. row ***************************
  10.   event_time: 2019-08-25 11:17:27.212629
  11.    user_host: root[root] @ localhost []
  12.    thread_id: 8
  13.    server_id: 1
  14. command_type: Query
  15.     argument: show databases
  16. *************************** 3. row ***************************
  17.   event_time: 2019-08-25 11:17:33.141855
  18.    user_host: root[root] @ localhost []
  19.    thread_id: 8
  20.    server_id: 1
  21. command_type: Query
  22.     argument: SELECT DATABASE()
  23. *************************** 4. row ***************************
  24.   event_time: 2019-08-25 11:17:33.143731
  25.    user_host: root[root] @ localhost []
  26.    thread_id: 8
  27.    server_id: 1
  28. command_type: Query
  29.     argument: show databases
  30. *************************** 5. row ***************************
  31.   event_time: 2019-08-25 11:17:33.144585
  32.    user_host: root[root] @ localhost []
  33.    thread_id: 8
  34.    server_id: 1
  35. command_type: Query
  36.     argument: show tables
  37. *************************** 6. row ***************************
  38.   event_time: 2019-08-25 11:17:36.233781
  39.    user_host: root[root] @ localhost []
  40.    thread_id: 8
  41.    server_id: 1
  42. command_type: Query
  43.     argument: show tables
  44. *************************** 7. row ***************************
  45.   event_time: 2019-08-25 11:18:30.360436
  46.    user_host: root[root] @ localhost []
  47.    thread_id: 8
  48.    server_id: 1
  49. command_type: Query
  50.     argument: SELECT * FROM mysql.general_log WHERE command_type='Query'
  51. 7 rows in set (0.00 sec)

也可以用类似的方法使用slow_log表：

  1. mysql> SET @@GLOBAL.slow_query_log=1;
  2. Query OK, 0 rows affected (0.00 sec)
  4. mysql> SET @@GLOBAL.long_query_time=1;
  5. Query OK, 0 rows affected (0.00 sec)
  7. mysql> SELECT SLEEP(2);
  8. +----------+
  9. | SLEEP(2) |
  10. +----------+
  11. |        0 |
  12. +----------+
  13. 1 row in set (2.00 sec)
  15. mysql> SELECT * FROM mysql.slow_log \G
  16. *************************** 1. row ***************************
  17.     start_time: 2019-08-25 11:22:04.430737
  18.      user_host: root[root] @ localhost []
  19.     query_time: 00:00:02.000963
  20.      lock_time: 00:00:00.000000
  21.      rows_sent: 1
  22.  rows_examined: 0
  23.             db: mysql
  24. last_insert_id: 0
  25.      insert_id: 0
  26.      server_id: 1
  27.       sql_text: SELECT SLEEP(2)
  28.      thread_id: 8
  29. 1 row in set (0.00 sec)

如果查询日志表变得非常大，你可以创建一个新表来替代它：

  1. 创建一个新表，mysql.general_log_new:
  1. mysql> DROP TABLE IF EXISTS mysql.general_log_new;
  2. Query OK, 0 rows affected, 1 warning (0.00 sec)
  4. mysql> CREATE TABLE mysql.general_log_new LIKE mysql.general_log;
  5. Query OK, 0 rows affected (0.06 sec)
  1. 使用RENAME TABLE命令交换新表和旧表：
  1. mysql> RENAME TABLE mysql.general_log TO mysql.general_log_1,\
  2.     -> mysql.general_log_new TO mysql.general_log;
  3. Query OK, 0 rows affected (0.01 sec)

管理二进制日志

二进制日志包含数据库的所有更改记录，包括数据和结构两方面。二进制日志不记录SELECT或SHOW等不修改数据的操作。运行带有二进制日志的服务器会带来轻微的性能影响。二进制日志能保证数据库出故障时数据是安全的。只有完整的事件或事务会被记录或回读。

为什么应该使用二进制日志？

  • 复制：使用二进制日志，可以把对服务器所做的更改以流式方式传输到另一台服务器上。从（slave）服务器充当镜像副本，也可以用于分配负载。接受写入的服务器称为主（master）服务器。
  • 时间点恢复：假设你在星期日的00:00进行了备份，而数据库在星期日的08:00出现故障。使用备份可以恢复到周日00:00的状态；而使用二进制日志可以恢复到周日08:00的状态。

使用二进制日志

要启用二进制日志，必须设置log_bin和server_id并重启服务器。可以在log_bin内提及path和base名称。例如，log_bin设置为/data/mysql/binlogs/server1,二进制日志存储在/data/mysql/binlogs文件夹中名为server1.000001、server1.000002等的日志文件中。每当服务器启动或刷新日志时，或者当前日志的大小达到max_binlog_size时，服务器都会在系列中创建一个新文件。每个二进制日志的位置都在server1.index文件中被维护。

如何使用二进制日志呢？过程如下：

启用二进制日志

  1. 启用二进制日志并设置server_id。server_id要在MySQL基础架构中唯一。也可以简单地把log_bin变量放在my.cnf中，不赋予任何值，在这种情况下，二进制日志是在数据目录中创建的。可以使用主机名作为目录名称。
    查看一下默认值： 
  1. mysql> show variables like '%log_bin%';
  2. +---------------------------------+-----------------------------+
  3. | Variable_name                   | Value                       |
  4. +---------------------------------+-----------------------------+
  5. | log_bin                         | ON                          |
  6. | log_bin_basename                | /var/lib/mysql/binlog       |
  7. | log_bin_index                   | /var/lib/mysql/binlog.index |
  8. | log_bin_trust_function_creators | OFF                         |
  9. | log_bin_use_v1_row_events       | OFF                         |
  10. | sql_log_bin                     | ON                          |
  11. +---------------------------------+-----------------------------+
  12. 6 rows in set (0.05 sec)
  14. mysql> show variables like '%server_id%';
  15. +----------------+-------+
  16. | Variable_name  | Value |
  17. +----------------+-------+
  18. | server_id      | 1     |
  19. | server_id_bits | 32    |
  20. +----------------+-------+
  21. 2 rows in set (0.00 sec)

修改配置文件

  1. [root@www ~]# vim /etc/my.cnf
  2. [mysqld]
  3. log_bin=/data/mysql/binlogs/server1
  4. server_id=100
  1. 重启MySQL服务器
  1. [root@www ~]# systemctl restart mysqld.service
  1. 验证是否创建了二进制日志：
  1. mysql> show variables like '%log_bin%';
  2. +---------------------------------+-----------------------------------+
  3. | Variable_name                   | Value                             |
  4. +---------------------------------+-----------------------------------+
  5. | log_bin                         | ON                                |
  6. | log_bin_basename                | /data/mysql/binlogs/server1       |
  7. | log_bin_index                   | /data/mysql/binlogs/server1.index |
  8. | log_bin_trust_function_creators | OFF                               |
  9. | log_bin_use_v1_row_events       | OFF                               |
  10. | sql_log_bin                     | ON                                |
  11. +---------------------------------+-----------------------------------+
  12. 6 rows in set (0.01 sec)
  14. mysql> show master logs;
  15. +----------------+-----------+-----------+
  16. | Log_name       | File_size | Encrypted |
  17. +----------------+-----------+-----------+
  18. | server1.000001 |       155 | No        |
  19. +----------------+-----------+-----------+
  20. 1 row in set (0.00 sec)
  22. mysql> show binary logs;
  23. +----------------+-----------+-----------+
  24. | Log_name       | File_size | Encrypted |
  25. +----------------+-----------+-----------+
  26. | server1.000001 |       155 | No        |
  27. +----------------+-----------+-----------+
  28. 1 row in set (0.00 sec)
  1. 执行SHOW BINARY LOGS或者SHOW MASTER LOGS可以显示服务器的所有二进制日志
  2. 执行命令SHOW MASTER STATUS;以获取当前的二进制日志位置：
  1. mysql> show master status;
  2. +----------------+----------+--------------+------------------+-------------------+
  3. | File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  4. +----------------+----------+--------------+------------------+-------------------+
  5. | server1.000001 |      155 |              |                  |                   |
  6. +----------------+----------+--------------+------------------+-------------------+
  7. 1 row in set (0.00 sec)

一旦server1.000001达到max_binlog_size（默认为1GB），一个新文件server1.000002就会被创建，并被添加到server1.index中。可以使用SET@@global.max_binlog_size=536870912动态设置max_binlog_size。

禁用会话的二进制日志

有些情况下可能不需要将执行语句复制到其他服务器上。为此，可以使用以下命令来禁用该会话的二进制日志：

  1. mysql> SET SQL_LOG_BIN=0;
  2. Query OK, 0 rows affected (0.00 sec)

在这条语句后的所有SQL语句都不会被记录到二进制日志中，不过这仅仅是针对该会话的。要重新启用二进制日志，可以执行：

  1. mysql> SET SQL_LOG_BIN=1;
  2. Query OK, 0 rows affected (0.00 sec)

移至下一个日志

可以使用FLUSH LOGS命令关闭当前的二进制日志并打开一个新的二进制日志：

  1. mysql> SHOW BINARY LOGS;
  2. +----------------+-----------+-----------+
  3. | Log_name       | File_size | Encrypted |
  4. +----------------+-----------+-----------+
  5. | server1.000001 |       155 | No        |
  6. +----------------+-----------+-----------+
  7. 1 row in set (0.00 sec)
  9. mysql> FLUSH LOGS;
  10. Query OK, 0 rows affected (0.05 sec)
  12. mysql> SHOW BINARY LOGS;
  13. +----------------+-----------+-----------+
  14. | Log_name       | File_size | Encrypted |
  15. +----------------+-----------+-----------+
  16. | server1.000001 |       200 | No        |
  17. | server1.000002 |       155 | No        |
  18. +----------------+-----------+-----------+
  19. 2 rows in set (0.00 sec)

清理二进制日志

随着写入次数的增多，二进制日志会消耗大量空间。如何清理呢？

  1. 使用binlog_expire_logs_seconds和expire_logs_days设置日志的到期时间。
    如果想以天为单位设置到期时间，请设置expire_logs_days。例如，如果要删除两天之前的所有二进制日志，请SET @@global.expire_logs_days=2。如果设置为0，则禁用设置会自动到期。
    这两个变量的效果是叠加的。例如，如果expire_logs_days是1并且binlog_expire_logs_seconds是43200，那么二进制日志就会每1.5天清除一次。这与将binlog_expire_logs_seconds设置为129600，将expire_logs_days设置为0的效果是相同的。
    如果想禁止自动清除二进制日志，这两个变量值必须都设置为0。
  2. 要手动清除日志，执行PURGE BINARY LOGS TO ‘’。例如，有server1.000001、server1.000002、server1.000003和server1.000004文件，如果执行PURGE BINARY LOGS TO ‘server1.000004’,则从server1.000001到server.000003的所有文件都会被删除，但文件server1.000004不会被删除：
  1. mysql> SHOW BINARY LOGS;
  2. +----------------+-----------+-----------+
  3. | Log_name       | File_size | Encrypted |
  4. +----------------+-----------+-----------+
  5. | server1.000001 |       200 | No        |
  6. | server1.000002 |       178 | No        |
  7. | server1.000003 |       155 | No        |
  8. +----------------+-----------+-----------+
  9. 3 rows in set (0.00 sec)
  11. mysql> PURGE BINARY LOGS TO 'server1.000003';
  12. Query OK, 0 rows affected (0.00 sec)
  14. mysql> SHOW BINARY LOGS;
  15. +----------------+-----------+-----------+
  16. | Log_name       | File_size | Encrypted |
  17. +----------------+-----------+-----------+
  18. | server1.000003 |       155 | No        |
  19. +----------------+-----------+-----------+
  20. 1 row in set (0.00 sec)

除了指定某个日志文件，还可以执行命令PURGE BINARY LOGS BEFORE ‘2017-08-03 15:45:00’。除了使用BINARY，还可以使用MASTER。

  1. mysql> PURGE MASTER LOGS TO 'server1.000003';

可以实现和之前语句一样的效果。

  1. 要删除所有二进制日志并再次从头开始，可以执行RESET MASTER:
  1. mysql> SHOW BINARY LOGS;
  2. +----------------+-----------+-----------+
  3. | Log_name       | File_size | Encrypted |
  4. +----------------+-----------+-----------+
  5. | server1.000003 |       155 | No        |
  6. +----------------+-----------+-----------+
  7. 1 row in set (0.00 sec)
  9. mysql> RESET MASTER;
  10. Query OK, 0 rows affected (0.01 sec)
  12. mysql> SHOW BINARY LOGS;
  13. +----------------+-----------+-----------+
  14. | Log_name       | File_size | Encrypted |
  15. +----------------+-----------+-----------+
  16. | server1.000001 |       155 | No        |
  17. +----------------+-----------+-----------+
  18. 1 row in set (0.00 sec)

在复制环境中使用上面方法删除二进制日志是不安全的，因为如果有任何一个从库没有消费完二进制日志，而却删除了这些日志，那么这个从库将会失去同步，就会导致需要重新构建它。

删除二进制日志安全方法是，检查每一个从库读取的二进制日志的情况，然后删除它们。可以使用mysqlbinlogpurge来实现。

二进制日志的格式

二进制日志可以写成下面三种格式：

  1. STATEMENT：记录实际的SQL语句
  2. ROW：记录每行所做的更改。
    例如，更新语句更新10行，所有10行的更新信息都会被写入日志。而在基于语句的复制中，只有更新语句会被写入日志，默认格式是ROW。
  3. MIXED：当需要时，MySQL会从STATEMENT切换到ROW。
    有些语句在不同服务器上执行时可能会得到不同的结果。例如，UUID()函数的输出就因服务器而异。这些语句被称为非确定性的语句，基于这些语句的复制是不安全的。在这些情况下，当设置MIXED格式时，MySQL服务器会切换为基于行的格式。

可以使用兼具全局和会话范围作用域的动态变量binlog_format来设置格式。在全局范围进行设置可使所有客户端使用指定的格式：

  1. mysql> show variables like '%binlog_format%';
  2. +---------------+-------+
  3. | Variable_name | Value |
  4. +---------------+-------+
  5. | binlog_format | ROW   |
  6. +---------------+-------+
  7. 1 row in set (0.06 sec)
  9. mysql> SET GLOBAL binlog_format = 'STATEMENT';
  10. 或者
  11. mysql> SET GLOBAL binlog_format = 'ROW';
  1. MySQL8.0使用第2版的二进制日志行事件，MySQL5.6.6之前的MySQL Server版本无法读取这些事件。将log_bin_use_v1_row_events设置为1，便可以使用第1版，这样MySQL5.6.6之前的版本就可以读取这些事件了。log_bin_use_v1_row_events的默认值是0。
  1. mysql> SET @@GLOBAL.log_bin_use_v1_row_events=1;
  1. 当创建一个存储函数时，你必须声明它是确定性的或者它不修改数据，否则二进制日志可能不安全。默认情况下，为了接受CREATE FUNCTION语句，必须至少明确指定DETERMINISTIC、NO SQL 或者 READS SQL DATA中的一个，否则会发生错误：
  1. ERROR 1418(HY000): This function has none of DETERMINISTIC,NO SQL, or READS SQL DATA in its declaration and binary logging is enabled(you *might* want to use the less safe log_bin_trust_function_creators variable)

可以在例程内编写非确定性语句，并仍然声明为DETERMINISTIC（不是一种很好的做法），如果要复制未声明为DETERMINISTIC的例程，可以设置log_bin_trust_function_creators变量：

  1. mysql> SET GLOBAL log_bin_trust_function_creators=1;

从二进制日志中提取语句

可以使用mysqlbinlog实用程序（MySQL已包含）从二进制日志中提取内容，并将其应用到其他服务器。

准备工作

使用各种二进制格式执行几条语句。如果设置为GLOBAL级别（全局范围），必须要断开并重新连接，以使更改生效；如果想保持连接，可以设置为SESSION级别（会话范围）。

更改为基于语句的复制（SBR）：

  1. mysql> SET @@GLOBAL.binlog_format='STATEMENT';
  2. Query OK, 0 rows affected (0.00 sec)
  4. mysql> SET @@SESSION.binlog_format='STATEMENT';
  5. Query OK, 0 rows affected (0.00 sec)

更新几行：

  1. mysql> BEGIN;
  2. Query OK, 0 rows affected (0.00 sec)
  4. mysql> UPDATE salaries SET salary=salary*2 WHERE emp_no<10002;
  5. Query OK, 17 rows affected (0.01 sec)
  6. Rows matched: 17  Changed: 17  Warnings: 0
  8. mysql> COMMIT;
  9. Query OK, 0 rows affected (0.01 sec)

更改为基于行的复制（BBR）：

  1. mysql> SET @@GLOBAL.binlog_format='ROW';
  2. Query OK, 0 rows affected (0.00 sec)
  4. mysql> SET @@SESSION.binlog_format='ROW';
  5. Query OK, 0 rows affected (0.00 sec)

更新几行

  1. mysql> BEGIN;
  2. Query OK, 0 rows affected (0.00 sec)
  4. mysql> UPDATE salaries SET salary=salary/2 WHERE emp_no<10002;
  5. Query OK, 17 rows affected (0.01 sec)
  6. Rows matched: 17  Changed: 17  Warnings: 0
  8. mysql> COMMIT;
  9. Query OK, 0 rows affected (0.00 sec)

更改为MIXED格式：

  1. mysql> SET @@GLOBAL.binlog_format='MIXED';
  2. Query OK, 0 rows affected (0.00 sec)
  4. mysql> SET @@SESSION.binlog_format='MIXED';
  5. Query OK, 0 rows affected (0.00 sec)

更新几行：

  1. mysql> BEGIN;
  2. Query OK, 0 rows affected (0.00 sec)
  4. mysql> UPDATE salaries SET salary=salary*2 WHERE emp_no<10002;
  5. Query OK, 17 rows affected (0.00 sec)
  6. Rows matched: 17  Changed: 17  Warnings: 0
  8. mysql> INSERT INTO departments VALUES('d010',UUID());
  9. Query OK, 1 row affected (0.05 sec)
  11. mysql> COMMIT;
  12. Query OK, 0 rows affected (0.01 sec)

如何操作

要显示日志server1.000002的内容，可以执行：

  1. [root@www ~]# mysqlbinlog /data/mysql/binlogs/server1.000002

会得到类似于下面的输出结果：

  1. # at 1467
  2. #190831 17:12:48 server id 100  end_log_pos 1608 CRC32 0x813af03f     Query    thread_id=8    exec_time=0    error_code=0

在第一行中，#at后面的数字表示二进制日志文件中事件的起始位置（文件偏移量）。

在第二行中，包含了语句在服务器上被启用的时间戳。时间戳后面跟着server ID、end_log_pos、thread_id、exec_time和error_code。

  • server id：产生该事件的服务器server_id值
  • end_log_pos：下一个事件的开始位置
  • thread_id：指示哪个线程执行了该事件
  • exec_time：在主服务器上，它代表执行事件的时间；在从服务器上，它代表从服务器的最终执行时间与主服务的开始执行时间之间的差值，这个差值可以作为备份相对于主服务器滞后多少的指标。
  • error_code：代表执行事件的结果。0意味着没有错误发生。

查看刚才的操作：

  1. 首先在基于语句的复制中执行了UPDATE语句，而且在二进制日志中记录了相同的语句。除了保存在服务器上，会话变量也被保存在二进制日志中，以在从库上复制相同的行为：
  1. BEGIN
  2. /*!*/;
  3. # at 326
  4. #190831 17:05:16 server id 100  end_log_pos 467 CRC32 0x4394accd     Query    thread_id=8    exec_time=0    error_code=0
  5. use `employees`/*!*/;
  6. SET TIMESTAMP=1567242316/*!*/;
  7. UPDATE salaries SET salary=salary*2 WHERE emp_no<10002
  8. /*!*/;
  9. # at 467
  10. #190831 17:05:26 server id 100  end_log_pos 498 CRC32 0x4d46db0a     Xid = 27
  11. COMMIT/*!*/;
  1. 当使用基于行的复制（PBR）时，会以二进制格式对整行（而不是语句）进行保存，而且二进制格式不能读取。可以观察长度，单个更新语句会生成很多数据。
  1. BEGIN
  2. /*!*/;
  3. # at 657
  4. #190831 17:08:48 server id 100  end_log_pos 719 CRC32 0xdb344355     Table_map: `employees`.`salaries` mapped to number 97
  5. # at 719
  6. #190831 17:08:48 server id 100  end_log_pos 1265 CRC32 0xe826fd88     Update_rows: table id 97 flags: STMT_END_F
  8. BINLOG '
  9. IDlqXRNkAAAAPgAAAM8CAAAAAGEAAAAAAAEACWVtcGxveWVlcwAIc2FsYXJpZXMABAMDCgoAAAEB
  10. AFVDNNs=
  11. IDlqXR9kAAAAIgIAAPEEAAAAAGEAAAAAAAEAAgAE//8AEScAAKrVAQDahA/ahg8AEScAANXqAADa
  12. hA/ahg8AEScAACzlAQDahg/ZiA8AEScAAJbyAADahg/ZiA8AEScAADQEAgDZiA/Zig8AEScAABoC
  13. AQDZiA/Zig8AEScAAEgIAgDZig/ZjA8AEScAACQEAQDZig/ZjA8AEScAACILAgDZjA/Zjg8AEScA
  14. AJEFAQDZjA/Zjg8AEScAAAwrAgDZjg/YkA8AEScAAIYVAQDZjg/YkA8AEScAALpEAgDYkA/Ykg8A
  15. EScAAF0iAQDYkA/Ykg8AEScAACxMAgDYkg/YlA8AEScAABYmAQDYkg/YlA8AEScAALRRAgDYlA/Y
  16. lg8AEScAANooAQDYlA/Ylg8AEScAAKhYAgDYlg/XmA8AEScAAFQsAQDYlg/XmA8AEScAABpxAgDX
  17. mA/Xmg8AEScAAI04AQDXmA/Xmg8AEScAAAJ5AgDXmg/XnA8AEScAAIE8AQDXmg/XnA8AEScAAJJ5
  18. AgDXnA/Xng8AEScAAMk8AQDXnA/Xng8AEScAAGqXAgDXng/WoA8AEScAALVLAQDXng/WoA8AEScA
  19. APCYAgDWoA/Wog8AEScAAHhMAQDWoA/Wog8AEScAANKYAgDWog/WpA8AEScAAGlMAQDWog/WpA8A
  20. EScAAEANAwDWpA8hHk4AEScAAKCGAQDWpA8hHk6I/Sbo
  21. '/*!*/;
  22. # at 1265
  23. #190831 17:09:00 server id 100  end_log_pos 1296 CRC32 0x3dbf7cf2     Xid = 50
  24. COMMIT/*!*/;
  1. 当使用MIXED格式时，UPDATE语句被记录为SQL语句，而INSERT语句以基于行的格式被记录，因为INSERT有非确定性的UUID()函数：
  1. BEGIN
  2. /*!*/;
  3. # at 1467
  4. #190831 17:12:48 server id 100  end_log_pos 1608 CRC32 0x813af03f     Query    thread_id=8    exec_time=0    error_code=0
  5. SET TIMESTAMP=1567242768/*!*/;
  6. UPDATE salaries SET salary=salary*2 WHERE emp_no<10002
  7. /*!*/;
  8. # at 1608
  9. #190831 17:14:13 server id 100  end_log_pos 1677 CRC32 0x8d7445ba     Table_map: `employees`.`departments` mapped to number 91
  10. # at 1677
  11. #190831 17:14:13 server id 100  end_log_pos 1755 CRC32 0x615a4bad     Write_rows: table id 91 flags: STMT_END_F
  13. BINLOG '
  14. ZTpqXRNkAAAARQAAAI0GAAAAAFsAAAAAAAMACWVtcGxveWVlcwALZGVwYXJ0bWVudHMAAv4PBP4Q
  15. oAAAAgP8/wC6RXSN
  16. ZTpqXR5kAAAATgAAANsGAAAAAFsAAAAAAAEAAgAC/wAEZDAxMCRiMzJhZmU1MC1jYmNmLTExZTkt
  17. OWVmNy0wMDBjMjkzNzRiZTatS1ph
  18. '/*!*/;
  19. # at 1755
  20. #190831 17:14:24 server id 100  end_log_pos 1786 CRC32 0xcd3f7743     Xid = 72
  21. COMMIT/*!*/;

提取的日志可以被传送给MySQL以回放事件。在重放二进制日志时最好使用force选项，这样即使force选项卡在某个点上，执行也不会停止。

  1. shell> sudo mysqlbinlog /data/mysql/binlogs/server1.000002 | mysql -f -h <remote_host> -u <username> -p

或者也可以先保存在文件中，稍后执行：

  1. shell> sudo mysqlbinlog /data/mysql/binlogs/server1.000002 > server1.binlog_extract
  2. shell> cat server1.binlog_extract | mysql -h <remote_host> -u <username> -p

根据时间和位置进行抽取

可以通过指定位置从二进制日志中提取部分数据。

假设你想做时间点恢复。例如在2017-08-19 12:18:00执行了DROP DATABASE命令，并且最新的可用备份是在2017-08-19 12:00:00做的，该备份已经恢复。现在，需要恢复从12:00:01至12:17:00的数据。如果提取完整的日志，它还将包含DROP DATABASE命令，该命令将再次擦除数据。

可以通过–start-datatime和–stop-datatime选项来指定提取数据的时间窗口。

  1. shell> sudo mysqlbinlog /data/mysql/binlogs/server1.000001 --start-datetime="2017-08-19 12:00:01" --stop-datetime="2017-08-19 12:17:00" > binlog_extract

使用时间窗口的缺点是，会失去灾难发生那一刻的事务。为避免这种情况，必须在二进制日志中使用事件的文件偏移量。

一个连续的备份会保存它已完成备份的所有binlog文件的偏移量。备份恢复后，必须从备份的偏移量中提取binlog。

假设备份的偏移量为471，执行DROP DATABASE命令的偏移量为1793。可以使用–start-position和–stop-position选项来提取偏移量之间的日志：

  1. shell> sudo mysqlbinlog /data/mysql/binlogs/server1.000001 --start-position=471 --stop-position=1793 > binlog_extract

要确保DROP DATABASE命令在提取的binlog中不再出现。

基于数据库进行提取

使用–database选项可以过滤特定数据库的事件。如果多次提交，则只有最后一个选项会被考虑。这对于基于行的复制非常有效。但对于基于语句的复制和MIXED，只有当选择默认数据库时才会提供输出。

以下命令从employees数据库中提取事件：

  1. shell> sudo mysqlbinlog /data/mysql/binlogs/server1.000001 --database=employees > binlog_extract

假设二进制日志是通过使用基于语句的日志记录执行以下语句而创建的：

  1. mysql>
  2. INSERT INTO test.t1 (i) VALUES(100);
  3. INSERT INTO db2.t2 (j) VALUES(200);
  5. USE test;
  6. INSERT INTO test.t1 (i) VALUES(101);
  7. INSERT INTO t1 (i) VALUES(102);
  8. INSERT INTO db2.t2 (j) VALUES(201);
  10. USE db2;
  11. INSERT INTO test.t1 (i) VALUES(103);
  12. INSERT INTO db2.t2 (j) VALUES(202);
  13. INSERT INTO t2 (j) VALUES(203);

mysqlbinlog —database=test 不输出前两个INSERT语句，因为没有默认数据库。

mysqlbinlog —database=test输出USE test后面的三条INSERT语句，但不是USE db2后面的三条INSERT语句。

因为没有默认数据库，mysqlbinlog —database=db2不输出前两条INSERT语句。

mysqlbinlog —database=db2不会输出USE test后的三条INSERT语句，但会输出在USE db2之后的三条INSERT语句。

提取行事件显示

默认情况下，基于行的复制日志显示为二进制格式。要查看行信息，必须将–verbose或者-v选项传递给mysqlbinlog。行事件的二进制格式以注释的伪SQL语句的形式显示，其中的行以###开始。可以看到，单个更新语句被改写为了每行的UPDATE语句。

  1. [root@www ~]#  mysqlbinlog /data/mysql/binlogs/server1.000002 \
  2. > --start-position=577 --stop-position=1265 --verbose

如果只想查看没有二进制行信息的伪SQL语句，可以指定–base64-output=”decode-rows”以及–verbose:

  1. [root@www ~]# mysqlbinlog /data/mysql/binlogs/server1.000002 \
  2. > --start-position=577 --stop-position=1265 \
  3. > --verbose --base64-output="decode-rows"

重写数据库名称

假设要将生产服务器上的employees数据库的二进制日志恢复为开发服务器上的employees_dev,可以使用–rewrite-db=’from_name->to_name’选项，这会将所有from_name重写为to_name。

要转换多个数据库，请多次指定该选项:

  1. shell> sudo mysqlbinlog /data/mysql/binlogs/server1.000001 \
  2. > --start-position=1499 --stop-position=1646 \
  3. > --rewrite-db='employees->employees_dev'

在恢复时，所有更改将应用于employees_dev数据库。

在恢复时禁用二进制日志

在恢复二进制日志的过程中，如果不希望mysqlbinlog进程创建二进制日志，则可以使用–disable-log-bin选项：

  1. shell> sudo mysqlbinlog /data/mysql/binlogs/server1.000001 \
  2. > --start-position=660 --stop-position=1298 \
  3. > --disable-log-bin > binlog_restore

可以看到SQL_LOG_BIN=0被写入binlog恢复文件中，这将防止创建binlog。

显示二进制日志文件中的事件

除了使用mysqlbinlog，还可以使用SHOW BINLOG EVENTS命令来显示事件。

  1. mysql> SHOW BINLOG EVENTS IN 'server1.000002' LIMIT 10;
  2. +----------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------+
  3. | Log_name       | Pos | Event_type     | Server_id | End_log_pos | Info                                                                    |
  4. +----------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------+
  5. | server1.000002 |   4 | Format_desc    |       100 |         124 | Server ver: 8.0.16, Binlog ver: 4                                       |
  6. | server1.000002 | 124 | Previous_gtids |       100 |         155 |                                                                         |
  7. | server1.000002 | 155 | Anonymous_Gtid |       100 |         234 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                    |
  8. | server1.000002 | 234 | Query          |       100 |         326 | BEGIN                                                                   |
  9. | server1.000002 | 326 | Query          |       100 |         467 | use `employees`; UPDATE salaries SET salary=salary*2 WHERE emp_no<10002 |
  10. | server1.000002 | 467 | Xid            |       100 |         498 | COMMIT /* xid=27 */                                                     |
  11. | server1.000002 | 498 | Anonymous_Gtid |       100 |         577 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                    |
  12. | server1.000002 | 577 | Query          |       100 |         657 | BEGIN                                                                   |
  13. | server1.000002 | 657 | Table_map      |       100 |         719 | table_id: 97 (employees.salaries)                                       |
  14. | server1.000002 | 719 | Update_rows    |       100 |        1265 | table_id: 97 flags: STMT_END_F                                          |
  15. +----------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------+
  16. 10 rows in set (0.00 sec)

也可以指定位置和偏移量：

  1. mysql> SHOW BINLOG EVENTS IN 'server1.000002' FROM 124 LIMIT 2,1;
  2. +----------------+-----+------------+-----------+-------------+-------+
  3. | Log_name       | Pos | Event_type | Server_id | End_log_pos | Info  |
  4. +----------------+-----+------------+-----------+-------------+-------+
  5. | server1.000002 | 234 | Query      |       100 |         326 | BEGIN |
  6. +----------------+-----+------------+-----------+-------------+-------+
  7. 1 row in set (0.00 sec)

忽略要写入二进制日志的数据库

可以通过在my.cnf中指定–binlog-do-db = db_name选项，来选择将哪些数据库写入二进制日志。要指定多个数据库，就必须使用此选项的多个实例。由于数据库的名字可以包含逗号，因此如果提供逗号分隔列表，则该列表将被视为单个数据库的名字。需要重启MySQL服务器才能使更改生效。

打开my.cnf并添加以下行:

  1. shell> sudo vim /etc/my.cnf
  2. [mysqld]
  3. binlog_do_db=db1
  4. binlog_do_db=db2

binlog_do_db上的行为从基于语句的日志记录更改为基于行的日志记录，就像mysqlbinlog实用程序中的–database选项一样。

在基于语句的日志记录中，只有那些默认数据库（即用USE选择的）的语句才会被写入二进制日志。使用binlog_do_db选项时应该非常小心，因为它的工作方式与你在使用基于语句的日志记录时的方式不同。

例1

如果服务器以–binlog_do_db=sales启动并且执行以下语句，则该UPDATE语句不会被记录：

  1. mysql> USE prices;
  2. mysql> UPDATE sales.january SET amount=amount+1000;

这种只检查默认数据库的行为主要是因为，单从语句来看，很难知道它是否应该被复制。如果没有必要，只检查默认数据库比检查所有数据库耗时更短。

例2

如果服务器以–binlog_do_db=sales启动，则即使在设置–binlog_do_db时未包括prices，也会记录以下UPDATE语句：

  1. mysql> USE sales;
  2. mysql> UPDATE prices.discounts SET percentage=percentage + 10;

因为当UPDATE语句执行时，sales是默认数据库，所以会记录该UPDATE语句。

在基于行的日志记录中，它仅限定于数据库db_name。只记录对db_name数据库中的表的更改，默认数据库对此没有影响。

与基于行的日志记录方式相比，基于语句的日志记录方式在执行–binlog_do_db处理方面的另一个重要区别是，后者涉及引用多个数据库的语句。假设服务器以–binlog_do_db=db1启动，并执行以下语句：

  1. mysql> USE db1;
  2. mysql> UPDATE db1.table1 SET col1 = 10, db2.table2 SET col2=20;

如果正在使用基于语句的日志记录，则这两个表的更新都会被写入二进制日志。但是，如果使用基于行的方式，则只会记录对table1的更改，不会记录对table2的更改，因为table2位于不同的数据库中。

同样，可以使用–binlog_ignore_db = db_name选项来指定不写入二进制日志的数据库。

迁移二进制日志

二进制日志会占用越来越多的空间，可能需要更改二进制日志的位置，可以按照以下步骤操作。单独更改log_bin是不够的，必须迁移所有二进制日志并在索引文件中更新位置。mysqlbinlogmove工具可以自动执行这些任务，简化你的工作。

首先需要先安装MySQL工具集，以使用mysqlbinlogmove脚本。

  1. [root@www ~]# wget https://downloads.mysql.com/archives/get/file/mysql-utilities-1.6.5-1.el7.noarch.rpm
  2. [root@www ~]# wget http://repo.mysql.com/yum/mysql-connectors-community/el/7/x86_64//mysql-connector-python-2.1.8-1.el7.x86_64.rpm
  3. [root@www ~]# rpm -ivh mysql-connector-python-2.1.8-1.el7.x86_64.rpm mysql-utilities-1.6.5-1.el7.noarch.rpm

步骤：
  1. 停止MySQL服务器的运行
  2. 启动mysqlbinlogmove工具。如果要将二进制日志从/data/mysql/binlogs更改为/binlogs，则应使用以下命令。如果base name不是默认名称，则必须通过–bin-log-basename选项设定base name:
  1. [root@www ~]# mysqlbinlogmove --bin-log-basename=server1 --binlog-dir=/data/mysql/binlogs /binlogs
  2. #
  3. # Moving bin-log files...
  4. # - server1.000001
  5. # - server1.000002
  6. # - server1.000003
  7. # - server1.000004
  8. # - server1.000005
  9. # - server1.000006
  10. # - server1.000007
  11. #
  12. #...done.
  13. #
  1. 编辑my.cnf文件并更新log_bin的新位置：
  1. [root@www ~]# vim /etc/my.cnf
  2. [mysqld]
  3. log_bin=/binlogs/server1
  1. 启动MySQL服务器

数据文件

在MySQL中每一个数据库都会在定义好（或者默认）的数据目录下存在一个以数据库名字命名的文件夹，用来存放该数据库中各种表数据文件。不同的MySQL存储引擎有各自不同的数据文件。如MyISAM用‘.MYD’作为扩展名，InnoDB用‘.ibd’，ARCHIVE用‘.arc’，CSV用‘.csv’，等等。

如何查看MySQL支持的存储引擎：

  1. mysql> show engines;
  2. +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
  3. | Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
  4. +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
  5. | FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
  6. | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
  7. | InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
  8. | PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
  9. | MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
  10. | MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
  11. | BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
  12. | CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
  13. | ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
  14. +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
  15. 9 rows in set (0.00 sec)

查看当前使用的存储引擎：

  1. mysql> show variables like '%storage_engine%';
  2. +---------------------------------+-----------+
  3. | Variable_name                   | Value     |
  4. +---------------------------------+-----------+
  5. | default_storage_engine          | InnoDB    |
  6. | default_tmp_storage_engine      | InnoDB    |
  7. | disabled_storage_engines        |           |
  8. | internal_tmp_mem_storage_engine | TempTable |
  9. +---------------------------------+-----------+
  10. 4 rows in set (0.00 sec)

创建一个数据库testdb,并在数据库中创建一个表：

  1. mysql> CREATE DATABASE testdb default character set utf8;
  2. Query OK, 1 row affected, 1 warning (0.05 sec)
  4. mysql> CREATE TABLE testdb.tb1 (id int,name varchar(20));
  5. Query OK, 0 rows affected (0.08 sec)

查看数据库数据目录下会存在一个以数据库名字命名的文件夹,文件夹下存在刚创建的表，后缀是.idb，说明是InnoDB存储引擎。

  1. [root@www ~]# ls -ld /var/lib/mysql/testdb/
  2. drwxr-x--- 2 mysql mysql 21 Sep  4 22:58 /var/lib/mysql/testdb/
  3. [root@www ~]# ls -l /var/lib/mysql/testdb/
  4. total 80
  5. -rw-r----- 1 mysql mysql 114688 Sep  4 22:58 tb1.ibd

如果想更改默认存储引擎，可以修改/etc/my.cnf文件

  1. [root@www ~]# vim /etc/my.cnf
  2. [mysqld]
  3. default_storage_engine=InnoDB