mysqlbinlog解析binlog乱码问题解密-tutoriel mysql-php.cn

Maison

base de données

tutoriel mysql

mysqlbinlog解析binlog乱码问题解密

PHP中文网

Mar 31, 2017 pm 04:51 PM

发现mysql库的binlog日志出来都是乱码，如下所示：
BINLOG ’
IXZqVhNIAAAALQAAAGcBAAAAAHoAAAAAAAEABHRlc3QAAno0AAEDAABUOcnY
IXZqVh5IAAAAKAAAAI8BAAAAAHoAAAAAAAEAAgAB//4BAAAAcu+UpA==，如果强行用-v出来也得不到具体执行的sql语句，这个问题困扰了很近，今天深入研究才发现核心问题所在。

1，binlog日志的困扰，先看下日志格式

mysql> show variables like &#39;%binlog%&#39;;
+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| binlog_cache_size                       | 1048576              |
| binlog_checksum                         | CRC32                |
| binlog_direct_non_transactional_updates | OFF                  |
| binlog_format                           | MIXED                |
| binlog_max_flush_queue_time             | 0                    |
| binlog_order_commits                    | ON                   |
| binlog_row_image                        | FULL                 |
| binlog_rows_query_log_events            | OFF                  |
| binlog_stmt_cache_size                  | 32768                |
| innodb_api_enable_binlog                | OFF                  |
| innodb_locks_unsafe_for_binlog          | OFF                  |
| max_binlog_cache_size                   | 18446744073709547520 |
| max_binlog_size                         | 1073741824           |
| max_binlog_stmt_cache_size              | 18446744073709547520 |
| sync_binlog                             | 1                    |
+-----------------------------------------+----------------------+
15 rows in set (0.01 sec)

mysql>

日志格式是MIXED的，这个表示一些特殊的uuid以及now()之类会记录成row，其它的仍然是记录sql模式。

2，测试例子：

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table z4 select 1 as a;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into z4 select 2;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>

blog为csdn博主黄杉(mchdba)所有，原地址为：http://blog.csdn.net/mchdba/article/details/50300035，谢绝转载。

3，查看binlog，打开是乱码模式，看不到执行的sql语句，如下所示

[[email protected] binlog_new]# ll
总用量 32
-rw-rw----. 1 mysql mysql   143 12月 10 21:09 mysql-bin.000001
-rw-rw----. 1 mysql mysql 17549 12月 11 15:06 mysql-bin.000002
-rw-rw----. 1 mysql mysql   618 12月 11 15:07 mysql-bin.000003
-rw-rw----. 1 mysql mysql   135 12月 11 15:06 mysql-bin.index
[[email protected] binlog_new]# /usr/local/mysql/bin/mysqlbinlog mysql-bin.000003
/*!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
#151211 15:06:46 server id 72  end_log_pos 120 CRC32 0x9961ff72     
Start: binlog v 4, server v 5.6.12-log created 151211 15:06:46
# Warning: this binlog is either in use or was not closed properly.
BINLOG &#39;
BnZqVg9IAAAAdAAAAHgAAAABAAQANS42LjEyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAXL/
YZk=
&#39;/*!*/;
# at 120
#151211 15:07:13 server id 72  end_log_pos 192 CRC32 0x3ea43b0e     
Query   thread_id=732   exec_time=0 error_code=0
SET TIMESTAMP=1449817633/*!*/;
SET @@session.pseudo_thread_id=732/*!*/;
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=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 192
#151211 15:07:13 server id 72  end_log_pos 314 CRC32 0xcaec51ae     
Query   thread_id=732   exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1449817633/*!*/;
CREATE TABLE `z4` (
  `a` int(1) NOT NULL DEFAULT &#39;0&#39;
)
/*!*/;
# at 314
#151211 15:07:13 server id 72  end_log_pos 359 CRC32 0xd8c93954     
Table_map: `test`.`z4` mapped to number 122
# at 359
#151211 15:07:13 server id 72  end_log_pos 399 CRC32 0xa494ef72     
Write_rows: table id 122 flags: STMT_END_F

BINLOG &#39;
IXZqVhNIAAAALQAAAGcBAAAAAHoAAAAAAAEABHRlc3QAAno0AAEDAABUOcnY
IXZqVh5IAAAAKAAAAI8BAAAAAHoAAAAAAAEAAgAB//4BAAAAcu+UpA==
&#39;/*!*/;
# at 399
#151211 15:07:13 server id 72  end_log_pos 430 CRC32 0xd1ab5b55     
Xid = 6908
COMMIT/*!*/;
# at 430
#151211 15:07:20 server id 72  end_log_pos 502 CRC32 0xdfc3212d     
Query   thread_id=732   exec_time=0 error_code=0
SET TIMESTAMP=1449817640/*!*/;
BEGIN
/*!*/;
# at 502
#151211 15:07:20 server id 72  end_log_pos 547 CRC32 0xc59aab0e     
Table_map: `test`.`z4` mapped to number 122
# at 547
#151211 15:07:20 server id 72  end_log_pos 587 CRC32 0x648b02a4     
Write_rows: table id 122 flags: STMT_END_F

BINLOG &#39;
KHZqVhNIAAAALQAAACMCAAAAAHoAAAAAAAEABHRlc3QAAno0AAEDAAAOq5rF
KHZqVh5IAAAAKAAAAEsCAAAAAHoAAAAAAAEAAgAB//4CAAAApAKLZA==
&#39;/*!*/;
# at 587
#151211 15:07:20 server id 72  end_log_pos 618 CRC32 0x9b35600a     Xid = 6915
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[[email protected] binlog_new]#

PS：大家看到/usr/local/mysql/bin/mysqlbinlog mysql-bin.000003解析出来的都是KHZqVhNIAAAALQAAACMCAAAAAHoAAAA这样的乱码格式。

4，google，得知可以用–base64-output=DECODE-ROWS -v查看出来sql语句，如下所示

[[email protected] binlog_new]# /usr/local/mysql/bin/mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000003
/*!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
#151211 15:06:46 server id 72  end_log_pos 120 CRC32 0x9961ff72     Start: binlog v 4, server v 5.6.12-log created 151211 15:06:46
# Warning: this binlog is either in use or was not closed properly.
# at 120
#151211 15:07:13 server id 72  end_log_pos 192 CRC32 0x3ea43b0e     Query   thread_id=732   exec_time=0 error_code=0
SET TIMESTAMP=1449817633/*!*/;
SET @@session.pseudo_thread_id=732/*!*/;
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=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 192
#151211 15:07:13 server id 72  end_log_pos 314 CRC32 0xcaec51ae     Query   thread_id=732   exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1449817633/*!*/;
CREATE TABLE `z4` (
  `a` int(1) NOT NULL DEFAULT &#39;0&#39;
)
/*!*/;
# at 314
#151211 15:07:13 server id 72  end_log_pos 359 CRC32 0xd8c93954     Table_map: `test`.`z4` mapped to number 122
# at 359
#151211 15:07:13 server id 72  end_log_pos 399 CRC32 0xa494ef72     Write_rows: table id 122 flags: STMT_END_F
### INSERT INTO `test`.`z4`
### SET
###   @1=1
# at 399
#151211 15:07:13 server id 72  end_log_pos 430 CRC32 0xd1ab5b55     Xid = 6908
COMMIT/*!*/;
# at 430
#151211 15:07:20 server id 72  end_log_pos 502 CRC32 0xdfc3212d     Query   thread_id=732   exec_time=0 error_code=0
SET TIMESTAMP=1449817640/*!*/;
BEGIN
/*!*/;
# at 502
#151211 15:07:20 server id 72  end_log_pos 547 CRC32 0xc59aab0e     Table_map: `test`.`z4` mapped to number 122
# at 547
#151211 15:07:20 server id 72  end_log_pos 587 CRC32 0x648b02a4     Write_rows: table id 122 flags: STMT_END_F
### INSERT INTO `test`.`z4`
### SET
###   @1=2
# at 587
#151211 15:07:20 server id 72  end_log_pos 618 CRC32 0x9b35600a     Xid = 6915
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[[email protected] binlog_new]#

确实可以看到sql语句，不过都是row模式的，如下所示：

### INSERT INTO `test`.`z4`
### SET
###   @1=2
# at 587

看到不到应用程序或者客户端执行的真正sql语句，这样也不利于进行业务分析数据分析，无助于对程序的优化。

5，问题分析

这样binlog日志格式MIXED都录制为乱码，那我将换成STATEMENT格式看看是否会持续乱码？，修改完my.cnf后，重启mysql数据库，开始建表测试，但是报错如下：

mysql> create table z3 select 1 as a;
ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since 
BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. 
InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
mysql>

问题发现了，看到问题贺新郎，隔离级别太低了，我的默认隔离级别是READ-COMMITTED，所以导致binlog记录的必须都是row模式，解析出来的是乱码，强行用-v显示出来也是row模式，所以我将隔离级别升级为REPEATABLE-READ的话，就会有row格式也会有statement格式了。接下来为了用2个小实例来验证我的判断：

4.1 REPEATABLE-READ和STATEMENT测试结果

**所以我将隔离级别升级为REPEATABLE-READ，binlog设置为binlog_format=STATEMENT
如下所示：**

# Set the default transaction isolation level. Levels available are:
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
transaction_isolation = REPEATABLE-READ
binlog_format=STATEMENT

然后重启mysql，看binlog的记录形式是啥样的，如下所示：

BEGIN
/*!*/;
# at 219
# at 251
#151211 16:15:02 server id 72  end_log_pos 251 CRC32 0x4ea440db     Intvar
SET INSERT_ID=10550/*!*/;
#151211 16:15:02 server id 72  end_log_pos 435 CRC32 0xa37c5f2d     Query   thread_id=1 exec_time=0 error_code=0
use `parking_db`/*!*/;
SET TIMESTAMP=1449821702/*!*/;
INSERT INTO access_log.access_log VALUES(NULL,CONNECTION_ID(),NOW(),USER(),CURRENT_USER())
/*!*/;
# at 435
#151211 16:15:02 server id 72  end_log_pos 466 CRC32 0x2970e89a     Xid = 3
COMMIT/*!*/;
# at 466
#151211 16:16:22 server id 72  end_log_pos 569 CRC32 0xbe43b367     Query   thread_id=2 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1449821782/*!*/;
create table z4 select 1 as a
/*!*/;
# at 569
#151211 16:16:32 server id 72  end_log_pos 648 CRC32 0x69b2383c     Query   thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1449821792/*!*/;
BEGIN
/*!*/;
# at 648
#151211 16:16:32 server id 72  end_log_pos 745 CRC32 0xcd1721a4     Query   thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1449821792/*!*/;
insert into z4 select 2
/*!*/;
# at 745
#151211 16:16:32 server id 72  end_log_pos 776 CRC32 0xfc0dcfc4     Xid = 70
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[[email protected] binlog_new]#

看到有类似的INSERT INTO access_log.access_log VALUES(NULL,CONNECTION_ID(),NOW(),USER(),CURRENT_USER())这里的一些函数最好用row模式，因为主从复制的时候，uuid已经now()等会造成时间延迟，故而为了数据一致性，statement格式不是最佳选择。

4.2 REPEATABLE-READ和MIXED测试结果

my.cnf里面修改设置：

transaction_isolation = REPEATABLE-READ
binlog_format=MIXED

重启mysql数据库后，录入测试数据：

mysql> insert into z4 select 3;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> exit

查看binlog数据，会看到insert into z4 select 3这条sql记录，表明在mixed模式下，解析出来的sql是正常的，有些now()已经uuid的直接解析成row格式了，如下所示：

[[email protected] binlog_new]# /usr/local/mysql/bin/mysqlbinlog 
--base64-output=DECODE-ROWS -v mysql-bin.000007
...
# at 274
#151211 16:21:02 server id 72  end_log_pos 368 CRC32 0x156a1c51     Write_rows: table id 70 flags: STMT_END_F
### INSERT INTO `access_log`.`access_log`
### SET
###   @1=10551
###   @2=1
###   @3=1449822062
###   @4=&#39;[email protected]&#39;
###   @5=&#39;[email protected]%&#39;
# at 368
#151211 16:21:02 server id 72  end_log_pos 399 CRC32 0x8254defe     Xid = 3
COMMIT/*!*/;
# at 399
#151211 16:21:25 server id 72  end_log_pos 478 CRC32 0xe252f5c7     Query   thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1449822085/*!*/;
BEGIN
/*!*/;
# at 478
#151211 16:21:25 server id 72  end_log_pos 575 CRC32 0x34308ad6     Query   thread_id=2 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1449822085/*!*/;
insert into z4 select 3
/*!*/;
# at 575
#151211 16:21:25 server id 72  end_log_pos 606 CRC32 0x67c460eb     Xid = 61
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[[email protected] binlog_new]#

Déclaration

Le contenu de cet article est volontairement contribué par les internautes et les droits d'auteur appartiennent à l'auteur original. Ce site n'assume aucune responsabilité légale correspondante. Si vous trouvez un contenu suspecté de plagiat ou de contrefaçon, veuillez contacter admin@php.cn

Article connexe

Quelles sont les procédures stockées dans MySQL?May 01, 2025 am 12:27 AM

Les procédures stockées sont des instructions SQL précompilées dans MySQL pour améliorer les performances et simplifier les opérations complexes. 1. Améliorer les performances: après la première compilation, les appels ultérieurs n'ont pas besoin d'être recompilés. 2. Améliorez la sécurité: restreignez l'accès à la table de données grâce au contrôle de l'autorisation. 3. Simplifier les opérations complexes: combinez plusieurs instructions SQL pour simplifier la logique de la couche d'application.

Comment fonctionne la mise en cache de requête dans MySQL?May 01, 2025 am 12:26 AM

Le principe de travail du cache de requête MySQL consiste à stocker les résultats de la requête sélectionnée, et lorsque la même requête est à nouveau exécutée, les résultats mis en cache sont directement renvoyés. 1) Le cache de requête améliore les performances de lecture de la base de données et trouve des résultats mis en cache grâce aux valeurs de hachage. 2) Configuration simple, définissez query_cache_type et query_cache_size dans le fichier de configuration mysql. 3) Utilisez le mot-clé SQL_NO_CACH pour désactiver le cache de requêtes spécifiques. 4) Dans les environnements de mise à jour à haute fréquence, le cache de requête peut provoquer des goulots d'étranglement des performances et doit être optimisé pour une utilisation par la surveillance et l'ajustement des paramètres.

Quels sont les avantages de l'utilisation de MySQL sur d'autres bases de données relationnelles?May 01, 2025 am 12:18 AM

Les raisons pour lesquelles MySQL sont largement utilisées dans divers projets comprennent: 1. Haute performances et évolutivité, en prenant en charge plusieurs moteurs de stockage; 2. Facile à utiliser et à maintenir, configuration simple et outils riches; 3. Écosystème riche, attirant un grand nombre de supports d'outils communautaires et tiers; 4. Prise en charge multiplateforme, adaptée à plusieurs systèmes d'exploitation.

Comment gérez-vous les mises à niveau de la base de données dans MySQL?Apr 30, 2025 am 12:28 AM

Les étapes de mise à niveau de la base de données MySQL incluent: 1. Sauvegarder la base de données, 2. Arrêtez le service MySQL actuel, 3. Installez la nouvelle version de MySQL, 4. Démarrez la nouvelle version du service MySQL, 5. Récupérez la base de données. Des problèmes de compatibilité sont nécessaires pendant le processus de mise à niveau, et des outils avancés tels que Perconatoolkit peuvent être utilisés pour les tests et l'optimisation.

Quelles sont les différentes stratégies de sauvegarde que vous pouvez utiliser pour MySQL?Apr 30, 2025 am 12:28 AM

Les politiques de sauvegarde MySQL incluent une sauvegarde logique, une sauvegarde physique, une sauvegarde incrémentielle, une sauvegarde basée sur la réplication et une sauvegarde cloud. 1. Logical Backup utilise MySQLDump pour exporter la structure et les données de la base de données, ce qui convient aux petites bases de données et aux migrations de versions. 2. Les sauvegardes physiques sont rapides et complètes en copie les fichiers de données, mais nécessitent la cohérence de la base de données. 3. La sauvegarde incrémentielle utilise la journalisation binaire pour enregistrer les modifications, ce qui convient aux grandes bases de données. 4. La sauvegarde basée sur la réplication réduit l'impact sur le système de production en sauvegarde du serveur. 5. Les sauvegardes cloud telles que Amazonrds fournissent des solutions d'automatisation, mais les coûts et le contrôle doivent être pris en compte. Lors de la sélection d'une politique, de la taille de la base de données, de la tolérance aux temps d'arrêt, du temps de récupération et des objectifs de points de récupération doit être pris en compte.

Qu'est-ce que le regroupement MySQL?Apr 30, 2025 am 12:28 AM

MysqlclusteringenhancesdatabaserobustnessandscalabilityByDistritingDataacRossMultiplenodes.itUsEsthendBenginefordataReplication andfaultToleance, assurant la manière

Comment optimiser la conception du schéma de base de données pour les performances dans MySQL?Apr 30, 2025 am 12:27 AM

L'optimisation de la conception du schéma de la base de données dans MySQL peut améliorer les performances par les étapes suivantes: 1. Optimisation d'index: Créer des index sur les colonnes de requête communes, équilibrant la surcharge de la requête et insertion des mises à jour. 2. Optimisation de la structure du tableau: Réduisez la redondance des données par normalisation ou anti-normalisation et améliorez l'efficacité d'accès. 3. Sélection du type de données: utilisez des types de données appropriés, tels que INT au lieu de Varchar, pour réduire l'espace de stockage. 4. Partionnement et sous-table: Pour les volumes de données importants, utilisez le partitionnement et la sous-table pour disperser les données pour améliorer l'efficacité de la requête et de la maintenance.

Comment pouvez-vous optimiser les performances MySQL?Apr 30, 2025 am 12:26 AM

TOOPTIMIZEMYSQLPERFORMANCE, SuivreTheSestets: 1) Implémentation de PROPERIDEXINGTOSPEEDUPQUERIES, 2) Utiliser la manière dont la gamme ajustée en ligne

See all articles