How to Backup Remote MySQL Database Using mysqldump

MySQL

I tried to backup the database of a remote server using automysqlbackup.

From this platform, I learnt it was not possible without ssh and I need to back it up on a remote machine and then rsync or scp to my local machine, so I am now working with mysqldump.

The size of the database is almost 12GB.

mysqldump -h 192.168.10.209 -u user -ppassword testdb   > dump.sql

I tried backing it up and due to internet issues I have been disconnected twice already., and got this error once:

mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `helmet`': Lost connection to MySQL server during query (2013)

Is there way to continue from where it got disconnected using msyqldump?

I also read that mysqldump is not good for such a big database backup but I need to test it from here only at this moment.

Are there any options that can be used to make it work smoothly and backup my database?

Best Answer

Unfortunately, you cant continue your failed backup. When mysqldump returns a failed status you have to start over again.

My first recommendation is to compress your script before transmitting it

mysqldump -h 192.168.10.209 -u user -ppassword testdb | gzip > dump.sql.gz

Another recommendation is to connect to the remote server via ssh, then make the backup in the remote server (never will fail for network issues because is a local backup) and, in the last step you could transmit the file (after compress of course).

Other than that, the other 2 options more used are PERCONA XTRA BACKUP and MYSQL ENTERPRISE BACKUP but there are also high prices involved.

Related Solutions

MySQL – How to Detect a Running mysqldump

Your only hope in this scenario is to have the PROCESS privilege.

The SUPER privilege allows you to kill processes. Naturally, you don't want that. On the other hand, the PROCESS privilege allows you to see the processlist.

According to the MySQL Documentation on the PROCESS Privilege

The PROCESS privilege pertains to display of information about the threads executing within the server (that is, information about the statements being executed by sessions). The privilege enables use of SHOW PROCESSLIST or mysqladmin processlist to see threads belonging to other accounts; you can always see your own threads.

OK, Big Deal. The PROCESS Privilege lets you see the Process List. How does that help?

You can quickly detect a mysqldump in progress when you run SHOW PROCESSLIST; and see a pattern like this in the Info field:

SELECT /*!40001 SQL_NO_CACHE */ * FROM

If you have the PROCESS Privilege and are running MySQL 5.1+, you can run this query:

select COUNT(1) mysqldumpThreads
from information_schema.processlist
where info like 'SELECT /*!40001 SQL_NO_CACHE */%';

Here is a sample output:

mysql> select COUNT(1) mysqldumpThreads
    -> from information_schema.processlist
    -> where info like 'SELECT /*!40001 SQL_NO_CACHE */%';
+------------------+
| mysqldumpThreads |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

All you need to do is query for that number. If you get a nonzero, mysqldump must be running.

Give it a Try !!!

Mysql – Backup and off-site restore setup

Taking a backup from a slave is exactly how I do it, and it works very well. I can't imagine a better way to take a backup without causing downtime of the master and your application.

I am intrigued by your statement about not being able to restore a backup onto a mac osx machine (this could probably be a different question). An alternative is to setup a virtual machine on your local mac machines that run the same OS your production servers run. A tool that makes managing those machines easy is Vagrant.

Mimicking the production environment has the added benefit of allowing you to test any restore procedure with a fairly high degree of certainty that it would work on the production servers should you need to.

Best Answer

Related Solutions

MySQL – How to Detect a Running mysqldump

Mysql – Backup and off-site restore setup

Related Question