-
-
Save timkuijsten/6067107 to your computer and use it in GitHub Desktop.
#!/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: 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.
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
@timkuijsten Can you explain what the purpose of renice 10 $$ >/dev/null
is? I see that it modifies the process priority, but I don't understand why you pipe it to dev null?
@timkuijsten Can you explain what the purpose of renice 10 $$ >/dev/null is? I see that it modifies the process priority, but I don't understand why you pipe it to dev null?
On some systems renice outputs some informational text on stdout so this is discarded.
This is an example on Debian without redirect:
$ renice 10 $$
9884 (process ID) old priority 0, new priority 10
I always try to make a script generate no output on succes so that I can add it to cron without having to redirect stdout and easily detect if something goes wrong after it has been installed.
Ah, makes more sense now. Thank you
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?