How to restore a table and table structure from a dump file backed up by MySQL

This article mainly introduces how to restore a table and table structure from the dump file backed up by MySQL, which has a certain reference value, and interested friends can refer to it. I hope you can learn a lot after reading this article.

For example, the full library dump file is test.sql.

Extract the contents of the t_user_sign table from the full library backup test.sql:

[root@linuxidc ~] # grep 'INSERT INTO `troomuser` sign`' test.sql > / tmp/t_user_sign.sql

INSERT INTO `tresume username _ sign` VALUES ('913400007568317795' 0Z) 661401092936Z' 20150101000000', '60.166.38.226'', '2016-11-24' 17 2419'

0001221E7FF447FB96111C2510F5B80 Filippence74-27murmurEAmur4BF5MuthA1Fujie.

.

Test.sql extracts the table structure of the tweak username _ sign table from the full database backup:

[root@linuxidc ~] # sed-eaccountant test.sql. / {Hincincter test.sql;}'- e'x Bandard create username _ sign` /! d * / tmp/create.sql

[root@linuxidc ~] # cat / tmp/create.sql

DROP TABLE IF EXISTS `tweak username _ sign`

/ *! 40101 SET @ saved_cs_client = @ @ character_set_client * /

/ *! 40101 SET character_set_client = utf8 * /

CREATE TABLE `troomuser _ sign` (

`SH`varchar (25) DEFAULT NULL COMMENT 'tax number'

`ZFJH` varchar (20) DEFAULT NULL COMMENT 'main extension'

`KPJH` varchar (20) DEFAULT NULL COMMENT 'invoice machine number'

`FXSJ` varchar (26) DEFAULT NULL COMMENT 'release time'

`IP`varchar (20) DEFAULT NULL COMMENT 'customer IP address'

`ZCSJ`datetime DEFAULT NULL COMMENT 'Registration time'

`ID`varchar (32) NOT NULL COMMENT 'ID'

`MAC`varchar (32) DEFAULT NULL COMMENT 'MAC address'

`DQMC` varchar (120) DEFAULT NULL COMMENT 'region name'

`DQBM`DQBM` varchar (6) DEFAULT NULL COMMENT 'area code'

`OS` varchar (120) DEFAULT NULL COMMENT 'operating system'

`SBLX` varchar (120) DEFAULT NULL COMMENT 'device type'

`WLFWS` varchar (120) DEFAULT NULL COMMENT 'Internet Service provider'

`LATITUDE` varchar (20) DEFAULT NULL COMMENT 'Dimension'

`LONGITUDE` varchar (20) DEFAULT NULL COMMENT 'longitude'

`NAME`varchar (500) DEFAULT NULL COMMENT 'Enterprise name'

`VERSION` varchar (30) DEFAULT NULL

PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/ * 40101 SET character_set_client = @ saved_cs_client * /

Restore based on binlog point in time:

[root@linuxidc] # mysqlbinlog-- start-datetime= "2017-1-16 19:00:00"-- stop-datetime= "2017-1-16 19:38:00" mysql-bin.000398 > / tmp/time.sql

[root@idb4 tmp] # more / tmp/time.sql

/ *! 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

# 170116 19:37:02 server id 1 end_log_pos 120 CRC32 0x1cf0554e Start: binlog v 4, server v 5.6.27-log created 170116 19:37:02

BINLOG'

XrB8WA8BAAAAdAAAAHgAAAAAAAQANS42LjI3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAU5V

8Bw =

'/ *! * /

# at 120

# 170116 19:37:02 server id 1 end_log_pos 215 CRC32 0x16c50c8a Query thread_id=238 exec_time=0 error_code=0

SET timestamp 1484566622

SET @ @ session.pseudoclinic thread readership idling 238plankton /

SET @ @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session.

SET @ @ session.sqlcards modewords 1075838976 Universe

SET @ @ session.auto_increment_increment=1, @ @ session.

/ *!\ C latin1 * /! * /

SET @ @ session. Session. Session setting client.session. Session. Collationalization connections8 session. Session. Collationalization serverationserver8 apperance.

SET @ @ session. Session. LCC timetables namespace.

SET @ @ session.collationalization databases

BEGIN

/ *! * /

. ..

Thank you for reading this article carefully. I hope the article "how to restore a table and table structure from a MySQL full database backup dump file" shared by the editor is helpful to everyone. At the same time, I also hope you can support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!