29

I have a MySQL database existing on a remote server. I only have connection privilege. I don't have FTP access to the server, and I need to do a complete dump of the database. I have tried mysqldump, but the issue is that it is creating the output on the server and as I don't have FTP I can not get the output from the server.

How can I do a clean backup and get the dump in my local machine(of course, the backup should be restored in my local machine)?

Nae
  • 14,209
  • 7
  • 52
  • 79
john smith
  • 301
  • 1
  • 3
  • 4
  • I don't understand the problem. `mysqldump` writes the dump to standard output, and when you redirect it it goes to the local host, not the server. – Barmar Oct 23 '13 at 21:02
  • for running mysqldump i need to first login to the server, which I dont have the privilage, what I have is only a sql connection to the Mysql server – john smith Oct 23 '13 at 21:05
  • 2
    `mysqldump` takes a `--host` option to access a remote server, just like the `mysql` command does. – Barmar Oct 23 '13 at 21:06

6 Answers6

35

You can specify the server name as an option to mysqldump:

mysqldump --host servername dbname > dbname.sql
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • I used it many times, but was always wondering - isn't that a horrible exploit ? I mean, anyone with a host and a db name can copy a whole DB without any password ?? – Obmerk Kronen Mar 07 '14 at 02:43
  • 11
    It checks permissions just like any other use of `mysql`. – Barmar Mar 07 '14 at 02:48
  • There could be a firewall blocking network connections to the server. Or maybe it's configured to only listen on `localhost`. @guitarlass – Barmar Jun 20 '22 at 14:07
28
mysqldump --host hostaddress -P portnumber -u username -ppassword dbname > dbname.sql

Normally the remote port of MySQL is 3306. Here is an example:

mysqldump --host 192.168.1.15 -P 3306 -u dev -pmjQ9Y mydb > mydb.sql
d-_-b
  • 21,536
  • 40
  • 150
  • 256
Henry
  • 1,077
  • 1
  • 16
  • 41
  • 4
    One gotcha -be sure NO space after the -p PASSWORD – jpw Jan 13 '15 at 23:26
  • 2
    man mysqldump: ... Specifying a password on the command line should be considered insecure... https://dev.mysql.com/doc/refman/5.7/en/password-security-user.html – alditis Apr 21 '17 at 05:31
2

You can use the MySQL workbench http://www.mysql.com/products/workbench/, which can backup directly to a local folder through a user-friendly interface

user2849406
  • 195
  • 1
  • 1
  • 6
1

mysqldump.exe locks tables by default, so other SQL actions are not possible during a dump. Without locking any tables, use the following syntax to backup a complete remote db and dump everything on your local machine:

mysqldump -u username -p --single-transaction --quick --lock-tables=false -h ipaddress myDB > backup.sql

Change username into your own username, change ipaddress into the remote ip address, and myDB to the actual database you want to backup. This will prompt you for your password. Once provided, the dump starts.

Kurt Van den Branden
  • 11,995
  • 10
  • 76
  • 85
0

I use SQLyog for this where we can connect to the remote server and take a back up with this tool.

Sathish D
  • 4,854
  • 31
  • 44
0

If the server admits PHP, you can upload and try Adminer. I like it as a PHPMyAdmin replacer, and you can create backups with it!

combuilder
  • 93
  • 1
  • 7