List:Replication« Previous MessageNext Message »
From:Rick James Date:August 11 2010 11:23pm
Subject:Re: InnoDB table replication
View as plain text  
SHOW SLAVE STATUS \G  -- any clues here?
Anything in mysqld.err (either machine)?
I assume you have fixed server_id.

On 8/11/10 2:33 PM, Julian Rawcliffe wrote:
> 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
>>>> ______________________________________________________________________
>>>>          
>>>        
>>      
>
>    

-- 
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