CODEWITHSUNDEEP
mysqlssh
Mild Fuzz
Mild Fuzz

Reputation: 30651

retrieve a mysql database via SSH

I need to copy an entire database from a mysql installation on a remote machine via SSH to my local machines mysql.

I know the SSH and both local and remote MYSQL admin user and password.

Is this enough information, and how is it done?

Upvotes: 2

Views: 10766

Answers (5)

Gilles Quénot
Gilles Quénot

Reputation: 184955

That will dump remote DB in your local MySQL via pipes :

ssh mysql-server "mysqldump --all-databases --quote-names --opt --hex-blob --add-drop-database" | mysql

You should take care about users in mysql.users

Moreover, to avoid typing users and passwords for mysqldump and mysql on local and remote hosts, you can create a file ~/.my.cnf :

[mysql]
user = dba
password = foobar

[mysqldump]
user = dba
password = foobar

See http://dev.mysql.com/doc/refman/5.1/en/option-files.html

Upvotes: 5

I0Result
I0Result

Reputation: 136

From remote server to local machine

 ssh {ssh.user}@{remote_host} \
 'mysqldump -u {remote_dbuser} --password={remote_dbpassword}
 {remote_dbname} | bzip2 -c' \  | bunzip2 -dc | mysql -u {local_dbuser}
 --password={local_dbpassword} -D {local_dbname}

Upvotes: 9

kitti
kitti

Reputation: 14794

Try reading here:

Modified from http://www.cyberciti.biz/tips/howto-copy-mysql-database-remote-server.html - modified because I prefer to use .sql as the extension for SQL files:

Usually you run mysqldump to create a database copy and backups as follows:

$ mysqldump -u user -p db-name > db-name.sql

Copy db-name.out file using sftp/ssh to remote MySQL server:

$ scp db-name.sql [email protected]:/backup

Restore database at remote server (login over ssh):

$ mysql -u user -p db-name < db-name.sql

Basically you'll use mysqldump to generate a dump of your database, copy it to your local machine, then pipe the contents into mysql to regenerate the DB.

You can copy the DB files themselves, rather than using mysqldump, but only if you can shutdown the MySQL service on the remote machine.

Upvotes: 4

Billy Moon
Billy Moon

Reputation: 58521

  1. ssh into the remote machine
  2. make a backup of the database using mysqldump
  3. transfer the file to local machine using scp
  4. restore the database to your local mysql

Upvotes: 0

PFY
PFY

Reputation: 336

I would recommend the Xtrabackup tool by Percona. It has support for hot copying data via SSH and has excelent documentation. Unlike using mysqldump, this will copy all elements of the MySQL instance including user permissions, triggers, replication, etc...

Upvotes: 0

Related Questions