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

"Peter H. Coffin" <hellsop (AT) ninehells (DOT) com> writes:

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:
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

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 (AT) karwin (DOT) com> writes:

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

-AJ