Step-by-Step Guide to Setting Up MySQL InnoDB Cluster

You set up a MySQL InnoDB Cluster by doing simple steps. This guide shows you how to build a strong database system. It keeps your data safe and easy to get, even if a server stops working. High availability and fault tolerance help your apps stay online. You learn ways to make your database tough and trustworthy by using tested methods.

Key Takeaways

• Make sure your servers meet the needed requirements. Use three servers with the same hardware. This helps set up MySQL InnoDB Cluster easily.

• Put the newest MySQL Server and MySQL Shell on each node. Using the same software versions stops problems with compatibility.

• Keep your MySQL safe by using strong passwords. Turn off features you do not need. This keeps your data safe from people who should not see it.

• Use MySQL Router to handle app connections. It helps balance the load. It also keeps things working if one node stops.

• Check your cluster’s health often and make backups. This keeps your data safe. It helps you fix problems fast if they happen.

Prerequisites and Preparation

You must check your setup before you build a MySQL InnoDB Cluster. Getting ready helps you stop problems and makes things easier.

System Requirements

You need three servers for a basic cluster. These can be real or virtual. Each server needs:

• 2 CPU cores or more

• At least 4 GB RAM

• 20 GB of free space

• 64-bit Linux like Ubuntu 20.04 or CentOS 8

Tip: Use the same hardware for every node. This helps keep your cluster running smoothly.

Network Setup

A good network is important for high availability. Put your servers on the same network or cloud subnet. Give each server a static IP address. Make sure all servers talk to each other on these ports:

Turn off firewalls or open these ports. This lets your servers connect. Use hostnames or IPs that do not change.

Software Checklist

Put the newest versions of these tools on every server:

• MySQL Server (8.0 or higher)

• MySQL Shell

• MySQL Router

Get them from the official MySQL website. Make sure your system is updated. You need sudo or root access to set up and change software.

Note: MySQL InnoDB Cluster works best if all nodes use the same MySQL version.

After you finish these steps, you can start installing. Good preparation helps your MySQL InnoDB Cluster stay safe and strong.

Install MySQL and MySQL Shell

Setting up your servers with the right software is the next step. You need to install MySQL Server and MySQL Shell on each node. Follow these steps to make sure your MySQL InnoDB Cluster works well.

MySQL Server Installation

Start by installing MySQL Server on all three nodes. Use your system’s package manager for a smooth process. For Ubuntu, run:

1
2

sudo apt update
sudo apt install mysql-server

For CentOS, use:

1

sudo yum install @mysql

After installation, check the MySQL service:

1

sudo systemctl status mysqld

You should see that MySQL is active and running. If not, start it with:

1

sudo systemctl start mysqld

Tip: Always install the same MySQL version on every node. This helps avoid compatibility issues.

MySQL Shell Setup

MySQL Shell gives you powerful tools to manage your cluster. Install it on each server. On Ubuntu, type:

1

sudo apt install mysql-shell

On CentOS, use:

1

sudo yum install mysql-shell

Check the installation by running:

1

mysqlsh --version

You should see the version number. If you do, you are ready for the next step.

Initial Configuration

Secure your MySQL installation. Run the security script:

1

sudo mysql_secure_installation

Set a strong root password. Remove anonymous users. Disallow remote root login. Remove the test database. Answer each prompt to make your server safer.

Next, enable binary logging and set the server ID. Edit the MySQL configuration file (/etc/mysql/my.cnf or /etc/my.cnf). Add these lines under the [mysqld] section:

1
2

server-id=1
log_bin=mysql-bin

Change the server-id for each node (use 1, 2, and 3). Save the file and restart MySQL:

1

sudo systemctl restart mysqld

You have now installed and configured the software needed for your MySQL InnoDB Cluster.

Configure MySQL InnoDB Cluster

Prepare Instances

You must prepare each server before you add it to your MySQL InnoDB Cluster. This step helps you avoid common problems and ensures smooth operation.

1. Map all hostnames and IP addresses in the /etc/hosts file on every server. This step ensures that each node can find the others by name.

2. Stop and disable AppArmor. AppArmor can block MySQL processes and cause issues with replication.

3. Download and install the latest MySQL APT configuration repository. This keeps your MySQL packages up to date.

4. Install the GPG key to verify package downloads.

5. Update your package list so your system sees the newest MySQL versions.

6. Install MySQL Server and MySQL Shell if you have not done so already.

7. Set a strong root password during installation. This protects your database from unauthorized access.

8. Choose the legacy authentication method for compatibility with older clients.

9. Use MySQL Shell to configure the server for Group Replication. This step is vital for cluster communication.

10. Create a new admin account for the cluster. Give it only the permissions it needs.

Tip: Use the same steps on every node. Consistency helps prevent errors later.

Create Sandbox Instances

You can use sandbox instances for testing your cluster setup before you go live. MySQL Shell makes this process easy.

To create sandbox instances, run the following command on your management server:

1

mysqlsh --sandbox

This command sets up isolated MySQL instances on your local machine. You can use these for practice or to test your configuration before you use real servers.

Note: Sandboxes are for testing only. Do not use them in production.

Initialize Cluster with MySQL Shell

Now you can start building your MySQL InnoDB Cluster. Open MySQL Shell and connect to the first node as the root user:

1

mysqlsh --uri root@<first-node-ip>

Switch to JavaScript mode by typing:

1

\js

Create the cluster with:

1

var cluster = dba.createCluster('myCluster')

This command sets up the cluster on your first node. MySQL Shell checks your configuration and enables Group Replication.

Tip: Use a clear name for your cluster. This helps you manage it later.

Add Nodes

After you create the cluster, you need to add the other two nodes. Stay in MySQL Shell and run:

1
2

cluster.addInstance('root@<second-node-ip>')
cluster.addInstance('root@<third-node-ip>')

Replace <second-node-ip> and <third-node-ip> with the actual IP addresses of your other servers.

MySQL Shell will check each node and join it to the cluster. You now have a three-node setup, which gives you high availability and fault tolerance.

Callout: A three-node MySQL InnoDB Cluster can keep running even if one node fails. This setup protects your data and keeps your applications online.

Verify Cluster Status

You should always check the health of your cluster after setup. In MySQL Shell, run:

1

cluster.status()

You will see a summary of your cluster. Look for all nodes listed as ONLINE. If you see errors, review your configuration and repeat the steps as needed.

A healthy MySQL InnoDB Cluster gives you a strong foundation for your database. You can now move on to deploying MySQL Router and connecting your applications.

Deploy and Bootstrap MySQL Router

Setting up MySQL Router helps your apps talk to your MySQL InnoDB Cluster. MySQL Router sends traffic to the right place. It keeps your database working, even if one node stops. You can follow these steps to put in and set up MySQL Router for your cluster.

Install MySQL Router

Put MySQL Router on a server that can reach all cluster nodes. Most people install it on the same server as their app. To install MySQL Router on Linux, do these steps:

1. Open the tar bundle you downloaded:

1

tar xvf mysql-enterprise-<REL_VERSION>_el<VERSION>_<ARCH>_bundle.tar

2. Add the MySQL GPG key:

1

sudo rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2023

3. Install Yum tools:

1

sudo yum install yum-utils

4. Add developer bundles as a local yum repo:

1

sudo yum-config-manager --add file:///path/to/rpms

5. Turn off the MySQL module for Oracle/Red Hat Linux 8:

1

sudo yum module disable mysql

6. Install MySQL Router:

1

sudo yum install mysql-router-commercial

Tip: Always use the newest MySQL Router. This helps keep your cluster safe and working well.

Bootstrap Router for Cluster

After you install MySQL Router, you need to set it up for your MySQL InnoDB Cluster. Bootstrapping gets the router ready to send traffic to the right nodes. Run this command:

1

mysqlrouter --bootstrap root@<primary-node-ip> --user=mysqlrouter

Change <primary-node-ip> to your cluster’s main node IP. This command makes config files and system accounts for the router.

MySQL Router has features that help your cluster work better:

Note: These features help your apps stay online and keep your data safe.

Connect Applications

Now you can make your apps use MySQL Router instead of talking to cluster nodes directly. Use the ports MySQL Router gives for reading and writing. This setup lets your apps use high availability and load balancing without big changes.

Look at your app’s database settings. Change the host and port to match MySQL Router. Test the connection to see if it works.

🛡️ MySQL Router keeps your data safe and your apps running, even if a cluster node goes down.

Scale, Monitor, and Maintain

Add or Remove Nodes

You can make your cluster bigger or smaller by adding or removing nodes. This helps you handle more users or save resources. Use MySQL Shell to do this. The table below shows the main commands you need:

Always check the cluster status after you make changes. This makes sure your cluster is working right.

Monitor Cluster Health

Watching your cluster helps keep it working well. You can use different tools to check health and speed:

• New Relic shows how your cluster is doing and finds problems.

• Sematext gives you charts and tracks what happens in your cluster.

• Percona Monitoring and Management Tool lets you see what is happening right now and checks your queries.

• Dynatrace helps you see everything and gives you details.

Pick the tool that works best for you. Checking your cluster often helps you find problems early.

Backup and Recovery

Keeping your data safe is very important. You should back up your database often and know how to get it back. The table below shows ways to back up and what is best:

Make a backup plan that fits your system and your business.

Security Best Practices

Keeping your cluster safe protects your data and users. Follow these tips:

• Make strong rules for user accounts.

• Use roles to control what people can do.

• Make sure passwords are strong.

• Turn off things you do not use.

• Use encryption to keep data safe when stored or sent.

• Always watch and check what is happening.

Teach users how to stay safe. This helps stop mistakes and makes passwords better. It also helps people pay attention to security. You should also make a plan for what to do if something bad happens. Write down steps for finding, stopping, fixing, and recovering from problems. This helps you act fast if there is a security issue.

A well-cared-for MySQL InnoDB Cluster helps your business. It gives you high availability, strong security, and keeps your data safe.

You now know how to set up MySQL InnoDB Cluster. This cluster keeps your data safe and helps your apps stay online. If something fails, your apps can recover quickly. You can make your setup better by doing these things:

1. Add more nodes to make your cluster stronger.

2. Change server settings to help it run faster.

3. Look at advanced features for bigger companies.

Keep trying new tools and features to make your cluster work even better.

FAQ

How do you recover a failed node in MySQL InnoDB Cluster?

First, restart the failed node. Then, use MySQL Shell to rejoin it. Run cluster.rejoinInstance('root@<node-ip>') to add it back. Always check the cluster status after you finish.

Can you upgrade MySQL InnoDB Cluster without downtime?

Yes, you can upgrade nodes one by one. Rolling upgrades keep your cluster working. Always test upgrades in a sandbox before you update real servers.

What is the minimum number of nodes for high availability?

You need three nodes for high availability. This setup lets your cluster keep working if one node fails.

How do you monitor cluster health?

Use tools like MySQL Shell, Percona Monitoring, or New Relic. Run cluster.status() in MySQL Shell to check node status and replication health.

Is MySQL Router required for application connections?

You should use MySQL Router for connections. It manages traffic, balances loads, and handles failover. Direct connections can cause downtime if a node fails.

Recommended reading

• SQL Optimization Course
• SQL optimization knowledge
• Database industry knowledge

What is SQLFlash?

SQLFlash is your AI-powered SQL Optimization Partner.

Based on AI models, we accurately identify SQL performance bottlenecks and optimize query performance, freeing you from the cumbersome SQL tuning process so you can fully focus on developing and implementing business logic.

How to use SQLFlash in a database?

• SQLFlash + MySQL

• SQLFlash + PostgreSQL