Foreword

In the actual production environment, if the reading and writing of the mysql database are operated in one database server, it cannot meet the actual needs in terms of security, high availability, or high concurrency.

strong>Generally, data should be synchronized through master-slave replication, and then the concurrent load capacity of the database should be improved through read-write separation.

1. Data Backup - Hot Backup & Disaster Recovery & High Availability
2. Separation of Read and Write to Support Greater Concurrency

Introduction to Principles

The process of master-slave replication: two logs (binlog binary log & relay log log) and three threads (one thread of master and two threads of
slave)

1. The update operation of the main library is written into the binlog binary log.
2. The master server creates a binlog dump thread to send the binary log content to the slave server.
3. When the slave machine executes the START SLAVE command, an IO thread will be created on the slave server, and the binary log of the master will be copied to its relay log.
First the slave starts a worker thread (I/O thread), the I/O thread opens a normal connection on the master, and then starts the
binlog dump process, which reads events from the master's binary log, if it has caught up with the
master, it sleeps and waits for the master to generate new events, which the I/O thread writes to the relay log.
4. The sql slave thread (sql slave thread) processes the last step of the process, the sql thread reads events from the relay log, and replays the events in
to update the data of the slave machine so that it matches the masterdata are consistent.As long as the thread is consistent with the I/O thread, the relay log will usually be in the OS cache, so the overhead of the relay log is small.

Question

Why not use the I/O thread to directly read data from the bin-log of the main library and write it to the slave library, but write to the relay log?

Because there may be a lot of bin-log content in the main library, and an I/O thread reads slowly, the data updated from the slave library may be more and more different from the content of the main library, and the data lags behind.

Configuration

Master (centos7): 192.168.131.129
Slave (win10): 192.168.0.6
Ensure network connectivity between master and slave, and ensure that port 3306 is open.

Master configuration:
1. Open binary log, directory: /etc/my.cnf
Configure log_bin and globally unique server-id.

2. Create an account for master-slave library communication

mysql> CREATE USER 'mslave'@'192.168.131.1' IDENTIFIED BY '[email protected]';
mysql> GRANT REPLICATION SLAVE ON . to 'mslave'@'192.168.131.1' IDENTIFIED BY '[email protected]';
mysql> FLUSH PRIVILEGES;

3. Get the log file name and position of binlog

mysql> show master status;

slave configuration:
1. Configure a globally unique server-id (involving modifying the configuration file, you need to restart the mysql57 service)

2. Use the account created by the master to read the binlog synchronization data (stop slave; start slave)

Adjust the parameters according to your own situation

Example, configure the personal main library bin-log wherever it is located

3. START SLAVE
View the master-slave replication status through the show slave status command.show processlist to view the running status of master and slave related threads
.

Common mistakes

1. IO_ERROR

Solution:

1. Use the ping command to check whether the network is connected?
2. Is the port 3306 of the machine where the main library is located normal?telnet xxx.xxx.xxx.xxx 3306
3. Does the firewall restrict ports?
4. View the error log of the main library /var/log/mysql/mysqld.log

2.Last_Error

Solution: Check if the configuration information is wrong, then restart the slave thread

3.SQL_Error

Error reason:

The slave library has not synchronized the mytest library, but the master library uses the SQL of drop database mytest, then this SQL will be written to the bin-log log, and then the I/O thread will read this SQL and write it to the relay log, the slave library reads this SQL from the relay log, and the error is generated after execution.

Solution:

You can stop the slave thread, skip an error, and restart the slave

stop slave;
set global sql_slave_skip_counter = 1;
start slave;