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 -O http://wedpathtoserver.com/outputfile.sql
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.