Mysql Dumping / Uploading a file or files via terminal and SSH

May 11, 2010

Just something I found myself looking up today so that I can do everything from the comfort of my little terminal window.

I wanted to take a backup from a table in one database and up it to another db.

Firstly I dumped the tables on my remote server to my sites webroot.

mysqldump -u root -p database_name table_1 table_2 table_3 > /path/to/outputfile/outputfile.sql

This will prompt you for your password, enter it and it should, providing everything else is correct dump to 'outputfile.sql'.

Now you have your backup residing at your web servers webroot or wherever it is you may have chosen to store it.

This file can now be retrieved either by a FTP client or if your like me then using terminal by either using curl or scp to local.

Curl example:

curl -O

Scp (preferred via ssh):
scp username@server:path_on_server/file path_to_download_to

Once on local you will probably want to move this to your other server via uploading which we will again use Scp for this.

scp path_to_file/file username@server:path_to_upload_to

Once youve got the file on the server which the database you want to import data to resides.
Connect to the server in this case via ssh.
Then restore your file to the database.
mysql -u root -p databasename < /path/toupload/directory/outputfile.sql

This is the general format to remember

mysql -u [username] -p [password] [database_to_restore] < [backupfile]

If its a zipped backup you want to restore simply use the following command

gunzip < outputfile.sql | mysql -u root -p DatabaseName

Also remember you dont have to have the password prompt you each time you can include it in the original command by changing `-p` to `-p password`.

Very basic but will suit most peoples needs.

About me

Hello! I'm David Heward, how are you going? I'm a Senior Devops/Build Engineer, specialising in AWS & Cloud Automation. Based in London. Strong 10+ year background in Software development. Have a read of my blog. Have a look at some of my working projects. Contact me at @davehewy or on Linkedin.