Command to view binlog file creation time in Linux

background

MySQL generated a large number of slow queries at 16:23:49 on the 26th. During this period, the binlog file was refreshed very quickly (checking the slow log shows that MySQL DML concurrency is relatively high). I want to know how many minutes it takes to write a binlog file?

analyze

• The last modification intervals of the three binlog files are 2 minutes and 1 minute respectively
•The same transaction can only write to the same binlog file
•The last modification time of the mysql-bin.016126 file, 16:22, is not necessarily the creation time of the mysql-bin.016127 file (in the case of a large transaction, the large transaction is still writing to the previous binlog file, and the new transaction is already writing to the newly created binlog file)
• Use mysqlbinlog to read the events in the binlog file and know the creation time of the file
•In Linux, is there a command to find out when a file was created?

method

1. Use the stat command

stat mysql-bin.016126

File: 'mysql-bin.016126'
Size: 262146609 Blocks: 512016 IO Block: 4096 regular file
Device: fd02h/64770d Inode: 135529302 Links: 1
Access: (0640/-rw-r-----) Uid: ( 5701/actiontech-mysql) Gid: ( 5701/actiontech-mysql)
Access: 2019-04-28 14:41:42.967043003 +0800
Modify: 2019-04-26 16:24:21.823932020 +0800
Change: 2019-04-26 16:24:21.823932020 +0800

1. Access time: This time will be updated after reading the contents of this file once. For example, use more, cat and other commands on this file. Neither the ls nor the stat command will modify the access time of the file
2. Modification time: The modification time is the time when the file content was last modified. For example: save the file after vi. The time listed by ls -l is this time
3. Status change time. This is the time when the i-node of the file was last modified. This time will be updated when the file attributes are modified once using the chmod or chown command.

If the file has not been modified since it was created, the modification time = creation time; if the status of the file has not changed since it was created, the status change time = creation time; if the file has not been read since it was created, the access time = creation time. For used files, these are unlikely

--------------------------------------------------------------------------------

2. Use debugfs

Get the root directory where binlog files are stored

grep 'datas' /etc/fstab
/dev/mapper/centos-datas /datas ext4 defaults 1 2

Get the binlog file inode number

ls -i mysql-bin.016127
135529232 mysql-bin.016127

Get the file creation time

debugfs -R 'stat <135529232>' /dev/mapper/centos-datas
debugfs 1.42.9 (28-Dec-2013)
Inode: 135529232 Type: regular Mode: 0640 Flags: 0x80000
Generation: 2396969921 Version: 0x00000000:00000001
User: 5701 Group: 5701 Size: 262147164
File ACL: 0 Directory ACL: 0
Links: 1 Blockcount: 512016
Fragment: Address: 0 Number: 0 Size: 0
ctime: 0x5cc2c082:47dcd9bc -- Fri Apr 26 16:25:38 2019
atime: 0x5cc55c49:2929fd18 -- Sun Apr 28 15:54:49 2019
mtime: 0x5cc2c082:47dcd9bc -- Fri Apr 26 16:25:38 2019
crtime: 0x5cc2c035:c470d1d0 -- Fri Apr 26 16:24:21 2019
Size of extra inode fields: 28
EXTENTS:

•crtime is the file creation time
• The difference between the last modification time of the crtime and mtime files is 1 minute and 17 seconds
•MySQL takes more than 1 minute to write a binlog file

Notice

The xfs file system does not support using debugfs to view the creation time of files

grep root /etc/fstab
 /dev/mapper/centos-root / xfs defaults 1 1
ls -i /test1
 357556 /test1
debugfs -R 'stat <357556>' /dev/mapper/centos-root
 debugfs 1.42.9 (28-Dec-2013)
 /dev/mapper/centos-root: Bad magic number in super-block while opening filesystem
 stat: Filesystem not open

Summarize

The above is the command for checking the creation time of binlog files under Linux introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!