Method of creating database users

Submitted by ReArmedHalo on Sat, 11/29/2014 - 18:58

Hello,

I decided to file this under "Webmin" as the relevant files that control this behavior are in part of Webmin I believe, however I am using Virtualmin Pro.

So I have a MariaDB Galera Cluster (two nodes currently, kinda in testing phase) and it works great! Except for one, tiny little thing... User permissions. Currently, from what I understand while looking at /usr/libexec/webmin/mysql/edit_user.cgi, users are created by simply inserting them into the mysql.user table. This however, does not work with replication as that table is MyISAM and only InnoDB is supported by the replication currently. (There is experimental support for MyISAM, which I am going to test in a bit) So users are not replicated between systems since only CREATE USER calls are replicated according to the documentation on MariaDB's website.

Is it possible that Webmin could be switched to using the CREATE USER commands instead of simply inserting the user into the table? Also, why are users being inserted rather than using CREATE USER?

MariaDB Galera Know Limitations (First bullet talks about this): https://mariadb.com/kb/en/mariadb/documentation/replication/galera/maria...

Thanks, Dustin

Status: 
Closed (fixed)

Comments

Submitted by JamieCameron on Sat, 11/29/2014 - 20:32

Probably not - the reason the user table is directly modified is that creating the "CREATE USER" SQL statement is much more complex than simply adding rows to a table with a known format.

Submitted by ReArmedHalo on Sat, 11/29/2014 - 21:28

Hi Jamie,

Hmm... I wasn't aware of that. Do you have any suggestions as to what I could do to work around this issue? I don't really want to try the experimental MyISAM replication at this time as the deployment I am working on is going to be in production use.

Thanks, Dustin

Submitted by JamieCameron on Sat, 11/29/2014 - 22:51

Perhaps a separate cron job that copies the entire mysql.user table from a master system to the replicas?

Submitted by ReArmedHalo on Sun, 11/30/2014 - 15:03

Hi,

Thanks Jamie for the idea! I always forget about cron! I've come up with the following:

*/5 * * * * /root/copy_mysql_users.sh >/dev/null 2>&1

Script:

#!/bin/bash
mysqldump -h {Virtualmin Managed DB Server IP} -u root -p'{Password}' mysql user | mysql -h {Local IP} -u root -p'{Password}' mysql
mysqladmin flush-privileges --user=root --password='{PASSWORD}' --host={Local IP}

Do you think this would suffice? I decided to run it every five minutes since the mysql.user table will be overwritten each time and every minute felt like I could start to overload a system depending on what it is doing.

Thanks Dustin

Submitted by JamieCameron on Sun, 11/30/2014 - 16:58

Sure, that would work. And because the "flush privileges" command is atomic, there should be no impact on your MySQL server.

Submitted by ReArmedHalo on Sun, 11/30/2014 - 20:37

Hi,

What do you mean by "atomic"? I'm sorry I've heard the word used but I am unsure of it's meaning in this context.

Thanks, -Dustin

Submitted by JamieCameron on Sun, 11/30/2014 - 22:15

By "atomic", I mean that all the old users are replaced by the new users in a single operation - there is no time at which only some of the users exist.

Submitted by ReArmedHalo on Tue, 12/16/2014 - 14:18

Hi Jamie,

Realized I never replied. Thank you for that explanation! So far, I believe things are working good. Still in testing phase with this setup but so far so good! Thank you again!

-Dustin