innodb_flush_log_at_trx_commit=0，在提交事务时，InnoDB不会立即触发将缓存日志写到磁盘文件的操作，而是每秒触发一次缓存日志回写磁盘操作，并调用操作系统fsync刷新IO缓存。innodb_flush_log_at_trx_commit=1，在每个事务提交时，InnoDB立即将缓存中的redo日志回写到日志文件，并调用操作系统fsync刷新IO缓存。innodb_flush_log_at_trx_commit=2，在每个事务提交时，InnoDB立即将缓存中的redo日志回写到日志文件，但并不马上调用fsync来刷新IO缓存，而是每秒只做一次磁盘IO缓存刷新操作。
如果设置innodb_flush_log_at_trx_commit=0,在mysqld进程崩溃的时候,就会丢失最后1秒的事务。
如果设置innodb_flush_log_at_trx_commit=2,只有在操作系统崩溃或者断电的时候才会丢失最后1秒的数据。
mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 10.3.23-MariaDB-0+deb10u1 Debian 20
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> set global sync_binlog=0;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> show variables like "%innodb_flush%";
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | fsync |
| innodb_flush_neighbors | 1 |
| innodb_flush_sync | ON |
| innodb_flushing_avg_loops | 30 |
+--------------------------------+-------+
6 rows in set (0.001 sec)
MariaDB [(none)]> set global innodb_flush_log_at_trx_commit=2;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> show variables like "%innodb_flush%";
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 2 |
| innodb_flush_method | fsync |
| innodb_flush_neighbors | 1 |
| innodb_flush_sync | ON |
| innodb_flushing_avg_loops | 30 |
+--------------------------------+-------+
6 rows in set (0.000 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.003 sec)
MariaDB [(none)]> exit;
Bye
