For database software , After a period of use , Upgrading is inevitable . This upgrade is mainly to deal with various Security vulnerabilities , Improve performance , new function ,bug Other questions . For popular MySQL database , How to deal with upgrading .

Why upgrade

1) Based on safety considerations （ Security vulnerabilities ）

2) Based on performance and Stability considerations ：
MGR Copy , Parallel replication writeset And so on , Performance improvement

3) The new function ：
Hash join , Window function ,DDL instant ,joson Support , MGR Copy , Histogram etc.

4) Operation and maintenance planning ： There are too many versions in the original environment , Unified management version .

5) edition bug：
5.7. Higher version , or 8.0 The version is basically stable .

6) Version lifecycle （8 year ）

Upgrade the scene

For different scenes , It can be upgraded into small versions , Big version upgrade , The characteristics are as follows ：
1） Content of the same version , Refers to the last small sequence upgrade . General repair bug, Add some function points . such as ：5.7.X in 5.7.21 Upgrade to 5.7.30.

• Don't change the data file , Fast upgrade ;
• No cross operating system , You can't cross large versions ;
• On the way in-place upgrade Small version upgrade method ; Answer 5.7.x Inside ;

2） Cross Version 5.7.X To 8.0.X Its architecture has changed a lot .

• Suitable for different operating systems MySQL upgrade , Slow upgrade ;
• logical upgrade Cross machine 、 Cross operating system . Cross platform such as ：Linux→Win,CentOS→ kirin V10;
• Build a high configuration slave Library , Install a new version MySQL, Configure synchronization from library ;
• Through third party tools , Export import ;

Upgrade plan

stay MySQL Upgrading , There are basically three schemes for reference ：

1. Build a high version slave Library

Build a new high-level version from the Library . Need to prepare new hardware Or Slave Library of original options .

MySQL It supports copying from a version series to a higher version series . for example , Can run from MySQL 5.6 Copy the source of to run MySQL 5.7 Copy of , From run MySQL 5.7 Copy the source of to run MySQL 8.0 Copy of , And so on . however , If the source uses statements or depends on the MySQL Behavior no longer supported in version , Then you may encounter difficulties when copying from the old source to the new copy . for example ,MySQL 8.0 No longer supports more than 64 Foreign key name of characters .

2.In-Place Upgrade

Install the new mysql Software , utilize mysql_upgrade Script to complete the upgrade of system tables . Upgrade from the library first , Then switch to the main database .
Compatibility testing required .

• The installation package of the new version replaces the installation package of the old version
• mysql_upgrade Command mode （8.0 mysqld --upgrade）
• Online upgrade , Master node switching ensures session kill fall
  

3. Logical import and export

use mysqldump,mysqlpump,mydumper,navicate Tools such as
1. First import the table structure
2. Then import the data
3. User table
4. stored procedure , trigger , View ,event etc.
5.blob,text,bit After importing fields, you need to pay attention to

4. MySQL5.7 To 8.0 Rolling upgrade

Mysql8.0 It still provides a lot of convenience , Not like before 5.6 upgrade 5.7 like that , Can pass mysql shell Confirm .
stay MySQL 8 in mysql_upgrade The client is now deprecated . The operation performed by the upgrade client is now completed by the server .
Start the new with the older data directory MySQL Binary . Automatically repair user table . There is no need to restart after upgrading .


remarks ：5.7 upgrade 8.0 It's still a new way . But it is still very practical .

Upgrade note

• 1.innodb_fast_shutdown Parameters ：
  0 ： Complete all of full purge and merge insert buffer operation ( Such as ： do InnoDB plugin Upgrade time )
  1 ： Default , No need to complete the above operations , But it will flush the dirty pages in the buffer pool
  2 ： Do not complete the above two operations , Instead, write the log to the log file , Next time it starts , Will perform the recovery operation recovery
  The database is not shut down normally ( Such as ：kill command )/innodb_fast_shutdown=2 when , Recovery operation is required .

• 2.rpm Installation and tar Installation differences ：
  rpm The path is fixed , Therefore, only one can be installed on a system mysql edition .

• 3. Character set , Sort order , Case sensitivity requires attention

• 4.mysql_upgrade
  -s, --upgrade-system-tables
  Only upgrade the system tables, do not try to upgrade the data.
  -f, --force
  Force execution of SQL statements even if mysql_upgrade has already been executed for the current version of MySQL.

• 5. Drive after upgrading JDBC Version of the problem , mysql Some middleware such as ：1.5mycat Compatibility issues :
  https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-versions.html
  

Upgrade check

Not after upgrading , Can , Inspection work is also required ：

• 1. Upgrade is complete , Restart the database ： Whether it can start normally
• 2. Check the upgrade results ：select version();
• 3. Whether the time is normally obtained
• 4.Blob,text, Field checking ： Easy to cut off Lost data
• 5. stored procedure , trigger ,event Can the check be performed ,define user
• 6. High availability switching drill
• 7. Whether business users can normally access

upgrade Error,Waring Handle

mysql Of error Log checking is an essential process . Here are some error records ：

1）innodb-file-format-check

[Warning] /usr/local/mysql-5.7/bin/mysqld: ignoring option ‘–innodb-file-format-check’ due to invalid value ‘Barracuda’
innodb-file-format-check 5.7 non-existent , Need to be commented out , Configuration parameters .

2）Storing MySQL user name or password &found a zombie dump thread

[Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the ‘START SLAVE Syntax’ in the MySQL Manual for more information.
[Note] While initializing dump thread for slave with UUID , found a zombie dump thread with the same UUID. Master is killing the zombie dump thread(2508)

  
  
  

   
   
   MASTER_HEARTBEAT_PERIOD Parameter result :# Method 1： Copy process information processing change master to MASTER_HEARTBEAT_PERIOD =30;# programme 2： You can also set the slave_net_timeout = 3600 Seconds to solve the problem set global slave_net_timeout=3600;
  
  
  

3）Cannot Setup server variables.

If you execute mysql_upgrade The following errors occurred in the process , Explain that permissions lead to , stay my.cnf Add ：skip-grant-tables Just skip it , Remove after execution .

4） The time field

1.timestamp Field default value problem

  
  
  

   
   
    Error Code: 1048. Column ‘create_time’ cannot be null explicit_defaults_for_timestamp=on Next   Don't allow null value .
  
  
  

2.TIMESTAMP Range ：‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’

5） Filter [Warning] Information

  
  
  

   
   
   [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).[Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.[Warning] InnoDB: Using innodb_file_format is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html
  
  
  

about timestamp defaults,innodb_file_format All parameters are obsolete in future versions

6）mysql8.0 Small version upgrade

  
  
  

   
   
   [ERROR] [MY-011091] [Server] Data dictionary upgrade prohibited by thecommand line option '--no_dd_upgrade'.[ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
  
  
  

The fallback program

The fallback plan is formulated for emergency , Make sure the data is not lost .
1. Backup before upgrading
2.binlog Remember not to clear and delete , Make a backup if necessary .
3. Rolling upgrade emergency treatment mode ：
In the process of upgrading from the Library , When the upgrade fails , Do not upgrade , Keep it as it is . Observe and solve problems , Then continue to upgrade .

Preparation for upgrade

There is a good saying , That is not to fight unprepared battles , This tells us that no matter what we do, we should be prepared in advance . Upgrading also requires preparation ：
1) Test library upgrade , Application validation
2) Database upgrade , Unaware of the problem
3)my.cnf Configuration information adjustment
4) Incompatible operation methods , Affect replication
5) A smooth filtration , Column such as upgrading a slave library first , To all slave libraries
6) Minimum downtime , Similarly, production data is restored to the environment , Conduct simulation upgrade , Evaluation time
7) How to verify data ： Row number , Table number wait
8) Consider the rollback scheme
9) Database backup
10） Operation command document & step Get ready

summary

Version update , It's not a simple thing . It takes a certain amount of energy to plan , We also need to coordinate all aspects .
Preparation is essential , Prepare in advance , It is necessary to , This can make things better , No matter what happens, you can deal with it well .