The principle and configuration of MySQL master-slave replication

Working principle diagram:

Principle of master-slave replication:

It is divided into synchronous replication and asynchronous replication. Most of the actual replication architecture is asynchronous replication. The basic process of replication is as follows:

1). The IO process on the Slave connects to the Master and requests the log content from the specified position of the specified log file (or from the beginning of the log);

2). After the Master receives the request from the Slave's IO process, the IO process responsible for replication reads the log information after the specified location of the log according to the request information, and returns it to the Slave's IO process. In addition to the information contained in the log, the returned information also includes the name of the bin-log file and the location of the bin-log that the returned information has reached on the Master side;

3).Slave's IO process receives After receiving the information, add the received log content to the end of the relay-log file on the Slave side in turn, and record the file name and location of the read bin-log on the Master side into the master-info file so that it can be read next time. When retrieving it, you can clearly tell the Master "I need to start from where in a certain bin-log to log the following content, please send it to me";

4). Slave's Sql process detects relay- After new content is added to the log, the content of the relay-log will be parsed immediately to become the executable content when it is actually executed on the Master side, and executed on itself.

Environment description

• Operating system: CentOS6.3_x64

• Main server master: 192.168. 0.202

• Slave server slave: 192.168.0.203

1. mysql master-slave replication

1. Master From the installation of mysql, the version is the same

We installed mysql-5.5.30.tar.gz, omitted here...

2. Modify the master and slave server

master服务器配置：
vi /usr/local/mysql/etc/my.cnf
[mysqld]
server-id=202     #设置服务器唯一的id，默认是1，我们设置ip最后一段，slave设置203
log-bin=mysql-bin # 启用二进制日志
binlog-ignore-db = mysql,information_schema  #忽略写入binlog的库

slave服务器配置：
vi /usr/local/mysql/etc/my.cnf
[mysqld]
server-id=203
replicate-do-db = abc     #只同步abc库
slave-skip-errors = all   #忽略因复制出现的所有错误

3. Restart the master-slave server mysql

/etc/init.d/mysqld restart

4. Create an account on the master server and authorize slave

mysql> mysql -u root -p123.com
mysql> GRANT REPLICATION SLAVE ON *.* to ‘sync’@‘192.168.1.2’ identified by ‘1234.com’; #replication:复制

5. Check the master database status

mysql> show master status;
+------------------+----------+--------------+------------------+
| File  | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 263 |  |   |
+------------------+----------+--------------+------------------+

6. Configure the slave database

mysql> change master to
 -> master_host='192.168.0.202',
 -> master_user='sync',
 -> master_password='1234.com',
 -> master_log_file='mysql-bin.000002',
 -> master_log_pos=263;
#Log和pos是master上随机获取的。这段也可以写到my.cnf里面。

7. Start the slave synchronization process and check the status

mysql> start slave;

The values ​​of Slave_IO_Running and Slave_SQL_Running must be YES to indicate that the status is normal.

8. Verify master-slave synchronization

Create database abc in master mysql, and then check from mysql that the synchronization has been successful!

mysql> create database abc;
mysql> show databases;
+--------------------+
| Database   |
+--------------------+
| information_schema |
| abc    |
| mysql    |
| performance_schema |
| test    |
+--------------------+

Error reported at slave startup:

"Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'"

Solution: The main reason for the error is that the binary file name or pos value set by the slave to the master does not correspond!

First flush logs; clear the logs, then check the status of the master database show master status; look at the log file name and position value;

Then in the slave, execute: CHANGE MASTER TO MASTER_LOG_FILE ='binary log name',MASTER_LOG_POS=value;

Finally start the synchronization process: start slave;

The above is the principle and configuration content of MySQL master-slave replication, please pay attention to more related content PHP Chinese website (www.php.cn)!