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
  phils AT caerllewys DOT net
  phil AT co.ordinate DOT 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 AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users