CODEWITHSUNDEEP
mysql
manhag
manhag

Reputation: 332

mysqldump backup and restore to remote server

How can i use mysqldump to backup and restore database to a remote server?

Both have root access. I am using putty to perform this.

So far I tried the following:

mysqldump -u root -p >z*x311a!@ masdagn_joom15 | mysql \ -u root -p g2154hE6-AsXP --host=207.210.71.26 -C masdagn_joom15temp \g

but it refused

the local password is: >z*x311a!@

the remote password is: g2154hE6-AsXP

Upvotes: 15

Views: 52478

Answers (6)

uzumas
uzumas

Reputation: 678

For a single DB, Taking backup from a remote server is :

mysqldump -u<user> -p<pwd> -h<remote-host> [database-name] > dump.sql

Restore is:

mysql -u<user> -p<pwd> -h<remote-host> [database-name] < dump.sql

more details about options of mysqldump are available here: https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html

Upvotes: 0

Artistan
Artistan

Reputation: 2037

here is what I do for a quick dump to another remote server... assuming that you have setup an ssh key between the 2 servers

  • create file dump-to-server.sh
  • chmod to executable (chmod 0755 dump-to-server.sh)
  • run your sync ./dump-to-server.sh schema_name [email protected]

dump-to-server.sh

\#!/bin/bash

if [[ -z "$1" || -z "$2" ]]; then
  echo "--------- usage ---------";
  echo "./dump-to-server.sh schema_name [email protected]";
  echo "";
else
  mysqldump --opt "$1" | gzip -c | ssh "$2" "gunzip -c | mysql $1"
fi

Upvotes: 0

Amar Pratap
Amar Pratap

Reputation: 1018

[local-server]# mysqldump -u root -prootpswd db | mysql \
                 -u root -ptmppassword --host=remote-server -C db1

[Note: There are two -- (hyphen) in front of host]

Please note that you should first create the db1 database on the remote-server before executing the following command.

Upvotes: 4

Mikkel
Mikkel

Reputation: 1212

Your local password contains the > character, which is interpreted as a redirect character by most shells. As a general rule, it will make your life considerably easier if you keep your MySQL passwords alphanumeric [A-Za-z0-9]. And it will make your system more secure if you avoid publicly posting your passwords.

Upvotes: 1

NebuSoft
NebuSoft

Reputation: 4000

This link provides information on backing up and restoring with mysqldump. It also gives some examples with a remote server.

The important commands from that link being:

backup: 

mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

restore: 

mysql -u root -p[root_password] [database_name] < dumpfilename.sql

Upvotes: 19

grokus
grokus

Reputation: 19092

mysqldump --user=username --password=pwd db_name | bzip2 -c > /backup_dir/db_name.sql.bz2

you can embed this part in a script, afterward you can use FTP to transfer to the other location.

To restore, you can

bzip2 -d db_name.sql.bz2
mysql --user=username --password=pwd db_name < db_name.sql

Upvotes: 2

Related Questions