LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 05-04-2017, 07:54 AM   #1
lvvloten
LQ Newbie
 
Registered: Mar 2011
Posts: 5

Rep: Reputation: 1
MySQL: Exclude user modifications from replication


I am running a MySQL database that is replicated to multiple instances for various purposes.

I want to distinguish database users between Master and Slaves. Users that have access to the Master should not automatically have access to all replication Slaves. On some slaves I would like to configure users that do not have access to the Master.

To achieve this I have excluded database "mysql" from the replica. However, I noticed that user modification queries (CREATE USER, ALTER USER, DROP USER) are still replicated to the slave. If I create a user on the Master it will also be created on the Slave. And, more importantly, if I drop specific users from the Slave and not from the Master, and then modify the users on the Master, this will lead to a replication error. Also, creating a user on the Master that already exists on the Slave will lead to errors.

Is there a way to exclude all user modification queries from the replication?

Is it a good idea to exclude the entire MySql database from replication?
 
Old 05-04-2017, 08:11 AM   #2
r3sistance
Senior Member
 
Registered: Mar 2004
Location: UK
Distribution: CentOS 6/7
Posts: 1,375

Rep: Reputation: 217Reputation: 217Reputation: 217
MySQL database due to it's heavy usage of myisam isn't great for replication and so the queries used are often replicated instead of the database itself (Statement based replication as opposed to Row based). If you are replicating databases/tables then you should be using innodb as a minimum as innodb is at least ACID compliant.

you can disable binlogging for the session and add/drop users or ensure you are actually in the mysql table (use mysql) before running such statements but really if you are replicating, then why would you want the replication slave to have different users to the master?
 
Old 05-04-2017, 08:30 AM   #3
lvvloten
LQ Newbie
 
Registered: Mar 2011
Posts: 5

Original Poster
Rep: Reputation: 1
Quote:
Originally Posted by r3sistance View Post
MySQL database due to it's heavy usage of myisam isn't great for replication and so the queries used are often replicated instead of the database itself (Statement based replication as opposed to Row based). If you are replicating databases/tables then you should be using innodb as a minimum as innodb is at least ACID compliant.
Yes, I am using INNODB tables and statement based replication.

Quote:
Originally Posted by r3sistance View Post
you can disable binlogging for the session and add/drop users
That does not seem to be an option since the database is continuously being updated so any normal updates during the period I disable binlogging would also be lost for replication.
I was thinking, is there any replication filter that I could apply to exclude specific statements?

Quote:
Originally Posted by r3sistance View Post
or ensure you are actually in the mysql table (use mysql) before running such statements
I don't see how being in the mysql table will stop such statements from being recorded in the binlog and, thus, from the replication

Quote:
Originally Posted by r3sistance View Post
if you are replicating, then why would you want the replication slave to have different users to the master?
I have several use cases:
One replica is actually a Data Warehouse database. It is used for a data warehouse function and contains a different set of user accounts from the Production environment. It is undesirable that database users from production also have accounts with equal privileges on the Data Warehouse servers.
My organization has a contractual obligation to maintain an extra database server containing the full dataset; however, this server should not be accessible using any of the existing database user accounts.
 
Old 05-04-2017, 08:52 AM   #4
r3sistance
Senior Member
 
Registered: Mar 2004
Location: UK
Distribution: CentOS 6/7
Posts: 1,375

Rep: Reputation: 217Reputation: 217Reputation: 217
Quote:
Originally Posted by lvvloten View Post
That does not seem to be an option since the database is continuously being updated so any normal updates during the period I disable binlogging would also be lost for replication.
I was thinking, is there any replication filter that I could apply to exclude specific statements?
It only affects the session, you would use the command "SET sql_log_bin = 0" https://dev.mysql.com/doc/refman/5.7...l-log-bin.html

This won't affect other sessions/connections at all. You may want to look into the differences between global and session variables in mysql to get a better understanding of this behavior

https://dev.mysql.com/doc/refman/5.7...variables.html
Quote:
There are two scopes in which system variables exist. Global variables affect the overall operation of the server. Session variables affect its operation for individual client connections. A given system variable can have both a global and a session value.
Quote:
Originally Posted by lvvloten View Post
I don't see how being in the mysql table will stop such statements from being recorded in the binlog and, thus, from the replication
Because the statement is now against the mysql database, when you are running it normally, you are actually running it against the database you are using. Tho behavior may differ between different versions of mysql and based on different forms of replication.

Quote:
Originally Posted by lvvloten View Post
I have several use cases:
One replica is actually a Data Warehouse database. It is used for a data warehouse function and contains a different set of user accounts from the Production environment. It is undesirable that database users from production also have accounts with equal privileges on the Data Warehouse servers.
My organization has a contractual obligation to maintain an extra database server containing the full dataset; however, this server should not be accessible using any of the existing database user accounts.
The only alternative I can think of is to try adding 'binlog-ignore-db=mysql' to the my.cnf of the master instead, that might catch the statements. You'd need to restart mysql to apply or manually update the global variable.

https://dev.mysql.com/doc/refman/5.6...nlog-ignore-db

Last edited by r3sistance; 05-04-2017 at 08:54 AM.
 
Old 05-04-2017, 09:41 AM   #5
lvvloten
LQ Newbie
 
Registered: Mar 2011
Posts: 5

Original Poster
Rep: Reputation: 1
Ok, thank you for the info. I will run some tests and post the result.
Regards,
Lucas
 
  


Reply

Tags
mysql, replication


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
User privileges in Mysql replication sem007 Linux - Server 2 09-27-2010 09:10 AM
Mysql replication failed after mysql reboot ajayan Linux - Newbie 8 09-08-2010 11:00 AM
MySQL replication Padawan.AVT Linux - Server 4 06-16-2009 09:27 PM
MySQL Replication ACDII Linux - Server 5 12-18-2006 08:08 AM
Mysql replication pk21 Linux - Software 0 08-28-2003 05:00 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 07:03 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration