MySQL简述和安装部署

最新推荐文章于 2024-05-06 16:39:26 发布

今天摘太阳

最新推荐文章于 2024-05-06 16:39:26 发布

阅读量593

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_72900594/article/details/126152726

版权

#MySQL
数据库介绍
关系型数据库（数据存放在硬盘）：

MySQL 5.7 及之前的版本，开源软件 → 8.0 商业软件
Oracle（甲骨文）商业软件
MariaDB 开源软件
SqlServer
msSQL 微软
非关系型数据库（数据存放在内存，变量）：

Redis
MemCache
MongoDB
PostgreSQL
其它（数据存放在文件中，由 sql 语句处理）：

SQLite
关系型数据库介绍
数据结构模型
数据结构模型主要有：

层次模型
网状结构
关系模型
关系模型：
二维关系：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，数据控制语言

mysql 安装
mysql 安装方式有三种：

源代码：编译安装
二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用
程序包管理器管理的程序包：
rpm：有两种
OS Vendor：操作系统发行商提供的
项目官方提供的
deb

SQL 语句类型	对应操作
DDL:	CREATE：创建DROP：删除ALTER：修改
DML	INSERT：向表中插入数据DELETE：删除表中数据UPDATE：更新表中数据SELECT：查询表中数据
DCL	GRANT：授权REVOKE：移除授权

//配置mysql的yum源

[root@localhost ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
--2022-07-24 21:58:29--  http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
Resolving dev.mysql.com (dev.mysql.com)... 23.4.59.106, 2600:1406:4400:2a7::2e31, 2600:1406:4400:29c::2e31
Connecting to dev.mysql.com (dev.mysql.com)|23.4.59.106|: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-24 21:58:29--  https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
Connecting to dev.mysql.com (dev.mysql.com)|23.4.59.106|: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-24 21:58:32--  https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm
Resolving repo.mysql.com (repo.mysql.com)... 23.78.91.208
Connecting to repo.mysql.com (repo.mysql.com)|23.78.91.208|: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-release-e 100%[===========================================>]  25.08K  --.-KB/s    in 0s      

2022-07-24 21:58:33 (235 MB/s) - 'mysql57-community-release-el7-11.noarch.rpm' saved [25680/25680]

//禁用mysql模块

[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%]
Updating / installing...
   1:mysql57-community-release-el7-11 ################################# [100%]
[root@localhost ~]#  yum module disable mysql
Failed to set locale, defaulting to C.UTF-8
CentOS Stream 8 - AppStream                                                     6.7 kB/s | 4.4 kB     00:00    
CentOS Stream 8 - BaseOS                                                        8.2 kB/s | 3.9 kB     00:00    
CentOS Stream 8 - Extras                                                        1.6 kB/s | 2.9 kB     00:01    
MySQL Connectors Community                                                       18 kB/s |  49 kB     00:02    
MySQL Tools Community                                                           116 kB/s | 651 kB     00:05    
MySQL 5.7 Community Server                                                      173 kB/s | 2.6 MB     00:15    
Dependencies resolved.
================================================================================================================
 Package                   Architecture             Version                     Repository                 Size
================================================================================================================
Disabling modules:
 mysql                                                                                                         

Transaction Summary
================================================================================================================

Is this ok [y/N]: y
Complete!

//安装mysql

[root@localhost ~]#  dnf -y install mysql-community-server mysql-community-client  mysql-community-common mysql-community-devel --nogpgcheck
Failed to set locale, defaulting to C.UTF-8
Last metadata expiration check: 0:01:06 ago on Sun Jul 24 21:59:28 2022.
Dependencies resolved.
================================================================================================================
 Package                         Architecture    Version                       Repository                  Size
================================================================================================================
Installing:
 mysql-community-client          x86_64          5.7.38-1.el7                  mysql57-community           28 M
 mysql-community-common          x86_64          5.7.38-1.el7                  mysql57-community          311 k
 mysql-community-devel           x86_64          5.7.38-1.el7                  mysql57-community          4.2 M
 mysql-community-server          x86_64          5.7.38-1.el7                  mysql57-community          178 M
Installing dependencies:
 mysql-community-libs            x86_64          5.7.38-1.el7                  mysql57-community          2.6 M
 ncurses-compat-libs             x86_64          6.1-9.20180224.el8            baseos                     328 k

Transaction Summary
================================================================================================================
Install  6 Packages

Total download size: 213 M
Installed size: 915 M
Downloading Packages:
(1/6): ncurses-compat-libs-6.1-9.20180224.el8.x86_64.rpm                        2.0 MB/s | 328 kB     00:00    
(2/6): mysql-community-common-5.7.38-1.el7.x86_64.rpm                            92 kB/s | 311 kB     00:03    
(3/6): mysql-community-devel-5.7.38-1.el7.x86_64.rpm                            376 kB/s | 4.2 MB     00:11    
(4/6): mysql-community-libs-5.7.38-1.el7.x86_64.rpm                             205 kB/s | 2.6 MB     00:12    
//安装mysql(5-6/6): mysql-community-server-5.7. 32% [===========-                        ] 622 kB/s |  70 MB    (5/6): mysql-community-client-5.7.38-1.el7.x86_64.rpm                           235 kB/s |  28 MB     02:00    
(6/6): mysql-community-server-5.7.38-1.el7.x86_64.rpm                           941 kB/s | 178 MB     03:13    
----------------------------------------------------------------------------------------------------------------
Total                                                                           1.0 MB/s | 213 MB     03:26     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                        1/1 
  Installing       : mysql-community-common-5.7.38-1.el7.x86_64                                             1/6 
  Installing       : mysql-community-libs-5.7.38-1.el7.x86_64                                               2/6 
  Running scriptlet: mysql-community-libs-5.7.38-1.el7.x86_64                                               2/6 
  Installing       : ncurses-compat-libs-6.1-9.20180224.el8.x86_64                                          3/6 
  Installing       : mysql-community-client-5.7.38-1.el7.x86_64                                             4/6 
  Running scriptlet: mysql-community-server-5.7.38-1.el7.x86_64                                             5/6 
  Installing       : mysql-community-server-5.7.38-1.el7.x86_64                                             5/6 
  Running scriptlet: mysql-community-server-5.7.38-1.el7.x86_64                                             5/6 
  Installing       : mysql-community-devel-5.7.38-1.el7.x86_64                                              6/6 
  Running scriptlet: mysql-community-devel-5.7.38-1.el7.x86_64                                              6/6 
[/usr/lib/tmpfiles.d/mysql.conf:23] Line references path below legacy directory /var/run/, updating /var/run/mysqld → /run/mysqld; please update the tmpfiles.d/ drop-in file accordingly.

  Verifying        : ncurses-compat-libs-6.1-9.20180224.el8.x86_64                                          1/6 
  Verifying        : mysql-community-client-5.7.38-1.el7.x86_64                                             2/6 
  Verifying        : mysql-community-common-5.7.38-1.el7.x86_64                                             3/6 
  Verifying        : mysql-community-devel-5.7.38-1.el7.x86_64                                              4/6 
  Verifying        : mysql-community-libs-5.7.38-1.el7.x86_64                                               5/6 
  Verifying        : mysql-community-server-5.7.38-1.el7.x86_64                                             6/6 

Installed:
  mysql-community-client-5.7.38-1.el7.x86_64            mysql-community-common-5.7.38-1.el7.x86_64              
  mysql-community-devel-5.7.38-1.el7.x86_64             mysql-community-libs-5.7.38-1.el7.x86_64                
  mysql-community-server-5.7.38-1.el7.x86_64            ncurses-compat-libs-6.1-9.20180224.el8.x86_64           

Complete!

//开启mysql服务，并设置开机自启

[root@localhost ~]# systemctl enable --now 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 Sun 2022-07-24 22:05:18 CST; 5s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 27874 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (cod>
  Process: 27621 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 27876 (mysqld)
    Tasks: 27 (limit: 23329)
   Memory: 328.1M
   CGroup: /system.slice/mysqld.service
           └─27876 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Jul 24 22:05:12 localhost.localdomain systemd[1]: Starting MySQL Server...
Jul 24 22:05:18 localhost.localdomain systemd[1]: Started MySQL Server.
[root@localhost ~]# ss -antl
State        Recv-Q       Send-Q             Local Address:Port             Peer Address:Port      Process      
LISTEN       0            32                 192.168.122.1:53                    0.0.0.0:*                      
LISTEN       0            128                      0.0.0.0:22                    0.0.0.0:*                      
LISTEN       0            5                      127.0.0.1:631                   0.0.0.0:*                      
LISTEN       0            128                      0.0.0.0:111                   0.0.0.0:*                      
LISTEN       0            128                         [::]:22                       [::]:*                      
LISTEN       0            5                          [::1]:631                      [::]:*                      
LISTEN       0            80                             *:3306                        *:*                      
LISTEN       0            128                         [::]:111                      [::]:*

//查找密码并登录

[root@localhost ~]# grep "password" /var/log/mysqld.log
2022-07-24T14:05:15.520703Z 1 [Note] A temporary password is generated for root@localhost: qI*g-Qjl3xCY
[root@localhost ~]#  mysql -uroot -pqI*g-Qjl3xCY
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.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> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[root@localhost ~]#  mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
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>  show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

//为避免mysql自动升级，需卸载最开始安装的yum源

[root@localhost ~]# rpm -e mysql57-community-release

#MariaDB 安装

[root@localhost ~]# dnf -y install mariadb*
Failed to set locale, defaulting to C.UTF-8
CentOS Stream 8 - AppStream                                                     8.0 kB/s | 4.4 kB     00:00    
CentOS Stream 8 - AppStream                                                     1.9 MB/s |  24 MB     00:12    
CentOS Stream 8 - BaseOS                                                        8.2 kB/s | 3.9 kB     00:00    
CentOS Stream 8 - BaseOS                                                        3.6 MB/s |  25 MB     00:06    
CentOS Stream 8 - Extras                                                        3.3 kB/s | 2.9 kB     00:00    
Dependencies resolved.
================================================================================================================
 Package                          Arch        Version                                      Repository      Size
================================================================================================================
Installing:
 mariadb                          x86_64      3:10.3.28-1.module_el8.3.0+757+d382997d      appstream      6.0 M
 mariadb-backup                   x86_64      3:10.3.28-1.module_el8.3.0+757+d382997d      appstream      6.1 M
 mariadb-common                   x86_64      3:10.3.28-1.module_el8.3.0+757+d382997d      appstream       64 k
 mariadb-connector-c              x86_64      3.1.11-2.el8_3                               appstream      200 k
 mariadb-connector-c-config       noarch      3.1.11-2.el8_3                               appstream       15 k
 mariadb-connector-c-devel        x86_64      3.1.11-2.el8_3                               appstream       68 k
 mariadb-connector-odbc           x86_64      3.1.12-1.el8                                 appstream      118 k
 mariadb-devel                    x86_64      3:10.3.28-1.module_el8.3.0+757+d382997d      appstream      1.1 M

//开启mariadb，并设置开机自启

[root@localhost ~]# 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@localhost ~]# ss -antl
State        Recv-Q       Send-Q             Local Address:Port             Peer Address:Port      Process      
LISTEN       0            32                 192.168.122.1:53                    0.0.0.0:*                      
LISTEN       0            128                      0.0.0.0:22                    0.0.0.0:*                      
LISTEN       0            5                      127.0.0.1:631                   0.0.0.0:*                      
LISTEN       0            25                       0.0.0.0:514                   0.0.0.0:*                      
LISTEN       0            80                       0.0.0.0:3306                  0.0.0.0:*                      
LISTEN       0            128                      0.0.0.0:111                   0.0.0.0:*                      
LISTEN       0            128                         [::]:22                       [::]:*                      
LISTEN       0            5                          [::1]:631                      [::]:*                      
LISTEN       0            25                          [::]:514                      [::]:*                      
LISTEN       0            128                         [::]:111                      [::]:*                      
[root@localhost ~]#

//启动

[root@localhost ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.28-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)]> exit
Bye
[root@localhost ~]#  mysql -uroot -pzhan123!
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.28-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)]>