#下載包
[root@srebro.cn ~]# wget https://downloads.mysql.com/archives/get/p/43/file/mysql-shell-8.0.34-linux-glibc2.12-x86-64bit.tar.gz -C /root 

[root@srebro.cn ~]# tar -xf mysql-shell-8.0.34-linux-glibc2.12-x86-64bit.tar.gz 

[root@srebro.cn ~]# cd /root/mysql-shell-8.0.34-linux-glibc2.12-x86-64bit/bin 


[root@srebro.cn bin]# ./mysqlsh -uroot -p -S /tmp/mysql.sock -e "util.checkForServerUpgrade()" > util.checkForServerUpgrade.log

The MySQL server at /tmp%2Fmysql.sock, version 5.7.39-log - MySQL Community 
Server (GPL), will now be checked for compatibility issues for upgrade to MySQL 
8.0.34... 

1) Usage of old temporal type 
 No issues found 

2) MySQL 8.0 syntax check for routine-like objects 
 No issues found 

3) Usage of db objects with names conflicting with new reserved keywords 
 No issues found 

4) Usage of utf8mb3 charset 
 Warning: The following objects use the utf8mb3 character set. It is 
 recommended to convert them to use utf8mb4 instead, for improved Unicode 
 support. 
 More information: 
 https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html 

 mysql - schema's default character set: utf8 
 test - schema's default character set: utf8 

5) Table names in the mysql schema conflicting with new tables in 8.0 
 No issues found 

6) Partitioned tables using engines with non native partitioning 
 No issues found 

7) Foreign key constraint names longer than 64 characters 
 No issues found 

8) Usage of obsolete MAXDB sql_mode flag 
 No issues found 

9) Usage of obsolete sql_mode flags 
 Notice: The following DB objects have obsolete options persisted for 
 sql_mode, which will be cleared during upgrade to 8.0. 
 More information: 
 https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals 

 global system variable sql_mode - defined using obsolete NO_AUTO_CREATE_USER 
 option 

10) ENUM/SET column definitions containing elements longer than 255 characters 
 No issues found 

11) Usage of partitioned tables in shared tablespaces 
 No issues found 

12) Circular directory references in tablespace data file paths 
 No issues found 

13) Usage of removed functions 
 No issues found 

14) Usage of removed GROUP BY ASC/DESC syntax 
 No issues found 

15) Removed system variables for error logging to the system log configuration 
 To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary 
 More information: 
 https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html#mysqld-8-0-13-logging 

16) Removed system variables 
 To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary 
 More information: 
 https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed 

17) System variables with new default values 
 To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary 
 More information: 
 https://mysqlserverteam.com/new-defaults-in-mysql-8-0/ 

18) Zero Date, Datetime, and Timestamp values 
 No issues found 


19) Schema inconsistencies resulting from file removal or corruption 
 No issues found 

20) Tables recognized by InnoDB that belong to a different engine 
 No issues found 

21) Issues reported by 'check table x for upgrade' command 
 No issues found 

22) New default authentication plugin considerations 
 Warning: The new default authentication plugin 'caching_sha2_password' offers 
 more secure password hashing than previously used 'mysql_native_password' 
 (and consequent improved client connection authentication). However, it also 
 has compatibility implications that may affect existing MySQL installations. 
 If your MySQL installation must serve pre-8.0 clients and you encounter 
 compatibility issues after upgrading, the simplest way to address those 
 issues is to reconfigure the server to revert to the previous default 
 authentication plugin (mysql_native_password). For example, use these lines 
 in the server option file: 
 
 [mysqld] 
 default_authentication_plugin=mysql_native_password 
 
 However, the setting should be viewed as temporary, not as a long term or 
 permanent solution, because it causes new accounts created with the setting 
 in effect to forego the improved authentication security. 
 If you are using replication please take time to understand how the 
 authentication plugin changes may impact you. 
 More information: 
 https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatibility-issues 
 https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replication 

23) Columns which cannot have default values 
 No issues found 

24) Check for invalid table names and schema names used in 5.7 
 No issues found 

25) Check for orphaned routines in 5.7 
 No issues found 

26) Check for deprecated usage of single dollar signs in object names 
 No issues found 

27) Check for indexes that are too large to work on higher versions of MySQL 
Server than 5.7 
 No issues found 

28) Check for deprecated '.<table>' syntax used in routines. 
 No issues found 

Errors: 0 
Warnings: 3 
Notices: 1 

NOTE: No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading.

which mysqldump 
/home/application/mysql/app/bin/mysqldump 

# --routines 備份存儲過程和函數；--set-gtid-purged=OFF: 禁用GTID（全局事務標識）;xxx1,XXX2 表示庫名，備份多個庫用空格做為間隔
/home/application/mysql/app/bin/mysqldump -uroot -p --routines --set-gtid-purged=OFF --databases XXX1 XXX2 > /root/all-database-20231026.sql

# 進入原5.7 mysql命令行 正確關閉數據庫
[root@srebro.cn ~]# mysql -uroot -p'srebro' 
mysql> select version(); 
+------------+ 
| version() | 
+------------+ 
| 5.7.43-log | 
+------------+ 
1 row in set (0.00 sec) 

mysql> show variables like 'innodb_fast_shutdown'; 
+----------------------+-------+ 
| Variable_name | Value | 
+----------------------+-------+ 
| innodb_fast_shutdown | 1 | 
+----------------------+-------+ 
1 row in set (0.00 sec) 

# 確保數據都刷到硬盤上，更改成0

mysql> set global innodb_fast_shutdown=0;

Query OK, 0 rows affected (0.00 sec)







mysql> shutdown;

Query OK, 0 rows affected (0.00 sec)



mysql> exit

Bye

[root@cmdb ~]# ps -ef | grep mysql

root     30990 30934  0 16:12 pts/0    00:00:00 grep --color=auto mysql

--確認數據庫狀態為關閉狀態
[root@srebro.cn ~]# systemctl status mysqld 

--數據目錄備份
[root@srebro.cn ~]# cp -r /home/application/mysql/data /home/application/mysql/data_bak_`date +%F` 

--安裝目錄備份
[root@srebro.cn ~]# cp -r /home/application/mysql/app/ /home/application/mysql/app_bak_`date +%F` 


--配置文件備份
[root@srebro.cn ~]# cp /etc/my.cnf /etc/my.cnf_`date +%F`

#執行以下步驟解壓tar包：
# 安裝包上傳至原安裝包目錄下 我的是/home/application/mysql 

[root@srebro.cn ~]# cd /home/application/mysql 
[root@srebro.cn mysql]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz 

[root@srebro.cn mysql]# tar -xf mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz 

# 文件夾重命名為mysql8 
[root@srebro.cn mysql]# mv mysql-8.0.34-linux-glibc2.12-x86_64 mysql8 

# 更改文件夾所屬
[root@srebro.cn mysql]# chown -Rf mysql:mysql /home/application/mysql/mysql8 

# 刪除安裝包
[root@srebro.cn mysql]# rm -rf mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz

[mysql]
socket=/tmp/mysql.sock 
default-character-set=utf8 

[mysqld]
user=mysql 
basedir=/home/application/mysql/app 
datadir=/home/application/mysql/data 
character_set_server=utf8 
collation-server=utf8_general_ci 

#日志時間
log_timestamps=SYSTEM 

port=3306
socket=/tmp/mysql.sock 

max_connections=1000
max_allowed_packet=500M 
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 


#慢日志
long_query_time=3
slow_query_log=ON 
slow_query_log_file=/home/application/mysql/slow_query.log 
 
#錯誤日志
log-error=/home/application/mysql/mysql-error.log 



#binlog配置
server_id=150
log-bin=mysql-bin 
max_binlog_size = 100M 
binlog_format=row 
log_slave_updates 
expire_logs_days=7
#只能用IP地址檢查客戶端的登錄，不用主機名
skip-name-resolve=1

[mysql]
socket=/tmp/mysql.sock 
default-character-set=utf8 

[mysqld]
user=mysql 
#日志時間
log_timestamps=SYSTEM 
port=3306
socket=/tmp/mysql.sock 
max_connections=1000
max_allowed_packet=500M 
#只能用IP地址檢查客戶端的登錄，不用主機名
skip-name-resolve=1

#binlog配置
server_id=150
log-bin=mysql-bin 
max_binlog_size = 100M 
binlog_format=row 
log_slave_updates 
expire_logs_days=7
#慢日志
long_query_time=3
slow_query_log=ON 
slow_query_log_file=/home/application/mysql/slow_query.log 
 
#錯誤日志
log-error=/home/application/mysql/mysql-error.log 



#for8.0 
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION 
basedir=/home/application/mysql/mysql8 
datadir=/home/application/mysql/data 
character_set_server=utf8 
collation-server=utf8_general_ci 
# 默認使用"mysql_native_password"插件認證
default_authentication_plugin=mysql_native_password 
# 創建新表時將使用的默認存儲引擎
default-storage-engine=INNODB

[root@srebro.cn ~]# /home/application/mysql/mysql8/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --upgrade=FORCE

2023-10-30T07:33:23.595626Z mysqld_safe Logging to '/home/application/mysql/mysql-error.log'.

2023-10-30T07:33:23.620303Z mysqld_safe Starting mysqld daemon with databases from /home/application/mysql/data

[root@srebro.cn ~]# mysql -uroot -p'srebro' 
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g. 
Your MySQL connection id is 12 
Server version: 8.0.34 MySQL Community Server - GPL 

Copyright (c) 2000, 2022, Oracle and/or its affiliates. 

Oracle is a registered trademark of Oracle Corporation and/or its 
affiliates. Other names may be trademarks of their respective 
owners. 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 


mysql> select version(); 
+-----------+ 
| version() | 
+-----------+ 
| 8.0.34 | 
+-----------+ 
1 row in set (0.00 sec) 

mysql> 

[root@srebro.cn ~]# vim /etc/profile 
export PATH=$PATH:/home/application/mysql/mysql8/bin 
....... 


#使環境變量生效
[root@srebro.cn ~]# source /etc/profile 
#驗證下mysql環境變量
#退出當前終端
[root@srebro.cn ~]# exit 

[root@srebro.cn ~]# which mysql 
/home/application/mysql/mysql8/bin/mysql 
[root@srebro.cn ~]# mysql -V 
mysql Ver 8.0.34 for Linux on x86_64 (MySQL Community Server - GPL)

[root@cmdb ~]# kill -9 `ps -ef | grep mysql | awk '{print $2}'` 


#確認沒有mysql進程
[root@cmdb ~]# ps -ef | grep mysql 


#使用systemd管理mysql8 
#修改原先的ExecStart中，basedir的路徑，改為mysql8 的路徑


[root@cmdb ~]# vim /etc/systemd/system/mysqld.service 


[Unit] 
Descriptinotallow=MySQL Server 
Documentatinotallow=man:mysqld 
Documentatinotallow=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=/home/application/mysql/mysql8/bin/mysqld --defaults-file=/etc/my.cnf

#reload下systemd 
[root@cmdb ~]# systemctl daemon-reload 

#加入開機自啟動
[root@cmdb ~]# systemctl enable mysqld 
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /etc/systemd/system/mysqld.service. 
#啟動mysql8數據庫
[root@cmdb ~]# systemctl start mysqld 

[root@cmdb ~]# ps -ef | grep mysql 
mysql 9497 1 36 14:59 ? 00:00:01 /home/application/mysql/mysql8/bin/mysqld --defaults-file=/etc/my.cnf 
root 9544 8560 0 14:59 pts/0 00:00:00 grep --color=auto mysql 


#登錄數據庫驗證
[root@cmdb ~]# mysql -uroot -p'srebro' 
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g. 
Your MySQL connection id is 8 
Server version: 8.0.34 MySQL Community Server - GPL 

Copyright (c) 2000, 2023, Oracle and/or its affiliates. 

Oracle is a registered trademark of Oracle Corporation and/or its 
affiliates. Other names may be trademarks of their respective 
owners. 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 

mysql> select version(); 
+-----------+ 
| version() | 
+-----------+ 
| 8.0.34 | 
+-----------+ 
1 row in set (0.00 sec) 

mysql> show databases; 
+--------------------+ 
| Database | 
+--------------------+ 
| information_schema | 
| mysql | 
| performance_schema | 
| srebro | 
| sys | 
| test | 
+--------------------+ 
5 rows in set (0.00 sec) 

mysql>