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:
    <br />
    $ 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<br />
    
  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.
    <br />
    $ sed -i '' -e'/^CREATE DATABASE /d' /path/to/dumpfile.sql<br />
    $ sed -i '' -e'/^USE /d' /path/to/dumpfile.sql<br />
    
  3. Get the dumpfile to your local machine, and import:
    <br />
    $ mysql -u [username] -p -e "create database foo"<br />
    $ mysql -u [username] -p foo < ~/path/to/local/dumpfile.sql
    
    (or whatever technique you use for mysql imports) (don't know why my code formatter is converting < to &lt; but you get the idea).

One thought on “Convert multi-db WordPress mysqldump to single-db

  1. http://discovery.info/

    Apple has already had some presence in the car, but when they today launches CarPlay along with a number of the world’s largest car manufacturers, they show that they want better conditions for cell phones in the car. CarPlay are of the company characterized as “the smart, safe and fun way to use your iPhone in the car,” and will primarily make it easier to call, navigate through Apple’s mapping service, listen to music and access text messages and similar.There are two ways to control CarPlay : directly from the vehicle’s info screen, or you can hold down the button for voice control on the steering wheel. The last of them enables Apple’s Siri service, which one can use for everything from asking about the weather to call certain people. Mercedes-Benz, Ferrari and Volvo will during this week’s auto show in Geneva show off their first cars with CarPlay integrated, but Apple has been joined by virtually all major automakers, including Ford, Nissan, Honda, General Motors, BMW, and Toyota. It is therefore every reason to believe that CarPlay is going to get some influence over the next few years.
    http://discovery.info/

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *