2

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.

  • 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
4

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.

2
  1. For Innodb you can just add CONFIG_mysql_dump_single_transaction='yes' in your conf file.

  2. For MyISAM engine, You will need to add --skip-add-locks in automysqlbackup file. find function parse_configuration and add array value as given below.

I have checked its working great.

Change

parse_configuration () {
    # OPT string for use with mysqldump ( see man mysqldump )
    opt=( '--quote-names' '--opt')

to

parse_configuration () {
    # OPT string for use with mysqldump ( see man mysqldump )
    opt=( '--quote-names' '--opt' '--skip-add-locks' '--skip-add-drop-table')

Your Answer

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

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