Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 2 down vote accepted

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
share|improve this answer
    
That's what I was looking for - thanks! –  Webnet Aug 21 '10 at 15:51

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.

share|improve this answer
    
Do you know of any place I can get a good MySQL backup script that creates an individual file for each database? Isn't it bad practice to backup the database itself? –  Webnet Aug 20 '10 at 22:56
    
Not at all! MySQL makes it easy to do so and it's encouraged. It allows you to restore a DB server to a fully functional state with a full backup. automysqlbackup has served me well in the past. I would suggest using that: sourceforge.net/projects/automysqlbackup –  vmfarms Aug 20 '10 at 23:27

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.