How to Batch Import Multiple WordPress MySQL Table Dumps

I was migrating a huge, high traffic WordPress site for a client on Codeable to Kinsta. Unfortunately they didn’t have FTP access or details so I had to get creative. Luckily, they were using VaultPress so I could generate a backup zip to download from there. Much to my surprise, I learned that the database backup was split into separate SQL files for each database table – there were over 150 of them!

I knew I didn’t want to import them manually so I thought I’d use WP-CLI to batch import them all automatically for me using the Linux command line.

This should work as long as you have SSH access on Debian, Ubuntu, CentOS etc.

How to Batch Import WordPress MySQL Table Dumps with or without WP-CLI

The first Linux script for importing all of your WordPress table .sql files is with WP-CLI.

If for some reason WP-CLI isn’t available you can use the version further down.

Importing Multiple WordPress Database Table Dumps with WP-CLI

You should define the absolute path to the WordPress installation, here it is /var/www/wp-bullet.com.

This script assumes you have all of the database table dump files in an sql folder.

# define WordPress path
WPPATH="/var/www/wp-bullet.com"

# loop through all of the 
for DUMP in sql/*.sql;
do
    wp db import ${DUMP} --allow-root --path=${WPPATH}
done

If you would like to know how long it takes to import all of the database tables add the time command before the for loop

# define WordPress path
WPPATH="/var/www/wp-bullet.com"

# extract database host and credentials
time for DUMP in sql/*.sql;
do
    #[ -f "$DUMP" ] || break
    wp db import ${DUMP} --allow-root --path=${WPPATH}
done

The output will look something like this when time is added, I focus on the real parameter

real    39m12.653s
user    2m49.977s
sys     5m2.513s

You can also find out the time taken for each table by adding time before the wp db import command

# define WordPress path
WPPATH="/var/www/wp-bullet.com"

# extract database host and credentials
time for DUMP in sql/*.sql;
do
    #[ -f "$DUMP" ] || break
    time wp db import ${DUMP} --allow-root --path=${WPPATH}
done

Just for fun let’s do it without WP-CLI as well

Importing Multiple WordPress Database Table Dumps without WP-CLI

Here is the script adjusted to work without WP-CLI. We use awk to grab the MySQL database values 🙂

# define WordPress config path
WPCONFIG="/var/www/wp-bullet.com/wp-config.php"

# extract database host and credentials
DBHOST=$(grep DB_HOST "$WPCONFIG" | awk -F ["'"] '{ print $4 }')
DBUSER=$(grep DB_USER "$WPCONFIG" | awk -F ["'"] '{ print $4 }')
DBPASS=$(grep DB_PASSWORD "$WPCONFIG" | awk -F ["'"] '{ print $4 }')
DBNAME=$(grep DB_NAME "$WPCONFIG" | awk -F ["'"] '{ print $4 }')

# processes all sql files in the sql folder e.g. sql/table-name.sql
for DUMP in sql/*.sql;
do
    [ -f "$DUMP" ] || break
    mysql -u ${DBUSER} -p${DBPASS} ${DBNAME} --force < ${DUMP}
done

Adding time to find out how long each table is taking to import

# define WordPress config path
WPCONFIG="/var/www/wp-bullet.com/wp-config.php"

# extract database host and credentials
DBHOST=$(grep DB_HOST "$WPCONFIG" | awk -F ["'"] '{ print $4 }')
DBUSER=$(grep DB_USER "$WPCONFIG" | awk -F ["'"] '{ print $4 }')
DBPASS=$(grep DB_PASSWORD "$WPCONFIG" | awk -F ["'"] '{ print $4 }')
DBNAME=$(grep DB_NAME "$WPCONFIG" | awk -F ["'"] '{ print $4 }')

time for DUMP in sql/*.sql;
do
    time mysql -u ${DBUSER} -p${DBPASS} ${DBNAME} --force < ${DUMP}
done

With either script you will get a long output list, keep an eye out for errors!

Success: Imported from 'sql/wp_cb_contests.sql'.
Success: Imported from 'sql/wp_cb_links.sql'.
Success: Imported from 'sql/wp_cb_prizes.sql'.
Success: Imported from 'sql/wp_cb_referrals.sql'.
Success: Imported from 'sql/wp_cb_user_contests.sql'.
Success: Imported from 'sql/wp_cleantalk_sfw_logs.sql'.
Success: Imported from 'sql/wp_cleantalk_sfw.sql'.
Success: Imported from 'sql/wp_cntctfrm_field.sql'.
Success: Imported from 'sql/wp_commentmeta.sql'.
Success: Imported from 'sql/wp_comments.sql'.
Success: Imported from 'sql/wp_contest_domination_entries.sql'.
Success: Imported from 'sql/wp_contest_domination_submissions.sql'.
Success: Imported from 'sql/wp_email.sql'.
Success: Imported from 'sql/wp_eshop_base_products.sql'.
Success: Imported from 'sql/wp_eshop_countries.sql'.
Success: Imported from 'sql/wp_eshop_discount_codes.sql'.
Success: Imported from 'sql/wp_eshop_download_orders.sql'.
Success: Imported from 'sql/wp_eshop_downloads.sql'.
Success: Imported from 'sql/wp_eshop_emails.sql'.
Success: Imported from 'sql/wp_eshop_option_names.sql'.
Success: Imported from 'sql/wp_eshop_option_sets.sql'.
Success: Imported from 'sql/wp_eshop_order_items.sql'.
Success: Imported from 'sql/wp_eshop_orders.sql'.
Success: Imported from 'sql/wp_eshop_rates.sql'.
Success: Imported from 'sql/wp_eshop_states.sql'.
Success: Imported from 'sql/wp_eshop_stock.sql'.
Success: Imported from 'sql/wp_et_social_stats.sql'.
Success: Imported from 'sql/wp_fbFreshCampaigns.sql'.
Success: Imported from 'sql/wp_fbFreshLinks.sql'.
Success: Imported from 'sql/wp_fbFreshLogs.sql'.
Success: Imported from 'sql/wp_fbFreshOG.sql'.
Success: Imported from 'sql/wp_forum_forums.sql'.
Success: Imported from 'sql/wp_forum_groups.sql'.
Success: Imported from 'sql/wp_forum_posts.sql'.
Success: Imported from 'sql/wp_forum_threads.sql'.
Success: Imported from 'sql/wp_forum_usergroup2user.sql'.
Success: Imported from 'sql/wp_forum_usergroups.sql'.
Success: Imported from 'sql/wp_gallery_galleriesslides.sql'.
Success: Imported from 'sql/wp_gallery_galleries.sql'.
Success: Imported from 'sql/wp_gallery_slides.sql'.
Success: Imported from 'sql/wp_gdsr_data_article.sql'.
Success: Imported from 'sql/wp_gdsr_data_category.sql'.
Success: Imported from 'sql/wp_gdsr_data_comment.sql'.
Success: Imported from 'sql/wp_gdsr_ips.sql'.
Success: Imported from 'sql/wp_gdsr_moderate.sql'.
Success: Imported from 'sql/wp_gdsr_multis_data.sql'.
Success: Imported from 'sql/wp_gdsr_multis.sql'.
Success: Imported from 'sql/wp_gdsr_multis_trend.sql'.
Success: Imported from 'sql/wp_gdsr_multis_values.sql'.
Success: Imported from 'sql/wp_gdsr_templates.sql'.
Success: Imported from 'sql/wp_gdsr_votes_log.sql'.
Success: Imported from 'sql/wp_gdsr_votes_trend.sql'.
Success: Imported from 'sql/wp_google_ads.sql'.
Success: Imported from 'sql/wp_httpbl_log.sql'.
Success: Imported from 'sql/wp_itsec_lockouts.sql'.
Success: Imported from 'sql/wp_itsec_log.sql'.
Success: Imported from 'sql/wp_itsec_logs.sql'.
Success: Imported from 'sql/wp_itsec_temp.sql'.
Success: Imported from 'sql/wp_layerslider.sql'.
Success: Imported from 'sql/wp_links.sql'.
Success: Imported from 'sql/wp_lockdowns.sql'.
Success: Imported from 'sql/wp_login_fails.sql'.
Success: Imported from 'sql/wp_mainwp_stream_context.sql'.
Success: Imported from 'sql/wp_mainwp_stream_meta.sql'.
Success: Imported from 'sql/wp_mainwp_stream.sql'.
Success: Imported from 'sql/wp_mc4wp_log.sql'.
Success: Imported from 'sql/wp_nivoslider4wp.sql'.
ERROR 1062 (23000) at line 3732 in file: 'sql/wp_options.sql': Duplicate entry 'itsec-lock-cron_test_fail_safe' for key 'option_name'
Success: Imported from 'sql/wp_options.sql'.
Success: Imported from 'sql/wp_panoramio.sql'.
Success: Imported from 'sql/wp_pmxe_exports.sql'.
Success: Imported from 'sql/wp_PO_groups.sql'.
Success: Imported from 'sql/wp_PO_post_plugins.sql'.
Success: Imported from 'sql/wp_popshops.sql'.
Success: Imported from 'sql/wp_postmeta.sql'.
Success: Imported from 'sql/wp_posts.sql'.
Success: Imported from 'sql/wp_PO_url_plugins.sql'.
Success: Imported from 'sql/wp_ratings.sql'.

Sources

Ignore MySQL Errors During Import