MySql NDB cluster replication配置

时间：2021-09-25 00:03:23

官方文档： https://dev.mysql.com/doc/refman/5.6/en/mysql-cluster.html

安装过程：略

单机部署：

1. 创建ndb_mgmd配置文件：

SC-1:/storage/ezohenz # cat m

[NDB_MGMD]

HostName=SC-1

[NDBD DEFAULT]

NoOfReplicas=2

[NDBD]

HostName=SC-1

Id=11

DataDir=/storage/ezohenz/data_ndbd1

[NDBD]

HostName=SC-1

Id=12

DataDir=/storage/ezohenz/data_ndbd2

[MYSQLD]

HostName=SC-1

Id=21

[MYSQLD]

HostName=SC-2

Id=22

2. 创建ndbd配置文件

SC-1:/storage/ezohenz # cat d

[MYSQLD]

ndbcluster

ndb-connectstring=SC-1:1186

[MYSQL_CLUSTER]

ndb-connectstring=SC-1:1186

3. 创建mysqld配置文件

SC-1:/storage/ezohenz # cat q

[MYSQLD]

ndbcluster

ndb-connectstring=SC-1:1186

default-storage-engine=ndbcluster

user=mysql

4. 安装初始数据库

mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=./data_mysqld

5. 按顺序启动ndb_mgmd, ndbd, mysqld

ndb_mgmd -f ./m --initial

ndbd --defaults-file=./d --initial

mysqld --defaults-file=./q --datadir=./data_mysqld &

6. 查看ndb cluster状态

ndb_mgm -e show

注意貌似需要确认配置文件m中的所有ndbd都启动并连接上mgmd，mysqld才能正常连接上mgmd并创建engine为ndb的数据表。

Geographic redundancy

以上为单机cluster，需要多个才有实际意义。而在不同地区的cluster之间，也可以进行数据的同步。

现在假设有另一套单机cluster，配置如上面的1-6步。假设两个cluster分别为A和B，则：

分别修改A和B的mysqld配置文件并重启mysqld：

SC-1:/storage/db_sc1 # cat q

[MYSQLD]

ndbcluster

ndb-connectstring=SC-1:1186

default-storage-engine=ndbcluster

user=mysql

server-id=1

replicate-do-db=test

replicate-do-table=test.ctest

slave-skip-errors=1062,1032,1590

log-slave-updates

log-bin=

sync_binlog=1

binlog_format=MIXED

expire_logs_days=3

binlog-do-db=test

slave-net-timeout=10

务必将A和B的server-id配置成不同的值。

重启后进入mysql客户端查看master状态

执行show master status;

mysql> show master status;

+-----------------+----------+--------------+------------------+-------------------+

| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+-----------------+----------+--------------+------------------+-------------------+

| SC-1-bin.000001 |      120 | test         |                  |                   |

+-----------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

分别记录A和B的File，在下面的命令中会用到。

分别重新配置A和B的slave

在A (sc-1)的mysql客户端输入以下命令

stop slave;

change master to master_host='sc-2', master_log_file='SC-2-bin.000001', master_user='mysql', master_password='', master_retry_count=86400, master_connect_retry=5;

start slave;

在B (sc-2)的mysql客户端输入以下命令

stop slave;

change master to master_host='sc-1', master_log_file='SC-1-bin.000001', master_user='mysql', master_password='', master_retry_count=86400, master_connect_retry=5;

start slave;

此时将会在两个site的客户端上分别看到连接成功的消息。

检查配置结果

在A和B上分别执行show slave status\G，应该看到Slave_IO_Running和Slave_SQL_Running状态都是Yes：

show slave status\G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: sc-2

                  Master_User: mysql

                  Master_Port: 3306

                Connect_Retry: 5

              Master_Log_File: SC-2-bin.000001

          Read_Master_Log_Pos: 120

               Relay_Log_File: SC-1-relay-bin.000002

                Relay_Log_Pos: 282

        Relay_Master_Log_File: SC-2-bin.000001

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB: test

          Replicate_Ignore_DB:

           Replicate_Do_Table: test.ctest

       Replicate_Ignore_Table:

      Replicate_Wild_Do_Table:

  Replicate_Wild_Ignore_Table:

                   Last_Errno: 0

                   Last_Error:

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 120

              Relay_Log_Space: 454

              Until_Condition: None

               Until_Log_File:

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File:

           Master_SSL_CA_Path:

              Master_SSL_Cert:

            Master_SSL_Cipher:

               Master_SSL_Key:

        Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 0

               Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 2

                  Master_UUID: 5301a65f-34b9-11e8-a869-021020010200

             Master_Info_File: /cluster/storage/db_sc1/data_mysqld/master.info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

           Master_Retry_Count: 86400

                  Master_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp:

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set:

            Executed_Gtid_Set:

                Auto_Position: 0

1 row in set (0.00 sec)

通过在数据库中修改数据来检查配置是否生效

在A的数据库test中建一个ctest表，并在其中插入一条记录，应该能在B上看到插入的结果。反之亦然。

mysql> USE test;

Database changed

mysql> CREATE TABLE ctest (i INT) ENGINE=NDBCLUSTER;

2018-03-31 16:49:10 619 [Note] NDB Binlog: CREATE TABLE Event: REPL$test/ctest

2018-03-31 16:49:10 619 [Note] NDB Binlog: logging ./test/ctest (UPDATED,USE_WRITE)

Query OK, 0 rows affected (0.48 sec)

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| ctest          |

+----------------+

1 row in set (0.00 sec)

mysql> select * from ctest;

Empty set (0.00 sec)

mysql> insert into ctest values (111);

Query OK, 1 row affected (0.00 sec)

mysql> select * from ctest;

+------+

| i    |

+------+

|  111 |

+------+

1 row in set (0.00 sec)

全文完。

相关文章