mysqldump via SSH to local computer

Question

I have an SSH access to production server of the Rails app.

I want to make a mysqldump of production database to my Mac. Please help me to achieve this.

Answer 1

Direct method to dump mysql data from remote server to your local computer is:

ssh root@ipaddress "mysqldump -u dbuser -p dbname | gzip -9" > dblocal.sql.gz 

Or

ssh -l root ipaddress "mysqldump -u dbuser -p dbname | gzip -9" > dblocal.sql.gz

Both command does the same work.

If you have password for ssh and database access there will two prompt for password or if you have no password for ssh then you will be asked to enter you database password.

Similarly, if you are using key from aws or cloud other service you can incorporate the key in the command as:

ssh -i key.pem root@ipaddress "mysqldump -u dbuser -p dbname | gzip -9" > dblocal.sql.gz

Answer 2

1. Connect to server via ssh: ssh remote_username@remote_host
2. Go to 'current' folder
3. Make a dump: mysqldump -u username -ppassword -h host database > dump.sql
4. Disconnect from server
5. Copy a dump.sql file to local computer: scp remote_username@remote_host:/path/to/dump.sql /Users/YourName/Documents/dump.sql
6. Connect to server via ssh again and go to 'current' folder
7. Remove dump.sql file: rm dump.sql