MySQL 로깅 시스템의 세부 사항을 설명하는 샘플 코드 공유

이 글은 MySQL의 로그 시스템에 대한 자세한 정보를 요약해서 소개하고 있습니다. 시스템은 이를 알고 있으므로 프로젝트의 중간 및 후반 단계에서 로그를 기반으로 최적화 및 업그레이드 결정을 내리는 경우가 많습니다. 그래서 MySQL을 배울 때 당연히 로깅 부분을 놓칠 수 없습니다. 인터뷰에서 논의된 최적화는 모두 로그에서 파생되었습니다. mysql 로그를 체계적으로 연구하면 문제를 정확하게 찾아내고 업무 수준을 향상시키는 데 도움이 됩니다. 또한 다음 일련의 로그는 DBA의 운영 및 유지 관리에 중점을 두고 MySQL의 모든 측면의 구성을 체계적으로 이해하고 자신과 적을 알고 MySQL을 편리한 데이터웨어 하우스로 만들어줍니다.

1. MySQL의

로그 유형유형기본적으로 모든 MySQL 로그는 데이터베이스의 루트 디렉터리에 파일 형식으로 저장됩니다. :

[root@roverliang data]# pwd
/usr/local/webserver/extend_lib/mysql/data
[root@roverliang data]# ls
auto.cnf ibdata1 ib_logfile0 ib_logfile1 mysql mytest performance_schema roverliang roverliang.err roverliang.pid test

MySQL의 로그 유형은 다음과 같습니다.

1. 오류 로그(error), MySQL 서비스 인스턴스의 시작, 실행 또는 중지와 같은 관련 정보입니다.

2. 일반 쿼리 로그(일반), MySQL 서비스 인스턴스에서 실행되는 모든 SQL 문 또는 MySQL 명령입니다.

3. 바이너리 로그(binary), select 및 show 문을 제외한 데이터베이스에서 실행되는 모든 업데이트 문입니다.
4. 느린 쿼리 로그(slow), 실행 시간이 long_query_time 설정 값을 초과하는 SQL 문 또는 인덱스를 사용하지 않는 SQL 문입니다.

2. MySQL 로그 캐시

캐시가 중요한 역할을 해야 하는 빠르고 안정적인 시스템입니다. MySQL 로그 처리도 캐싱 메커니즘을 사용합니다. MySQL 로그는 초기에 MySQL 서버의 메모리에 저장되며, 지정된 저장 용량을 초과하면 메모리에 있는 로그는 외부 메모리에 기록(또는 플러시)되어 데이터베이스 테이블 형태로 하드 디스크에 영구 저장됩니다. 또는 파일.

3. MySQL 오류 로그(오류 로그)

MySQL의 오류 로그는 주로 MySQL 서비스 인스턴스의 각 시작 및 중지에 대한 자세한 정보를 기록합니다. 에서 생성된 경고 또는

오류 메시지

. 다른 로그와 달리 MySQL의 오류 로그는 켜져 있어야 하며 끌 수 없습니다. 기본적으로 오류 로그 파일 이름은 호스트 이름.err입니다. 그러나 오류 로그에는 모든 오류 정보가 기록되지는 않습니다. MySQL 서비스 인스턴스 실행 중에 발생하는 심각한 오류(Critical)만 기록됩니다.

mysql> show variables like 'log_error'\G
*************************** 1. row ***************************
Variable_name: log_error
Value: /usr/local/webserver/extend_lib/mysql/data/roverliang.err
1 row in set (0.02 sec)

4. MySQL 일반 쿼리 로그(일반 로그)

MySQL 일반 쿼리 로그에는 선택, 업데이트, 삽입 등 MySQL 서비스 인스턴스의 모든 작업이 기록됩니다. 삭제 등의 작업, 작업이 성공적으로 수행되었는지 여부. 또한 연결 성공 여부와 관계없이 MySQL 클라이언트와 MySQL 서버 간의 연결 및 연결 끊김에 대한 정보도 있습니다. MySQL 일반 쿼리 로그와 관련된 세 가지 매개변수가 있습니다.

[]()general_log

mysql> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log  | OFF  |
+---------------+-------+
1 row in set (0.01 sec)

@@global.general_log = 1로 설정하면 일반 쿼리 로그를 활성화할 수 있습니다.

mysql> set @@global.general_log =1;
mysql> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log  | ON  |
+---------------+-------+

그러나 이런 방식으로 MySQL의

변수

를 수정하면 현재 MySQL 인스턴스가 실행되는 동안에만 적용됩니다. MySQL이 다시 시작되면 기본 상태. 이를 영구적으로 만드는 방법은 mysql의 my.cnf 파일을 수정하는 것입니다. 추가:

general_log = 1 
general_log_file

config 파일 이후에 일반 쿼리 로그가 켜지면 MySQL 서비스 인스턴스가 자동으로 일반 쿼리 로그 파일을 생성합니다. General_log_file 매개변수는 파일의 물리적 위치를 설정합니다. 일반 쿼리 로그 파일.

mysql> show variables like 'general_log_file';
+------------------+-----------------------------------------------------------+
| Variable_name  | Value                           |
+------------------+-----------------------------------------------------------+
| general_log_file | /usr/local/webserver/extend_lib/mysql/data/roverliang.log |
+------------------+-----------------------------------------------------------+

참고: 일반 쿼리 로그는 MySQL의 거의 모든 작업을 기록하므로 데이터 액세스가 빈번한 데이터베이스 서버의 경우 MySQL의 일반 쿼리 로그를 켜면 데이터베이스의 성능이 저하됩니다. 따라서 일반 쿼리 로그를 끄는 것이 좋습니다. 특별한 기간에만 일부 특수 쿼리 로그를 추적해야 하는 경우 일반 쿼리 로그를 임시로 열 수 있습니다.

log_output

log_output 매개변수는 일반 쿼리 로그와 느린 쿼리 로그의 내용이 데이터베이스 테이블에 저장되도록 설정합니다. set @@global.log_output='table'을 사용하면 일반 쿼리 로그와 느린 쿼리 로그를 mysql 시스템 데이터베이스의 일반 테이블과 Slow_log 테이블에 저장할 수 있습니다. 이 두 테이블의 스토리지 엔진은 이제부터 새로운 일반 쿼리 로그의 내용을 볼 때 SQL 문을 사용할 수 있습니다.

set @@global.log_output = 'table';
mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output  | TABLE |
+---------------+-------+

5. (느린 로그)

느린 쿼리 로그와 관련된 질문입니다. 인터뷰 중에 면접관은 이 문제에 대해 이야기하는 것을 매우 좋아합니다. 과거에는 MySQL 마스터-슬레이브 아키텍처에 대해서만 이야기하고 MySQL을 모든 측면에서 최적화할 수 있었지만 느린 쿼리 및 관련 구성을 활성화하는 방법을 실제로 이해하지 못했습니다.

使用MySQL慢查询日志可以有效的跟踪 执行时间过长 或者 没有使用索引的查询语句。这种包括select 语句，update语句，delete语句，以及insert语句，为优化查询提供帮助。与普通查询日志不同的另一个区别在于，慢查询日志只包含成功执行过的查询语句。与MySQL慢查询日志有关的参数有5个。

1、slow_query_log

slow_query_log 设置慢查询日志是否开启。

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF  |
+----------------+-------+

2、slow_query_log_file

慢查询日志一旦开启，MySQL实例将自动创建慢查询日志文件。slowquerylog_file 所指定的文件，存放慢查询日志内容。 修改方法和上文所示的一致。直接到my.cnf 文件中进行编辑。

3、long_query_time

long_query_time 设置了慢查询的时间阈值。默认阈值是10s。

4、log_quries_not_using_indexes

log_quries_not_using_indexes 是否将不使用索引的查询语句记录到慢查询日志中，无论查询速度有多快。

mysql> set @@global.log_queries_not_using_indexes=1;

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name         | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON  |
+-------------------------------+-------+

5、log_output

设置了普通查询日志以及慢查询日志的输出形式，值有两个file、table;

六、MySQL慢查询日志查看

log_output参数可以设置慢查询日志的输出形式。默认为FILE,可以设置为TABLE;

mysql> desc mysql.slow_log;
+----------------+---------------------+
| Field     | Type        |
+----------------+---------------------+
| start_time   | timestamp      |
| user_host   | mediumtext     |
| query_time   | time        |
| lock_time   | time        |
| rows_sent   | int(11)       |
| rows_examined | int(11)       |
| db       | varchar(512)    |
| last_insert_id | int(11)       |
| insert_id   | int(11)       |
| server_id   | int(10) unsigned  |
| sql_text    | mediumtext     |
| thread_id   | bigint(21) unsigned |
+----------------+---------------------+

其中: lock_time表示该SQL执行时被锁阻塞的时间。 rows_send表示执行SQL后返回的内容行数。 rows_examined表示该SQL执行时实际扫描的记录条数。

但是使用TABLE来存储慢查询日志并不常见，业务量较大的情况下，对于系统的主服务会有影响。我们可以使用FILE 的方式来进行日志存储。安装MySQL的时候在MySQL的bin目录下已经默认安装了mysqldumpslow.pl工具来进行慢查询的日志分析。 window下使用这个工具，可能需要折腾些配置，这不在本文的介绍范围内，学系统服务，还是移步linux吧。 linux下的命令以及工具都可以使用 命令本身 + --help 的选项来查看帮助文档。

-s 表示按照何种方式排序

子选项: c、t、l、r

c : SQL执行的次数
t : 执行时间
l : 锁等待时间
r : 返回数据条数
at、al、ar 是对应 t l r 的平均值。 -t :表示返回前 N 条记录。

-g： grep 缩写。包含模糊匹配

常用法如下：

//返回访问次数最多的20条SQL语句
./mysqldumpslow -s c -t 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log

//返回return记录数最多的20条SQL语句
./mysqldumpslow -s r -t 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log

//返回含有like的SQL语句
./mysqldumpslow -g 'like' 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log

七、二进制日志（binary）

二进制日志和前面提到的几种日志不同，二进制不能直接通过cat或者less 文本查看器查看。需要借助专业的工具。二进制日志主要记录数据库的变化情况，因此可以用作主从库的同步。内容主要包括数据库所有的更新操作，use语句、insert语句、delete语句、update语句、create语句、alter语句、drop语句。用一句更简洁易懂的话概括就是：所有涉及数据变动的操作，都要记录进二进制日志中。

启动二进制日志 使用 show variables like 'log_bin'\G 来查看二进制日志是否开启。

mysql> show variables like 'log_bin'\G
*************************** 1. row ***************************
Variable_name: log_bin
    Value: OFF
1 row in set (0.00 sec)

mysql> set @@global.log_bin=1;
ERROR 1238 (HY000): Variable 'log_bin' is a read only variable
mysql>

看到log_bin 默认是不开启的，并且是个只读的变量，需要在my.cnf中配置，然后重启MySQL。 service mysql restart 重启MySQL后，在data目录会发现生成了一个1.000001的文件。实际上每次MySQL重启，在目录下都会生成一个这样的文件，文件名依次递增。此外，MySQL还会在该目录下创建一个二进制日志的索引文件，可以通过命令show variables like 'log_bin_index'\G来查看索引文件的位置，然后使用cat命令看下。会发现，里面记录着二进制文件的相对路径。

查看二进制日志 可以使用MySQL 中自带的工具。具体位置在mysql的bin目录下。 mysqlbinlog命令的常用选项：

-s                          以精简的方式显示日志内容
-v                          以详细的方式显示日志内容
-d=数据库名                  只显示指定数据库的日志内容
-o=n                        忽略日志中前n行MySQL命令
-r=file                    将指定内容写入指定文件

--start-datetime 
                            显示指定时间范围内的日志内容
--stop-datetime        

--start-position       
                            显示指定位置间隔内的日志内容
--stop-position    

获取当前使用的二进制日志文件

mysql> show master status;
+----------+----------+--------------+------------------+-------------------+
| File   | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------+----------+--------------+------------------+-------------------+
| 1.000002 |   120 |       |         |          |
+----------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

使用二进制日志恢复数据

语法很简单：

mysqlbinlog -s 1.000001 | mysql -h 192.168.1.188 -u root -p

mysqlbinlog 之后可以跟 --start-datetime 、--stop-datetime 、start-position 、stop-position 等参数。

--start-datetime 、--stop-datetime 这两个参数可以基于时间点进行数据恢复；

start-position 、stop-position 可以进行操作点更加细化的进行数据恢复；

MySQL二进制日志相关参数

mysql> show variables like '%binlog%';
+-----------------------------------------+----------------------+
| Variable_name              | Value        |
+-----------------------------------------+----------------------+
| binlog_cache_size            | 32768        |
| binlog_checksum             | CRC32        |
| binlog_direct_non_transactional_updates | OFF         |
| binlog_error_action           | IGNORE_ERROR     |
| binlog_format              | STATEMENT      |
| binlog_gtid_simple_recovery       | OFF         |
| binlog_max_flush_queue_time       | 0          |
| binlog_order_commits          | ON          |
| binlog_row_image            | FULL         |
| binlog_rows_query_log_events      | OFF         |
| binlog_stmt_cache_size         | 32768        |
| binlogging_impossible_mode       | IGNORE_ERROR     |
| innodb_api_enable_binlog        | OFF         |
| innodb_locks_unsafe_for_binlog     | OFF         |
| max_binlog_cache_size          | 18446744073709547520 |
| max_binlog_size             | 1073741824      |
| max_binlog_stmt_cache_size       | 18446744073709547520 |
| simplified_binlog_gtid_recovery     | OFF         |
| sync_binlog               | 0          |
+-----------------------------------------+----------------------+

max_binlog_size

maxbinlogsize 单个二进制日志文件的大小。如果超过该值，则生成新的文件，后缀名+1；

binlog_cache_size

binlogcachesize 内存中存放二进制日志的缓存大小

sync_binlog

sync_binlog 缓存中写入几次二进制日志，开始同步刷新到外存（硬盘）中。

log_slave_updates

logslvaeupdates 用于主从复制

二进制日志的清理

原则上要先将准备清理的日志通过物理备份的方式备份到其他存储设备上，永久留存。 然后推荐使用下面两种风险较小的清理方式：

第一种：

purge master logs before '2017-02-16 00:00:00';

第二种：

直接在MySQL的配置文件my.cnf 中设置expire_logs_days参数来设置二进制文件的过期天数，过期的二进制文件将会被自动删除。建议在删除前另启一个周期计划任务，定期去备份二进制任务。免得一些数据若干天才发现出现了差错，而二进制日志被自动删除了。

expire_logs_days=90

八 、InnoDB 事务日志

MySQL会最大程度的利用缓存，从而提高数据的访问效率。那么换一句话来说，任何高性能的系统都必须利用到缓存，从各个层面来讲，缓存都发挥了巨大的作用。再上升到一个高度提炼一下：缓存和队列是实现高性能的必走之路。那么对于数据库来说这个却是个很棘手的问题，要保证数据更高效率的读取和存储，所以要利用到缓存。但是要保证数据的一致性，则必须保证所有的数据都必须准确无误的存储到数据库中，及时发生意外，也要保证数据可恢复。我们知道InnoDB是一个事务安全的存储引擎，而一致性是事务ACID中的一个重要特性。InnoDB存储引擎主要是通过InnoDB事务日志实现数据一致性的，InnoDB事务日志包括重做（redo）日志，以及回滚（undo）日志。

InnoDB事务日志与前面提高的日志不同，InnoDB事务日志由InnoDB存储引擎自行维护，而且内容不能被数据库管理员读取。

重做日志（redo）

重做日志主要是记录已经全部完成的事务,即执行了commit的日志，在默认情况下重做日志的值记录在iblogfile0 以及iblogfile1重做日志中。

[root@roverliang data]# pwd
/usr/local/webserver/mysql/data
[root@roverliang data]# ls ib*
ibdata1 ib_logfile0 ib_logfile1

回滚日志（undo）

回滚日志主要记录已经部分完成并且写入硬盘的未完成事务，默认情况情况下，回滚日志的信息记录在表空间文件，共享表空间文件ibdata1或者独享表空间未见ibd中。

由上图我们可以知道，回滚日志默认是记录在ibdta1中的。我的mysql系统版本为：5.6.24.

Checkpoint机制

MySQL服务器崩溃后，重新启动MySQL服务时，由于重做日志（redo）与回滚日志（undo）日志的存在，InnoDB通过回滚日志（undo）日志将所有已部分完成并写入硬盘的未完成事务进行回滚操作（rollback）。然后将重做日志（undo）日志中的事务全部重新执行一遍即可恢复所有的数据。但是数据量过大，为了缩短恢复的时间InnoDB引入了Checkpoint 机制。

脏页（dirty page）

当事务需要修改某条记录是，InnoDB首先将该数据所在的数据块从外存中读取到硬盘中，事务提交后，InnoDB修改数据页中的记录，这时缓存的数据页已经和外存中的数据块已经不一样了，此时缓存中的数据页称为脏页（dirty page）,脏页刷新到外存中，变为干净页（clean page）。

备注：一个内存页默认为4K，或者4K的倍数。你可以把内存想象成一本可以擦洗的书，每次MySQL读数据的时候，向内存申请几张干净的书页，然后书写上去。当数据刷新到硬盘之后，这些数据页马上被擦除，供其他程序使用。

日志序列号（log sequence number）

日志序列号（LSN）是日志空间中每条日志的结束点，用字节偏移量表示，在Checkpoin和恢复时使用。

Checkpoint 机制原理 假设在某个时间点，所有的脏页（dirty page）都被刷新到了硬盘上，这个时间点之前的所有重做日志（redo）就不需要重做了.系统就就下这个时间点重做日志的结尾位置作为Checkpoint,Checkpoint之前的重做日志也就不需要再重做了，可以放心的删除掉。为了更好的利用重做日志（redo）的空间,InnoDb采用轮循的策略使用重做日志空间，因此InnoDB的重做日志文件至少为2个。通过Checkpoint 机制，通过重做日志（redo）将数据库崩溃时已经完成但还没有来得及将缓存中已经修改但还未完全写入外存的事务进行重做（undo）操作，即可保证数据的一致性，也可以缩短恢复时间。

InnoDB重做日志(redo)的参数

innodb_log_buffer_size: 设置了重做日志缓存的大小。
innodb_log_files_in_group : 设置了日志文件组中重做日志（redo）日志的数量。
innodb_log_file_size: 设置了重做日志文件的大小，文件越大，恢复起来耗时越长。
innodb_mirrored_log_groups: 重做日志镜像文件组数量，只能设置为1.
innodb_log_group_home_dir： 设置日志文件组存放的目录，默认在数据库根目录下。

InnoDB回滚日志（undo）的参数

innodb_undo_directory: 设置回滚日志存放的目录。
innodb_undo_logs :设置回滚日志的回滚段大小，默认为128k
innodb_undo_tablespace:设置了回滚日志由多少个回滚日志文件组成，默认为0.
Warning 特别注意：安装MySQL后需要在my.cnf中设置回滚日志的参数，如果创建数据库以后再设置回滚日志的参数，MySQL就会报错，并且回滚日志建好后，就不能再次修改或者增加。

九 、日志文件备份

备份的时候可以使用flush logs，关闭当前的所有日志文件，然后产生新的日志文件。关闭日志文件后，可以采用物理方式备份。 另外flush logs可以添加具体的日志类型：

flush error logs
flush general logs
flush binary logs
flush slow logs

위 내용은 MySQL 로깅 시스템의 세부 사항을 설명하는 샘플 코드 공유의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!