1. Введение в бинлог

войти в системуmysql 3.23.14Представлен впервые, сгенерирован экземпляром сервера mysql.二进制日志, записывает все данные обновления mysql и операторы, которые могут обновлять данные (например, оператор удаления, который пропускает какую-либо строку). Binlog не имеет ничего общего с механизмом хранения, используемым mysql. Независимо от того, используется ли innodb или myisam, будет сгенерирован один и тот же binlog .

binlog также включает некоторые другие метаданные, такие как:

Информация о состоянии для сервера mysql, который регенерирует оператор
Error code
Метаданные, необходимые для ведения бинарного журнала, такие как события ротации

Бинлог — это ссылка на изменение глобального состояния сервера mysql, поддерживаемого mysql в серии операций. В более общем смысле бинарный журнал описывает все события глобального изменения состояния, которые когда-либо происходили на сервере mysql.

тип

Существует три типа binlog: журнал на основе операторов SQL (база операторов), журнал на основе строк (база строк) и смешанный журнал (смешанная база).

statement base

Журнал содержит SQL, который вызывает изменения данных, такие как вставка, обновление, удаление и т. д.

Недостатки: сохраняются только изменения sql, сильно зависящие от контекста каждого sql и сильно зависящие от полной согласованности между ведущим и подчиненным.Например, может быть несогласованная производительность функций (таких как сон, current_timestamp) во время репликации master-slave .

база строк (начиная с mysql 5.1)

Журнал содержит допустимые изменения строки

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

mixed base

Журнал операторов будет использоваться по умолчанию и при необходимости переключится на журнал строк.

использовать

синхронизация ведущий-ведомый

MySQL поддерживает ключ к высокому параллелизму. Синхронизация ведущий-ведомый обычно связана с разделением чтения и записи. Главная библиотека используется для записи, а подчиненные библиотеки используются для чтения. Таким образом, подчиненная библиотека может использоваться для совместного использования большое количество запросов на чтение и улучшить общую производительность MySQL. Бинлог записывает изменения данных главного узла mysql. Подчиненный узел периодически синхронизирует записи bin-журнала мастера по подписке, а ведомый узел записывает невыполненный bin-журнал в журнал ретрансляции.Форматы журнала ретрансляции и бин-журнала одинаковы. Ведомый узел воспроизводит изменения, происходящие на ведущем узле mysql, анализируя и выполняя журнал ретрансляции.

Процесс синхронизации master-slave в MySQL

Включить опцию лог-бина

Обязательно включитеlog-binвариант, mysql будет писать в binlog следующим образом

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
6 rows in set (0.00 sec)
如果发现log_bin为OFF状态，在/etc/mysql/mysql.conf.d/mysqld.cnf 中 写入

log-bin=on
数据备份/恢复

Частичное восстановление данных. После восстановления mysql с файлом резервной копии журнал bin, записанный после файла резервной копии, будет повторно выполнен.Эти журналы bin гарантируют, что данные mysql не будут потеряны, а данные будут скопированы.

2. Фактическая синхронизация master-slave и восстановление данных

В следующем примере используется docker для Mac, а базовое использование docker может быть опубликовано позже 😁

Используемая версия MySQL:mysql 5.6.49-log

Топология

Принята архитектура один ведущий и два ведомых.Конечно, принцип тот же.Также можно настроить два ведущих и два ведомых, несколько ведущих и несколько ведомых и т.д.

Из-за ограничений Docker для Mac невозможно напрямую использовать ip+порт контейнера для связи с Mac, поэтому порт 3306 контейнера mysql сопоставляется с Mac для связи, а докер по-прежнему использует порт ip контейнера для связи.

синхронизация ведущий-ведомый

Запустите три экземпляра и выполните соответствующее сопоставление портов.

docker run --name mysql_m -e MYSQL_ROOT_PASSWORD=1234 -d -P -p 32000:3306 mysql:5.6
docker run --name mysql_s1 -e MYSQL_ROOT_PASSWORD=1234 -d -P -p 32001:3306 mysql:5.6
docker run --name mysql_s2 -e MYSQL_ROOT_PASSWORD=1234 -d -P -p 32002:3306  mysql:5.6
查看是否开启bin log,log_bin 若为OFF状态则未开启bin_log

mysql -h0.0.0.0 -P32000 -uroot -p1234 -se  "show variables like '%log_bin%'"

Variable_name        Value
log_bin        OFF
log_bin_basename
log_bin_index
log_bin_trust_function_creators        OFF
log_bin_use_v1_row_events        OFF
sql_log_bin        ON
m开启bin_log，并配置server_id

docker exec -it mysql_m bash //进入容器
apt-get update && apt-get install vim //安装vim
vim /etc/mysql/mysql.conf.d/mysqld.cnf //进入配置文件
配置文件路径：/etc/mysql/mysql.conf.d/mysqld.cnf

Добавьте две строки в [mysqld]

log-bin   = binlog  #开启bin log,并且设置bin log文件名
server-id = 1   #主从同步机器的唯一标识
s1、s2配置server-id

Путь к файлу конфигурации: /etc/mysql/mysql.conf.d/mysqld.cnf

Добавить в [mysqld]

server-id = 2 #s1 配置

server-id = 3 #s2 配置
重启各个实例

docker restart mysql_m
docker restart mysql_s1
docker restart mysql_s2
m创建账号并赋予replacation和client权限用于主从同步

create user 'slave'@'%' identified by 'slave';
grant replication slave, replication client on *.* to 'slave'@'%';
s1、s2 master指向 m,并开启同步

change master to master_host='172.17.0.2', master_user='slave', master_password='slave', master_port=3306;
start slave;
在m创建test库，创建user表,并插入一条数据，不出意外的话，在从库也会自动复现上述数据

create database test;
create table user(
id int,
name varchar(100)
);
insert into user  values(1,"ab");
上述mysql_m, mysql_s1已经push到了docker hub，感兴趣的可以down一下: )

docker push ifndef666/mysql_m:v1
docker push ifndef666/mysql_s1:v1

Восстановление данных

В текущей тестовой библиотеке есть два фрагмента данных.

mysql> select * from user;
+------+------+
| id   | name |
+------+------+
|    1 | ab   |
|    2 | abc  |
+------+------+
2 rows in set (0.00 sec)
记录当前binlog位置，使用show master status

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000005 |      336 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
插入一个id为3的数据

mysql> insert into user  values(3,'abcd')
    -> ;
Query OK, 1 row affected (0.02 sec)

mysql> select * from user;
+------+------+
| id   | name |
+------+------+
|    1 | ab   |
|    2 | abc  |
|    3 | abcd |
+------+------+
3 rows in set (0.00 sec)
再次记录当前binlog位置

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000005 |      554 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
删除id为3的数据

delete from user where id = 3;
mysql> delete from user where id = 3;
Query OK, 1 row affected (0.01 sec)

mysql> select * from user;
+------+------+
| id   | name |
+------+------+
|    1 | ab   |
|    2 | abc  |
+------+------+
2 rows in set (0.00 sec)
登录到实例，导出指定位置binlog数据

root@55f5cfce56c8:/var/lib/mysql# mysqlbinlog --start-position=336 --stop-position=554 binlog.000005  > backup.sql
恢复数据

source /var/lib/mysql/backup.sql
...
mysql> select * from user;
+------+------+
| id   | name |
+------+------+
|    1 | ab   |
|    2 | abc  |
|    3 | abcd |
+------+------+
3 rows in set (0.00 sec)
总结

Восстановление данных фактически снова выполняет sql в течение указанного интервала binlog mysql. Само собой в процессе рекавери тоже будет генериться бинлог, можно и свой рекавери восстановить, эмммм, куклы запрещены 😸.

3. разбор формата binlog

Состав бинарного журнала

Содержит серию файлов журнала binlog и индексный файл.
Каждый файл журнала содержит «магическое число» 4, за которым следует серия событий описания изменения данных.
- 4 «магических числа»0xfe 0x62 0x69 0x6e
- Каждое событие содержит заголовок события и данные после заголовка.
  - Заголовок описывает тип события, когда оно было сгенерировано, какой сервер его сгенерировал и т. д.
  - данные — это конкретное описание указанного события, например, частичное изменение данных
- Первое событие указывает отформатированную версию, используемую текущим файлом binlog.
- Остальные события фиксируют изменения данных
- Последнее событие указывает на следующий файл журнала, поддерживая связь между бинлогами.
Индексный файл представляет собой список файлов binlog.

настоящий бой

Затем вы можете перейти к экземпляру mysql_m, который мы настроили, чтобы проверить конкретный формат файла binlog.

ps: Для удобства чтения ниже скрыта некоторая бесполезная информация Путь к каталогу файла binlog, который мы настроили, находится здесь: /var/lib/mysql Войдите в указанный каталог, вы можете просмотреть структуру каталогов

ls -l 
-rw-rw---- 1 mysql mysql      143 Nov  2 05:57 binlog.000001
-rw-rw---- 1 mysql mysql      143 Nov  2 05:58 binlog.000002
-rw-rw---- 1 mysql mysql     1540 Nov  3 12:38 binlog.000003
-rw-rw---- 1 mysql mysql       80 Nov  6 05:51 binlog.index

Вы можете видеть, что есть 1 файл индекса binlog.index и 3 файла журнала binlog.

root@55f5cfce56c8:/var/lib/mysql# cat binlog.index ./бинлог.000001 ./бинлог.000002 ./бинлог.000003 Состав файла индекса binlog по-прежнему очень прост, и цепочка журналов binlog сохраняется.

Ниже мы рассмотрим файл журнала binlog, который мы используем сейчас. Используйте show master status для просмотра файлов, используемых в данный момент, вы можете увидеть binlog.00000, когда файлы используются в данный момент.

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000003 |     1540 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Проверьте файл binlog и обнаружите, что в файле есть некоторые «искаженные символы». кот бинлог.000003

\bin��_tx5.6.49-log��_8


���_�"@std!!
            mysqlCREATE USER 'slave'@'%' IDENTIFIED BY PASSWORD '*51125B3597BEE0FC43E0BCBFEE002EF8641B44CF'�A�_��*@std!!
                                                                                                                       root%
                                                                                                                            mysqlGRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%'lj�u�_�9*@std!!
                 root%
                      mysqlgrant replication slave, replication client on *.* to 'slave'@'%'V��)��_^!@std!!
                                                                                                          testtestcreate database testZ}��_x        !@std!!
                                                                                                                                                      testtestcreate table user(
id int,
name varchar(100)
)��_���_O^      !@std!!
                      testtestBEGIN�[2���_n�        !@std!!
                                                      testtestinsert into user  values(1,"小明")o*��_�K3ї��_O:  !@std
                                                                                                                  testtestBEGIN
$����_��        !@std
                  testtestUPDATE `user` SET `name` = '1' WHERE `id` = '1' AND `name` = '??' LIMIT 1u�_J��_�XO�g��_O;    !@std
                                                                                                                          testtestBEGIN�����_��        !@std
                                                                                                                                                  testtestUPDATE `user` SET `name` = 'ab' WHERE `id` = '1' AND `name` = '1' LIMIT 1��4|��_�ZKzY�[O�_Nj�j

На самом деле, это также источник журнала binlog.Чтобы повысить эффективность сжатия данных, binlog использует определенный двоичный код для некоторых флагов.Вы также можете использовать инструмент обслуживания binlog - mysqlbinlog, упомянутый выше. Используйте mysqlbinlog для просмотра файла:

#201102  5:58:29 server id 1  end_log_pos 120 CRC32 0xe47fb0f4         Start: binlog v 4, server v 5.6.49-log created 201102  5:58:29 at startup

mysqlbinlog binlog.000003

#201103  5:50:16 server id 1  end_log_pos 663 CRC32 0x7d035a14         Query        thread_id=7        exec_time=0        error_code=0
SET TIMESTAMP=1604382616/*!*/;
create database test
/*!*/;
# at 663
#201103  5:51:50 server id 1  end_log_pos 783 CRC32 0xf45ff780         Query        thread_id=9        exec_time=0        error_code=0
use `test`/*!*/;
SET TIMESTAMP=1604382710/*!*/;
create table user(
id int,
name varchar(100)
)
/*!*/;
# at 783
#201103  5:55:34 server id 1  end_log_pos 862 CRC32 0xa4325bec         Query        thread_id=9        exec_time=0        error_code=0
SET TIMESTAMP=1604382934/*!*/;
BEGIN
/*!*/;
# at 862
#201103  5:55:34 server id 1  end_log_pos 972 CRC32 0x14052a6f         Query        thread_id=9        exec_time=0        error_code=0
SET TIMESTAMP=1604382934/*!*/;
insert into user  values(1,"小明")
/*!*/;
# at 972
#201103  5:55:34 server id 1  end_log_pos 1003 CRC32 0x97d11933         Xid = 75
COMMIT/*!*/;
# at 1003
#201103  5:56:01 server id 1  end_log_pos 1082 CRC32 0xede6240a         Query        thread_id=9        exec_time=0        error_code=0
SET TIMESTAMP=1604382961/*!*/;
/*!\C latin1 *//*!*/;

Видно, что sql при настройке синхронизации master-slave записывается в бинлог, поэтому, опираясь на бинлог, mysql slave нода может воспроизводить изменения master ноды.

4. Резюме

Вышеприведенное кратко описывает, что такое binlog, затем использует binlog для практики синхронизации master-slave и восстановления данных, и, наконец, кратко знакомит с форматом журнала binlog.

5. Справочная документация

Официальная документация:Dev.MySQL.com/doc/intern А…

Далее: Как реализовать «контейнер» с помощью go