MySQL Bugs: #37262: Access denied for Linux Master and Slave on other machine/OS

Bug #37262	Access denied for Linux Master and Slave on other machine/OS
Submitted:	7 Jun 2008 10:18	Modified:	27 Nov 2008 10:34
Reporter:	Susanne Ebrecht	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0, 5.1	OS:	Linux
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Install 5.0 bzr tree on Ubuntu 8.04 and on FreeBSD 7.0 (both 64 bit)

First try:
Start mysqld under ubuntu and then mysql-CLI:
GRANT ALL on *.* to myuser@'192.168.%.%' with grant option;

Now copy data dir from this FreeBSD installation to an Ubuntu folder and to an extra FreeBSD folder.

Create an etc folder in the folders of the systems.

Create a my.cnf in the etc folder: Try first:

my.cnf on FreeBSD as master:

[mysqld]
port = 5050
socket = /tmp/mysql50bkrepim.sock
server-id=1
log-bin=mysql-bin
datadir=/home/myuser/repimaster50bk/var
[client]
port = 5050
socket = /tmp/mysql50bkrepim.sock

my.cnf on Ubuntu as slave:

[mysqld]
port = 5051
socket = /tmp/mysql50bkrepis.sock
server-id=2
datadir=/home/myuser/repislave50bk/var
[client]
port = 5051
socket = /tmp/mysql50bkrepis.sock

Start ./libexec/mysqld --defaults-file=PATH/etc/my.cnf &

Start CLI on slave (Ubuntu)

CHANGE MASTER TO MASTER_HOST='192.168.144.12', MASTER_PORT=5050, MASTER_USER='myuser';

START SLAVE;

All works fine.

HERE COMES THE ERROR:

stop all mysqld and delete the folder.
Create new folder on both systems and copy the original data dir from FreeBSD installation again into these folders.

Now configure Ubuntu as Master and FreeBSD as Slave.

To be sure that all GRANTS are ok, make again on both systems:

GRANT ALL on *.* TO myuser@'192.168.%.%' with GRANT OPTION;

Start slave CLI on FreeBSD:
CHANGE MASTER TO MASTER_HOST='192.168.144.15', MASTER_USER='myuser', MASTER_PORT=5050;
START SLAVE;

[ERROR] Error reading packet from server: Access denied; you need the REPLICATION SLAVE priviledge for this operation (server_errno=1227)

Why that? My user already has all Grants. And also why only by using this direction (Ubuntu as master) and not on the other direction (FreeBSD as master)?

Workaround here is on the master:
GRANT all on *.* to myuser@'192.168.144.12';

It won't work by using wild cards for IP-address.

These wildcards are working fine in both direction by using just CLI to connect to the system.

Consider, that you sometimes have more then one slave at one network, so you should use wildcards for IP addresses as well.
 
 

How to repeat:
see above

Suggested fix:
I think this is a wrong behaviour by using Grants on Linux.

Is not repeatable with master and slave on same machine.

Bug is repeatable on Linux: "FreeBSD" is Linux 32-bit machine and "Linux" is Linux 64-bit amd machine.

Same problem with 5.1.

How to repeat again.

Server A: 32-bit Linux
Server B: 64-bit AMD Linux

Use compile-amd64-debug-max script to compile MySQL on B

1. A: mysql_install_db --datadir=data
2. A: start mysqld
3. A: GRANT ALL on *.* to myuser@'192.168.%.%' with grant option;
4. A: stop mysqld
5. B: rsync -a
6. B: Create my.cnf with server-id=1
7. B: start mysqld
8. B: GRANT ALL on *.* to myuser@'192.168.%.%' with grant option;
9. A: Create my.cnf with server-id=2
10. A: change master to master_host='192.168.1.179', master_user='myuser', master_port=5050, master_log_file='mysql-bin.000001', master_log_pos=214;
11. A: start slave;
12. A: find error in the error log
13. B:  GRANT ALL on *.* to myuser@'192.168.1.178' with grant option;
14. A: start slave;
15. A: find slave IO has started.