Home / server / Questions / 310632
Accepted
dlo
Asked: 2011-09-13 11:43:14 +0800 CST

Is it ok to replicate the `mysql` database?

  • 772

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?

mysql mysql-replication

2 Answers

  1. Best Answer

    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.

    • 12

  2. 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.

    • 4