Transfer MySQL user grants to another server
- Details
- Kategorie: Blog
- Veröffentlicht: Donnerstag, 07. Mai 2020 10:57
- Geschrieben von Martin Wilmes
- Zugriffe: 2459
How to transfer a set of user's grants from one mysql server to another?
There is an answer on serverfault.
The essential part is to define this function in bash:
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;}'
}
edit the skript and remove all double backslashes (\\) and then use it like this:
mygrants --host=prod-db1 --user=admin --password=secret | grep rails_admin | mysql --host=staging-db1 --user=admin --password=secret