MYSQL version upgrade: precautions for upgrading from 5.7.27 to 8.0.25

Why upgrade?

• 5.7.27 there are many known vulnerabilities

Current situation description

5.7.27 there are already used data in the database. Considering that only the database instance is upgraded, it is judged that the version upgrade will not affect the running data

Upgrade preparation

• Configuration file: my.cnf

• Due to the [mysql8] new pit, the lower_ case_ table_ The names = 1 attribute takes effect only during initialization, so you need to write the configuration file in advance

• Due to SQL_ No in the value corresponding to the mode attribute_ AUTO_ CREATE_ User is deleted in MySQL 8.0 and is no longer recognized, so it is necessary to avoid writing redundant attributes to the configuration file

• ERROR 1129 (00000): Host 'xxx' is blocked because of many connection errors. Unblock with 'mysql admin flush hosts'. You need to modify the configuration properties, such as: max_connect_errors=1000

• After mysql is started, the business service will encounter an error connecting to mysql: MySQL 8.0 Public Key Retrieval is not allowed. The parameter allowPublicKeyRetrieval=true needs to be added to the database URL,

• For example, the springboot configuration spring. Datasource. Url = JDBC: mysql://localhost:3306/db -name?useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true&useSSL=false

• Profile template: / etc/mysql8/my.cnf

  [mysqld]
  #secure_file_priv=/var/lib/mysql
  max_connect_errors=1000
  skip-name-resolve
  max_allowed_packet = 100M
  wait_timeout=600
  max_connections=500
  innodb_monitor_enable=all
  performance_schema=ON
  thread_cache_size = 24 
  innodb_lock_wait_timeout = 120
  # Disabling symbolic-links is recommended to prevent assorted security risks
  symbolic-links=0
  
  log_timestamps=SYSTEM
  lower_case_table_names=1
  explicit_defaults_for_timestamp=true
  # ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'employees.employees.emp_no' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
  sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
  

Upgrade steps

• Since 5.7.27 is the mysql instance installed by rpm file, in order to realize seamless connection (port or 3306) of existing services after 8.0.25 upgrade, docker version 20.10.9 and build c2ea9bc mode will be adopted for upgrade

  1. Use mysqldump to back up the business database in use

  2. Suspend the previous business service and mysql instance of 5.7.27

  3. Start the 8.0.25 database based on the initialization configuration file my.cnf:

     docker run --name mysql8 -v /opt/mysql8:/var/lib/mysql -v /etc/mysql8:/etc/mysql/conf.d -v /home/mysql-bak/mysql5.7.27-mysql8.0.25:/home -p 3306:3306 -e MYSQL_ROOT_PASSWORD=your-pwd -d mysql:8.0.25 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

  4. Import SQL that has backed up mysqldump

  5. Finally, restart the business service to complete the upgrade

Database management

Allow root account remote access

1. grant all privileges on *.* to 'root'@'%' with grant option;
2. flush privileges;

reference material

• [mysql8] change the size conversion setting of the Mysql table
• docker installs the pit lower of Mysql8.0_ case_ table_ names
• NO_AUTO_CREATE_USER is deleted in MySQL 8.0 and is no longer recognized.
• New MYSQL feature secure_ file_ The influence of priv on reading and writing files
• max_connect_errors = 1000
• Java connection to Mysql database exception: Public Key Retrieval is not allowed
• Meaning of various MySQL timeout parameters

It may be useful

• sed & symbol

• sed replaces the string with the special character "/"

  • sed "s/The original string contains/The replacement string contains/g"
    
    sed -i "s/\/root/\/headless/g" /etc/passwd
    
    [Linux Find and replace strings in all files in the directory](http://rubyer.me/blog/1613/)
    Find files containing strings under folders
     Example: find/usr/local "All contents in the directory" rubyer.me"File.
    
    grep -lr 'rubyer.me' /usr/local/*
    vim Replace all string methods in a single file
     Example: replace all in the current file old by new
    
    :%s/old/new/g
    #%Indicates that there is a row, and g indicates that all matching points in a row are replaced.
    Replace files containing strings in folders
    sed combination grep
     Example: to add a directory/www In all files below zhangsan All modified to lisi，Do it in this way.
    
    sed -i "s/old/new/g" `grep old -rl /www`