1, 如何查看 mysql 执行命令历史
#.mysql_history 此文件记录了在 mysql 中执行命令的历史
2, 事务日志:
transaction log: 事务型存储引擎自行的管理和使用.
(redo log) 在一个事务完成提交后, 还没存入磁盘的情况下, 会记录到事务日志. 开机重启后将自动把已经提交的事务重做.
(uodo log) 如果事务未完成, 系统崩溃, 开机重启之后将会把未完成得事务撤销.
相关变量:
- (root@localhost) [(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_log_file_size 默认大小为 5M,innodb_log_files_in_group 个数大小默认为 2 个. 这两个值生产中强烈建议大家可以调大. 不支持动态更改, 写进配置文件中即可.
注: 修改 innodb_log_file_size 时需要先删除原来的日志文件
如果事务操作的数据大于事务日志文件总大小, 在执行 ROLLBACK 时: 可以撤销, 但是数据文件会被占用, 需要使用 OPTIMIZE TABLE 命令来释放数据空间;
使用 TRUNCATE 删除表记录可以缩小占用空间, DELETE 命令不会释放磁盘空间, 如果需要释放需要使用 OPTIMIZE 命令从新整理碎片来释放空间.
3, 错误日志:
mysqld 启动和关闭过程中输出的事件信息
mysqld 运行中产生的错误信息
event scheduler 运行一个 event 时产生的日志信息
在主从复制架构中的从服务器上启动从服务器线程时产生的信息
相关变量:
(root@localhost) [(none)]> SHOW GLOBAL VARIABLES LIKE 'log_error'; 错误日志文件的路径
- +---------------+------------------------------+
- | Variable_name | Value |
- +---------------+------------------------------+
- | log_error | /var/log/mariadb/mariadb.log |
- +---------------+------------------------------+
错误文件路径:
log_error=/PATH/TO/LOG_ERROR_FILE 添加至配置文件中, 自定义路径.
log_warnings=1|0 默认值 1(是), 是否记录警告信息至错误日志文件
4, 通用日志:
记录对数据库得通用操作, 包括错误的 sql 语句
文件: file, 默认值
表: table
通用日志相关设置:
general_log=ON|OFF #默认为 OFF 不记录
为全局变量, 需写入配置文件中, 才可永久保存. 建议无特殊需求此项不启动.
- (root@localhost) [(none)]> SHOW VARIABLES LIKE 'general_log%';
- +------------------+-------------+
- | Variable_name | Value |
- +------------------+-------------+
- | general_log | OFF | #开启或关闭通用日志. 默认为关闭
- | general_log_file | centes7.log | #通用日志文件的文件名,/var/lib/mysql/HOSTNAME.log
- +------------------+-------------+
- (root@localhost) [(none)]> SHOW VARIABLES LIKE 'log_output';
- +---------------+-------+
- | Variable_name | Value |
- +---------------+-------+
- | log_output | FILE | -# 以什么方式存放日志,(TABLE|FILE|NONE)
- +---------------+-------+
mysql.general_log: 表存放查询日志, 前提是以表方式存放的.
5, 慢查询日志:
记录执行查询时长超出指定时长的操作
慢查询相关变量:
slow_query_log=ON|OFF : 为全局变量, 支持动态更改.
- (root@localhost) [(none)]> SHOW VARIABLES LIKE 'general_log%';
- +------------------+-------------+
- | Variable_name | Value |
- +------------------+-------------+
- | general_log | OFF | #开启或关闭慢查询日志, 默认关闭
- | general_log_file | centes7.log | #慢查询的日志文件.
- +------------------+-------------+
- slow_query_log=ON|OFF
- (root@localhost) [(none)]> SHOW VARIABLES LIKE 'long_query_time';
- +-----------------+-----------+
- | Variable_name | Value |
- +-----------------+-----------+
- | long_query_time | 10.000000 | #慢查询的阀值, 单位秒, 查询时间超过 10 则记录到慢查询日志
- +-----------------+-----------+
slow_query_log_file=HOSTNAME-slow.log 慢查询日志文件 , 路径可手动指定.
- MariaDB [school]> SHOW VARIABLES LIKE 'log_slow%';
- +---------------------+-------+| Variable_name | Value |
- +---------------------+-------+| log_slow_rate_limit | 1 | # 多少次查询才记录, mariadb 特有 | log_slow_verbosity | | #记录内容的详细程度 (Query_plan,explain)
- +---------------------+-------+
log_queries_not_using_indexes=ON 不使用索引也没有达到慢查询阀值的语句是否记录日志, 默认 OFF, 即不记录. 为全局变量, 支持动态更改.
- (root@localhost) [(none)]> SHOW VARIABLES LIKE 'log_queries_not_using_indexes';
- +-------------------------------+-------+
- | Variable_name | Value |
- +-------------------------------+-------+
- | log_queries_not_using_indexes | OFF | #记录没有利用索引查询, 而使用全文扫描的语句; 默认关闭, 建议开启
- +-------------------------------+-------+
log_slow_filter : 根据查询结果过滤
- admin
- filesort
- filesort_on_disk
- full_join
- full_scan
- query_cache
- query_cache_miss
- tmp_table
- tmp_table_on_disk
利用 profiling 跟踪查询语句查询语句使用得时间.
- (root@localhost) [(none)]> show variables like 'profil%';
- +------------------------+-------+
- | Variable_name | Value |
- +------------------------+-------+
- | profiling | ON | #开启或关闭此功能, 默认为 OFF
- +------------------------+-------+
- (root@localhost) [(none)]> set profiling=ON; #开启语句查询功能
- (root@localhost) [(none)]> SHOW profiles; #查询语句执行列表
- +----------+------------+-----------------------------------------------------+
- | Query_ID | Duration | Query |
- +----------+------------+-----------------------------------------------------+
- | 1 | 0.00019069 | ELECT stuid,name,age FROM students WHERE stuid = 2 |
- | 2 | 0.00024737 | SELECT stuid,name,age FROM students WHERE stuid = 2 |
- | 3 | 0.00069597 | SHOW VARIABLES LIKE 'log_slow%' |
- +----------+------------+-----------------------------------------------------+
(root@localhost) [(none)]> show profile FOR query 3 ; 查询指定编号的 SQL 语句的详细执行过程.
- +----------------------+----------+
- | Status | Duration |
- +----------------------+----------+
- | starting | 0.000052 |
- | Opening tables | 0.000106 |
- | After opening tables | 0.000007 |
- | System lock | 0.000002 |
- +----------------------+----------+
6, 二进制日志:
记录导致数据改变或潜在导致数据改变的 SQL 语句, 记录已提交的日志, 功能: 通过 "重放" 日志文件中的事件来生成数据副本, 不依赖于存储引擎类型.
开启二进制日志: 默认为关闭. 建议二进制日志和数据文件分开存放.
开启二进制文件: 在 / my.cnf 文件 [mysqld] 下添加 log_bin=[PATH/somefile], 可默认路径
默认二进制日志在数据库目录下, mariadb-bin.000001 为二进制数据文件. mariadb-bin.index 为二进制目录索引文件.
记录二进制日志的三种方法:
基于语句记录: 每一条更改数据的语句, 记录为一条记录, 节省空间, 系统默认模式. 不推荐使用, 存在一定得安全风险, 容易丢失数据.
基于行记录: 每一行的改变都会将使其改变的语句记录为一条语句, 日志量会很大. 但对于数据得安全性大.
基于混合模式记录: mixed, 让系统自行判定该基于哪种方式进行
格式配置
- (root@localhost) [(none)]> show variables like '%binlog_format%';
- +---------------+-----------+
- | Variable_name | Value |
- +---------------+-----------+
- | binlog_format | STATEMENT | #记录方式, 基于语句记录.
- +---------------+-----------+
- SET binlog_format='ROW|STATEMENT|MIXED'; #修改二进制日志记录方式
二进制日志相关变量:
(root@localhost) [(none)]> SHOW VARIABLES LIKE 'sql_log_bin';
是否记录二进制日志, 默认为 ON
(root@localhost) [(none)]> SHOW VARIABLES LIKE 'log_bin';
指定文件位置; 默认为 OFF, 表示不启用二进制日志功能, 上述两项都开启才可
- (root@localhost) [(none)]> show master logs; #查看 mariadb 自行管理使用中的二进制日志文件列表
- +--------------------+-----------+
- | Log_name | File_size |
- +--------------------+-----------+
- | mariadb-bin.000001 | 245 | #当前二进制文件的大小
- +--------------------+-----------+
- (root@localhost) [(none)]> show master status; #查询当前使用中的二进制文件记录在哪一个时间点.
- +--------------------+----------+--------------+------------------+
- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
- +--------------------+----------+--------------+------------------+
- | mariadb-bin.000001 | 245 | | |
- +--------------------+----------+--------------+------------------+
+--------------------+----------+--------------+------------------+MariaDB [(none)]> SHOW BINLOG EVENTS IN 'mariadb-bin.000004' FROM 1 LIMIT 2,3\G -- 查看二进制文件中的指定内容
- (root@localhost) [(none)]> SHOW VARIABLES LIKE 'max_binlog_size';
- +-----------------+------------+
- | Variable_name | Value |
- +-----------------+------------+
- | max_binlog_size | 1073741824 | #单个二进制日志文件的最大体积, 到达最大值会自动滚动, 默认为 1G
- +-----------------+------------+
- (root@localhost) [(none)]> SHOW VARIABLES LIKE 'sync_binlog';
- +---------------+-------+
- | Variable_name | Value |
- +---------------+-------+
- | sync_binlog | 0 | #设定是否启动二进制日志即时同步磁盘功能, 默认 0, 由操作系统负责同步日志到磁盘
- +---------------+-------+
- (root@localhost) [(none)]> SHOW VARIABLES LIKE 'expire_logs_days';
- +------------------+-------+
- | Variable_name | Value |
- +------------------+-------+
- | expire_logs_days | 0 | #二进制日志可以自动删除的天数. 默认为 0, 即不自动删除
- +------------------+-------+
mysqlbinlog: 命令
二进制日志的客户端命令工具
语法: mysqlbinlog[OPTIONS]log_file...
常用选项:
- --start-position=# 指定开始位置
- --stop-position=# 指定结束位置
--start-datetime=(YYYY-MM-DD hh:mm:ss) 指定结束时间
--stop-datetime=(YYYY-MM-DD hh:mm:ss) 指定结束时间
--base64-output[=name] 以编码形式显示
-v |-vv |-vvv |-vvvv 显示详细信息
- [root@centos7 mysql]# mysqlbinlog --start-position=528864 --stop-position=529019 mariadb-bin.000004 --base64-output=decode-row -v# at 528864
- #180611 20:59:46 server id 1 end_log_pos 528992 Query thread_id=29 exec_time=0 error_code=0use `school`/*!*/;SET TIMESTAMP=1528721986/*!*/;INSERT students(StuID,Name,Age,Gender) VALUES (26,'Tom',22,'M') #改变数据的 SQL 语句 /*!*/;
事件发生的日期和时间: 180611 20:59:46
事件发生的服务器标识: server id 1
事件的结束位置: end_log_pos 528992
事件的类型: Query
事件发生时所在服务器执行此事件的线程的 ID:thread_id=29
语句的时间戳与将其写入二进制文件中的时间差: exec_time=0
错误代码: error_code=0
事件内容:
GTID:Global Transaction ID,mysql5.6 以 mariadb10 以上版本专属属性: GTID
使用 mysqlbinlog mariadb-bin.000001> file.sql 命令重定向到 SQL 文件中,
再使用 mysql -uroot -p <file.sql 命令可以直接导入数据, 实现了备份还原的功能
二进制日志的管理
MariaDB [(none)]> PURGE BINARY LOGS TO 'mariadb-bin.000003'; 删除到 03, 注意: 03 不删除.
MariaDB [(none)]> PURGE BINARY LOGS BEFORE '2018-01-23'; -- 删除 2018-01-23 之前的日志
MariaDB [(none)]> PURGE BINARY LOGS BEFORE '2017-03-22 09:25:30';
MariaDB [(none)]> RESET MASTER; -- 删除所有二进制日志, index 文件重新记数
master 第一次启动时执行, MariaDB10.1.6 开始支持 TO #
MariaDB [(none)]> FLUSH LOGS; -- 手动触发日志滚动, 切换日志文件.
来源: http://www.jianshu.com/p/4e486f5d5d82