On 01/11/17 16:57, scar wrote: > i have been using the script at [1] but then i noticed that the > incremental backups were quite large and containing some MYD and MYI > files. so i realized these were myisam databases. i read that the best > way to backup both innodb and myisam databases was to use the mysqldump > option --lock-tables instead of --single-transaction, which is what the > script[1] is using.
Correct, there are two use cases for MySQl backups using mysqldump: Case 1 - MyISAM tables are present Use default locking (an explicit --lock-tables is not necessary, it's the default anyway) Case 2 - Only InnoDB tables present Use --skip-lock-tables --single-transaction You may want to use 2 if there are no InnoDB tables *outside of the MySQL schema*, because in most The other option here is to use mydumper (use only v0.9.1 or newer, earlier versions have serious bugs). mydumper is inherently aware of transactional and non-transactional storage tables, and unlike mysqldump it can Just Do The Right Thing with both at the same time on a table-by-table basis. As an added bonus, it's multithreaded, so it gets your backups done faster. > therefore, i was thinking i'd update the script to use --lock-tables, > and also adjust my bacula settings so it is only backing up the output > from mysqldump and the binlog directory. > > so i just wanted to check in to see if that would cover everything? or > am i missing things? That's the sane approach. Get a consistent logical dump, then back up the dump. It is not really useful to back up the binary database table files. You can't guarantee a consistent backup that way, you are almost GUARANTEED problems upon restore if you do a hot binary backup. Yes, there is a mysqlhotcopy script out there that *promises* that it can safely and consistently hot-backup MyISAM tables. It even works, most of the time. But the MySQL dev team have been actively working for several years towards deprecating and phasing out MyISAM anyway. The truth is, MyISAM pretty much sucks and you shouldn't be using it in production any more. Remember that MyISAM is a 20-year-old storage engine, one of whose primary design criteria was that it needed to work *acceptably well* on a small shared server, at a time when a "large" server might have a whole 32MB of RAM. The default sizes of some individual MySQL *BUFFERS* are larger than that. -- Phil Stracchino Babylon Communications ph...@caerllewys.net p...@co.ordinate.org Landline: 603.293.8485 ------------------------------------------------------------------------------ Developer Access Program for Intel Xeon Phi Processors Access to Intel Xeon Phi processor-based developer platforms. With one year of Intel Parallel Studio XE. Training and support from Colfax. Order your platform today. http://sdm.link/xeonphi _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
