CODEWITHSUNDEEP
mysqlruby-on-railsssh
Nadiya
Nadiya

Reputation: 1527

mysqldump via SSH to local computer

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.

Upvotes: 40

Views: 66862

Answers (4)

Ivan
Ivan

Reputation: 319

An alternative way to backup via SSH is to create an SSH tunnel, like this:

ssh -f -N -L 3307:localhost:3306 [email protected]

here, -L is used to specify the ports (in the format port:host:hostport) to create the tunnel; the -f option backgrounds ssh; and -N means no command execution, just forward ports

after that you can connect to the database as if it were a local server

mysqldump -u user -ppassword -h 127.0.0.1 -P 3307 --all-databases > backup.sql

note the port number -P matches the one specified earlier

Run a restore process is even easier

mysql -h 127.0.0.1 -u user -ppassword < backup.sql

This approach can be convenient when you don’t want to put one command into another (for example, due to confusion with the quotes).

Examples with mysql backup via SSH connection:

Access Your Database Remotely Through an SSH Tunnel

Over SSH – Remote MySQL Backup

Upvotes: 3

Henrik
Henrik

Reputation: 53

I couldn't get the other ones to work.

This is the solution I found for linux:

ssh username@ipadress "mysqldump -u USERNAME -pPASSWORD DB_NAME " > ~/dump.sql

This will prompt you to enter a password for your ssh server, and then dumps the database to the specified location on the SSH server.

Upvotes: 3

sarathkm
sarathkm

Reputation: 1426

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

Upvotes: 112

Nadiya
Nadiya

Reputation: 1527

  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

Upvotes: 4

Related Questions