how to mysqldump remote db from local machine

Question

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 user remote server -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

User · Answer

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

User · Answer

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  gt  file sql   I followed the mysqldump documentation on connection options

User · Answer

As I haven t seen it at serverfault yet  and the answer is quite simple   Change   ssh -f -L3310 remote server 3306 user remote server -N   To   ssh -f -L3310 localhost 3306 user remote server -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

User · Answer

Bassed on this page here   Compare two MySQL databases  I modified it so you can use ddbb in diferent hosts        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    2    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  3   grep  table           then     echo  Comparing   table          dump  1  tmp file1 sql     dump  2  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

[mysql] how to mysqldump remote db from local machine

mysqldump from remote server use SSL

1- Security with SSL

2 - Security with SSL (REQUIRE X509)

[Note]

Examples related to mysql

Examples related to mysqldump