List:Replication« Previous MessageNext Message »
From:Julian Rawcliffe Date:August 12 2010 3:51pm
Subject:Re: InnoDB table replication
View as plain text  
I think the mysql installation on the slave had just become completely
mangled so I trashed the replica and started from scratch having got
replication to work on a couple of dev boxes with innodb tables.

I'm now getting duplicate inserts but the problem with the super
privileges has gone. Despite numerous dumps it is always the
same table that is affected, but with a different record
each time.
I'm using --master-data --single-transaction; I don't know if this
could be the problem now.
I guess I'll have to try some binlog processing to try and determine
where the duplication is occurring.


Thanks for all the suggestions so far.


On 12/08/2010 15:30, Database System wrote:
> 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
>>
>>
>
>
>
>
> ______________________________________________________________________
>
> 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
______________________________________________________________________
Thread
InnoDB table replicationJulian Rawcliffe11 Aug
Re: InnoDB table replicationJulian Rawcliffe11 Aug
  • Re: InnoDB table replicationmartín arrieta11 Aug
    • Re: InnoDB table replicationJulian Rawcliffe11 Aug
      • Re: InnoDB table replicationPrabhat Kumar11 Aug
        • Re: InnoDB table replicationDatabase System11 Aug
      • Re: InnoDB table replicationRick James11 Aug
        • Re: InnoDB table replicationJulian Rawcliffe11 Aug
          • Re: InnoDB table replicationRick James12 Aug
          • Re: InnoDB table replicationDatabase System12 Aug
            • Re: InnoDB table replicationJulian Rawcliffe12 Aug
              • Re: InnoDB table replicationMarcus Bointon12 Aug
Re: InnoDB table replicationJulian Rawcliffe13 Aug
Re: InnoDB table replicationJulian Rawcliffe13 Aug
  • Re: InnoDB table replicationRick James17 Aug
    • SUMMARY - InnoDB table replicationJulian Rawcliffe18 Aug