How to configure master-slave replication of MySQL database?
Jul 13, 2023 pm 10:05 PMHow to configure master-slave replication of MySQL database?
Master-slave replication of MySQL database is a common data backup and high availability solution. By configuring master-slave replication, you can synchronize data from one MySQL server (master server) to another (slave server), thereby improving database availability and performance.
The following will introduce how to configure master-slave replication in a MySQL database and provide corresponding code examples.
- Make sure the MySQL server is installed and started
First, make sure the MySQL server has been installed in your system and the MySQL service has been started.
- Configure the main server
Configure the following on the main server:
2.1 Modify the configuration file of the main server
Find the main server The server's configuration file (generally located in /etc/mysql/my.cnf or /etc/my.cnf), and add the following configuration:
[mysqld] server-id=1 log_bin=mysql-bin binlog_do_db=your_database_name
Among them, server-id is used to identify the unique ID of the server, in Uniqueness needs to be maintained in master-slave replication. log_bin specifies the file name of the binary log, and binlog_do_db specifies the name of the database that needs to be synchronized (multiple can be set, separated by commas).
2.2 Restart the main server
After saving and closing the configuration file, restart the main server to make the configuration take effect.
$ sudo service mysql restart
- Configure the slave server
Configure the following on the slave server:
3.1 Modify the configuration file of the slave server
Found From the server's configuration file (generally located at /etc/mysql/my.cnf or /etc/my.cnf), and add the following configuration:
[mysqld] server-id=2 relay-log=mysql-relay-bin log_slave_updates=1 read_only=1
where server-id is also used to identify the unique ID of the server . relay-log specifies the relay log file name of the slave server. Log_slave_updates is set to 1 to indicate that the slave server also records binary logs. Read_only is set to 1 to indicate that the slave server is read-only.
3.2 Restart the slave server
After saving and closing the configuration file, restart the slave server to make the configuration take effect.
$ sudo service mysql restart
- Connect to the master server and create a replication user
First, connect to the master server using the MySQL client and create a user for replication:
$ mysql -u root -p mysql> CREATE USER 'replication'@'%' IDENTIFIED BY 'your_password'; mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replication'@'%'; mysql> FLUSH PRIVILEGES; mysql> SHOW MASTER STATUS;
Among them, 'replication' is the user name of the replication user, and 'your_password' is the password of the replication user. Please modify it according to the actual situation.
Finally, execute the SHOW MASTER STATUS command and record the values ??of File and Position, which will be used on the slave server later.
- Connect to the slave server and configure master-slave replication
Use the MySQL client to connect to the slave server and configure master-slave replication:
$ mysql -u root -p mysql> CHANGE MASTER TO MASTER_HOST='master_host', MASTER_USER='replication', MASTER_PASSWORD='your_password', MASTER_LOG_FILE='master_log_file', MASTER_LOG_POS=master_log_pos; mysql> START SLAVE; mysql> SHOW SLAVE STATUS G;
where , 'master_host' is the IP address or host name of the master server, 'replication' is the user name of the replication user, 'your_password' is the password of the replication user, 'master_log_file' and 'master_log_pos' are the File and The value of Position.
Finally, execute the SHOW SLAVE STATUS G command and check whether the values ??of Slave_IO_Running and Slave_SQL_Running are 'Yes' to ensure that master-slave replication has been successfully configured.
After the configuration is completed, the data on the master server will be synchronized to the slave server, realizing master-slave replication.
Summary
This article introduces how to configure master-slave replication in a MySQL database and provides corresponding code examples. Through master-slave replication, data backup and high availability can be achieved, and the availability and performance of the database can be improved. The configuration process is relatively simple, but in actual applications, you still need to pay attention to issues such as configuration consistency and fault handling to ensure the stability and reliability of master-slave replication.
The above is the detailed content of How to configure master-slave replication of MySQL database?. For more information, please follow other related articles on the PHP Chinese website!

Hot AI Tools

Undress AI Tool
Undress images for free

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

How to configure a workgroup in Win11 A workgroup is a way to connect multiple computers in a local area network, which allows files, printers, and other resources to be shared between computers. In Win11 system, configuring a workgroup is very simple, just follow the steps below. Step 1: Open the "Settings" application. First, click the "Start" button of the Win11 system, and then select the "Settings" application in the pop-up menu. You can also use the shortcut "Win+I" to open "Settings". Step 2: Select "System" In the Settings app, you will see multiple options. Please click the "System" option to enter the system settings page. Step 3: Select "About" In the "System" settings page, you will see multiple sub-options. Please click

Title: The working principle and configuration method of GDM in Linux systems In Linux operating systems, GDM (GNOMEDisplayManager) is a common display manager used to control graphical user interface (GUI) login and user session management. This article will introduce the working principle and configuration method of GDM, as well as provide specific code examples. 1. Working principle of GDM GDM is the display manager in the GNOME desktop environment. It is responsible for starting the X server and providing the login interface. The user enters

Understanding Linux Bashrc: Function, Configuration and Usage In Linux systems, Bashrc (BourneAgainShellruncommands) is a very important configuration file, which contains various commands and settings that are automatically run when the system starts. The Bashrc file is usually located in the user's home directory and is a hidden file. Its function is to customize the Bashshell environment for the user. 1. Bashrc function setting environment

Title: How to configure and install FTPS in Linux system, specific code examples are required. In Linux system, FTPS is a secure file transfer protocol. Compared with FTP, FTPS encrypts the transmitted data through TLS/SSL protocol, which improves Security of data transmission. In this article, we will introduce how to configure and install FTPS in a Linux system and provide specific code examples. Step 1: Install vsftpd Open the terminal and enter the following command to install vsftpd: sudo

MyBatisGenerator is a code generation tool officially provided by MyBatis, which can help developers quickly generate JavaBeans, Mapper interfaces and XML mapping files that conform to the database table structure. In the process of using MyBatisGenerator for code generation, the setting of configuration parameters is crucial. This article will start from the perspective of configuration parameters and deeply explore the functions of MyBatisGenerator.

Title: Computer configuration recommendations for building a high-performance Python programming workstation. With the widespread application of the Python language in data analysis, artificial intelligence and other fields, more and more developers and researchers have an increasing demand for building high-performance Python programming workstations. When choosing a computer configuration, in addition to performance considerations, it should also be optimized according to the characteristics of Python programming to improve programming efficiency and running speed. This article will introduce how to build a high-performance Python programming workstation and provide specific

When we use win11 system, we sometimes need to check the configuration of our computer, but many users are also asking where to check the configuration of win11 computer? In fact, the method is very simple. Users can directly open the system information under settings, and then view the computer configuration information. Let this site carefully introduce to users how to find win11 computer configuration information. How to find win11 computer configuration information. Method 1: 1. Click Start and open Computer Settings. 3. You can view computer configuration information on this page. 2. In the command prompt window, enter systeminfo and press Enter to view the computer configuration.

PyCharm is a powerful Python integrated development environment that allows developers to write, debug and manage Python code more efficiently. In the daily development process, we often encounter situations where environment variables need to be configured so that the program can correctly access the required resources. This article will introduce in detail how to configure environment variables in PyCharm and provide specific code examples. 1. Configure PyCharm’s environment variables. Configuring environment variables in PyCharm is very simple. The following are the specific steps:
