如何从本地机器远程备份MySQL数据库

Question

我需要在远程服务器上进行数据库的mysqldump，但是该服务器未安装mysqldump。 我想使用我的本地机器上的mysqldump连接到远程数据库并在本地执行dump操作。

我尝试创建ssh隧道，然后进行转储，但这似乎没有起作用。 我尝试过：

ssh -f -L3310:remote.server:3306 [e-mail] -N

隧道已成功创建。如果我执行以下操作：

telnet localhost 3310

我得到了一些信息，其中显示了正确的 MySQL 服务器版本。然而，执行以下操作似乎会尝试进行本地连接。

mysqldump -P 3310 -h localhost -u mysql_user -p database_name table_name

- Mauritz Hansen

---

由于这个问题更多涉及行政管理而非编程，我建议您向 http://serverfault.com/ 的专业人士寻求更好的帮助。 - Piskvor left the building

请查看MSQL WorkBench 5.2.22。它可以轻松地让您完成此操作。 - Gary

请注意：要转储远程 MySQL 服务器，请务必包含 --host=sqlserver.host.name --port=3306 - ro0ter

我投票关闭此问题，因为它属于DBA。 - user207421

Answer 1

我在serverfault上还没有看到这个答案，而且答案很简单：

改为：

ssh -f -L3310:remote.server:3306 [e-mail] -N

收件人：

ssh -f -L3310:localhost:3306 [e-mail] -N

并进行更改：

mysqldump -P 3310 -h localhost -u mysql_user -p database_name table_name

mysqldump -P 3310 -h 127.0.0.1 -u mysql_user -p database_name table_name

（不要使用localhost，它是其中一种“特殊含义”的无聊东西，可能是通过套接字而不是端口连接）

编辑：好吧，具体来说，如果主机设置为 localhost ，则假定配置的（或默认的） --socket 选项。有关搜索/使用哪个选项文件，请参见手册。在Windows下，这可以是一个命名管道。

- Wrikken

---

注意：localhost 经常默认为 ::1 IPv6 地址，而不是 127.0.0.1。 - polkovnikov.ph

“-P是做什么的？-P是端口，而-p则是提示输入密码。” - Jeshizaemon

“P” 的确是端口的意思，就像你所说的那样。根据问题中的提示，在回答中已经完成了这一步骤。如果需要，可以将其删除，并将 SSH 隧道中的 3310 替换为 3306。 - Wrikken

Answer 2

可以在本地调用 mysqldump 来备份远程服务器。

以下是我成功使用的示例：

mysqldump -h hostname-of-the-server -u mysql_user -p database_name > file.sql

我按照 文档 中的连接选项使用了 mysqldump。

- Ondrej Burkert

---

以后不再需要先打开SSH隧道了。+1 - Abhay Maurya

这非常危险，需要将MySQL端口开放给公众访问，这会使其易受机器人暴力破解攻击。请注意安全风险。 - louffi

使用VPN如何？或者从你通过SSH登录并可以访问数据库机器的那台机器中转存储数据？端口不需要公开。 - Ondrej Burkert

这里需要记住的是，-p 是用于密码参数而不是数据库名称。但是将密码以明文形式存储是不安全的，因此添加 -p 表示您将在登录时被提示输入密码。也许只有我觉得，mysql 和 mysql dump 语法在命令行参数方面从来都不是那么直接明了。 - fIwJlxSzApHEZIl

如果你无法远程访问 MYSQL，请执行以下命令: ssh root@ipaddress "mysqldump -u dbuser -p dbname | gzip -9" > dblocal.sql.gz - FooBar

Answer 3

使用SSL从远程服务器导出mysqldump

1- SSL安全性

192.168.0.101 - 远程服务器

192.168.0.102 - 本地服务器

远程服务器

CREATE USER 'backup_remote_2'@'192.168.0.102' IDENTIFIED WITH caching_sha2_password BY '3333333' REQUIRE SSL;

GRANT ALL PRIVILEGES ON *.* TO 'backup_remote_2'@'192.168.0.102';

FLUSH PRIVILEGES;

本地服务器

sudo /usr/local/mysql/bin/mysqldump \
 --databases test_1 \
 --host=192.168.0.101 \
 --user=backup_remote_2 \
 --password=3333333 \
 --master-data \
 --set-gtid-purged \
 --events \
 --triggers \
 --routines \
 --verbose \
 --ssl-mode=REQUIRED \
 --result-file=/home/db_1.sql

2 - 使用SSL确保安全 (要求X509)

192.168.0.101 - 远程服务器

192.168.0.102 - 本地服务器

远程服务器

CREATE USER 'backup_remote'@'192.168.0.102' IDENTIFIED WITH caching_sha2_password BY '1111111' REQUIRE X509;

GRANT ALL PRIVILEGES ON *.* TO 'backup_remote'@'192.168.0.102';

FLUSH PRIVILEGES;

本地服务器

sudo /usr/local/mysql/bin/mysqldump \
 --databases test_1 \
 --host=192.168.0.101 \
 --user=backup_remote \
 --password=1111111 \
 --events \
 --triggers \
 --routines \
 --verbose \
 --ssl-mode=VERIFY_CA \
 --ssl-ca=/usr/local/mysql/data/ssl/ca.pem \
 --ssl-cert=/usr/local/mysql/data/ssl/client-cert.pem \
 --ssl-key=/usr/local/mysql/data/ssl/client-key.pem \
 --result-file=/home/db_name.sql

---

[注]

关于本地服务器

/usr/local/mysql/data/ssl/

-rw------- 1 mysql mysql 1.7K Apr 16 22:28 ca-key.pem
-rw-r--r-- 1 mysql mysql 1.1K Apr 16 22:28 ca.pem
-rw-r--r-- 1 mysql mysql 1.1K Apr 16 22:28 client-cert.pem
-rw------- 1 mysql mysql 1.7K Apr 16 22:28 client-key.pem

从远程服务器复制这些文件（需要X509）或如果没有SSL（不需要X509），则不要复制。

---

在远程服务器上：

/usr/local/mysql/data/

-rw------- 1 mysql mysql 1.7K Apr 16 22:28  ca-key.pem
-rw-r--r-- 1 mysql mysql 1.1K Apr 16 22:28  ca.pem
-rw-r--r-- 1 mysql mysql 1.1K Apr 16 22:28  client-cert.pem
-rw------- 1 mysql mysql 1.7K Apr 16 22:28  client-key.pem
-rw------- 1 mysql mysql 1.7K Apr 16 22:28  private_key.pem
-rw-r--r-- 1 mysql mysql  451 Apr 16 22:28  public_key.pem
-rw-r--r-- 1 mysql mysql 1.1K Apr 16 22:28  server-cert.pem
-rw------- 1 mysql mysql 1.7K Apr 16 22:28  server-key.pem

my.cnf

[mysqld]
# SSL
ssl_ca=/usr/local/mysql/data/ca.pem
ssl_cert=/usr/local/mysql/data/server-cert.pem
ssl_key=/usr/local/mysql/data/server-key.pem

增强密码安全

https://dev.mysql.com/doc/refman/8.0/en/password-security-user.html

- Юрий Светлов

Answer 4

基于这个页面： 比较两个MySQL数据库 我进行了修改，使您可以在不同的主机上使用ddbb。

#!/bin/sh

echo "з”Ёжі•: dbdiff [user1:pass1@dbname1:host] [user2:pass2@dbname2:host] [ignore_table1:ignore_table2...]"

dump () { up=${1%%@*}; down=${1##*@}; user=${up%%:*}; pass=${up##*:}; dbname=${down%%:*}; host=${down##*:}; mysqldump --opt --compact --skip-extended-insert -u $user -p$pass $dbname -h $host $table > $2 }

rm -f /tmp/db.diff

# жЇ”иѕѓ up=${1%%@*}; down=${1##*@}; user=${up%%:*}; pass=${up##*:}; dbname=${down%%:*}; host=${down##*:}; for table in `mysql -u $user -p$pass $dbname -h $host -N -e "show tables" --batch`; do if [ "`echo $3 | grep $table`" = "" ]; then echo "жЇ”иѕѓ '$table'..." dump $1 /tmp/file1.sql dump $2 /tmp/file2.sql diff -up /tmp/file1.sql /tmp/file2.sql >> /tmp/db.diff else echo "еїЅз•Ґ '$table'..." fi done less /tmp/db.diff rm -f /tmp/file1.sql /tmp/file2.sql

- Eduard Pertíñez