Show All Grants

Useful when migrating. Point at source DB instance, retrieve grants using this script, pipe through the Add a semicolon in PERL script (below), and voila, recreate grants on destination instance.

 mysql --batch --skip-column-names -e "SELECT user, host FROM user" mysql 
) | while read user host 
  echo "# $user @ $host"
  mysql --batch --skip-column-names -e"SHOW GRANTS FOR '$user'@'$host'"

Quickie to add a semicolon to end of non-commented lines (useful paired with the SHOW GRANTS bash script)

#!/usr/bin/perl --
open DATA, "$ARGV[0]";
while () {
    if ("$liner" =~ /^#/) {
      print $liner;
    } else {
      print "$liner" . ";n";
close DATA;

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: