Home / server / Questions / 172950
Accepted
Ben
Asked: 2010-08-21 14:33:37 +0800 CST

Backup MySQL users and permissions

  • 772

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?

backup mysql

4 Answers

  1. Best Answer

    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
    • 4

  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.

    • 1

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

    • 0

  4. In Addition the other answers, You can use this script just to export all of your users and their privileges except the root user:

    mysql -BNe "select concat('\'',user,'\'@\'',host,'\'') from mysql.user where user != 'root'" | \
while read uh; do mysql -BNe "show grants for $uh" | sed 's/$/;/; s/\\\\/\\/g'; done > grants.sql

    So, TNX to: https://stackoverflow.com/a/9410698/926639

    • 0