List:Replication« Previous MessageNext Message »
From:Julian Rawcliffe Date:August 13 2010 8:20am
Subject:Re: InnoDB table replication
View as plain text  
On 12/08/2010 19:53, Database System wrote:
> Did you dump the "mysql" database?

Yes. A trawl through mysqlbinlog shows the affected row in the binlog
after the MASTER_LOG_POS mentioned in the dump.

I know the replication *can* work, but I think I'm going to have to
look for an alternative with this db as it just does not want to work.
I also only have a short time before it hits full operational
service.

I will try the backup tool mentioned by Marcus, though it does
look a bit tricky to build and I don't have the time to figure
it out.

>
> --- On Thu, 8/12/10, Julian Rawcliffe<jrawcliffe@stripped>  wrote:
>
>> From: Julian Rawcliffe<jrawcliffe@stripped>
>> Subject: Re: InnoDB table replication
>> To: replication@stripped
>> Date: Thursday, August 12, 2010, 10:51 AM
>> 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
>> ______________________________________________________________________
>>
>> --
>> 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
______________________________________________________________________
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