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 [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 this question is more related to administration than to programming, I'd say the kind people at serverfault.com could assist you better. — Piskvor left the building
– Piskvor left the building, Commented Jun 7, 2010 at 13:44
Take a look at MSQL WorkBench 5.2.22. It will easily allow you to do this. — Gary
– Gary, Commented Jun 7, 2010 at 13:55
NOTE: for dumping a remote mysql server, please do include --host=sqlserver.host.name --port=3306 — ro0ter
– ro0ter, Commented Feb 13, 2017 at 7:40
I'm voting to close this question as off-topic because it belongs on DBA, — user207421
– user207421, Commented Aug 5, 2019 at 11:03

Wrikken · Accepted Answer · 2013-04-05 21:56:09Z

297

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.

edited Apr 5, 2013 at 21:56

answered Jun 7, 2010 at 15:21

Wrikken

70.8k8 gold badges99 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

polkovnikov.ph Over a year ago

Beware: localhost often defaults to ::1 IPv6, not 127.0.0.1.

Jeshizaemon Over a year ago

What does -P do? -P is for port and -p is to prompt for password.

Wrikken Over a year ago

-P is indeed, like you say, for port. As indicated in the question, and done in the answer. Feel free to drop that, and replace 3310 in the SSH tunnel to 3306

Ondrej Burkert · Accepted Answer · 2019-08-05 10:42:45Z

219

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.

edited Aug 5, 2019 at 10:42

answered Feb 15, 2018 at 15:12

Ondrej Burkert

7,3623 gold badges36 silver badges28 bronze badges

17 Comments

Abhay Maurya Over a year ago

No more need to open ssh tunnel before. +1

TheGabiRod Over a year ago

simplest aproach, theres no need to connect via ssh and bring the file back to your local machine! Just bring the dump directly!

louffi Over a year ago

this is dangerous, very dangerous. it requires that the port of mysql is open to public. which can be easily brute forced by bots.

Ondrej Burkert Over a year ago

How about being in a VPN? Or doing a dump from a machine you SSHed into that has access to the database machine? The port does not need to be public.

fIwJlxSzApHEZIl Over a year ago

It's important to remember here that -p is for the password argument - not the database name. But it's insecure to store it in plain text so adding -p means you will be prompted for the password at login. Maybe it's just me but the mysql and thus the mysql dump syntax was never all that straight forward as far as command line arguments.

|

Юрий Светлов · Accepted Answer · 2020-04-17 00:51:25Z

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

Eduard Pertíñez · Accepted Answer · 2020-01-30 17:26:27Z

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

Collectives™ on Stack Overflow

how to mysqldump remote db from local machine

4 Answers 4

3 Comments

17 Comments

mysqldump from remote server use SSL

1- Security with SSL

2 - Security with SSL (REQUIRE X509)

[Note]

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

17 Comments

mysqldump from remote server use SSL

1- Security with SSL

2 - Security with SSL (REQUIRE X509)

[Note]

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related