Tag Archives: sed

Convert multi-db WordPress mysqldump to single-db

On a number of client sites, I use HyperDB or SharDB to spread a WordPress Multisite installation across multiple databases on a single server. However, in my local dev environments, it’s annoying to have thousands of databases. So I use the following technique to create a copy of the remote site that operates in a single database locally.

  1. Use mysqldump to get a backup file. The following command ensures that you don’t pull in information_schema or any other unrelated databases; you can add other DBs to ignore to the NOT IN list:
    [code language=”bash”]
    $ mysql -u [username] -p -B -N -e “SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME NOT IN (‘mysql’,’tmp’,’innodb’,’information_schema’,’performance_schema’)” | xargs mysqldump -u [username] -p –add-drop-table –skip-lock-tables –quick –extended-insert –result-file=[path/to/your/dumpfile.sql] –databases
    [/code]
  2. Use sed to remove all the ‘CREATE DATABASE’ and ‘USE’ lines in the dumpfile. This prevents the multiple databases from being created when importing locally.
    [code language=”bash”]
    $ sed -i ” -e’/^CREATE DATABASE /d’ /path/to/dumpfile.sql
    $ sed -i ” -e’/^USE /d’ /path/to/dumpfile.sql
    [/code]
  3. Get the dumpfile to your local machine, and import:
    [code language=”bash”]
    $ mysql -u [username] -p -e “create database foo”
    $ mysql -u [username] -p foo < ~/path/to/local/dumpfile.sql [/code] (or whatever technique you use for mysql imports) (don't know why my code formatter is converting < to &lt; but you get the idea).