Tag Archives: sql dump

Ubuntu Terminal: Efficient Way to Copy Across a MySQL Database from One Server to Another CodeUnit 23 MAY 2011

I often need to pull down a copy of a live MySQL database instance for development work on my local machine. Because my access to bandwidth is limited, I need to come up with the most efficient way of achieving this, and my method for achieving this is chronicled below – mainly so that I can refer to it when I forget!

Anyway, the process is simple enough to explain. First, we ssh into the target server and we use mysqldump to create a SQL dump text file of the required MySQL database. Next, we compress it using the powerful 7-Zip to create a nice and compact 7z file. Exit ssh. To pull it down from the server, we use plain old scp and do a secure copy down to our local machine. Next we uncompress the newly copied 7z archive and once extracted, import it into our local MySQL server using the mysql command.


And a nice example for copy and paste purposes:


mysqldump -u #USERNAME# -p#PASSWORD# -c --add-drop-table --all --quick #DATABASE# > dump.sql 

7za a -t7z -m0=lzma -mx=9 -mfb=64 -md=32m -ms=on dump.7z dump.sql 


scp -v -P #PORT# #REMOTEUSERNAME#@#HOSTNAME#:/home/server/dump.7z /home/craig/dumpscp.7z 

7za e /home/craig/dumpscp.7z 

mysql -u #USERNAME# -p#PASSWORD# database < /home/craig/dump.sql

Ubuntu: Import a SQL Dump into MySQL CodeUnit 09 JAN 2011

The quickest way to import a properly generated SQL dump containing structure and table definitions plus data into a MySQL database is to simply pipe the file directly to the powerful mysql command function via a terminal window.

The command would look something like this:

mysql --verbose -u username -ppassword mydatabase < sqldump.sql

Although the --verbose switch is technically not needed, it does make it easier to follow the progress of the resulting import. The -u and -p switches indicates the account with which to access the MySQL serve (Note the lack of space between -p and the password). Finally "mydatabase" should be replaced with the name of the database to effect.


Ubuntu Terminal: How to Quickly Create a SQL Dump File from a MySQL Database CodeUnit 05 JUL 2010

Backing up your MySQL database or generating a copy of it to shift around is quite a simple affair thanks to the powerful mysqldump command that ships with MySQL.

To generate a backup sqldump, simply execute:

mysqldump -h localhost -u [MySQL user, e.g. root] -p[database password] -c --add-drop-table --add-locks --all --quick --lock-tables [name of the database] > sqldump.sql

Note the lack of a space between -p and the password! Obviously if you don’t have a password assigned, simply omit the -p switch.

And that is it, all done! :)

Note, restoring a database from a mysqldump is as simple as: mysql -u [MySQL user, e.g. root] -p[database password] < sqldump.sql