MySQL 5.6 master info repository: documentation, behavior, and reality

MySQL 5.6 includes a couple new options that allow you to store replication master and relay information in tables instead of in the respective master.info and relay-log.info files that have been used historically. This appears to be partly under the guise of increased security, particularly in the case of the master info "repository", if the "Note" we get from CHANGE MASTER TO is to be believed. However, it's important to note that using --master-info-repository=TABLE really offers no security benefit of any kind.

Filesystem permissions should restrict access to anything kept in the MySQL data directory, be it a master.info file or the table-based master info repository. If you can read one, you can probably read the other. It's pretty common these days to run with innodb-file-per-table, and in fact that is the new default in MySQL 5.6. That means that the contents of the mysql.slave_master_info are just as visible to someone with filesystem access as the master.info file would be.

mysql 5.6.10-log (root) [test]> set global master_info_repository='table';
Query OK, 0 rows affected (0.00 sec)

mysql 5.6.10-log (root) [test]> change master to master_host='127.0.0.1', master_port=4001, master_user='root', master_password='abf3$**92kjh1lffjmavn', master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.07 sec)

mysql 5.6.10-log (root) [test]> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1759
Message: Sending passwords in plain text without SSL/TLS is extremely insecure.
*************************** 2. row ***************************
  Level: Note
   Code: 1760
Message: Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.
2 rows in set (0.00 sec)

Note first of all that master_info_repository a not a read-only variable, as the documentation claims (bug #68600).

Second, we get a "Note" about master.info being unsafe, even though we're not even using master.info (bug #68599). This note is really poorly written, in my opinion. For one thing, "see the MySQL Manual" is not a very specific suggestion, when instead a specific section or details about alternative functionality could be mentioned instead. Ultimately, though, I think perhaps this Note is meant to be an admonition not to use either the FILE- or TABLE-tased master info repositories and instead to use the new USER and PASSWORD arguments to START SLAVE?

Third, we can now see the password using normal SELECT statements, which wasn't possible before...

mysql 5.6.10-log (root) [test]> select User_name, User_password from mysql.slave_master_info;
+-----------+-----------------------+
| User_name | User_password         |
+-----------+-----------------------+
| root      | abf3$**92kjh1lffjmavn |
+-----------+-----------------------+
1 row in set (0.00 sec)

And most interestingly, to me, is that if you have the same filesystem access to the data directory that would've allowed you to read master.info, you can still find the password just as easily as before:

kolbe datadir $ strings -a mysql/slave_master_info.ibd | tail -n 1
rootabf3$**92kjh1lffjmavn

The table-based master and relay info repositories can be really nice for including this information in mysqldump output when taking backups or setting up new slaves, but please note that this does nothing to increase security and may actually be somewhat less secure, by exposing the password via SQL.

About the Author

Kolbe Kegel is a Principal Support Engineer at SkySQL, where he has worked since 2011. Kolbe has worked with MySQL since 2005, first at MySQL, later at Sun Microsystems after its acquisition of MySQL Inc., then at Oracle after its acquisition of Sun.
Anonymous (not verified)

I have to disagree here

I have to disagree here having it in a table allows mysql acl to kick in and protect that table. Additionally if you are storing the table as innodb it is inheritor more secure as more work is needed to extract the pages. Note I did not say it isnsecure , but it's better and with a small patch we could salt the password on the db to protect file snoop or select snooping. All on all think of PCI here no password maybe stored in a plaintext format, storing this in a innodb table solves that requirement.

kolbe

Back to reality

Hello, Anonymous! Thanks for sharing your thoughts.

You make several points; let's take a look at them.

1) "having it in a table allows mysql acl to kick in and protect that table". That is true. However, when using master-info-repository=file, it is not possible to get the data from within MySQL (unless maybe you use some LOAD DATA INFILE or LOAD_FILE() tricks, but I sure do hope those are locked down). You're right that using master-info-repository=table "allows mysql acl to kick in", but surely that's not more secure than keeping "mysql acl" out of the equation entirely?

2) "if you are storing the table as innodb it is inherently* more secure". This, in some theoretical sense, might be true, but my post includes an extremely trivial way to look inside the .ibd file and find the password. This table should only ever have one row (in the current version), so it's not like there are going to be a lot of pages in this .ibd file that would make it difficult to dig out the password.

3) "with a small patch we could salt the password on the db to protect file snoop or select snooping". Sure. Where do you keep the salt? Why can't this also be done for master.info?

4) "no password maybe stored in a plaintext format, storing this in a innodb table solves that requirement". This is absolutely false. My example proves this. I used a simple Unix command to find the password in the .ibd file, in plaintext, I don't know how you can look at that and still draw the conclusion that this sentence you wrote is true.

I thought perhaps enabling compression for the table would at least make the data appear garbled when read as text using system tools, but that isn't even the case:

 

kolbe@prosimmon 5.6 $ mysql -e "select @@innodb_file_format, row_format from information_schema.tables where table_name='slave_master_info'"
+----------------------+------------+
| @@innodb_file_format | row_format |
+----------------------+------------+
| Barracuda | Compressed |
+----------------------+------------+

kolbe@prosimmon 5.6 $ strings -a data/mysql/slave_master_info.ibd
127.0.0.1
rootlkajsdf91823fh9
D0075f2616-8508-11e2-a100-2d636aea58f7

(Note that enabling compression requires innodb_file_format=barracuda, which still isn't the default in MySQL 5.6.)

The fact is that it is simply no more secure, in any kind of real sense, to use master-info-repository=TABLE instead of master-info-repository=FILE.

Kolbe

kolbe

The whole premise of this

The whole premise of this post was based around that weird Note/Warning issued when executing CHANGE MASTER TO, but it seems pretty clear to me now that this Note is just terribly written and is actually trying to discourage the user from using MASTER_USER/MASTER_PASSWORD with CHANGE MASTER TO in the first place. I've filed a bug report to that effect: http://bugs.mysql.com/bug.php?id=68602