Mysql – Why does MySQL Galera not replicate the user table

galeraMySQLreplication

I am running a 3 node MySQL 5.6 Galera Cluster. Everything is in sync and looks fine, but as I just found out the user table was not in sync.

One particular user was not present on the other nodes where I thought it might replicate it after issuing:

mysql -u root -p -e "INSERT INTO mysql.user (Host,User) values ('10.0.0.10','haproxy_check'); FLUSH PRIVILEGES;"

I double checked wsrep status and user tables on all nodes. After issuing the command on the other 2 nodes it is available and galera seems to be still in sync.

Am I missing something here? Why is galera not replicating the user table?

Best Answer

It is in the known limitations:

Currently replication works only with the InnoDB storage engine. Any writes to tables of other types, including system (mysql.*) tables are not replicated (this limitation excludes DDL statements such as CREATE USER, which implicitly modify the mysql.* tables — those are replicated). There is however experimental support for MyISAM - see the wsrep_replicate_myisam system variable)

So using proper CREATE USER statement will replicate, your INSERT INTO mysql.user statement will not.

I can't quite find a suitable reference if GRANT statements are considered DDL statements and will replicate or not.

Related Solutions

Debian MySQL User – What is the debian-sys-maint MySQL User?

What is debian-sys-maint used for?

One major thing it is used for is telling the server to roll the logs. It needs at least the reload and shutdown privilege.

See the file /etc/logrotate.d/mysql-server

It is used by the /etc/init.d/mysql script to get the status of the server. It is used to gracefully shutdown/reload the server.

Here is the quote from the README.Debian

* MYSQL WON'T START OR STOP?:
=============================
You may never ever delete the special mysql user "debian-sys-maint". This user
together with the credentials in /etc/mysql/debian.cnf are used by the init
scripts to stop the server as they would require knowledge of the mysql root
users password else.

What is the easiest way to restore it after I've lost it?

The best plan is to simply not lose it. If you really lose the password, reset it, using another account. If you have lost all admin privileges on the mysql server follow the guides to reset the root password, then repair the debian-sys-maint.

You could use a command like this to build a SQL file that you can use later to recreate the account.

mysqldump --complete-insert --extended-insert=0 -u root -p mysql | grep 'debian-sys-maint' > debian_user.sql

Is the password in /etc/mysql/debian.cnf already hashed

The password is not hashed/encrypted when installed, but new versions of mysql now have a way to encrypt the credentials (see: https://serverfault.com/a/750363).

MySQL user’s grant are not working – hidden permission in thesql.db

It may be possible that you probably have an anonymous user in mysql.user.

First things first. Please run this query:

SELECT USER(),CURRENT_USER();

USER() reports how you attempted to authenticate

CURRENT_USER() reports how mysqld allowed to authenticate

If the second function reveals a weird user, chances are it may be the anonymous user.

Go to my DBA StackExchange answer on how MySQL performs user authentication plus how and why to remove anonymous users.

Best Answer

Related Solutions

Debian MySQL User – What is the debian-sys-maint MySQL User?

MySQL user’s grant are not working – hidden permission in thesql.db

Related Topic