From: Rick James Date: August 11 2010 11:23pm Subject: Re: InnoDB table replication List-Archive: http://lists.mysql.com/replication/1921 Message-Id: <4C6330EC.8060407@yahoo-inc.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit 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' >>>> IDENTIFIED BY'slavepass';|* >>>> mysql> *|GRANT REPLICATION SLAVE ON *.* >>>> TO'repl'@'%.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>>> > 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>>> >>>> >> >>>> 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 >>>> > >>>> >>>> >>>> >>>> ______________________________________________________________________ >>>> >>>> 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=necrite@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 >>>> ______________________________________________________________________ >>>> >>>> >>>> >>>> -- >>>> 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=necrite@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 >>>> ______________________________________________________________________ >>>> >>> >> > > -- Rick James - MySQL Geek