Re: How to setup cluster replication

[Nick Cen]

Hi,

The server-id is unique, but the record inside the ndb_apply_status is not
unique. i have a lot of record all have the same server-id, and the
replication works. i am not sure whether this is the decide behavior.

2008/5/6 ewen fortune <ewen.fortune@stripped>:

> Hi,
> server-id must be a unique (across all the servers involved in
> replication) numeric value
>
>
> http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#option_mysqld_server-id
>
> I think you might have a duplicate somewhere
>
>
> Ewen
>
> On Tue, May 6, 2008 at 4:22 AM, Nick Cen <cenyongh@stripped> wrote:
> > Hi,
> >
> > I create the ndb_apply_status by mysql on the Slave Server, and it seems
> to
> > work, when i create table and insert a value in the M2, these operations
> are
> > being replicate to the Slave. But when i try to insert another value,
> the
> > log of Slave Server indicate an error.
> >
> > 080506  1:45:24 [ERROR] Slave SQL: Could not execute Write_rows event on
> > table mysql.ndb_apply_status; Duplicate entry '2' for key 'PRIMARY',
> > Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's
> master
> > log mysql-bin.000006, end_log_pos 1092, Error_code: 0
> >  080506  1:45:24 [ERROR] Slave SQL: Duplicate entry '2' for key
> 'PRIMARY',
> > Error_code: 1062
> > 080506  1:45:24 [Warning] Slave: Duplicate entry '2' for key 'PRIMARY'
> > Error_code: 1062
> > 080506  1:45:24 [ERROR] Error running query, slave SQL thread aborted.
> Fix
> > the problem, and restart the slave SQL thread with "SLAVE START". We
> stopped
> > at log 'mysql-bin.000006' position 863
> >
> > I think the Duplicate entry '2' is stands for the server-id value of M2.
> >
> >
> >
> > 2008/5/5 ewen fortune <ewen.fortune@stripped>:
> > > Hi,
> > >
> > > From your previous mail with the create statement:
> > >
> > > """
> > > mysql> CREATE TABLE `ndb_apply_status` (
> > >    ->   `server_id` int(10) unsigned NOT NULL,
> > >    ->   `epoch` bigint(20) unsigned NOT NULL,
> > >    ->   `log_name` varchar(255) CHARACTER SET latin1 COLLATE
> > > latin1_bin NOT NULL,
> > >    ->   `start_pos` bigint(20) unsigned NOT NULL,
> > >    ->   `end_pos` bigint(20) unsigned NOT NULL,
> > >    ->   PRIMARY KEY (`server_id`) USING HASH
> > >    -> ) ENGINE=ndbcluster DEFAULT CHARSET=latin1
> > >    -> ;
> > > ERROR 1498 (HY000): For the partitioned engine it is necessary to
> > > define all partitions
> > > """
> > >
> > >
> > > The ENGINE is stated as being ndbcluster, this is what i was asking
> about.
> > >
> > >
> > > > From the reference doc, when i wanna to start the slave, i should
> pass a
> > > > --ndbcluster parameter to mysql.server script, what does this
> parameter
> > > > being used for, as the slave is not cluster can i just amit it. Any
> > hints
> > >
> > > I believe "--ndbcluster" is needed to support row based replication,
> > > not sure.....
> > >
> > >
> > > > New Question, as in the slave's "change master to" statement point
> to a
> > > > single server in the cluster, will this be a single point of failure
> > when
> > > > that server is down? Thanks in advance.
> > >
> > > Yes, there is a possibility to run two channel replication to avoid
> spof
> > > (for example)
> > >
> >
> http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-replication-two-channels.html
> > >
> > > Ewen
> > >
> > >
> > >
> > >
> > > On Mon, May 5, 2008 at 12:55 PM, Nick Cen <cenyongh@stripped>
> wrote:
> > > > no, the slave is not cluster, i have set the default engine to
> MyISAM.
> > > > From the reference doc, when i wanna to start the slave, i should
> pass a
> > > > --ndbcluster parameter to mysql.server script, what does this
> parameter
> > > > being used for, as the slave is not cluster can i just amit it. Any
> > hints
> > > >
> > > > New Question, as in the slave's "change master to" statement point
> to a
> > > > single server in the cluster, will this be a single point of failure
> > when
> > > > that server is down? Thanks in advance.
> > > >
> > > >
> > > >
> > > >  2008/5/5 ewen fortune <ewen.fortune@stripped>:
> > > > > Are you replicating to another NDB cluster? If not, switch the
> ENGINE
> > > > > to MyISAM on the slave create ndb_apply_status table.
> > > > >
> > > > > Ewen
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > On Mon, May 5, 2008 at 9:34 AM, Nick Cen
> <cenyongh@stripped>
> wrote:
> > > > > > I try to create the mysql.ndb_apply_status by myself, but
> it
> give
> > the
> > > > > > following error.
> > > > > >
> > > > > > mysql> CREATE TABLE `ndb_apply_status` (
> > > > > >     ->   `server_id` int(10) unsigned NOT NULL,
> > > > > >     ->   `epoch` bigint(20) unsigned NOT NULL,
> > > > > >      ->   `log_name` varchar(255) CHARACTER SET latin1
> COLLATE
> > > > latin1_bin
> > > > > > NOT NULL,
> > > > > >     ->   `start_pos` bigint(20) unsigned NOT NULL,
> > > > > >     ->   `end_pos` bigint(20) unsigned NOT NULL,
> > > > > >     ->   PRIMARY KEY (`server_id`) USING HASH
> > > > > >      -> ) ENGINE=ndbcluster DEFAULT CHARSET=latin1
> > > > > >     -> ;
> > > > > > ERROR 1498 (HY000): For the partitioned engine it is
> necessary
> to
> > define
> > > > all
> > > > > > partitions
> > > > > >
> > > > > >
> > > > > > 2008/5/5 ewen fortune <ewen.fortune@stripped>:
> > > > > >
> > > > > >
> > > > > > > Hi,
> > > > > > > Sounds like the ndb_schema tables havn't been created.
> These
> are
> > > > > > > needed for ndb replication.
> > > > > > >
> > > > > >
> > > >
> >
> http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-replication-schema.html
> > > > > > > """
> > > > > > > These tables are created during the MySQL installation
> process
> by
> > the
> > > > > > > mysql_install_db script,
> > > > > > > and include a table for storing the binary log's
> indexing
> data.
> > > > > > > """
> > > > > > >
> > > > > > > Ewen
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > On Mon, May 5, 2008 at 6:54 AM, Nick Cen
> <cenyongh@stripped>
> > wrote:
> > > > > > > > Hi All,
> > > > > > > >
> > > > > > > >  I wanna to build up a whole new cluster
> replication, i have
> 3
> > > > machines
> > > > > > M1,
> > > > > > > >  M2 and S2. M1 & M2 makeup the cluster. M2 and
> S2 makeup a
> > > > replication
> > > > > > > >  channel.
> > > > > > > >  I am using the tar ball
> mysql-5.1.23-ndb-6.2.14-telco.tar.
> And
> > > > follow
> > > > > > the
> > > > > > > >  instructions in this page
> > > > > > > >
> > > > > >
> > > >
> >
> http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-replication-preparation.html
> > > > > > > >  ,
> > > > > > > >  the initial table is create with the
> mysql_install_db
> script.
> > > > > > > >
> > > > > > > >  The cluster is run without problem and here is
> the my.cnf
> in M2
> > and
> > > > S2.
> > > > > > > >
> > > > > > > >  M2's my.cnf
> > > > > > > >  [client]
> > > > > > > >  default-character-set=utf8
> > > > > > > >  [mysqld_safe]
> > > > > > > >  default-character-set=utf8
> > > > > > > >  # Options for mysqld process:
> > > > > > > >  [mysqld]
> > > > > > > >  ndbcluster                      # run NDB storage
> engine
> > > > > > > >  ndb-connectstring=192.168.145.131  # location of
> management
> > server
> > > > > > > >  default-character-set=utf8
> > > > > > > >  default-storage-engine=ndbcluster
> > > > > > > >  # Replication
> > > > > > > >  log-bin=mysql-bin
> > > > > > > >  binlog-format=ROW
> > > > > > > >  server-id=2
> > > > > > > >  # Options for ndbd process:
> > > > > > > >  [mysql_cluster]
> > > > > > > >  ndb-connectstring=192.168.145.131  # location of
> management
> > server
> > > > > > > >
> > > > > > > >  S2's my.cnf
> > > > > > > >  [client]
> > > > > > > >  default-character-set=utf8
> > > > > > > >  [mysqld_safe]
> > > > > > > >  default-character-set=utf8
> > > > > > > >  # Options for mysqld process:
> > > > > > > >  [mysqld]
> > > > > > > >  ndbcluster
> > > > > > > >  default-character-set=utf8
> > > > > > > >  default-storage-engine=MyISAM
> > > > > > > >  server-id=3
> > > > > > > >
> > > > > > > >  The bin-log file info is accquire with the
> following sql
> > statement
> > > > > > > >  mysql> flush tables with read lock;
> > > > > > > >  Query OK, 0 rows affected (0.02 sec)
> > > > > > > >  mysql> show master status;
> > > > > > > >
> > +------------------+----------+--------------+------------------+
> > > > > > > >  | File             | Position | Binlog_Do_DB |
> Binlog_Ignore_DB
> > |
> > > > > > > >
> > +------------------+----------+--------------+------------------+
> > > > > > > >  | mysql-bin.000001 |      233 |              |
> > |
> > > > > > > >
> > +------------------+----------+--------------+------------------+
> > > > > > > >  1 row in set (0.00 sec)
> > > > > > > >
> > > > > > > >
> > > > > > > >  The cluster(M1,M2) and S2 can start without
> problem.
> > > > > > > >
> > > > > > > >  When i create a table in M2, a replicate table is
> create in
> S2.
> > but
> > > > > > when i
> > > > > > > >  insert a record in to M2's table nothing happen
> in S2. And
> here
> > is
> > > > the
> > > > > > > >  mysqld log of S2.
> > > > > > > >  080505  7:38:01 [Note] Slave I/O thread:
> connected to
> master '
> > > > > > > >  repl@stripped:3306',replication started in
> log
> > > > > > 'mysql-bin.000001' at
> > > > > > > >  position 233
> > > > > > > >  080505  7:39:00 [ERROR] Slave SQL: Error 'Table
> > > > > > 'mysql.ndb_apply_status'
> > > > > > > >  doesn't exist' on opening table
> `mysql`.`ndb_apply_status`,
> > > > Error_code:
> > > > > > 1146
> > > > > > > >  080505  7:39:00 [Warning] Slave: Table
> 'mysql.ndb_apply_status'
> > > > doesn't
> > > > > > > >  exist Error_code: 1146
> > > > > > > >  080505  7:39:00 [Warning] Slave: Got error 4009
> 'Cluster
> > Failure'
> > > > from
> > > > > > NDB
> > > > > > > >  Error_code: 1296
> > > > > > > >  080505  7:39:00 [ERROR] Error running query,
> slave SQL
> thread
> > > > aborted.
> > > > > > Fix
> > > > > > > >  the problem, and restart the slave SQL thread
> with "SLAVE
> > START".
> > > > We
> > > > > > stopped
> > > > > > > >  at log 'mysql-bin.000001' position 322
> > > > > > > >
> > > > > > > >
> > > > > > > >  Any idea, thanks in advance.
> > > > > > > >
> > > > > > > >  Nick
> > > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > --
> > > > > > http://candynick.vicp.net
> > > > >
> > > >
> > > >
> > > >
> > > > --
> > > > http://candynick.vicp.net
> > >
> >
> >
> >
> > --
> > http://candynick.vicp.net
>



-- 
http://candynick.vicp.net