02-----Centos upgrade database version enhanced version (it is recommended to use this method for Centos upgrade database version)

Since the previous article talked about downloading two databases at the same time in order to achieve the problem of updating the database version, but thinking that subsequent deletion is inconvenient, it is better to back up the data in a unified manner, then uninstall the existing database, and then install the new version of the database.

Follow the above ideas and start practicing.
Refer to the article, download two Mysql versions for your own article 01-----Centos (the scene is to upgrade the database version)

One Centos upgrade database version enhanced version

1 Back up data first

Use Navicat to log in to the existing database version. Back up each database by right-clicking on it. Note that the backup location is saved on this computer, not on the Centos computer.

Insert picture description here

Then, record the properties of each database, and also right-click each database to ensure the same when creating a new database. Of course, you can choose the default when creating a new database, but I haven't tried it and I am not sure whether it will affect the follow-up.

Insert picture description here

2 Download mysql8.0.24

After the above backup is completed, we can download the high version database first.
MYSQL official website .

# 创建mysql安装目录
mkdir /usr/local/mysql

# 进入mysql安装目录
cd /usr/local/mysql/

# 下载mysql-8.0.24,官网选择Linux-Generic类型的操作系统
wget -c https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.24-linux-glibc2.12-x86_64.tar.xz

# 解压
tar -xvf mysql-8.0.24-linux-glibc2.12-x86_64.tar.xz

# 重命名解压出来的文件夹名
mv mysql-8.0.24-linux-glibc2.12-x86_64 mysql-8.0.24

# 创建数据存储目录
mkdir mysql-8.0.24/data

3 Uninstall the original database version

#1 先把已有数据库服务停止
service mysqld stop

#2 先查看已有的数据库依赖
rpm -qa | grep -i mysql

# 2 卸载依赖,注意,必须按照顺序,否则会报错
rpm -e mysql57-community-release-el7-8.noarch
rpm -e mysql-community-server-5.7.30-1.el7.x86_64
rpm -e mysql-community-libs-5.7.30-1.el7.x86_64
rpm -e mysql-community-libs-compat-5.7.30-1.el7.x86_64
rpm -e mysql-community-common-5.7.30-1.el7.x86_64
rpm -e mysql-community-client-5.7.30-1.el7.x86_64
Insert picture description here

When there is a problem with the uninstall sequence, an error is reported.

Insert picture description here

The solution is:

yum remove mysql-libs
Insert picture description here

Then after executing the above, you need to check the dependencies again, if there are still, delete them according to the above. After I execute the above, there is only one dependency left, so I only need to delete it with rpm -e.

rpm -qa | grep -i mysql

rpm -e mysql-community-common-5.7.30-1.el7.x86_64

rpm -qa | grep -i mysql
Insert picture description here

Then delete the directory.

cd /var/lib/  
rm -rf mysql/
whereis mysql

rm -rf /usr/lib64/mysql/
rm -rf /usr/share/mysql/
Insert picture description here

Deleting the configuration is actually doing a backup to prevent problems.

mv /etc/my.cnf /etc/my.cnf.back

Last check, I don't have it here, so there is no extra operation.

chkconfig --list | grep -i mysql
chkconfig --del mysqld

4 Create user groups and users and authorize

At this point, it is actually basically the same as the previous one.
There is no need to deal with this step with a mysql user.

#1 先查看是否有mysql这个用户,如果有就不需要再创建,没有就需创建
cat /etc/passwd | grep mysql
cat /etc/group | grep mysql

#2 参加mysql用户
# 创建用户组
groupadd mysql
# 创建用户
useradd -g mysql mysql

# 授权用户
chown -R mysql.mysql /usr/local/mysql/mysql-8.0.24

5 Initialize the database

# 初始化数据库,不区分大小写--lower-case-table-names=1需在初始化时设置才生效
/usr/local/mysql/mysql-8.0.24/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/mysql-8.0.24 --datadir=/usr/local/mysql/mysql-8.0.24/data --lower-case-table-names=1

During initialization, we need to remember this temporary password, which will be used later.

Insert picture description here

6 Write my.cnf configuration file

vim /etc/my.cnf
# 设置3307端口,避免与已有数据库重复。
# 设置mysql的安装目录
# 设置mysql数据库的数据的存放目录
# 允许最大连接数
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
# 服务端使用的字符集默认为UTF8
# 创建新表时将使用的默认存储引擎
# 默认使用“mysql_native_password”插件认证
# 是否对sql语句大小写敏感,1表示不敏感
# MySQL连接闲置超过一定时间后(单位:秒)将会被强行关闭
# MySQL默认的wait_timeout  值为8个小时, interactive_timeout参数需要同时配置才能生效
# Metadata Lock最大时长(秒),一般用于控制alter操作的最大时长sine mysql5.6
# 执行 DML操作时除了增加innodb事务锁外还增加Metadata Lock,其他alter(DDL)session将阻塞
# 内部内存临时表的最大值
# 比如大数据量的group by ,order by时可能用到临时表
# 超过了这个值将写入磁盘,系统IO压力增大
# 设置mysql客户端连接服务端时默认使用的端口

7 Configure as a service and start automatically after booting

# 进入主目录
cd mysql-8.0.24/
# 添加mysqld服务到系统
cp -a ./support-files/mysql.server /etc/init.d/mysql
# 授权以及添加服务
chmod +x /etc/init.d/mysql
chkconfig --add mysql
# 检查是否生效
chkconfig --list mysql

The content shown in the figure below shows that the configuration takes effect:

Insert picture description here

8 Configure environment variables

vim /etc/profile

Add the following at the end of /etc/profile:

export MYSQL_HOME=/usr/local/mysql/mysql-8.0.24
Insert picture description here
source /etc/profile

9 Start the mysql service

# 启动服务
service mysql start
# 查看服务状态
service mysql status
# 停止服务
service mysql stop
# 重启服务
service mysql restart

The startup success is as follows, it is recommended to use start, I want to see the error situation by myself:

Insert picture description here

10 Log in to mysql

mysql -u root -p

Enter the temporary password during initialization above.

11 Modify the root user password

-- 修改root用户密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

-- 刷新权限
flush privileges;

12 Set up external network connection authorization

Execute the following statement to allow all ip access to the root user:

-- 进入数据库mysql
use mysql;

-- 修改root用户host,%表示允许所有ip访问root用户
update user set host='%' where user='root';

-- 刷新权限
flush privileges;


13 Login to Navicat

Insert picture description here

The picture above is a screenshot of the backup data I have restored.

Except for rgvs and vss, the other four databases are all built-in libraries of the system, so I don't need to restore them, but it is necessary to back them up.

14 Restore the backed up database

First create the database according to the pre-saved database attributes, or you can use the default, but you are not sure whether it will affect the original business, it is recommended to use the original attributes first.

Insert picture description here

Then right-click the database and execute the sql file. If it is successfully executed, you can see words such as sucessfully.

Insert picture description here

Log in to mysql again on xshell and execute the corresponding command. If it is correct, it basically proves that the upgrade is successful.

mysql -u root -p
select version();
use vss;
show tables;
Insert picture description here