Extract tables from a SQL database file
SQL database backup files for a VIP Platform environment can be downloaded from the VIP Dashboard. Database backup files can include a copy of the entire database, only the tables for a specific network site, or a specific set of one or more tables.
If needed, a specific table—or set of tables—can be extracted from a downloaded database file and saved to a new and separate SQL database file on a user’s local machine. The newly created file can be imported to a local development environment or be used for other purposes.
Prerequisite
A SQL database backup file must be downloaded and available on the user’s local machine.
List tables
Retrieve a list of tables that exist in a downloaded backup SQL database file:
grep -E '^-- Table structure' <file-name>.sql
An example request and output for the list of tables in a downloaded file backup.sql:
$ grep -E '^-- Table structure' backup.sql -- Table structure for table `wp_a8c_cron_control_jobs` -- Table structure for table `wp_blogmeta` -- Table structure for table `wp_blogs` -- Table structure for table `wp_commentmeta` -- Table structure for table `wp_comments` -- Table structure for table `wp_links` -- Table structure for table `wp_options` -- Table structure for table `wp_postmeta` -- Table structure for table `wp_posts` -- Table structure for table `wp_signups` -- Table structure for table `wp_site` -- Table structure for table `wp_sitemeta` -- Table structure for table `wp_term_relationships` -- Table structure for table `wp_term_taxonomy` -- Table structure for table `wp_termmeta` -- Table structure for table `wp_terms` -- Table structure for table `wp_usermeta` -- Table structure for table `wp_users`
Extract a specific table
To extract a specific table, the line numbers in the SQL file where the table exists must first be identified. Use those identified line numbers to target and extract the table to a separate file.
Identify the lines in the file that contain the table
Adding the -n
option to the “list tables” command will include line numbers in the command output. Piping that command output to grep -A1 <table-name>
will report the line number where the selected table begins, as well as the line number where the next table in the SQL file begins.
grep -n -E '^-- Table structure' <file-name>.sql | grep -A1 <table-name>
In this example, the command is formatted to identify the line where the table wp_posts
begins, and the table that follows it, in a backup file named backup.sql:
$ grep -n -E '^-- Table structure' backup.sql | grep -A1 wp_posts 3793:-- Table structure for table `wp_posts` 3842:-- Table structure for table `wp_signups`
The example command output indicates that the wp_posts
table begins on line 3793
, and ends at line 3841
(one line before the wp_signups
table begins).
Extract the table with the identified line numbers
Target a table by using the beginning and ending line number values and extract the table to a new SQL file.
sed -n '<beginning>,<ending> p' <file-name>.sql > new-file.sql
In this command example, a table is extracted from a backup file named backup.sql using the beginning line number 3793
and the ending line number 3841
. The table is then saved to a newly created file named new-file.sql:
sed -n '3793,3841 p' backup.sql > new-file.sql
Extract multiple tables
Multiple tables can be extracted from a downloaded backup file and appended to a single SQL file.
Identify the lines in the file that contain each table
For each table to be extracted from the downloaded backup file, identify the beginning and ending lines for a table.
grep -n -E '^-- Table structure' <file-name>.sql | grep -A1 <table-name>
Repeat the above command for each table to be extracted.
Extract specific tables and append to a single file
Extract the first table to a new SQL file:
sed -n '<beginning>,<ending> p' <file-name>.sql > new-file.sql
Extract additional tables in separate commands and append them to the new SQL file using >>
.
sed -n '<beginning>,<ending> p' <file-name>.sql >> new-file.sql
Extract tables for a network site on a multisite
Database backup files for WordPress multisite environments include all tables for all sites on the network. If only the tables for a single network site are needed, those tables can be extracted from the backup file and saved to their own separate file.
A network site’s tables in a SQL file are structured with the table_prefix wp_
followed by the site’s ID. All of a network site’s tables in a database backup file can be identified and extracted using a combination of the steps listed above.
Identify the lines that contain the network site tables
Identify the beginning line number in a SQL file for each table belonging to a specific network site ID.
grep -n -E '^-- Table structure' <file-name>.sql | grep -A1 wp_[ID]
In this example, the command is formatted to identify the first line of each table belonging to site ID 2 in a backup file named backup.sql:
$ grep -n -E '^-- Table structure' backup.sql | grep -A1 wp_2 3059:-- Table structure for table `wp_2_a8c_cron_control_jobs` 3094:-- Table structure for table `wp_2_commentmeta` 3121:-- Table structure for table `wp_2_comments` 3163:-- Table structure for table `wp_2_links` 3198:-- Table structure for table `wp_2_options` 3226:-- Table structure for table `wp_2_postmeta` 3254:-- Table structure for table `wp_2_posts` 3303:-- Table structure for table `wp_2_term_relationships` 3329:-- Table structure for table `wp_2_term_taxonomy` 3359:-- Table structure for table `wp_2_termmeta` 3386:-- Table structure for table `wp_2_terms` 3414:-- Table structure for table `wp_a8c_cron_control_jobs`
The example command output indicates that tables belonging to site ID 2 begin on line 3059
, and end at line 3413
(one line before the wp_a8c_cron_control_jobs
table begins).
Extract the network site’s tables with the identified line numbers
Using the beginning and ending line number values, extract the network site’s table to a new SQL file.
sed -n '<beginning>,<ending> p' <file-name>.sql > new-file.sql
Using the output from the command example above, the tables for site ID 2 are extracted from a backup file named backup.sql using the identified beginning line number
and the ending line number 3059
. The tables are then saved to a newly created file named new-file.sql:3413
sed -n '3059
,3413
p' backup.sql > new-file.sql
Validating the structure of SQL files
New SQL files created by extracting tables from a database backup file should be validated before using them for import. As a first step, verify that the new file includes the expected tables from the extraction process.
grep -E '^-- Table structure' <file-name>.sql
Retrieve the line count of a SQL file
Run the wc
command against the newly created SQL file to retrieve the number of lines contained in the file.
wc -l <file-name>.sql
Validate a SQL file with VIP-CLI
Verify that a SQL file is correctly formatted for import with the VIP-CLI command: vip import validate-sql [options]
The output from vip import validate-sql
will report existing file formatting issues as well as a line count. The line count reported by VIP-CLI includes one blank line in a SQL file that the wc
command ignores.
Last updated: August 03, 2023