How to configure master-slave replication of MySQL database?
How 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
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
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)
Hot Topics
1378
52
The perfect combination of PyCharm and PyTorch: detailed installation and configuration steps
Feb 21, 2024 pm 12:00 PM
PyCharm is a powerful integrated development environment (IDE), and PyTorch is a popular open source framework in the field of deep learning. In the field of machine learning and deep learning, using PyCharm and PyTorch for development can greatly improve development efficiency and code quality. This article will introduce in detail how to install and configure PyTorch in PyCharm, and attach specific code examples to help readers better utilize the powerful functions of these two. Step 1: Install PyCharm and Python
How to set up Git configuration in PyCharm
Feb 20, 2024 am 09:47 AM
Title: How to correctly configure Git in PyCharm In modern software development, the version control system is a very important tool, and Git, as one of the popular version control systems, provides developers with powerful functions and flexible operations. As a powerful Python integrated development environment, PyCharm comes with support for Git, allowing developers to manage code versions more conveniently. This article will introduce how to correctly configure Git in PyCharm to facilitate better development during the development process.
The working principle and configuration method of GDM in Linux system
Mar 01, 2024 pm 06:36 PM
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
Understand Linux Bashrc: functions, configuration and usage
Mar 20, 2024 pm 03:30 PM
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
How to configure workgroup in win11 system
Feb 22, 2024 pm 09:50 PM
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
Simple and easy-to-understand PyCharm configuration Git tutorial
Feb 20, 2024 am 08:28 AM
PyCharm is a commonly used integrated development environment (IDE). In daily development, using Git to manage code is essential. This article will introduce how to configure Git in PyCharm and use Git for code management, with specific code examples. Step 1: Install Git First, make sure Git is installed on your computer. If it is not installed, you can go to [Git official website](https://git-scm.com/) to download and install the latest version of Git
How to configure and install FTPS in Linux system
Mar 20, 2024 pm 02:03 PM
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
MyBatis Generator configuration parameter interpretation and best practices
Feb 23, 2024 am 09:51 AM
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.
