Taking MySQL dumps with a twist

Taking mysql dumps is pretty straightforward, but what if you needed to take a dump excluding certain tables? For this, you have two options: 1. use the –ignore-table option: mysqldump -u username -p database –ignore-table=database.table1 –ignore-table=database.table2 > database.sql This works fine as long as you don’t have too much tables to exclude. 2. The second … Continue reading Taking MySQL dumps with a twist

Mysql temporary tables and replication

MySQL and replication, it has always been a touchy subject. Overall, things work properly. Non the less, on a certain project, we experienced regular replication failures: Error ‘Can’t create table ‘db.temp_table’ (errno: -1)’ on query. Default database: ‘db’. Query: ‘CREATE TEMPORARY TABLE temp_table … Basically the errors clearly points out that the temporary MySQL table … Continue reading Mysql temporary tables and replication

mysqldump: 1044 Access denied when using LOCK TABLES

A few days ago, I tried to make a backup using the mysqldump command and got the following error: mysqldump: Got error: 1044: Access denied for user ‘root’@’localhost’ to database ‘information_schema’ when using LOCK TABLES A quick workaround is to pass the –-single-transaction option to mysqldump: $ mysqldump –single-transaction -u user -p DBNAME > backup.sql … Continue reading mysqldump: 1044 Access denied when using LOCK TABLES