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:
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 !!!