From: Database System
Date: August 11 2010 3:05pm
Subject: Re: InnoDB table replication
List-Archive: http://lists.mysql.com/replication/1918
Message-Id: <304484.22964.qm@web51304.mail.re2.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

The issue was access denied, not how to setup slave. we need to solve acces=
s issue before slave can run. Did you directly access a table on the slave =
by using root, and got the error message?

Lisa

--- On Wed, 8/11/10, Prabhat Kumar <aim.prabhat@stripped> wrote:

> From: Prabhat Kumar <aim.prabhat@stripped>
> Subject: Re: InnoDB table replication
> To: "Julian Rawcliffe" <jrawcliffe@stripped>
> Cc: replication@stripped
> Date: Wednesday, August 11, 2010, 9:59 AM
> might be, on slave that record
> already exist (..direct entry done on
> slave)..
> if that record already exist you can skip that...but before
> skipping must
> verify that record.
>=20
> for skip use following command:
>=20
> mysql> *SET GLOBAL SQL_SLAVE_SKIP_COUNTER=3D1;*
> mysql> *start slave;*
>=20
> On Wed, Aug 11, 2010 at 8:20 PM, Julian Rawcliffe <jrawcliffe@stripped>=
wrote:
>=20
> > On 11/08/2010 14:12, mart=EDn arrieta wrote:
> >
> >> A few comments.
> >>
> >> 1- servers ids must be unique.
> >>
> > Master/slave ids are unique, copy-paste error in my
> post below:)
> >
> >
> >=A0 2- you have to start the initialize the slave
> with one dump
> >>=A0 *|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>=A0 *|CREATE
> USER'repl'@'%.mydomain.com=A0 <http://mydomain.com>'
> >>=A0 IDENTIFIED BY'slavepass';|*
> >> mysql>=A0 *|GRANT REPLICATION SLAVE ON *.*
> TO'repl'@'%.mydomain.com=A0 <
> >> 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:
> >>
> >>=A0 =A0 On 11/08/2010 13:45, mart=EDn arrieta
> wrote:
> >>
> >>=A0 =A0 =A0 =A0 We need more
> information..
> >>
> >>=A0 =A0 =A0 =A0 1- mysql versions?
> >>
> >>=A0 =A0 Server version:
> 5.0.51a-3ubuntu5.7-log (Ubuntu) on Ubuntu 8.04
> >>
> >>
> >>
> >>=A0 =A0 =A0 =A0 2- how did you
> initialize the slave server?
> >>
> >>=A0 =A0 FLUSH TABLES WITH READ LOCK; in one
> master session.
> >>=A0 =A0 SHOW MASTER STATUS; in a different
> session.
> >>
> >>=A0 =A0 Stop the master and created a tarball
> of ibdata1, ib_logfile0,
> >>=A0 =A0 ib_logfile1 and the database
> directories (with .frm files)
> >>
> >>=A0 =A0 Stop the slave mysqld, unpack the
> tarball on the slave, start
> >>=A0 =A0 mysqld.
> >>=A0 =A0 CHANGE MASTER ....;
> >>=A0 =A0 START SLAVE;
> >>
> >>
> >>=A0 =A0 =A0 =A0 3- config (master and
> slave) .. only the replication related
> >>=A0 =A0 =A0 =A0 variables.
> >>
> >>=A0 =A0 Master:
> >>=A0 =A0 server-id=A0 =A0 =A0 =A0
> =A0 =A0 =A0=A0=A0=3D 2
> >>=A0 =A0 log_bin=A0 =A0 =A0 =A0
> =A0 =A0 =A0 =A0=A0=A0=3D
> /var/log/mysql/mysql-bin.log
> >>=A0 =A0 relay_log=A0 =A0 =A0 =A0
> =A0 =A0 =A0=A0=A0=3D mysqld-relay-bin
> >>=A0 =A0 log_slave_updates=A0 =A0
> =A0=A0=A0=3D 1
> >>=A0 =A0 expire_logs_days=A0 =A0 =A0
> =A0 =3D 10
> >>=A0 =A0 max_binlog_size=A0 =A0 =A0
> =A0=A0=A0=3D 100M
> >>=A0 =A0 replication user with super, reload,
> file privileges on *.*.
> >>=A0 =A0 innodb_flush_log_at_trx_commit=3D1
> >>=A0 =A0 sync_binlog=3D1
> >>
> >>=A0 =A0 Slave:
> >>=A0 =A0 server-id=A0 =A0 =A0 =A0
> =A0 =A0 =A0=A0=A0=3D 2
> >>=A0 =A0 log_bin=A0 =A0 =A0 =A0
> =A0 =A0 =A0 =A0=A0=A0=3D
> /var/log/mysql/mysql-bin.log
> >>=A0 =A0 relay_log=A0 =A0 =A0 =A0
> =A0 =A0 =A0=A0=A0=3D mysqld-relay-bin
> >>=A0 =A0 log_slave_updates=A0 =A0
> =A0=A0=A0=3D 1
> >>=A0 =A0 expire_logs_days=A0 =A0 =A0
> =A0 =3D 10
> >>=A0 =A0 max_binlog_size=A0 =A0 =A0
> =A0=A0=A0=3D 100M
> >>=A0 =A0 innodb_flush_log_at_trx_commit=3D1
> >>=A0 =A0 sync_binlog=3D1
> >>
> >>=A0 =A0 It is the unpacking of the ibdata1
> file that I am unsure about. With
> >>=A0 =A0 MyIsam replication I have done
> before, it was simply a matter of
> >> copying
> >>=A0 =A0 the database directories. The mysql
> notes on replicating with InnoDB
> >> are
> >>=A0 =A0 very vague.
> >>
> >>
> >>
> >>=A0 =A0 =A0 =A0 Martin.
> >>
> >>=A0 =A0 =A0 =A0 On 11 August 2010
> 08:57, Julian Rawcliffe <jrawcliffe@stripped
> >>=A0 =A0 =A0 =A0 <mailto:jrawcliffe@stripped>
> >>=A0 =A0 =A0 =A0 <mailto:jrawcliffe@stripped
> <mailto:jrawcliffe@stripped>>>
> >>
> >>=A0 =A0 =A0 =A0 wrote:
> >>
> >>=A0 =A0 =A0 =A0 =A0 =A0
> Greetings
> >>
> >>=A0 =A0 =A0 =A0 =A0 =A0 I am try
> to replicate a couple of databases that use InnoDB
> >>=A0 =A0 =A0 =A0 tables.
> >>
> >>=A0 =A0 =A0 =A0 =A0 =A0 I have
> followed the standard replication instructions,
> >>=A0 =A0 =A0 =A0 inasmuch as
> >>=A0 =A0 =A0 =A0 =A0 =A0 they
> exist for InnoDB,=A0 but every time I try and start the
> >>=A0 =A0 =A0 =A0 slave I get
> >>
> >>=A0 =A0 =A0 =A0 =A0 =A0 Error
> 'Access denied; you need the SUPER privilege for this
> >>=A0 =A0 =A0 =A0 operation'
> >>
> >>=A0 =A0 =A0 =A0 =A0 =A0 Indeed,
> even attempting to insert into a replica table as
> >>=A0 =A0 =A0 =A0 root gives
> >>=A0 =A0 =A0 =A0 =A0 =A0 the same
> error.
> >>
> >>=A0 =A0 =A0 =A0 =A0 =A0 Is it the
> case that any InnoDB tables must have their own
> >>=A0 =A0 =A0 =A0 =A0 =A0 ibd files
> in order for replication to succeed?
> >>
> >>=A0 =A0 =A0 =A0 =A0 =A0 --
> >>=A0 =A0 =A0 =A0 =A0 =A0 Julian
> Rawcliffe | Senior Infrastructure Engineer | Information
> >>=A0 =A0 =A0 =A0 =A0 =A0 Systems
> Division
> >>=A0 =A0 =A0 =A0 =A0 =A0 London
> Business School | Regent's Park | London NW1 4SA |
> >>=A0 =A0 =A0 =A0 United Kingdom
> >>=A0 =A0 =A0 =A0 =A0 =A0
> Switchboard +44 (0)20 7000 7000 |
> >>=A0 =A0 =A0 =A0 =A0 =A0 Direct
> line +44 (0)20 7000 7782 |
> >>=A0 =A0 =A0 =A0 =A0 =A0
> Mobile=A0 =A0 =A0 +44 (0) 7966 907782 |
> >>=A0 =A0 =A0 =A0 =A0 =A0 Email jrawcliffe@stripped
> <mailto:jrawcliffe@stripped>
> >>=A0 =A0 =A0 =A0 <mailto:jrawcliffe@stripped
> <mailto:jrawcliffe@stripped>>
> >>
> >>
> >>
> >>
> >>
> >>=A0
> ______________________________________________________________________
> >>
> >>=A0 =A0 =A0 =A0 =A0 =A0 This
> email has been scanned by the MessageLabs Email
> >>=A0 =A0 =A0 =A0 Security System
> >>=A0 =A0 =A0 =A0 =A0 =A0 on behalf
> of the London Business School community.
> >>=A0 =A0 =A0 =A0 =A0 =A0 For more
> information please visit
> >>=A0 =A0 =A0 =A0 http://www.messagelabs.com/email
> >>
> >>
> >>=A0
> ______________________________________________________________________
> >>
> >>=A0 =A0 =A0 =A0 =A0 =A0 --
> >>=A0 =A0 =A0 =A0 =A0 =A0 MySQL
> Replication Mailing List
> >>=A0 =A0 =A0 =A0 =A0 =A0 For list
> archives: http://lists.mysql.com/replication
> >>=A0 =A0 =A0 =A0 =A0 =A0 To
> unsubscribe:
> >>=A0 =A0 =A0 =A0 http://lists.mysql.com/replication?unsub=3Dnecrite@gmai=
l.com
> >>
> >>
> >>
> >>
> >>=A0
> ______________________________________________________________________
> >>
> >>=A0 =A0 =A0 =A0 This email has been
> scanned by the MessageLabs Email Security
> >> System
> >>=A0 =A0 =A0 =A0 on behalf of the London
> Business School community.
> >>=A0 =A0 =A0 =A0 For more information
> please visit http://www.messagelabs.com/email
> >>
> >>=A0
> ______________________________________________________________________
> >>
> >>
> >>
> >>=A0 =A0 --
> >>=A0 =A0 Julian Rawcliffe | Senior
> Infrastructure Engineer | Information
> >>=A0 =A0 Systems Division
> >>=A0 =A0 London Business School | Regent's
> Park | London NW1 4SA | United
> >> Kingdom
> >>=A0 =A0 Switchboard +44 (0)20 7000 7000 |
> >>=A0 =A0 Direct line +44 (0)20 7000 7782 |
> >>=A0 =A0 Mobile=A0 =A0 =A0 +44 (0)
> 7966 907782 |
> >>=A0 =A0 Email jrawcliffe@stripped
> <mailto:jrawcliffe@stripped>
> >>
> >>=A0 =A0
> ______________________________________________________________________
> >>
> >>=A0 =A0 This email has been scanned by the
> MessageLabs Email Security System
> >>=A0 =A0 on behalf of the London Business
> School community.
> >>=A0 =A0 For more information please visit http://www.messagelabs.com/em=
ail
> >>=A0 =A0
> ______________________________________________________________________
> >>
> >>=A0 =A0 --
> >>=A0 =A0 MySQL Replication Mailing List
> >>=A0 =A0 For list archives: http://lists.mysql.com/replication
> >>=A0 =A0 To unsubscribe:
> >>=A0 =A0 http://lists.mysql.com/replication?unsub=3Dnecrite@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=A0 =A0 =A0 +44 (0) 7966 907782 |
> > Email=A0 =A0 =A0=A0=A0jrawcliffe@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=3Daim.prabhat@stripped
> >
> >
>=20
>=20
> --=20
> Best Regards,
>=20
> Prabhat Kumar
> MySQL DBA
>=20
> My Blog: http://adminlinux.blogspot.com
> My LinkedIn: http://www.linkedin.com/in/profileprabhat
> =0A=0A=0A