The principle and deployment of 5--master-slave replication in the primary knowledge MARIADB

I. Introduction of the Principle

In order to ensure data consistency, the current mainstream database adopts a data storage engine that supports transaction types, such as InnoDB, and so on, when a transaction is committed, it is written to the transaction log, the data is modified, and the statements that have any effect on the data are recorded in the binary log. Master-slave replication is implemented based on binary logs.

The workflow for master-slave synchronization is:

1. Any data modification of the master node in Binlog write Binlog

2. Initiating a request from a node through an I/O thead thread

3. The primary node sends the contents of the Binlog through the I/O dump thread thread

4. From the node through the I/O thead thread writes the contents of the master node Binlog to the local Relaylog

5. The contents of the Relaylog are replay locally by the SQL thread thread from the node

The whole process should be noted in the following areas:

1. The primary node must be binlog, and the node must be turned on Relay_log

2. Each MySQL server must have a unique server_id

3. To avoid write operations from the server, the Read-only option is turned on from the server, but this option is not valid for super users, and a super_read_only option is added after mysql5.7

4.binlog_format must be row format (binlog_do_db and binlog_ignore_db do not support statement format)

Second, the Operation procedure

This experiment uses CentOS7.4 system, 5.5.56-MARIADB, the specific operation steps are as follows:

1. master node Operation steps

(1) Start the master node of the MySQL service, turn on binlog,server_id set to 10,binlog_format set to row

[Mysqld]

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

Skip_name_resolve=on

Log_bin=mysql-binlog

Slow_query_log=on

server-id=10

Innodb_file_per_table=on

Binlog_format=row

(2) Importing a database

[[email protected] ~]# MySQL < hellodb.sql

(3) Create a master-slave copy of the account

MariaDB [mysql]> Grant Replication Slave on * * to ' bak ' @ ' 172.16.10.% ' identified by ' bakpass ';

MariaDB [mysql]> flush Privileges;

(4) Back up the primary node database, in order to keep the node and the master node at this time consistent, using the--master-data=2 option to log and log off the Bin_log log and location used by the primary node at this time

[Email protected] ~]# mysqldump--single-transaction--databases hellodb--master-data=2 > A.sql

(5) Copy the backup to the slave node, and the master node operation completes

[Email protected] ~]# SCP a.sql [email protected]:/tmp/

2. Steps from node operation

(1) Start the MySQL service from the node, start the Relaylog log, change the ServerID

[Mysqld]

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

Skip_name_resolve=on

Relay_log=mysql-relaylog

Relay_log_index=mysql-relaylog

Read_only=on

Relay_log_purge=on

Slow_query_log=on

Server-id=20

Innodb_file_per_table=on

(2) Restore the primary node backup database, confirm the master-slave database data consistent

[[email protected] tmp]# MySQL < a.sql

(3) Specify the current Binlog log and position for the primary server and the primary server

MariaDB [hellodb]> Change Master to

Master_host= ' 172.16.10.30 ',

Master_user= ' Bak ',

Master_password= ' Bakpass ',

master_port=3306,

Master_log_file= ' mysql-binlog.000004 ',

master_log_pos=7734,

master_connect_retry=10;

(4) Start from node (can refer to start specified thread type, not specified as both start)

MariaDB [hellodb]> start slave [Io_thread | Sql_thread];

(5) View from node status (Slave_io_running and slave_sql_running show Yes succeeded)

MariaDB [(none)]> Show slave status \g

1. Row ***************************

master_log_file:mysql-binlog.000004

read_master_log_pos:7734

relay_log_file:mysql-relaylog.000002

relay_log_pos:532

relay_master_log_file:mysql-binlog.000004

Slave_io_running:yes

Slave_sql_running:yes

exec_master_log_pos:7734

Option Description:

Read_master_log_pos read the position position of the primary node Binlog

Exec_master_log_pos performing the position position of the primary node Binlog

Third, the effect verification

1. View the slave node in the master node

MariaDB [hellodb]> show Slave hosts;

2. The master node deletes a row of data to see the position location at this time

MariaDB [hellodb]> Delete from students where stuid=23;

3. Verify that the position from the node that reads and executes the primary node Binlog is normal

Additional notes:

The so-called dual-master is each node is enabled Binlog and Relaylog, pointing to each other, and the proposed Log_slave_updates=on option, so that all synchronous operations in the local Binlog log to save, at this time server_id becomes very important, Between nodes is to determine whether the operation is a self-node generation, production is not recommended to use a dual-master model, will result in inconsistent data, at this time can only discard one node with the other node is the subject

The principle and deployment of 5--master-slave replication in the primary knowledge MARIADB