Shulou(Shulou.com)06/03 Report--

This article mainly explains "What is the principle of Replication master-slave replication mechanism?" Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let Xiaobian take you to learn "What is the principle of Replication master-slave replication mechanism"!

Copy Overview

Replication refers to the transfer of DDL and DML operations from the master database to the slave server through binary logs, and then re-execution (also known as redo) of these logs on the slave database, thus keeping the data of the slave and master databases synchronized.

MySQL supports replication of a master database to multiple slave databases simultaneously, and slave databases can also be used as master databases of other slave servers to realize chain replication.

replication principle

MySQL master-slave replication works as follows:

From the top, replication is divided into three steps:

When a transaction is committed, the Master repository records the update of the data as a time event in the binary log file Binlog. (Dump thread-Main library thread)

Master pushes log events from binary log file Binlog to slave and writes Relay Log (I/O thread-slave thread).

Slave reads from the library and redoes (replays) events in the Relay Log, changing the data that reflects itself. (SQL threads-slave library threads)

Copy dominance

MySQL replication has three main aspects:

If there is a problem with the master library, you can quickly switch to the slave library to provide services (provide failover mechanism).

Perform queries on the slave repository, update from the master repository, achieve read/write separation, reduce access pressure on the master repository (improve read/write separation and improve high availability and load and throughput).

Perform backups from the secondary repository to avoid affecting services of the primary repository during backups (improve data security).

Set up the steps master

In the master configuration file (/usr/my.cnf), configure the following:

mysql service ID, which is unique in the entire cluster environment

server-id=1

mysql binlog storage path and file name

log-bin=/var/lib/mysql/mysqlbin

log-bin-index = mysql-bin.index (set binary log index file name)

binlog_format = mixed (binlog pattern)

STATEMENT: statement copy

ROW: Row copy

MIXED: Mixed Copy, default option

sync-binlog = 1 (whether synchronization mode is enabled): default is 0, in order to ensure that data will not be lost, it needs to be set to 1, used to force synchronization of binary logs to disk every time a transaction is committed.

character-set-server = utf8 (string encoding)

Error log, enabled by default

log-err

mysql installation directory

basedir

Temporary directory of mysql

tmpdir

mysql data storage directory

datadir

Read only, 1 means read only, 0 means read write

read-only=0

Ignored data, refers to databases that do not need synchronization (comma separation)

binlog-ignore-db=mysql

Specify synchronized databases

binlog-do-db=db01

After execution, you need to restart Mysql:

service mysql restart;

Create an account to synchronize data and perform authorization operations:

grant replication slave on . to 'itcast'@'192.168.192.131' identified by 'itcast'; flush privileges;

To get a consistent snapshot, read locks are set on all tables:

flush tables with read lock;

Backup primary database data

If the master database can be down, copy all database files directly.

The master library is an online production library that can be backed up using mysqldump and is available to all storage engines.

For transactional engines mysqldump -uroot -ptiger --all-database -e --single-transaction --flush-logs --max_allowed_packet=1048576 --net_buffer_length=16384 > /data/all_db.sql For MyISAM engines, or multi-engine hybrid database mysqldump -uroot --all-database -e--flush-logs --max_allowed_packet=1048576 --net_buffer_length=16384 >/data/all_db. sql

The data in both master and slave databases are consistent. Execute show master status directly to view log coordinates.

show master status;

Field meaning:

File : From which log file to push log files

Position: From which position to start pushing logs

Binlog_Ignore_DB : Specify databases that do not require synchronization

Restore the write operation of the main library:

unlock tables;

slave

Import backup data

mysql -uroot -p

< /data/all_db.sql 在 slave 端配置文件中，配置如下内容： mysql服务端ID,唯一 server-id=2 指定binlog日志 log-bin=/var/lib/mysql/mysqlbin 其他配置 binlog_format = mixed log-slave-updates = 0（控制 slave 上的更新是否写入二进制日志，默认为0；若 slave 只作为从服务器，则不必启用；若 slave 作为其他服务器的 master，则需启用，启用时需和 log-bin、binlog-format 一起使用，这样 slave 从主库读取日志并重做，然后记录到自己的二进制日志中；） relay-log = mysql-relay-bin（设置中继日志文件基本名） relay-log-index = mysql-relay-bin.index（设置中继日志索引文件名） read-only = 1（设置 slave 为只读，但具有super权限的用户仍然可写） slave_net_timeout = 10（设置网络超时时间，即多长时间测试一下主从是否连接，默认为3600秒，即1小时，这个值在生产环境过大，我们将其修改为10秒，即若主从中断10秒，则触发重新连接动作。） 执行完毕之后，需要重启Mysql： service mysql restart； 执行如下指令 ： change master tomaster_host='192.168.2.21',master_user='repl',master_password='repl',master_port=3306,master_log_file='mysql-bin.000001',master_log_pos=120; 指定当前从库对应的主库的IP地址，用户名，密码，从哪个日志文件开始的那个位置开始同步推送日志。 开启同步操作 start slave; show slave status; 可以看到图中显示出来的：IO线程和SQL线程都处于运行状态： Slave_IO_Running：此进程负责 slave 从 master 上读取 binlog 日志，并写入 slave 上的中继日志。 Slave_SQL_Running：此进程负责读取并执行中继日志中的 binlog 日志。 这两个进程的状态需全部为 YES，只要有一个为 NO，则复制就会停止。当 Relay_Master_Log_File = Master_Log_File 且 Read_Master_Log_Pos = Exec_Master_Log_Pos 时，则表明 slave 和 master 处于完全同步的状态。 停止同步操作 stop slave; 验证同步操作 在主库中创建数据库，创建表，并插入数据 ： create database db01;user db01;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'); 在从库中查询数据，进行验证 ： 在从库中，可以查看到刚才创建的数据库：

In this database, query the user table for data:

At this point, I believe that everyone has a deeper understanding of "what is the principle of Replication master-slave replication mechanism," so let's actually operate it! Here is the website, more related content can enter the relevant channels for inquiry, pay attention to us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.