I had a friend who couldn’t mysqldump a customer’s database — the best guess freenode’s #mysql and I could come up with was that the username provided didn’t have the ability to LOCK tables — so I came up with an alternate method that I thought I’d abstract it and post it here for others to use. All this assumes you can connect to the database from your host…
1) Get database schema
[highlight lang=sh]
for i in `mysql —host=$MYSQLDUMP_HOST —user=$MYSQLDUMP_USER —password=$MYSQLDUMP_PWD $MYSQLDUMP_DATABASENAME -e “SHOW TABLES” | tail -10`; do
mysql —host=$MYSQLDUMP_HOST —user=$MYSQLDUMP_USER —password=$MYSQLDUMP_PWD $MYSQLDUMP_DATABASENAME -e “SHOW CREATE TABLE $i\G”;
done > schema.sql
[/highlight]
2) Dump the table contents as XML files to the local disk. I went with XML files because I had trouble getting this particular data set to dump and then import using the ‘batch’ format MySQL more natively supports.
[highlight lang=sh]
for i in `mysql —host=$MYSQLDUMP_HOST —user=$MYSQLDUMP_USER —password=$MYSQLDUMP_PWD $MYSQLDUMP_DATABASENAME -e “SHOW TABLES” | tail -10`; do
mysql —host=$MYSQLDUMP_HOST —user=$MYSQLDUMP_USER —password=$MYSQLDUMP_PWD $MYSQLDUMP_DATABASENAME —xml -e “SELECT * from $i;” | tee $i.xml;
done
[/highlight] I used ‘tee’ so I could watch the tables being created, in an attempt to gauge how much work was left. You could just as easily replace “| tee” with “>”.
3) Turn the XML files into INSERT statements. I did this with Python and lxml – you could use just about anything that has a decent XML API.
import.py
python import.py > inserts.sql
4) Create the database, run the INSERTs.
[highlight lang=sh]mysql -e “CREATE DATABASE $MYSQLDUMP_DATABASENAME;”
mysql $MYSQLDUMP_DATABASENAME < schema.sql
mysql $MYSQLDUMP_DATABASENAME < inserts.sql[/highlight]
Hope that can help someone else.