mysql ndbcluster集群复制到innodb单机环境配置搭建

迈不过友情╰ 2022-08-21 11:41 225阅读 0赞

**mysql ndbcluster集群复制到innodb单机环境配置搭建**

1.环境准备

一套ndbcluster集群环境

一套innodb单机环境

ndbcluster环境配置

IP：192.168.1.91   mydb1

IP：192.168.1.94   mydb2

innodb环境

IP：192.168.1.39   mydb3

确保环境搭建完毕，能正常使用

2.复制配置

（1）确保集群的版本和mysql版本兼容

（2）在集群master上开启二进制日志

添加如下配置：(在集群的两个sql节点都开启)

\#开启二进制日志

log-bin=/data/mysqllog/ndb1.bin

log-bin-index=/data/mysqllog/ndb1.index

expire\_logs\_days=10

\#max\_binlog\_size=500M

（3）在master上创建复制的slave账号

mysql> GRANT REPLICATION SLAVE

ON \*.\* TO 'myslave'@'192.168.1.39'

IDENTIFIED BY 'myslave';

（4）在slave创建ndb\_apply\_status、ndb\_index\_stat\_sample、ndb\_index\_stat\_head

To create the table execute the following statements on the slave:

use 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=InnoDB DEFAULT CHARSET=latin1;

To use a replication filter, you can add the following to the MySQL configuration file on the slave:

use mysql;

SET GLOBAL innodb\_file\_per\_table = ON,

GLOBAL innodb\_large\_prefix = ON,

GLOBAL innodb\_file\_format='barracuda';

CREATE TABLE \`ndb\_index\_stat\_sample\` (

\`index\_id\` int(10) unsigned NOT NULL,

\`index\_version\` int(10) unsigned NOT NULL,

\`sample\_version\` int(10) unsigned NOT NULL,

\`stat\_key\` varbinary(3056) NOT NULL,

\`stat\_value\` varbinary(2048) NOT NULL,

PRIMARY KEY (\`index\_id\`,\`index\_version\`,\`sample\_version\`,\`stat\_key\`),

KEY \`ndb\_index\_stat\_sample\_x1\` (\`index\_id\`,\`index\_version\`,\`sample\_version\`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW\_FORMAT=DYNAMIC;

CREATE TABLE \`ndb\_index\_stat\_head\` (

\`index\_id\` int(10) unsigned NOT NULL,

\`index\_version\` int(10) unsigned NOT NULL,

\`table\_id\` int(10) unsigned NOT NULL,

\`frag\_count\` int(10) unsigned NOT NULL,

\`value\_format\` int(10) unsigned NOT NULL,

\`sample\_version\` int(10) unsigned NOT NULL,

\`load\_time\` int(10) unsigned NOT NULL,

\`sample\_count\` int(10) unsigned NOT NULL,

\`key\_bytes\` int(10) unsigned NOT NULL,

PRIMARY KEY (\`index\_id\`,\`index\_version\`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

（5）在master配置文件添加如下配置

server-id = 1000

binlog\_format = ROW

ndb-log-update-as-write=0

log-bin-use-v1-row-events = 1

注：mysql集群的my.cnf配置文件

\[mysqld\]

\# Options for mysqld process:

ndbcluster                      \#run NDB storage engine

server-id = 1000

binlog\_format = ROW

ndb-log-update-as-write=0

log-bin-use-v1-row-events = 1

\#skip-grant-tables

character\_set\_server=utf8

lower\_case\_table\_names=1

max\_connections=1000

ndb-connectstring=192.168.1.91,192.168.1.94  \# location of management server

default-storage-engine=ndbcluster

datadir=/data/mysql

max\_allowed\_packet=20M

open\_files\_limit=65535

\#开启二进制日志

log-bin=/data/mysqllog/ndb1.bin

log-bin-index=/data/mysqllog/ndb1.index

expire\_logs\_days=10

\#max\_binlog\_size=500M

\#开启慢查询日志

slow\_query\_log=1

slow-query-log-file=/data/mysqllog/mysqlslow.log

long\_query\_time=1

\#错误日志

log-error=/data/mysqllog/mysqlerror.log

\# provide connection string for management server host (default port: 1186)

\[ndbd\]

connect-string=192.168.1.91,192.168.1.94  \# location of management server

\#provide connection string for management server host (default port: 1186)

\[ndb\_mgm\]

connect-string=192.168.1.91,192.168.1.94  \# location of management server

\#provide location of cluster configuration file

\[ndb\_mgmd\]

config-file=/var/lib/mysql-cluster/config.ini

（6）在slave配置文件中添加如下：

server-id = 3000

relay-log-purge=1

skip-slave-start

slave\_exec\_mode = IDEMPOTENT

sql\_log\_bin = 0

replicate-ignore-table=mysql.ndb\_apply\_status

replicate-wild-ignore-table = mysql.ndb\\\_index\\\_stat\\\_%

注：mysql slave配置文件：

\[mysqld\]

\# Options for mysqld process:

\#skip-grant-tables

character\_set\_server=utf8

lower\_case\_table\_names=1

max\_connections=1000

datadir=/data/mysql

server-id = 3000

relay-log-purge=1

skip-slave-start

slave\_exec\_mode = IDEMPOTENT

replicate-ignore-table=mysql.ndb\_apply\_status

replicate-wild-ignore-table = mysql.ndb\\\_index\\\_stat\\\_%

innodb\_buffer\_pool\_size=4000M

innodb\_log\_file\_size=256M

innodb\_log\_files\_in\_group=3

innodb\_io\_capacity=500

innodb\_flush\_method=O\_DIRECT

innodb\_flush\_log\_at\_trx\_commit=1

innodb\_open\_files=8192

（7）在slave执行如下语句配置复制

mysqlS> CHANGE MASTER TO

MASTER\_HOST='192.168.1.91',

MASTER\_PORT=3306,

MASTER\_USER='myslave',

MASTER\_PASSWORD='myslave';

（8）初始化数据把mysql cluster数据迁移到slave上

导出全部数据包括（mysql）

mysqldump  -uroot -p  --add-drop-database --all-databases --master-data=2 > db.dump

导出指定的数据库

mysqldump  -uroot -p --add-drop-database --databases uxpay uxorder --master-data=2 > ux.dump

把导出的dump文件传输到slave上

转换dump文件中表的引擎 ndbcluster转换为innodb

sed -i 's/ENGINE=ndbcluster/ENGINE=innodb/g' db.dump

（8）在slave执行编辑后的dump文件

mysql -uroot -p < db.dmp

（9）执行change master设置日志应用开始的位置

CHANGE MASTER TO

MASTER\_LOG\_FILE='@file',

MASTER\_LOG\_POS=@pos;

例如：

CHANGE MASTER TO MASTER\_LOG\_FILE='ndb1.000006', MASTER\_LOG\_POS=141993409;

启动复制

mysql> START SLAVE;

（10）查看复制的状态

mysql> show slave status \\G;

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

Slave\_IO\_State: Waiting for master to send event

Master\_Host: 192.168.1.91

Master\_User: myslave

Master\_Port: 3306

Connect\_Retry: 60

Master\_Log\_File: ndb1.000010

Read\_Master\_Log\_Pos: 6268

Relay\_Log\_File: mydb3-relay-bin.000006

Relay\_Log\_Pos: 4544

Relay\_Master\_Log\_File: ndb1.000010

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: Yes

Replicate\_Do\_DB:

Replicate\_Ignore\_DB:

Replicate\_Do\_Table:

Replicate\_Ignore\_Table: mysql.ndb\_apply\_status

Replicate\_Wild\_Do\_Table:

Replicate\_Wild\_Ignore\_Table: mysql.ndb\\\_index\\\_stat\\\_%

Last\_Errno: 0

Last\_Error:

Skip\_Counter: 0

Exec\_Master\_Log\_Pos: 6268

Relay\_Log\_Space: 5312

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: 1000

Master\_UUID: 67004af0-0c27-11e6-9fff-7427eaa3c20f

Master\_Info\_File: /data/mysql/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)

3.最后验证复制

mysql ndbcluster集群复制到innodb单机环境配置搭建

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 mysql8搭建innodb_cluster集群

相关 Cenots搭建mysql集群：主从复制

相关 Mysql组复制-简单集群搭建

相关 mysql ndbcluster集群复制到innodb单机环境配置搭建

相关 MySQL Cluster单机搭建集群环境

相关 <转>MySQL Cluster （二） --- 单机搭建集群环境

相关 MySQL Cluster （二） --- 单机搭建集群环境

相关基于mgr组复制搭建mysql innodb cluster集群

相关 mysql集群搭建之主从复制

相关 docker环境下zookeeper单机搭建、集群搭建