Comment utiliser mysql binlog pour restaurer des données

Avant d'utiliser mysql binlog pour la récupération de données, binlog doit d'abord être activé. Configurez un environnement de test ici pour apprendre à utiliser mysql binlog pour restaurer la base de données. Le principe est relativement simple. Binlog stockera les données modifiées dans MySQL. Par exemple, si vous créez une base de données et écrivez des données, celles-ci seront stockées dans le binlog de MySQL.

Lorsque vous avez besoin de récupérer, trouvez deux positions : une position de départ et une position d'arrivée. La moitié de la position finale est la position avant la destruction ou la suppression des données. Mysql 8 a binlog activé par défaut

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

Vous pouvez voir que log_bin a été activé, et vous pouvez également voir que l'emplacement de stockage est dans le répertoire /daba/mysql, et le préfixe est binlog

ls /data/mysql/binlog.*
/data/mysql/binlog.000143  /data/mysql/binlog.000144  /data/mysql/binlog.000145  /data/mysql/binlog.000146  /data/mysql/binlog.index

Vous pouvez voir qu'il existe plusieurs fichiers journaux binlog, car voici La base de données de test (avec des informations historiques et des données précédentes, qui ne sont d'aucune utilité), est directement réinitialisée pour faciliter les tests. Soyez prudent lorsque vous supprimez tous les fichiers binlog. S'il s'agit d'un environnement de production, la valeur des données est incommensurable.

show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000143 |       156 | No        |
| binlog.000144 |       200 | No        |
| binlog.000145 |       156 | No        |
| binlog.000146 |       156 | No        |
+---------------+-----------+-----------+
4 rows in set (0.01 sec)

mysql> reset master;
Query OK, 0 rows affected (0.02 sec)

mysql> show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       156 | No        |
+---------------+-----------+-----------+
1 row in set (0.00 sec)


ls /data/mysql/binlog.*
/data/mysql/binlog.000001  /data/mysql/binlog.index

Après avoir exécuté la réinitialisation (reset master), vous pouvez voir que le fichier binlog précédent a été supprimé et qu'un nouveau fichier binlog est généré.

Vous pouvez vérifier le contenu de ce fichier

$mysqlbinlog binlog.000001
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
...

À ce moment, nous ajoutons des données

mysql <<EOT
create database test_liuhaolin_com;
select now();
EOT

mysql <<EOT
use test_liuhaolin_com;

create table if not exists test(
	\`id\` int unsigned not null auto_increment primary key,
	\`key\` varchar(100),
	\`val\` varchar(255)
) engine=myisam charset=utf8mb4;
EOT

mysql <<EOT
use test_liuhaolin_com;
insert into test  values (&#39;1&#39;,&#39;website&#39;, &#39;https://www.liuhaolin.com&#39;);
EOT

À ce moment, nous avons accidentellement supprimé la base de données test_liuhaolin_com

mysql> drop database test_liuhaolin_com;
Query OK, 1 row affected (0.09 sec)

Maintenant, nous devons résoudre un problème pratique, comment restaurer cela base de données

Tout d'abord, afin d'éviter les interférences, les journaux de vidage sont exécutés pour générer un nouveau fichier binlog.

mysql> show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |      1594 | No        |
+---------------+-----------+-----------+
1 row in set (0.00 sec)

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

mysql> flush logs;
Query OK, 0 rows affected (0.08 sec)

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

Pour récupérer des données, vous devez d'abord trouver où les données ont été supprimées.

mysqlbinlog binlog.000001 | grep -n  "drop database"
113:drop database test_liuhaolin_com

Vous pouvez voir qu'il y a une instruction delete à la ligne 113. Enfin, vous pouvez trouver deux endroits

1. L'emplacement de départ où les données doivent être restaurées

2. L'emplacement final où les données doivent être restaurées

L'emplacement de départ ici est l'endroit où vous avez créé la base de données, et l'emplacement de fin est l'endroit où vous avez supprimé l'emplacement de la base de données.

mysqlbinlog --set-charset=utf8  binlog.000001 > tmp.sql

Vous pouvez connaître les positions de début et de fin dans le fichier tmp.sql

binlog

Vous pouvez voir que la position de départ est 233

binlog

Vous pouvez voir que la position de fin est 1371, alors exécutez À propos de la récupération de données.

mysqlbinlog -v binlog.000001 --start-position=233 --stop-position=1371 | mysql

À titre de vérification, vous pouvez vérifier si la base de données existe avant l'exécution.

mysql> use test_liuhaolin_com;
ERROR 1049 (42000): Unknown database 'test_liuhaolin_com'

# 执行恢复操作
mysqlbinlog -v  binlog.000001  --start-position=233  --stop-position=1371 | mysql 

# 再次检查，可以发现数据已经恢复
mysql>
mysql> use test_liuhaolin_com;
Database changed

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!