backup of databases which have a mix of MyISAM- and InnoDB-tables
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ür Entwicklungsgenetik Gebäude 35.34 - Raum 208 HelmholtzZentrum münchen bernd.len...@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ünchen Deutsches Forschungszentrum für Gesundheit und Umwelt (GmbH) Ingolstädter Landstr. 1 85764 Neuherberg www.helmholtz-muenchen.de Aufsichtsratsvorsitzende: MinDir´in Bärbel Brumme-Bothe Geschäftsführer: Prof. Dr. Günther Wess, Dr. Nikolaus Blum, Dr. Alfons Enhsen Registergericht: Amtsgericht München 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
MySQL Connector/Python v1.2.3 GA has been released
Dear MySQL users, MySQL Connector/Python v1.2.3 is the new version of the 1.2 release series of the pure Python database driver for MySQL. This is the first GA (General Availability) version of Connector/Python 1.2. MySQL Connector/Python version 1.2.3 is compatible with MySQL Server versions 5.5 and greater. Python 2.6 and 2.7, as well as Python 3.2 and greater are supported. MySQL Connector/Python 1.2.3 is available for download from http://dev.mysql.com/downloads/connector/python/#downloads This release will be available on eDelivery (OSDC) in next month's upload cycle. The ChangeLog file included in the distribution contains a brief summary of changes in MySQL Connector/Python 1.2.3. For a more complete list of changes, see below or online at: http://dev.mysql.com/doc/relnotes/connector-python/en/ Changes in MySQL Connector/Python 1.2.3 (2014-08-22) Functionality Added or Changed * Connector/Python is now compatible with Django 1.7. (Bug #72746, Bug #19225481) Bugs Fixed * Negative timedelta values were incorrectly converted to and from Python. Thanks to Vitali Graf for the patch. (Bug #72493, Bug #18694096) * Fetching results from a prepared statement that returned many columns could produce an error. (Bug #72602, Bug #18742429) * Previously, a RuntimeError exception was raised when a Django application was inactive for a while. Now, the Django backend verifies that the database connection is still valid each time a database request is made. (Bug #72545, Bug #18843153) * Django TimeField values of 00:00:00 were incorrectly converted to NULL because Python considered that value equal to False. (Bug #72732, Bug #18956789) * An exception was raised when a cursor tried to convert LINESTRING data as UTF-8 data. Now such values are returned without decoding. (Bug #73187, Bug #19164627) * With Python 2, Connector/Python could truncate digits of floating-point values. (Bug #73266, Bug #19225481) Documentation Online: http://dev.mysql.com/doc/connector-python/en/index.html The source distribution includes the manual in various formats under the docs/ folder. Reporting Bugs We welcome and appreciate your feedback and bug reports: http://bugs.mysql.com/ Enjoy! On Behalf of the MySQL RE team at Oracle, Sowmya Dass
Re: backup of databases which have a mix of MyISAM- and InnoDB-tables
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
Re: backup of databases which have a mix of MyISAM- and InnoDB-tables
XTrabackup can handle both InnoDB and MyISAM in a consistent way while minimizing lock time on MyISAM tables ... http://www.percona.com/doc/percona-xtrabackup/2.1/ -- Hartmut Holzgraefe, Principal Support Engineer (EMEA) SkySQL - The MariaDB Company | http://www.skysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
