MySQL export user with semi-colon ";" on the end

Question

I am trying to set up automatic export of user accounts from one server to import into another. I am running the following command;

$ mysql -u root -p -B -s -e "show grants for 'myuser'@'localhost';" > user_export.sql

$ cat user_export.sql

GRANT USAGE ON *.* TO 'myuser'@'localhost' IDENTIFIED BY PASSWORD '*IHIHIHOOEHOEHOH'
GRANT SELECT, INSERT, UPDATE, DELETE ON `mydb`.* TO 'myuser'@'localhost'

The problem here is that these SQL statements don't end with ; so I can't import it on another remote server using:

$ mysql -u root -p -D mydb < user_export.sql

I know I could write bash one-liner to wrap this and add a semi-colon but I am hoping there is away to get these statements with the semi-colon on the end to keep this nice and simple, and clear.

Answer 1

In addition to the fact that there's not any "official" way to do this, there's another potential problem with the approach: there's no mechanism that will ever revoke any privilege for a user.

Today, user 'foo' at host 'bar' has SELECT, UPDATE, and DELETE.

mysql> SHOW GRANTS FOR 'foo'@'bar';
+-------------------------------------------------------------------------------+
| Grants for foo@bar                                                            |
+-------------------------------------------------------------------------------+
| GRANT SELECT, UPDATE, DELETE ON *.* TO 'foo'@'bar' IDENTIFIED BY PASSWORD '*' |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Oops, they shouldn't have delete, so you fix that. Tomorrow's file contains the correct grant statement:

mysql> GRANT SELECT, UPDATE ON *.* TO 'foo'@'bar';
Query OK, 0 rows affected (0.00 sec)

After that's processed, what privileges does the user have?

mysql> SHOW GRANTS FOR 'foo'@'bar';
+-------------------------------------------------------------------------------+
| Grants for foo@bar                                                            |
+-------------------------------------------------------------------------------+
| GRANT SELECT, UPDATE, DELETE ON *.* TO 'foo'@'bar' IDENTIFIED BY PASSWORD '*' |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)

The GRANT statement is additive, so it only specifies what privileges should be added if not present.

I would think, then, that a better approach might be what @Mat was (I think) implying... you should script the privilege changes you want to make, then execute that script against all of the servers you want to update, rather than trying to treat one server as permissions master.

Answer 2

What is unusual about this is that there are two ways to do this:

OPTION #1 : Use pt-show-grants / mk-show-grants

Percona already made this a simple tool that appends the semicolon (;) to every SQL statement

OPTION #2 : Emulate pt-show-grants / mk-show-grants

Simply use the sed command to append the semicolon (;)

mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql

Give it a Try !!!

I have mentioned these before:

• Jul 26, 2011 : Restoring an old backup to latest MySQL release
• Dec 24, 2012 : Backup and restore "mysql" database