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.

Nifty.

And a nice example for copy and paste purposes:

ssh #REMOTEUSERNAME#@#HOSTNAME#

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 

exit

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.

Nifty.

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