How to backup MySQL database on a remote server?
Solution 1
You can specify the server name as an option to mysqldump
:
mysqldump --host servername dbname > dbname.sql
Solution 2
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
Solution 3
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
Solution 4
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.
Related videos on Youtube
john smith
Updated on May 31, 2020Comments
-
john smith almost 4 years
I have a MySQL database existing on a remote server. I only have sql 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)?
-
Barmar over 10 yearsI 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. -
john smith over 10 yearsfor 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
-
Barmar over 10 years
mysqldump
takes a--host
option to access a remote server, just like themysql
command does.
-
-
Obmerk Kronen about 10 yearsI 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 ??
-
Barmar about 10 yearsIt checks permissions just like any other use of
mysql
. -
jpw over 9 yearsOne gotcha -be sure NO space after the -p PASSWORD
-
Rael Gugelmin Cunha over 8 yearsAnd, if you have only SSH access to the server, Workbench will work too. And it's available on Ubuntu:
sudo apt-get install mysql-workbench
. -
alditis about 7 yearsman mysqldump: ... Specifying a password on the command line should be considered insecure... dev.mysql.com/doc/refman/5.7/en/password-security-user.html
-
user7082181 about 2 yearsthat soft is garbage, I been using it for years, you always get versions issues
-
Barmar almost 2 yearsThere could be a firewall blocking network connections to the server. Or maybe it's configured to only listen on
localhost
. @guitarlass