Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

I'm trying to use AutoMysqlBackup script to perform daily backups of my application. Unfortunately on the first try it didn't served me how I thought it would.

I believe that the script has used the --lock-tables=true parameter and it made my application stop working.

If this is the case, how can I make the script not lock the tables? (so my application can continue running)?

Thank you.

share|improve this question
    
If you're using InnoDB tables, can it do --single-transaction instead? It locks the tables to ensure a consistent backup. –  derobert Sep 28 '12 at 18:33
    
How do I know if it's InnoDB tables? –  Darkeden Sep 28 '12 at 18:39
    
SHOW TABLE STATUS, look at the ENGINE column. –  derobert Sep 28 '12 at 18:43
    
Tables = MyISAM –  Darkeden Sep 28 '12 at 18:46
    
I haven't found a community/forum of users of that software ... It seems to be a very peculiar problem ... I hope someone can guide me –  Darkeden Sep 28 '12 at 18:52

1 Answer 1

Ok, not really familiar with that software, but:

  • It is a shell script, so trivial (in theory) to edit. Of course, its a ~2200 line shell script.
  • I think --lock-tables is coming from --opt, which is specified (from a very quick glance) at the top of the parse_configuration function. You could add --skip-lock-tables there.
  • It uses the mysql utilities, so you may also be able to add it in your .my.cnf in the normal way.

In general:

  • You're not using InnoDB, but are using MyISAM, so you don't have transactions. So you can't use --single-transaction.
  • You thus need to lock to ensure consistency. Even with locking, its not guaranteed (just more likely). But it is just as much as you are guaranteed in normal operation. Consider InnoDB, seriously (but please read the docs, and research first, and test, to make sure it won't break your app).
  • If you disable locking, you may have:
    1. Start backup
    2. Backup table A.
    3. Delete a record from A, and also the child record in B.
    4. Backup table B.
    5. Your backup now contains a record in A that points to a nonexistent record in B. In other words, its not consistent.
  • There are more-widely-used MySQL backup solutions. Maybe you should switch to one of them. Its easier to find help with software that more people use (and it also tends to be better tested).
  • You may think you have a backup, but you don't until you've actually done a successful restore, preferably from bare metal (freshly formatted hard disk). Ideally, you do this routinely, and preferably automate it. This isn't specific to MySQL, it applies to all backups.

There is a solution (besides switching to InnoDB): you can perform your backup on a slave server. It doesn't matter if you lock all tables, or SLAVE STOP SQL_THREAD during the backup, because it won't matter to the primary. This is the no-downtime solution. You should have this server anyway, as a warm/hot standby in case the primary fails.

There is another solution, which minimizes downtime: Put the database on a LVM volume, do a FLUSH TABLES WITH READ LOCK, take a LVM snapshot, and then release your read lock (disconnect will do this). You can then do a backup from the snapshot. This is the "I can't afford another machine" solution.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.