MYSQL version upgrade: precautions for upgrading from 5.7.27 to 8.0.25

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

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`
      

Tags: mysql8

Posted on Sat, 16 Oct 2021 13:42:21 -0400 by geaser_geek