MySQL8.0就地升级到MySQL8.4.0的方法

--查询数据库中哪些线程正在执行
show processlist;

#查版本
mysql -v 
或
mysql> select version();
+------------+
| version()  |
+------------+
| 8.0.35     |
+------------+
1 row in set (0.00 sec)

--查询所有数据库的总大小
use information_schema;
select concat(round(sum(data_length/1024/1024),2),'mb') as data from tables;

--统计一下所有库数据量
select
sum(avg_row_length*table_rows+index_length)/1024/1024 as total_mb
from information_schema.tables; 

--统计每个库大小
select
table_schema,sum(avg_row_length*table_rows+index_length)/1024/1024 as total_mb
from information_schema.tables group by table_schema;  

use information_schema;
select concat(round(sum(data_length/1024/1024),2),'mb') as data from tables where table_schema='pijiake';

--查看所有数据库各容量大小
select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(mb)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(mb)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

--查看所有数据库各表容量大小
select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(mb)',
truncate(index_length/1024/1024, 2) as '索引容量(mb)'
from information_schema.tables
order by data_length desc, index_length desc;

--查看指定数据库容量大小(例：查看mysql库容量大小)
select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(mb)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(mb)'
from information_schema.tables
where table_schema='test';　

--查看指定数据库各表容量大小(例：查看mysql库各表容量大小)
select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(mb)',
truncate(index_length/1024/1024, 2) as '索引容量(mb)'
from information_schema.tables
where table_schema='test'
order by data_length desc, index_length desc;

#检查没有未提交的xa事务
mysql> xa recover;
empty set (0.00 sec)

# 确保数据都刷到硬盘上，更改成0
mysql -u root -p --execute="set global innodb_fast_shutdown=0"
或
mysql> show variables like 'innodb_fast_shutdown';
+----------------------+-------+
| variable_name        | value |
+----------------------+-------+
| innodb_fast_shutdown | 1     |
+----------------------+-------+
1 row in set (0.00 sec)

mysql> set global innodb_fast_shutdown=0;
query ok, 0 rows affected (0.00 sec)
mysql> show variables like 'innodb_fast_shutdown';
+----------------------+-------+
| variable_name        | value |
+----------------------+-------+
| innodb_fast_shutdown | 0     |
+----------------------+-------+

set global innodb_fast_shutdown = 1; -- fast shutdown
set global innodb_fast_shutdown = 0; -- slow shutdown

mysqlcheck -u root -p --all-databases --check-upgrade

select table_schema, table_name
    from information_schema.tables
    where engine not in ('innodb', 'ndbcluster')
        and create_options like '%partitioned%';

alter table table_name engine = innodb;

alter table table_name remove partitioning;

select table_schema, table_name
from information_schema.tables
where 
    lower(table_schema) = 'mysql'
    and 
    lower(table_name) in
    (
    'catalogs',
    'character_sets',
    'check_constraints',
    'collations',
    'column_statistics',
    'column_type_elements',
    'columns',
    'dd_properties',
    'events',
    'foreign_key_column_usage',
    'foreign_keys',
    'index_column_usage',
    'index_partitions',
    'index_stats',
    'indexes',
    'parameter_type_elements',
    'parameters',
    'resource_groups',
    'routines',
    'schemata',
    'st_spatial_reference_systems',
    'table_partition_values',
    'table_partitions',
    'table_stats',
    'tables',
    'tablespace_files',
    'tablespaces',
    'triggers',
    'view_routine_usage',
    'view_table_usage'
    );

select table_schema, table_name
from information_schema.tables
where table_name in
  (select left(substr(id,instr(id,'/')+1),
               instr(substr(id,instr(id,'/')+1),'_ibfk_')-1)
   from information_schema.innodb_sys_foreign
   where length(substr(id,instr(id,'/')+1))>64);

mysql> select table_name, if(sha(table_name) !=sha(lower(table_name)),'yes','no') as uppercase from information_schema.tables;

--多个数据库备份成1个文件
mysqldump -uroot -p  --databases  xiaomiaoao pijiake  > /root/all-databases2213.sql

--备份test数据库
mysqldump -uroot -p test > /data/mysqldb/tmp/test_exp_$(date +%f).sql 

--查看进程  确定使用的配置文件 安装目录
[root@localhost ~]# ps -ef | grep mysql
mysql      2232      1  0 13:10 ?        00:00:30 /mysqldb/mysql8/bin/mysqld --defaults-file=/mysqldb/my8.cnf

--确定安装目录和数据目录
[root@localhost ~]# cat /mysqldb/my8.cnf | grep dir
basedir=/mysqldb/mysql8  #mysql安装根目录
datadir=/mysqldb/data8 #mysql数据文件所在目录

--停止数据库
systemctl stop mysqld
或
mysql -uroot -p
shutdown;
或
mysqladmin -u root -p shutdown

[root@localhost ~]# ps -ef | grep mysql
无输出

--安装目录备份
cp -r /mysqldb/mysql8 /mysqldb/mysql8_bak_`date +%f`
--数据目录备份
cp -r /mysqldb/data8 /mysqldb/data8_bak_`date +%f`
--配置文件备份
cp /mysqldb/my8.cnf /mysqldb/my8.cnf_bak_`date +%f`

#1.安装介质上传至/opt/下
#2.创建目录
mkdir -p /data/mysqldb840
#3.解压安装包
cd /opt
tar -xvf mysql-8.4.0-linux-glibc2.17-x86_64.tar.xz  -c /data/mysqldb840
#4.文件夹重命名为mysql8 
mv /data/mysqldb840/mysql*  /data/mysqldb840/mysql840
#5.更改文件夹所属
chown -r mysql.mysql /data/mysqldb840/mysql840

vi /mysqldb/my8.cnf
#认证策略解决登录error 2059 (hy000): authentication plugin 'caching_sha2_password' cannot be loaded#
#default_authentication_plugin = mysql_native_password

#add by mysql8.4.0
mysql_native_password=on

--1.进入原5.7 mysql命令行 正确关闭数据库
法1：
mysqladmin -uroot -p shutdown

[root@mysql]# mysql -uroot -p
enter password: 
#使用mysql shell 命令util.checkforserverupgrade('root@127.0.0.1:3307', {"password":"xxxxx", "targetversion":"8.0.26", "configpath":"/etc/my3307.cnf"}) 检查升级到目标版本8.0.26，确认没有error级别的问题
errors:   0
warnings: 17
notices:  0

#检查没有未提交的xa事务
mysql> xa recover;
empty set (0.00 sec)

# 确保数据都刷到硬盘上，更改成0
mysql> show variables like 'innodb_fast_shutdown';
+----------------------+-------+
| variable_name        | value |
+----------------------+-------+
| innodb_fast_shutdown | 1     |
+----------------------+-------+
1 row in set (0.00 sec)

mysql> set global innodb_fast_shutdown=0;
query ok, 0 rows affected (0.00 sec)
mysql> show variables like 'innodb_fast_shutdown';
+----------------------+-------+
| variable_name        | value |
+----------------------+-------+
| innodb_fast_shutdown | 0     |
+----------------------+-------+

mysql> shutdown;
query ok, 0 rows affected (0.00 sec)

mysql> exit

#查看进程和端口后，已无输出记录，确保已停止
[root@localhost mysqldb8]# ps -ef | grep mysql
root       6330   6202  0 07:35 pts/0    00:00:00 mysql -uroot -p -p2213 test
root       6466   6202  0 09:18 pts/0    00:00:00 mysql -uroot -p
root       6522   6293  0 09:58 pts/1    00:00:00 mysql -uroot -p
root       6603   6551  0 10:14 pts/3    00:00:00 grep --color=auto mysql
[root@localhost mysqldb8]# ss -ntl | grep 2213

cd /data/mysqldb840/mysql840
bin/mysqld_safe --user=mysql --datadir=/mysqldb/data8 &

[root@localhost opt]# tail -300f /mysqldb/log8/mysqld_error.log
......
2024-05-08t16:35:02.692061+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=536870912. please use innodb_redo_log_capacity instead.
2024-05-08t16:35:02.695130+08:00 1 [system] [my-013576] [innodb] innodb initialization has started.
2024-05-08t16:35:03.457545+08:00 1 [system] [my-013577] [innodb] innodb initialization has ended.
2024-05-08t16:35:03.477878+08:00 1 [system] [my-011090] [server] data dictionary upgrading from version '80023' to '80300'.
2024-05-08t16:35:04.004278+08:00 1 [system] [my-013413] [server] data dictionary upgrade from version '80023' to '80300' completed.
2024-05-08t16:35:05.563366+08:00 4 [system] [my-013381] [server] server upgrade from '80035' to '80400' started.
2024-05-08t16:35:09.055973+08:00 4 [system] [my-013381] [server] server upgrade from '80035' to '80400' completed.
2024-05-08t16:35:09.194856+08:00 0 [warning] [my-010068] [server] ca certificate ca.pem is self signed.
2024-05-08t16:35:09.194920+08:00 0 [system] [my-013602] [server] channel mysql_main configured to support tls. encrypted connections are now supported for this channel.
2024-05-08t16:35:09.221684+08:00 0 [system] [my-010931] [server] /data/mysqldb840/mysql840/bin/mysqld: ready for connections. version: '8.4.0'  socket: '/mysqldb/mysql.sock'  port: 3306  mysql community server - gpl.
2024-05-08t16:35:09.228009+08:00 0 [system] [my-011323] [server] x plugin ready for connections. bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
.......

#更改环境变量
[root@localhost ~]# vi .bash_profile
...
mysql_data=/data/mysqldb/data
#mysql8.0弃用
#export path=$path://mysqldb/mysql8/bin
#export mysql_home=/mysqldb/mysql8
#add for mysql8.4.0
export mysql_home=/data/mysqldb840/mysql840
export path=$path:/data/mysqldb840/mysql840/bin

#生效环境变量
[root@localhost ~]# source .bash_profile

#查看版本
[root@localhost ~]# mysql -v
mysql  ver 8.4.0 for linux on x86_64 (mysql community server - gpl)
或
[root@localhost ~]# mysql -uroot -p
root@localhost :(none) 10:40:24>select version();
+-----------+
| version() |
+-----------+
| 8.4.0     |
+-----------+
1 row in set (0.00 sec)

root@localhost :(none) 10:40:30>exit

vi /mysqldb/my8.cnf
basedir=/mysqldb/mysql8  #mysql安装根目录
替换为
basedir=/data/mysqldb840/mysql840  #mysql安装根目录

可用下面的批量替换
:%s#/mysqldb/mysql8#/data/mysqldb840/mysql840#g

--配置mysql8系统服务
cp /usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/mysqld.service_bak_20240508

[root@localhost ~]# cat /usr/lib/systemd/system/mysqld.service
[unit]
description=mysql server
documentation=man:mysqld(8)
documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
after=network.target
after=syslog.target

[install]
wantedby=multi-user.target

[service]
user=mysql
group=mysql
execstart=/data/mysqldb840/mysql840/bin/mysqld --defaults-file=/data/mysqldb/my.cnf
limitnofile=65536  
limitnproc=65536

--关闭数据库
mysql -uroot -p
shutdown;

--通过系统服务重启数据库
systemctl start mysqld
systemctl status mysqld