how to mysqldump remote db from local machine
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.
I have tried to create an ssh tunnel and then do the dump, but this does not seem to work. I tried:
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
mysqldump -P 3310 -h localhost -u mysql_user -p database_name table_name
As I haven't seen it at serverfault yet, and the answer is quite simple:
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)
edit: well, to elaborate: if host is set to localhost, a configured (or default) --socket option is assumed. See the manual for which option files are sought / used. Under Windows, this can be a named pipe.
One can invoke mysqldump locally against a remote server.
Example that worked for me:
mysqldump -h hostname-of-the-server -u mysql_user -p database_name > file.sql
I followed the mysqldump documentation on connection options.
mysqldump from remote server use SSL
1- Security with SSL
192.168.0.101 - remote server
192.168.0.102 - local server
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)
192.168.0.101 - remote server
192.168.0.102 - local server
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/
-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
On remote server
/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
Increase Password Security
https://dev.mysql.com/doc/refman/8.0/en/password-security-user.html