Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm using MySQL 5.5 + Opensuse 12.3 . After face an issue with mysqldump (appear be a bug with OpenSuse Mysql distribution) I have looking for others backup solution.

I found the xtrabackup from percona which is very nice solution . I tested it with successfully to backup and restore .

But, it don't appear to be complete as I expected , they don't backup the users/grants and the configuration file (my.cnf)...
With mysqlpdump I user this shell script:

  mysql -B -N $@ -e "SELECT DISTINCT CONCAT(
    'SHOW GRANTS FOR ''', user, '''@''', host, ''';'
    ) AS query FROM mysql.user" | \
  mysql $@ | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'

and with xtrabackup I believe I will keep using this "complement" .

Looking at xtrabackup documentation, I found nothing about this subject.

What I need to know:

  1. There is some way to include this (grant and my.cnf) into xtrabackup automatically?
  2. If not, running my script to get the grants and copy of my.cnf is enougth as backup from my database? or I missing something here?
share|improve this question
up vote 1 down vote accepted

Your backup solution for MySQL User Grants is fine.

I have been advocating doing that for years:

Also, please notice that Percona created pt-show-grants and xtrabackup. It is curiously funny that pt-show-grants has not been integrated into xtrabackup already (Hey I could be wrong. After all, a physical backup of the mysql schema should be very adequate for a Backup of Grants). Perhaps, one of the following is the reason:

  • Business Decision to keep Text version of Grants separate from the Backup
  • Potential Security Reasons (should anyone get a hold of the Backup)
  • No one ever thought of it before

Going back to you original question, copies of the User Grants in SQL and the my.cnf are sufficient.

share|improve this answer
Thanks Rolando, I restore my backup created with xtrabackup (innobackupex utility) and restored at other server, where I just need to copy my.cnf and reapply my grants (the script I post and pt-show-grants was created exactly the same commands when compared with diff) and all appear be ok. – ceinmart Apr 29 '13 at 17:21

Your Answer


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.