How to backup MySQL database on a remote server?

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.

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

mysqldump --host servername dbname > dbname.sql

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

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