4

I've mysql server setup in remote server. I can access it using pem file through workbench using connection method as ssh over IP. I want to take backup of one of the DBs from my remote server.

I know, I can use following command to do remote mysqldump,

mysqldump -P3306  -h192.168.1.101 -uroot -ppassword database > /root/user/my.sql

But, I don't have password for the mysql server. I use .pem file for authenticating. So, how do I run mysqldump command?

Edit 1: I tried following command

ssh -i ~/.ssh/my.pem -f -L3310:localhost:3306 ubuntu@<server-ip> -N

But couldn't succeed. It throws following error,

REMOTE HOST IDENTIFICATION HAS CHANGED!

So, I tried this. But same error. :(

Community
  • 1
  • 1
Abhishek
  • 6,912
  • 14
  • 59
  • 85

2 Answers2

4

Not sure if you eventually solved this, but I did by using the following:

mysqldump --ssl-key=/path/to/pem -u your_username -p -h your.host.com  your_db > your_dump.sql

Hope this helps.

thomasstephn
  • 3,775
  • 4
  • 24
  • 29
0

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

Юрий Светлов
  • 1,622
  • 2
  • 23
  • 27