3

I am a newbie to DB and in particular to MySQL. Are there any good practices to backup MySQL on a large scale? For example, there are a few things I've already found.

  • Use mysqlbackup (physical backup) instead of mysqldump.

  • increase key_buffer_size to 20% of RAM (not just for backup extremely helpful option).

  • take a look at tools of maatkit there are a few for backup.

If you aware of any good stuff for MySQL backup share.

Starfish
  • 2,735
  • 25
  • 28
com
  • 261
  • 2
  • 15

3 Answers3

8

Do NOT use the tools in Maatkit. (I wrote them.) They are dangerous. I have written about this elsewhere. Percona Toolkit is the replacement for Maatkit and it does not include these tools.

If you have a MySQL Enterprise subscription, mysqlbackup is great. If not, consider Percona XtraBackup, a free tool that works almost identically.

Royce Williams
  • 1,362
  • 8
  • 16
Baron Schwartz
  • 96
  • 1
  • Hey Baron, I just mentioned XtraBackup in my answer. If I made any mistakes in my answer with regard to XtraBackup, please feel free to correct me. +1 !!! – RolandoMySQLDBA Jan 06 '12 at 18:18
3

This is not a silly question. You should take backups seriously.

Let's look at your three options:

1) Use mysqlbackup (physical backup) instead of mysqldump.

MySQL's Enterprise Backup is mentioned in Baron Schwartz's so I won't rehash.

2) increase key_buffer_size to 20% of RAM (not just for backup extremely helpful option).

The key_buffer_size governs the size of the MyISAM Key Buffer, which is for caching index pages of MyISAM tables. They do not play a factor in backups. For example, if you use mysqldump and do SHOW PROCESSLIST;, you will see something like

SELECT /* SQL_NO_CACHE */ FROM tblname

This prevents thrashing caches. Otherwise, needed data will be needlessly pushed out of MySQL's caches during a backup.

3) take a look at tools of maatkit there are few for backup.

Percona featured mk-parallel-dump and mk-parallel-restore. Percona recently declared these tools as deprecated. Some still use them at their own risk.

I actually wrote my own version of parallel dumping for databases and tables and posted the algorithms in the DBA StackExchange.

Percona now has Percona Tools. It does not include any backup tools. That is marketed separately as XtraBackup. IMHO it has pros and cons but it is definitely ideal for large installations

It is not ideal if you are looking for point-in-time recovery because the point-in-time of the backup data from XtraBackup is based on when XtraBackup has completed rather than when XtraBackup started. This becomes quickly apparent if the rate of incoming data is almost as fast as the backup process itself. Theorectically, if the rate of incoming data is as fast or faster that backup process, the backup process will never finish. That was said on stage LIVE at the Percona Live Conference back in May 2011. As long as you can live with this and your current rate of incoming data, then XtraBackup is the solution for you.

Community
  • 1
RolandoMySQLDBA
  • 16,544
  • 3
  • 48
  • 84
  • SQL_NO_CACHE is an instruction for the query cache; this is orthogonal to the MyISAM key cache (key_buffer_size). –  Jan 07 '12 at 03:02
  • Thank you, Baron. That one was a face palm moment : http://www.profilebrand.com/funny-pictures/category/demotivational/114_face-palm-star-trek.gif – RolandoMySQLDBA Jan 07 '12 at 04:28
-4

You can make a MySQL cluster and use the second node as a backup.

Stone
  • 7,011
  • 1
  • 21
  • 33