How to use mysqldump backup
This article will give you a detailed explanation on how to use mysqldump backup. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.
Problem description
When using mysqldump to back up part of the data in a single table, no backup data was found. The array analyzes this strange phenomenon.
Problem recurrence and analysis # Table structure information mysql > show create table test.t1 +- - -+ | Table | Create Table | +-+- - -- + | T1 | CREATE TABLE `t1` (`id` int (11) NOT NULL AUTO_INCREMENT `time`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (`id`) KEY `idx_ t` (`time`) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 | +-+- - -- + 1 row in set (0.00 sec) # Table data information mysql > select * from test.t1 +-+ | id | time | +-+-+ | 1 | 2018-12-10 22:15:39 | | 2 | 2018-12-10 22:15:47 | | 3 | 2018-12-10 22:15:50 | 4 | 2018-12-10 22:15:56 | 5 | 2018-12-10 22:15:57 | | 6 | 2018-12-10 22:15:58 | | 7 | 2018-12-10 22:15:58 | 8 | 2018-12-10 22:16:06 | | 9 | 2018-12-10 22:16:06 | | 10 | 2018-12-10 22:16:07 | 11 | 2018-12-10 22:16:08 | | 12 | 2018-12-10 22 22 Suzhou 16pur13 | 13 | 2018-12-10 22 22 Suzhou 1612 | 14 | 2018-12 | -10 22:16:14 | | 15 | 0-12-10 22:16:15 | +-+ 15 sec)
Use the mysqldump-- where option to back up part of the T1 table data.
# using mysqldump to back up mysqldump-uroot-p123456-- default-character-set=utf8-Q-- master-data=2-- single-transaction-- databases test-- tables T1-- where "time > '2018-12-10 22 purl 16VR 08' > beifen.sql# through the backup file, you can see that there is no data in the backup result. ... LOCK TABLES `t1` WRITE * / /; 40000 ALTER TABLE `t1` ENABLE KEYS * /; UNLOCK TABLES;...
If you use the same condition query in MySQL, there is no exception, and the data can be found.
[root@master ~] # mysql-uroot-p123456-e "select * from test.t1 where time > '2018-12-10 22 uroot 16V 08'" mysql: [Warning] Using a password on the command line interface can be insecure.+----+-+ | id | time | +-+-+ | 12 | 2018-12-10 22:16:13 | | 13 | 2018-12-10 22:16:13 | | 14 | 2018-12-10 22:16:14 | 15 | 2018-12-10 22:16:15 | +-
Attempt to back up T1 full table data
Mysqldump-uroot-p123456-- default-character-set=utf8-Q-- master-data=2-- single-transaction-- databases test-- tables T1 > beifen.sql# has data, but by careful comparison, we can see that the time has gone back eight hours. LOCK TABLES `t1`WRITEX DISABLE KEYS * / 40000 ALTER TABLE `t1` INSERT INTO `t1` VALUES), (2meme 2018-12-10 1414 1548'), (3meme 2018-12-10 1414 151519'), (4meme 2018-12-10 1414 1556lv'), (5meme 2018-12-10 1414purr 1557'), (6meme 2018-12-10 141414 1515 1558'), (7meme 2018-12-10 1414 14RV 1558'), ), (2018-12-10 14 1608'), (12-12-10 12-10 12-10 13'), (13) 2018-12-10 14 14 1612 13'), (15) (15) '2018-12-10 14-14-16-15') / *! 40000 ALTER TABLE `t1` ENABLE KEYS * /; UNLOCK TABLES;# views the header information of mysqldump backup files. Mysqldump uses the mid-time zone. ... / *! 40103 SET TIME_ZONE='+00:00' * /;. # View MySQL and system time zone. Mysql > show variables like'% 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 | +-+-+ 29 rows in set [root@master ~] # date-RWed 12 Dec 2018 16:00:34 + 080 analog data recovery mysql > drop table test.t1 Mysql-uroot-p123456 select * from T1 +-+ | id | time | +-+-+ | 1 | 2018-12-10 22:15:39 | | 2 | 2018-12-10 22:15:47 | | 3 | 2018-12-10 22:15:50 | 4 | 2018-12-10 22:15:56 | 5 | 2018-12-10 22: 15:57 | | 6 | 2018-12-10 22:15:58 | | 7 | 2018-12-10 22:15:58 | | 8 | 2018-12-10 22:16:06 | 9 | 2018-12-10 22:16:06 | | 10 | 2018-12-10 22:16:07 | 11 | 2018-12-10 22:16:08 | 12 | 2018-12-10 22:16:13 | 13 | 2018-12-10 22 22 22 14 | 14 | 2018-12-10 22 22 14 | | | 15 | 0-12-10 22:16:15 | +-+ 15 rows in set (0.00 sec) # data returns to normal. However, there is a problem, because the mysqldump backup will convert the time zone of the data, resulting in a difference of 8 hours between the mysqldump filter condition and the filtered data, so it may make the where condition filter inaccurate. For example, the problems encountered at the beginning of the article.
Solution.
# use-- skip-tz-utc instead of mysqldump's default mid-time zone. Mysqldump-- default-character-set=utf8-Q-- master-data=2-- single-transaction-- databases test-- tables T1-- where "time='2018-12-10 2222"-uroot-p123456-- skip-tz-utc > beifen.sql# data is completely correct, and there is no time zone conversion in the file header, so the data is restored normally. LOCK TABLES `t1`WRITEActualization40000 ALTER TABLE `t1` DISABLE KEYS * /; INSERT INTO `t1` VALUES (11 ALTER TABLE 2018-12-10 22 DISABLE KEYS 1615 08'); / *! 40000 WRITE `t1` ENABLE KEYS * /; this is the end of UNLOCK TABLES; 's article on "how to use mysqldump backup". I hope the above content will be helpful to you so that you can learn more knowledge. if you think the article is good, please share it for more people to read.
