mysql5.5参考手册
https://dev.mysql.com/doc/refman/5.5/en/preface.html
错误日志
错误日志记录的是mysql的启动和停止以及运行过程中发生错误的信息。
- 配置错误日志：/etc/my.cnf
[mysqld]
port=3406
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/sunpy.err
pid-file=/var/run/mysqld/mysqld.pid
user=mysql
或者使用SQL语句，列出系统参数show variables查看：
mysql> show variables like 'log_error';
+---------------+--------------------+
| Variable_name | Value |
+---------------+--------------------+
| log_error | /var/log/sunpy.err |
+---------------+--------------------+
1 row in set (0.01 sec)
- 例子Sunpy.err：
2019-01-10T15:18:57.344147Z 0 [Note] mysqld (mysqld 5.7.24) starting as process 24617 ...
2019-01-10T15:18:57.347483Z 0 [Note] InnoDB: PUNCH HOLE support available
2019-01-10T15:18:57.347506Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-01-10T15:18:57.347511Z 0 [Note] InnoDB: Uses event mutexes
2019-01-10T15:18:57.347514Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-01-10T15:18:57.347518Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2019-01-10T15:18:57.347521Z 0 [Note] InnoDB: Using Linux native AIO
2019-01-10T15:18:57.347796Z 0 [Note] InnoDB: Number of pools: 1
2019-01-10T15:18:57.347903Z 0 [Note] InnoDB: Using CPU crc32 instructions
2019-01-10T15:18:57.349404Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2019-01-10T15:18:57.357926Z 0 [Note] InnoDB: Completed initialization of buffer pool
2019-01-10T15:18:57.360060Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2019-01-10T15:18:57.371937Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2019-01-10T15:18:57.383016Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2019-01-10T15:18:57.383063Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2019-01-10T15:18:57.486323Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2019-01-10T15:18:57.487098Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2019-01-10T15:18:57.487109Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2019-01-10T15:18:57.487977Z 0 [Note] InnoDB: Waiting for purge to start
2019-01-10T15:18:57.538133Z 0 [Note] InnoDB: 5.7.24 started; log sequence number 5701546
2019-01-10T15:18:57.538459Z 0 [Note] Plugin 'FEDERATED' is disabled.
2019-01-10T15:18:57.543119Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2019-01-10T15:18:57.543150Z 0 [ERROR] InnoDB: Cannot open '/var/lib/mysql/ib_buffer_pool' for reading: Permission denied
2019-01-10T15:18:57.625995Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2019-01-10T15:18:57.626657Z 0 [Warning] CA certificate ca.pem is self signed.
2019-01-10T15:18:57.627790Z 0 [Note] Server hostname (bind-address): '*'; port: 3406
2019-01-10T15:18:57.627832Z 0 [Note] IPv6 is available.
2019-01-10T15:18:57.627841Z 0 [Note] - '::' resolves to '::';
2019-01-10T15:18:57.627862Z 0 [Note] Server socket created on IP: '::'.
2019-01-10T15:18:57.822797Z 0 [Note] Event Scheduler: Loaded 0 events
2019-01-10T15:18:57.822938Z 0 [Note] mysqld: ready for connections.
Version: '5.7.24' socket: '/var/lib/mysql/mysql.sock' port: 3406 MySQL Community Server (GPL)
通过日志可以看到错误：
2019-01-10T15:18:57.543150Z 0 [ERROR] InnoDB: Cannot open '/var/lib/mysql/ib_buffer_pool' for reading: Permission denied
意思是Innodb缓冲池没有读权限，分配权限即可。
慢查询日志
慢查询日志主要是用来分析sql语句的执行效率，优化的时候使用。慢查询的操作就是设置一个阙值，然后运行时间超过该值的sql语句会被记录到慢查询的日志中。
方式1：命令行
- 查看是否启用了慢查询：使用系统参数show variables
mysql> show variables like '%slow_query_log%';
+---------------------+-------------------------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/izuf6ea4rfo45y44oj74jhz-slow.log |
+---------------------+-------------------------------------------------+
2 rows in set (0.00 sec)
- 开启慢查询日志： 使用set命令
mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%slow_query_log%';
+---------------------+-------------------------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/izuf6ea4rfo45y44oj74jhz-slow.log |
+---------------------+-------------------------------------------------+
2 rows in set (0.00 sec)
方式2：配置文件
[mysqld]
port=3406
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# configure error log
log-error=/var/log/sunpy.err
# configure slow query log
# configure slow query log switch
slow_query_log =1
# configure slow query log file path
slow_query_log_file=/var/log/sunpy-slow.log
# configure slow query threshold
long_query_time=1
pid-file=/var/run/mysqld/mysqld.pid
# configure mysqld process start by mysql user
user=mysql
例子
操作：
select * from single_user;
查看慢查询日志sunpy-slow.log：
# Time: 2019-01-11T07:32:07.980236Z
# User@Host: root[root] @ localhost [] Id: 4
# Query_time: 4.302931 Lock_time: 0.000124 Rows_sent: 3695748 Rows_examined: 3695748
use single-app-db;
SET timestamp=1547191927;
select * from single_user;
不使用索引：是否将不使用索引的查询记录到慢查询日志中
log_queries_not_using_indexes=1
mysqldumpslow工具的使用
-s 排序方式
c： 访问计数
l： 锁定时间
r：返回记录
t： 查询时间
al：平均锁定时间
ar：平均返回记录数
at：平均查询时间
-t 取前面几条记录
-g 使用正则表达式筛选记录
日志查询
对于数据库请求的信息，默认文件为主机名.log
# 查看主机名
mysql> system hostname;
izuf6ea4rfo45y44oj74jhz
开启日志
mysql> show variables like '%general_log%';
+------------------+--------------------------------------------+
| Variable_name | Value |
+------------------+--------------------------------------------+
| general_log | OFF |
| general_log_file | /var/lib/mysql/izuf6ea4rfo45y44oj74jhz.log |
+------------------+--------------------------------------------+
2 rows in set (0.00 sec)
mysql> set global general_log=1;
Query OK, 0 rows affected (0.04 sec)
mysql> show variables like '%general_log%';
+------------------+--------------------------------------------+
| Variable_name | Value |
+------------------+--------------------------------------------+
| general_log | ON |
| general_log_file | /var/lib/mysql/izuf6ea4rfo45y44oj74jhz.log |
+------------------+--------------------------------------------+
2 rows in set (0.00 sec)
查看izuf6ea4rfo45y44oj74jhz.log
[root@izuf6ea4rfo45y44oj74jhz log]# vim /var/lib/mysql/izuf6ea4rfo45y44oj74jhz.log
mysqld, Version: 5.7.24-log (MySQL Community Server (GPL)). started with:
Tcp port: 3406 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
2019-01-11T09:14:55.028584Z 6 Query show variables like '%general_log%'
2019-01-11T09:17:56.227351Z 6 Quit
二进制日志
二进制日志主要是记录对数据库的修改操作。
开启二进制日志：
server-id = 1
log-bin = binlog
log-bin-index = binlog.index
mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/binlog |
| log_bin_index | /var/lib/mysql/binlog.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 binary logs;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| binlog.000001 | 198 |
| binlog.000002 | 154 |
+---------------+-----------+
2 rows in set (0.00 sec)
服务器配置参数
https://dev.mysql.com/doc/refman/5.5/en/server-option-variable-reference.html
网友评论