Problems with permissions when replicating mysqldb (possibly only a doc error?)

>Description:
Hi
According to documentation (manual.html), the slave server only need FILE=
=20
permissions on the master server. However, this does not seems to be=20
sufficient.

>How-To-Repeat:
on master:
mysql> grant FILE on *.* to repl@secondarydbserver.ez.no identified by=20
"foobar";

on slave:
mysql> CHANGE MASTER TO MASTER_HOST=3D'primarydbserver.ez.no',=20
MASTER_USER=3D'repl', MASTER_PASSWORD=3D'foobar';
mysql> load data from master;
ERROR 1219: Error running query on master: Access denied. You need the RE=
LOAD=20
privilege for this operation

on master:
mysql> grant RELOAD on *.* to repl@secondarydbserver.ez.no identified by=20
"foobar";

on slave:
mysql> load data from master;
ERROR 1219: Error running query on master: Access denied. You need the SU=
PER=20
privilege for this operation

on master:
mysql> grant SUPER on *.* to repl@secondarydbserver.ez.no identified by=20
"foobar";

on slave:
mysql> load data from master;
Query OK, 0 rows affected (0.01 sec)
mysql> slave start;
Query OK, 0 rows affected (0.00 sec)

However, in the logfile on the slave, I now get:
30205 15:08:23 Slave I/O thread: connected to master=20
'repl@primarydbserver.ez.no:3306', replication started in log 'ez1-bin.0=
01'=20
at position 1192
030205 15:08:23 Error on COM_REGISTER_SLAVE: 1045 'Access denied for use=
r:=20
'repl@192.168.0.4' (Using password: YES)'
030205 15:08:23 Slave I/O thread exiting, read up to log 'ez1-bin.001',=20
position 1192


Dirty fix:
On Master:
mysql> grant ALL on *.* to repl@secondarydbserver.ez.no identified by=20
"foobar";

So, is this a bug in 4.0.10, or a documentation bug? (or just me beeing=20
stupid?) If it is an doc-bug, what is the correct permissions needed ?=20

Best regards,
Vidar

>Submitter-Id:
>Originator: Vidar

>MySQL support: none
>Synopsis: Problems with permissions when replication mysqldb (possi=
bly=20
doc error)
>Severity: non-critical
>Priority: medium
>Category: mysql
>Class: sw-bug or doc-bug
>Release: mysql-4.0.10-gamma (Official MySQL RPM)
>Server: /usr/bin/mysqladmin Ver 8.40 Distrib 4.0.10-gamma, for pc-linux=
on=20
i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version 4.0.10-gamma-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 2 hours 26 min 17 sec

Threads: 1 Questions: 43 Slow queries: 0 Opens: 246 Flush tables: 1 =
Open=20
tables: 60 Queries per second avg: 0.005
>C compiler: 2.95.3
>C++ compiler: 2.95.3
>Environment:

System: Linux ez2.ez.no 2.4.18-19.8.0 #1 Thu Dec 12 04:37:40 EST 2002 i68=
6=20
athlon i386 GNU/Linux
Architecture: i686

Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc=20
/usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs
Configured with: ../configure --prefix=3D/usr --mandir=3D/usr/share/man=20
--infodir=3D/usr/share/info --enable-shared --enable-threads=3Dposix=20
--disable-checking --host=3Di386-redhat-linux --with-system-zlib=20
--enable-__cxa_atexit
Thread model: posix
gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7)
Compilation info: CC=3D'gcc' CFLAGS=3D'-O6 -fno-omit-frame-pointer -mpen=
tium' =20
CXX=3D'g++' CXXFLAGS=3D'-O6 -fno-omit-frame-pointer =20
-felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS=3D'' =20
ASFLAGS=3D''
LIBC:
lrwxrwxrwx 1 root root 14 Oct 15 1999 /lib/libc.so.6 ->=
=20
libc-2.2.93.so
-rwxr-xr-x 1 root root 1235468 Sep 6 01:12 /lib/libc-2.2.93.=
so
-rw-r--r-- 1 root root 2233342 Sep 6 00:59 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Sep 6 00:50 /usr/lib/libc.so
Configure command: ./configure '--disable-shared'=20
'--with-mysqld-ldflags=3D-all-static' '--with-client-ldflags=3D-all-stati=
c'=20
'--without-berkeley-db' '--with-innodb' '--without-vio' '--without-openss=
l'=20
'--enable-assembler' '--enable-local-infile' '--with-mysqld-user=3Dmysql'=
=20
'--with-unix-socket-path=3D/var/lib/mysql/mysql.sock' '--prefix=3D/'=20
'--with-extra-charsets=3Dcomplex' '--exec-prefix=3D/usr' '--libexecdir=3D=
/usr/sbin'=20
'--sysconfdir=3D/etc' '--datadir=3D/usr/share' '--localstatedir=3D/var/li=
b/mysql'=20
'--infodir=3D/usr/share/info' '--includedir=3D/usr/include'=20
'--mandir=3D/usr/share/man' '--with-embedded-server'=20
'--enable-thread-safe-client' '--with-comment=3DOfficial MySQL RPM' 'CFLA=
GS=3D-O6=20
-fno-omit-frame-pointer -mpentium' 'CXXFLAGS=3D-O6 -fno-omit-frame-pointe=
r =20
-felide-constructors -fno-exceptions -fno-rtti -mpentium'


--=20
Vidar



------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13737@lists.mysql.com
To unsubscribe, e-mail

Re: Problems with permissions when replicating mysqldb (possibly only a doc error?)

Hi Vidar,

As our online manual says (section 4, "how to set up replication")
* Set up special a replication user on the master with the FILE (in MySQL
versions older than 4.0.2) or REPLICATION SLAVE privilege in newer MySQL
versions.

As you are using 4.0.10, the required privilege is REPLICATION SLAVE, not
FILE.
grant replication slave on *.* to repl@secondarydbserver.ez.no identified by
"foobar";
is enough for your slave to connect to the master and read the master's
binary log without errors.

Then, about LOAD DATA FROM MASTER, you are right, our doc has lacks and I
will correct them these days. See below.

> on slave:
> mysql> CHANGE MASTER TO MASTER_HOST='primarydbserver.ez.no',
> MASTER_USER='repl', MASTER_PASSWORD='foobar';
> mysql> load data from master;
> ERROR 1219: Error running query on master: Access denied. You need the
> RELOAD privilege for this operation

This means that the master wants repl@secondarydbserver.ez.no to have the
RELOAD privilege on the master. This is true because LOAD DATA FROM MASTER
does FLUSH TABLES WITH READ LOCK on the master (closes and locks all tables
before copying them), which requires RELOAD on the master.
All slave actions on the master are run with the user you supplied in CHANGE
MASTER, hence this user must have sufficient privileges to do FLUSH TABLES
WITH READ LOCK.

>
> on master:
> mysql> grant RELOAD on *.* to repl@secondarydbserver.ez.no identified by
> "foobar";
>
> on slave:
> mysql> load data from master;
> ERROR 1219: Error running query on master: Access denied. You need the
> SUPER privilege for this operation

Same problem : LOAD DATA FROM MASTER does SHOW MASTER STATUS on the master
which requires SUPER on the master.

> on master:
> mysql> grant SUPER on *.* to repl@secondarydbserver.ez.no identified by
> "foobar";
>
> on slave:
> mysql> load data from master;
> Query OK, 0 rows affected (0.01 sec)
> mysql> slave start;
> Query OK, 0 rows affected (0.00 sec)
>
> However, in the logfile on the slave, I now get:
> 30205 15:08:23 Slave I/O thread: connected to master
> 'repl@primarydbserver.ez.no:3306', replication started in log
> 'ez1-bin.001' at position 1192
> 030205 15:08:23 Error on COM_REGISTER_SLAVE: 1045 'Access denied for user:
> 'repl@192.168.0.4' (Using password: YES)'
> 030205 15:08:23 Slave I/O thread exiting, read up to log 'ez1-bin.001',
> position 1192

This is because you granted FILE and not REPLICATION SLAVE (see the beginning
of my email).

> So, is this a bug in 4.0.10

No

> or a documentation bug?

Yes, I will correct this.

> (or just me beeing
> stupid?)

No

> If it is an doc-bug, what is the correct permissions needed ?

Summary : the user used for replication should have, on the master :
* REPLICATION SLAVE
* and, if you want to do LOAD DATA FROM MASTER:
- RELOAD
- SUPER
Note that a user that issues the LOAD DATA FROM MASTER command on the slave
should have SUPER on the slave.

Thanks for this doc-bug-report.
Guilhem

>
> >Submitter-Id:
> >Originator: Vidar
> >
> >MySQL support: none
> >Synopsis: Problems with permissions when replication mysqldb
> > (possibly
>
> doc error)
>
> >Severity: non-critical
> >Priority: medium
> >Category: mysql
> >Class: sw-bug or doc-bug
> >Release: mysql-4.0.10-gamma (Official MySQL RPM)
> >Server: /usr/bin/mysqladmin Ver 8.40 Distrib 4.0.10-gamma, for pc-linux
> > on
>
> i686
> Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
> This software comes with ABSOLUTELY NO WARRANTY. This is free software,
> and you are welcome to modify and redistribute it under the GPL license
>
> Server version 4.0.10-gamma-log
> Protocol version 10
> Connection Localhost via UNIX socket
> UNIX socket /var/lib/mysql/mysql.sock
> Uptime: 2 hours 26 min 17 sec
>
> Threads: 1 Questions: 43 Slow queries: 0 Opens: 246 Flush tables: 1
> Open tables: 60 Queries per second avg: 0.005
>
> >C compiler: 2.95.3
> >C++ compiler: 2.95.3
> >Environment:
>
>
> System: Linux ez2.ez.no 2.4.18-19.8.0 #1 Thu Dec 12 04:37:40 EST 2002 i686
> athlon i386 GNU/Linux
> Architecture: i686
>
> Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
> /usr/bin/cc
> GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs
> Configured with: ../configure --prefix=/usr --mandir=/usr/share/man
> --infodir=/usr/share/info --enable-shared --enable-threads=posix
> --disable-checking --host=i386-redhat-linux --with-system-zlib
> --enable-__cxa_atexit
> Thread model: posix
> gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7)
> Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium'
> CXX='g++' CXXFLAGS='-O6 -fno-omit-frame-pointer
> -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS=''
> ASFLAGS=''
> LIBC:
> lrwxrwxrwx 1 root root 14 Oct 15 1999 /lib/libc.so.6 ->
> libc-2.2.93.so
> -rwxr-xr-x 1 root root 1235468 Sep 6 01:12 /lib/libc-2.2.93.so
> -rw-r--r-- 1 root root 2233342 Sep 6 00:59 /usr/lib/libc.a
> -rw-r--r-- 1 root root 178 Sep 6 00:50 /usr/lib/libc.so
> Configure command: ./configure '--disable-shared'
> '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static'
> '--without-berkeley-db' '--with-innodb' '--without-vio' '--without-openssl'
> '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql'
> '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/'
> '--with-extra-charsets=complex' '--exec-prefix=/usr'
> '--libexecdir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share'
> '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info'
> '--includedir=/usr/include'
> '--mandir=/usr/share/man' '--with-embedded-server'
> '--enable-thread-safe-client' '--with-comment=Official MySQL RPM'
> 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6
> -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti
> -mpentium'

--
MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/
For technical support contracts, visit https://order.mysql.com/?ref=mgbi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Guilhem Bichot
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Software Developer
/_/ /_/\_, /___/\___\_\___/ Bordeaux, France
<___/ www.mysql.com +33 5 56 88 34 39

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13761@lists.mysql.com
To unsubscribe, e-mail