Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Wouldn't piping through sed s/$/;/ be simple enough? –  Mat Mar 23 '13 at 16:51
    
Yes but I was trying to find a way to natively export a user from one server to another. Just a simple method of copying users. –  jwbensley Mar 23 '13 at 17:14
    
What's complicated about adding a 12 character pipe in there? If you intend to do that regularly, you should be scripting it anyway. –  Mat Mar 23 '13 at 17:16
    
@Mat I guess this will have to do, do you want to post this as an answer and I will mark it as correst? –  jwbensley Mar 24 '13 at 0:18
add comment

2 Answers

up vote 1 down vote accepted

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:

share|improve this answer
    
Thank you for accepting :-) –  RolandoMySQLDBA Apr 4 '13 at 16:07
add comment

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.

share|improve this answer
    
+1 Good point about it not covering revoke! –  jwbensley Apr 4 '13 at 16:05
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.