MySQL mysqldump 备份和恢复到远程服务器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2768598/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysqldump backup and restore to remote server
提问by manhag
How can i use mysqldump to backup and restore database to a remote server?
如何使用 mysqldump 将数据库备份和恢复到远程服务器？
Both have root access. I am using putty to perform this.
两者都有root访问权限。我正在使用腻子来执行此操作。
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!@
本地密码为：>z*x311a!@
the remote password is: g2154hE6-AsXP
远程密码为：g2154hE6-AsXP
回答by NebuSoft
This linkprovides information on backing up and restoring with mysqldump. It also gives some examples with a remote server.
此链接提供有关使用 mysqldump 进行备份和恢复的信息。它还提供了一些远程服务器的示例。
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
回答by grokus
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.
您可以将这部分嵌入到脚本中，然后您可以使用 FTP 传输到其他位置。
To restore, you can
要恢复，您可以
bzip2 -d db_name.sql.bz2
mysql --user=username --password=pwd db_name < db_name.sql
回答by Amar Pratap
[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]
【注：host前面有两个--（连字符）】
Please note that you should first create the db1 database on the remote-server before executing the following command.
请注意，在执行以下命令之前，您应该先在远程服务器上创建 db1 数据库。
回答by Mikkel
Your local password contains the >character, which is interpreted as a redirect characterby 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.
您的本地密码包含该>字符，大多数 shell将其解释为重定向字符。作为一般规则，如果您将 MySQL 密码保留为 alphanumeric ，它将使您的生活变得更加轻松[A-Za-z0-9]。如果您避免公开发布您的密码，它将使您的系统更加安全。
回答by Artistan
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
这是我为快速转储到另一台远程服务器所做的工作……假设您已在两台服务器之间设置了 ssh 密钥
- 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]
- 创建文件转储到 server.sh
- chmod 到可执行文件 (
chmod 0755 dump-to-server.sh) - 运行你的同步
./dump-to-server.sh schema_name [email protected]
dump-to-server.sh
转储到 server.sh
\#!/bin/bash
if [[ -z "" || -z "" ]]; then
echo "--------- usage ---------";
echo "./dump-to-server.sh schema_name [email protected]";
echo "";
else
mysqldump --opt "" | gzip -c | ssh "" "gunzip -c | mysql "
fi
