Construction of mysql cluster

MySQL is one of the most popular relational database management systems, but a single MySQL server has performance bottlenecks and reliability issues. In order to meet high load and high reliability requirements, MySQL cluster can be used to solve these problems.

MySQL Cluster is a way for multiple MySQL Server instances to work and run together on physical or virtual devices, providing high performance and reliability by sharing the load and improving availability. This article will introduce how to build a MySQL cluster.

1. MySQL cluster components

MySQL cluster is mainly composed of the following components:

1. MySQL Server: The MySQL server in the cluster can be of different versions and configurations. Each MySQL Server instance runs on a different physical or virtual device.
2. MySQL Cluster Manager (MCM): A toolset for managing MySQL Server instances in MySQL clusters. With the help of MCM, MySQL clusters can be easily configured, deployed, monitored and managed.
3. Data Nodes: Each data node is a MySQL Server instance used to store data and execute queries. MySQL Cluster uses the NDB storage engine to process memory and disk data.
4. Management Nodes: Management nodes are nodes that perform management tasks and control the MySQL Cluster configuration. Each MySQL Cluster requires at least one management node.
5. SQL Nodes: SQL nodes are clients used to interact with MySQL Cluster. MySQL Cluster does not provide ready-made SQL nodes. You need to use a MySQL Server instance as the SQL node.

2. Construction of MySQL Cluster

Before you start, make sure that MySQL Server has been installed and configured, and multiple computer nodes are ready. For simplicity, in the following example, a MySQL cluster will be configured with 3 MySQL Server nodes and 2 management nodes. Each node has MySQL Server 8.0.25 installed and uses CentOS 8 operating system.

1. Installing MySQL Cluster Manager

Since MySQL Cluster Manager (MCM) is a key part of configuring and managing MySQL cluster, you need to install MCM on each node first. MCM can be downloaded from the MySQL official website.

After downloading, execute the following command to install:

sudo rpm -ivh mysql-cluster-manager-1.4.10-linux-glibc2.12-x86_64.rpm

2. Configuring MySQL Cluster Manager

After installing MCM, initial configuration is required. Do it on one of the nodes, this node will become the management node.

Execute the following command to initialize MCM:

sudo /opt/mysql/mcm/bin/setup --config-file=/etc/mysql/mcm.ini --yes

This command will create the /etc/mysql/mcm.ini file and initialize MCM to stand-alone mode according to default settings. If you need to use more than one node in your cluster, you can add more nodes in this file.

3. Configuring data nodes

First, you need to install mysql-community-server on all nodes, and then stop the mysqld service.

Next, create a new MySQL Server example so that it can be used as a data node. This new instance can be created from an existing mysqld installation. In this example, you can use 8.0.25 as version and use the following command to create a new MySQL Server instance:

sudo /usr/sbin/mysqld --defaults-file=/etc/my_node1.cnf -- initialize-insecure --ignore-builtin-innodb

This command will create a new MySQL Server instance while creating all necessary files and directories in the $datadir/mysql-cluster folder.

This command needs to be executed on all data nodes.

4. Configuring the management node

Similar to the data node, a new MySQL Server instance needs to be configured for the management node in order to use it as a management node. Execute the following command to create a new MySQL Server instance:

sudo /usr/sbin/mysqld --defaults-file=/etc/my_mgmt1.cnf --initialize-insecure --ignore-builtin-innodb

This command will create a new MySQL Server instance, creating all necessary files and directories in the $datadir/mysql-cluster folder.

This command needs to be executed on all management nodes.

5. Running MySQL Cluster

Now that all preparations have been completed, you can start MySQL Server instances one by one and connect to the cluster.

First start the management node, and then execute the following command to add the node to MySQL Cluster:

sudo ndb_mgmd -f /var/lib/mysql-cluster/config.ini --initial

This command starts the ndb_mgmd process, which will read the configuration file and join itself as a management node to MySQL Cluster. Finally, a message will be printed confirming that the management node has started successfully.

Next, start all data nodes. Execute the following command on each node:

sudo ndbd

This command starts an ndbd process, connects to the started management node, and then joins MySQL Cluster. When using multiple data nodes, this command needs to be executed on each node.

Finally, test whether MySQL Cluster is running normally by starting a MySQL Server instance as a SQL node. In this example, you can start a MySQL Server instance on one node and connect to MySQL Cluster:

sudo mysqld_safe --defaults-file=/etc/my_sql1.cnf &

mysql - u root

This command will start the MySQL Server instance and open the MySQL client. You can then perform various SQL query operations to test whether the cluster is working properly.

3. Summary

This is a basic tutorial on building a MySQL cluster. MySQL Cluster can improve performance and reliability and is often used in applications that need to handle high loads, such as web applications and e-commerce websites. Using the MCM toolset, you can easily configure, deploy, monitor and manage MySQL clusters. For many companies, setting up a MySQL cluster is a must-have.

The above is the detailed content of Construction of mysql cluster. For more information, please follow other related articles on the PHP Chinese website!