Mysql database master-slave separation instance code, mysql instance

Mysql database master-slave separation instance code, mysql instance

Introduction

Setting read/write splitting for the MySQL database enables write and read operations on the database to be executed on different servers, improving the concurrency and speed.

Currently, most websites use master-slave database separation and read/write separation, which can backup the database and reduce the read/write pressure on the database, but I have never practiced it myself. Today I have time to practice it and record the process.

Lab Environment

I have prepared two servers. One is a local computer and the other is a remote vps. The two servers are equipped with databases.
I will not introduce the installation of MySQL. here we need to note that the versions installed in MySQL are best consistent. If they are different, there may be problems when reading the lower version to the higher version, and it is best to keep them consistent.

Master database master

45.78.57.4 centos 7 Linux system, mysql 5.1.73

Slave

The macOs System of the Local Machine 127.0.0.1 and mysql 5.1.73

Configuration

Create user

Create a user in the master database to read the execution logs of the master database.

To run the command in the mysql command line, log on to the command line first.

Copy codeThe Code is as follows:
Grant replication slave on *. * TO 'test' @ '45. 78.57.4 'identified BY 'test ';

Modify my. cnf

Linux is in the directory of/etc/my. cnf, mac is in the installed MySQL, and windows is the same.

Add the code to the my. cnf file.

Server-id = 1 // Database id log-bin = master-bin // enable binary log-bin-index = master-bin.index // binary log name

Do not put it at the end of the file. put it at the front, that is, after [mysqld], put my. cnf content here.

[mysqld]server-id=1log-bin=master-binlog-bin-index=master-bin.indexdatadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysqlsymbolic-links=0max_allowed_packet=100M[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

View status

After logging on to the mysql command line, enter show master status. If the following information appears, 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 File and Position content, which will be used during slave database configuration.

Slave Database Configuration

Find the my. cnf file on the Local Computer (from the database) and add the following content. The configuration is the same as the configuration in the master database.

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

Make sure that the location of the master database is the same as that of the master database. I cannot be associated because the location is placed at the end.

Associate a master-slave Database

The last step is very important. log on to the MySQL command line of the slave database and execute the following code, mainly related to the information of the master database.

Change master to master_host = '45. 78.57.4 ', # Master server Ipmaster_port = 3306, master_user = 'test', master_password = 'test', master_log_file = 'master-bin.000001', # master log file name master_log_pos = 672675; # Start position of Master log Synchronization

Check whether the execution is successful. If the execution fails, check the code to see where the error is written.

If the slave database runs normally, start the slave database slave and check the connection status.

// Run start slave on the mysql command line; show slave status \ G; // view the 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

Note!

The two statuses must be "Yes". If not, check that the configuration in the previous step is incorrect.

Slave_IO_Running: YesSlave_SQL_Running: Yes

Test

Now you can add a piece of data to the master database to check whether the slave database has the same data. If yes, the configuration is normal and the function is normal.

The principle of master-slave separation is: Enable the log execution function of the master database, and then read the log information of the master database from the database, then, execute the SQL statements executed in the master database from the slave database to separate the master and slave databases, keep the master and slave data, and back up the data.

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.