Shulou(Shulou.com)05/31 Report--

This article introduces how to use two servers to achieve master-slave synchronization and separation of the database, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Background: in the era of continuous development of the network, people's use of data is also increasing, and the requests for users are increasing, and the read and write operations of the database are particularly large. it will lead to the server can not stand so many user requests and data operations, resulting in server load, the corresponding users' waiting time will be very long. The experience for users is particularly poor, and master-slave synchronization is a good solution to this concurrency problem.

Master-slave synchronization: to put it simply, two servers are used to handle the user's read and write operations respectively, thus achieving the separation of read and write. Because the data on the master server is synchronized from the slave server, the master-slave synchronization greatly ensures the security of the data.

Basic principle: master-slave synchronization, divided into two servers

As shown in the figure: Master is the master server and Slave is the slave server

1. The operation of the master server (Master) to the data, before the transaction update is completed, the mysql will write the log to the Binary log log in binary form in serial form (that is, binary log events). After the transaction is written, Master notifies the storage engine to commit the transaction.

two。 After the transaction is committed, the Slave server copies the Binary log log of the Master to its own Relay log (that is, Slave opens the Slave O thread to read the Binary log in the Master and then writes it to the Relay log in the Slave)

The 3.SOL thread thread reads the log in Relay log and redoes the data to synchronize the data changes in Master to Slave.

So: two servers, Master provides users with write (add, delete, this) operation, Slave for public users to read (query) operation, thus greatly reducing the load of the server and solving the problem of high concurrency.

So let's take a look at how to use two servers to build master-slave synchronization for mysql (ps: today I'll only show you how to build a master-slave synchronization implementation on the server).

Before you start, you need two servers, install the linux system, and install mysql version 5.1 or later. (* vim editor is installed).

So when these environments are ready, let's start building them.

1. Use SSH to connect to your two servers (the two servers I use here are Master and Slave)

two。 Modify the configuration file of mysql on Master and Slave

a. Modify the configuration file of the primary server (Master)

Enter the command: vim / etc/my.cnf (default installation location for mysql)

The following prompt appears:

Select e to edit.

Press the I key

Add the following configuration under mysqld:

Server-id=1 log-bin=master-bin log-bin-index=master-bin.index

As shown in the figure (see the details in the figure), wq saves and exits, and here the configuration of the main server (Master) is fine.

Then reload the mysql configuration

Input: service mysqld restart

Next, let's see if the configuration is successful.

Log in to mysql in Master

Command: mysql-uroot-p

Enter the password and enter show master status in the mysql interface, as shown in the figure:

b. Modify the configuration of slave server (Slave)

Connect to the Slave server, and vim opens the configuration file of mysql

The principle is the same, configure under mysqld

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

As shown in the figure:

Save exit

Then restart mysql. Here is another way to restart mysql.

Enter the command: / etc/init.d/mysql stop stop

/ etc/init.d/mysql start start

Log in to mysql to verify whether the configuration is incorrect

There is nothing wrong with the configuration.

3. Start the master-slave connection

a. Add the user used to connect to the slave server in the master server

Create a user mengxin in master (ps: this is the author's name)

Create user mengxin

Then grant mengxin master-slave access

Grant replication slave on *. * to 'mengxin'@' user password required to connect from the server's ip' identified by' (set by yourself)'

Then enter: flush privileges

Refresh update permission information

b. Come to connect from the server (Slave)

Log in to the mysql of the slave server

Enter the command:

Change master to master_host=' host ip',master_port=3306,master_user='mengxin',master_password='mengxin',master_log_file='master-bin.000001',master_log_pos=0

Make a connection

Command description: master_host is the Master server ip,master_port is the port of the Master server (default is 3306), master_user is the user name just set on the Master server, master_password is the password just set, and master_log_file is the parameter to be used just mentioned above (ps: you can enter the command: show master status on the master server to view)

After the command is executed, the connection is complete

Enable master-slave synchronization: start slave

Then enter: show slave status\ G

Check the running status: (as shown in the figure, no error is reported, indicating that the master-slave synchronization is successfully configured)

* Let's verify the effect of master-slave synchronization:

Create a mengxin_shop database in Master

In the Slave server, looking at the database, you can see that the database added in master has been updated to Slave

Whoo-hoo, finally finished, encountered a variety of mistakes, or a little bit to solve

Here are some common mistakes encountered in the configuration process.

1. If the configuration is in progress, the error shown in the figure appears when you check the status of Slave step by step.

This shows that there is no plugin field in mysql.user, this is because your mysql version is 5.1. without this field, there will be problems with authorization.

Solution: keep the mysql version of the two servers above 5.5, and try to keep the same version

two。 Sometimes there is an error that the Slave connection cannot be connected to the Master. Please check that the mysql database port 3306 on the server is off the firewall.

3. Sometimes there will be the same error as slave and master id. Make sure that there is only one value of server-id in the slave configuration, and it is 2.

On how to use the two servers to achieve the master-slave synchronization and separation of the database is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.