There is no centralized authentication for mysql. You have to set up your user accounts individually for each of your mysql servers. You can use replication, but that would mean, your master servers must have the same authentication privileges as your slaves have. What about security?
So, here is a little workaround that I did and I can't find the same anywhere.
How does it work?
This is an unofficial and unsupported solution. Let's have a master and one or more slave hosts. On your master, you have to create a database lets say slave_auth. Create the same tables in this database as you have in the 'mysql' database and needed for authentication (more details below), and set up a user who can read/write those tables.
When you are done, create a temporary database on your slaves lets say auth_temp with the exact structure of the tables on your master, but with federated engine connected to your master with the user you added previously. Shut down mysql, overwrite the mysql system tables with those you got in auth_temp db (or don't shut down, just overwrite), flush privileges, and there you go, its done. Funny humm?
What are the requirements?
- It needs federated engine supported on your slaves
(no, there is no more requirements)
How to make it work:
Let's move on to some direct working example:
1. Moves on your master(s):
1.1 Creating tables for slaves:
mysql>create database auth_slaves;
mysql>create table auth_slaves.columns_priv like mysql.columns_priv;
mysql>create table auth_slaves.db like mysql.db;
mysql>create table auth_slaves.tables_priv like mysql.tables_priv;
mysql>create table auth_slaves.user like mysql.user;
(If you already have different privileges on your slaves, I would recommend a mysql dump instead)
1.2 Create a user for your slaves:
mysql>grant select,insert,update on auth_slaves.* to 'auth_slave_user'@'192.168.1.%' identified by 'auth_4_sl4v3s';
You are able to set up different kind of auth users, for example if you only wan't on some of your slaves to be just read only, don't grant other rights, but select. Up to you.
All done on your master..
2. Moves on your slave(s):
2.1 Create a temporary database
mysql>create database dropme_auth;
2.2 Create the tables
Now, I don't paste all the table definitions, but execute show create table on each table printed above, copy the result to a txt or something and modify the command to work with federated like in this example:
mysql#host:master#db:auth_slaves>show create table user\G
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
......
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
1 row in set (0.00 sec)
Now all you have to do is modify the engine like: engine = federated CONNECTION='mysql://auth_slave_user:auth_4_sl4v3s@master.host:3306/auth_slaves/user'
So, just create a table driven by federated engine connected to your masters auth_slaves db with the same table names with the same table structure.
3. Replace the system tables:
In this scenerio you must overwrite the tables in the mysql database folder (for example /var/lib/mysql/mysql) with the tables from your temporary database where you built the federated tables (for example /var/lib/mysql/dropme_auth) like this: cp /var/lib/mysql/dropme_auth/*.frm /var/lib/mysql/mysql/ .
Don't use '*' in the cp, it's even better if you copy every table one by one.
Take care of the privileges after the copy.
4. Execute a simple select query like select * from mysql.user. If you can see the same what is on your master, you are done. If not, hmm. Thats a kind of a problem 
What if connection broken?
Nothing will happen unless you execute flush privileges. Because as we all know, mysql cache the authentication tables and doesn't load the content again until flushing of privileges.
What about flush privileges?
The question you will consider soon if you build this up: Ok wait, how can I modify authentication settings now?! The answer is so simple, just make it on any of your slaves and it will affect the tables stored on your master. When you done, if the documentation correct, execute flush privileges command on your master and all your slaves will reload the modified tables.
Benefits:
The most important is what used to be when you move on to centralized authentication.
- You only need to maintain the privileges on one of your slaves instead of a lots.
- In this case, you don't have to keep the same privileges for your masters and slaves and you don't have to replicate the mysql database.
PlanetMySQL Voting: Vote UP / Vote DOWN