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

by jwbensley   Last Updated September 11, 2019 21:06 PM

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.



Answers 2


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 [email protected]                                                            |
+-------------------------------------------------------------------------------+
| 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 [email protected]                                                            |
+-------------------------------------------------------------------------------+
| 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.

Michael - sqlbot
Michael - sqlbot
March 24, 2013 21:32 PM

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:

RolandoMySQLDBA
RolandoMySQLDBA
March 24, 2013 22:30 PM

Related Questions


MySQL user export PASSWORD is showing up as <secret>

Updated February 16, 2019 01:06 AM


Oracle - restore a single table

Updated August 07, 2015 14:02 PM

mysql export with partial data

Updated August 28, 2016 08:04 AM

How to export views and import it in other DB?

Updated June 06, 2016 08:02 AM