Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When I try to dump an online database using --lock-tables=false, others cannot update the table. Are there any methods to solve this?

As I am using MyISAM, does --single-transaction for InnoDB help?

Also, is Master-Slave architecture the ultimate method to handle backing up online DB?

Thanks!

share|improve this question
add comment

1 Answer 1

up vote 0 down vote accepted

Your Question : When I try to dump an online database using --lock-tables=false, others cannot update the table. Are there any methods to solve this?

You need to understand why other cannot update. Using MyISAM, each table experiences a full table lock with every INSERT, UPDATE, and DELETE. Using --lock-tables will attempt to lock every table, but will be held up of SELECTs are being issued in a heavy-read environment. There are other alternatives.

Your Question : As I am using MyISAM, does --single-transaction for InnoDB help?

InnoDB is an ACID-compliant storage engine and can have data updated and read in transactions. Row-level locking is normally issued in InnoDB, which allows for other to read and write to tables while they being backed up. MyISAM does not allow this given its full-table locking protocol. Therefore, MyISAM is not assisted by --single-transaction at all. If every table you created was InnoDB, then you could perform an online backup on a running master.

Your Question : Also, is Master-Slave architecture the ultimate method to handle backing up online DB?

Master/Slave does make it easier to backup an online database because you perform the backup using mysqldump on the slave while the Master continues to accept INSERTs, UPDATEs, and DELETEs. In addition, this allows for backups that allow for point-in-time recovery. You would simply perform the following steps on a slave:

  • STOP SLAVE;
  • mysqldump
  • START SLAVE;

If you issue this on the Slave at midnight, the data is frozen in time. That way, when you restore the mysqldump, all the tables are from the same timeframe.

SIDENOTE : You are not obligated to convert all the tables on the Slave to InnoDB.

If all tables were InnoDB, using single-transaction would create point-in-time backups also againsa a master. Since you have MyISAM tables, then Master-Slave is the ideal solution, not necessarily the ultimate. There are still other methods out there, but for the sake of simplicity: GO WITH MASTER-SLAVE !!!

share|improve this answer
    
Thanks for your answer! It seems that I should try to look into master/slave solution first. –  Harold Chan Jan 10 '12 at 2:39
add comment

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.