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!