How to backup MySQL database on a remote server?

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)?

Answers


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

mysqldump --host servername dbname > dbname.sql

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

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


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


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!


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.


Need Your Help

PHP - How to use Curl with Kerberos

php linux curl kerberos spnego

I'm trying to do some operation with curl, but the website where I have to work uses Kerberos authentication. Consequently I have to enable the SPNEGO function of curl which is disabled by default....

GitHub client: Pull Request Button is missing

git github pull-request github-for-windows

I use GitHub client for Bitbucket repository. All work & sync correctly, but Pull Request button not showing. In GitHub repo all works.