Sometimes, when you need to restore a backup, you do not need to restore the whole database – maybe you only need to, say, restore one or two tables – that’s where extracting data from a MySQL backup file can help.
Extracting a MySQL Backup
Extracting something from a MySQL backup refers to a set of procedures that are supposed to only restore a subset of the data in question – if the whole file you have is, for example, 50GB in size, you may be able to reduce disk space usage and time consumption. Here’s how to extract data from a MySQL backup:
- Start off by running a grep query for the table structure. The query below will show you all of the tables and their line numbers:
$ grep -n “Table structure” backup.sql
The query above will produce an output like so:
10:-- Table structure for table `table_1`
27:-- Table structure for table `table_2`
45:-- Table structure for table `table_3`
In this way, you can determine the starting and ending line numbers of each table – the starting number is the number which is displayed, the ending number is the number with which the other table begins with minus one (since the second table starts on the line after it).
Now, extract the data from the dump using the sed command (here the n option disables automatic printing and the p option means that the lines that the tool is told to print gets printed only once). In this case, the output will be saved to a file called table_1.sql (replace the file name according to your needs):
$ sed -n ‘10,26’ p backup.sql > table_1.sql
Then you can use the head command (a Linux and UNIX command for outputting the first n lines of a file, by default the line amount is set to 10) to check the contents of the output. Do it like so:
$ head table_1.sql
The query above should produce an output similar to the following:
-- Table structure for table `table_1`
DROP TABLE IF EXISTS `table_1`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `table_1` (
`column_1` VARCHAR(255) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’,
`column_2` VARCHAR(255) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’,
`column_3` VARCHAR(255) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’,
- If the contents of the output satisfy you, you’re almost done! To restore the backup, run:
$ mysql -u root -p database_name < table_1.sql
An extracted backup should now reside in a database called database_name.
Extracting a MySQL backup refers to a set of procedures that are supposed to only extract a subset of the entire data set. If you only need to extract, for example, one table out of the entire data dump that could contain tens of tables, MySQL backup extraction could be a sensible way to get the data you need.