В MySQL или MariaDB в любое время, когда вы вносите изменения в базу данных, это конкретное событие регистрируется.
Например, когда вы создаете новую таблицу или обновляете данные в существующей таблице, эти события хранятся в двоичных журналах.
Двоичный журнал очень полезен в репликации MySQL, где главный сервер будет отправлять данные из двоичных журналов на удаленные сервера.
Вы также будете иметь дело с бинарными файлами журнала, когда выполняете какие-либо операции восстановления в MySQL.
Команда mysqlbinlog используется для просмотра содержимого двоичного журнала в удобном для чтения формате. Вы также будете использовать команду mysqlbinlog для чтения содержимого и подключения его к другим утилитам mysql.
В этом уроке мы обсудим следующие примеры, используя команду mysqlbinlog:
- Получить список текущих двоичных журналов
- Поведение по умолчанию Mysqlbinlog
- Получить записи для конкретной базы данных
- Отключить двоичный журнал для восстановления
- Управление base-64 в выводе BINLOG
- Отладочная информация в выводе mysqlbinlog
- Пропустить первые N количество записей
- Сохранить вывод в файл
- Извлечение записей, начиная с определенного положения
- Извлечь записи до определенного положения
- Сбросить журналы для чистого вывода Binlog
- Отображать только SQL-запросы в выводе
- Просмотр записей, начиная с определенного времени
- Просмотр записей до определенного времени
- Получить двоичный журнал с удаленного сервера
1. Получить список текущих двоичных журналов
Из mysql выполните следующую команду show binary logs, которая отобразит все бинарные журналы в вашей системе.
mysql> SHOW BINARY LOGS; + ------------------- + ----------- + | Log_name | File_size | + ------------------- + ----------- + | mysqld-bin.000001 | 15740 | | mysqld-bin.000002 | 3319 | .. ..
Если вы не используете двоичный журнал, то вы увидите следующее сообщение об ошибке.
mysql> SHOW BINARY LOGS; ERROR 1381 (HY000): You are not using binary logging
По умолчанию файлы двоичного журнала находятся в каталоге /var/lib/mysql, как показано ниже.
# ls -l /var/lib/mysql/ -rw-rw----. 1 mysql mysql 15740 Aug 16 14:57 mysqld-bin.000001 -rw-rw----. 1 mysql mysql 3319 Aug 16 14:57 mysqld-bin.000002 .. ..
2. Поведение по умолчанию Mysqlbinlog
Далее будет отображаться содержимое указанного бинарного файла журнала mysql (например: mysqld-bin.000001) в удобном для пользователя формате.
# mysqlbinlog mysqld-bin.000001
Ниже приводится частичный вывод указанной выше команды:
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170726 14:57:37 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.73-log created 170726 14:57:37 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
IeZ4WQ8BAAAAZgAAAGoAAAABAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAh5nhZEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*!*/;
# at 106
#170726 14:59:31 server id 1 end_log_pos 182 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1501095571/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
..
..
..
# at 14191
#170726 15:20:38 server id 1 end_log_pos 14311 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1501096838/*!*/;
insert into salary(name,dept) values('Ritu', 'Accounting')
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
Вышеприведенная команда отображает события, которые произошли во всей базе данных в этой системе.
Если вы новичок в MySQL, это поможет: 6 шагов по установке и настройке MariaDB MySQL на Linux
3. Получить записи для конкретной базы данных
Вывод mysqlbinlog по умолчанию может быть ошеломляющим, так как он отображает много данных из всех операторов.
Используя параметр -d, вы также можете указать имя базы данных, которое будет отображаться в событиях, которые происходят в данной базе данных.
Следующая команда сбрасывает все события, принадлежащие базе данных «crm», в файл crm-events.txt
mysqlbinlog -d crm mysqld-bin.000001> crm-events.txt
Вместо опции -d вы также можете использовать параметр -database, как показано ниже.
mysqlbinlog --database crm mysqld-bin.000001> crm-events.txt
4. Отключите двоичный журнал для восстановления.
Когда вы используете mysqlbinlog для восстановления базы данных из-за сбоя, вы не хотите, чтобы ваш процесс восстановления создавал двоичные журналы. Если да, то вы окажетесь в цикле, где вы продолжите восстановление, так как само восстановление будет генерировать новые двоичные файлы журналов.
Таким образом, чтобы отключить двоичный журнал, когда вы используете команду mysqlbinlog, используйте параметр -D, как показано ниже:
mysqlbinlog -D mysqld-bin.000001
Вы также можете использовать -disable-log-bin, как показано ниже. Следующее точно так же, как приведенная выше команда.
mysqlbinlog --disable-log-bin mysqld-bin.000001
/ *! 40019 SET @@ session.max_insert_delayed_threads = 0 * /; / *! 32316 SET @OLD_SQL_LOG_BIN = @@ SQL_LOG_BIN, SQL_LOG_BIN = 0 * /; / *! 50003 SET @OLD_COMPLETION_TYPE = @@ COMPLETION_TYPE, COMPLETION_TYPE = 0 * /;
Этот параметр также будет полезен, если вы используете опцию «–to-last-log». Кроме того, имейте в виду, что вам нужна привилегия root для выполнения этой команды.
Для резервного копирования и восстановления вы обычно используете команду mysqldump, но иногда в ситуации, когда вам приходится восстанавливаться после сбоя, полезно использовать mysqlbinlog.
5. Управление base-64 в выводе BINLOG
Используя опцию base64-output, вы можете управлять поведением, когда оператором вывода должны быть операторы BINLOG, закодированные в base64.
Ниже приведены возможные значения для base64-output:
- never
- always
- decode-rows
- auto (this is default)
never: Когда вы укажете «never», как показано ниже, будут выводиться операторы BINLOG с кодировкой base64 в выходном файле.
mysqlbinlog --base64-output = never mysqld-bin.000001
т.е. когда вы используете «never», в выводе команды mysqlbinlog вы не будете использовать строки, похожие на следующие, которые имеют BINLOG с кодировкой base64.
BINLOG ' IeZ4WQ8BAAAAZgAAABAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAh5nhZEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
Обратите внимание, что вышеупомянутая опция «never» для вывода base64 будет работать в двоичных файлах журнала, если они не содержат события на основе строк.
always: когда вы укажете опцию «always», отображать только записи BINLOG, когда это возможно. Таким образом, используйте это только тогда, когда вы специально отлаживаете некоторые проблемы.
mysqlbinlog --base64-output = всегда mysqld-bin.000001
Ниже приведен вывод выше с помощью «always», который показывает только записи BINLOG.
BINLOG ' IeZ4WQ8BAAAAZgAAAGoAAAABAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAh5nhZEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC «/ * * /!; # при 106 # 170726 14:59:31 server id 1 end_log_pos 182 BINLOG ' к + Z4WQIBAAAATAAAALYAAAAIAAIAAAAAAAAADAAAGgAAAEAAAAEAAAAAAAAAAAYDc3RkBAgACAAI AHRoZWdlZWtzdHVmZgBCRUdJTg == «/ * * /!; # at 182 # 170726 14:59:30 server id 1 end_log_pos 291 BINLOG ' kuZ4WQIBAAAAbQAAACMBAAAAAAIAAAAAAAAADAAAGgAAAEAAAAEAAAAAAAAAAAYDc3RkBAgACAAI AHRoZWdlZWtzdHVmZgBJTlNFUlQgSU5UTyB0IFZBTFVFUygxLCAnYXBwbGUnLCBOVUxMKQ == «/ * * /!; # 291 # 170726 14:59:30 server id 1 end_log_pos 422 BINLOG ' kuZ4WQIBAAAAgwAAAKYBAAAAAAIAAAAAAAAADAAAGgAAAEAAAAEAAAAAAAAAAAYDc3RkBAgACAAI AHRoZWdlZWtzdHVmZgBVUERBVEUgdCBTRVQgbmFtZSA9ICdwZWFyJywgZGF0ZSA9ICcyMDA5LTAx LTAxJyBXSEVSRSBpZCA9IDE =
decode-rows: эта опция будет декодировать события на основе строк в комментированные инструкции SQL, особенно если вы укажете опцию -verbose также вместе с ней, как показано ниже.
mysqlbinlog --base64-output = decode-rows --verbose mysqld-bin.000001
auto: Это опция по умолчанию. Если вы не укажете опцию base64-decode, она будет использовать auto. В этом случае mysqlbinlog будет печатать записи BINLOG только для определенных типов событий, таких как события на основе строк и события описания формата.
Оба следующих утверждения точно совпадают.
mysqlbinlog --base64-output = auto mysqld-bin.000001 mysqlbinlog mysqld-bin.000001
6. Отладочная информация в выводе mysqlbinlog.
Следующая опция проверки отладки проверяет использование открытого файла и памяти после завершения обработки данного файла двоичного журнала.
mysqlbinlog --debug-check mysqld-bin.000001
В следующем параметре debug-info будет отображаться дополнительная опция отладки после завершения обработки данного файла двоичного журнала, как показано ниже.
# mysqlbinlog --debug-info mysqld-bin.000001 > /tmp/m.di User time 0.00, System time 0.00 Maximum resident set size 2848, Integral resident set size 0 Non-physical pagefaults 863, Physical pagefaults 0, Swaps 0 Blocks in 0 out 48, Messages in 0 out 0, Signals 0 Voluntary context switches 1, Involuntary context switches 2
7. Пропустить первые N количество записей
Вместо чтения всего бинарного файла mysql, вы также можете прочитать только определенную его часть, указав смещение.
Для этого используйте опцию -o. -o означает смещение.
Ниже перечислены первые 10 записей в указанном журнале bin mysql.
mysqlbinlog -o 10 mysqld-bin.000001
Чтобы убедиться, что это работает должным образом, дайте номер события для смещения, и вы не увидите никаких записей. Следующий пример пропустит первые 10 000 записей (событий) из журнала.
В этом примере, поскольку этот конкретный файл журнала, нет 10 000 записей, он не указывает на какие-либо события базы данных на выходе.
# mysqlbinlog -o 10000 mysqld-bin.000001 /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; .. .. # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
8. Сохранить вывод в файл
Вы можете использовать простую команду перенаправления Linux > и сохранить вывод в файл, как показано ниже.
mysqlbinlog mysqld-bin.000001> output.log
Или вы можете использовать параметр -r (файл результата), как показано ниже, для сохранения вывода в файле. Обратите внимание, что -r и -result- file совпадают.
mysqlbinlog -r output.log mysqld-bin.000001
mysqlbinlog --server-id = 1 -r output.log mysqld-bin.000001
9. Извлечь записи, начиная с определенного положения
Обычно в бинарном файле mysql вы увидите номера позиций, как показано ниже. Ниже представлен частичный вывод mysqlbinlog, где вы видите «15028» – номер позиции.
#170726 15:38:14 server id 1 end_log_pos 15028 Query thread_id=5 exec_time=0 error_code=0 SET TIMESTAMP=1501097894/*!*/; insert into salary values(400,'Nisha','Marketing',9500) /*!*/; # at 15028 #170726 15:38:14 server id 1 end_log_pos 15146 Query thread_id=5 exec_time=0 error_code=0 SET TIMESTAMP=1501097894/*!*/; insert into salary values(500,'Randy','Technology',6000)
Следующая команда начнет считывать записи двоичного журнала с номером позиции 15028.
mysqlbinlog -j 15028 mysqld-bin.000001> от-15028.out
Когда вы укажете несколько файлов двоичного журнала в командной строке, стартовая позиция будет применяться только к первому двоичному журналу в данном списке.
Вы можете использовать параметр -H для получения шестнадцатеричного дампа данных двоичного журнала, как показано ниже.
mysqlbinlog -H mysqld-bin.000001> binlog-hex-dump.out
10. Извлечь записи до определенного положения
Как и в предыдущем примере, вы также можете читать записи из двоичного журнала mysql до определенной позиции, как показано ниже.
mysqlbinlog --stop-position = 15028 mysqld-bin.000001> upto-15028.out
Вышеприведенный пример остановится из binlog точно в позиции 15028. Когда вы укажете несколько файлов двоичного журнала в командной строке, позиция остановки будет применяться только к последнему двоичному журналу в данном списке.
11. Сбросить журналы для чистого вывода Binlog
Когда двоичный журнал не был правильно закрыт, на выходе вы увидите предупреждающее сообщение, как показано ниже.
# mysqlbinlog mysqld-bin.000001> output.log
Как вы видите здесь, он говорит, что файл binlog не был закрыт должным образом.
# head output.log /*!40019 SET @@session.max_insert_delayed_threads=0*/; .. .. # Warning: this binlog is either in use or was not closed properly. .. .. .. BINLOG ' IeZ4WQ8BAAAAZgAAAGoAAAABAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAh5nhZEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
Когда вы это увидите, подключитесь к mysql и очистите журналы, как показано ниже.
mysql> flush logs;
После того, как вы очистите журналы и снова выполните команду mysqlbinlog, вы не увидите, что binlog не закрыл должным образом предупреждающее сообщение в вашем выводе mysqlbinlog.
12. Отображать только SQL-запросы в выводе
По умолчанию, как вы видите в выводе предыдущих примеров, помимо SQL-запросов, вы также увидите некоторую дополнительную информацию в выводе mysqlbinlog.
Если вам нужны только обычные SQL-запросы и ничего больше, используйте опцию -s, как показано ниже.
-s здесь означает короткую форму. Вы также можете использовать опцию -short-form. Оба приведенных ниже примера совпадают.
mysqlbinlog -s mysqld-bin.000001 mysqlbinlog -short-form mysqld-bin.000001
Ниже представлен частичный вывод вышеуказанной команды. Как вы видите здесь, он отображает только SQL-запросы из данного файла двоичного журнала.
SET TIMESTAMP=1501096106/*!*/; insert into employee values(400,'Nisha','Marketing',9500) /*!*/; SET TIMESTAMP=1501096106/*!*/; insert into employee values(500,'Randy','Technology',6000) .. .. ..
В короткой форме вы не увидите следующее:
# at 1201 #170726 15:08:26 server id 1 end_log_pos 1329 Query thread_id=3 exec_time=0 error_code=0
13. Просмотр записей, начиная с определенного времени
Ниже перечислены только те записи, которые начинаются с указанного времени. Любые записи до этого времени будут проигнорированы.
mysqlbinlog --start-datetime = "2017-08-16 15:00:00" mysqld-bin.000001
Это очень полезно, если вы хотите извлечь данные из двоичного файла только из определенного временного интервала, который вы хотите использовать для восстановления или восстановления определенных действий базы данных, которые произошли за этот период времени.
Формат метки времени может быть любым, что понимается типами DATETIME и TIMESTAMP сервера MYSQL. Итак, у вас здесь много гибкости.
14. Просмотр записей до определенного времени
Как и в предыдущем примере времени начала, вы также можете указать время остановки, как показано ниже.
mysqlbinlog --stop-datetime = "2017-08-16 15:00:00" mysqld-bin.000001
Вышеуказанная команда будет считывать записи до заданного времени остановки. Любые записи из бинарного файла журнала mysql, которые находятся за пределами заданного времени остановки, не будут обрабатываться.
15. Получить двоичный журнал с удаленного сервера
С вашего локального компьютера вы также можете прочитать бинарные журналы mysql, расположенные на удаленном сервере.
Для этого вам необходимо указать IP-адрес, имя пользователя и пароль для удаленного сервера, как описано ниже.
Используйте для этого вариант -R. Параметр -R аналогичен -read-from-remote-server.
mysqlbinlog -R -h 192.168.101.2 -p mysqld-bin.000001
В приведенном выше:
- Параметр -R указывает команде mysqlbinlog считывать файл журнала с удаленного сервера
- -h указать ip-адрес удаленного сервера
- -p даст вам пароль. По умолчанию он будет использовать «root» в качестве имени пользователя. Вы также можете указать имя пользователя, используя опцию -u.
- mysqld-bin.000001 Это имя бинарного файла журнала с удаленного сервера, который мы здесь читаем.
Следующая команда точно такая же, как приведенная выше команда:
mysqlbinlog --read-from-remote-server --host = 192.168.101.2 -p mysqld-bin.000001
Ниже приводится частичный вывод указанной выше команды:
/*!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 #170726 13:57:37 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.73-log created 170726 13:57:37 at startup ROLLBACK/*!*/; BINLOG ' IeZ4WQ8BAAAAZgAAAGoAAAAAAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAh5nhZEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC '/*!*/; # at 106 #170726 13:59:31 server id 1 end_log_pos 182 Query thread_id=2 exec_time=0 error_code=0 .. DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
Если вы укажете только параметр -h, вы получите следующее сообщение об ошибке.
# mysqlbinlog -h 192.168.101.2 mysqld-bin.000001 mysqlbinlog: File 'mysqld-bin.000001' not found (Errcode: 2)
Если у вас недостаточно прав для удаленной базы данных, вы получите сообщение об ошибке “is not allowed to connect”. В этом случае убедитесь, что вы предоставили правильные привилегии в удаленной базе данных для своего локального клиента (т. е. когда запущена команда mysqlbinlog)
# mysqlbinlog -R --host=192.168.101.2 mysqld-bin.000001 ERROR: Failed on connect: Host '216.172.166.27' is not allowed to connect to this MySQL server
Если вы не укажете правильный пароль с помощью параметра -p, вы получите следующее сообщение об ошибке «access denied»
# mysqlbinlog -R --host=192.168.101.2 mysqld-bin.000001 ERROR: Failed on connect: Access denied for user 'root'@'216.172.166.27' (using password: YES)
В следующем примере показано, что вы также можете использовать опцию -u для указания имени пользователя, которое должен использовать mysqlbinlog для подключения к удаленной базе данных MySQL. Обратите внимание, что этот пользователь является пользователем mysql (а не пользователем Linux-сервера).
mysqlbinlog -R --host = 192.168.101.2 -u root -p mysqld-bin.000001