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?

1 Answer 1


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.

  • 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, 2013 at 17:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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