1

So, I have to check and come with a backup solution for a very busy mysql server (with tables over 5 GB). Due to resources constraints, they choose to set all tables with the MyISAM engine. Currently, they use a bash script with calls to mysqldump for every single table, and they use the command --lock-tables=false, because it is imperative for them to be always online, but I really think that's not working the way they intended.

My guessing is all their backups are inconsistent, since they don't lock all the tables, but one by one with every dump. Am I correct?

Also, the only non-locking solution I can think of is to make a slave replication to properly lock the tables and use mysqldump or XtraBackup. My concern with this is getting a hit on performance, since it'll be writing twice and both instances will have to run on the same machine. Is there any other alternative for a non locking solution for MyISAM ?

Improve this question

1 Answer 1

Reset to default
1

Using lock-tables=false becomes irrelevant given MyISAM's table locking approach to DML.

You asked

My guessing is all their backups are inconsistent, since they don't lock all the tables, but one by one with every dump. Am I correct?

Yes, you are correct. Furthermore, when it comes to XtraBackup and MySQL Enterprise Backup, there must be a FLUSH TABLES WITH READ LOCK if MyISAM is present.

Oracle and I recently had dialogue. They asked me to change everything to InnoDB in order to use Enterprise Backup. When I explained I had 40GB and 2TB MyISAM (most of the tables had hundreds of partitions), they understood. I still that after all these years, you must still take matters into your own hands for point-in-time consistency with MyISAM.

SUGGESTION

Your best solution would be to use MySQL Replication and run the backups from the Slave

I have suggested this before

You may want to use binlog_format=ROW on Master and Slave to catch granular changes before running STOP SLAVE; FLUSH TABLES; on the Slave. Then, you can mysqldump from the Slave without worrying about any Storage Engine locking or consistency issues.

Give it a Try !!!

Improve this answer
4
  • Thanks a lot! I would upvote you if I could xD. About the replicated instance, how much of a performance overhead would it make?
    – Hito_kun
    Commented Mar 13, 2014 at 23:39
  • Only binlogs are read and sent through dedicated threads to the Slave, so not much.
    – RolandoMySQLDBA
    Commented Mar 14, 2014 at 1:47
  • Even when right now CPU and RAM utilization is over 95%? There are concerns here about this :S
    – Hito_kun
    Commented Mar 14, 2014 at 17:58
  • Also, what about Xtrabackup? Right now, mysqldump takes up to three hours to perform a backup, and I'm very interested to cut that time and still being able to restore the whole thing or specific databases/tables. Im under the impression that MyISAM tables can be copied directly, so I wouldn't actually need asny tool, but I'm unable to find further information on how doing so.
    – Hito_kun
    Commented Mar 14, 2014 at 21:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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