Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

When I originally set up my master-to-master replication, I used:

binlog-ignore-db=mysql

and have been syncing the user accounts and grants manually. This is simply how it was done in the how-to I was using at the time. But is there any reason why I shouldn't remove this line and allow the mysql database itself to replicate as well?

If so: Before I make the change, besides making sure that all the grants are the same on both (or better stated, that the entire mysql database is identical), is there anything else I should double-check or be aware of?

share|improve this question

2 Answers 2

up vote 11 down vote accepted

It is entirely possible to give yourself mysql permissions without knowing SQL GRANT commands.

Example : Here is to create your own user with full privileges using SQL GRANT from anywhere called superdba with a password of ClarkKent:

GRANT ALL PRIVILEGES ON *.* TO superdba@'%' IDENTIFIED BY 'ClarkKent' WITH GRANT OPTION; 

Here is how you can do this without the GRANT command:

First of all, here is mysql.user for MySQL 5.1.51

mysql> desc mysql.user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field                 | Type                              | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host                  | char(60)                          | NO   | PRI |         |       |
| User                  | char(16)                          | NO   | PRI |         |       |
| Password              | char(41)                          | NO   |     |         |       |
| Select_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv          | enum('N','Y')                     | NO   |     | N       |       |
| File_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv            | enum('N','Y')                     | NO   |     | N       |       |
| References_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Event_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type              | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher            | blob                              | NO   |     | NULL    |       |
| x509_issuer           | blob                              | NO   |     | NULL    |       |
| x509_subject          | blob                              | NO   |     | NULL    |       |
| max_questions         | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates           | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections       | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections  | int(11) unsigned                  | NO   |     | 0       |       |
+-----------------------+-----------------------------------+------+-----+---------+-------+
39 rows in set (0.00 sec)

Just execute these SQL commands:

INSERT INTO mysql.user SET
Host='%',User='superdba',Password=PASSWORD('ClarkKent'),
Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y',
Create_priv='Y',Drop_priv='Y',Reload_priv='Y',Shutdown_priv='Y',
Process_priv='Y',File_priv='Y',Grant_priv='Y',References_priv='Y',
Index_priv='Y',Alter_priv='Y',Show_db_priv='Y',Super_priv='Y',
Create_tmp_table_priv='Y',Lock_tables_priv='Y',Execute_priv='Y',
Repl_slave_priv='Y',Repl_client_priv='Y',Create_view_priv='Y',
Show_view_priv='Y',Create_routine_priv='Y',Alter_routine_priv='Y',
Create_user_priv='Y',Event_priv='Y',Trigger_priv='Y';
FLUSH PRIVILEGES;

That INSERT is a legal SQL statement that can land in a binary log. Do you want someone to run this and have a visible password travel along the network? to sit in a binary log on the master? to sit in a relay log on the slave?

Having this directive

binlog-ignore-db=mysql       

prevents giving away mysql permissions using such SQL. However, GRANTs cannot be stopped this way. Therefore, make sure you perform grants like this:

SET SQL_LOG_BIN=0;
GRANT ...

to prevents the GRANTs from traversing from master to slave.

share|improve this answer
    
Oh, wait... I'm confused: Are you saying that GRANT statements replicate no matter what the binlog-ignore-db conf says? I know if I make a new user on one server, that user is not appearing on the other. So perhaps you mean that the GRANT gets transmitted and stored but simply not applied on the slave? –  dlo Sep 12 '11 at 21:43
    
GRANTS get applied without any road blocks. That is why you must run SET SQL_LOG_BIN=0; in the mysql session to prevent any GRANT commands from entering the binary logs. –  RolandoMySQLDBA Sep 12 '11 at 21:45
    
GRANTS get applied without any road blocks on master and slave. That is why you must run SET SQL_LOG_BIN=0; in the mysql session to prevent any GRANT commands from entering the binary logs and traversing over to the relay logs of the slave. –  RolandoMySQLDBA Sep 12 '11 at 21:46
1  
Ok. Just to be clear for others, I believe you're warning me about GRANTS exposing passwords in transit (which can be addressed with the SSL suggestion by Scott) and being stored cleartext in the binlog. You are suggesting that if those are concerns for me, then I should use both binlog-ignore-db=mysql and SET SQL_LOG_BIN=0;. But if I'm ok with those conditions, there's no particular problem with replicating GRANTs by removing the binlog-ignore-db line. Correct me if this is an inaccurate summary. –  dlo Sep 12 '11 at 22:58
    
@dlo - Affirmative !!! –  RolandoMySQLDBA Sep 13 '11 at 0:23

I've had no problems with replicating the mysql database, but then again, my infrastructure lends to an additional level of security with firewalls and proxy appliances where it's impossible for anyone other than the infrastructure folks to even connect on any of the ports MySQL uses. It does add an extra level of convenience knowing that you only have to grant permissions once and have it replicated. When it boils down to it, as long as you have properly set the host as to not expose it to anyone other than the intended (e.g. you, the slave, etc...), you should be fine.

If you're overly concerned with man in the middle interceptions, there's always the option of sending replication across over SSL.

share|improve this answer

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.