Hi,

i've been already reading the documentation the whole day, but still confused and unsure what to do.

We have two databases which are important for our work. So both are stored hourly. Now I recognized that each database has a mixture of MyISAM- and InnoDB-tables. A backup of this mix does not seem to be easy. Until now it was dumped using "mysqldump --opt -u root --databases mausdb ...". What I understand until now is that --opt is not necessary because it is default. It includes, among others, --lock-tables which is senseful for saving MyISAM-tables. For InnoDB-tables --single-transaction is useful. But both are mutually exclusive (http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_single-transaction ). The dump of both take about 10 seconds. If the db is locked for that period I can live with.
When I use --single-transaction only the InnoDB-tables are consistent. Using --lock-tables the MyISAM-tables are stored consistently. What is about --lock-tables in conjunction with InnoDB-tables ?
Are they stored consistently ? Are they locked during the dumping ? As I said, I could live with a small lock period (< 30 sec). Would --lock-all-tables be better ?
"Lock all tables across all databases. This is achieved by acquiring a global read lock for the duration of the whole dump. This option automatically turns off --single-transaction and --lock-tables" (from the manpage). I can live with a global read lock for the duration of the whole dump.
--lock-tables causes any pending transactions to be committed implicitly (http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_single-transaction ). Is that a problem for the InnoDB tables ?

Our system is:
mysql-5.0.26-12.29.1 on a SLES 10 SP4 64 bit host.


Bernd



--
Bernd Lentes

Systemadministration
Institut f=FCr Entwicklungsgenetik
Geb=E4ude 35.34 - Raum 208
HelmholtzZentrum m=FCnchen
bernd.lentes@helmholtz-muenchen.de
phone: +49 89 3187 1241
fax: +49 89 3187 2294
http://www.helmholtz-muenchen.de/idg

Die Freiheit wird nicht durch weniger Freiheit verteidigt



Helmholtz Zentrum M=FCnchen
Deutsches Forschungszentrum f=FCr Gesundheit und Umwelt (GmbH)
Ingolst=E4dter Landstr. 1
85764 Neuherberg
www.helmholtz-muenchen.de
Aufsichtsratsvorsitzende: MinDir=B4in B=E4rbel Brumme-Bothe
Gesch=E4ftsf=FChrer: Prof. Dr. G=FCnther Wess, Dr. Nikolaus Blum, Dr. Alfons Enhsen
Registergericht: Amtsgericht M=FCnchen HRB 6466
USt-IdNr: DE 129521671

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql