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

A brief introduction to the principle

At present, in order to ensure data consistency, mainstream databases use data storage engines that support transaction types, such as innodb, etc., when a transaction is first written to the transaction log, the data is modified, and any statements that affect the data are recorded in the binary log. Master-slave replication is based on the binary log.

The workflow of master-slave synchronization is:

1. Any data modification of the master node is written to binlog by binlog

two。 The slave node initiates the request through the Imax O thead thread

3. The master node sends the contents of the binlog through the Imax O dump thread thread

4. The slave node writes the contents of the master node binlog to the local relaylog through the Imax O thead thread.

5. The slave node uses the sql thread thread to replay the contents of relaylog locally.

What should be paid attention to in the whole process:

1. The master node must enable binlog, and the slave node must enable relay_log.

two。 Each mysql server must have a unique server_id

3. To avoid writing from the server, the read-only option is enabled from the server, but it is not valid for super users. A new super_read_only option has been added after mysql5.7.

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

II. Operation steps

The CentOS7.4 system, 5.5.56-MariaDB, is used in this experiment. The specific steps are as follows:

1. Master node operation steps

(1) start the mysql service of the master node, and set the binlog,server_id to 10, and set the binding format 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) Import a database

[root@host3 ~] # mysql

Grant replication slave on *. * to 'bak'@'172.16.10.%' identified by' bakpass'

MariaDB [mysql] > flush privileges

(4) backup the master node database in order to keep the slave node consistent with the master node at this time, and use the-- master-data=2 option to record and log out the bin_log log and location used by the master node at this time.

[root@host3] # mysqldump-- single-transaction-- databases hellodb-- master-data=2 > a.sql

(5) copy the backup to the slave node, and the operation of the master node is complete.

[root@host3 ~] # scp a.sql root@172.16.10.40:/tmp/

two。 Steps from the node

(1) start the mysql service of the slave node, start the relaylog log, and change 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 database backed up by the master node and confirm that the data of the master-slave database is consistent

[root@host4 tmp] # mysql

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 the slave node (can refer to start the specified thread type, not all start)

MariaDB [hellodb] > start slave [IO_THREAD | SQL_THREAD]

(5) View the status of slave nodes (Slave_IO_Running and Slave_SQL_Running show that Yes is successful)

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 reads the position location of the primary node binlog

Exec_Master_Log_Pos executes the position location of the primary node binlog

Third, effect verification

1. View the slave node in the master node

MariaDB [hellodb] > show slave hosts

two。 The master node deletes a row of data to view the position location at this time

MariaDB [hellodb] > delete from students where stuid=23

3. Confirm whether the position of reading and executing the master node binlog is normal at the slave node

Supplementary note:

The so-called dual-master means that binlog and relaylog are enabled on each node, pointing to each other, and it is recommended to turn on the log_slave_updates=ON option so that all operations done synchronously are saved in the local binlog log. At this time, server_id becomes very important. Nodes judge whether the operation is generated by their own nodes through server_id. It is not recommended to use dual-master model in production, which will cause data inconsistency. At this time, only one node can be abandoned and the other node shall prevail.

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.