Exploring the cluster deployment of MySQL master-slave replication: a step-by-step guide from installation to configuration

Exploring the cluster deployment of MySQL master-slave replication: a step-by-step guide from installation to configuration

High availability and flexibility of data in large-scale Internet applications is very important. MySQL master-slave replication is a commonly used solution, which can achieve data backup, load balancing and disaster recovery. This article will introduce in detail how to set up a cluster deployment of MySQL master-slave replication, from installation to configuration, explaining it step by step.

Step One: Install MySQL
Before you start setting up MySQL master-slave replication, you first need to install the MySQL database on each node. You can download the appropriate version from the MySQL official website and then follow the installation wizard to install it. After the installation is complete, ensure that the MySQL service has been started successfully and you can log in to the MySQL console through the command line tool.

Step 2: Configure the master node
Configure on the master node so that other slave nodes can connect to the master node and copy data. Open the MySQL configuration file on the master node, usually located at /etc/mysql/my.cnf or /etc/my.cnf. Find and edit the following configuration items:

server-id=1
log-bin=mysql-bin
binlog-do-db=your_database

Among them, server-id is the unique identifier of the node and can be any integer. The log-bin configuration item enables binary logging, which is used by the master node to record all database changes. The binlog-do-db configuration item sets the name of the database to be copied, which can be modified according to actual needs.

After saving and closing the configuration file, restart the MySQL service to make the configuration take effect.

Step 3: Configure the slave node
Configure the slave node so that it can connect to the master node and realize data replication. Similarly, open the MySQL configuration file on the slave node, find and edit the following configuration items:

server-id=2
relay-log=mysql-relay-bin
relay-log-index=mysql-relay-bin.index
replicate-do-db=your_database

Among them, the value of server-id can be any integer and must be different from the server-id of the master node. . The relay-log configuration item specifies the file name of the relay log, which is used by the slave node to record data changes received from the master node. The relay-log-index configuration item specifies the relay log index file name. The replicate-do-db configuration item sets the name of the database to be replicated, consistent with the primary node.

After saving and closing the configuration file, restart the MySQL service.

Step 4: Connect to the primary node and create a replication account
On the primary node, use the MySQL command line tool to connect to the MySQL console of the primary node. Create an account for copying and grant appropriate permissions.

GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'从节点IP' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Among them, replication_user is the account name used for replication, the slave node IP is the IP address of the slave node, and password is the password of the account. Please replace it according to the actual situation.

Step 5: Start master-slave replication
On the slave node, use the MySQL command line tool to connect to the MySQL console of the slave node. Execute the following command to start master-slave replication:

CHANGE MASTER TO
   MASTER_HOST='主节点IP',
   MASTER_USER='replication_user',
   MASTER_PASSWORD='password',
   MASTER_LOG_FILE='mysql-bin.000001',
   MASTER_LOG_POS=0;
START SLAVE;

Among them, the master node IP is the IP address of the master node, replication_user is the name of the previously created replication account, and password is the password of the account. MASTER_LOG_FILE and MASTER_LOG_POS are the information about the binary log file and location on the master node respectively.

After execution, use the following command to verify whether the master-slave replication is started successfully:

SHOW SLAVE STATUSG

In the displayed information, the values ​​of the two fields Slave_IO_Running and Slave_SQL_Running should be "YES", which means Master-slave replication has been started successfully.

Step 6: Test master-slave replication
On the master node, create a new database and insert some data. Then query the database on the slave node to confirm that the data has been synchronized.

At this point, the cluster deployment of MySQL master-slave replication has been completed. You can configure more slave nodes and increase the number of master nodes and slave nodes according to actual needs to achieve higher availability and flexibility.

Summary
MySQL master-slave replication is a reliable solution that can achieve data backup, load balancing and disaster recovery. This article introduces a step-by-step guide from installation to configuration, hoping to help you successfully set up a cluster deployment of MySQL master-slave replication.

However, it should be noted that master-slave replication cannot replace high-availability database solutions, such as MySQL Cluster or databases based on distributed systems. For large-scale Internet applications, issues such as data fragmentation and horizontal expansion also need to be considered. Therefore, in practical applications, the appropriate database solution should be selected according to the specific situation.

The above is the detailed content of Exploring the cluster deployment of MySQL master-slave replication: a step-by-step guide from installation to configuration. For more information, please follow other related articles on the PHP Chinese website!