Shulou(Shulou.com)06/01 Report--

This article mainly introduces the installation and configuration of mysql-cluster tutorials, which involves things, learned from the theoretical knowledge, there are many books, literature for your reference, from a practical point of view, accumulated years of practical experience can be shared with you.

Download mysql-cluster compiler-free package: https://dev.mysql.com/downloads/file/?id=469881

The new version supports loading only the index and the data used into memory, rather than adding the entire database of the old version to memory.

Wget https://cdn.mysql.com//Downloads/MySQL-Cluster-7.5/mysql-cluster-gpl-7.5.6-linux-glibc2.5-x86_64.tar.gz

Decompress:

# tar xvf mysql-cluster-gpl-7.5.6-linux-glibc2.5-x86_64.tar.gz

# mv xvf mysql-cluster-gpl-7.5.6-linux-glibc2.5-x86_64 / soft/mysql

Authorize the mysql-cluster directory

# useradd-s mysql / sbin/nologin

# mkdir / soft/mysql/data

# chown-R mysql:mysql / soft/mysql/*

# chmod + x / soft/mysql/bin/ndb_mem*

# cd / soft/mysql/

# ln-s / soft/mysql/bin/ndb_mgmd / sbin/ndb_mgmd # convenient to invoke ndb_mgmd commands directly in shell

# ln-s / soft/mysql/bin/ndb_mgm / sbin/ndb_mgm # convenient to invoke ndb_mgm commands directly in shell

Create a config.ini file

# vim config.ini # is as follows, be sure to pay attention to the format. If you copy from windows, remember to encode it in uft8.

[NDBD DEFAULT]

NoOfReplicas=2 # number of mirrors per data node

DataMemory=500M # memory allocated to data in each data node

IndexMemory=300M # memory allocated to the index in each data node

[NDB_MGMD] # configure the management node. [] cannot be followed by spaces or comments.

Hostname=192.168.240.130

Datadir=/soft/mysql/data # Management Node data (Log) Directory

[NDBD] # data node configuration, [] cannot be followed by spaces or comments

Hostname=192.168.240.129

Datadir=/soft/mysql/data

[NDBD]

Hostname=192.168.240.127

Datadir=/soft/mysql/data

[MYSQLD] # SQL node directory, [] cannot be followed by spaces or comments

Hostname=192.168.240.129

[MYSQLD]

Hostname=192.168.240.128

-

[root@amoebe mysql] #. / bin/ndb_mgmd-f config.ini # start the service

MySQL Cluster Management Server mysql-5.7.18 ndb-7.5.6 # started successfully

[root@amoebe mysql] #. / bin/ndb_mgm # enters the management program, and you can see that the configuration file is in effect, but because the mysql on the node is not configured, it is shown that it is not connected.

-NDB Cluster-Management Client--

Ndb_mgm > show

Connected to Management Server at: localhost:1186

Cluster Configuration

-

[ndbd (NDB)] 2 node (s)

Id=2 (not connected, accepting connect from 192.168.240.129)

Id=3 (not connected, accepting connect from 192.168.240.127)

[ndb_mgmd (MGM)] 1 node (s)

Id=1@192.168.240.130 (mysql-5.7.18 ndb-7.5.6)

[mysqld (API)] 2 node (s)

Id=4 (not connected, accepting connect from 192.168.240.129)

Id=5 (not connected, accepting connect from 192.168.240.128)

-

Configuration Node: 192.168.240.128Universe 192.168.240.129

[root@localhost] # useradd-s / sbin/nologin mysql

[root@localhost] # mkdir / soft/mysql

[root@localhost] # tar xf mysql-cluster-gpl-7.5.6-linux-glibc2.5-x86_64.tar.gz

[root@localhost] # mkdir / soft/mysql/data

[root@localhost] # chown-R mysql:mysql / soft/mysql

[root@localhost] # cd / soft/mysql

[root@localhost mysql] # yum install-y libaio*

Vim support-files/mysql.server # modify service files

Basedir=/soft/mysql

Datadir=/soft/mysql/data

[root@yaho mysql] # bin/mysqld-initialize-user=mysql-basedir=/soft/mysql-datadir=/soft/mysql/data

. / bin/mysqld-- initialize-- user=mysql-- datadir=/soft/mysql/data-- pay attention to the automatically generated temporary password

[Note] A temporary password is generated for root@localhost: pByj > rz95OhV

-

Change the password first:

[root@yaho mysql] # bin/mysql-u root-p'pByj > rz95OhV'-- Log in to mysql, with a special symbol to enclose it in'', step on the hole here

Mysql > use mysql;-- password must be changed before operation

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

Mysql > alter user 'root'@'localhost' identified by' 123456'

Query OK, 0 rows affected (0.00 sec)

[root@mysql] # vim / etc/my.cnf

[mysqld]

Datadir=/soft/mysql/data

User=mysql

Ndbcluster

Lc_messages-dir=/soft/mysql/share/

Ndb-connectstring=192.168.240.130

[mysql_cluster]

Ndb-connectstring=192.168.240.130

[root@mysql] # bin/ndbd-- initial # to initialize data for the first time, do not add-- initial next time, otherwise the database will be emptied

[root@mysql] # ln-s / soft/mysql/bin / sbin/mysql

[root@mysql] # ln-s / soft/mysql/support-files/mysql.server / sbin/mysqld

-

Return to the 130 console:

Ndb_mgm > show

Cluster Configuration

-

[ndbd (NDB)] 2 node (s)

Id=2@192.168.61.128 (mysql-5.7.18 ndb-7.5.6, starting, Nodegroup: 0)-Node ndbd is connected

Id=3@192.168.61.129 (mysql-5.7.18 ndb-7.5.6, starting, Nodegroup: 0)-Node ndbd is connected

[ndb_mgmd (MGM)] 1 node (s)

Id=1@192.168.61.130 (mysql-5.7.18 ndb-7.5.6)

[mysqld (API)] 2 node (s)

[mysqld (API)] 2 node (s)

Id=4@192.168.61.128 (mysql-5.7.18 ndb-7.5.6) # beware of firewalls

Id=5@192.168.61.129 (mysql-5.7.18 ndb-7.5.6) # beware of firewalls

-

Test:

Build a database / table

Log in to mysql on 192.168.61.128:

Mysql > create databases abc charset=gbk

Query OK, 0 rows affected (0.72 sec)

Mysql > create table name (id int (5) not null primary key auto_increment,name varchar (10) not null) engine=ndb

Query OK, 0 rows affected (0.72 sec)

Mysql > insert into name (name) values ('Zhang San'), ('Li Si')

-

Query at 192.168.240.128DB 192.168.240.129

Mysql > select * from tp.name

+-+ +

| | id | name |

+-+ +

| | 1 | Zhang San |

| | 2 | Li Si |

+-+ +

-

Simulate one of the failures of 192.168.240.128swap 192.168.240.129, query again.

Mysql > select * from tp.name

+-+ +

| | id | name |

+-+ +

| | 1 | Zhang San |

| | 2 | Li Si |

+-+ +

Mysql > insert into name (name) values ('Yangtze River'), ('Yellow River')

Query OK, 0 rows affected (0.72 sec)

Mysql > select * from tp.name group by id

+-+ +

| | id | name |

+-+ +

| | 1 | Yangtze River |

| | 2 | Yellow River |

| | 3 | Zhang San |

| | 4 | Li Si |

+-+ +

4 rows in set (0.01sec)

After testing, there are a few points:

When the cluster is down, the mysql of both data nodes cannot access the database

When the cluster is down, the two mysql will still work properly, and the updated data will be synchronized, but the mysql service cannot be restarted.

two。 The data is safe as long as both mysql services are not down at the same time.

3. When cluster hangs and node A dies, the data is updated to node B. when cluster and node An are back online, the data of node An and node B are inconsistent.

4. When the two mysql data are inconsistent, start cluster first, then start the data node and SQL node of the data update to ensure that the cluster has been successfully connected, and finally start another node, which will automatically update the latest data.!! The startup sequence is very important!

5. As long as cluster and ndbd survive, mysql temporarily fails, and data will not be lost.

7. A ndbd must survive for the mysql to work properly.

-

Boot sequence:

Management node ndb_megd-> data node ndbd-> SQL node specifies the private mysqld of the ndbcluster parameter

Ndb_mgmd-f config.ini-> ndbd-> mysqld start

Closing order:

The SQL node specifies the private mysqld of the ndbcluster parameter-> the management node ndb_mem

Mysqld stop-- > ndb_mgm-e shutdown-- > finish

Several errors encountered in the configuration process are attached:

1. Mysqld-- initialize-insecure automatically generates root users without passwords, and mysqld-- initialize automatically generates root users with random passwords

two。 If you set up a mysql account, you need to append the following information to / etc/security/limits.conf, otherwise the limite limit error will be prompted:

Mysql hard nofile 65535

3. [ERROR] Can't find error-message file'/ usr/local/mysql/share/zh-cn/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.

Add a line to the my.cnf:

Lc_messages-dir=/soft/mysql/share/

4. When you start mysql, you may be prompted: ERROR 2002 (HY000): Can't connect to local MySQL server through socket'/ tmp/mysql.sock' (111)

Ln-s / soft/mysql/mysql.sock / tmp/mysql.sock # to use the absolute path

5.ndb_mgm > showNode 2: Forced node shutdown completed. Occured during startphase 4. Caused by error 2353: 'Insuffice. Ndbd service is automatically disconnected, which may be due to jet lag:

Kill the ndb_mgmd on cluser and restart it. The data node can restart ndbd and mysqld.

The difference between NDB engine and Innodb engine: excerpt from http://www.oschina.net/question/565065_79802

N NDB and Innodb are basically the same, and ACID (atomicity, consistency, isolation, persistence) is implemented in basically the same way, but there is one important difference between them: in the same transaction, if something goes wrong, Innodb will continue to execute, while NDB will immediately roll back to the original state (similar to the state of SET XACT_ABORT ON in SqlServer), if you don't pay attention to this. In some extreme cases, there will be data inconsistency in the system, so we still need to pay more attention to it.

The test cases are as follows (tested in the test library):

[Innodb use case]

Mysql > create table test_innodb (i int not null primary key) engine innodb

Mysql > begin

Mysql > insert into test_innodb values (1)

Mysql > insert into test_innodb values (1); # repeated primary key error

Mysql > commit

Mysql > select * from test_innodb

+-- +

| | I |

+-- +

| | 1 |

+-- +

1 row in set (0.00 sec)

[NDB use case]

Mysql > create table test_ndb (i int not null primary key) engine ndbcluster

Mysql > begin

Mysql > insert into test_ndb values (1)

Mysql > insert into test_ndb values (1); # repeated primary key error

Mysql > commit

Mysql > select * from test_ndb

Empty set (0.00 sec)

Read the above installation and configuration of mysql-cluster tutorials, hoping to give you some help in practical application. Due to the limited space in this article, there will inevitably be deficiencies and areas that need to be supplemented. You can continue to pay attention to the industry information section and will update your industry news and knowledge regularly. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.