MySQL 如何从本地机器mysqldump远程数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2989724/
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 mysqldump remote db from local machine
提问by Mauritz Hansen
I need to do a mysqldump of a database on a remote server, but the server does not have mysqldump installed. I would like to use the mysqldump on my machine to connect to the remote database and do the dump on my machine.
我需要在远程服务器上对数据库进行 mysqldump，但该服务器没有安装 mysqldump。我想在我的机器上使用 mysqldump 连接到远程数据库并在我的机器上进行转储。
I have tried to create an ssh tunnel and then do the dump, but this does not seem to work. I tried:
我试图创建一个 ssh 隧道，然后进行转储，但这似乎不起作用。我试过：
ssh -f -L3310:remote.server:3306 [email protected] -N
The tunnel is created with success. If I do
隧道创建成功。如果我做
telnet localhost 3310
I get some blurb which shows the correct server mysql version. However, doing the following seems to try to connect locally
我得到了一些显示正确服务器 mysql 版本的简介。但是，执行以下操作似乎尝试在本地连接
mysqldump -P 3310 -h localhost -u mysql_user -p database_name table_name
回答by Wrikken
As I haven't seen it at serverfault yet, and the answer is quite simple:
因为我还没有在 serverfault 上看到它，答案很简单：
Change:
改变：
ssh -f -L3310:remote.server:3306 [email protected] -N
To:
到：
ssh -f -L3310:localhost:3306 [email protected] -N
And change:
并改变：
mysqldump -P 3310 -h localhost -u mysql_user -p database_name table_name
To:
到：
mysqldump -P 3310 -h 127.0.0.1 -u mysql_user -p database_name table_name
(do not use localhost, it's one of these 'special meaning' nonsense that probably connects by socket rather then by port)
（不要使用 localhost，这是可能通过套接字而不是通过端口连接的这些“特殊含义”废话之一）
edit: well, to elaborate: if host is set to localhost, a configured (or default) --socketoption is assumed. See the manualfor which option files are sought / used. Under Windows, this can be a named pipe.
编辑：好吧，详细说明：如果主机设置为localhost，--socket则假定已配置（或默认）选项。请参阅寻找/使用选项文件的手册。在 Windows 下，这可以是命名管道。
回答by Ondrej Burkert
One can invoke mysqldump locally against a remote server.
可以针对远程服务器在本地调用 mysqldump。
Example that worked for me:
对我有用的例子：
mysqldump -h hostname-of-the-server -u mysql_user -p database_name > file.sql
I followed the mysqldump documentationon connection options.
我遵循了有关连接选项的 mysqldump文档。
回答by Eduard Pertí?ez
Bassed on this page here:
基于此页面：
I modified it so you can use ddbb in diferent hosts.
我修改了它，以便您可以在不同的主机中使用 ddbb。
#!/bin/sh
echo "Usage: 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 >
}
rm -f /tmp/db.diff
# Compare
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 | grep $table`" = "" ]; then
echo "Comparing '$table'..."
dump /tmp/file1.sql
dump /tmp/file2.sql
diff -up /tmp/file1.sql /tmp/file2.sql >> /tmp/db.diff
else
echo "Ignored '$table'..."
fi
done
less /tmp/db.diff
rm -f /tmp/file1.sql /tmp/file2.sql
回答by Юрий Светлов
mysqldump from remote server use SSL
来自远程服务器的 mysqldump 使用 SSL
1- Security with SSL
1- 使用 SSL 的安全性
192.168.0.101 - remote server
192.168.0.101 - 远程服务器
192.168.0.102 - local server
192.168.0.102 - 本地服务器
Remore server
服务器
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;
-
——
Local server
本地服务器
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 - Security with SSL (REQUIRE X509)
2 - SSL 安全（需要 X509）
192.168.0.101 - remote server
192.168.0.101 - 远程服务器
192.168.0.102 - local server
192.168.0.102 - 本地服务器
Remore server
服务器
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;
-
——
Local server
本地服务器
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
[Note]
[笔记]
On local server
在本地服务器上
/usr/local/mysql/data/ssl/
/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
Copy this files from remote server for (REQUIRE X509) or if SSL without (REQUIRE X509) do not copy
从远程服务器复制此文件 (REQUIRE X509) 或如果 SSL 没有 (REQUIRE X509) 不复制
On remote server
在远程服务器上
/usr/local/mysql/data/
/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
我的.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
Increase Password Security
提高密码安全性
https://dev.mysql.com/doc/refman/8.0/en/password-security-user.html
https://dev.mysql.com/doc/refman/8.0/en/password-security-user.html
