List:Replication« Previous MessageNext Message »
From:Julian Rawcliffe Date:August 13 2010 4:32pm
Subject:Re: InnoDB table replication
View as plain text  
On 13/08/2010 15:23, Database System wrote:
> Good luck. Let me know what the tool did for you.
What a way to finish a Friday afternoon.! I have got the
replication working!
It turns out the --opt that I was using for mysqldump was not
a great idea. Also, suing --single-transaction breaks things.

Oh, happy days!


>
> Lisa
> --- On Fri, 8/13/10, Julian Rawcliffe<jrawcliffe@stripped>  wrote:
>
>> From: Julian Rawcliffe<jrawcliffe@stripped>
>> Subject: Re: InnoDB table replication
>> To: replication@stripped
>> Date: Friday, August 13, 2010, 3:20 AM
>> 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
>> ______________________________________________________________________
>>
>> --
>> 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