Upgrade MySQL-5.6 to version 5.7 & switch to GTID mode, and upgrade mysql-5.65.7

MySQL upgrade-upgrade from mysql 5.6 to version 5.7 & switch to GTID mode, MySQL upgrade-5.65.7
The database version has not been upgraded in the production environment, but has been upgraded in the test environment and development environment. You can use the mysqldump SQL file or mysql_upgrade to upgrade the SQL file. The former takes a long time and requires sufficient disk space. This article will not discuss it for the time being. The upgrade uses the mysql_upgrade method.

If it is reprinted, please indicate the source of the blog: www.cnblogs.com/xinysu/. The copyright belongs to xiaoradish in the blog garden. Thank you for your support! To upgrade the online environment, follow these steps:

Master-slave switchover

New Slave Database Upgrade

This article mainly records the detailed steps for upgrading the master database 5.6 from the database 5.7 and how to change from the traditional mode to the GTID mode. 1 The steps for upgrading MySQL 5.7 to MySQL are as follows:
 

#1 install the new mysql version # download mysql5.7.17 and copy it to the/opt file directory under the server # decompress the package to create a soft connection, authorize tar zvxf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gzln-s/opt/mysql-5.7.17-linux-glibc2.5-x86_64/usr/local/mysql57chown-R mysql: mysql/usr/data/mysql57 #2 modify the configuration parameters # check that the configurations in the configuration file use the installation directory and modify the old ones: basedir =/usr/local/mysql56plugin-dir =/usr/local/mysql56/lib/plugin/New: basedir =/usr/local/mysqlplugin-dir =/usr/local/mysql/lib/plugin /#3 close mysql [root @ sutest244 mysqlup] #/usr/local/mysql56/bin/mysqladmin -- socket =/tmp/mysql3399.sock-uroot-p shutdownEnter password: [root @ sutest244 mysqlup] # ps axu | grep mysql3399 | grep mysqld [root @ sutest244 mysqlup] #4 start mysql [root @ sutest244 mysqlup] #/usr/local/mysql /bin/mysqld -- defaults-file =/data/mysqlup/mysql3399.cnf & [1] 15477 [root @ sutest244 mysqlup] # ps axu | grep mysql3399 | grep Mysqldmysql 15477 37.1 26.7 11931672 1037520 pts/4 Sl/usr/local/mysql/bin/mysqld -- ults-file =/data/mysqlup/mysql3399.cnf [root @ sutest244 mysqlup] # [root @ sutest244 mysqlup] # vim/data/mysqlup/data/error. log #4.1 check and check the error log after startup to see if any configuration parameter error is reported. If yes, a large amount of dictionary information will be reported when the error log is modified, which is not processed at the moment, next step to fix #5 upgrade the dictionary [root @ sutest244 bin] #/usr/local/mysql/bin/mysql_upgrade -- socket =/tmp/mysql3399.sock-uroot-pEnter passw Ord: Checking if update is needed. checking server version. running queries to upgrade MySQL server. checking system database. mysql. columns_priv OKmysql. db OKmysql. engine_cost OKmysql. event OKmysql. func OKmysql. general_log OKmysql. gtid_executed OKmysql. help_category OKmysql. help_keyword OKmysql. help_relation OKmysql. help_topic OKmysql. innodb_index_stats OKmysql. innodb_table_stats OKmysql. ndb_binlog_in Dex OKmysql. plugin OKmysql. proc OKmysql. procs_priv OKmysql. proxies_priv OKmysql. server_cost OKmysql. servers OKmysql. slave_master_info OKmysql. slave_relay_log_info OKmysql. slave_worker_info OKmysql. slow_log OKmysql. tables_priv OKmysql. time_zone OKmysql. time_zone_leap_second OKmysql. time_zone_name OKmysql. time_zone_transition OKmysql. time_zone_transition_type OKmysql. user OKUpgrading the sys schema. Checking databases. sys. sys_config OK omitted... check that the user database and table are omitted... Upgrade process completed successfully. Checking if update is needed. #6 check that the log is normal.

2. Problems with master database 5.6 slave database 5.7 because the slave database version is 5.7, some system database objects such as mysql, performance, and sys have changed, and some SQL statements have also changed. 2.1 failed to modify user password 1). The problem is that the master database changes the user password, update mysql. user set password = password ('newpasswd') where...

• 2018-03-29T01: 22: 45.058927Z 12 [ERROR] Slave SQL for channel'': Column 1 of table 'mysql. user 'cannot be converted from type' char (16) 'to type' char (32)', Error_code: 1677
  
• 2018-03-29T01: 22: 45.059066Z 12 [ERROR] Error running query, slave SQL thread aborted. fix the problem, and restart the slave SQL thread with "SLAVE START ". we stopped at log 'bin _ log.000003 'position 3208

2). The slave Database Replication stops abnormally due to analysis modification. Because the password field of mysql. user table in mysql 5.6 does not exist in mysql 5.7, it is changed to authentication_string.

3 ). solution 1: handle the SQL statement in advance. Before executing update password, configure the session to not record binlog: set session SQL _log_bin = off, and then run the SQL statement on the master and slave separately. solution 2: post-event processing, if this error occurs, skip the SQL statement from the slave database, enable replication synchronization, and change the password from the slave database.

• Set global SQL _slave_skip_counter = 1;
• Start slave SQL _thread;
• Show slave status;
• Set session SQL _log_bin = off;
• Alter user suuser @ '%' identified by 'newpassword ';
• Flush privileges;
• Set session SQL _log_bin = on;

2.2 SQL syntax question 1 ).

• If the SELECT field exceeds the group by field, an error is returned.
• Select id, name, age, count (*) from tbuser group by name;
• Other SQL Syntax problems

2 ). the default SQL _mode configuration in analytics 5.7 and 5.6 is different. If SQL _mode is not described in the mysql configuration file, after the upgrade, SQL _mode will be changed from region to ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, region, NO_ZERO_DATE, region, no, NO_ENGINE_SUBSTITUTION. In this mode, some SQL statements supported in MySQL 5.6 report a syntax error in MySQL 5.7. 3). solution 1: handle it in advance. In the test environment, test the compatibility of program code in the new version of the database in detail. If an exception exists, fix the SQL operation logic in the program code. Solution 2: handle it in advance. In the mysql configuration file, the specified SQL _mode is consistent with version 5.6. Solution 3: In post-event processing, if this error has already occurred and requires rapid response, you can change SQL _mode to the same as the default SQL _mode in version 5.6. 3 Switch GTID mode 3.1 What is GTIDGlobal Transaction ID, which is a globally unique identifier (GTID). a gtid represents a Transaction occurring on an instance. GTID = source_id: transaction_id. source_id indicates the server_uuid of the instance that executes the transaction, and transaction_id indicates the auto-increment value starting from 1. Therefore, GTID is actually expressed: transaction_id transactions occurred on the source_id instance. 3.2 GTID-related configuration parameters

• ENFORCE_GTID_CONSISTENCY
• Warn
• If GTID incompatible statement usage occurs, the error log will record the relevant information, then you need to adjust the program to avoid incompatible writing until no incompatible statement is generated at all, you can check all the SQL statements through the program, or you can set it to observe the error log for a period of time. This step is very important.
• On
• Enable force GTID consistency

• GTID_MODE
• Description
• OFF
• The new transaction is non-GTID. Slave only accepts transactions without GTID. The transaction sent to GTID reports an error.
• OFF_PERMISSIVE
• The new transaction is non-GTID. Slave only accepts transactions without GTID and also accepts transactions with GTID.
• ON_PERMISSIVE
• The new transaction is GTID, And the Slave only accepts transactions without GTID and also accept transactions with GTID.
• ON
• The new transaction is GTID, and Slave only accepts transactions with GTID.
• Switch order
• Skip is not allowed strictly in the following order
• OFF <==> OFF_PERMISSIVE <==> ON_PERMISSIVE <==> ON

3.3 switching from traditional replication to GTID Replication

 

# Step 1 # modify ENFORCE_GTID_CONSISTENCY to warn, run for a period of time, check whether the error log contains GTID incompatible statement usage, and fix it as soon as possible # Run both the master and slave nodes, the sequence does not require set @ global. enforce_gtid_consistency = warn; # step 2 # modify ENFORCE_GTID_CONSISTENCY to on. If no incompatible syntax exists, you can change it to ON # execute both the master and slave nodes, and set @ global is not required for the sequence. enforce_gtid_consistency = on; # step 3 # SET GTID_MODE to off_permissiv # Run both the master and slave nodes. SET @ GLOBAL is not required for the sequence. GTID_MODE = OFF_PERMISSIVE; # step 4 # SET GTID_MODE to off_permissiv = on_permissiv # the master and slave nodes are executed. SET @ GLOBAL is not required for the sequence. GTID_MODE = ON_PERMISSIVE; # step 5 # check the number of anonymous transactions in all instances, so that all non-GTID transactions are transmitted to the slave database, the following operations can be performed when the variable is 0 # both the master and slave nodes are executed, and the show status like 'ongoing _ ANONYMOUS_TRANSACTION_COUNT 'is not required in the sequence '; # step 6 # check whether all the non-gtids of slave on all instances have completed the show master status; # Run the SELECT MASTER_POS_WAIT command from the file and pos file to the slave database ('bin _ log.000003', 88748605 ); # If the returned result is greater than or equal to 0, the transaction has been completely copied. # step 7 # Clear the binlog and switch to the new binlog. # The master and slave nodes are executed. The flush logs is not required in the sequence; # Step 8 # Start GTID # execute both the master and slave nodes. SET @ GLOBAL is not required for the sequence. GTID_MODE = ON; # step 9 # modify the cnf file # Run both the master and slave nodes. The sequence does not require gtid_mode = onenforce-gtid-consistency = onbinlog_gtid_simple_recovery = 1.

 

 

 

3.4 GTID replication switching traditional Replication

# Step 1 # stop slave database # All slave databases are executed. stop slave is not required in sequence; # step 2 # reset the chanage master to statement and disable master_auto_position # All slave databases are executed, show slave status \ G; # the file and position of SQL _thread are not required in the order of order. Exec_Master_Log_Poschange master to master_log_file = 'mysql-bin.000003', master_log_pos = 4563, master_auto_position = 0; # step 3 # test whether synchronization is normal # operate the data in the master database to check whether the position of the slave database has changed, check whether the data is changed # step 4 # Change GTID_MODE to ON_PERMISSIVE # Run SET @ GLOBAL on both the master and slave nodes. GTID_MODE = ON_PERMISSIVE; # step 5 # Change GTID_MODE to OFF_PERMISSIVE # Run SET @ GLOBAL on both the master and slave nodes. GTID_MODE = OFF_PERMISSIVE; # step 6 # Change GTID_MODE to OFF # Run SET @ GLOBAL on both the master and slave nodes. GTID_MODE = OFF; # step 7 # Clear the binlog and switch to the new binlog # both the master and slave nodes are executed. flush logs is not required in sequence; # step8 # disable GTID, enforce-gtid-consistency can be disabled, or GTID consistency check # both the master and slave nodes are executed. SET @ GLOBAL is not required in the order of order. GTID_MODE = OFF; # step9 # verify synchronization #10 # modify the cnf file and comment out the GTID parameters # Run both the master and slave nodes, order not required # gtid_mode = on # enforce-gtid-consistency = on # binlog_gtid_simple_recovery = 1