Am 22.08.2014 um 19:40 schrieb Lentes, Bernd: > 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
why that complex? just setup replication because you have a lot of benefits: * in case your master crashs and the FS got damaged you have a real-time "backup" * for backups you can stop the slave, tar the whole datadir and start the slave * after it is restarted it pulls any change happened on the master due backup * the backup is likely smaller than verbose sql dumps * you do not need to care about table types and what not else
signature.asc
Description: OpenPGP digital signature
