Bug #31815 mysqld cannot be backed-up properly when triggers on cluster tables are used

• Submitted: 24 Oct 2007 14:45
• Modified: 15 Oct 2009 8:35
• Reporter: Oli Sennhauser
• Status: Verified
• Category: MySQL Server: Command-line Clients
• Severity: S4 (Feature request)
• Version: 5.1.x ff.
• OS: Any
• CPU Architecture: Any

[24 Oct 2007 14:45] Oli Sennhauser

Description:
When we have a MySQL Cluster + triggers + SP/SF + views + many users in the DD I do not see any possibility to do a proper backup/restore of the mysqld when the cluster tables should be backed up with ndb_mgm!

How to repeat:
mysqldump -u root --database test --> user, SP/SF are missing and tables are exported (what we do not want)
mysqldump -u root --no-data --database test --> user SP/SF are missing and table create commands are exported (what we do not want)
mysqldump -u root --triggers ignore-table=test.test test --> triggers are missing
mysqldump -u root --database mysql --> triggers and views are missing

Suggested fix:
add a flag in mysqldump to skip table only or to include triggers when tables are not there

[30 Oct 2007 11:00] Oli Sennhauser

Hi Geert

I have verified your stuff and it seems to work well...
To have it proper we need the following stuff:

mysqldump -u root --routines --triggers --skip-add-drop-table --no-data --database backup | sed 's/CREATE TABLE/CREATE TABLE IF NOT EXISTS/g' > dd_backup.sql
echo "use mysql;" >> dd_backup.sql
mysqldump -u root mysql user >> dd_backup.sql
echo "flush privileges;" >> dd_backup.sql

Thanks Oli