Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
mysql per database backup without mysql.event warning
#!/bin/sh -
# The important part is not to put the password as an argument to the mysql
# commands, i.e. don't use the -p option. Instead use a .my.cnf file in the home
# dir of the user you are running this script as, i.e. /root/.my.cnf if running
# as root. Make the file readable for the owner only: chmod 400 /root/.my.cnf
# See: http://stackoverflow.com/questions/17829659/securing-backup-credentials-for-mysqldump/17844997#17844997
umask 007
renice 10 $$ >/dev/null
BACKUPPATH=/home/backup/last
for db in `mysql --batch --skip-column-names --execute="SHOW DATABASES" | egrep -v 'performance_schema|information_schema'`; do
/usr/bin/nice /usr/bin/mysqldump --events --ignore-table=mysql.event --single-transaction --quick --extended-insert "$db" | gzip > "${BACKUPPATH}/mysql_${db}.sql.gz"
done
@shadowbooker

This comment has been minimized.

Copy link

shadowbooker commented Dec 21, 2013

This does't work. Unknown option '--events'

@rbahassine

This comment has been minimized.

Copy link

rbahassine commented Oct 8, 2015

I am getting this error :
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
But i dont want to put the root password in the script. how did you do?

@timkuijsten

This comment has been minimized.

Copy link
Owner Author

timkuijsten commented Feb 9, 2017

@shadowbooker: just updated and tested with mysql 5.7

@rbahassine: use a .my.cnf file, see the comment I've added to the script. Do not use -p like in https://gist.github.com/ryanburnette/9456453, this is insecure.

@bitslv

This comment has been minimized.

Copy link

bitslv commented Jun 20, 2017

I suggest altering the script to add directory timestamp to allow for daily backups.

umask 007

renice 10 $$ >/dev/null

TIMESTAMP=$(date +"%F")

BACKUPPATH="/home/backup/$TIMESTAMP"

for db in mysql --batch --skip-column-names --execute="SHOW DATABASES" | egrep -v 'performance_schema|information_schema'; do
/usr/bin/nice /usr/bin/mysqldump --events --ignore-table=mysql.event --single-transaction --quick --extended-insert "$db" | gzip > "${BACKUPPATH}/mysql_${db}.sql.gz"
done

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.