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 #开启二进制日志文件

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...';

   Three. Modify the slave server

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

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

5. Check the status of the master server

show master status

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;

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

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:

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:

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

#View the slave server

You can see that it has been synchronized and synchronized in the past

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!