如何从远程机器使用 MySQL 转储
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8444108/
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
How to use MySQL dump from a remote machine
提问by Harsha
How can I backup a mysql database which is running on a remote server, I need to store the back up file in the local pc.
如何备份在远程服务器上运行的 mysql 数据库，我需要将备份文件存储在本地 PC 中。
回答by CloudyMarble
回答by Ian Jamieson
Have you got access to SSH?
您可以访问 SSH 吗？
You can use this command in shell to backup an entire database:
您可以在 shell 中使用此命令来备份整个数据库：
mysqldump -u [username] -p[password] [databasename] > [filename.sql]
This is actually one command followed by the >operator, which says, "take the output of the previous command and store it in this file."
这实际上是>操作符后面的一个命令，它说，“获取前一个命令的输出并将其存储在这个文件中”。
Note: The lack of a space between -p and the mysql password is not a typo. However, if you leave the -pflag present, but the actual password blank then you will be prompted for your password. Sometimes this is recommended to keep passwords out of your bash history.
注意： -p 和 mysql 密码之间缺少空格不是拼写错误。但是，如果您保留该-p标志，但实际密码为空，则系统将提示您输入密码。有时建议这样做以将密码排除在 bash 历史记录之外。
回答by datasn.io
No one mentions anything about the --single-transactionoption. People should use it by default for InnoDB tables to ensure data consistency. In this case:
没有人提到有关--single-transaction选项的任何内容。人们应该默认为 InnoDB 表使用它以确保数据一致性。在这种情况下：
mysqldump --single-transaction -h [remoteserver.com] -u [username] -p [password] [yourdatabase] > [dump_file.sql]
This makes sure the dump is run in a single transaction that's isolated from the others, preventing backup of a partial transaction.
这可确保转储在与其他事务隔离的单个事务中运行，从而防止备份部分事务。
For instance, consider you have a game server where people can purchase gears with their account credits. There are essentially 2 operations against the database:
例如，假设您有一个游戏服务器，人们可以在其中使用他们的帐户积分购买装备。对数据库基本上有 2 个操作：
- Deduct the amount from their credits
- Add the gear to their arsenal
- 从他们的信用中扣除金额
- 将装备添加到他们的武器库中
Now if the dump happens in between these operations, the next time you restore the backup would result in the user losing the purchased item, because the second operation isn't dumped in the SQL dump file.
现在，如果转储发生在这些操作之间，下次您恢复备份时将导致用户丢失购买的项目，因为第二个操作不会转储到 SQL 转储文件中。
While it's just an option, there are basically not much of a reason why you don't use this option with mysqldump.
虽然它只是一个选项，但基本上没有太多理由不将这个选项与 mysqldump 一起使用。
回答by Unnawut
This topic shows up on the first page of my google result, so here's a little useful tip for new comers.
这个主题出现在我的谷歌结果的第一页上，所以这里有一些对新人有用的提示。
You could also dump the sql and gzip it in one line:
您还可以转储 sql 并将其压缩在一行中：
mysqldump -u [username] -p[password] [database_name] | gzip > [filename.sql.gz]
回答by Unnawut
mysqldump -h [domain name/ip] -u [username] -p[password] [databasename] > [filename.sql]
回答by jsibs
Tried all the combinations here, but this worked for me:
在这里尝试了所有组合，但这对我有用：
mysqldump -u root -p --default-character-set=utf8mb4 [DATABASE TO BE COPIED NAME] > [NEW DATABASE NAME]
回答by Nico
This is how you would restore a backup after you successfully backup your .sql file
这是成功备份 .sql 文件后恢复备份的方式
mysql -u [username] [databasename]
And choose your sql file with this command:
并使用以下命令选择您的 sql 文件：
source MY-BACKED-UP-DATABASE-FILE.sql
