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:
- There is some way to include this (grant and my.cnf) into xtrabackup automatically?
- 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?
Best Answer
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 endDec 24, 2012
: Backup and restore "mysql" databaseJun 13, 2012
: Fastest way to move a database from one server to anotherApr 12, 2012
: Cannot GRANT privileges as rootApr 09, 2012
: Mysql users deletedJun 26, 2011
: Restoring an old backup to latest MySQL releaseAlso, please notice that Percona created pt-show-grants and xtrabackup. It is curiously funny that
pt-show-grants
has not been integrated intoxtrabackup
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:Going back to you original question, copies of the User Grants in SQL and the
my.cnf
are sufficient.