MySql advanced master-slave synchronization ☆

Master slave synchronization of MySql advanced

1. Replication overview

This means that the master and slave databases can be synchronized again by transferring these data to the master and slave databases through the DDL.

MySQL supports the replication of one master database to multiple slave databases at the same time. The slave database can also be used as the master database of other slave servers to realize chain replication.

2. Replication principle

The master-slave replication principle of MySQL is as follows.

From the upper level, replication is divided into three steps:

  • When the transaction is committed, the Master master database will record the data changes as time Events in the binary log file Binlog.

  • The master database pushes the log events in the binary log file Binlog to the Relay Log relay log of the slave database.

  • slave redoes the events in the relay log and will change the data reflecting itself.

3. Replication advantages

The advantages of MySQL replication mainly include the following three aspects:

  • If there is a problem with the master library, you can quickly switch to the slave library to provide services.

  • The query operation can be performed on the slave database and updated from the master database to realize the separation of reading and writing and reduce the access pressure of the master database.

  • You can perform a backup from the secondary library to avoid affecting the services of the primary library during the backup.

4. Construction steps

4.1 master

  1. In the master configuration file (/ usr/my.cnf), configure the following:
#mysql service ID to ensure uniqueness in the whole cluster environment
server-id=1

#Storage path and file name of mysql binlog log
log-bin=/var/lib/mysql/mysqlbin

#The error log is enabled by default
#log-err

#mysql installation directory
#basedir

#Temporary directory of mysql
#tmpdir

#mysql data storage directory
#datadir

#Is it read-only? 1 means read-only and 0 means read-write
read-only=0

#Ignored data refers to the database that does not need to be synchronized
binlog-ignore-db=mysql

#Specify the database to synchronize
binlog-do-db=mysql_senior
  1. After execution, you need to restart Mysql:
service mysql restart;
  1. Create an account for synchronizing data and perform authorization operations:
grant replication slave on *.* to 'justweb'@'192.168.68.102' identified by 'justweb';	
flush privileges;
  1. View master status:
show master status;

Field meaning:

  • File: which log file to start pushing log files from
  • Position: where to start pushing logs
  • Binlog_Ignore_DB: Specifies the database that does not need to be synchronized

4.2 slave

  1. In the slave side configuration file, configure the following:
#mysql server ID, unique
server-id=2

#Specify binlog log
log-bin=/var/lib/mysql/mysqlbin
  1. After execution, you need to restart Mysql:
 service mysql restart;
  1. Execute the following instructions:
change master to master_host= '192.168.68.101', master_user='justweb', master_password='justweb', master_log_file='mysqlbin.000001', master_log_pos=413;

Specify the IP address, user name and password of the primary database corresponding to the current slave database, and the location from which the log file starts to synchronously push logs.

  1. Turn on synchronous operation
start slave;
show slave status;

  1. Stop synchronization
stop slave;

4.3 verify synchronization operation

  1. Create a database in the main library, create a table, and insert data:
create database mysql_senior default charset=utf8mb4;

use mysql_senior;

create table user(
	id int(11) not null auto_increment,
	name varchar(50) not null,
	sex varchar(1),
	primary key (id)
)engine=innodb default charset=utf8;

insert into user(id,name,sex) values(null,'Tom','1');
insert into user(id,name,sex) values(null,'Trigger','0');
insert into user(id,name,sex) values(null,'Dawn','1');
  1. Query data from the library for verification:
  2. In the slave database, you can view the database just created;

Tags: Database MySQL

Posted by upit on Wed, 25 May 2022 17:55:35 +0300