Mysql database master-slave separation example code

introduce

Setting up read-write separation for the MySQL database allows write and read operations on the database to be performed on different servers, thereby increasing concurrency and response speed.

Nowadays, most larger websites adopt database master-slave separation and read-write separation, which can not only play a backup role but also reduce the pressure of database reading and writing. I have always heard of this, but I have never practiced it myself. Today I have time to practice it and record the process.

Experimental environment

I prepared two servers, one is the local computer and the other is a remote vps, and the databases are installed on both machines.
I will not introduce the installation of MySQL. What you need to pay attention to here is: the versions of MySQL installed should be consistent. If they are inconsistent, there may be problems when reading from a lower version to a higher version. It is best to keep them consistent.

Master

45.78.57.4 centos 7 Linux system, mysql version 5.1.73

Slave

Local machine 127.0.0.1 macOs system, mysql version 5.1.73

Configuration

Create User

Create a user in the master database to read the execution log of the master database from the slave database.

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

Modify my.cnf

The Linux system is in /etc/my.cnf, the Mac system is in the directory where MySQL is installed, and the Windows system is the same.

Add the following code to the my.cnf file

server-id = 1 //Database ID number log-bin=master-bin //Enable binary log log-bin-index=master-bin.index //Binary log name

Be careful not to put it at the end of the file, but at the front, after [mysqld]. Here is 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 master 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 two contents of File and Position, which will be used when configuring the slave library.

Configuration from the library

Find the my.cnf file on the local computer (slave library) and add the following content. This configuration has the same meaning as the configuration of the master library.

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

Make sure it is in the same location as the main database. I couldn’t associate it because I placed it at the end.

Associating master and slave libraries

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 server IP
master_port=3306,
master_user='test',
master_password='test', 
master_log_file='master-bin.000001', #Master log file name master_log_pos=672675; #Master log synchronization start position

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

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

//You need to execute start slave in the mysql command line; 
show slave status\G; //View slave connection status

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

Notice!

These two states must be Yes to be considered successful. If not, check which step in the above step has a configuration error.

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

test

Now you add a piece of data on the master database to see if there is an identical piece of data on the slave database. If so, the configuration is normal and the function is working properly.

The principle of master-slave separation is mainly: turn on the execution log function of the master database, then read the log information of the master database from the slave database, and then execute the SQL statements executed by the master database on the slave database to achieve master-slave separation, keep the master-slave data consistent, and back up the data.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.