How MYSQL Master/Slave server configuration works

MYSQL Master-slave server configuration principle 1. Master-slave configuration principle: Mysql Replication is an asynchronous Replication process, from a Mysql instace (we call it a Master) copy to another Mysql instance ). The entire replication process between the Master and Slave is mainly completed by three threads, two of which (SQL thread and IO thread) are on the Slave side, and the other (IO thread) on the Master side. To implement Replication for MySQL, you must first enable the BinaryLog (mysql-bin.xxxxxx) function on the Master side, otherwise it cannot be implemented. The whole replication process is actually because Slave obtains the log from the Master end and then executes the operations recorded in the log in full order on itself. To enable the Binary Log of MySQL, you can use the "-log-bin" parameter option during MySQL Server startup, or. the "log-bin" parameter item is added to the mysqld parameter group (the parameter section marked by [mysqld]) in the cnf configuration file. 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 (or from the beginning) of the log file; 2. after the Master receives a request from the Slave IO thread, the IO thread responsible for replication reads the log information after the specified log location based on the request information and returns it to the Slave IO 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 the position of the Binary Log file in the BinaryLog; 3. after the Slave IO thread receives the information, it writes the received log content to the end of the RelayLog file (mysql-relay-bin.xxxxxx) at the Slave end in sequence, and record the file name and location of the bin-log on the Master end to the master-info file, so that the next read can clearly show the High-Speed Master "I need to start from the location of a bin-log, please send it to me" 4. after the Slave SQL thread detects the newly added content in the Relay Log, it will immediately parse the content in the Log file into the executable Query statements during actual execution on the Master end, and execute these queries on your own. In this way, the same Query is actually executed on the Master and Slave ends, so the data at both ends is exactly the same. 2. Advantages of configuring mysql Master/Slave: 1. Solve the performance bottleneck of the web application system and database, and use the database cluster to implement query load; query operations on databases in a system are much more complex than update operations. 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 the Master/Slave Database Server 1. configuration of the master database server (1). Modify the mysql configuration file (/etc/my. cnf) 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/etc/my. (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 slave server can have money to first access the master database server 2. Modify the database configuration file/etc/my from the settings of the database server. cnf: # server-id = 1 #### comment out server-id = 1, server-id = 2 #### set slave id master-host = 172.28.3.43 ##### set master server IP m Aster-user = replication ##### set master-password = concept #### set the password for connection to the master server replicate-do-db = imtest0 # ##### set the database to be synchronized, multiple usernames and passwords can be set, in addition, if there is a change in the port number, you also need to configure the port master-port = <port> to 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 Slave_IO_Running: YesSlave_ SQL _Running: Yes, the operation is 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 will appear. After restart! 4. monitor the status of the server 1. monitor the status of the master server through show master status. 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. You can view the status of the slave server through: show slave status \ G. In addition, if a problem occurs during the replication of the slave database, you can run the reset slave command to copy threads from the database server. The common operation commands on the slave Database Server include start slave; ### start the replication thread stop slave; #### stop the replication thread reset slave; #### reset the replication thread to change master to ### dynamically change the configuration to the master server