Re: How to setup cluster replication

[ewen fortune]

Hi Nick,
I think you need to have a check though the resent changes:
For example there were incompatible changes here
http://dev.mysql.com/doc/refman/5.1/en/news-5-1-18.html
checkout
http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-replication-schema.html

Ewen

On Tue, May 6, 2008 at 10:35 AM, ewen fortune <ewen.fortune@stripped> wrote:
> Can you run a quick check on each of the machines to see if they are
>  picking up the correct server-id
>  select @@server_id;
>
>  Ewen
>
>
>
>  On Tue, May 6, 2008 at 10:27 AM, Nick Cen <cenyongh@stripped> wrote:
>  > 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
>