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@stripped 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@stripped 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@stripped 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@stripped 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@stripped: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@stripped' (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: <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:
>
> <machine, os, target, libraries (multiple lines)>
> 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 <guilhem@stripped>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Software Developer
/_/ /_/\_, /___/\___\_\___/ Bordeaux, France
<___/ www.mysql.com +33 5 56 88 34 39
