MYSQL版本升级：5.7.27升级到8.0.25的注意事项

为什么要升级？

• 5.7.27存在不少已知漏洞

现状描述

5.7.27数据库中已经存在使用的数据，考虑到只是升级数据库实例，所以判断版本升级是不会影响运行数据的

升级准备

• 配置文件：my.cnf

• 由于[mysql8]新坑，该lower_case_table_names=1属性只有在初始化时才会生效，所以需要提前写入配置文件

• 由于sql_mode属性对应的值中的NO_AUTO_CREATE_USER在 MySQL 8.0 中被删除，并且不再被识别，所以需要避免写入多余的属性到配置文件

• mysql启动后，应用服务连接数据库时会遇到的错误：ERROR 1129 (00000): Host ‘xxx’ is blocked because of many connection errors. Unblock with ‘mysqladmin flush-hosts’，需要修改配置属性，比如：max_connect_errors=1000

• mysql启动后，业务服务连接mysql会遇到错误：MySQL 8.0 Public Key Retrieval is not allowed，需要在数据库URL追加参数allowPublicKeyRetrieval=true，

• 比如springboot配置spring.datasource.url=jdbc:mysql://localhost:3306/db-name?useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true&useSSL=false

• 配置文件模板：/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
  

升级步骤

• 由于5.7.27是rpm文件安装的mysql实例，为了实现8.0.25升级后能够无缝对接（端口还是3306）已经的服务，将采用Docker version 20.10.9, build c2ea9bc方式进行升级

  1. 采用mysqldump的方式备份正在使用的的业务数据库

  2. 暂停之前的业务服务和5.7.27的mysql实例

  3. 基于初始化配置文件my.cnf，启动8.0.25数据库：

     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. 导入已经备份mysqldump的SQL

  5. 最后重启业务服务,完成升级

数据库管理

允许root账户远程访问

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

参考资料

• [mysql8]新坑哈 更改Mysql 表的大小转换设置
• docker安装Mysql8.0的坑之lower_case_table_names
• NO_AUTO_CREATE_USER在 MySQL 8.0 中被删除，并且不再被识别。
• MYSQL新特性secure_file_priv對讀寫檔案的影響
• max_connect_errors = 1000
• Java连接Mysql数据库异常：Public Key Retrieval is not allowed
• MySQL 各种超时参数的含义

可能用得上的

• sed 之 & 符号

• sed 替换带有特殊字符"/”的字符串

  • sed "s/原字符串包含/替换字符串包含/g"
    
    sed -i "s/\/root/\/headless/g" /etc/passwd
    
    [Linux查找和替换目录下所有文件中字符串](http://rubyer.me/blog/1613/)
    查找文件夹下包含字符串的文件
    例：查找/usr/local目录下所有包含”rubyer.me”的文件。
    
    grep -lr 'rubyer.me' /usr/local/*
    vim替换单个文件中所有字符串方法
    例：替换当前文件中所有old为new
    
    :%s/old/new/g
    #%表示替换说有行，g表示替换一行中所有匹配点。
    替换文件夹下包含字符串的文件
    sed结合grep
    例：要将目录/www下面所有文件中的zhangsan都修改成lisi，这样做：
    
    sed -i "s/old/new/g" `grep old -rl /www`