From: Rick James Date: August 11 2010 8:50pm Subject: Re: InnoDB table replication List-Archive: http://lists.mysql.com/replication/1919 Message-Id: <4C630D33.70305@yahoo-inc.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit 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