MySQL版本升级

升级说明

MySQL升级的实质

对数据字典的升级

数据字典有：mysql、information_schema、performance_schema、sys schema。

MySQL升级的两种方式

大版本升级（需要考虑业务的可用性）

5.6 to 5.7
5.6 to 8.0
5.7 to 8.0

小版本升级（业务不是必须的）

5.7.11 to 5.7.22
8.0.11 to 8.0.12

升级技术

1）备份恢复：mysqldump，mysqlpump，mydumper

2） mysql_upgrade

升级的注意事项

升级之前要做好备份

确认新版本是否有重大变更

注意 SQL mode 的变化

比如：在MySQL5.7中发生了SQL mode的变化，对不再支持的SQL mode，部分SQL会跑不通，此时可以清空SQL mode，跑完之后在设置SQL mode。

升级成功后，确认业务SQL是否可以跑通

程序层是否都正常

有时原使用的程序语言部分内容不被支持新版本数据库。比如，有一次在5.1时用的是PHP4.0，但升级到5.6，PHP的某些函数不被支持。

在升级完成之后，一定要在测试时使用和线上版本相同的程序，测试是否存在问题。

存储引擎的变化

比如：在8.0版本，不再支持myisam 引擎。

注意字符集的乱码问题

大版本升级

5.7 to 8.0

1、在原版本创建测试数据库和测试数据

[root@itpuxdb app]# pwd
/mysql/app
[root@itpuxdb app]# 
[root@itpuxdb app]# ls -lsa *.sql
47380 -rw-r--r-- 1 mysql mysql 48513198 Mar 10  2018 itpuxdb.sql
[root@itpuxdb app]# 

[root@itpuxdb app]# mysql -uroot -prootroot -e "create database itpuxdb;"
[root@itpuxdb app]# mysql -uroot -prootroot itpuxdb < itpuxdb.sql
[root@itpuxdb app]# mysql -uroot -prootroot  itpuxdb -e "show tables"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------------+
| Tables_in_itpuxdb |
+-------------------+
| bm                |
| dd                |
| dq                |
| gj                |
| gw                |
| itpux11           |
| itpux12           |
| itpux_m1          |
| itpux_m5          |
| itpux_obj         |
| itpux_sales       |
| itpux_yg          |
| jl                |
| yg                |
+-------------------+
[root@itpuxdb app]#

2、下载解压新版安装包

#当前的版本
[root@itpuxdb ~]# mysql --version
mysql  Ver 14.14 Distrib 5.7.20, for linux-glibc2.12 (x86_64) using  EditLine wrapper
[root@itpuxdb ~]# 
[root@itpuxdb ~]# mysql -uroot -prootroot -e "select @@version"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@version  |
+------------+
| 5.7.20-log |
+------------+
[root@itpuxdb ~]# mysql -uroot -prootroot -e "select version()"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| version()  |
+------------+
| 5.7.20-log |
+------------+
[root@itpuxdb ~]#


#老版本的目录
[root@itpuxdb app]# pwd
/mysql/app
[root@itpuxdb app]# ll
total 603148
drwx------ 2 mysql mysql     16384 Jan 31  2021 lost+found
lrwxrwxrwx 1 mysql mysql        35 Jan 31  2021 mysql -> mysql-5.7.20-linux-glibc2.12-x86_64
drwxr-xr-x 9 mysql mysql      4096 Jan 31  2021 mysql-5.7.20-linux-glibc2.12-x86_64

#上传解压高版本的代码包
[root@itpuxdb app]# ll mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz
-rw-r--r-- 1 root root 617598500 Aug 12 18:02 mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz
[root@itpuxdb app]# tar xvJf mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz
[root@itpuxdb app]# ll
lrwxrwxrwx 1 mysql mysql        35 Jan 31  2021 mysql -> mysql-5.7.20-linux-glibc2.12-x86_64
drwxr-xr-x 9 mysql mysql      4096 Jan 31  2021 mysql-5.7.20-linux-glibc2.12-x86_64
drwxr-xr-x 9 root  root       4096 Aug 12 18:23 mysql-8.0.34-linux-glibc2.12-x86_64
-rw-r--r-- 1 root  root  617598500 Aug 12 18:02 mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz
[root@itpuxdb app]#

#删除原链接，做新链接
[root@itpuxdb app]# ln -sf mysql-8.0.34-linux-glibc2.12-x86_64 mysql
[root@itpuxdb app]# ll
total 650552
lrwxrwxrwx 1 root  root         35 Aug 12 18:24 mysql -> mysql-8.0.34-linux-glibc2.12-x86_64
drwxr-xr-x 9 mysql mysql      4096 Jan 31  2021 mysql-5.7.20-linux-glibc2.12-x86_64
drwxr-xr-x 9 root  root       4096 Aug 12 18:23 mysql-8.0.34-linux-glibc2.12-x86_64
-rw-r--r-- 1 root  root  617598500 Aug 12 18:02 mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz
[root@itpuxdb app]#

3、停止MySQL服务

#停止mysql数据库服务
service mysql stop

4、准备适合8.0版本的参数文件和启动脚本

#注意5.7和8.0的一些参数还不一样，所以需要准备适应与8.0的参数文件和mysql服务文件：
[root@itpuxdb app]# ll
drwx------ 2 mysql mysql     16384 Jan 31  2021 lost+found
-rw-r--r-- 1 root  root       3911 Feb 25  2021 my.cnf
lrwxrwxrwx 1 mysql mysql        35 Jan 31  2021 mysql -> mysql-5.7.20-linux-glibc2.12-x86_64
drwxr-xr-x 9 mysql mysql      4096 Jan 31  2021 mysql-5.7.20-linux-glibc2.12-x86_64
-rw-r--r-- 1 root  root  617598500 Aug 12 18:02 mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz
-rw-r--r-- 1 root  root      10735 Aug  1  2018 mysql.server
[root@itpuxdb app]# 

#进去新的代码路径里备份mysql.server文件
[root@itpuxdb support-files]# pwd
/mysql/app/mysql/support-files
[root@itpuxdb support-files]# ll
total 20
-rwxr-xr-x 1 7161 31415  1061 Jun 22 19:07 mysqld_multi.server
-rw-r--r-- 1 7161 31415  2027 Jun 22 20:56 mysql-log-rotate
-rwxr-xr-x 1 7161 31415 10576 Jun 22 20:56 mysql.server
[root@itpuxdb support-files]# mv mysql.server mysql.server.bak
[root@itpuxdb support-files]# ll
total 20
-rwxr-xr-x 1 7161 31415  1061 Jun 22 19:07 mysqld_multi.server
-rw-r--r-- 1 7161 31415  2027 Jun 22 20:56 mysql-log-rotate
-rwxr-xr-x 1 7161 31415 10576 Jun 22 20:56 mysql.server.bak
[root@itpuxdb support-files]# 


#然后修改一份该文件，或者直接使用我们之前准备好的，放到该路径下
[root@itpuxdb support-files]# cp /mysql/app/mysql.server .
[root@itpuxdb support-files]# ll
total 32
-rwxr-xr-x 1 7161 31415  1061 Jun 22 19:07 mysqld_multi.server
-rw-r--r-- 1 7161 31415  2027 Jun 22 20:56 mysql-log-rotate
-rw-r--r-- 1 root root  10735 Aug 12 18:27 mysql.server
-rwxr-xr-x 1 7161 31415 10576 Jun 22 20:56 mysql.server.bak


#修改权限
[root@itpuxdb support-files]# chmod 775 mysql.server
[root@itpuxdb support-files]# ll
total 32
-rwxr-xr-x 1 7161 31415  1061 Jun 22 19:07 mysqld_multi.server
-rw-r--r-- 1 7161 31415  2027 Jun 22 20:56 mysql-log-rotate
-rwxrwxr-x 1 root root  10735 Aug 12 18:27 mysql.server
-rwxr-xr-x 1 7161 31415 10576 Jun 22 20:56 mysql.server.bak
[root@itpuxdb support-files]# 

#替换参数文件
[root@itpuxdb 3306]# ll
total 12
drwxr-xr-x 6 mysql mysql 4096 Aug 12 18:29 data
-rw-r--r-- 1 root  root  3678 Feb  1  2021 my.cnf
-rwxrwxrwx 1 root  root    88 Jan 31  2021 mysql.start
[root@itpuxdb 3306]# pwd
/mysql/data/3306
[root@itpuxdb 3306]# mv my.cnf my.cnf.bak
[root@itpuxdb 3306]# cp /mysql/app/my.cnf /mysql/data/3306/my.cnf
[root@itpuxdb 3306]# ll
total 16
drwxr-xr-x 6 mysql mysql 4096 Aug 12 18:29 data
-rw-r--r-- 1 root  root  3911 Aug 12 18:31 my.cnf
-rw-r--r-- 1 root  root  3678 Feb  1  2021 my.cnf.bak
-rwxrwxrwx 1 root  root    88 Jan 31  2021 mysql.start
[root@itpuxdb 3306]#

适合MySQL 8.0的参数

[client]
port=3306
socket = /mysql/data/3306/mysql.sock

[mysql]
no-beep
prompt="\u@mysqldb \R:\m:\s [\d]> "
#no-auto-rehash
auto-rehash
default-character-set=utf8mb4

[mysqld]
########basic settings########
server-id=3306
port=3306
user = mysql
bind_address= 192.168.1.51
basedir=/mysql/app/mysql
datadir=/mysql/data/3306/data
socket = /mysql/data/3306/mysql.sock
pid-file = /mysql/data/3306/mysql.pid
character-set-server=utf8mb4
autocommit = 0
#skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
default-storage-engine=INNODB
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
sort_buffer_size = 32M
join_buffer_size = 128M
tmp_table_size = 72M
max_allowed_packet = 16M
#sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16M
read_rnd_buffer_size = 32M

#query_cache_type = 1
#query_cache_size=1M
table_open_cache=2000
thread_cache_size=768
myisam_max_sort_file_size=10G
myisam_sort_buffer_size=135M
key_buffer_size=32M
read_buffer_size=8M
read_rnd_buffer_size=4M

back_log=1024
#flush_time=0
open_files_limit=65536
table_definition_cache=1400
#binlog_row_event_max_size=8K
#sync_master_info=10000
#sync_relay_log=10000
#sync_relay_log_info=10000

########log settings########
log-output=FILE
general_log = 0 
general_log_file=/mysql/log/3306/itpuxdb-general.err
slow_query_log = ON
slow_query_log_file=/mysql/log/3306/itpuxdb-query.err
long_query_time=10
log-error=/mysql/log/3306/itpuxdb-error.err

log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
#expire_logs_days = 90
#binlog_expire_logs_seconds=2592000
binlog_expire_logs_seconds=604800
min_examined_row_limit = 100

log_bin=/mysql/log/3306/binlog/itpuxdb-binlog
log_bin_index=/mysql/log/3306/binlog/itpuxdb-binlog.index
binlog_format='ROW'
binlog_rows_query_log_events=on

########replication settings########
#master_info_repository = TABLE
#relay_log_info_repository = TABLE
#log_bin = bin.log
#sync_binlog = 1
#gtid_mode = on
#enforce_gtid_consistency = 1
#log_slave_updates
#binlog_format = row 
#relay_log = relay.log
#relay_log_recovery = 1
#binlog_gtid_simple_recovery = 1
#slave_skip_errors = ddl_exist_errors

########innodb settings########
#根据您的服务器IOPS能力适当调整
#一般配置普通SSD盘的话，可以调整到10000 - 20000
#配置高端的PCIe SSD卡的话,可以调整到50000 - 80000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_buffer_pool_size = 500M
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 5
#innodb_flush_method = O_DIRECT

innodb_log_file_size = 200M
innodb_log_files_in_group = 2 
innodb_log_buffer_size = 16M

#innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G

innodb_flush_neighbors = 1
innodb_purge_threads = 4
#innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 64M
innodb_flush_log_at_trx_commit=1
innodb_autoextend_increment=64
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=65536
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
innodb_data_file_path=ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:5G
innodb_temp_data_file_path = ibtmp1:200M:autoextend:max:20G

innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
#transaction_write_set_extraction=MURMUR32
default_authentication_plugin=mysql_native_password
#default_authentication_plugin=caching_sha2_password

mysql.server修改

[root@itpuxdb support-files]# vim mysql.server
#修改
46 basedir=
47 datadir=
#改为
46 basedir=/mysql/app/mysql
47 datadir=/mysql/data/3306/data

#修改
63 mysqld_pid_file_path=
#改为
63 mysqld_pid_file_path=/mysql/data/3306/mysql.pid

#修改
 64 if test -z "$basedir"
 65 then
 66   basedir=/usr/local/mysql
 67   bindir=/usr/local/mysql/bin
 68   if test -z "$datadir"
 69   then
 70     datadir=/usr/local/mysql/data
 71   fi
 72   sbindir=/usr/local/mysql/bin
 73   libexecdir=/usr/local/mysql/bin
 74 else
 75   bindir="$basedir/bin"
 76   if test -z "$datadir"
 77   then
 78     datadir="$basedir/data"
 79   fi
 80   sbindir="$basedir/sbin"
 81   libexecdir="$basedir/libexec"
 82 fi
#将如上代码块中涉及到的/usr/local相关目录根据我们的规划进行修改

 64 if test -z "$basedir"
 65 then
 66   basedir=/mysql/app/mysql
 67   bindir=/mysql/app/mysql/bin
 68   if test -z "$datadir"
 69   then
 70     datadir=/mysql/data/3306/data
 71   fi
 72   sbindir=/mysql/app/mysql/bin
 73   libexecdir=/mysql/app/mysql/bin
 74 else
 75   bindir="$basedir/bin"
 76   if test -z "$datadir"
 77   then
 78     datadir="$basedir/data"
 79   fi
 80   sbindir="$basedir/sbin"
 81   libexecdir="$basedir/libexec"
 82 fi



因为默认是去读的/etc/my.cnf，启动这个复制出来的mysql文件还是不能启动mysql服务，规范的情况下我们的配置文件不往系统里放（不放在/etc目录下，因为一般OS安装在一块单独的磁盘），不方便管理不方便备份，系统一旦坏了，文件就没了。
#修改
266       $bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null &
加个参数：指向我们建立的my.cnf文件
改为：
266       $bindir/mysqld_safe --defaults-file=/mysql/data/3306/my.cnf --datadir="$datadir" --pid-file="$mysql    d_pid_file_path" $other_args >/dev/null &

5、修改权限启动MySQL

#修改权限
chown -R mysql:mysql /mysql

#尝试启动MySQL
#启动数据库服务：
[root@itpuxdb 3306]# service mysql start
Starting MySQL.The server quit without updating PID file (/[失败]data/3306/mysql.pid).

#查看报错信息
[root@itpuxdb 3306]# tail -100f /mysql/log/3306/itpuxdb-error.err
2023-08-12T18:33:40.069204+08:00 0 [Warning] [MY-011068] [Server] The syntax 'log_slow_slave_statements' is deprecated and will be removed in a future release. Please use log_slow_replica_statements instead.
2023-08-12T18:33:40.069230+08:00 0 [Warning] [MY-011070] [Server] 'binlog_format' is deprecated and will be removed in a future release.
2023-08-12T18:33:40.069361+08:00 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
2023-08-12T18:33:40.069389+08:00 0 [System] [MY-010116] [Server] /mysql/app/mysql/bin/mysqld (mysqld 8.0.34) starting as process 8142
mysqld: File '/mysql/log/3306/binlog/itpuxdb-binlog.index' not found (OS errno 2 - No such file or directory)
2023-08-12T18:33:40.072133+08:00 0 [ERROR] [MY-010119] [Server] Aborting
2023-08-12T18:33:40.072281+08:00 0 [System] [MY-010910] [Server] /mysql/app/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.34)  MySQL Community Server - GPL.

#如上提示
File '/mysql/log/3306/binlog/itpuxdb-binlog.index' not found (OS errno 2 - No such file or directory)

#创建对应目录
mkdir /mysql/log/3306/binlog
chown mysql:mysql /mysql/log/3306/binlog


#重新启动
service mysql start

#查看后台信息
[root@itpuxdb 3306]# tail -100f /mysql/log/3306/itpuxdb-error.err
2023-08-12T18:39:45.721519+08:00 0 [Warning] [MY-011068] [Server] The syntax 'log_slow_slave_statements' is deprecated and will be removed in a future release. Please use log_slow_replica_statements instead.
2023-08-12T18:39:45.721544+08:00 0 [Warning] [MY-011070] [Server] 'binlog_format' is deprecated and will be removed in a future release.
2023-08-12T18:39:45.721766+08:00 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
2023-08-12T18:39:45.721798+08:00 0 [System] [MY-010116] [Server] /mysql/app/mysql/bin/mysqld (mysqld 8.0.34) starting as process 10476
2023-08-12T18:39:45.742785+08:00 0 [Warning] [MY-013267] [InnoDB] The setting INNODB_UNDO_TABLESPACES is deprecated and is no longer used.  InnoDB always creates 2 undo tablespaces to start with. If you need more, please use CREATE UNDO TABLESPACE.
2023-08-12T18:39:45.742814+08:00 0 [Warning] [MY-013907] [InnoDB] Deprecated configuration parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute innodb_redo_log_capacity=419430400. Please use innodb_redo_log_capacity instead.
2023-08-12T18:39:45.745048+08:00 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2023-08-12T18:39:45.745121+08:00 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-08-12T18:39:49.385103+08:00 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-08-12T18:39:52.810972+08:00 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2023-08-12T18:39:53.972450+08:00 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80034' started.
2023-08-12T18:40:01.393930+08:00 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80034' completed.
2023-08-12T18:40:02.907693+08:00 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2023-08-12T18:40:02.907945+08:00 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2023-08-12T18:40:03.433234+08:00 0 [System] [MY-010931] [Server] /mysql/app/mysql/bin/mysqld: ready for connections. Version: '8.0.34'  socket: '/mysql/data/3306/mysql.sock'  port: 3306  MySQL Community Server - GPL.
2023-08-12T18:40:03.433359+08:00 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock


#如上后台提醒版本升级成功了

6、升级数据字典（非必要）

#如下是当前mysql库里面的文件结构
[root@itpuxdb mysql]# pwd
/mysql/data/3306/data/mysql
[root@itpuxdb mysql]# ll
total 32
-rw-r----- 1 mysql mysql  5975 Aug 12 18:40 general_log_394.sdi
-rw-r----- 1 mysql mysql    35 Aug 12 18:40 general_log.CSM
-rw-r----- 1 mysql mysql     0 Aug 12 18:39 general_log.CSV
-rw-r----- 1 mysql mysql 12504 Aug 12 18:40 slow_log_396.sdi
-rw-r----- 1 mysql mysql    35 Aug 12 18:40 slow_log.CSM
-rw-r----- 1 mysql mysql     0 Jan 31  2021 slow_log.CSV
[root@itpuxdb mysql]# 


#升级数据字典
[root@itpuxdb mysql]# mysql_upgrade -uroot -p -S /mysql/data/3306/mysql.sock
The mysql_upgrade client is now deprecated. The actions executed by the upgrade client are now done by the server.
To upgrade, please start the new MySQL binary with the older data directory. Repairing user tables is done automatically. Restart is not required after upgrade.
The upgrade process automatically starts on running a new MySQL binary with an older data directory. To avoid accidental upgrades, please use the --upgrade=NONE option with the MySQL binary. The option --upgrade=FORCE is also provided to run the server upgrade sequence on demand.
It may be possible that the server upgrade fails due to a number of reasons. In that case, the upgrade sequence will run again during the next MySQL server start. If the server upgrade fails repeatedly, the server can be started with the --upgrade=MINIMAL option to start the server without executing the upgrade sequence, thus allowing users to manually rectify the problem.
[root@itpuxdb mysql]# 

#如上提示从第5步骤的后台日志可以看出升级已经完成
#此处的提示告诉我们升级是自动完成的

mysql_upgrade客户端现在已经被弃用。升级客户端执行的操作现在由服务器执行。要进行升级，请使用旧数据目录启动新的MySQL二进制文件。用户表的修复将自动完成。升级后不需要重新启动。

升级过程会在使用旧数据目录运行新的MySQL二进制文件时自动启动。为了避免意外升级，请在MySQL二进制文件中使用–upgrade=NONE选项。还提供了–upgrade=FORCE选项，以按需运行服务器升级序列。

由于多种原因，服务器升级可能会失败。在这种情况下，升级序列将在下次MySQL服务器启动时再次运行。如果服务器升级反复失败，可以使用–upgrade=MINIMAL选项启动服务器，以在不执行升级序列的情况下启动服务器，从而允许用户手动解决问题。

7、检查数据和版本信息

#升级后的版本
[root@itpuxdb mysql]# mysql --version
mysql  Ver 8.0.34 for Linux on x86_64 (MySQL Community Server - GPL)
[root@itpuxdb mysql]# mysql -uroot -prootroot -e "select @@version"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+
| @@version |
+-----------+
| 8.0.34    |
+-----------+
[root@itpuxdb mysql]# mysql -uroot -prootroot -e "select version()"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+
| version() |
+-----------+
| 8.0.34    |
+-----------+


#查看数据
[root@itpuxdb mysql]# mysql -uroot -prootroot  itpuxdb -e "show tables"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------------+
| Tables_in_itpuxdb |
+-------------------+
| bm                |
| dd                |
| dq                |
| gj                |
| gw                |
| itpux11           |
| itpux12           |
| itpux_m1          |
| itpux_m5          |
| itpux_obj         |
| itpux_sales       |
| itpux_yg          |
| jl                |
| yg                |
+-------------------+
[root@itpuxdb mysql]#

升级后中文字符乱码处理

#升级后发现表中的中文字符乱码
#对比两个版本的字符集设置
#原版本
mysql> show variables like 'collation_%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

mysql> show variables like '%character%';
+--------------------------+----------------------------------------------------------------+
| Variable_name            | Value                                                          |
+--------------------------+----------------------------------------------------------------+
| character_set_client     | utf8                                                           |
| character_set_connection | utf8                                                           |
| character_set_database   | utf8                                                           |
| character_set_filesystem | binary                                                         |
| character_set_results    | utf8                                                           |
| character_set_server     | utf8                                                           |
| character_set_system     | utf8                                                           |
| character_sets_dir       | /mysql/app/mysql-5.7.43-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------------------+
8 rows in set (0.00 sec)



#升级后的版本
mysql> show variables like 'collation_%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | latin1_swedish_ci  |
| collation_database   | utf8mb3_general_ci |
| collation_server     | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)
mysql> show variables like '%character%';
+--------------------------+----------------------------------------------------------------+
| Variable_name            | Value                                                          |
+--------------------------+----------------------------------------------------------------+
| character_set_client     | latin1                                                         |
| character_set_connection | latin1                                                         |
| character_set_database   | utf8mb3                                                        |
| character_set_filesystem | binary                                                         |
| character_set_results    | latin1                                                         |
| character_set_server     | utf8mb4                                                        |
| character_set_system     | utf8mb3                                                        |
| character_sets_dir       | /mysql/app/mysql-8.0.34-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------------------+
8 rows in set (0.00 sec)


#修改参数
#会话修改（立即生效）
SET character_set_client = utf8 ;  
SET character_set_connection = utf8 ;   
SET character_set_database = utf8 ;
set character_set_filesystem = 'utf8';
SET character_set_results = utf8 ;    
SET character_set_server = utf8 ;   
-- set character_set_system = 'utf8';
-- set character_sets_dir = 'utf8';
SET collation_connection = utf8_general_ci ;  
SET collation_database = utf8_general_ci ;   
SET collation_server = utf8_general_ci ; 

#修改my.cnf（重启生效）
[client]
default_character_set=utf8
[mysql]
default_character_set=utf8
[mysqld]
init_connect='SET collation_connection = utf8_general_ci'
init_connect='SET NAMES utf8'
character_set_server=utf8
collation_server=utf8_general_ci
skip_character_set_client_handshake

#修改后的结果
mysql> show variables like 'collation_%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb3_general_ci |
| collation_database   | utf8mb3_general_ci |
| collation_server     | utf8mb3_general_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)

mysql> show variables like '%character%';
+--------------------------+----------------------------------------------------------------+
| Variable_name            | Value                                                          |
+--------------------------+----------------------------------------------------------------+
| character_set_client     | utf8mb3                                                        |
| character_set_connection | utf8mb3                                                        |
| character_set_database   | utf8mb3                                                        |
| character_set_filesystem | binary                                                         |
| character_set_results    | utf8mb3                                                        |
| character_set_server     | utf8mb3                                                        |
| character_set_system     | utf8mb3                                                        |
| character_sets_dir       | /mysql/app/mysql-8.0.34-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------------------+
8 rows in set (0.01 sec)

mysql>

小版本升级

5.7.20 to 5.7.43