0

Mysql 5.6 general log file output not showing unix timestamp, so i wonder if there an option that i can execute to enable unix timestamp while logging general queries ?

UPDATE : Here is example for the query log

/usr/sbin/mysqld, Version: 5.6.39-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
                6480900 Connect database@localhost on
                6480900 Init DB database
                6480900 Query   SET NAMES utf8
                6480900 Query   SELECT id,language_id,season_id FROM Episode_serie WHERE slug LIKE 'URL' LIMIT 1
                6480900 Query   SELECT shortname,name FROM Language WHERE id = 1
                6480900 Query   SELECT slug,serie_id FROM Season_serie WHERE id = 1259
                6480900 Query   SELECT id,title,image,slug,views FROM Serie WHERE id = 35 AND status = '1'
                6480900 Query   SELECT count(m.id) as cpt,l.shortname,e.slug FROM Serie m INNER JOIN Season_serie s INNER JOIN Episode_serie e INNER JOIN Language l ON m.id = s.serie_id AND l.id = e.language_id AND e.season_id = s.id W$
                6480900 Query   SELECT count(m.id) as cpt,l.shortname,e.slug FROM Serie m INNER JOIN Season_serie s INNER JOIN Episode_serie e INNER JOIN Language l ON m.id = s.serie_id AND l.id = e.language_id AND e.season_id = s.id W$
                6480900 Query   SELECT * FROM  `dernier_ep` WHERE `type` = 1 and `link`!='URL' ORDER BY id DESC limit 8
                6480900 Query   SELECT number,id,slug FROM `Season_serie` WHERE `serie_id` = 35 order by number
                6480900 Query   SELECT e.id,e.number,l.shortname,l.slug,l.name,e.slug as sslug FROM Episode_serie e INNER JOIN Language l ON e.language_id=l.id WHERE e.season_id = 1253 ORDER BY l.shortname,e.tartib
                6480900 Query   SELECT e.id,e.number,l.shortname,l.slug,l.name,e.slug as sslug FROM Episode_serie e INNER JOIN Language l ON e.language_id=l.id WHERE e.season_id = 1254 ORDER BY l.shortname,e.tartib
                6480900 Query   SELECT e.id,e.number,l.shortname,l.slug,l.name,e.slug as sslug FROM Episode_serie e INNER JOIN Language l ON e.language_id=l.id WHERE e.season_id = 1255 ORDER BY l.shortname,e.tartib
                6480900 Query   SELECT e.id,e.number,l.shortname,l.slug,l.name,e.slug as sslug FROM Episode_serie e INNER JOIN Language l ON e.language_id=l.id WHERE e.season_id = 1256 ORDER BY l.shortname,e.tartib
                6480900 Query   SELECT e.id,e.number,l.shortname,l.slug,l.name,e.slug as sslug FROM Episode_serie e INNER JOIN Language l ON e.language_id=l.id WHERE e.season_id = 1257 ORDER BY l.shortname,e.tartib
                6480900 Query   SELECT e.id,e.number,l.shortname,l.slug,l.name,e.slug as sslug FROM Episode_serie e INNER JOIN Language l ON e.language_id=l.id WHERE e.season_id = 1258 ORDER BY l.shortname,e.tartib
                6480900 Query   SELECT e.id,e.number,l.shortname,l.slug,l.name,e.slug as sslug FROM Episode_serie e INNER JOIN Language l ON e.language_id=l.id WHERE e.season_id = 1259 ORDER BY l.shortname,e.tartib
                6480900 Query   SELECT e.id,e.number,l.shortname,l.slug,l.name,e.slug as sslug FROM Episode_serie e INNER JOIN Language l ON e.language_id=l.id WHERE e.season_id = 1260 ORDER BY l.shortname,e.tartib
                6480900 Query   SELECT e.id,e.number,l.shortname,l.slug,l.name,e.slug as sslug FROM Episode_serie e INNER JOIN Language l ON e.language_id=l.id WHERE e.season_id = 79 ORDER BY l.shortname,e.tartib
                6480900 Query   SELECT DISTINCT h.name,v.host_id,v.date FROM  Video v JOIN Host h ON v.host_id = h.id WHERE  v.episodeserie_id = 26605 ORDER BY h.priority desc         
                6480900 Query   SELECT v.id,u.username,q.name FROM Video v JOIN ooo_users u JOIN Quality q ON v.uploader_id = u.id AND v.quality_id = q.id WHERE v.host_id =54 AND v.episodeserie_id =26605
                6480900 Query   SELECT v.id,u.username,q.name FROM Video v JOIN ooo_users u JOIN Quality q ON v.uploader_id = u.id AND v.quality_id = q.id WHERE v.host_id =57 AND v.episodeserie_id =26605
                6480900 Query   SELECT v.id,u.username,q.name FROM Video v JOIN ooo_users u JOIN Quality q ON v.uploader_id = u.id AND v.quality_id = q.id WHERE v.host_id =132 AND v.episodeserie_id =26605
                6480900 Query   SELECT v.id,u.username,q.name FROM Video v JOIN ooo_users u JOIN Quality q ON v.uploader_id = u.id AND v.quality_id = q.id WHERE v.host_id =53 AND v.episodeserie_id =26605
                6480900 Query   SELECT v.id,u.username,q.name FROM Video v JOIN ooo_users u JOIN Quality q ON v.uploader_id = u.id AND v.quality_id = q.id WHERE v.host_id =2 AND v.episodeserie_id =26605
                6480900 Query   SELECT v.id,u.username,q.name FROM Video v JOIN ooo_users u JOIN Quality q ON v.uploader_id = u.id AND v.quality_id = q.id WHERE v.host_id =126 AND v.episodeserie_id =26605
                6480900 Query   SELECT v.id,u.username,q.name FROM Video v JOIN ooo_users u JOIN Quality q ON v.uploader_id = u.id AND v.quality_id = q.id WHERE v.host_id =124 AND v.episodeserie_id =26605
                6480900 Query   SELECT v.id,u.username,q.name FROM Video v JOIN ooo_users u JOIN Quality q ON v.uploader_id = u.id AND v.quality_id = q.id WHERE v.host_id =123 AND v.episodeserie_id =26605
                6480900 Query   SELECT v.id,u.username,q.name FROM Video v JOIN ooo_users u JOIN Quality q ON v.uploader_id = u.id AND v.quality_id = q.id WHERE v.host_id =86 AND v.episodeserie_id =26605
                6480900 Query   SELECT v.id,v.idvideo,v.metadata,v.expires,v.date,v.host_id,v.activeurl,h.domain,h.embedurl,h.basicurl FROM  Video v JOIN Host h ON v.host_id = h.id WHERE  v.episodeserie_id = 26605 ORDER BY h.priority d$
                6480900 Query   UPDATE Serie SET views = views + 1, date_vue=1532350547 WHERE id=35
                6480900 Query   SELECT c.*, u.username, u.usergroup FROM ooo_comments2 AS c LEFT JOIN ooo_users AS u ON (u.id = c.userid) WHERE c.fileid = 26605 AND c.status = '1' ORDER BY c.commentid DESC LIMIT 0, 101
                6480900 Query   SELECT v.id,v.idvideo,v.metadata,v.expires,v.host_id,v.activeurl,v.date,h.domain,h.embedurl,h.basicurl,v.quality_id,h.name,u.username FROM Video v JOIN Host h JOIN ooo_users u ON v.host_id = h.id AND v.u$
                6480900 Query   SELECT name FROM Quality WHERE id=9
                6480900 Query   SELECT name FROM Quality WHERE id=9
                6480900 Query   SELECT name FROM Quality WHERE id=9
                6480900 Query   SELECT name FROM Quality WHERE id=9
                6480900 Query   SELECT name FROM Quality WHERE id=9
                6480900 Query   SELECT name FROM Quality WHERE id=9
                6480900 Query   SELECT name FROM Quality WHERE id=9
                6480900 Query   SELECT name FROM Quality WHERE id=9
                6480900 Query   SELECT name FROM Quality WHERE id=9
                6480900 Quit

2 Answers 2

3

Ok i figure the problem now which is i enabled the mysql general query log through mysql console without restarting mysql , I had to restart mysql services in order to show mysql timestamp in query log file

0

Not a unix timestamp like the seconds since 1970-01-01, but you have the exact date and time at the very beginning of every line. There is no variable or whatever that you have to define for that.

Example:

/usr/sbin/mysqld, Version: 5.6.40-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
180723 15:21:32   157 Query     select now()
180723 15:21:38   157 Query     select sleep(1)

The very first number is the date in format yymmdd

1
  • I update my question with an example of the query log i am having , as you can see there is no timestamp like u post in your answer which is 15:21:32 i have another machine running mysql 5.7 and the file output showing timestamp like 2018-07-23T13:25:14.655125Z 11978622 Connect database_user@localhost on using Socket
    – iLinux85
    Jul 23, 2018 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.