MySQL privileges export via CLI

Here is an awesome tidbit for getting permissions from your db to transfer to another...

# how-can-i-export-the-privileges-from-mysql-and-then-import-to-a-new-server
# mygrants --host=prod-db1 --user=admin --password=secret | grep rails_admin | mysql --host=staging-db1 --user=admin --password=secret
# mygrants > grants.sql
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;}'
}

Popular Posts