SEO

Hello,

I would like to make daily automatic full backups of my
databases. I've read a few articles/books about that topic in several
documents without finding any acceptable solution. I'm able to backup
my Myisam and innodb tables by hand (with mysqldum) while keeping a
consistent state for each db.

Is it possible to automate that, ie to 1) make a list of all tables 2)
define for each table if it's an Innodb or Myisam one 3) backup each
table with mysqldump with the options needed by the format of the
table (myisam or innodb) ?

Thanks a lot for yor answer
-AJ

On 21 Jul 2006 14:49:17 +0200, Antoine Junod wrote:
> I would like to make daily automatic full backups of my
> databases. I've read a few articles/books about that topic in several
> documents without finding any acceptable solution. I'm able to backup
> my Myisam and innodb tables by hand (with mysqldum) while keeping a
> consistent state for each db.
>
> Is it possible to automate that, ie to 1) make a list of all tables 2)
> define for each table if it's an Innodb or Myisam one 3) backup each
> table with mysqldump with the options needed by the format of the
> table (myisam or innodb) ?

Which of the above does mysqldump *not* do for you?

--
58. If it becomes necessary to escape, I will never stop to pose dramatically
and toss off a one-liner.
--Peter Anspach's list of things to do as an Evil Overlord

"Peter H. Coffin" <hellsop@ninehells.com> writes:

> On 21 Jul 2006 14:49:17 +0200, Antoine Junod wrote:
> > I would like to make daily automatic full backups of my
> > databases. I've read a few articles/books about that topic in several
> > documents without finding any acceptable solution. I'm able to backup
> > my Myisam and innodb tables by hand (with mysqldum) while keeping a
> > consistent state for each db.
> >
> > Is it possible to automate that, ie to 1) make a list of all tables 2)
> > define for each table if it's an Innodb or Myisam one 3) backup each
> > table with mysqldump with the options needed by the format of the
> > table (myisam or innodb) ?
>
> Which of the above does mysqldump *not* do for you?

mysqldump -u root --all-databases > backup.sql locks table after table
and thus permits to change a table within a db while another table of
that db is backuped. That implies that a record of the first table
point onto a modified (inexistant) record on the second
table. Consistency is not guarenteed.

The solution is the option --lock-all-tables. But I read in "The
definitive guide to MySQL 5, Apress", page 354, that this options
solves nothing for innodb tables (why?). In the same place, they
propose to use the following set of commands for backuping InnoDB
tables;

mysqldump -u root -p --skip-opt --single-transaction \
--add-drop-table --create-options --quick --extended-insert \
—set-charset —disable-keys databasename > backup.sql

That is what i'm using now. It's mean i've two different commands, one
for the myisam tables and one for the innodb tables. My current backup
script has a list of myisam tables and another list for innodb
tables. These lists are hand made. I would be able to build them
automaticaly. Is there a way to do that?

Or, more simply, is there a set of mysqldump options that are doing
what I want and that is common to both tables type?

Thans for your reply
-AJ

Antoine Junod wrote:
> The solution is the option --lock-all-tables. But I read in "The
> definitive guide to MySQL 5, Apress", page 354, that this options
> solves nothing for innodb tables (why?).

One doesn't need to lock tables to get a consistent read, if the tables
support transaction isolation.

Read about REPEATABLE READ here:
http://dev.mysql.com/doc/refman/5.0/...isolation.html

> My current backup
> script has a list of myisam tables and another list for innodb
> tables. These lists are hand made. I would be able to build them
> automaticaly. Is there a way to do that?

The following query should get you the information you need (assuming
you using MySQL 5.0):

SELECT t.table_schema, t.table_name, t.table_type, t.engine
FROM INFORMATION_SCHEMA.tables t;

The `engine` column has values 'InnoDB', 'MyISAM', 'MEMORY'. For views,
the column is NULL.

See http://dev.mysql.com/doc/refman/5.0/...les-table.html

Regards,
Bill K.

Bill Karwin <bill@karwin.com> writes:

> Antoine Junod wrote:
> > The solution is the option --lock-all-tables. But I read in "The
> > definitive guide to MySQL 5, Apress", page 354, that this options
> > solves nothing for innodb tables (why?).
>
> One doesn't need to lock tables to get a consistent read, if the
> tables support transaction isolation.
>
> Read about REPEATABLE READ here:
> http://dev.mysql.com/doc/refman/5.0/...isolation.html
>
> > My current backup
> > script has a list of myisam tables and another list for innodb
> > tables. These lists are hand made. I would be able to build them
> > automaticaly. Is there a way to do that?
>
> The following query should get you the information you need (assuming
> you using MySQL 5.0):
>
> SELECT t.table_schema, t.table_name, t.table_type, t.engine
> FROM INFORMATION_SCHEMA.tables t;
>
> The `engine` column has values 'InnoDB', 'MyISAM', 'MEMORY'. For
> views, the column is NULL.
>
> See http://dev.mysql.com/doc/refman/5.0/...les-table.html

Thanks for your reply and the links. That's helped a lot.

-AJ