事务日志 transaction log
MariaDB [(none)]> show variables like "%innodb_log%";
+---------------------------+---------+
| Variable_name | Value |
+---------------------------+---------+
| innodb_log_block_size | 512 |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
+---------------------------+---------+
参数 innodb_flush_log_at_trx_commit
- 选择参数2可以提高log io性能。
-
高性能磁盘放置事务日志，或者单独的分区
image.png
错误日志 error log
- mysqld启动和关闭过程中输出的事件信息
- mysqld运行中产生的错误信息
- event scheduler运行一个event时产生的日志信息
- 在主从复制架构中的从服务器上启动从服务器线程时产生的信息
MariaDB [(none)]> show variables like "%log_error%";
+---------------+------------------------------+
| Variable_name | Value |
+---------------+------------------------------+
| log_error | /var/log/mariadb/mariadb.log |
+---------------+------------------------------+
通用日志 general log
记录对数据库的通用操作，包括错误的SQL语句，默认关闭，一般用作排错，不用在生产环境
相关设置
- general_log=ON|OFF
- general_log_file=HOSTNAME.log
- log_output=TABLE|FILE|NONE
慢查询日志 slow query log
记录执行查询时长超出指定时长的操作
- slow_query_log=ON|OFF 开启或关闭慢查询
- long_query_time=N 慢查询的阀值，单位秒,
- slow_query_log_file=HOSTNAME-slow.log 慢查询日志文件
log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,
query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
# 上述查询类型且查询时长超过long_query_time，则记录日志
- log_queries_not_using_indexes=ON 不使用索引或使用全索引扫描，不论
是否达到慢查询阀值的语句是否记录日志，默认OFF，即不记录 - log_slow_rate_limit = 1 多少次查询才记录，mariadb特有
- log_slow_verbosity= Query_plan,explain 记录内容
利用profile分析sql查询语句
MariaDB [hellodb]> show variables like "%profiling";
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| have_profiling | YES |
| profiling | OFF |
+----------------+-------+
2 rows in set (0.00 sec)
MariaDB [hellodb]> set profiling=ON;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> select sleep(1) from teachers;
+----------+
| sleep(1) |
+----------+
| 0 |
| 0 |
| 0 |
| 0 |
+----------+
4 rows in set (4.01 sec)
MariaDB [hellodb]> show profiles;
+----------+------------+-------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------+
| 1 | 4.00351854 | select sleep(1) from teachers |
+----------+------------+-------------------------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> show profile for query 1;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000086 |
| checking permissions | 0.000009 |
| Opening tables | 0.000020 |
| After opening tables | 0.000005 |
| System lock | 0.000004 |
| Table lock | 0.000002 |
| After table lock | 0.000005 |
| init | 0.000013 |
| optimizing | 0.000008 |
| statistics | 0.000029 |
| preparing | 0.000009 |
| executing | 0.000003 |
| Sending data | 0.000030 |
| User sleep | 1.000813 |
| User sleep | 1.000540 |
| User sleep | 1.000857 |
| User sleep | 1.001032 |
| end | 0.000018 |
| query end | 0.000010 |
| closing tables | 0.000006 |
| freeing items | 0.000004 |
| updating status | 0.000015 |
| cleaning up | 0.000002 |
+----------------------+----------+
数据库表整理
MariaDB [hellodb]> optimize table testlog;
+-----------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------------+----------+----------+-------------------------------------------------------------------+
| hellodb.testlog | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| hellodb.testlog | optimize | status | OK |
+-----------------+----------+----------+-------------------------------------------------------------------+
二进制日志 binary log
二进制日志服务器变量：
sql_log_bin=ON|OFF # 是否开启二进制文件
log_bin=/path/bin_log_file # 指定二进制文件位置，写入配置文件
binlog_format=STATEMENT|ROW|MIXED #二进制日志记录的格式，默认为STATEMENT，生产环境建议ROW格式
max_binlog_size=1073741824 #单个二进制文件最大大小为1G，超过回滚
sync_binlog=1|0 #是否启动二进制日志即时同步磁盘功能，默认为0
expire_logs_days=N #二进制日志可以自动删除的天数。默认为0，不删除
查看二进制日志
#使用中的二进制日志文件列表，及大小
MariaDB [hellodb]> show binary logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 245 |
+--------------------+-----------+
# 使用中的二进制日志文件
MariaDB [hellodb]> show master status;
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000001 | 245 | | |
+--------------------+----------+--------------+------------------+
# 查看二进制日志中的内容
# SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
# 查看从23775889后面的log
show binlog events in 'mysql-bin.000001' from 23775889
# 查看从23775889后跳过2个看下3个记录
show binlog events in 'mysql-bin.000001' from 23775889 limit 2，3
二进制日志客户端工具mysqlbinlog
mysqlbinlog --start-position=67687 --stop-position=7527 /path/to/bin_log_file -v
mysqlbinlog --start-datetime="2010-03-30 23:12:00" --stop-datetime="2018-01-30 20:35:22" /path/to/bin_log_file/ -vvv
# 生成还原文件
mysqlbinlog binlogfile -v > /data/backup.sql
