MySQL日志类型

很多人都会有这样的疑问,究竟MySQL有多少种类型的日志文件呢?每个日志类型文件作用又是什么?它们之间又有什么区别?我们应该如何选择使用哪一种日志类型呢?

MySQL数据库共有六种类型的日志:Error Log、General Query Log、Binary Log、Relay Log、Slow Query Log 和DDL Log(metadata log)

image-20210310084300735

下面我们一起来看看上面所讲的日志类型的文件存储位置以及日志内容:

Error Log:错误日志,主要是记录 mysqld 发生的一些错误;从下面我们可以看到部分 Error Log 内容、mysqld.log文件中记录了MySQL的启动停止等信息;当数据库无法启动或者启动异常的时候我们可以根据 mysqld.log 的日志信息来判断具体错误。

[root@mysql-01 mysql]# tail /var/log/mysqld.log      
2021-03-08T05:32:40.471820Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 210308 13:32:40
2021-03-08T05:32:41.822874Z 0 [Note] InnoDB: Shutdown completed; log sequence number 2747021
2021-03-08T05:32:41.825647Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2021-03-08T05:32:41.825682Z 0 [Note] Shutting down plugin 'MEMORY'
2021-03-08T05:32:41.825698Z 0 [Note] Shutting down plugin 'CSV'
2021-03-08T05:32:41.825711Z 0 [Note] Shutting down plugin 'sha256_password'
2021-03-08T05:32:41.825721Z 0 [Note] Shutting down plugin 'mysql_native_password'
2021-03-08T05:32:41.826218Z 0 [Note] Shutting down plugin 'binlog'
2021-03-08T05:32:41.827417Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

[root@mysql-01 mysql]# 

General Query Log:mysql通用查询日志,记录 mysqld 正在做的事情,如客户端的连接和断开、来自客户端每条 Sql Statement 记录信息;如果你想准确知道客户端究竟传了什么内容给服务端,这个日志就非常管用了,当然了这货是非常影响性能的。

Binary Log:简称 bin-log 即 mysql二进制日志文件,这个文件记录了mysql所有的 DML 操作。通过 Bin-log 日志我们可以做数据恢复,做主主复制和主从复制等等。对于开发者可能对 Bin-log 并不怎么关注,但是对于运维或者架构人员来讲是非常重要的。简而言之,Bin-log 两个重要的用途:复制和恢复,很多十分好用的 MySQL 体验比如说增量备份,回滚至指定时间以及上面提到的主主和主从等等都依赖于 Bin-log。

# 我们用下面的方式开启Binary Log
[root@mysql-01 mysql]# cat /etc/my.cnf 
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
# log-bin后面也可以接具体的日志文件路径
log-bin
# MySQL5.7版本以后必须指定server-id(为了防止集群中各节点之间相互重复复制日志)、后面的50是节点IP(只要是一个整数即可)
server-id=50

[root@mysql-01 mysql]# systemctl restart mysql
[root@mysql-01 mysql]# 

# 然后我们就可以在下面的路径中找到索引文件以及日志文件
[root@mysql-01 data]# pwd
/usr/local/mysql/data
# 索引文件记录了有多少个日志文件以及他们的路径
[root@mysql-01 data]#  cat mysql-01-bin.index
./mysql-01-bin.000001
./mysql-01-bin.000002
[root@mysql-01 data]#

# 当然我们也可以自定义一个日志路径
[root@mysql-01 mysql]# cat /etc/my.cnf 
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
log-bin=/var/log/mysql-bin/master1
server-id=50

# 必须授权mysql用户对log-bin日志文件有写入权限
mkdir /var/log/mysql-bin
chown mysql.mysql /var/log/mysql-bin/
systemctl restart mysqld

Binary Log 日志为二进制文件、不能用直接进行查看;MySQL提供了一个叫做mysqlbinlog的工具可以查看 Binary Log 日志文件。

# 默认的Binary Log没有任何日志
[root@mysql-01 ~]# mysqlbinlog /usr/local/mysql-5.7.33-linux-glibc2.12-x86_64/data/mysql-01-bin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#210309 20:47:54 server id 50  end_log_pos 123 CRC32 0x2d5cf82f         Start: binlog v 4, server v 5.7.33-log created 210309 20:47:54 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
em5HYA8yAAAAdwAAAHsAAAABAAQANS43LjMzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAB6bkdgEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AS/4XC0=
'/*!*/;
# at 123
#210309 20:47:54 server id 50  end_log_pos 154 CRC32 0xbb467c93         Previous-GTIDs

# 下面我们来创建一个数据库和数据表、然后在数据表中插入数据(从下面我们可以看到数据库信息变更的日志记录)
[root@mysql-01 ~]# mysqlbinlog /usr/local/mysql-5.7.33-linux-glibc2.12-x86_64/data/mysql-01-bin.000002
.....................................................................................................................
create database z0ukun
/*!*/;
# at 319
#210309 21:18:34 server id 50  end_log_pos 384 CRC32 0xbaa82507         Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 384
#210309 21:18:34 server id 50  end_log_pos 485 CRC32 0x25e8036c         Query   thread_id=6     exec_time=0     error_code=0
use `z0ukun`/*!*/;
SET TIMESTAMP=1615295914/*!*/;
create table t1(id int)
/*!*/;
.....................................................................................................................
# at 624
#210309 21:19:05 server id 50  end_log_pos 671 CRC32 0xcb33440b         Table_map: `z0ukun`.`t1` mapped to number 108
# at 671
#210309 21:19:05 server id 50  end_log_pos 711 CRC32 0x057aad1a         Write_rows: table id 108 flags: STMT_END_F

BINLOG '
yXVHYBMyAAAALwAAAJ8CAAAAAGwAAAAAAAEABnowdWt1bgACdDEAAQMAAQtEM8s=
yXVHYB4yAAAAKAAAAMcCAAAAAGwAAAAAAAEAAgAB//4CAAAAGq16BQ==
'/*!*/;
# at 711
#210309 21:19:05 server id 50  end_log_pos 742 CRC32 0x55ab5118         Xid = 9
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@mysql-01 ~]# 

注:如果MySQL重启之后、会把原有的日志文件截断并生成一个新的日志文件(执行 flush logs 也会截断);reset master命令千万不要使用、使用了此命令会删除所有的binlog日志文件。但是我们可以使用下面的命令删除部分日志(还是建议不要删除):

PURGE BINARY LOGS TO 'mysql-01-bin.000002';
PURGE BINARY LOGS BEFORE '2020-03-09 22:46:26'

Relay Log:从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件,然后SQL线程会读取relay-log日志的内容并应用到从服务器。

Slow Query Log :mysql慢查询日志,记录一些查询执行较慢的 SQL 语句,这个日志非常常用,主要是给开发者调优用的。

[root@mysql-01 data]# cat /etc/my.cnf 
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data

log-bin
server-id=50

# 开启slow_query_log
slow_query_log=1
# 配置slow_query_log日志文件
slow_query_log_file=slow.log
# 配置慢查询超过时间记录日志
long_query_time=3
[root@mysql-01 data]# systemctl restart mysql

# 查看慢查询日志(测试语句如下)
BENCHMARK(count,expr)
SELECT BENCHMARK(5000000,2*3);
[root@mysql-01 data]# fg
mysql -u root -p
mysql> SELECT BENCHMARK(500000000,2*3); 
+--------------------------+
| BENCHMARK(500000000,2*3) |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (13.72 sec)

mysql> 
[1]+  Stopped                 mysql -u root -p

# 这样我们就可以把查询时间较长的慢查询日志信息提供给开发者了
[root@mysql-01 data]# cat slow.log 
/usr/local/mysql/bin/mysqld, Version: 5.7.33-log (Source distribution). started with:
Tcp port: 0  Unix socket: (null)
Time                 Id Command    Argument
# Time: 2021-03-09T13:42:14.102816Z
# User@Host: root[root] @ localhost []  Id:     4
# Query_time: 13.713131  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use mysql;
SET timestamp=1615297334;
SELECT BENCHMARK(500000000,2*3);
[root@mysql-01 data]# 

DDL Log(metadata log):DDL日志或元数据日志记录由数据定义语句(如DROP TABLE和ALTER TABLE) 生成的元数据操作。

推荐文章

31条评论

  1. Hiya very nice website!! Guy .. Excellent .. Amazing ..
    I will bookmark your website and take the feeds also?
    I’m happy to find numerous useful information here in the put
    up, we need work out more techniques in this regard, thanks
    for sharing. . . . . .

  2. Howdy just wanted to give you a brief heads up and let you know a
    few of the images aren’t loading properly. I’m not sure why but
    I think its a linking issue. I’ve tried it in two different internet browsers and both show the same
    results.

  3. We’re a group of volunteers and starting a
    new scheme in our community. Your web site offered us with valuable info to
    work on. You’ve performed an impressive activity and our
    entire community can be thankful to you.

  4. Hi there! Quick question that’s totally off topic. Do you know how to make
    your site mobile friendly? My weblog looks weird when viewing from
    my iphone4. I’m trying to find a template or plugin that might be able to correct this issue.
    If you have any recommendations, please share. Many thanks!

  5. Simply wish to say your article is as astounding.
    The clarity in your post is simply excellent and
    i could assume you’re an expert on this subject.
    Well with your permission let me to grab your RSS feed to keep up to
    date with forthcoming post. Thanks a million and please carry on the gratifying work.

  6. Hi there it’s me, I am also visiting this site daily, this web page is
    really nice and the viewers are in fact sharing pleasant thoughts.

  7. There is certainly a lot to learn about this topic.
    I love all the points you made.

  8. When someone writes an paragraph he/she maintains the idea of a user in his/her
    brain that how a user can understand it.
    So that’s why this piece of writing is outstdanding.
    Thanks!

  9. Thanks for the marvelous posting! I quite enjoyed reading it, you will be a great author.
    I will be sure to bookmark your blog and will eventually
    come back later in life. I want to encourage that you continue your great job,
    have a nice day!

  10. Thanks , I’ve recently been looking for info about this subject for a while and yours is the greatest I have found out so far.
    However, what in regards to the bottom line? Are you certain in regards to the supply?

  11. Hey! This is my first comment here so I just wanted to give a quick shout out and say I genuinely enjoy reading through your blog posts.
    Can you suggest any other blogs/websites/forums that deal with the
    same topics? Appreciate it!

  12. Asking questions are truly good thing if you are not understanding anything totally, except this article offers good understanding
    yet.

  13. I do not know if it’s just me or if perhaps everybody else encountering problems with your website.
    It looks like some of the written text within your posts are running off the screen.
    Can somebody else please comment and let me know if this is happening to them as well?
    This may be a issue with my internet browser because I’ve had this
    happen before. Cheers

  14. I every time spent my half an hour to read this weblog’s articles daily along with a cup of coffee.

  15. This post is priceless. How can I find out more?

  16. I love your blog.. very nice colors & theme. Did you design this website yourself or did you hire someone to do it for you?
    Plz reply as I’m looking to construct my own blog and would like to
    know where u got this from. thanks a lot

  17. If you wish for to take a good deal from this article then you have
    to apply these methods to your won webpage.

  18. If some one wants expert view on the topic of blogging then i
    propose him/her to pay a visit this blog, Keep up the good job.

  19. I’m not that much of a online reader to be honest but your blogs
    really nice, keep it up! I’ll go ahead and bookmark your
    website to come back later. All the best

  20. I blog quite often and I seriously thank you for your content.
    The article has really peaked my interest.

    I’m going to bookmark your site and keep checking for new details about once per week.

    I opted in for your RSS feed as well.

  21. Wonderful blog! I found it while browsing on Yahoo
    News. Do you have any tips on how to get listed in Yahoo News?

    I’ve been trying for a while but I never seem to get there!
    Thank you

  22. This paragraph is actually a good one it helps new internet viewers,
    who are wishing in favor of blogging.

  23. Attractive portion of content. I just stumbled upon your weblog and in accession capital to claim that I get in fact enjoyed account your weblog
    posts. Any way I’ll be subscribing on your augment and even I success you get
    right of entry to persistently fast.

  24. I know this if off topic but I’m looking into starting my own weblog and was wondering what all is needed to get set up?
    I’m assuming having a blog like yours would cost a pretty penny?
    I’m not very web smart so I’m not 100% certain. Any suggestions or advice would be greatly appreciated.
    Cheers

  25. Pretty! This has been a really wonderful article. Thanks for supplying this information.

  26. Thank you for another informative website. The place else could I am getting that type of
    information written in such an ideal approach? I have a project that I am simply now operating on, and I’ve been at the glance
    out for such info.

  27. I am really glad to glance at this website posts which includes tons of valuable facts, thanks for providing such statistics.

  28. I’m curious to find out what blog system you are utilizing?
    I’m having some small security issues with my latest site and I would
    like to find something more secure. Do you
    have any suggestions?

  29. Nice post. I learn something new and challenging on websites I
    stumbleupon on a daily basis. It will always be helpful to read articles from other writers
    and practice a little something from other websites.

  30. Appreciate the recommendation. Will try it out.

  31. hey there and thank you for your info – I’ve definitely picked up something new from right here.
    I did however expertise a few technical points using
    this web site, since I experienced to reload the web site many times
    previous to I could get it to load properly. I had
    been wondering if your web hosting is OK? Not that I am complaining, but slow loading instances times will often affect your placement in google and could damage your quality score if advertising and marketing with
    Adwords. Well I’m adding this RSS to my e-mail and can look out for a lot
    more of your respective fascinating content. Make sure
    you update this again soon.

发表评论

邮箱地址不会被公开。 必填项已用*标注