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

MySQL Cluster MySQL cluster

Basic concepts:

"NDB" is an in-memory storage engine, which has the characteristics of high availability and good data consistency.

MySQL Cluster can configure the NDB storage engine with a variety of failover and load balancing options, but this is easiest at the Cluster level. MySQL Cluster's NDB storage engine contains the complete dataset, depending only on other data within the Cluster itself.

MGM nodes: the role of such nodes is to manage other nodes in the MySQL Cluster, such as providing configuration data, starting and stopping nodes, running backups, and so on. Because such nodes are responsible for managing the configuration of other nodes, they should be started before starting other nodes. The MGM node is started with the command "ndb_mgmd".

Data nodes: this type of node is used to hold Cluster data. The number of data nodes is related to the number of copies and is a multiple of the fragment. For example, for two replicas, each with two fragments, there are four data nodes. However, there is no need to set up multiple copies. The data node is started with the command "ndbd".

SQL node: this is the node used to access Cluster data. For MySQL Cluster, the client node is a traditional MySQL server that uses the NDB Cluster storage engine. Typically, SQL nodes are started with the command "mysqld-ndbcluster" or with "mysqld" after adding "ndbcluster" to "my.cnf".

The management server (MGM node) is responsible for managing Cluster configuration files and Cluster logs. Each node in the Cluster retrieves configuration data from the management server and requests to determine how the management server is located. When a new event occurs within the data node, the node transmits information about such events to the management server, and then writes such information to the Cluster log.

Overview of cluster configuration:

Installation version: mysql cluster 7.3.2

Operating system: centos6.3 (X64)

Software name: mysql-cluster-gpl-7.3.2-linux-glibc2.5-x86_64.tar.gz (Universal Edition)

Management Node IP:192.168.0.202

Data Node-SQL Node IP:192.168.0.203

Data Node-SQL Node IP:192.168.0.204

Install the dependency package: yum install-y glibc perl libaio-devel

X32-bit system to install compatible library group: yum groupinstall "Compatibility libraries"

I. manage the installation and configuration of nodes

1. Install mysql-cluster

Groupadd mysqluseradd-g mysql- s / sbin/nologin mysqltar-zxvf mysql-cluster-gpl-7.3.2-linux-glibc2.5-x86_64. Tar .gzmv mysql-cluster-gpl-7.3.2-linux-glibc2.5-x86_64 / usr/local/mysql-clusterchown-R root.mysql / usr/local/mysql/chown-R mysql.mysql / usr/local/mysql/data//usr/local/mysql/scripts/mysql_install_db-- basedir= / usr/local/mysql--datadir= / usr/local/mysql/data-- user=mysql & # initialize the database cp-rf / Usr/local/mysql/bin/ndb_mgm * / usr/local/bin/ # copy ndb node management commands locally Easy to use

2. Modify the main configuration file of mysql

Vi / etc/my .cnf [MYSQLD] user = mysql socket = / tmp/mysql .sock basedir = / usr/local/mysql # installation directory datadir = / usr/local/mysql/data # database storage directory character-sets-server=UTF8 ndbcluster # run NDB storage engine ndb-connectstring=192.168.0.202 lower_case_table_names=1 # whether the table name is case-sensitive or not Otherwise, the table name under linux is a case-sensitive [MYSQL_CLUSTER] ndb-connectstring=192.168.0.202 # Mysql Cluster management node IP

3. Create a mysql cluster configuration file

Mkdir / var/lib/mysql-clustervi / var/lib/mysql-cluster/config .ini [ndbd default] NoOfReplicas=2 # defines the number of copies of the same data in the Cluster environment, and the maximum amount of data memory allocated for 4DataMemory=256M # is allocated according to the appropriate amount of memory on the native server. IndexMemory=256M # sets the memory segment size used to store index (non-primary key) data # the amount of data that a NDB node can store is constrained by the two parameters DataMemory and IndexMemory After either of them reaches the limit, it is impossible to increase the amount of data that can be stored. If you continue to save to the data system, you will get an error of "table is full". [ndb_mgmd] nodeid=1hostname = 192.168.0.202datadir = / var/lib/mysql-cluster/ [NDBD] nodeid=2hostname = 192.168.0.203datadir = / usr/local/mysql/ data [ndbd] nodeid=3hostname = 192.168.0.204datadir = / usr/local/mysql/ datamysqld] nodeid=4hostname = 192.168.0.203 [mysqld] nodeid=5hostname = 192.168.0.204

Save and exit!

Chown mysql.mysql / var/lib/mysql-cluster/config .ini

2. The configuration of two data nodes and SQL nodes are the same.

1. Install mysql-cluster

Tar-zxvf mysql-cluster-gpl-7.3.2-linux-glibc2.5-x86_64. Tar .gzmv mysql-cluster-gpl-7.3.2-linux-glibc2.5-x86_64 / usr/local/mysqlgroupadd mysqluseradd-g mysql- s / sbin/nologin mysqlchown-R root.mysql / usr/local/mysqlchown-R mysql.mysql / usr/local/mysql/data/usr/local/mysql/scripts/mysql_install_db-- basedir= / usr/local/mysql-- datadir= / usr/local/mysql/data-- user=mysql & # initialization data Library cp / usr/local/mysql/support-files/mysql .server / etc/init .d / mysqldcp / usr/local/mysql/support-files/medium .cnf / etc/my .cnfchmod + x / etc/init .d / mysqld

2. Modify mysql configuration file

Vi / etc/my. CNF [MYSQLD] user=mysqlcharacter_set_server=utf8ndbclusterndb-connectstring=192.168.0.202default-storage-engine=ndbcluster # setting defaults to NDB storage engine datadir= / usr/local/mysql/databasedir= / usr/local/my SQL [MySQL _ CLUSTER] ndb-connectstring=192.168.0.202 # mysql cluster management node IP

3. Test (first turn off the firewall (IPTABLES) and Selinux of the three servers)

1. Start the management node

Ndb_mgmd-f / var/lib/mysql-cluster/config .ini-- initialnetstat-tuplna | grep 1186 # default connection port 1186, successfully started tcp 000.0.0.0V1186 0.0.0.0initialnetstat * LISTEN 1369 / ndb_mgmd#--initial: add it for the first time, and do not add it at other times, otherwise the data will be emptied, unless it is restarted after backup, recovery or configuration changes.

If there is an error in startup: delete all the nodeid set in config.ini!

2. Start two data nodes and SQL nodes

Data node: / usr/local/mysql/bin/ndbd-- initial

SQL node: bin/mysqld_safe-- user=mysql & or service mysqld start

3. Check the cluster status

Ndb_mgm-e show # shows that the management node and data node are configured successfully

4. Create a database to verify synchronization

Mysql-u root-p

Mysql > create database test

Mysql > use test

Mysql > create table abc (id int) engine=ndbcluster; # specifies that the engine of the database table is NDB, otherwise synchronization fails

Mysql > Insert into abc () values (1)

Mysql > select * from abc

# at this point, it depends on whether the two data are the same. If they are consistent, the cluster has been successful!

Note:

1. Be sure to use ENGINE=NDB or ENGINE=NDBCLUSTER to specify the use of the NDB cluster storage engine when creating the table, or to change the storage engine of the table with the ALTER TABLE option.

The 2.NDB table must have a primary key, so the primary key must be defined when the table is created, otherwise the NDB storage engine will automatically generate the implied primary key.

The user rights table of 3.Sql node is still saved by MYISAM storage engine, so MySql users created in one Sql node can only access this node. If you want to use the same user to access other Sql nodes, you need to append users to the corresponding Sql node.

IV. Management and maintenance orders

Shut down the mysql cluster: ndb_mgm-e shutdown

Restart the mysql cluster: ndb_mgmd-f / var/lib/mysql-cluster/config.ini

Restart the data node: / usr/local/mysql/bin/ndbd

Start the SQL node: / usr/local/mysql/bin/mysqld_safe-- user=mysql & or service mysqld restart

View mysql status: ndb_mgm-e show

Boot sequence:

Manage Node-> data Node-> SQL Node

Closing order:

SQL Node-> data Node-> Management Node

These are the details of the MySQL-Cluster MySQL cluster, please pay attention to other related articles!

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.