Mysql – How to backup a MySQL database without slowing the application

backupmyisamMySQLperformance

I'm an application developer and wondering about our database backup strategy. Right now our sysadmins backup our database every 8 hours. I don't really know how the do it, but while the backup is running our application response time goes through the roof. So every 8 hours, the application is slow and sometimes even produces errors.

Is there a way to backup a MySQL database with MyISAM tables without slowing application code and still get a consistent backup?

Best Answer

A) Take the backup from a replicating slave. Zero performance loss.

B) Use LVM to take a consistent snapshot. Copy the database files at your leisure.

C) Use Rsync over a remote connection to copy the data files, then do a final Rsync with the database briefly locked for a couple seconds.

As an aside, how are they taking a consistent backup with MyISAM tables without LOCKING the application for 8 hours straight? It doesn't seem possible.

Related Solutions

Mysql – Is this an appropriate development environment MySQL backup strategy

I would also recommend MySQL dump with an additional twist, place the dump file under version control, at regular intervals, I would recommend when a change is complete. That way you can also track the changes to the database and make reconstruction easier

Oracle 11, incremental backup goes wrong

You are doing a differential incremental backup rather than cumulative, but the syntax for recover of copy tag 'sometag' indicates that you plan to incrementally update an image file backup. Phil was on the right tack with the missing RECOVER COPY OF DATABASE WITH TAG 'sometag'. Since you are not running this each incremental must be able to recover the un-updated tag 'sometag' causing it to grow each day as though CUMULATIVE were specified.

See Backup Sets and Image Copies in the Concepts Guide and see Incrementally Updating Backups in the Backup and Recovery User's Guide (11.2). If your intention was to use incrementally updated image copies you will want something more like this:

  RUN
  {
    RECOVER COPY OF DATABASE 
      WITH TAG 'incr_update';
    BACKUP 
      INCREMENTAL LEVEL 1
      FOR RECOVER OF COPY WITH TAG 'incr_update'
      DATABASE;
  }

If you didn't intend to do this, then simply remove the for recover... syntax.

Best Answer

Related Solutions

Mysql – Is this an appropriate development environment MySQL backup strategy

Oracle 11, incremental backup goes wrong

Related Question