这篇文章将为大家详细讲解有关如何使用mysqldump备份，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。

问题描述

在一次使用mysqldump备份单表部分数据时，发现无备份数据。阵针对这一奇怪现象，进行分析。

问题复现与分析

#表结构信息mysql>showcreatetabletest.t1;+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|Table|CreateTable|+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|t1|CREATETABLE`t1`(`id`int(11)NOTNULLAUTO_INCREMENT,`time`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,PRIMARYKEY(`id`),KEY`idx_t`(`time`))ENGINE=InnoDBAUTO_INCREMENT=16DEFAULTCHARSET=utf8|+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1rowinset(0.00sec)#表数据信息mysql>select*fromtest.t1;+----+---------------------+|id|time|+----+---------------------+|1|2018-12-1022:15:39||2|2018-12-1022:15:47||3|2018-12-1022:15:50||4|2018-12-1022:15:56||5|2018-12-1022:15:57||6|2018-12-1022:15:58||7|2018-12-1022:15:58||8|2018-12-1022:16:06||9|2018-12-1022:16:06||10|2018-12-1022:16:07||11|2018-12-1022:16:08||12|2018-12-1022:16:13||13|2018-12-1022:16:13||14|2018-12-1022:16:14||15|2018-12-1022:16:15|+----+---------------------+15rowsinset(0.00sec)

使用mysqldump --where选项备份t1表部分数据。

#使用mysqldump根据time列条件备份mysqldump-uroot-p123456--default-character-set=utf8-q--master-data=2--single-transaction--databasestest--tablest1--where"time>'2018-12-1022:16:08'">beifen.sql#通过备份文件可以看出备份结果中并无数据。...LOCKTABLES`t1`WRITE;/*!40000ALTERTABLE`t1`DISABLEKEYS*/;/*!40000ALTERTABLE`t1`ENABLEKEYS*/;UNLOCKTABLES;...

在MySQL中使用相同条件查询，并无异常，可以查到数据。

[root@master~]#mysql-uroot-p123456-e"select*fromtest.t1wheretime>'2018-12-1022:16:08'"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+----+---------------------+|id|time|+----+---------------------+|12|2018-12-1022:16:13||13|2018-12-1022:16:13||14|2018-12-1022:16:14||15|2018-12-1022:16:15|+----+---------------------+

尝试备份t1全表数据

mysqldump-uroot-p123456--default-character-set=utf8-q--master-data=2--single-transaction--databasestest--tablest1>beifen.sql#有数据，但是仔细对照，我们可以发现时间回退了八个小时。LOCKTABLES`t1`WRITE;/*!40000ALTERTABLE`t1`DISABLEKEYS*/;INSERTINTO`t1`VALUES(1,'2018-12-1014:15:39'),(2,'2018-12-1014:15:47'),(3,'2018-12-1014:15:50'),(4,'2018-12-1014:15:56'),(5,'2018-12-1014:15:57'),(6,'2018-12-1014:15:58'),(7,'2018-12-1014:15:58'),(8,'2018-12-1014:16:06'),(9,'2018-12-1014:16:06'),(10,'2018-12-1014:16:07'),(11,'2018-12-1014:16:08'),(12,'2018-12-1014:16:13'),(13,'2018-12-1014:16:13'),(14,'2018-12-1014:16:14'),(15,'2018-12-1014:16:15');/*!40000ALTERTABLE`t1`ENABLEKEYS*/;UNLOCKTABLES;#查看mysqldump备份文件头部信息，mysqldump使用的是中时区。.../*!40103SETTIME_ZONE='+00:00'*/;...#查看MySQL和系统时区。mysql>showvariableslike'%time%';+---------------------------------+-------------------+|Variable_name|Value|+---------------------------------+-------------------+|binlog_max_flush_queue_time|0||connect_timeout|10||datetime_format|%Y-%m-%d%H:%i:%s||default_password_lifetime|0||delayed_insert_timeout|300||explicit_defaults_for_timestamp|OFF||flush_time|0||have_statement_timeout|YES||innodb_flush_log_at_timeout|1||innodb_lock_wait_timeout|50||innodb_old_blocks_time|1000||innodb_rollback_on_timeout|OFF||interactive_timeout|28800||lc_time_names|en_US||lock_wait_timeout|31536000||log_timestamps|UTC||long_query_time|10.000000||max_execution_time|0||net_read_timeout|30||net_write_timeout|60||rpl_semi_sync_master_timeout|10000||rpl_stop_slave_timeout|31536000||slave_net_timeout|60||slow_launch_time|2||system_time_zone|CST||time_format|%H:%i:%s||time_zone|+08:00||timestamp|1544775697.554299||wait_timeout|28800|+---------------------------------+-------------------+29rowsinset(0.01sec)[root@master~]#date-RWed,12Dec201816:00:34+0800#模拟数据恢复mysql>droptabletest.t1;mysql-uroot-p123456<beifenmysql>select*fromt1;+----+---------------------+|id|time|+----+---------------------+|1|2018-12-1022:15:39||2|2018-12-1022:15:47||3|2018-12-1022:15:50||4|2018-12-1022:15:56||5|2018-12-1022:15:57||6|2018-12-1022:15:58||7|2018-12-1022:15:58||8|2018-12-1022:16:06||9|2018-12-1022:16:06||10|2018-12-1022:16:07||11|2018-12-1022:16:08||12|2018-12-1022:16:13||13|2018-12-1022:16:13||14|2018-12-1022:16:14||15|2018-12-1022:16:15|+----+---------------------+15rowsinset(0.00sec)#数据恢复正常。但是存在一个问题，因为mysqldump备份时会把数据进行时区转换，导致mysqldump过滤条件与筛选的数据差8个小时，所以可能会使where条件过滤不准确。比如文章开头所碰到的问题。

解决办法

#使用--skip-tz-utc，不使用mysqldump默认的中时区。mysqldump--default-character-set=utf8-q--master-data=2--single-transaction--databasestest--tablest1--where"time='2018-12-1022:16:08'"-uroot-p123456--skip-tz-utc>beifen.sql#数据完全正确，文件头部无时区转换，所以数据恢复时正常。LOCKTABLES`t1`WRITE;/*!40000ALTERTABLE`t1`DISABLEKEYS*/;INSERTINTO`t1`VALUES(11,'2018-12-1022:16:08');/*!40000ALTERTABLE`t1`ENABLEKEYS*/;UNLOCKTABLES;

关于“如何使用mysqldump备份”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。