List:Replication« Previous MessageNext Message »
From:Julian Rawcliffe Date:August 11 2010 2:50pm
Subject:Re: InnoDB table replication
View as plain text  
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
______________________________________________________________________
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