Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the differences between 5.6and 5.5in mysql". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Differences: 1, master-slave configuration in version 5.5 can not omit binlog and POS parameters, but in version 5.6 these two parameters can be omitted; 2, in version 5.5 does not support multithreaded replication, synchronous replication is single-threaded, queued, while in version 5.6 supports multithreaded replication.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

What's the difference between 5.6and 5.5in mysql

5.6 improvements:

1. In mysql versions 5.5 and earlier, binlog and POS should be specified in the slave node configuration for master-slave configuration. And in 5.6 and beyond

These two parameters can be omitted. MySQL can automatically find synchronization through the internal GTID mechanism. We only need to specify the IP, username and password, and port of the master.

2. 5.6 support for multithreaded replication

In 5.5, synchronous replication is single-threaded, queued and can only be performed one by one. In 5.6, multiple libraries can be copied at the same time (note: multithreading is still not allowed in the same library).

The parameter UUID will be covered in 5. 6

MySQL [(none)] > show variables like'% uuid%' +-+-+ | Variable_name | Value | +-+- -+ | server_uuid | ca910cf0-3aec-11e6-9319-b888e3dcfeb8 | +-+-- + 1 row in set (0.00 sec)

Note: this UIID is automatically generated and written to auto.cnf when mysql is started for the first time. It is not recommended to change this value. And server_uuid is closely related to GTID.

GTID: global transaction identifier

When using this feature, each transaction commit generates a unique identifier in binlog, which consists of UUID and transaction ID. The ID of the transaction committed for the first time is 1, and then increments in turn.

When GTID is enabled, there is no need to find binlog logs and POS points when slave does synchronous replication. direct

GTID is written:

Another traditional way to write change master tomaster_HOST=192.168.2.100,master_PORT=2206,master_USER=repluser,master_PASSWORD='123456',master_AUTO_POSITION=1;: CHANGE MASTER TOMASTER_HOST='master2.mycompany.com',MASTER_USER='replication',MASTER_PASSWORD='bigs3cret',MASTER_PORT=3306,MASTER_LOG_FILE='master2-bin.001',MASTER_LOG_POS=4,MASTER_CONNECT_RETRY=10

If GTID has been enabled before, then the traditional change master to method cannot be used again, and an error will be reported as follows:

ERROR 1776 (HY000): Parameters MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when MASTER_AUTO_POSITION is active.

The workflow of GTID:

1. Commit a transaction on master and write to binlog

2. Binlog is sent to slave, and slave receives and writes relay log,slave to read to the GTID, and sets the value of gtid_next. For example:

Set @ @ SESSION.GTID_NEXT='B0869D03-D332223-35454 3'

Then tell slave that the next transaction must use GTID and write to its own binlog.

3. Slave checks and confirms that the gtid is not in use, and if not, starts executing the transaction and writes it to its own binlog.

4. Because the value of gtid_next is not empty, slave does not try to generate a new gtid, but obtains the GTID through master-slave synchronization.

In addition, if you want to use GTID for master-slave synchronization, add the following configuration to my.cnf:

[mysqld] log-bin=mysql-binbinlog_format = mixedlog_slave_updates = ONgtid-mode = ONenforce_gtid_consistency = ON

Then export mysqldump-uroot-proot-Q-- single-transaction-R-E-- triggers-B hellodb > / root/hello.sql on master

Import mysql-uroot-proot on slave

< /root/hello.sql 在slave上配置change master to指向(如下6行代码)： change master tomaster_HOST=192.168.2.100,master_PORT=3306,master_USER=repluser,master_PASSWORD='123456',master_AUTO_POSITION=1; GTID的局限性： 1、GTID的复制是基于事务的，不支持MyISAM，这可能导致多个GTID分配各同一个事务。 2、对create table ... select语句不支持。因为该语句会被拆分成create table和insert 两个事务，并且如果这两个事务被分配了同一个GTID，将会导致insert被备库忽略掉。 3、不支持创建、删除临时表 多线程复制演示： 在slave上执行下面几条命令： >

Stop slave; > set global slave_parallel_workers = 4; > start slave; > show full processlist; can see that there are 4 threads Waitingfor an eventfromCoordinator

If there are a large number of insert operations on the master at this time, you can execute > select * from mysql.slave_worker_info\ G on the slave. You should be able to see that the worker_id is constantly changing, indicating that multithreaded replication is at work.

Description: slave_parallel_workers can achieve multi-thread concurrent replication on slave. However, it can only support concurrent replication between multiple database under one instance, and can not really achieve concurrent replication of multiple tables. Therefore, when there is a large concurrent load, slave still has no way to catch up with master in time, and needs to find ways to optimize it. For example, try to split the tables in a library into multiple libraries according to business logic, so that slave can enable multi-thread replication during write operations, reducing synchronization latency.)

In addition, it is recommended to modify the my.cnf by adding 2 lines (by default, this info_file is file and does not write to the database)

Relay_log_info_repository = tablemaster_info_repository = table

This alone is not enough. By default, these two tables are MyISAM, and they have to be converted if they are not safe.

> alter table slave_master_info engine innodb; > alter table slave_relay_log_info engine innodb; > alter table slave_worker_info engine innodb

In this way, the damage to the table can be prevented and can be repaired by itself after the damage.

Master-slave replication in GTID mode, the solution that cannot be skipped when reporting an error during synchronization:

If you see a synchronous error on slave, "the XXX key of the slave node does not exist"

We can try the old method on 5.5.

> stop slave; > set global sql_slave_skip_counter=1 > start slave

During the execution, you will find that there is an error. The prompt is as follows:

You can see that running in GTID mode, sql_slave_skip_counter is not supported for skipping.

Then you can skip it in the following ways:

> show slave status\ G to view the following 2 lines of information:

Retrieved_Gtid_Set: ca910cf0-3aec-11e6-9319-b888e3dcfeb8:1-2Executed_Gtid_Set: ca910cf0-3aec-11e6-9319-b888e3dcfeb8:1

The first line represents the received transaction, and the second line represents the transaction that has been completed. In other words, an error occurred when the execution reached Retrieved_Gtid_Set.

Therefore, we can just skip this transaction.

> stop slave; > set GTID_NEXT='ca910cf0-3aec-11e6-9319 color b888e3dcfeb8 YES 2; this is the way to write, do not add any 1-2 these things > begin; > commit; > set GTID_NEXT= "AUTOMATIC"; # set gtid_next back > start slave; > show slave status\ G to verify whether the IO/SQL is in YES state.

The method of transforming GTID mode into traditional mode and the points for attention:

To convert to traditional mode, comment out the following two lines in my.cnf:

# gtid-mode=ON# enforce_gtid_consistency = ON

Then restart MySQL.

Log in to mysql and execute a command similar to the following:

> stop slave; > CHANGE MASTER TOMASTER_HOST='master2.mycompany.com',MASTER_USER='replication',MASTER_PASSWORD='bigs3cret',MASTER_PORT=3306,MASTER_LOG_FILE='master2-bin.001',MASTER_LOG_POS=4,MASTER_CONNECT_RETRY=10

The result was wrong, as shown in the following figure:

Solution:

> change master to MASTER_AUTO_POSITION=0; # turns off this parameter, which is only used by GTID replication. > CHANGE MASTER TOMASTER_HOST = '192.168.2.11 recording master users replicated usernames 123456, master login Port 3306, master logos FILEDs mysqlpin, 000012 MASTERLOGUSERVONETRY10; > start slave; > show slave status\ G verify whether the IO/SQL is in YES status. This is the end of the introduction of "what's the difference between mysql 5.6 and 5.5". Thank you. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.