xtrabackup backup grants?

Question

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:

mygrants()
{
  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?

Answer 1

Your backup solution for MySQL User Grants is fine.

I have been advocating doing that for years:

• Mar 24, 2013 : MySQL export user with semi-colon ";" on the end
• Dec 24, 2012 : Backup and restore "mysql" database
• Jun 13, 2012 : Fastest way to move a database from one server to another
• Apr 12, 2012 : Cannot GRANT privileges as root
• Apr 09, 2012 : Mysql users deleted
• Jun 26, 2011 : Restoring an old backup to latest MySQL release

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.