Личное присутствие вредит славе (Ф. Петрарка).

15 примеров команды mysqlbinlog для двоичных файлов в MySQL

FavoriteLoadingДобавить в избранное
1 Звезда2 Звезды3 Звезды4 Звезды5 Звезд (1 оценок, среднее: 5,00 из 5)
Загрузка...
10 октября 2018
15 примеров команды mysqlbinlog для двоичных файлов в MySQL
В MySQL или MariaDB в любое время, когда вы вносите изменения в базу данных, это конкретное событие регистрируется.

Например, когда вы создаете новую таблицу или обновляете данные в существующей таблице, эти события хранятся в двоичных журналах.

Двоичный журнал очень полезен в репликации MySQL, где главный сервер будет отправлять данные из двоичных журналов на удаленные сервера.

Вы также будете иметь дело с бинарными файлами журнала, когда выполняете какие-либо операции восстановления в MySQL.

Команда mysqlbinlog используется для просмотра содержимого двоичного журнала в удобном для чтения формате. Вы также будете использовать команду mysqlbinlog для чтения содержимого и подключения его к другим утилитам mysql.

В этом уроке мы обсудим следующие примеры, используя команду mysqlbinlog:

  1. Получить список текущих двоичных журналов
  2. Поведение по умолчанию Mysqlbinlog
  3. Получить записи для конкретной базы данных
  4. Отключить двоичный журнал для восстановления
  5. Управление base-64 в выводе BINLOG
  6. Отладочная информация в выводе mysqlbinlog
  7. Пропустить первые N количество записей
  8. Сохранить вывод в файл
  9. Извлечение записей, начиная с определенного положения
  10. Извлечь записи до определенного положения
  11. Сбросить журналы для чистого вывода Binlog
  12. Отображать только SQL-запросы в выводе
  13. Просмотр записей, начиная с определенного времени
  14. Просмотр записей до определенного времени
  15. Получить двоичный журнал с удаленного сервера

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*/;

Вышеприведенная команда отображает события, которые произошли во всей базе данных в этой системе.

Примечание
Когда вы выполняете команду mysqlbinlog, вы можете получить это сообщение об ошибке «unknown variable default-character-set=utf8», в этом случае используйте параметр -set-charset в mysqlbinlog, чтобы установить соответствующий набор символов. Другой вариант заключается в том, чтобы добавить “free-default-character-set = utf8” в файл /etc/my.cnf в разделе [client].

Если вы новичок в 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 

 

Примечание
На выходе, когда вы указываете опцию -D, вы увидите дополнительную вторую строку на выходе, которая говорит SQL_LOG_BIN = 0.
/ *! 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 

 

Предупреждение
Примечание. Вы также можете указать -server-id и извлечь записи журнала, которые генерируются сервером mysql с данным идентификатором сервера.
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

 

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Просмотров: 12

Если статья понравилась, то поделитесь ей в социальных сетях:

Добавить комментарий

Войти с помощью: 

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Сообщить об опечатке

Текст, который будет отправлен нашим редакторам:

Заполните форму и наш менеджер перезвонит Вам в самое ближайшее время!

badge
Обратный звонок 1
Отправить
galka

Спасибо! Ваша заявка принята

close
galka

Спасибо! Ваша заявка принята

close