Copying MySQL Data and/or Databases between machines
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
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