Backup MySQL users and permissions

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

backup a file you get by running "mysqldump mysql >filename"