Could you give me the whole error message on the access denied? Any indication of what
operation it talks about? Are all the users/privileges on master same as in slave?
Lisa
--- On Wed, 8/11/10, Julian Rawcliffe <jrawcliffe@stripped> wrote:
> From: Julian Rawcliffe <jrawcliffe@stripped>
> Subject: Re: InnoDB table replication
> To: replication@stripped
> Date: Wednesday, August 11, 2010, 4:33 PM
> I dropped the replication user,
> recreated with replication slave
> privs, repopulated the slave....
> start slave;
> show slave status briefly shows the Slave_SQL as running,
> the slave's Read_Master_Log_Pos advances, then ...
>
> 'Access denied; you need the SUPER privilege for this
> operation' on query.
>
> The Slave_IO_State is 'Queueing master event to the relay
> log' which
> isn't what I'd expect either.
>
> (This is how the replication user ended up with these privs
> in the first
> place). Who exactly needs the super privs? And on the
> master? The slave?
>
> I am going to try building a smaller database on my dev
> boxes and make
> I get the proper process for doing innodb replication.
>
> On 11/08/2010 21:50, Rick James wrote:
> > The repl user (or is it "replicate"?) should NOT have
> SUPER, it needs
> > ONLY REPLICATION SLAVE (as of about 4.0.xx).
> >
> > On 8/11/10 7:50 AM, Julian Rawcliffe wrote:
> >> On 11/08/2010 14:12, martín arrieta wrote:
> >>> A few comments.
> >>>
> >>> 1- servers ids must be unique.
> >> Master/slave ids are unique, copy-paste error in
> my post below:)
> >>
> >>> 2- you have to start the initialize the slave
> with one dump
> >>> *|mysqldump --all-databases
> --master-data>dbdump.db|*
> >>>
> >> Tried that before using the tarball.
> >> Dumped the master databases (with --master-data)
> >> reset the slave, import the dump and start the
> slave.
> >> The replication works briefly but I now get a
> dulicate entry error.
> >>
> >>> more
> >>>
> infohttp://dev.mysql.com/doc/refman/5.0/en/replication-howto-mysqldump.html
> >>>
> >>> 3- did you make the grant on master server?
> >>> mysql> *|CREATE
> USER'repl'@'%.mydomain.com<http://mydomain.com>'
> >>> IDENTIFIED BY'slavepass';|*
> >>> mysql> *|GRANT REPLICATION SLAVE ON *.*
> >>> TO'repl'@'%.mydomain.com<http://mydomain.com>';|*
> >> mysql> show grants for
> replicate@'campusgroupsmtp.london.edu';
> >> GRANT SELECT, RELOAD, FILE, SUPER, REPLICATION
> SLAVE ON *.* TO
> >> 'replicate'@'slavehost' IDENTIFIED BY PASSWORD
> 'slavepass'
> >>
> >>
> >>>
> >>> more
> >>>
> infohttp://dev.mysql.com/doc/refman/5.0/en/replication-howto-repuser.html
> >>>
> >>>
> >>>
> >>> Good luck.
> >>>
> >>> Martin.
> >>>
> >>> On 11 August 2010 09:58, Julian
> Rawcliffe<jrawcliffe@stripped
> >>> <mailto:jrawcliffe@stripped>>
> wrote:
> >>>
> >>> On 11/08/2010 13:45, martín arrieta wrote:
> >>>
> >>> We need more information..
> >>>
> >>> 1- mysql versions?
> >>>
> >>> Server version: 5.0.51a-3ubuntu5.7-log
> (Ubuntu) on Ubuntu 8.04
> >>>
> >>>
> >>>
> >>> 2- how did you initialize the slave server?
> >>>
> >>> FLUSH TABLES WITH READ LOCK; in one master
> session.
> >>> SHOW MASTER STATUS; in a different session.
> >>>
> >>> Stop the master and created a tarball of
> ibdata1, ib_logfile0,
> >>> ib_logfile1 and the database directories (with
> .frm files)
> >>>
> >>> Stop the slave mysqld, unpack the tarball on
> the slave, start
> >>> mysqld.
> >>> CHANGE MASTER ....;
> >>> START SLAVE;
> >>>
> >>>
> >>> 3- config (master and slave) .. only the
> replication related
> >>> variables.
> >>>
> >>> Master:
> >>> server-id = 2
> >>> log_bin = /var/log/mysql/mysql-bin.log
> >>> relay_log = mysqld-relay-bin
> >>> log_slave_updates = 1
> >>> expire_logs_days = 10
> >>> max_binlog_size = 100M
> >>> replication user with super, reload, file
> privileges on *.*.
> >>> innodb_flush_log_at_trx_commit=1
> >>> sync_binlog=1
> >>>
> >>> Slave:
> >>> server-id = 2
> >>> log_bin = /var/log/mysql/mysql-bin.log
> >>> relay_log = mysqld-relay-bin
> >>> log_slave_updates = 1
> >>> expire_logs_days = 10
> >>> max_binlog_size = 100M
> >>> innodb_flush_log_at_trx_commit=1
> >>> sync_binlog=1
> >>>
> >>> It is the unpacking of the ibdata1 file that I
> am unsure about. With
> >>> MyIsam replication I have done before, it was
> simply a matter of copying
> >>> the database directories. The mysql notes on
> replicating with InnoDB are
> >>> very vague.
> >>>
> >>>
> >>>
> >>> Martin.
> >>>
> >>> On 11 August 2010 08:57, Julian
> Rawcliffe<jrawcliffe@stripped
> >>> <mailto:jrawcliffe@stripped>
> >>>
> <mailto:jrawcliffe@stripped<mailto:jrawcliffe@stripped>>>
> >>> wrote:
> >>>
> >>> Greetings
> >>>
> >>> I am try to replicate a couple of databases
> that use InnoDB
> >>> tables.
> >>>
> >>> I have followed the standard replication
> instructions,
> >>> inasmuch as
> >>> they exist for InnoDB, but every time I try
> and start the
> >>> slave I get
> >>>
> >>> Error 'Access denied; you need the SUPER
> privilege for this
> >>> operation'
> >>>
> >>> Indeed, even attempting to insert into a
> replica table as
> >>> root gives
> >>> the same error.
> >>>
> >>> Is it the case that any InnoDB tables must
> have their own
> >>> ibd files in order for replication to
> succeed?
> >>>
> >>> --
> >>> Julian Rawcliffe | Senior Infrastructure
> Engineer | Information
> >>> Systems Division
> >>> London Business School | Regent's Park |
> London NW1 4SA |
> >>> United Kingdom
> >>> Switchboard +44 (0)20 7000 7000 |
> >>> Direct line +44 (0)20 7000 7782 |
> >>> Mobile +44 (0) 7966 907782 |
> >>> Email jrawcliffe@stripped<mailto:jrawcliffe@stripped>
> >>>
> <mailto:jrawcliffe@stripped<mailto:jrawcliffe@stripped>>
> >>>
> >>>
> >>>
> >>>
> ______________________________________________________________________
> >>>
> >>> This email has been scanned by the MessageLabs
> Email
> >>> Security System
> >>> on behalf of the London Business School
> community.
> >>> For more information please visit
> >>> http://www.messagelabs.com/email
> >>>
> >>>
> ______________________________________________________________________
> >>>
> >>> --
> >>> MySQL Replication Mailing List
> >>> For list archives: http://lists.mysql.com/replication
> >>> To unsubscribe:
> >>> http://lists.mysql.com/replication?unsub=1
> >>>
> >>>
> >>>
> >>>
> ______________________________________________________________________
> >>>
> >>> This email has been scanned by the MessageLabs
> Email Security System
> >>> on behalf of the London Business School
> community.
> >>> For more information please visit http://www.messagelabs.com/email
> >>>
> ______________________________________________________________________
> >>>
> >>>
> >>>
> >>> --
> >>> Julian Rawcliffe | Senior Infrastructure
> Engineer | Information
> >>> Systems Division
> >>> London Business School | Regent's Park |
> London NW1 4SA | United Kingdom
> >>> Switchboard +44 (0)20 7000 7000 |
> >>> Direct line +44 (0)20 7000 7782 |
> >>> Mobile +44 (0) 7966 907782 |
> >>> Email jrawcliffe@stripped<mailto:jrawcliffe@stripped>
> >>>
> >>>
> ______________________________________________________________________
> >>>
> >>> This email has been scanned by the MessageLabs
> Email Security System
> >>> on behalf of the London Business School
> community.
> >>> For more information please visit http://www.messagelabs.com/email
> >>>
> ______________________________________________________________________
> >>>
> >>> --
> >>> MySQL Replication Mailing List
> >>> For list archives: http://lists.mysql.com/replication
> >>> To unsubscribe:
> >>> http://lists.mysql.com/replication?unsub=1
> >>>
> >>>
> >>>
> >>>
> ______________________________________________________________________
> >>>
> >>> This email has been scanned by the MessageLabs
> Email Security System
> >>> on behalf of the London Business School
> community.
> >>> For more information please visit http://www.messagelabs.com/email
> >>>
> ______________________________________________________________________
> >>
> >
>
>
> --
> Julian Rawcliffe | Senior Infrastructure Engineer |
> Information Systems
> Division
> London Business School | Regent's Park | London NW1 4SA |
> United Kingdom
> Switchboard +44 (0)20 7000 7000 |
> Direct line +44 (0)20 7000 7782 |
> Mobile +44 (0) 7966 907782 |
> Email jrawcliffe@stripped
>
> ______________________________________________________________________
>
> This email has been scanned by the MessageLabs Email
> Security System
> on behalf of the London Business School community.
> For more information please visit http://www.messagelabs.com/email
> ______________________________________________________________________
>
> --
> MySQL Replication Mailing List
> For list archives: http://lists.mysql.com/replication
> To unsubscribe: http://lists.mysql.com/replication?unsub=1
>
>
