mysql体系结构及安装部署mysql-优快云博客

本文链接：https://blog.youkuaiyun.com/wjs_6667/article/details/131557633

1.体系结构

MySQL server
连接层	连接池（缓冲池）
SQL层	系统管理和控制工具	SQL接口。	解析器。	查询优化组件	缓存和缓冲区
存储引擎	MylSAM、InnoDB、BDB、MeMory、Archive等
物理文件层	NTFS、EXT3、NFS等文件系统；数据、配置、日志、其他文件

MySQL是由SQL接口、解析器、优化器、缓存、存储引擎组成的

Connectors指的是不同语言中与SQL的交互

Management Services & Utilities：系统管理和控制工具

Connection Pool：连接池。管理缓冲用户连接，线程处理等需要缓存的需求

SQL Interface：SQL接口,接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface

Parser：解析器。SQL命令传递到解析器的时候会被解析器验证和解析。

Optimizer：查询优化器。SQL语句在查询之前会使用查询优化器对查询进行优化。

Cache和Buffer：查询缓存。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据.

Engine：存储引擎。存储引擎是MySql中具体的与文件打交道的子系统。

2.安装mysql

1.yum 源安装

第一步配置yum源，可以手动配置也可以直接下载。

可以手动配置yum源

baseurl指向国内镜像源地址，比如清华、中科大。
               /etc/yum.repos.d/mysql.repo

               [mysql]
               name=mysql5.7
               baseurl=http://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-x86_64/
               gpgcheck=0

自动

yum install http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm

2.下载mysql

yum install mysql-community-server -y

3.启动服务

[root@localhost ~]# systemctl start mysqld #启动服务
[root@localhost ~]# systemctl enable mysqld #开机自启

[root@localhost ~]# systemctl status mysqld #检查状态
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Wed 2023-07-05 17:21:14 CST; 9min ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Main PID: 1100 (mysqld)
CGroup: /system.slice/mysqld.service
└─1100 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Jul 05 17:21:12 localhost.localdomain systemd[1]: Starting MySQL Server...
Jul 05 17:21:14 localhost.localdomain systemd[1]: Started MySQL Server.

4.查看临时密码登陆mysql

使用awk从/var/log/mysqld.log这个文件里面过滤密码

[root@localhost ~]# awk '/temporary password/ {print $NF}' /var/log/mysqld.log
e(KoOp30kcwU
[root@localhost ~]#

5.登陆mysql及改密

[root@localhost ~]# mysql -uroot -p'e(KoOp30kcwU'

---p和密码不能有空隙，否则将认为是数据库名称
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.42

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> alter user root@localhost identified by 'Redhat123.';
Query OK, 0 rows affected (0.01 sec)

mysql>

2. 第二种安装方式-通用二进制方式

这种方式适合生产环境

1. 导包到linux中/root目录下

[root@localhost ~]# ll
total 656956
-rw-------. 1 root root 1403 Jul 5 16:39 anaconda-ks.cfg
-rw-r--r-- 1 root root 672716800 Jul 5 18:11 mysql-5.7.14-linux-glibc2.5-x86_64.tar
[root@localhost ~]# ll mysql-5.7.14-linux-glibc2.5-x86_64.tar
-rw-r--r-- 1 root root 672716800 Jul 5 18:11 mysql-5.7.14-linux-glibc2.5-x86_64.tar

2.创建用户组
[root@localhost ~]# groupadd -r mysqld
[root@localhost ~]# useradd mysqld -r -g mysqld -c "MySQL Server" -s /bin/false

3.解压
[root@localhost ~]# tar xf mysql-5.7.14-linux-glibc2.5-x86_64.tar -C /usr/local/
[root@localhost ~]# tar xf /usr/local/mysql-5.7.14-linux-glibc2.5-x86_64.tar.gz -C /usr/local/

4.创建软链接

[root@localhost ~]# ln -sv /usr/local/mysql-5.7.14-linux-glibc2.5-x86_64 /usr/local/mysql
‘/usr/local/mysql’ -> ‘/usr/local/mysql-5.7.14-linux-glibc2.5-x86_64’
5.初始化

[root@localhost ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysqld --basedir=/usr/local/mysql/--datadir=/usr/local/mysql/data

6.提供配置文件和服务脚本

[root@localhost ~]# cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf
cp: overwrite ‘/etc/my.cnf’? yes

7.修改配置文件：在/etc/my.cnf 中的[mysqld]下添加：

basedir = /usr/local/mysql
datadir = /usr/local/mysql/data

8.服务脚本
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
9.添加系统服务，并设置开机自启动
  chkconfig --add mysqld
chkconfig mysqld on
10.启动mysql
   /usr/local/mysql/bin/mysqld_safe --user=mysql &

12. 配置环境变量：

# vim /etc/profile.d/mysql.sh
export PATH=/usr/local/mysql/bin:$PATH

           加载使其生效。

3.mysql改密方式

第一种，知道密码的情况下

知道密码改密
第一种
[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.
[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.42 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>



方法2：
mysql> alter user root@localhost identified by '123';
Query OK, 0 rows affected (0.00 sec)
方法3：
mysql> SET PASSWORD FOR 'root'@'localhost' = '123456';
Query OK, 0 rows affected (0.00 sec)

方法4：
mysql> update mysql.user set authentication_string=password('12345')
	    -> where user="root" and host="localhost";
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

第二种，不知道密码

跳过权限表的第一种

1.在配置文件/etc/my.cnf里面添加 skip-grant-tables

2.重启，然后直接登陆mysql

[root@localhost ~]# systemctl restart mysqld---重启
[root@localhost ~]# mysql -uroot -登陆
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.42 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> flush privileges; --刷新权限
Query OK, 0 rows affected (0.01 sec)
mysql> alter user root@localhost identified by 'Redhat222.'; --修改密码
Query OK, 0 rows affected (0.00 sec)

第二种

重启登陆之后，直接修改，然后刷新权限表

mysql> update mysql.user set authentication_string=password('123456')
-> where user='root' and host='localhost';
mysql> flush privileges；

最后删除在/etc/my.cnf的skip-grant-tables