How MYSQL master/slave server configuration works _ MySQL

How MYSQL master/slave server is configured

How MYSQL master/slave server configuration works

I. principle of master-slave configuration:

Mysql Replication is an asynchronous Replication process, from one Mysql instace (we call it the Master) to another

Mysql instance (we call it Slave ). In Master and Slave

The entire replication process is mainly completed by three threads. two threads (SQL thread and IO thread) are on the Slave side, and the other thread (IO thread) is on the Master node.

.

To implement MySQL Replication, you must first enable the Binary

Log (mysql-bin.xxxxxx) function, otherwise it cannot be implemented. Because the whole replication process is that Slave obtains the log from the Master end and then completely

The operations recorded in the sequential execution log. You can enable the Binary Log of MySQL by using

"-Log-bin" parameter option, or add the mysqld parameter group (the parameter section marked by [mysqld]) in the my. cnf configuration file.

"Log-bin" parameter.

The basic process of MySQL replication is as follows:

1. the IO thread on the Slave connects to the Master, and requests the log content after the specified location of the specified log file (or from the beginning of the log;

　

2. after the Master receives a request from the Slave IO thread

The thread reads the log information after the specified location of the specified log based on the request information, and returns the IO information to the Slave end.

Thread. Besides the information contained in the Log, the returned information also includes the name of the Binary Log file on the Master end and

Location in Log;

3. after the Slave IO thread receives the information, it writes the received log content to the Slave Relay in sequence.

The end of the Log file (mysql-relay-bin.xxxxxx) and records the file name and location of the bin-log on the Master end to the master-

In the info file, so that the next read can clearly understand the high-speed Master "I need to start from the location of a bin-log and send the log content to me"

　

4. after the Slave SQL thread detects that the Relay Log has added a new content, it will immediately parse the content in the Log file to become

Execute the executable Query statements when the end is actually executing, and execute these queries on its own. In this way, it is actually on the Master side and Slave

The data at both ends of the Query is identical.

II. Advantages of configuring mysql master/slave:

1. Solve the performance bottleneck of the web application system and database, and use the database cluster method to achieve query load. in a system, the database query operation is much more than the update operation, multiple query servers are used to distribute database queries to different query servers, thus improving query efficiency.

2. the Mysql database supports the master-slave replication function of the database. the master database is used for data insertion, deletion, and update operations, while the slave database is used for data query operations, in this way, the update and query operations can be shared to different databases, thus improving the query efficiency.

III. configuration of master/slave database servers

1. master database server configuration

(1) modify the mysql configuration file (/etc/my. cnf) and set it in the configuration file:

Server-id = 1 ### each database server must have a unique server-id. Generally, the master server is set to 1.

Log-bin = mysql-bin ### mysql performs master-slave replication through binary log files. Therefore, you must enable the mysql log function.

(This is the default configuration of/etc/my. cnf. keep it unchanged)

(2) grant replication slave on *. * TO 'replicase' @ '172. 28.3.41 'identified BY 'koncept'; ##### authorize the master database server a user who can copy data. 172.28.3.41 is the slave server IP address, in this way, the primary database server can be accessed first with money from the server.

2. Slave Database server settings

Modify the database configuration file/etc/my. cnf. the configuration is as follows:

# Server-id = 1 #### you must comment out server-id = 1,

Server-id = 2 #### set slave id

Master-host = 172.28.3.43 ##### set the IP address of the master server

Master-user = replication ##### set the user name to connect to the master server

Master-password = concept ##### set the password for connecting to the master server

Replicate-do-db = imtest0 ##### set the database to be synchronized. you can set multiple

#### The username and password we created earlier. In addition, if there is a change in the port number, configure the port

Master-port = Set the port you set to OK!

3. restart the master/slave servers #### if you do not restart the master server, you may encounter problems when viewing the status later!

4. log on to mysql from the server and enter: show slave status/G if any of the following information is found:

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

It indicates that the two options are successful. if the two options are not all Yes, it indicates that a configuration of your money is wrong,

When I did not restart the master server, Slave_IO_Running: NO appeared. After restart!

4. monitor the server status

1. monitor the status of the master server

You can use show master status to monitor the status of the master server. the content is as follows:

+------------------+----------+--------------+------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000003 |     1164 |              |                  |+------------------+----------+--------------+------------------+

##### File indicates the log File record and Position indicates the location of the log File, which is also a required identifier for the database to perform the copy operation, the following two fields indicate the copied database name and the database name that is not copied. you can also configure them in the configuration file.

2. monitor slave server status

You can view it through: show slave status/G. In addition, if a problem occurs during Database replication, you can run the reset slave command to copy the thread from the database server, common commands for the Slave Database Server include:

Start slave; #### start the replication thread

Stop slave; #### stop the replication thread

Reset slave; #### reset the replication thread

Change master to; ### dynamically change the configuration to the master server

BitsCN.com