Quantcast

correct strategy for mysql innodb and myisam backup

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

correct strategy for mysql innodb and myisam backup

scar-2
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.

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?

Thanks


1.
http://wiki.bacula.org/doku.php?id=application_specific_backups:mysql#using_binary_logs


------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: correct strategy for mysql innodb and myisam backup

Phil Stracchino-2
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
  [hidden email]
  [hidden email]
  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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: correct strategy for mysql innodb and myisam backup

Phil Stracchino-2
On 01/11/17 17:43, Phil Stracchino wrote:
>
> You may want to use 2 if there are no InnoDB tables *outside of the
> MySQL schema*, because in most

...oops.  That paragraph was meant to continue:  "..in most cases, the
MyISAM tables inside the mysql schema change only rarely and you are
unlikely to be doing things like changing grants while in the middle of
a backup."



--
  Phil Stracchino
  Babylon Communications
  [hidden email]
  [hidden email]
  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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: correct strategy for mysql innodb and myisam backup

Josip Deanovic
In reply to this post by Phil Stracchino-2
On Wednesday 2017-01-11 17:43:42 Phil Stracchino wrote:
[...]
> 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.

There are applications that require MyISAM and the Innodb is not an
option at all because critical parts of the application just doesn't
work with innodb and if the application is proprietary software you
can't do much about it but continue to use MyISAM.

People usually ignore such cases and insist that there is absolutely
no reason to use MyISAM.

--
Josip Deanovic

------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: correct strategy for mysql innodb and myisam backup

Phil Stracchino-2
On 01/12/17 00:22, Josip Deanovic wrote:

> On Wednesday 2017-01-11 17:43:42 Phil Stracchino wrote:
> [...]
>> 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.
>
> There are applications that require MyISAM and the Innodb is not an
> option at all because critical parts of the application just doesn't
> work with innodb and if the application is proprietary software you
> can't do much about it but continue to use MyISAM.
>
> People usually ignore such cases and insist that there is absolutely
> no reason to use MyISAM.

Most applications that "require" MyISAM "require" it for no good reason,
or depend on features that have historically been supported only by
MyISAM but are now supported in InnoDB in current MySQL releases.  It
bears case-by-case examination.  One of the most common reasons to
"require" MyISAM is fulltext search, but the honest truth is that if
you're making heavy use of fulltext search in MySQL you should probably
be using an external fulltext engine such as Sphinx, SOLR, or Lucene for
it anyway.  You might be shocked to find how many proprietary
application that "require" MyISAM do so simply because "but we've always
done it that way".

The fact remains, the MyISAM storage engine is well on its way to being
removed from MySQL, and some awareness on the matter is in order.  MySQL
5.7 already allows it to be completely disabled.


--
  Phil Stracchino
  Babylon Communications
  [hidden email]
  [hidden email]
  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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: correct strategy for mysql innodb and myisam backup

Josip Deanovic
On Thursday 2017-01-12 08:36:53 Phil Stracchino wrote:

> Most applications that "require" MyISAM "require" it for no good reason,
> or depend on features that have historically been supported only by
> MyISAM but are now supported in InnoDB in current MySQL releases.  It
> bears case-by-case examination.  One of the most common reasons to
> "require" MyISAM is fulltext search, but the honest truth is that if
> you're making heavy use of fulltext search in MySQL you should probably
> be using an external fulltext engine such as Sphinx, SOLR, or Lucene
> for it anyway.  You might be shocked to find how many proprietary
> application that "require" MyISAM do so simply because "but we've always
> done it that way".

Nothing shocking there.

There are application written around year 2000 before the innodb and
certainly before innodb became mature.
Mysql is still getting nice innodb options and performance improvements
almost every year.

The fulltext search is the exactly the feature that might lock such
applications to myisam and that was the time before the dawn of
search engines such as sphinx, redis etc.

So if one is for some reason locked to specific old version of a
specific old proprietary application one can't do much than continue
with the MyISAM as innodb is not an option and external search engines
are not supported either.

I am not saying that innodb isn't a better mysql storage engine when
compared to MyISAM. I am saying that sometimes the innodb is not the
option and you have to stick with MyISAM for some time.

--
Josip Deanovic

------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: correct strategy for mysql innodb and myisam backup

Alan Brown
On 12/01/17 14:14, Josip Deanovic wrote:
> So if one is for some reason locked to specific old version of a
> specific old proprietary application one can't do much than continue
> with the MyISAM as innodb is not an option and external search engines
> are not supported either.

I've had to deal with such software:


Once the database is created "UPDATE TABLE table ENGINE=MYISAM" works
just fine in 99.9% of cases.

The only table I ever had trouble with was in GLPI, where one search
function failed using innodb, but that was fixed in a more recent
version of mysql anyway.

> I am not saying that innodb isn't a better mysql storage engine when
> compared to MyISAM. I am saying that sometimes the innodb is not the
> option and you have to stick with MyISAM for some time.
>
At the risk of offending someone:


1: If your database load is high enough that you really need to worry
about myisam vs innodb for performance reasons then you should be
considering using something like PostgreSQL anyway.

2: If Bacula has caused memory consumption of MySQL to grow larger than
4GB, it's time to switch to PostgreSQL.

3: If your database must have utterly reliable recovery from a crash
(including write recovery), it's time to look at using something other
than mysql.


I'm not "dissing" MySQL. It's a brilliant database for what it's
designed to do - a small, fast, simple query-optimised database for
things like webservers.

It's not designed to be used in a write-heavy multiuser environment or
with lots of complex queries. Once the system load (cpu and memory) of
mysql exceeds that of another database (or queries are executed
noticeably slower) then it's time to switch.


If you only have a hammer then every problem looks like a nail.

Even if you "only know mysql", it's not difficult to change to something
else - and once you do, you'll be happily surprised to notice how little
tuning pg_sql needs (almost none: Everything is designed to be
automatically optimising and there's a pg_tune script which will do all
the startup stuff for you)


Example: The Bacula system here needed regular MySQL maintenance. After
switching to PGsql I find that I only ever look at it to confirm it's
still working ok.


On a very small (sub 1GB) system, PostgreSQL vs MySQL will (on paper)
favour mysql because of the no-load/no-data footprints, but on just
about any x86 system made in the last 10 years there's no fundamental
disadvantage to using PostgreSQL from the outset (and doing so avoids
possibly crossing paths with Oracle in future)


If you are backing up databases:


The "best" strategy for DB table backup is DO NOT DO IT - generate a DB
dump using the appropriate tools and back that up. Anything else will
cause problems when it's restoration time.






------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: correct strategy for mysql innodb and myisam backup

Josip Deanovic
On Thursday 2017-01-12 17:21:05 Alan Brown wrote:

> On 12/01/17 14:14, Josip Deanovic wrote:
> > So if one is for some reason locked to specific old version of a
> > specific old proprietary application one can't do much than continue
> > with the MyISAM as innodb is not an option and external search engines
> > are not supported either.
>
> I've had to deal with such software:
>
>
> Once the database is created "UPDATE TABLE table ENGINE=MYISAM" works
> just fine in 99.9% of cases.

This is only the last step.
Before that you will need to analyze your database and carefully prepare
the server for the migration. Especially if your MySQL server is heavily
optimized for MyISAM only setup.

For example, if you have optimized the server for MyISAM you probably
chose not to use large pages (which also requite some kernel tweaking)
because MyISAM doesn't benefit from it.

If you are preparing to migrate to innodb you might consider to optimize
the server for the innodb only setup which has somewhat different
requirements relating the way memory and disk is accessed.

If the server wasn't really optimized for MyISAM and after the migration
you didn't monitor the server and optimized it for innodb then it is
questionable if it was really needed (performance wise, of course there
is still benefit from better chances for data consistency but then again
that depends of the configuration and level of optimization).

> The only table I ever had trouble with was in GLPI, where one search
> function failed using innodb, but that was fixed in a more recent
> version of mysql anyway.

Exactly. As Phil mentioned it's the fulltext search feature.

> > I am not saying that innodb isn't a better mysql storage engine when
> > compared to MyISAM. I am saying that sometimes the innodb is not the
> > option and you have to stick with MyISAM for some time.
>
> At the risk of offending someone:

I estimate the risk as low. :-)

> 1: If your database load is high enough that you really need to worry
> about myisam vs innodb for performance reasons then you should be
> considering using something like PostgreSQL anyway.

Again, it depends on the application db support and whether you
can deal with it or not.

We went off-topic in this thread as we started to talk about the myisam
and innodb in general and not just in context of Bacula support.
I find myself guilty. :-)

> 2: If Bacula has caused memory consumption of MySQL to grow larger than
> 4GB, it's time to switch to PostgreSQL.

I have just checked one of my bacula setups. The database is 16G in size
and there are no performance or any other issues whatsoever.
Interesting thing is that this particular database I have checked
resides on vmware virtual machine (with fast disks), few CPUs and just
few gigabytes of RAM.

I have to admit that the number of servers in this particular setup
is less than 100 and that jobs are not executing concurrently. Might
be a bit different story otherwise.

> 3: If your database must have utterly reliable recovery from a crash
> (including write recovery), it's time to look at using something other
> than mysql.

Absolutely agree.
But as I already said, it depends on the application db support.
One would first need to move from the old application if possible.

> I'm not "dissing" MySQL. It's a brilliant database for what it's
> designed to do - a small, fast, simple query-optimised database for
> things like webservers.

I believe that "brilliant" is a too strong word in this context. :-)

> It's not designed to be used in a write-heavy multiuser environment or
> with lots of complex queries. Once the system load (cpu and memory) of
> mysql exceeds that of another database (or queries are executed
> noticeably slower) then it's time to switch.

Yes, these are the MySQL's weak points (among others).

> If you only have a hammer then every problem looks like a nail.
>
> Even if you "only know mysql", it's not difficult to change to something
> else - and once you do, you'll be happily surprised to notice how
> little tuning pg_sql needs (almost none: Everything is designed to be
> automatically optimising and there's a pg_tune script which will do all
> the startup stuff for you)

I am too much into mysql and postgres to be able to agree with this.
In my opinion in order to get most out of these databases it would
take yeeears of hard work, reading books and manuals, experimenting,
reading their source, cursing, banging desk with a head, debugging,
cursing again, monitoring, graphing and so on (at least that's what it
took me to get it).

In short if you master only one of those databases there is a whole
nee world that awaits to be mastered when you start working with the
other one.

> Example: The Bacula system here needed regular MySQL maintenance. After
> switching to PGsql I find that I only ever look at it to confirm it's
> still working ok.

Interesting. I don't have any problems whatsoever and I only have to
deal with jobs, not with the database (once it is setup and optimized)
regardless whether it is mysql or postgres.

> On a very small (sub 1GB) system, PostgreSQL vs MySQL will (on paper)
> favour mysql because of the no-load/no-data footprints, but on just
> about any x86 system made in the last 10 years there's no fundamental
> disadvantage to using PostgreSQL from the outset (and doing so avoids
> possibly crossing paths with Oracle in future)
>
>
> If you are backing up databases:
>
>
> The "best" strategy for DB table backup is DO NOT DO IT - generate a DB
> dump using the appropriate tools and back that up. Anything else will
> cause problems when it's restoration time.

There are however tools for both mysql and postgres that can safely
copy the datadir without dump. But of course this is not the table
backup you have been referring to.

The problem with dump and restore is that they take awful lot of time
and sometimes they are impractical. Fortunately there are other tools
and approaches.


Regards

--
Josip Deanovic

------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Loading...