MySQL replication and master users on slave server

MySQLpermissionsreplicationusers

Do the users initiating inserts on the master server also have to be on the slave with at least as many permissions for the replicated queries to be correctly executed? My understanding is that you only have to have a replication user with enough permissions but I still have a doubt.

Moreover, let's say that the slave server has the same users as the master server, in addition to a replication-dedicated user, but with less permissions, non writes for instance. Would there be any kind of conflicts resulting in the replicated queries not being executed?

The idea is to have a read-only slave with only SELECT perm users to access to data, not necessarily the same as the master's users, plus a replication-dedicated user on the slave with REPLICATION SLAVE perm. Would all replicated queries then supposed to be executed normally? That's my actual wondering.

Best Answer

The "user" that the Slave connects to the Master with -- It needs only REPLICATION SLAVE, no other privileges. Replication's goal is to keep the Slave identical to the Master. This goal overrides any privilege issues.
The Slave should have read_only = ON as an extra protection.
"Users" (either human or application), other than the system admin, should not have SUPER privilege. This keeps you out of trouble, and helps prevent hacking.
Another hacking prevention is to GRANT users access only to dbname.*, not *.*. The latter would include the system tables where all the Grants are stored.
Users need write permissions when writing to the Master. Don't worry about whether the users have write perm to the Slave; the Slave user bypasses that kind of check. (This is probably the answer you were looking for.)
Users need only read permission when reading from the Slave.
TEMPORARY tables follow different rules. (It is something you might want to use on a read-only Slave.) I hesitate to spell them out here, because I am not sure of the rules.

Related Solutions

Mysql master-master permission replication issue

Your grants are not replicating because you have an (unnecessary) replication filter on INFORMATION_SCHEMA. There is no reason to exclude this, as it is not replicated anyhow. When replication filters are in place, you have to USE a database or your statements are not replicated.

For example...not replicated

Grant all privileges on *.* to foo@localhost identified by 'bar';

This is replicated...

USE anydb
Grant all privileges on *.* to foo@localhost identified by 'bar';

....but instead, just remove that replication filter.

Mysql – Master/Master/Slave Replication

Using the topology you mentioned from the MySQL Documentation

sxjn

Let's setup the first scenario

EXAMPLE IPs for Each DB Server

Master1 : 10.20.30.40
Master2 : 10.20.30.50
Slave_1 : 10.20.30.60
replication user is repluser@'%'
replication password is replpass

Make sure binary logging is enabled on all the Slaves

We will do the following

Promote Master2 to Master1
Promote Slave_1 to Master2
Demote Master1 to Slave1

Step 01 : On Master2, run the following

mysql> SET GLOBAL read_only = 1;
mysql> STOP SLAVE;
mysql> RESET SLAVE;
mysql> CHANGE MASTER TO master_host='';
mysql> FLUSH TABLES;
mysql> SET GLOBAL read_only = 0;

Step 02 : Move your CNAME/VIP to Master2

Step 03 : On Slave_1, run mysql> RESET MASTER; FLUSH TABLES;

Step 04 : On Slave_1, dump the data

NEW_MASTER_HOST="10.20.30.60"
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQLDUMP_OPTIONS="--single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --triggers"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --master-data=1"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --all-databases"
echo "STOP SLAVE;" > MySQLData.sql
echo "CHANGE MASTER TO master_host='${NEW_MASTER_IP}'," >> MySQLData.sql
echo "master_port=3306," >> MySQLData.sql
echo "master_user='repluser'," >> MySQLData.sql
echo "master_password='replpass'," >> MySQLData.sql
echo "master_log_file='bogus'," >> MySQLData.sql
echo "master_log_pos=1;" >> MySQLData.sql
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} >> MySQLData.sql
echo "START SLAVE;" >> MySQLData.sql
gzip MySQLData.sql

Step 05 : When Master1 comes back up, rsync or scp MySQLData.sql.gz from Slave_1 to Master1

Step 06 : Login to MySQL on the Master1 and setup it up to replicate from Slave_1

Don't worry about the real binary log filename and position.

Using --master-data=1 embeds CHANGE MASTER TO command with the real coordinates on line 23 of a standard dump.

You can see it with the following

less MySQLData.sql.gz | head -35 | tail -1

Step 07 : Load the data into Master1

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
gzip -d < MySQLData.sql.gz | mysql ${MYSQL_CONN}

Step 08 : Login to MySQL on Master1

mysql> SHOW SLAVE STATUS\G

and Make sure Slave_IO_Running and Slave_SQL_Running both say Yes

EPILOGUE

When Done, the topology should be

Master1 : 10.20.30.50
Master2 : 10.20.30.60
Slave_1 : 10.20.30.40

DISCLAIMER

Please try this out on Test Servers before deploying to Production

GIVE IT A TRY !!!

Note: I would advise that you have multiples slaves

One for Nightly Backups
The others for load balancing SELECTs