mysql8.0.26 卸载、安装、初始化、启动、修改密码

转载为了学习

已于 2022-04-08 23:49:38 修改

阅读量1.4k

点赞数 1

分类专栏： Mysql 文章标签： mysql

于 2022-04-08 23:35:56 首次发布

本文链接：https://blog.csdn.net/lcz134258/article/details/124053483

版权

Mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

一、卸载mysql

如果是第一次安装mysql可以跳过，直接去看安装mysql。

1、关闭mysql服务

先查看一下mysql服务状态

systemctl status mysql

状态显示 active (running)，正在运行。

[root@VM-20-12-centos app]# systemctl status mysql
● mysql.service - MySQL Community Server
   Loaded: loaded (/usr/lib/systemd/system/mysql.service; disabled; vendor preset: disabled)
   Active: active (running) since Sun 2022-03-13 14:50:14 CST; 3 weeks 5 days ago
  Process: 1519602 ExecStop=/app/mysql-5.7.37/bin/mysqladmin shutdown -p L1changzhen_mysql (code=exited, status=0/SUCCESS)
 Main PID: 1519761 (mysqld)
    Tasks: 37 (limit: 23710)
   Memory: 249.7M
   CGroup: /system.slice/mysql.service
           └─1519761 /app/mysql-5.7.37/bin/mysqld --defaults-file=/etc/my.cnf --user=mysql

3月 13 14:50:14 VM-20-12-centos systemd[1]: mysql.service: Succeeded.
3月 13 14:50:14 VM-20-12-centos systemd[1]: Stopped MySQL Community Server.
3月 13 14:50:14 VM-20-12-centos systemd[1]: Started MySQL Community Server.

停止mysql服务

systemctl stop mysql

停止之后查看一下状态 failed

[root@VM-20-12-centos app]# systemctl status mysql
● mysql.service - MySQL Community Server
   Loaded: loaded (/usr/lib/systemd/system/mysql.service; disabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Fri 2022-04-08 20:08:55 CST; 7s ago
  Process: 1356143 ExecStop=/app/mysql-5.7.37/bin/mysqladmin shutdown -p L1changzhen_mysql (code=exited, status=1/FAILURE)
  Process: 1519761 ExecStart=/app/mysql-5.7.37/bin/mysqld --defaults-file=/etc/my.cnf --user=mysql (code=exited, status=0/SUCCESS)
 Main PID: 1519761 (code=exited, status=0/SUCCESS)

3月 13 14:50:14 VM-20-12-centos systemd[1]: mysql.service: Succeeded.
3月 13 14:50:14 VM-20-12-centos systemd[1]: Stopped MySQL Community Server.
3月 13 14:50:14 VM-20-12-centos systemd[1]: Started MySQL Community Server.
4月 08 20:08:51 VM-20-12-centos systemd[1]: Stopping MySQL Community Server...
4月 08 20:08:52 VM-20-12-centos systemd[1]: mysql.service: Control process exited, code=exited status=1
4月 08 20:08:55 VM-20-12-centos systemd[1]: mysql.service: Failed with result 'exit-code'.
4月 08 20:08:55 VM-20-12-centos systemd[1]: Stopped MySQL Community Server.

2、删除mysql相关的文件夹

查找一下mysql相关的文件夹

[root@VM-20-12-centos app]# find / -name mysql
/app/mysql-5.7.37/include/mysql
/app/mysql-5.7.37/bin/mysql
/data/mysql
/data/mysql/mysql
/var/lib/selinux/targeted/active/modules/100/mysql
/usr/bin/mysql
/usr/share/bash-completion/completions/mysql
/usr/share/selinux/targeted/default/active/modules/100/mysql

删除文件

rm -rf /app/mysql-5.7.37/include/mysql
rm -rf /app/mysql-5.7.37/bin/mysql
rm -rf /data/mysql
rm -rf /data/mysql/mysql
rm -rf /usr/bin/mysql

3、检查是否安装了mysql组件

[root@VM-20-12-centos app]# rpm -qa | grep -i mysql
mysql80-community-release-el8-3.noarch

有就删除

rpm -ev mysql80-community-release-el8-3.noarch

4、删除mysql用户及用户组

[root@VM-20-12-centos app]# userdel mysql
[root@VM-20-12-centos app]# groupdel mysql

二、linux安装mysql

1、下载安装包

官网地址: https://downloads.mysql.com/archives/community/

选择版本和系统，点击下载
在这里插入图片描述

2、上传安装包

上传到linux的指定目录下，我的安装目录是/app

[root@VM-20-12-centos app]# pwd
/app

3、解压缩文件

[root@VM-20-12-centos app]# tar -xvf mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz 
mysql-8.0.26-linux-glibc2.12-x86_64/bin/
mysql-8.0.26-linux-glibc2.12-x86_64/bin/myisam_ftdump
mysql-8.0.26-linux-glibc2.12-x86_64/bin/myisamchk
mysql-8.0.26-linux-glibc2.12-x86_64/bin/myisamlog
mysql-8.0.26-linux-glibc2.12-x86_64/bin/myisampack
mysql-8.0.26-linux-glibc2.12-x86_64/bin/mysql
mysql-8.0.26-linux-glibc2.12-x86_64/bin/mysql_config_editor
mysql-8.0.26-linux-glibc2.12-x86_64/bin/mysql_migrate_keyring
mysql-8.0.26-linux-glibc2.12-x86_64/bin/mysql_secure_installation
mysql-8.0.26-linux-glibc2.12-x86_64/bin/mysql_ssl_rsa_setup
......

解压的东西有点多

4、重命名或者移动

一般情况下是放在/usr/local文件夹下,个人习惯安装到/app下。

移动文件夹并改名mysql-8.0.26

[root@VM-20-12-centos app]# mv mysql-8.0.26-linux-glibc2.12-x86_64 /app/mysql-8.0.26
[root@VM-20-12-centos app]# ll
总用量 893392
drwxr-xr-x 9 root root      4096 4月   8 21:44 mysql-8.0.26
-rw-r--r-- 1 root root 914806904 4月   8 21:41 mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz

5、创建用户组和用户

[root@VM-20-12-centos mysql-8.0.26]# groupadd mysql	#创建用户组
[root@VM-20-12-centos mysql-8.0.26]# useradd -g mysql mysql	#创建用户

6、修改mysql目录权限

先看一下现在是什么权限，7161一列是用户组，31415一列是用户

[root@VM-20-12-centos mysql-8.0.26]# cd /app/mysql-8.0.26/
[root@VM-20-12-centos mysql-8.0.26]# ll
总用量 308
drwxr-xr-x  2 7161 31415   4096 7月   1 2021 bin
drwxr-xr-x  2 root root    4096 4月   8 21:52 data
drwxr-xr-x  2 7161 31415   4096 7月   1 2021 docs
drwxr-xr-x  3 7161 31415   4096 7月   1 2021 include
drwxr-xr-x  6 7161 31415   4096 7月   1 2021 lib
-rw-r--r--  1 7161 31415 276551 7月   1 2021 LICENSE
drwxr-xr-x  4 7161 31415   4096 7月   1 2021 man
-rw-r--r--  1 7161 31415    666 7月   1 2021 README
drwxr-xr-x 28 7161 31415   4096 7月   1 2021 share
drwxr-xr-x  2 7161 31415   4096 7月   1 2021 support-files

修改mysql目录权限。chown -R mysql.mysql /app/mysql-8.0.26/

[root@VM-20-12-centos mysql-8.0.26]# chown -R mysql.mysql /app/mysql-8.0.26/
[root@VM-20-12-centos mysql-8.0.26]# ll
总用量 308
drwxr-xr-x  2 mysql mysql   4096 7月   1 2021 bin
drwxr-xr-x  2 mysql mysql   4096 4月   8 21:52 data
drwxr-xr-x  2 mysql mysql   4096 7月   1 2021 docs
drwxr-xr-x  3 mysql mysql   4096 7月   1 2021 include
drwxr-xr-x  6 mysql mysql   4096 7月   1 2021 lib
-rw-r--r--  1 mysql mysql 276551 7月   1 2021 LICENSE
drwxr-xr-x  4 mysql mysql   4096 7月   1 2021 man
-rw-r--r--  1 mysql mysql    666 7月   1 2021 README
drwxr-xr-x 28 mysql mysql   4096 7月   1 2021 share
drwxr-xr-x  2 mysql mysql   4096 7月   1 2021 support-files

7、初始化mysql

每个版本都不一样，8.0.26需要初始化mysql。命令如下:

./mysqld --defaults-file=/etc/my.cnf --basedir=/app/mysql-8.0.26 --datadir=/app/mysql-8.0.26/data --user=mysql --initialize-insecure

--defaults-file：指定配置文件（要放在–initialize 前面）
--user：指定用户
--basedir：指定安装目录
--datadir：指定初始化数据目录
--intialize-insecure：初始化无密码

执行过初始化命令后。去mysql根目录下找到mysql.log，查看初始化密码。

2022-04-08T14:56:19.598847Z 0 [System] [MY-013169] [Server] /app/mysql-8.0.26/bin/mysqld (mysqld 8.0.26) initializing of server in progress as process 1684526
2022-04-08T14:56:19.641493Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-04-08T14:56:24.045216Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-04-08T14:56:26.435689Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2022-04-08T14:56:26.436099Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2022-04-08T14:56:26.708381Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.# 这一行说root用户在localhost环境下登录不需要密码，密码是空的。

8、编辑配置文件my.cnf

vim /etc/my.cnf #编辑配置文件

在配置文件中填写下面内容

[mysql]
# 默认字符集
default-character-set=utf8mb4
[client]
# 客户端端口，不指定端口时，默认以这个端口去请求，不再是3306
port       = 33306
# 客户端socket文件，和服务端位置一致
socket     = /app/mysql-8.0.26/data/mysql.sock

[mysqld]
# 允许哪些ip访问，0.0.0.0允许所有
bind-address=0.0.0.0
# 端口
port       = 33306
# 服务id，唯一id
server-id  = 33306
# 启动用户
user       = mysql
# 服务端socket文件
socket     = /app/mysql-8.0.26/data/mysql.sock
# 安装目录
basedir    = /app/mysql-8.0.26
# 数据存放目录
datadir    = /app/mysql-8.0.26/data
log-bin    = /app/mysql-8.0.26/log-bin
innodb_data_home_dir      =/app/mysql-8.0.26/data
innodb_log_group_home_dir =/app/mysql-8.0.26/data
#日志及进程数据的存放目录
log-error =/app/mysql-8.0.26/mysql.log
pid-file  =/app/mysql-8.0.26/mysql.pid
# 服务端使用的字符集默认为8比特编码
character-set-server=utf8mb4
lower_case_table_names=1
 
##################以上要修改的########################
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 1024
sort_buffer_size = 4M
net_buffer_length = 8K
read_buffer_size = 4M
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 64M
thread_cache_size = 128
 
#query_cache_size = 128M
tmp_table_size = 128M
explicit_defaults_for_timestamp = true
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535
 
binlog_format=mixed
 
binlog_expire_logs_seconds =864000
 
# 创建新表时将使用的默认存储引擎
default_storage_engine = InnoDB
innodb_data_file_path = ibdata1:10M:autoextend
innodb_buffer_pool_size = 1024M
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
transaction-isolation=READ-COMMITTED
 
[mysqldump]
quick
max_allowed_packet = 16M
 
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 4M
read_buffer = 2M
write_buffer = 2M
 
[mysqlhotcopy]
interactive-timeout

9、配置systemctl启动服务(centos7)

想要通过systemctl start mysql命令去启动mysql，前提linux系统必须是centos7及以上版本。

第一步、在**/usr/lib/systemd/system下创建一个文件mysql.service**

vim /usr/lib/systemd/system/mysql.service

第二步、然后输入下面内容。ctrl+c 输入**:wq** 保存并退出

[Unit]
Description=MySQL Community Server
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target
Alias=mysql.service

[Service]
# 启动服务的用户和用户组
User=mysql
Group=mysql

# 启动命令 mysql的位置、启动默认配置文件、启动的用户
ExecStart=/app/mysql-8.0.26/bin/mysqld --defaults-file=/etc/my.cnf --user=mysql

# 停止命令 mysqladmin位置、-p后面是数据库密码
ExecStop=/app/mysql-8.0.26/bin/mysqladmin shutdown -p L1changzhen_mysql

#根据自己配置，编写关闭命令
LimitNOFILE = 5000

第三步、执行systemctl daemon-reload刷新systemctl配置

[root@VM-20-12-centos system]# systemctl daemon-reload

第四步、通过systemctl start mysql命令启动

[root@VM-20-12-centos system]# systemctl start mysql

第五步、查看mysql状态

[root@VM-20-12-centos system]# systemctl status mysql
● mysql.service - MySQL Community Server
   Loaded: loaded (/usr/lib/systemd/system/mysql.service; disabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Fri 2022-04-08 22:44:10 CST; 47s ago
  Process: 1660629 ExecStart=/app/mysql-8.0.26/bin/mysqld --defaults-file=/etc/my.cnf --user=mysql (code=exited, status=1/FAILURE)
 Main PID: 1660629 (code=exited, status=1/FAILURE)

4月 08 22:44:09 VM-20-12-centos systemd[1]: Started MySQL Community Server.
4月 08 22:44:10 VM-20-12-centos systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE
4月 08 22:44:10 VM-20-12-centos systemd[1]: mysql.service: Failed with result 'exit-code'

10、启动mysql服务

启动mysql，查看一下mysql状态

systemctl start mysql 启动mysql命令
systemctl status mysql 查看mysql状态
ps -ef | grep mysql 查看mysql进程

[root@VM-20-12-centos bin]# systemctl start mysql
[root@VM-20-12-centos bin]# systemctl status mysql
● mysql.service - MySQL Community Server
   Loaded: loaded (/usr/lib/systemd/system/mysql.service; disabled; vendor preset: disabled)
   Active: active (running) since Fri 2022-04-08 23:05:52 CST; 11s ago
 Main PID: 1703287 (mysqld)
    Tasks: 42 (limit: 23710)
   Memory: 474.2M
   CGroup: /system.slice/mysql.service
           └─1703287 /app/mysql-8.0.26/bin/mysqld --defaults-file=/etc/my.cnf --user=mysql

4月 08 23:05:52 VM-20-12-centos systemd[1]: Started MySQL Community Server.
[root@VM-20-12-centos bin]# ps -ef | grep mysql
mysql    1703287       1  5 23:05 ?        00:00:00 /app/mysql-8.0.26/bin/mysqld --defaults-file=/etc/my.cnf --user=mysql
root     1703947 1346428  0 23:06 pts/0    00:00:00 grep --color=auto mysql

11、连接mysql

连接之前把mysql命令做一个软链接，可以全局执行mysql。

[root@VM-20-12-centos bin]# ln -s /app/mysql-8.0.26/bin/mysql /usr/local/bin/mysql

由于初始化root用户是没有密码的，所以可以直接在任何目录执行mysql -u root

[root@VM-20-12-centos /]# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 206
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, 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>

12、修改密码

MySQL初始化后的root用户、新创建的用户，都需要设置第一次密码。

连接mysql之后，执行修改密码语句。

我将root账户localhost环境的密码修改为L1changzhen_mysql

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'L1changzhen_mysql';
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

\q退出mysql，重新登录，输入密码L1changzhen_mysql

[root@VM-20-12-centos /]# mysql -u root -p
Enter password: #这里输入 L1changzhen_mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 510
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, 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>

13、创建远程登录用户

语法如下:

# 创建用户
CREATE user 'root'@'%';
# 设置密码
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '密码';
# 授权用户所有权限
GRANT ALL PRIVILEGES ON *.* TO "root"@"%";
# 刷新权限
FLUSH PRIVILEGES;

创建用户并赋予所有权限

mysql> CREATE user 'root'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'L1changzhen_mysql';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO "root"@"%";
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

这个时候使用db工具就可以连接了。

四、窗口函数

窗口函数式mysql8.0才有的，一定要先确认自己的环境是8.0

1.1 使用窗口函数前后的对比

假设我现在有一个数据表，它显示每个网站在每个城市每个区的销售额。

#创建表
create table sales (
id int primary key auto_increment,
city VARCHAR(15),
county VARCHAR(15),
sales_value decimal
);
# 向表中插入数据
insert into sales (city,county,sales_value)
values
('北京','海淀',10.00),
('北京','朝阳',20.00),
('上海','黄埔',30.00),
('上海','长宁',10.00);

查询一下看看结构：

mysql> select * from sales;
+----+--------+--------+-------------+
| id | city   | county | sales_value |
+----+--------+--------+-------------+
|  1 | 北京   | 海淀   |          10 |
|  2 | 北京   | 朝阳   |          20 |
|  3 | 上海   | 黄埔   |          30 |
|  4 | 上海   | 长宁   |          10 |
+----+--------+--------+-------------+
4 rows in set (0.00 sec)

现在有一个需求：计算每个城市每个区的销售总额、在全国的销售总额、每个区的销售额占所在城市销售额中的比率以及占总销售额的比率。

如果用分组和聚合函数，就需要分好几步来计算。

第一步，计算总销售金额，并存入临时表a：

create temporary table a
select sum(sales_value) as sales_value
from sales;

查看一下临时表a:

mysql> select * from a;
+-------------+
| sales_value |
+-------------+
|          70 |
+-------------+
1 row in set (0.00 sec)

第二步，计算每个城市的销售总额，并存储临时表b：

create temporary table b
select city,sum(sales_value) as sales_value
from sales
group by city;

查看一下临时表b：

mysql> select * from b;
+--------+-------------+
| city   | sales_value |
+--------+-------------+
| 上海   |          40 |
| 北京   |          30 |
+--------+-------------+
2 rows in set (0.00 sec)

第三步，计算各区销售额所占城市总销售额的比例以及占全部销售总额的比例。

select s.city as 城市,s.county as 区,s.sales_value as 区销售额,b.sales_value as 市销售额,s.sales_value/b.sales_value as 市比率,s.sales_value/a.sales_value as 总比率
from sales s join b on b.city = s.city
join a 
order by s.city,s.county;

最终显示的结果为：

+--------+--------+--------------+--------------+-----------+-----------+
| 城市   | 区     | 区销售额     | 市销售额     | 市比率    | 总比率    |
+--------+--------+--------------+--------------+-----------+-----------+
| 上海   | 长宁   |           10 |           40 |    0.2500 |    0.1429 |
| 上海   | 黄埔   |           30 |           40 |    0.7500 |    0.4286 |
| 北京   | 朝阳   |           20 |           30 |    0.6667 |    0.2857 |
| 北京   | 海淀   |           10 |           30 |    0.3333 |    0.1429 |
+--------+--------+--------------+--------------+-----------+-----------+
4 rows in set (0.00 sec)

以上方式使用了**临时表**的方式，性能不高，占用空间。

下面使用**窗口函数**的形式去实现同样的效果。

select city as 城市,county as 区,sales_value as 区销售额,
sum(sales_value) OVER(PARTITION by city) as 市销售额,
sales_value/sum(sales_value) OVER(PARTITION by city) as 市比率,
sum(sales_value) OVER() as 总销售额,
sales_value/sum(sales_value) OVER() as 总比率
from sales
order by city,county;

这里需要安装mysql8.0，我的是5.7，进行不下去了。