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.

#!/bin/bash 
( 
 mysql --batch --skip-column-names -e "SELECT user, host FROM user" mysql 
) | while read user host 
do 
  echo "# $user @ $host"
  mysql --batch --skip-column-names -e"SHOW GRANTS FOR '$user'@'$host'"
done 

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 () {
  $liner=$_;
    if ("$liner" =~ /^#/) {
      print $liner;
    } else {
      chomp($liner);
      print "$liner" . ";n";
    }
 }
close DATA;

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Connecting to %s

%d bloggers like this: