0

Is there a performance issue, if I run a cronjob in background with mysqldump --single-transaction with a 60 seconds interval? Will my original database keep functioning without an issue while the dumping is in progress?

what if I reduce the time interval to 5 seconds (in order to have a real time update), does this affect the performance of my database server a lot?

Also how does the mysqldump command generate the sql file? ( does it generate the statements from scratch again and again as we execute the command, or it just has the statements generated ready, and just gives it when we execute the mysqldump command? )

3

From here, the complete mysqldump command for a hot backup of a MySQL database using only InnoDB tables is: (from here).

mysqldump -uuser -ppass --single-transaction --routines --triggers --all-databases > backup_db.sql

In addition to the Enterprise solution mentioned by @TrentLloyd, there is a F/LOSS solution available from Percona (who are big hitters in the MySQL world). I would recommend without hesitation any tool by Percona and I would put this first on my list for testing on any MySQL system which I work with.

What they offer is Percona XtraBackup which has an incremental backup functionality. The documentation is here.

You might also be interested in LVM snapshots - it's not a technology with which I am currently familiar but it's on my to-do list (like a lot of stuff :-) ). If you're familiar with all that stuff about mounting logical volumes &c., this may be of interest to you.

You could also look at zmanda - (mentioned in "High Performance MySQL").

I would concur with @TrentLloyd that regularly taking a full backup (at such small time intervals) doesn't make sense - your system will not be able to do any real work with a constant resource drain like that.

For completeness, you might want to look at the options here: (Credit where credit is due, I "stole" this image from here :-) )

hereOn a final note, there is an endless number of shell (mostly bash) scripts out there. A Google of "mysql incremental backup bash script" (or similar) will get you a shedload of these. One of the best (IMHO) of which (and one of the most popular) is automysqlbackup, but you may like to try out any/all/some of the others.

With bash now being available natively on Windows (traditionally poor in the MySQL backup area), these might be worth investigating for users of that OS.

An interesting variant on automysqlbackup (disclaimer - I haven't used it) is a script which will perform incremental backups which is to be found here.

Another way to backup - which is "hot" is to use replication - this process is explained in detail here. The MySQL documentation is available here.

  • where did you get that image from? – knocte Jun 29 '16 at 6:04
  • Check where I wrote "I "stole" this image from here :-) )" - there's a link! The original source isn't quoted, but I think I've seen it in an O'Reilly book? – Vérace Jun 29 '16 at 6:06
1

Running a complete backup at these time intervals doesn't make sense. What you want to do is take a complete mysqldump back with binary log position (master-data) at some smaller time interval (daily, weekly) and then roll forward to the exact point in time you desire using binary logs.

See: https://dev.mysql.com/doc/refman/5.6/en/point-in-time-recovery.html

There is also a MySQL Enterprise-only product that helps to make hot and hot incremental backups without the binary log: http://www.mysql.com/products/enterprise/backup.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy