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

Today, I will talk to you about how to understand this version of MariaDB.10.5.1. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something from this article.

Tip: the demo environment of this blog is based on centos7.2 x86 installation 64-bit, minimizing installation system and MariaDB.10.5.1 binary installation.

1. Briefly introduce the parallel replication mode of slave library.

5 modes of slave parallel replication for slave_parallel_mode:

Five models given by the government

Description: Controls what transactions are applied in parallel when using parallel replication.optimistic: tries to apply most transactional DML in parallel, and handles any conflicts with rollback and retry. See optimistic mode.conservative: limits parallelism in an effort to avoid any conflicts. See conservative mode.aggressive: tries to maximize the parallelism, possibly at the cost of increased conflict rate.minimal: only parallelizes the commit steps of transactions.none: disables parallel apply completely.

The optimistic optimistic mode of orderly parallel replication of in-order provides many opportunities for parallel applications on slave, while retaining precise transaction semantics from an application point of view. This is the default mode in MariaDB 10.5.1

2. Master_use_gtid parameter description:

When configuring master-slave replication, the three parameters CHANGE MASTER TO master_use_gtid = {slave_pos | current_pos | no} are described:

2.1.Introduces CHANGE MASTER TO master_use_gtid=slave_pos:

If:

A slave is configured to use GTID by CHANGE MASTER TO master_use_gtid=slave_pos

When the slave slave station connects to the master master station, it will start replicating at the location where it was copied to the last GTID of the slave station, as you can see in the variable gtid_slave_pos. Because the GTID is the same on all replication servers, you can point the secondary server to another primary server and automatically determine the correct location.

If you do not want to change the binlog on the secondary server to affect the GTID replication location, you should use master_use_gtid = slave_pos. The slave station will then always connect to the primary station at the last copied GTID location.

This avoids some surprises for users who expect consistent behavior with traditional replication, because the replication location will never change due to local changes made on the server.

If you write any local transactions on the slave station, you may encounter problems using the value current_pos. For example, if you issue an INSERT statement or otherwise write a table when the slave slave thread stops, a new local GTID may be generated in gtid_binlog_pos, which affects the dependent gtid_current_ posvalue. This may result in an error when the slave thread is restarted, because the local GTID will not exist on the primary controller. You can correct this problem by setting the MASTER_USE_GTID replication parameter to slave_pos instead of current_pos.

Important: demo environment for the following three scenarios: centos7.2_x86_64 bit MariaDB.10.5.1 binary installation

Mgr01 172.16.0.130 master

Mgr03 172.16.0.131 slave

1. Scenario 1: when the online master library has been running for some time, and there is very little data in the database. Deploy a new slave library to the master library for this scenario

After mgr03 starts, the GTID site is empty by default, especially gtid_slave_pos.

(root@'mgr03':mysql.sock) [(none)] > show variables like 'Gtid%' +-+-+ | Variable_name | Value | +-+-+ | gtid_binlog_pos | gtid_binlog_state | | gtid_cleanup_batch_size | 64 | | gtid_ Current_pos | gtid_domain_id | 0 | | gtid_ignore_duplicates | OFF | | gtid_pos_auto_engines | | gtid_seq_no | 0 | gtid_slave_pos | | gtid_strict_mode | OFF | +-+- -- + 10 rows in set (0.00 sec) (root@'mgr03':mysql.sock) [(none)] > ALTER USER 'root'@'localhost' IDENTIFIED BY' 123456' Query OK, 0 rows affected (0.02 sec) (root@'mgr03':mysql.sock) [(none)] > show variables like 'Gtid%' +-+-+ | Variable_name | Value | +-+-+ | gtid_binlog_pos | 0-1313306-1 | | gtid_binlog_state | 0-1313306-1 | | gtid_cleanup_batch_size | 64 | gtid_current_pos | 0-1313306-1 | | gtid_domain_id | 0 | gtid_ignore_duplicates | OFF | | gtid_pos_auto_engines | | gtid_seq_no | 0 | gtid_slave_pos | gtid_strict_ Mode | OFF | +-+-+ 10 rows in set (0.01sec)

Master operation:

Tip: this step of reset master is not necessary unless this database has done other cluster slave libraries before this library. Especially on master libraries, this is strictly prohibited unless you know exactly what you are doing

Mysql-e "grant replication slave on *. * to repuser@'172.16.0.%' identified by 'JuwoSdk21TbUser'; flush privileges;" mysqldump-uroot-pendant 123456'-B-A-F-- master-data=2-- single-transaction-- events | gzip > test.sql.gzscp-rp-P52110 / opt/tes.sql.gz root@172.16.0.131:/root

Slave operation:

(root@'mgr03':mysql.sock) [test] > source / root/test.sqlCHANGE MASTER TO MASTER_HOST='mgr01',MASTER_PORT=3306,MASTER_USER='repuser',MASTER_PASSWORD='JuwoSdk21TbUser',master_use_gtid=slave_pos;start slave;show slave status\ G

two。 Scenario 2: when the online master library has been running for a period of time, and the database has very little data. Deploy a slave library to the master library for this scenario. But this new slave library has been the main library of other projects before.

Then the initial position needs to be manually set to empty: SET GLOBAL gtid_slave_pos = ""

Mgr01-master:

Current gtid situation:

Root@localhost [test] > show variables like 'Gtid%' +-+-+ | Variable_name | Value | +-+-+ | gtid_binlog_pos | 0-1283306-50 | | gtid_binlog_state | | 0-1283306-50 | | gtid_cleanup_batch_size | 64 | gtid_current_pos | 0-1283306-50 | | gtid_domain_id | 0 | gtid_ignore_duplicates | OFF | | gtid_pos_auto_engines | | gtid_seq_no | 0 | gtid_slave_pos | 0-1313306-41 | | gtid_ | Strict_mode | OFF | +-+-+ 10 rows in set (0.001 sec)

Mgr03-slave:

Current gtid situation:

(root@'mgr03':mysql.sock) [test] > show variables like 'Gtid%' +-+ | Variable_name | Value | +-+- -+ | gtid_binlog_pos | 0-1313306-45 | | gtid_binlog_state | 0-1283306-42, 0-1313306-45 | | gtid_cleanup_batch_size | 64 | gtid_current_pos | 0-1313306-45 | | gtid_domain_id | 0 | | gtid_ignore _ duplicates | OFF | | gtid_pos_auto_engines | | gtid_seq_no | 0 | gtid_slave_pos | 0-1283306-42 | | gtid_strict_mode | OFF | +- -+-+ 10 rows in set (0.00 sec)

Specific operations:

Master operation:

Mysql-e "grant replication slave on *. * to repuser@'172.16.0.%' identified by 'JuwoSdk21TbUser'; flush privileges;" mysqldump-uroot-pendant 123456'-B-A-F-- master-data=2-- single-transaction-- events | gzip > test.sql.gzscp-rp-P52110 / opt/test.sql.gz root@172.16.0.131:/root

Slave operation:

(root@'mgr03':mysql.sock) [test] > source / root/test.sql (root@'mgr03':mysql.sock) [test] > reset master; (root@'mgr03':mysql.sock) [test] > stop slave;Query OK, 0 rows affected (0.12 sec) (root@'mgr03':mysql.sock) [test] > reset slave all; (root@'mgr03':mysql.sock) [test] > SET GLOBAL gtid_slave_pos = "" Query OK, 0 rows affected (0.03 sec) (root@'mgr03':mysql.sock) [test] > show variables like 'Gtid%' +-+-+ | Variable_name | Value | +-+-+ | gtid_binlog_pos | gtid_binlog_state | | gtid_cleanup_batch_size | 64 | | gtid_ Current_pos | gtid_domain_id | 0 | | gtid_ignore_duplicates | OFF | | gtid_pos_auto_engines | | gtid_seq_no | 0 | gtid_slave_pos | | gtid_strict_mode | OFF | +-+- -- + 10 rows in set (0.00 sec) (root@'mgr03':mysql.sock) [test] > CHANGE MASTER TO MASTER_HOST='mgr01' MASTER_PORT=3306,MASTER_USER='repuser',MASTER_PASSWORD='JuwoSdk21TbUser',master_use_gtid=slave_pos Start slave;show slave status\ G

3. Scenario 3: set from mysqldump or XtraBackup or Mariabackup backup set

Both of these methods can get the correct Binlog location at the time of backup without blocking (all tables are transaction engines), and of course, if there are no writes during backup, then SHOW MASTER STATUS can also provide the correct location.

Once you have obtained the correct Binlog site (file name and offset) at the time of backup, you can use the BINLOG_GTID_POS () function to calculate the GTID:SELECT BINLOG_GTID_POS ("mysql-bin.000011", 342).

Use mysqldump + gtid to create a new slave:

Starting with MariaDB version 10.0.13, mysqldump will do this automatically and write the GTID in the export file, as long as you set-master-data or-dump-slave and set-gtid.

Mgr01-master:

[root@mgr01] # mysqldump-uroot-pendant 123456'-B-A-F-- master-data=1-- gtid-- single-transaction-- events > 2.sql [root@mgr01 ~] # [root@mgr01 ~] # grep 'CHANGE MASTER TO' 2.sql-CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=342;CHANGE MASTER TO MASTER_USE_GTID=slave_pos (root@'mgr01':mysql.sock) [(none)] > SELECT BINLOG_GTID_POS ("mysql-bin.000011" 342) +-- + | BINLOG_GTID_POS ("mysql-bin.000011") 342) | +-+ | 0-1283306-68 | +-+ 1 row in set (0.009 sec) [root@mgr01 ~ ] # scp-rp-P52110 2.sql root@'172.16.0.131':/root

Slave library operation:

(root@'mgr03':mysql.sock) [test] > source / root/2.sql (root@'mgr03':mysql.sock) [test] > show variables like 'gtid_slave_pos' +-+-+ | Variable_name | Value | +-+-+ | gtid_slave_pos | 0-1283306-68 | +-+-+ 1 row in Set (0.00 sec) (root@'mgr03':mysql.sock) [test] > SET GLOBAL gtid_slave_pos = "0-1283306-68" This step is optional (root@'mgr03':mysql.sock) [test] > CHANGE MASTER TO MASTER_HOST='mgr01',MASTER_PORT=3306,MASTER_USER='repuser',MASTER_PASSWORD='JuwoSdk21TbUser',master_use_gtid=slave_pos;start slave;show slave status\ G

Use the full backup of the current day's Mariabackup to create a new slave library:

Operations on the mgr01 master library:

Create backup user root@localhost [(none)] > grant reload,replication client,lock tables,process,super on *. * to mariabackup@'127.0.0.1' identified by 'mypassword';flush privileges; mariabackup-- backup-- target-dir=/data/backup/-- user=mariabackup-- password=mypassword-- host=127.0.0.1 mariabackup-- prepare-- target-dir=/data/backup/cd / data/ on the main master library Tar zcf bak.tar.gz. / backupscp-rp-P22 bak.tar.gz root@172.16.0.131:/root [root@mgr01 data] # cat / data/backup/xtrabackup_binlog_info mysql-bin.000013 7460-1283306-74

Operations on the mgr03 slave library:

[root@mgr03 ~] # mariabackup-- defaults-file=/etc/my.cnf-- copy-back-- target-dir=/data/backup/ chown-R mysql.mysql / data/mysql/mysql3306/data / etc/init.d/mysql start based on Gtid replication configuration: SET GLOBAL gtid_slave_pos = "0-1283306-74"; CHANGE MASTER TO MASTER_HOST='mgr01',MASTER_PORT=3306,MASTER_USER='repuser',MASTER_PASSWORD='JuwoSdk21TbUser',master_use_gtid=slave_pos;start slave;show slave status\ G

Replicate the configuration based on File and Position:

CHANGE MASTER TO MASTER_HOST='mgr01', MASTER_PORT=3306, MASTER_USER= "repuser", MASTER_PASSWORD= "JuwoSdk21TbUser", MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=746;START SLAVE

Mariabackup backup the database from an online slave library, and then use this backup as the slave library for the current master library:

Replicate the configuration based on Gtid:

Master library operation:

Mariabackup-backup-- slave-info-- safe-slave-backup-- target-dir=/data/backup/-- user=mariabackup-- password=mypassword mariabackup-- prepare-- target-dir=/data/backup/ cd / data/; tar zcf bak.tar.gz. / backupscp-rp-P22 bak.tar.gz root@172.16.0.131:/root [root@mgr01 data] # cat / data/backup/xtrabackup_binlog_info mysql-bin.000013 7460-1283306-74

Slave library operation:

SET GLOBAL gtid_slave_pos = "0-1283306-74"; CHANGE MASTER TO MASTER_HOST='mgr01',MASTER_PORT=3306,MASTER_USER='repuser',MASTER_PASSWORD='JuwoSdk21TbUser',master_use_gtid=slave_pos;start slave;show slave status\ G2.2, introduction to CHANGE MASTER TO master_use_gtid=curren_pos:

Suppose we set up two servers An and B with An as the master master server and B as the slave slave server. It's been running for a while. Then at some point, masterA dies and slaveB becomes the new master.

Then, later we want to add the source masterA as the standby library, that is, the slave library as the new master.

Because A has never been a slave library before, it does not have any previously copied GTID, and the gtid_slave_pos is empty

To allow A to be automatically added as a slave library, you can use master_use_gtid = current_pos, which connects with the value of the variable gtid_current_pos instead of gtid_slave_pos, which also takes into account the GTID written to the binary log when the server is the primary server.

When using master_use_gtid = current_pos, there is no need to consider whether the server is master or slave before using CHANGE MASTER.

However, care must be taken not to inject redundant transactions into binlog on slave, which are not intended to be replicated to other servers.

If such an additional transaction is the latest transaction when the slave server starts, it will be used as the starting point for replication. This may fail because the transaction is not on the master primary server. To prevent local transaction changes on the slave secondary server from entering the binlog, set sql_log_bin on the slave to 0.

When GTID strict mode is enabled (by setting @ @ GLOBAL.gtid_strict_mode to 1), it is usually best to use current_pos. In strict mode, additional transactions are not allowed on the primary server

Tip:

The mysql.gtid_slave_ postable should not be modified in any other way. In particular, do not attempt to update the rows in the table to change the slave's idea of the current GTID location

2.3. CHANGE MASTER TO master_use_gtid = no after reading the above, do you have any further understanding of how to understand this version of MariaDB.10.5.1? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.