Save, restore and copy MySQL accounts and privileges (mysqldumpgrants)

I just stumbled across a post on the forums where somebody asked for a good way to copy MySQL grants between two servers. Since about two years I mostly use a little Perl script mysqldumpgrants I've written back then for such and other related tasks. The post on the forums was still unanswered after almost three months, so I thought it might be helpful to release this little script albeit its simplicity.

When you call mysqldumpgrants without any arguments it will look up the connection information (host, port, user and password) in your .my.cnf file (of course you can also specify them on the command line, in the same way as for the other mysql command line tools), connect to the server and write all the grant statements (plus some comments) to standard output, like so:

-- Grants for pattern %@% extracted by /root/bin/mysqldumpgrants
-- 2006-04-18 16:30:15 (MySQL 5.0.20-standard-log)

-- jobs@%
GRANT USAGE ON *.* TO 'jobs'@'%' IDENTIFIED BY PASSWORD 'xxx';
GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES, EXECUTE
    ON `jobs`.* TO 'jobs'@'%';

...

I usually extract such a file together with my backups. You never know when you might need it (and I prefer the GRANT/REVOKE statements to dealing directly with the user tables).

mysqldumpgrants > grants.sql

It also allows me to quickly hook up a text editor, change something and feed that part back to MySQL.

vi/emacs grants.sql
mysql < grants.sql

And for the original question: Of course you can just pipe the output of mysqldumpgrants to a mysql command line client to copy the grants to another server:

mysqldumpgrants | mysql -h remote.host -u remote.user -p

Let me just give you the relevant sections of the perldoc for a complete overview of the few options:

SYNOPSIS
      mysqldumpgrants [options] [user_pattern[@host_pattern]]

DESCRIPTION
      mysqldumpgrants dumps the grants of a MySQL database as
      valid SQL commands.

      mysqldumpgrants reads the user, password, host and port
      settings from your .my.cnf file by default. These settings
      can be overriden with the options provided on the command
      line.

      The optional argument user_pattern[@host_pattern] controls
      which accounts should be dumped (defaults to all). You can
      use the usual MySQL wildcards _ and %.

      You can redirect the output of mysqldumpgrants to a file
      to backup your user accounts or to easily edit them in a
      text editor or you can pipe it to the mysql command line
      client to copy them directly to another server, like so:

        mysqldumpgrants -h mysql1 a% | mysql -h mysql2

      This would copy all the user accounts starting with the
      letter 'a' from host mysql1 to mysql2 (given both servers
      use the same credentials for login and those are stored in
      a .my.cnf file).

OPTIONS
      -u, --user=#
          user for database login

      -p, --password
          ask for password (you can't provide a password on the
          command line for security reasons, use a .my.cnf file
          instead)

      -h, --host=#
          hostname of database server to connect to

      -P, --port=#
          port to use when connecting to database server

      -d, --drop-user
          adds a DROP USER command for every dumped user just
          before the GRANT commands

      -?, --help
          display this help page

The --drop-user (or -d) option comes in handy if you want to make sure that previous grants with the same user/host combination get really overwritten and not just combined. This will only work with MySQL 5.0 or above (before you had to revoke all the grants manually). The rest of the script should work fine with older releases (of course, as it was written back in 2004), you just have to pay attention if you try to load privileges from a newer server into an older release as some of the privileges might not yet be supported.

It's a simple script, but I use it almost every week, and actually I think this feature should be added to mysqldump (or has it been added already and I just missed it because I used my own script all the time?).

And in case you have to work on a server with a broken Perl DBI/DBD::mysql installation as I had to recently, you can use mysqldumpgrants.sh, a tiny shell script, as a simple replacement:

#/bin/sh
while read GRANT
  do
  echo "-- $GRANT"
  mysql --skip-column-names -Be "SHOW GRANTS FOR $GRANT" | sed 's/$/;/'
  echo
done <<< "`mysql --skip-column-names -Be \
\"SELECT CONCAT('''', user, '''@''', host, '''') FROM mysql.user ORDER BY user, host\"`"