How to Export Large WordPress Databases and Speed Up the Process

by

Anybody who has had to export large WordPress databases will tell you it can often be an arduous task. If the database server crashes while exporting, you have to start over. If phpMyAdmin times out because the PHP handler has a low time out, you have to start over. If you are using the command line and sqldump and your SSH session crashes, you have to start over (use screen next time!). In this post I will outline the flags for mysqldump that can speed up the database dumping process. I will also show how to export the WordPress database tables in separate sql dump files with some handy scripts in versions that include WP-CLI but also one that is pure bash and mysqldump.

Exporting Large WordPress Databases

Here are the following sections for this post

  • mysqldump flag settings can be used with and without WP-CLI
  • Exporting each table as separate sql file can also be done with and without WP-CLI
  • Scripting it all with WP-CLI
  • Scripting it all with mysqldump

mysqldump Flag Settings

These are the flags that can speed up the database export processs --single-transaction --quick --lock-tables=false as usual, thanks to Stackexchange's helpful posts here and here.

WP-CLI will let us pass these flags in our wp db export command like so

wp db export /tmp/wordpress-dump.sql --all-tablespaces --single-transaction --quick --lock-tables=false

Let's do the same thing without WP-CLI and only use mysqldump

Mysqldump Script without WP-CLI

WP-CLI automates a lot of this process for us like extracting the database name, user and password. We can use grep to extract the information though!

Get database name

grep DB_NAME wp-config.php | awk -F "'" '{print $4}'

Get database user

grep DB_USER wp-config.php | awk -F "'" '{print $4}'

get database password

grep DB_PASS wp-config.php | awk -F "'" '{print $4}'

Now we can build the mysqldump command

mysqldump -u DBUSER -p --single-transaction --quick --lock-tables=false DBNAME > BACKUP-NAME.sql

We can even add gzip but beware this could take a long time on huge WordPress databases

mysqldump -u DBUSER -p --single-transaction --quick --lock-tables=false DBNAME | gzip > BACKUP-NAME.sql.gz

put it all together

DBNAME=$(grep DB_NAME wp-config.php | awk -F "'" '{print $4}')
DBUSER=$(grep DB_USER wp-config.php | awk -F "'" '{print $4}')
DBPASS=$(grep DB_PASS wp-config.php | awk -F "'" '{print $4}')
mysqldump -u ${DBUSER} -p${DBPASS} --single-transaction --quick --lock-tables=false ${DBNAME} > BACKUP-NAME.sql

let's focus on exporting each table as a separate SQL file now

Export Large WordPress Database into Separate Table Files

In these sections we will look at dumping the database as separate sql files.

Export Large WordPress Database into Split sqldump Table Files with WP-CLI

In this section we will take it up a notch and export each table as a separate files and combine this with the mysqldump flags for speeding up the export

wp db query "SHOW TABLES" --skip-column-names --allow-root

WP-CLI actually has a specific command for listing tables

wp db tables --all-tables --allow-root

Now we can put it together in a larger script

# set WP-CLI flags
WPFLAGS="--allow-root"

# define path to the database dumps
DBSTORE="/tmp"
# get the name of the database
DBNAME=$(wp config get DB_NAME ${WPFLAGS})

# list all of the tables regardless of database prefix
TABLELIST=($(wp db tables --all-tables ${WPFLAGS}))

# create the temporary directory for storing the dumps
mkdir -p ${DBSTORE}/${DBNAME}

# loop through tables and export, log details to /tmp/mysqlexport-<database>.txt
for TABLE in ${TABLELIST[@]}
do
    # export the table
    wp db export ${DBSTORE}/${DBNAME}/${TABLE}.sql --tables=${TABLE} ${WPFLAGS} | tee /dev/stderr
done > /tmp/mysqlexport-${DBNAME}.txt

If the process crashes for any reason, you can define the tables you wish to re-export manually by defining them in the TABLELIST array

# set WP-CLI flags
WPFLAGS="--allow-root"

# define path to the database dumps without trailing slash
DBSTORE="/tmp"
# get the name of the database
DBNAME=$(wp config get DB_NAME ${WPFLAGS})

# list all of the tables regardless of database prefix
TABLELIST=(wp_posts wp_postmeta)

# create the temporary directory for storing the dumps
mkdir -p ${DBSTORE}/${DBNAME}

# loop through tables and export, log details to /tmp/mysqlexport-<database>.txt
for TABLE in ${TABLELIST[@]}
do
    # export the table
    wp db export ${DBSTORE}/${DBNAME}/${TABLE}.sql --tables=${TABLE} ${WPFLAGS} | tee /dev/stderr
done > /tmp/mysqlexport-${DBNAME}.txt

Remember I have a script for helping batch import these dumps as well.

Export Large WordPress Database into Split sqldump Table Files without WP-CLI

Need DB_USER, DB_PASSWORD and DB_NAME

grep DB_NAME wp-config.php | awk -F "'" '{print $4}'

Get database user

grep DB_USER wp-config.php | awk -F "'" '{print $4}'

get database password

grep DB_PASS wp-config.php | awk -F "'" '{print $4}'

Now we can put it all together into the command to list the tables

mysql -u DB_USER -p"DB_PASSWORD" -e "USE DB_NAME; SHOW TABLES';" --skip-column-names

Let's combine it into a script to dump all the tables as separate sql dumps.

# define path to the database dumps without trailing slash
DBSTORE="/tmp"
# get the wp-config.php variables
DBNAME=$(grep DB_NAME wp-config.php | awk -F "'" '{print $4}')
DBUSER=$(grep DB_USER wp-config.php | awk -F "'" '{print $4}')
DBPASS=$(grep DB_PASS wp-config.php | awk -F "'" '{print $4}')

# create the temporary directory for storing the dumps
mkdir -p ${DBSTORE}/${DBNAME}

# create array of the database tables
TABLELIST=($(mysql -u ${DBUSER} -p"${DBPASS}" -e "USE ${DBNAME}; SHOW TABLES;" --skip-column-names))

# loop through tables and export, log details to /tmp/mysqlexport-<database>.txt
for TABLE in ${TABLELIST[@]}
do
    # export the table
    >&2 echo "Exporting ${TABLE}" 
    mysqldump -u ${DBUSER} -p${DBPASS} --single-transaction --quick --lock-tables=false ${DBNAME} ${TABLE} > ${DBSTORE}/${DBNAME}/${TABLE}.sql
    >&2 echo "Exported ${TABLE}"
done > /tmp/mysqlexport-${DBNAME}.txt

Similarly to the WP-CLI version, we can specify the tables to export in the array in case some crash or timeout during the export process

# define path to the database dumps without trailing slash
DBSTORE="/tmp"
# get the wp-config.php variables
DBNAME=$(grep DB_NAME wp-config.php | awk -F "'" '{print $4}')
DBUSER=$(grep DB_USER wp-config.php | awk -F "'" '{print $4}')
DBPASS=$(grep DB_PASS wp-config.php | awk -F "'" '{print $4}')

# create the temporary directory for storing the dumps
mkdir -p ${DBSTORE}/${DBNAME}

# create array of the database tables
TABLELIST=(wp_posts wp_postmeta)

# loop through tables and export, log details to /tmp/mysqlexport-<database>.txt
for TABLE in ${TABLELIST[@]}
do
    # export the table
    >&2 echo "Exporting ${TABLE}" 
    mysqldump -u ${DBUSER} -p${DBPASS} --single-transaction --quick --lock-tables=false ${DBNAME} ${TABLE} > ${DBSTORE}/${DBNAME}/${TABLE}.sql
    >&2 echo "Exported ${TABLE}"
done > /tmp/mysqlexport-${DBNAME}.txt

If you want to import the database dump files automatically I have a post about that too.

Sources

How to mysqldump Specific Tables
SQLDump File Splitter
Slow down mysqldump to control load on server
mysqldump out of resources
Suppress awk warnings
Output echo to stderr