Shulou(Shulou.com)06/01 Report--

Editor to share with you the method of extracting table backup from MySQL DB dump files. I hope you will gain a lot after reading this article. Let's discuss it together.

Mysqldump is a utility provided by mysql/mariadb for backing up databases and tables. Generally speaking, we will dump and save the database on a regular basis. But sometimes we need to restore one or two tables from the backup. The question is how to restore a specific table from a full backup file? This article will cover extracting table backups from a complete database backup file. In addition, we can extract all table backups from a single file in each table.

Step 1: download the MySQL dump split script

Split the complete mysqldump database backup file in a separate table-specific file backup. Create a file called splitdb.sh and copy the following script in it.

#! / bin/bash# Split MySQL dump SQL file into one file per table# based on http://blog.tty.nl/2011/12/28/splitting-a-database-dump####if [$#-lt 1]; then echo "USAGE $0 DUMP_FILE [TABLE]" exitfiif [$#-ge 2] Then csplit-s-ftable $1 "/-- Table structure for table/"%-- Table structure for table `$ 2`%"/-- Table structure for table/"% 40103 SET TIME_ZONE=@OLD_TIME_ZONE%1" else csplit-s-ftable $1 "/-- Table structure for table/" {*} fi [$?-eq 0] | exitmv table00 headFILE= `ls-1 table* | tail-n 1`if [$#-ge 2] Then mv $FILE footelse csplit-b'% d'-s-f$FILE $FILE "/ 40103 SET TIME_ZONE=@OLD_TIME_ZONE/" {*} mv ${FILE} 1 footfifor FILE in `ls-1 table* `; do NAME= `head-N1 $FILE | cut-dudes roomx60'-f2` cat head $FILE foot > "$NAME.sql" donerm head foot table*

Step 2: extract all tables from dump

For this example, there is a dump file called mydb.sql that you want to split in a small backup of each table. To do this, you need to create a new directory / opt / splitdb and copy a script called splitDB.sh in this directory. Now use the following command to extract all tables from a single backup file.

# cd / opt / splitdb # sh splitDB.sh mydb.sql

Step 3: extract a single table from dump

If we only want to extract one table, we can use the following command. For example, you want to split only tables named my_tbl1 and my_tbl2. It will extract backups named my_tbl1.sql and my_tbl2.sql in the current directory.

# cd / opt / splitdb # sh splitDB.sh mydb.sql my_tbl1 # sh splitDB.sh mydb.sql my_tbl2 after reading this article, I believe you have some understanding of the method of extracting table backups from MySQL DB dump files. You want to know more about it. Welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.