A civic-minded citizen seeking the singularity

An entry

Copying MySQL Data and/or Databases between machines

Date: 2010-09-02
Status: draft
Tags: commands data mysql

While I was in Washington DC, I committed to Ubuntu (10.04), switching from a Windows XP setup. I suppose it was long overdue, but being immersed in Ubuntu for 10 weeks was necessary to appreciate its open-source-yness and come to love it and its tools.

I basically had 2 1/2 months of setup effort and time invested into my laptop while on the road, but when I returned to my dual monitor setup in my home office, I had to make the switch again, from Windows XP over to Ubuntu, along with all my files, setups, projects, and data.

Early on in the transition, I needed to copy my MySQL tables between machines, which can done by exporting data to a file, copying the file to another machine, then importing it. The commands below got the job done for me. MySQL Export 1 Table - from laptop

The general syntax is:

mysqldump -u <username> -p<password> -h <host> <database name> > <filename to export to>

An example of the command is:

mysqldump -u ryan -pexamplepassword -h localhost database_name > database.sql

Because I'm storing "blob" data in my database, I also found the following command helpful. The --all-database option exports all databases, and thus does not require a to be passed. The --hex-blob option ensures the blob data will be readable for import. MySQL Export All Databases
mysqldump -u ryan -pexamplepassword -h localhost --all-databases --hex-blob > entire_database.sql

MySQL Import to a Table - to my Desktop

mysql -u <username> -p<password> -h <host> < <filename to export from>
mysql -u ryan -ppassword -h localhost < database.sql