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

-- 
Rick James - MySQL Geek

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