Comment afficher rapidement l'instruction SQL d'origine dans MySQL

Contexte

Dans le cadre d'une recherche récente sur un projet existant, afin de déterminer quelles tables de base de données ont été modifiées pour une certaine opération commerciale, nous avons décidé de visualiser les instructions SQL enregistrées dans le binlog. J'ai toujours su que MySQL dispose de nombreux outils d'analyse de journaux binaires, mais je ne les ai jamais utilisés auparavant. Aujourd'hui, je vais enregistrer le processus.

Cette fois, en plus d'utiliser l'outil d'analyse officiel, nous avons également utilisé un outil open source tiers.

Préparation

Activer binlog

Tout d'abord, assurez-vous que le serveur MySQL a activé binlog Ceci est configuré dans le fichier my.cnf.

cat /etc/my.cnf

# 取消log_bin的注释即可，这里可以提供一个具体的路径，否则就使用默认地址
log_bin
# 高版本MySQL需要server-id这个参数，提供一个集群中不重复的id值即可
server-id=1
# 重新启动服务器
service mysqld restart

Enregistrez la position actuelle du journal

Afin de faciliter les tests ultérieurs, nous enregistrons d'abord certaines informations pertinentes du journal MySQL.

-- Confirmez que le journal a été correctement activé. Vous pouvez également afficher le chemin spécifique du fichier journal ici

mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------+
| Variable_name                   | Value                           |
+---------------------------------+---------------------------------+
| log_bin                         | ON                              |
| log_bin_basename                | /var/lib/mysql/mysqld-bin       |
| log_bin_index                   | /var/lib/mysql/mysqld-bin.index |
| log_bin_trust_function_creators | OFF                             |
| log_bin_use_v1_row_events       | OFF                             |
| sql_log_bin                     | ON                              |
+---------------------------------+---------------------------------+

-- Enregistrez le nom du fichier et la position de décalage du journal actuel, afin de pouvoir le localiser avec précision pendant visualisation ultérieure du journal

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysqld-bin.000001 |     2425 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+

-- Vous pouvez également afficher le journal en spécifiant l'heure de début, donc également enregistrer l'heure actuelle

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2018-08-02 09:59:43 |
+---------------------+

Exécuter DDL

Exécuter les instructions suivantes dans la bibliothèque de tests Enfin, notre objectif est de. restaurez cette partie des déclarations en consultant le binlog.

-- 建表
create table aaa(id int, title varchar(100));
-- 插入初始化数据
insert into aaa(id, title) values (1, '测试1');
insert into aaa(id, title) values (2, '测试2');
insert into aaa(id, title) values (3, '测试3');
-- 更新/删除
update aaa set title='修改' where id=2;
delete from aaa where id=1;

View binlog

Outil officiel mysqlbinlog

L'outil d'analyse officiel fourni est très simple à utiliser, mais la lisibilité des résultats analysés n'est pas bonne. Nous avons utilisé le paramètre no-defaults lors de l'appel pour éviter les erreurs de variable inconnue 'default-character-set=utf8'.

mysqlbinlog --no-defaults  /var/lib/mysql/mysqld-bin.000001 --start-position=2425

Les résultats d'affichage de mysqlbinlog ne sont pas lisibles. Voici un extrait Vous pouvez voir qu'en plus des instructions DDL que nous avons exécutées, d'autres informations contextuelles sont également enregistrées.

# at 2425
#180802 10:05:32 server id 1  end_log_pos 2553 CRC32 0x77e80f22 Querythread_id=70exec_time=0error_code=0
use `aaaa`/*!*/;
SET TIMESTAMP=1533175532/*!*/;
SET @@session.pseudo_thread_id=70/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
-- 建表
create table aaa(id int, title varchar(100))
/*!*/;
# at 2553
#180802 10:05:32 server id 1  end_log_pos 2632 CRC32 0x2bcb9bbd Querythread_id=70exec_time=0error_code=0
SET TIMESTAMP=1533175532/*!*/;
BEGIN
/*!*/;
# at 2632
#180802 10:05:32 server id 1  end_log_pos 2779 CRC32 0x8414086d Querythread_id=70exec_time=0error_code=0
SET TIMESTAMP=1533175532/*!*/;
-- 插入初始化数据
insert into aaa(id, title) values (1, '测试1')
/*!*/;

Outils tiers binlog2sql

Il existe de nombreux outils open source pour binlog Nous avons trouvé binlog2sql après une recherche sur Google. Il fournit plus de fonctions que le logiciel officiel. Selon différentes options, vous pouvez obtenir le SQL original, le SQL de restauration, INSERT SQL avec la clé primaire supprimée, etc. Cette fois, nous l'utilisons uniquement pour afficher le DDL exécuté. Pour une utilisation plus avancée, veuillez vous référer à la documentation officielle chinoise.

Cet outil est développé sur la base de Python (2.7+, 3.4+), alors installez d'abord les dépendances pertinentes conformément au manuel

git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.txt

La méthode d'utilisation est similaire à l'outil officiel, fournissant une position de décalage ou une heure de départ pour un positionnement précis. Si aucune information de localisation n'est fournie, toutes les informations seront affichées depuis le début.

# 使用偏移位置
python binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'root' --start-file='mysqld-bin.000001' --start-pos=2425 -d aaaa
# 同样功能，使用时间戳
python binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'root' --start-file='mysqld-bin.000001' --start-datetime='2018-08-02 10:00:00' -d aaaa

Les résultats d'affichage de cet outil sont très simples, comme suit

USE aaaa;
-- 建表
create table aaa(id int, title varchar(100));
USE aaaa;
-- 插入初始化数据
insert into aaa(id, title) values (1, '测试1');
USE aaaa;
insert into aaa(id, title) values (2, '测试2');
USE aaaa;
insert into aaa(id, title) values (3, '测试3');
USE aaaa;
-- 更新/删除
update aaa set title='修改' where id=2;
USE aaaa;
delete from aaa where id=1;

Apprentissage recommandé : "Tutoriel vidéo mysql"

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!