Backing up All Databases in MySQL without the Warnings

Backing up MySQL used to be quick, easy and silent using mysqldump, but more recent versions of MySQL produce the following nagging warning:

mysqldump -u root -p --all-databases --single-transaction > bak.sql
Enter password:
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.

If you'd like to restore mysqldump to its normally quiet operation you have two options. On option is to add -E or --events to your command so that the event table is not skipped. Or tell mysqldump to ignore the table:

mysqldump -u root -p --all-databases --single-transaction --ignore-table=mysql.event > bak.sql
Enter password:

The events table is a non-standard table added in MySQL 5.1.6 and allows "cron-like" support inside of MySQL. If you're not taking advantage of this feature then it's safe to ignore. I perform my backups as a nightly cronjob using the following script:




mv $BASE/$FILE.8.gz $BASE/$FILE.9.gz
mv $BASE/$FILE.7.gz $BASE/$FILE.8.gz
mv $BASE/$FILE.6.gz $BASE/$FILE.7.gz
mv $BASE/$FILE.5.gz $BASE/$FILE.6.gz
mv $BASE/$FILE.4.gz $BASE/$FILE.5.gz
mv $BASE/$FILE.3.gz $BASE/$FILE.4.gz
mv $BASE/$FILE.2.gz $BASE/$FILE.3.gz
mv $BASE/$FILE.1.gz $BASE/$FILE.2.gz
mv $BASE/$FILE.0.gz $BASE/$FILE.1.gz

mysqldump -u $USER --password=$PASS --all-databases --single-transaction --ignore-table=mysql.event | gzip --best -c > $BASE/$FILE.0.gz

I also use a dedicated backup user account in MySQL that only has permissions to read information out of my databases. Check out "Create a MySQL User for Backups" for the commands to create this user.