MySQL/MariaDB: mysqlbinlog – примеры

Автор: | 10/10/2017
Нет комментариев
 

Краткая заметка по работе с бинарными логами MySQL/MariaDB.

Файлы логов:

$ ls -l /var/log/mysql
total 19616
-rw-rw---- 1 mysql adm  4284668 Nov 24  2016 mariadb-bin.000004
-rw-rw---- 1 mysql adm 15779518 Oct  4 16:14 mariadb-bin.000005
-rw-rw---- 1 mysql adm       68 Nov 24  2016 mariadb-bin.index
-rw-rw---- 1 mysql adm      404 Oct  5 11:26 mariadb-general.log

Для просмотра используем mysqlbinlog:

[simterm]

$ mysqlbinlog /var/log/mysql/mariadb-bin.000004

[/simterm]

Конвертировать логи в текст

Записать данные в текстовый лог:

[simterm]

# mysqlbinlog /var/log/mysql/mariadb-bin.000004 > /var/log/mysql/mariadb-bin.000004.txt
# mysqlbinlog /var/log/mysql/mariadb-bin.000005 > /var/log/mysql/mariadb-bin.000005.txt

[/simterm]

Проверяем:

[simterm]

# head /var/log/mysql/mariadb-bin.000004.txt
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160930  8:37:28 server id 1  end_log_pos 249   Start: binlog v 4, server v 10.1.17-MariaDB-1~xenial created 160930  8:37:28 at startup
ROLLBACK/*!*/;
BINLOG '
SCTuVw8BAAAA9QAAAPkAAAAAAAQAMTAuMS4xNy1NYXJpYURCLTF+eGVuaWFsAGxvZwAAAAAAAAAA
AAAAAAAAAAAAAAAAAABIJO5XEzgNAAgAEgAEBAQEEgAA3QAEGggAAAAICAgCAAAACgoKAAAAAAAA

[/simterm]

Получить данные с одной базы

Можно с помощью -d, например – записи только по базе datameer:

[simterm]

$ mysqlbinlog -d datameer mariadb-bin.000010  | head
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#161122 10:06:17 server id 1  end_log_pos 249   Start: binlog v 4, server v 10.1.17-MariaDB-1~xenial created 161122 10:06:17
BINLOG '
efwzWA8BAAAA9QAAAPkAAAAAAAQAMTAuMS4xNy1NYXJpYURCLTF+eGVuaWFsAGxvZwAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA3QAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

[/simterm]

Отобразить только SQL запросы

Используем -s:

[simterm]

$ mysqlbinlog -s -d datameer mariadb-bin.000010 | head 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
BEGIN
/*!*/;
SET INSERT_ID=22816087/*!*/;
SET TIMESTAMP=1479801977/*!*/;
SET @@session.pseudo_thread_id=999999999/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

[/simterm]

Даты в логе

В логе есть даты:

…
#171004 16:14:15 server id 1 end_log_pos 15779518 Query thread_id=55096 exec_time=0 error_code=0
SET TIMESTAMP=1507133655/*!*/;
DROP TABLE IF EXISTS `yongger2` /* generated by server */
…

Тут #171004 – 17 год, 10 месяц, 04 число.

Лог за период времени

Используем --start-datetime и --stop-time:

[simterm]

$ mysqlbinlog --start-datetime="2016-12-04 08:00:00" --stop-date="2016-12-04 11:00:00" mariadb-bin.000021
...
#161204  9:00:08 server id 1  end_log_pos 10876377      GTID 0-1-1483426 ddl
/*!100101 SET @@session.skip_parallel_replication=0*//*!*/;
/*!100001 SET @@session.gtid_domain_id=0*//*!*/;
/*!100001 SET @@session.server_id=1*//*!*/;
/*!100001 SET @@session.gtid_seq_no=1483426*//*!*/;
# at 10876377
...
#161204 10:02:30 server id 1  end_log_pos 10882073      Query   thread_id=153   exec_time=0     error_code=0
SET TIMESTAMP=1480838550/*!*/;
DROP TABLE IF EXISTS `#Tableau_0_sid_00000099_3_Connect` /* generated by server */
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[/simterm]

Лог с удалённого сервера

Опция -R или --read-from-remote–server:

[simterm]

$ mysqlbinlog -R -h 52.***.***.67 -u root -p mariadb-bin.000005
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
ERROR: Failed on connect: Access denied for user 'root'@'194.***.***5.45' (using password: YES)
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[/simterm]

  • -h: host
  • -u: user
  • -p: пароль

Больше опций – в man или --help:

[simterm]

$ mysqlbinlog --help
...
Usage: mysqlbinlog [options] log-files

Default options are read from the following files in the given order:
/etc/mysql/my.cnf ~/.my.cnf
The following groups are read: mysqlbinlog client client-server client-mariadb
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
--no-defaults           Don't read default options from any option file.
--defaults-file=#       Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
...

[/simterm]