Do not mess with the mysql db. There is a lot more going on there than just the users table. Your best bet is the "SHOW GRANTS FOR" command. I have a lot of CLI maintenance aliases and functions in my .bashrc (actually my .bash_aliases that I source in my .bashrc). This function:
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;}'
}
The first mysql command uses SQL to generate valid SQL which is piped to the second mysql command. The output is then piped through sed to add pretty comments.
The $@ in the command will allow you to call it as:
mygrants --host=prod-db1 --user=admin --password=secret
You can use your full unix tool kit on this like so:
mygrants --host=prod-db1 --user=admin --password=secret | grep rails_admin | mysql --host=staging-db1 --user=admin --password=secret
That is THE right way to move users. Your MySQL ACL is modified with pure SQL.
Yes.
Setup replication to a second machine. When you need to do a backup, you can lock the secondary machine, perform mysqlhotcopy or mysqldump, and then unlock it. It will catch back up with your master, and you never have to take the master offline.
You could even do this on the same machine, if you don't mind doubling the write I/O, but ideally you should back it up in real-time to a second physical server, and take your snapshot backups as often as you need without disturbing your production server.
It's also theoretically possible to restore a database using a known state and binlogs. I've never done it, so please investigate first, but you could backup a known state of your database, then just backup all new binlogs and replay them if you ever needed to restore. Since binlogs are written linearly, rsyncing new binlogs to a remote computer would be very fast.
Edit: Indeed, it looks like using binlogs for backup is documented.
This question is highly related
Best Answer
Unfortunately, the way Percona performs XtraBackup procedure includes running something like rsync against a running production server and then applies InnoDB crash recovery against a tmp folder (the backup directory) with the option to generate ib_logfile0 and ib_logfile1 that can be drop-in replacements for the ones in production. That data is just a backup.
If all your data is InnoDB, you are better off just doing a mysqldump against production using the --single-transaction parameter. Even better, you can do parallel mysqldumps of databases or inidivdual tables again using --single-transaction parameter on each mysqldump process.