15

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?

dlo
  • 451
  • 1
  • 4
  • 14
  • Over the last 4.5 years I've constantly struggled with replication problems when upgrading mysql, even on minor upgrades. The reason is that `apt-get upgrade` (actually, the mysql-server .deb postinst script) executes `ALTER TABLE user` statements that can't be replicated. I posted a working solution at http://serverfault.com/questions/686025/mysql-package-upgrades-debian-apt-get-always-break-master-master-replication-d – dlo May 08 '15 at 05:12

2 Answers2

12

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.

RolandoMySQLDBA
  • 16,364
  • 3
  • 47
  • 80
  • 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 00:23
  • For all others, GRANTs cannot be filtered by the binlog-ignore-db command because the filter catches only strict DML (INSERT, UPDATE, DELETE). Unfortunately, GRANT does not fall under this category, so GRANTs always make their way through. Thus, any DML directly updating any table in the mysql schema can be blocked. GRANTs cannot.The extra information about passwords is just additional precautionary advice. – RolandoMySQLDBA Sep 13 '11 at 00:27
  • According to https://www.percona.com/blog/2009/05/14/why-mysqls-binlog-do-db-option-is-dangerous/, `binlog-ignore-db` shouldn'be used. Also you concentrated only on the not very well documented issue with replication and GRANT (where the password issue could be mitigated somewhat by using the password hash), I was wondering are there any other possible pitfalls of _not_ ignoring `mysql` db from the binary log? I keep hearing "best practice is not to replicate `mysql`"... – minusf Apr 18 '19 at 11:57
4

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.

Scott
  • 397
  • 1
  • 4
  • 14