User permissions not replicating (5.5.31).

Hello,

We have setup a 4 node cluster. I started the first node using service mysql bootstrap-pxc and prepopulated our database with some mysql.user values by running:

INSERT INTO `mysql`.`user` VALUES
('localhost', 'user', ... ),
('localhost', 'user2', ...);
FLUSH PRIVILEGES;

However, when we bring up nodes 2-4 these permissions are not replicated and I am unable to connect as these users. The only way to connect is to login as our root user on node one, then manually run:

GRANT INSERT,...,DELETE ON dbname.* TO 'user'@'localhost' IDENTIFIED BY PASSWORD 'password';

Then the user is then replicated out to the cluster and I am able to connect.

Is this a bug, or is there something I’m doing wrong? Here is a snippet of our build script that runs this:

service mysql bootstrap-pxc
mysql -uroot < /tmp/bootstrap.sql
rm /tmp/*.sql

Best regards,
Andrew

You must use the GRANT commands to assign permissions. Item #1: http://support.severalnines.com/entries/21692388-limitations-in-galera-replication-for-mysql

You can also use MyISAM replication feature - [HTML]wsrep_replicate_myisam = ON[/HTML], but indeed GRANT commands are better approach. While it’s (still) OK for some system tables, I would not recommend using MyISAM for any production tables.