Bug #2791
Backup: pre-backup-data event fails if mysqldump can't acquire lock
Status: | CLOSED | Start date: | ||
---|---|---|---|---|
Priority: | Normal | Due date: | ||
Assignee: | - | % Done: | 100% | |
Category: | nethserver-mysql | |||
Target version: | v6.5 | |||
Security class: | Resolution: | |||
Affected version: | v6.5-final | NEEDINFO: | No |
Description
In rare cases, mysqldump can fail dumping the databases because some locks can't be acquired.
The error is something like this:
mysqldump: Got error: 1016: Can't open file: './sogo/sogopfonti0043296bda1_acl.frm' (errno: 24) when using LOCK TABLES
To avoid this kind of errors, the best practice seems to be using mysqldump with --single-transaction
and --quick
options (see man mysqldump).
Associated revisions
Backup: avoid table locking. Refs #2791
History
#1 Updated by Giacomo Sanchietti about 7 years ago
- Status changed from NEW to TRIAGED
- Target version set to v6.5
- % Done changed from 0 to 20
- Affected version set to v6.5
#2 Updated by Giacomo Sanchietti about 7 years ago
Add documentation about this implementation can impact on MyISAM tables:
"When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this option may still change state"
We should also advise to stopo/start critical services in post and pre backup.
#3 Updated by Davide Principi about 7 years ago
Could we change the default MySQL engine?
http://dev.mysql.com/doc/refman/5.1/en/storage-engine-setting.html
# rpm -q mysql mysql-5.1.73-3.el6_5.x86_64
#4 Updated by Giacomo Sanchietti about 7 years ago
I'd like to stick with our policy: configure as upstream.
In this case the default engine option (default-storage-engine) is not set, and the value is delegated to the packager.
#5 Updated by Giacomo Sanchietti about 7 years ago
- Status changed from TRIAGED to ON_DEV
- Assignee set to Giacomo Sanchietti
- % Done changed from 20 to 30
#6 Updated by Giacomo Sanchietti about 7 years ago
- Status changed from ON_DEV to MODIFIED
- % Done changed from 30 to 60
#7 Updated by Giacomo Sanchietti about 7 years ago
- Status changed from MODIFIED to ON_QA
- Assignee deleted (
Giacomo Sanchietti) - % Done changed from 60 to 70
- nethserver-mysql-1.0.5-1.0git2c4e3cf2.ns6.noarch.rpm
- Install mysql and backup data:
yum install nethserver-mysql nethserver-backup-data signal-event pre-backup-data
- Create a database with some data:
mysql -e "create database test" mysql test -e 'create table `test` ( `key` varchar(20), `value` varchar(20))' mysql test -e "GRANT ALL PRIVILEGES ON test.* TO test@localhost IDENTIFIED BY 'DQAFyzsWsxfZPJuz'" mysql test -e "INSERT INTO test VALUES ('foo','bar')"
- Execute pre backup and verify it returns 0
signal-event pre-backup-data
- Check the test database is created:
/var/lib/nethserver/backup/mysql/test.dump
#8 Updated by Davide Principi about 7 years ago
- Assignee set to Davide Principi
#9 Updated by Davide Principi about 7 years ago
- Affected version changed from v6.5 to v6.5-final
#10 Updated by Davide Principi about 7 years ago
- Status changed from ON_QA to VERIFIED
- Assignee deleted (
Davide Principi) - % Done changed from 70 to 90
VERIFIED
test.dump
was created. BTW table type is MyISAM.
#11 Updated by Davide Principi almost 7 years ago
- Status changed from VERIFIED to CLOSED
- % Done changed from 90 to 100
In nethserver-updates:
nethserver-mysql-1.0.6-1.ns6.noarch.rpm