Home > Database > Mysql Tutorial > body text

MySQL implements master-slave replication function

步履不停
Release: 2019-06-19 14:45:11
Original
2120 people have browsed it

MySQL implements master-slave replication function

Environment Description

centos7.3, MySQL5.7

Foreword

MySQL installation reference previous article https:// www.jianshu.com/p/452aa99c7476 has an explanation.

Basic Concepts

MySQL’s built-in replication capabilities are the foundation for building large-scale, high-performance applications. Distribute hundreds of millions of MySQL data to multiple systems. This distribution mechanism is achieved by copying the data of a certain MySQL host to other hosts (Slave) and executing it again. During replication one server acts as the server and one or more other servers act as slaves. The master writes updates to the binary log and maintains an index of the files to track log rotation. These logs record updates sent to slave servers. When a slave connects to the master, it notifies the master of the location of the last successful update the slave read in the log, the slave receives any updates that have occurred since then, then blocks, etc. The master notifies new updates. . Please note that when you replicate, all updates to the replicating tables must be made on the master server. Otherwise, you must be careful to avoid conflicts between updates made by users to tables on the master server and updates to tables on the server

Master-slave replication implementation

一, Modify the master server

If you installed MySQL according to the article I wrote, then the my.cnf configuration file is in the /etc/ directory. If it is not there, you can search it through the whereis command. Add the following configuration under the my.cnf configuration file

server_id=1 #指定MySQL的id
log-bin=mysql-bin #开启二进制日志文件
Copy after login

2. Create a replication account

Execute this command in the main server (all are allowed here Address access, it is recommended to set it to the address of the slave server)

GRANT REPLICATION SLAVE ON *.* to 'replication'@'%' identified by 'Abc123...';
Copy after login

   Three. Modify the slave server

server_id=2
log-bin=mysql-bin
binlog_do_db=db_test#表示要同步的数据库
Copy after login

Instructions: binlog-ignore-db=test means unsynchronized test database binlog_do_db=db_test #Indicates the database to be synchronized

4. Restart MySQL

Restart both the master server and the slave server

service mysqld restart
Copy after login

5. Check the status of the master server

show master status
Copy after login

Results As follows:

Some information needs to be recorded here, which needs to be used when configuring the slave server# ​ ​ 6. Configure the slave server (connect to the Master server)

change master to master_host='192.168.74.129',master_user='replication',master_password='Abc123...',master_log_file='mysql-bin.000001',master_log_pos=2041;
Copy after login

master_host is the ip of the master server master_port=3306 (There is no configuration here, the default is 3306) master_user: Master server authorized user, that is, the user created before Master master_password: Master server authorized user corresponding password master_log_file: Master binlog file name master_log_pos: Postion value in Master binlog file about position value , here is a note: If the master server already has a lot of data, you first need to back up the data of the master server to the slave server, and then use the show master status command to record the location where synchronization needs to be started. #       7. Start replication and execute

start slave
Copy after login

on the slave server. The stop command is: stop slave; The two circled results are yes, indicating that the master-slave replication configuration is successful. If the configuration fails, you can check the failure information in the MySQL log file, or you can check a brief error message through the above command:

下载 (2).jpg

This is the master-slave server I just configured The main reason for the error is that the UUID of the MySQL server conflicts. The reason is that my slave server is copied from the master server. Just go to /var/lib/mysql/auto.cnf and modify it. #Verify the current status of the master-slave results:

下载 (3).jpg

## Create a new table below to see if it can be synchronized:

#View the slave server

下载 (5).jpg

You can see that it has been synchronized and synchronized in the past下载 (6).jpg

For more MySQL related technical articles, please visit the MySQL Tutorial column to learn!

The above is the detailed content of MySQL implements master-slave replication function. For more information, please follow other related articles on the PHP Chinese website!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template