#mysql详解及其安装(外加mariadb安装)
文章目录
##关系型数据库介绍
###数据结构模型
数据结构模型主要有:
- 层次模型
- 网状结构
- 关系模型(存放在硬盘)
关系模型:
二维关系:row,column
数据库管理系统:DBMS
关系:Relational,RDBMS
####RDBMS专业名词
常见的关系模型数据库管理系统:
- MySQL:MySQL,MariaDB,Percona-Server
- PostgreSQL:简称为pgsql
- Oracle
- MSSQL
**SQL:**Structure Query Language,结构化查询语言
**约束:**constraint,向数据表提供的数据要遵守的限制
- 主键约束:一个或多个字段的组合,填入的数据必须能在本表中唯一标识本行。且必须提供数据,不能为空(NOT NULL)。
- 一个表只能存在一个
- 惟一键约束:一个或多个字段的组合,填入的数据必须能在本表中唯一标识本行。允许为空(NULL)
- 一个表可以存在多个
- 外键约束:一个表中的某字段可填入数据取决于另一个表的主键已有的数据
- 检查性约束
**索引:**将表中的一个或多个字段中的数据复制一份另存,并且这些数据需要按特定次序排序存储
###关系型数据库的常见组件
关系型数据库的常见组件有:
- 数据库:database
- 表:table,由行(row)和列(column)组成
- 索引:index
- 视图:view
- 用户:user
- 权限:privilege
- 存储过程:procedure
- 存储函数:function
- 触发器:trigger
- 事件调度器:event scheduler
###SQL语句
SQL语句有三种类型:
- DDL:Data Defination Language,数据定义语言
- DML:Data Manipulation Language,数据操纵语言
- DCL:Data Control Language,数据控制语言
SQL语句类型 | 对应操作 |
---|---|
DDL | CREATE:创建 DROP:删除 ALTER:修改 |
DML | INSERT:向表中插入数据 DELETE:删除表中数据 UPDATE:更新表中数据 SELECT:查询表中数据 |
DCL | GRANT:授权 REVOKE:移除授权 |
##mysql安装与配置
###mysql安装
mysql安装方式有三种:
- 源代码:编译安装
- 二进制格式的程序包:展开至特定路径,并经过简单配置后即可使用
- 程序包管理器管理的程序包:
- rpm:有两种
- OS Vendor:操作系统发行商提供的
- 项目官方提供的
- deb
- rpm:有两种
####源码安装mysql:
安装源码包
[root@localhost ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
--2022-07-22 20:06:31-- http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
Resolving dev.mysql.com (dev.mysql.com)... 23.64.178.143, 2600:140b:2:a9b::2e31, 2600:140b:2:a93::2e31
Connecting to dev.mysql.com (dev.mysql.com)|23.64.178.143|:80... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm [following]
--2022-07-22 20:06:31-- https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
Connecting to dev.mysql.com (dev.mysql.com)|23.64.178.143|:443... connected.
HTTP request sent, awaiting response... 302 Moved Temporarily
Location: https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm [following]
--2022-07-22 20:06:32-- https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm
Resolving repo.mysql.com (repo.mysql.com)... 23.58.116.230
Connecting to repo.mysql.com (repo.mysql.com)|23.58.116.230|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 25680 (25K) [application/x-redhat-package-manager]
Saving to: ‘mysql57-community-release-el7-11.noarch.rpm’
mysql57-community 100%[===========>] 25.08K 167KB/s in 0.2s
2022-07-22 20:06:33 (167 KB/s) - ‘mysql57-community-release-el7-11.noarch.rpm’ saved [25680/25680]
[root@localhost ~]# lls
-bash: lls: command not found
[root@localhost ~]# ls
anaconda-ks.cfg mysql57-community-release-el7-11.noarch.rpm
[root@localhost ~]# rpm -Uvh mysql57-community-release-el7-11.noarch.rpm
warning: mysql57-community-release-el7-11.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Verifying... ################################# [100%]
Preparing... ################################# [100%]
package mysql57-community-release-el7-11.noarch is already installed
[root@localhost ~]# ls /etc/yum.repos.d/
mysql-community.repo mysql-community-source.repo xx.repo
[root@localhost ~]#
下载mysql的服务包,数据库的客户包,数据库的命令包,数据库的工具包
首先,需要先禁用mysql模块
[root@localhost ~]# yum module disable mysql
Last metadata expiration check: 0:13:06 ago on Fri 22 Jul 2022 09:25:48 PM CST.
Dependencies resolved.
==================================================================================================
Package Architecture Version Repository Size
==================================================================================================
Disabling modules:
mysql
Transaction Summary
==================================================================================================
Is this ok [y/N]: y
Complete!
再下载包
[root@localhost ~]# yum -y install mysql-community-server mysql-community-client mysql-community-common mysql-community-devel --nogpgcheck
一定要加nogpgcheck
查看
[root@localhost ~]# rpm -qa | grep mysql
mysql57-community-release-el7-11.noarch
mysql-community-libs-5.7.38-1.el7.x86_64
mysql-community-server-5.7.38-1.el7.x86_64
mysql-community-common-5.7.38-1.el7.x86_64
mysql-community-client-5.7.38-1.el7.x86_64
mysql-community-devel-5.7.38-1.el7.x86_64
[root@localhost ~]#
启动数据库设置开启自启
[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 Fri 2022-07-22 21:43:52 CST; 43s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 4562 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYS>
Process: 4513 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 4565 (mysqld)
Tasks: 27 (limit: 4840)
Memory: 267.9M
CGroup: /system.slice/mysqld.service
└─4565 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
Jul 22 21:43:50 localhost.localdomain systemd[1]: Starting MySQL Server...
Jul 22 21:43:52 localhost.localdomain systemd[1]: Started MySQL Server.
lines 1-15/15 (END)
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 [::]:22 [::]:*
[root@localhost ~]#
找到数据库的临时密码
[root@localhost ~]# grep "password" /var/log/mysqld.log
2022-07-22T13:43:51.031414Z 1 [Note] A temporary password is generated for root@localhost: hjpf7dy!uJp4
[root@localhost ~]#
登入数据库
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.38
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>
修改数据库密码
1.设置密码安全性
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
2.设置密码长度
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
设置密码
mysql> alter user 'root'@'localhost' identified by '@123';
Query OK, 0 rows affected (0.00 sec)
mysql>
退出验证
mysql> quit
Bye
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.38 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>
记得删除mysql的源码包,避免mysql自动升级
[root@localhost ~]# rpm -e mysql57-community-release
[root@localhost ~]# ls /etc/yum.repos.d/
xx.repo
[root@localhost ~]#
下载mariadb
[root@zzz ~]# mount /dev/cdrom /mnt/
mount: /mnt: WARNING: device write-protected, mounted read-only.
[root@zzz ~]# yum install -y mariadb*
查看
[root@zzz ~]# systemctl enable --now mariadb
Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.
[root@zzz ~]# systemctl start mariadb
[root@zzz ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 80 0.0.0.0:3306 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
[root@zzz ~]#
进入到数据库(mariadb第一次不需要密码)
[root@zzz ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.17-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
设置密码
MariaDB [(none)]> set password = password('@123');
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]>
验证
[root@zzz ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.3.17-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>