Detailed explanation of the master-slave separation example code of Mysql database

This article mainly introduces the example code of Mysql. MySQL database sets up read-write separation, which can make the write operations and read operations of the database in different servers. execution to improve concurrency and corresponding speed.

Introduction

Setting up read-write separation for MySQL database allows write operations and read operations on the database to be executed on different servers, improving concurrency and corresponding speed.

Nowadays, most websites are generally larger and adopt database master-slave separation and read-write separation, which not only acts as a backup but also reduces the pressure of reading and writing the database. I have always heard of this, but I have never heard of it. I have never practiced it myself. I have time to practice it today and record the process.

Experimental environment

I prepared two servers, one is a local computer and the other is a remote vps. There are databases installed on the two machines respectively.
I won’t introduce the installation of MySQL. What needs to be noted here is: it is best to have the same version of MySQL installed. If it is inconsistent, there may be problems when the lower version reads from the higher version. It is best to keep it consistent.

Master library master

45.78.57.4 centos 7 Linux system, mysql version 5.1.73

From the library slave

This machine is 127.0.0.1 macOs system, mysql version 5.1.73

Configuration

Create user

Create a user in the main library to read the execution log of the main library from the library.

Needs to be executed in the mysql command line, you need to log in to the command line first

The code is as follows:

GRANT REPLICATION SLAVE ON *.* TO 'test'@'45.78.57.4' IDENTIFIED BY 'test';

Modify my.cnf

Linux system is in /etc/my.cnf, mac system is in the directory where MySQL is installed, and the same is true for windows.

Add some code to the my.cnf file

server-id = 1 //数据库ID号
log-bin=master-bin //启用二进制日志
log-bin-index=master-bin.index //二进制日志名称

Be careful not to put it at the end of the file, but to put it in front, that is, after [mysqld], put my my.cnf content

[mysqld]
server-id=1
log-bin=master-bin
log-bin-index=master-bin.index

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
max_allowed_packet=100M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

View status

After logging in to the mysql command line, enter show master status. If the following information appears, it means that the main database configuration is complete

mysql> show master status;
+-------------------+----------+--------------+------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 |  672675 |       |         |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Record the contents of File and Position, which will be used when configuring from the library.

Slave library configuration

Find the my.cnf file on the local computer (slave library), and then add the following content. The meaning of this configuration and the configuration of the main library is Same

server-id=2
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index

Be sure to make sure the location is the same as the main library. I couldn't connect it because the location was placed at the end.

Associating the master-slave database

The last step is very important. Log in to the MySQL command line of the slave database and execute the following code, which is mainly to associate some information of the master database.

change master to master_host='45.78.57.4',  #Master 服务器Ip
master_port=3306,
master_user='test',
master_password='test', 
master_log_file='master-bin.000001', #Master日志文件名
master_log_pos=672675; #Master日志同步开始位置

Pay attention to whether the execution is successful. If the execution fails, check the code carefully to see where there are mistakes.

If the execution is normal, start the slave slave and check the connection status.

//需要再mysql命令行执行 
start slave; 
show slave status\G; //查看slave连接状态

Status information

        Slave_IO_State: Waiting for master to send event
         Master_Host: 45.78.57.4
         Master_User: test
         Master_Port: 3306
        Connect_Retry: 60
       Master_Log_File: master-bin.000001
     Read_Master_Log_Pos: 672913
        Relay_Log_File: slave-relay-bin.000044
        Relay_Log_Pos: 504
    Relay_Master_Log_File: master-bin.000001
       Slave_IO_Running: Yes
      Slave_SQL_Running: Yes

Attention!

These two statuses must be Yes to be considered successful. If not, check the configuration error in the above step.

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Test

Now you add a piece of data on the main database to see if there is the same data on the slave database. If so, the configuration is normal and the function is used. normal.

The main principle of master-slave separation is to enable the execution log function of the main library, then read the log information of the main library from the slave library, and then execute the SQL statements executed by the main library on the slave library. Until the master-slave is separated, the master-slave data remains intact and has the function of backing up the data.

The above is the detailed content of Detailed explanation of the master-slave separation example code of Mysql database. For more information, please follow other related articles on the PHP Chinese website!