Ok so now that we have our secure backup location, it's time to actually grab everything from the database. The first command will allow us to securely communicate with the remote server over SSH. If we didn't do this, all of the website traffic would be vulnerable to interception since it would be unencrypted in transit (with the exception of the user passwords which are stored encrypted already).
ssh -f -L3310:localhost:3306 user@remote.sever -NThe above command will forward traffic directed at local port 3310 to the remote machine's port 3306 which is the default port for a mysql server. To make sure this works, you'll want to make sure the port is actually open for communication on port 3306, preferably restricted to just the IP address of your local machine for better security. If you're using uncomplicated firewall, you can achieve this with the following command (replacing the 1's with your own IP address):
sudo ufw allow from 111.111.11.111 to any port 3306And now we're ready to actually dump the database. Fill in your own credentials where appropriate:
mysqldump -P 3310 -h 127.0.0.1 -u mysql_user -p database_name table_name > backup_file.sqlYou can leave off the name of the table if you want to dump the entire database. This will generate all the SQL statements needed to recreate the database and write them out to the file backup_file.sql.
Import the SQL Dump
Now on my local machine, I want to import the dump into a new database I've created:
create database website_bak;Then I give my non-root user permissions to write to all of the tables in the new database:
grant all privileges on website_bak . * to 'user'@'localhost';Now we can quit back out and run the command to import the dump file into the database:
mysql -u user -p website_bak < /path/to/backup_file.sql