A preliminary understanding of the principle and deployment of Master-Slave replication in MariaDB
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
< hellodb.sql （3）创建一个主从复制的账号 MariaDB [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
< a.sql （3）指定主服务器及主服务器当前的binlog日志和position 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 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.
