Backup MySQL users and permissions
I'm setting up backup procedures on my server. I'm going to backup all files, mysql databses, etc. I'm wondering if there's a file or place I can backup in order to just be able to "restore" mysql users?
The Mysql user is in the table user of database Mysql, for backup this table you could do this :
mysqldump -u root -p mysql user > UserTableBackup.sql
For backup all database of mysql and create a file for each database you could do your own shell script, following some line of code that can help you :
# Get all database list first
DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')"
echo `date +%Y%m%d_%H_%M_%S` - Backup Mysql Database Started >> /home/Backup/Backup.log
for db in $DBS
do
skipdb=-1
if [ "$IGGY" != "" ];
then
for i in $IGGY
do
[ "$db" == "$i" ] && skipdb=1 || :
done
fi
if [ "$skipdb" == "-1" ] ; then
FILE="$MBD/$db.$HOST.$NOW.gz"
# do all inone job in pipe,
# connect to mysql using mysqldump for select mysql database
# and pipe it out to gz file in backup dir :)
echo `date +%Y%m%d_%H_%M_%S` - Backup "$db.$HOST.$NOW.gz" Started >> /home/Backup/Backup.log
$MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS --default-character-set=utf8 $db | $GZIP -9 > $FILE
echo `date +%Y%m%d_%H_%M_%S` - Backup "$db.$HOST.$NOW.gz" Completed ! >> /home/Backup/Backup.log
fi
done
The mysql
database itself contains all the data needed to restore users and permissions. Just make sure your backup script backs up the mysql
database and you should be able to restore it easily.