RHCA 数据库第一天作业（RPM、通用二进制当时安装MySQL及修改密码）

1、CentOS7 RPM方式安装MySQL5.7

1）进入MySQL官网下载RPM安装包

2)复制链接并安装

3）解压包并查看

4）卸载mariadb相关软件包

5）安装包

6）启动

2、CentOS7 通用二进制方式安装MySQL5.7

1）下载包

2）创建组和用户

[root@localhost ~]# groupadd mysql -r -g 306
[root@localhost ~]# useradd -r（系统用户） -g（基本组） mysql -s /bin/false -u 306  mysql

3）解压包

[root@localhost ~]# tar xf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz -C /usr/local/

4）创建软连接

[root@localhost ~]# ln -sv /usr/local/mysql-5.7.37-linux-glibc2.12-x86_64/ /usr/local/mysql
‘/usr/local/mysql’ -> ‘/usr/local/mysql-5.7.37-linux-glibc2.12-x86_64/’

5）设置权限

[root@localhost ~]# ll /usr/local/mysql/
total 272
drwxr-xr-x  2 root root    4096 Mar 27 01:22 bin
drwxr-xr-x  2 root root      55 Mar 27 01:22 docs
drwxr-xr-x  3 root root    4096 Mar 27 01:22 include
drwxr-xr-x  5 root root     230 Mar 27 01:22 lib
-rw-r--r--  1 7161 31415 259253 Nov 30 04:09 LICENSE
drwxr-xr-x  4 root root      30 Mar 27 01:22 man
-rw-r--r--  1 7161 31415    566 Nov 30 04:09 README
drwxr-xr-x 28 root root    4096 Mar 27 01:22 share
drwxr-xr-x  2 root root      90 Mar 27 01:22 support-files
[root@localhost ~]# chown -R mysql.mysql /usr/local/mysql/*
[root@localhost ~]# ll /usr/local/mysql/
total 272
drwxr-xr-x  2 mysql mysql   4096 Mar 27 01:22 bin
drwxr-xr-x  2 mysql mysql     55 Mar 27 01:22 docs
drwxr-xr-x  3 mysql mysql   4096 Mar 27 01:22 include
drwxr-xr-x  5 mysql mysql    230 Mar 27 01:22 lib
-rw-r--r--  1 mysql mysql 259253 Nov 30 04:09 LICENSE
drwxr-xr-x  4 mysql mysql     30 Mar 27 01:22 man
-rw-r--r--  1 mysql mysql    566 Nov 30 04:09 README
drwxr-xr-x 28 mysql mysql   4096 Mar 27 01:22 share
drwxr-xr-x  2 mysql mysql     90 Mar 27 01:22 support-files

6）做初始化

[root@localhost ~]# vim /etc/profile.d/mysql.sh

[root@localhost ~]# source /etc/profile.d/mysql.sh(立即生效)

启动初始化

[root@localhost ~]# mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
2022-03-29T10:39:35.447191Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-03-29T10:39:35.694508Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-03-29T10:39:35.729468Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-03-29T10:39:35.799660Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 86f19196-af4c-11ec-bb79-000c296f2538.
2022-03-29T10:39:35.800422Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-03-29T10:39:36.019401Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-03-29T10:39:36.019416Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-03-29T10:39:36.020000Z 0 [Warning] CA certificate ca.pem is self signed.
2022-03-29T10:39:36.132972Z 1 [Note] A temporary password is generated for root@localhost: iwwlyG.o)7bf（临时密码）

查看保存数据的文件夹：

[root@localhost ~]# ll /usr/local/mysql/data/
total 110660
-rw-r----- 1 mysql mysql       56 Mar 28 21:56 auto.cnf
-rw------- 1 mysql mysql     1676 Mar 28 21:56 ca-key.pem
-rw-r--r-- 1 mysql mysql     1112 Mar 28 21:56 ca.pem
-rw-r--r-- 1 mysql mysql     1112 Mar 28 21:56 client-cert.pem
-rw------- 1 mysql mysql     1676 Mar 28 21:56 client-key.pem
-rw-r----- 1 mysql mysql      436 Mar 28 21:56 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Mar 28 21:56 ibdata1
-rw-r----- 1 mysql mysql 50331648 Mar 28 21:56 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Mar 28 21:56 ib_logfile1
drwxr-x--- 2 mysql mysql     4096 Mar 28 21:56 mysql
drwxr-x--- 2 mysql mysql     8192 Mar 28 21:56 performance_schema
-rw------- 1 mysql mysql     1680 Mar 28 21:56 private_key.pem
-rw-r--r-- 1 mysql mysql      452 Mar 28 21:56 public_key.pem
-rw-r--r-- 1 mysql mysql     1112 Mar 28 21:56 server-cert.pem
-rw------- 1 mysql mysql     1676 Mar 28 21:56 server-key.pem
drwxr-x--- 2 mysql mysql     8192 Mar 28 21:56 sys

7）为mysql提供主配置文件
拷贝模板文件
因为本机没有提供 \cp support-files/my-default.cnf
需要从RPM安装方式的虚拟机上拷贝并且改写文件，操作如下：
拷贝文件：

[root@localhost ~]# scp 192.168.116.132:/etc/my.cnf /etc
The authenticity of host '192.168.116.132 (192.168.116.132)' can't be established.
ECDSA key fingerprint is SHA256:yip5TKG9sxeopK5Uc+0GfRaFR8ae2tS9EZpInu6PNFM.
ECDSA key fingerprint is MD5:ca:e5:2f:3f:81:2b:0e:78:d1:da:6a:c8:b2:bf:fb:f7.
Are you sure you want to continue connecting (yes/no)? y
Please type 'yes' or 'no': yes
Warning: Permanently added '192.168.116.132' (ECDSA) to the list of known hosts.

修改文件：

[root@localhost ~]# vim /etc/my.cnf

修改内容为框中内容

8）、为mysql提供sysv服务脚本

[root@localhost ~]# cp /usr/local/mysql/support-files/mysql.server（脚本） /etc/init.d/mysqld

查看脚本是否有执行权限

[root@localhost ~]# ll /etc/init.d/mysqld
-rwxr-xr-x 1 root root 10576 Mar 29 19:16 /etc/init.d/mysqld

9）启动mysql

[root@localhost ~]#  chkconfig --add mysqld（添加为系统服务）
[root@localhost ~]# chkconfig mysqld on（设置开机自启动）
[root@localhost ~]# systemctl start mysqld（当前启动）

查看是否启动

[root@localhost ~]# systemctl status mysqld
● mysqld.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
   Active: active (running) since Tue 2022-03-29 19:25:02 CST; 1min 33s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 11344 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS)
   CGroup: /system.slice/mysqld.service
           ├─11354 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/mysqld...
           └─11508 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/m...

Mar 29 19:25:01 localhost.localdomain systemd[1]: Starting LSB: start and stop MySQL...
Mar 29 19:25:02 localhost.localdomain mysqld[11344]: Starting MySQL. SUCCESS!
Mar 29 19:25:02 localhost.localdomain systemd[1]: Started LSB: start and stop MySQL.

安装完成并尝试登录：

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.37

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> 

3、简述MySQL体系结构。

Mysql是由SQL接口，解析器，优化器，缓存，存储引擎组成的
Connectors指的是不同语言中与SQL的交互
Management Serveices & Utilities： 系统管理和控制工具
Connection Pool: 连接池。管理缓冲用户连接，线程处理等需要缓存的需求
SQL Interface: SQL接口,接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface
Parser: 解析器。SQL命令传递到解析器的时候会被解析器验证和解析。
Optimizer: 查询优化器。SQL语句在查询之前会使用查询优化器对查询进行优化。
Cache和Buffer： 查询缓存。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数
据.
**Engine ：**存储引擎。存储引擎是MySql中具体的与文件打交道的子系统。

4、使用mysql客户端登录服务器，重置root密码

方法一：这个方法不保险因为密码显示在了命令行

[root@localhost ~]# mysqladmin -uroot -p'iwwlyG.o)7b@'

方法二：
将密码从iwwlyG.o)7bf改为iwwlyG.o)7b@（通用二进制方式）

[root@localhost ~]# mysqladmin -uroot -p password
Enter password: 
New password: 
Confirm new password: 
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.