Virtualmin and mySQL Galera replication. Users not replicated.

Virtualmin
1

Hello,

I’ve created 2 Centos 6.3 servers, with a Gluster filesystem that shares www and home. Both nodes share a Galera mySQL repliacted database.

both nodes have Virtualmin installed. I want to only use 1 node to manage things on. I have a script that creates new users on the 2nd node when they are created, but the problem is with database users.

Both nodes can see the newly created databases within mySQL, but users for the databases do not appear on node 2.

Googling gives this “Currently replication works only with InnoDB storage engine. Any writes to tables of other types, including system (mysql.) tables are not replicated. However, DDL statements are replicated in statement level, and changes to mysql. tables will get replicated that way. So, you can safely issue:CREATE USER… or GRANT… but issuing: INSERT INTO mysql.user… will not be replicated. In general, non-transactional engines cannot be supported in multi-master replication.”

The databases are of type InnoDB, so I am assuming that the INSERT INTO is the problem. I’ve tried looking for a script in Virtualmin that (may) create the user, but I’ve found nothing and I’m a bit stumped.

Any pointers would be appreciated.

Thanks,

David

2

I’ve found the code in Virtual min that is creating the users. And it is doing an INSERT INTO.

&mysql::execute_sql_logged($mysql::master_db,
"insert into user (host, user, password) ".
“values (?, ?, $encpass)”, $h, $user);

Oh well. I’ll try and work round it.

3

Yes, that bit can be a pain.

You can either change the code (messy) or replicate the grants some other way.
If you are going to execute a script to keep the users in sync, have a look at http://www.percona.com/doc/percona-toolkit/2.1/pt-show-grants.html

This basically lets you grab only the grants and pipe them to another server

Please let me know how this setup works, because I’ve been toying with some of the same ideas (Gluster and Galera)

4

Thanks.

I’m going to change the code. It’s only 5 lines and if it changes after a virtualmin update, I can change it back.

Gluster is fairly easy to set up. I’m using it with /var/www mounted as a gluster volume and on the same volume I have a mount to point /home to /var/www/home.
http://www.gluster.org/community/documentation/index.php/QuickStart#Step_4_.E2.80.93_Installing_Gluster

I did originally use mySQL Cluster, but that had some performance issues with how we use the DB, so we switched to Galera.
We have 2 DB nodes and an arbitrator. If you shut both both nodes down at the same time, you have to re-create the cluster again. It can be a bit of a pain. Seems to work OK though.

http://blog.secaserver.com/2012/02/high-availability-mysql-cluster-galera-haproxy/

5

Thanks for getting back on this. I would most likely setup some form of cloud servers across a few (local) datacenters and this looks very interesting!

The only thing holding me back on this is the lack of MyISAM support for Galera (my life as a host is simpler when using MyISAM as not every WordPress / plugin combination is known to work properly with InnoDB)

Best of luck