How MySQL replication works and the implementation of master-slave replication

There are 3 main steps to the MySQL replication function

The primary server logs changes to the binary log (these are called Binary log events)

Copy the primary server's binary log events from the server to its trunk log

Redo the events in the relay log from the server.

The first part of the process is that the primary server records the binary logs, and before each transaction updates the data is completed,Master logs These changes in binary logging, and MySQL writes the transaction serially to the binary log, after the event is written to the binary log, The primary server notifies the storage engine to commit the transaction and can then receive requests from the server.

The next step is to copy the primary service's binary log from the server to its own trunk log, starting with a worker thread from the server, I/o threads,I/O threads open a normal connection on the primary server, and then start binlog dump process(binary dump thread) on the primary node. binlog Dump process reads events from the primary server's binary log, and if it has been followed by the primary server, it sleeps and waits for the primary server to generate new events,which the I/O thread writes to the relay log.

SQL handles the last step of the process from the thread, and the SQL thread reads the events from the log and replays the events in them to update the data from the service so that it is consistent with the data in the master service. As long as the thread is consistent with the I/O thread, the trunk log is typically located in the Os cache, so the overhead of the trunk log is minimal.

MySQL implements master-slave replication of the database

Environment Preparation:CentOS System Server 2 , a user to do the MySQL master server, one for the MySQL from the server, configure a good yum source, The firewall shuts down, each node clock service synchronizes, each node can communicate with each other through the host name.

Two: Prepare steps iptables–f && sentenforce Empty The firewall policy, turn off selinux

start the two server MySQL service separately to ensure the service is OK.

[[email protected] ~]# yum install-y mariadb[[email protected] ~]# yum install-y mariadb-server[[email protected] ~]# sy Stemctl restart Mariadb[[email protected] ~]# iptables-f[[email protected] ~]# Getenforce

Configure Master master server

Includes opening binaries, specifying a unique server ID

Server-id #配置server-id, let the primary server have a unique ID number

Log-bin=mysql-bin #打开mysql日志, the log format is in binary

Skip-name-resolve #关闭名称解析 (not required)

[[email protected] ~]# Vim/etc/my.cnf[mysqld]server-id = 1log-bin = Master-logskip_name_resolve = on save exit [email protecte D] ~]# systemctl restart MARIADB

View the status of the primary server

Create a copy Account

Establish a backup account in the database of the primary server, each user who connects to the master server with a standard MySQL user name and password from the server, for the replication operation

MariaDB [(None)]> Grant replication Slave,replication Client on * * to ' slave ' @ ' 172.17.%.% ' identified by ' 123456 ';

Configure the slave server

Configure from the server, open the relay log, specify a unique server ID, set the read-only permission, add the following value in the configuration file

server-id=2 #配置server-id to have a unique ID number from the server
Relay_log = Mysql-relay-bin #打开Mysql日志, log format is binary
Read_Only = 1 #设置只读权限
Log_bin = Mysql-bin #开启从服务器二进制日志
Log_slave_updates = 1 #使得更新的数据写进二进制日志中

Server-id=2relay-log=mysql-relay-binread-only=1log-bin=mysql-binlog-slave-updates=1

Then restart the service

[Email protected] ~]# systemctl restart MARIADB

Start a replication thread from the server, let the primary server connect from the server, and start the re-master server

MariaDB [(none)]> change master to master_host= ' 172.17.252.89 ', master_user= ' slave ', master_password= ' 123456 ', master_log_file= ' master-log.000006 ', and master_log_pos=245;

The start slave is then executed in the database; Start the replication thread

View the status from the server

You can use show SLAVE status\g to view the status from the server, as shown below, or show processlist \g to view the current replication status

Then we build a database on the main server.

The database can then be seen from the server, and the master-slave replication succeeds.

How MySQL replication works and the implementation of master-slave replication