MySQL介绍与安装
1.MySQL简介(关系型数据库管理系统)
MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一。
MySQL是一种关系型数据库管理系统,关系数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。
MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策,分为社区版和商业版,由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,一般中小型网站的开发都选择 MySQL 作为网站数据库。
2.数据库类型
关系型数据库:
mysql 5.7 免费/mariadb 10.0
mysql 8.0 收费 (与mysql 5.7版本的人员不同)
oracle 收费
mongodb
sqlite
MSSQL (微软的数据库 MicroSoft 图形化数据库)
缓存数据库:
memcache
redis
DBA(数据库管理员)
数据库与excel区别:
- 数据库可以存两种数据:文本和数字
- excel可以存多种(以链接方式存放)
关系型数据库介绍:
数据结构模型
1.层次模型
2.网状模型
3.关系模型(二维关系:row,column)
数据库管理系统:DBMS
关系行数据库:Relational,RDBMS
数据库结构设计:DBA
3.RDBMS专业名词
常见的关系型数据库管理系统:
- MySQL:MySQL,MariaDB,Percona-Server
- PostgreSQL:简称为pgsql
- Oracle
- MSSQL
SQL:Structure Query Language,结构化查询语言
约束:constraint,向数据表提供的数据要遵守的限制
实例:
#在数据库中,行叫记录,列叫字段
表一:
ID(不能重复) NAME AGE
1 harry 20
2 tom 21
表二:
ID salary(工资)
1 10000
2 5000
- 主键约束:表一中ID不能重复,例如我们的身份证不能重复,保证在一个字段或者一组字段里的数据与表中其它行的数据相比是唯一的
- 惟一键约束:一个或多个字段的组合,填入的数据必须能在本表中唯一标识本行。允许为空(NULL)一个表上可以放置多个唯一性约束
- 外键约束:表一与表二相约束,例如表一中21岁的tom需要查看工资,则需要在表二去查询
- 检查性约束
索引:将表中的一个或多个字段中的数据复制一份另存,并且这些数据需要按特定次序排序存储
4.关系型数据库的常见组件
关系型数据库的常见组件有:
数据库:database
表:table,由行(row)和列(column)组成
数据:
索引:index
视图:view000
权限:privilege
存储过程:procedure
存储函数:function
触发器:trigger
时间调度器:event scheduler
5.SQL语句
三种类型:
- DDL:Data Defination Language,数据定义语言
- DML:Data Manipulation Language,数据操纵语言
- DCL:Data Control Language,数据控制语言
类型 | 对应操作 |
---|---|
DDL | CREATE:创建 DROP:删除 ALTER:修改 |
DML | INSERT:向表中插入数据 DELETE:删除表中数据 UPDATE:更新表中数据 SELECT:查询表中数据 |
DCL | GRANT:授权 REVOKE:移除授权 |
6.mysql安装与配置
mysql安装方式有三种:
1.源代码:编译安装
2.二进制格式的程序包:展开至特定路径,并经过简单配置后即可使用
3.程序包管理器管理的程序包:
3.1 rpm有两种:
OS Vendor:操作系统发行商提供的
项目官方提供的
3.2 deb
6.1使用yum安装mysql
- 配置Mysql的yum源
[root@node3 ~]# wget -O /usr/src/mysql57-community-release-el7-10.noarch.rpm http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
[root@node3 ~]# rpm -Uvh /usr/src/mysql57-community-release-el7-10.noarch.rpm
警告:/usr/src/mysql57-community-release-el7-10.noarch.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
Verifying... ################################# [100%]
准备中... ################################# [100%]
正在升级/安装...
1:mysql57-community-release-el7-10 ################################# [100%]
- 安装mysql
[root@localhost ~]# yum -y install mariadb mariadb-server
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
上次元数据过期检查:0:01:50 前,执行于 2021年08月24日 星期二 04时27分51秒。
软件包 mariadb-3:10.3.28-1.module_el8.3.0+757+d382997d.x86_64 已安装。
软件包 mariadb-server-3:10.3.28-1.module_el8.3.0+757+d382997d.x86_64 已安装。
依赖关系解决。
无需任何处理。
完毕!
[root@localhost ~]#
MySQL配置
- 设置开机自启mysql服务
[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 ~]#
- 查看端口3306
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
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 127.0.0.1:6010 0.0.0.0:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 5 [::1]:631 [::]:*
LISTEN 0 128 [::1]:6010 [::]:*
[root@localhost ~]#
- 由于是下载的是mariadb,所以mysql是没有密码的,需要手动配置
[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)]> set password = password("1"); //设置密码
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> exit
Bye
[root@localhost ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@localhost ~]# mysql -u root -p1
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
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)]>
6.2使用二进制安装mysql
-
下载安装包完成后,利用Xshell远程工具,Xftp将安装包移动到/usr/src目录中去
[root@localhost ~]# cd /usr/src/
[root@localhost src]# ls
debug kernels
[root@localhost src]# ls
debug kernels mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
[root@localhost src]#
- 安装依赖包
[root@localhost src]# yum whatprovides libncurses.so.5
[root@localhost src]# yum -y install ncurses-compat-libs
- 创建用户和组
[root@localhost src]# useradd -r -M -s /bin/nologin mysql
[root@localhost src]#
- 解压软件包至/usr/local
[root@localhost src]# tar xf mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@localhost src]# ln -s /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64/ /usr/local/mysql
[root@localhost src]# cd /usr/local/
[root@localhost local]# ll
total 0
drwxr-xr-x. 2 root root 6 Aug 12 2018 bin
drwxr-xr-x. 2 root root 6 Aug 12 2018 etc
drwxr-xr-x. 2 root root 6 Aug 12 2018 games
drwxr-xr-x. 2 root root 6 Aug 12 2018 include
drwxr-xr-x. 2 root root 6 Aug 12 2018 lib
drwxr-xr-x. 2 root root 6 Aug 12 2018 lib64
drwxr-xr-x. 2 root root 6 Aug 12 2018 libexec
lrwxrwxrwx 1 root root 47 Sep 14 21:55 mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64/
drwxr-xr-x 9 root root 129 Sep 14 21:54 mysql-5.7.34-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root 6 Aug 12 2018 sbin
drwxr-xr-x. 5 root root 49 Sep 14 14:59 share
drwxr-xr-x. 2 root root 6 Aug 12 2018 src
[root@localhost local]#
- 修改mysql属主属组
[root@localhost local]# chown -R mysql.mysql mysql*
[root@localhost local]# ll
total 0
drwxr-xr-x. 2 root root 6 Aug 12 2018 bin
drwxr-xr-x. 2 root root 6 Aug 12 2018 etc
drwxr-xr-x. 2 root root 6 Aug 12 2018 games
drwxr-xr-x. 2 root root 6 Aug 12 2018 include
drwxr-xr-x. 2 root root 6 Aug 12 2018 lib
drwxr-xr-x. 2 root root 6 Aug 12 2018 lib64
drwxr-xr-x. 2 root root 6 Aug 12 2018 libexec
lrwxrwxrwx 1 mysql mysql 47 Sep 14 21:55 mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64/
drwxr-xr-x 9 mysql mysql 129 Sep 14 21:54 mysql-5.7.34-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root 6 Aug 12 2018 sbin
drwxr-xr-x. 5 root root 49 Sep 14 14:59 share
drwxr-xr-x. 2 root root 6 Aug 12 2018 src
[root@localhost local]#
- 修改mysql属主属组
[root@localhost local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@localhost local]# source /etc/profile.d/mysql.sh //授权
[root@localhost local]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@localhost local]#
- 建立数据存放目录
[root@localhost local]# mkdir /opt/data
[root@localhost local]# chown -R mysql.mysql /opt/data/
[root@localhost data]# ll /opt/
total 0
drwxr-xr-x 2 mysql mysql 6 Sep 14 21:59 data
- 初始化数据库
#[root@localhosts ~]# /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --datadir=/opt/data
//如果使用这条命令初始化数据库,则跳过密码登录
#[root@localhosts ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
//如果上面没有source /etc/profile.d/mysql.sh授权这个文件,就要加绝对路径
[root@localhost local]# mysqld --initialize --user=mysql --datadir=/opt/data/
2021-09-15T02:35:43.767826Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-09-15T02:35:43.909818Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-09-15T02:35:43.932380Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-09-15T02:35:43.938514Z 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: a00ddde7-15cd-11ec-a220-000c29297f87.
2021-09-15T02:35:43.939982Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-09-15T02:35:44.283372Z 0 [Warning] CA certificate ca.pem is self signed.
2021-09-15T02:35:44.612823Z 1 [Note] A temporary password is generated for root@localhost: jd*Vud.mY2S-
//这个命令的最后会生成一个临时密码,此处密码是 jd*Vud.mY2S-
//这个密码是随机的,一定要记住这个密码,因为一会登录时会用到
[root@localhost ~]#
- 生成配置文件
[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
[root@localhost ~]#
- 修改配置文件
[root@localhost ~]# cd /usr/local/mysql/support-files/
[root@localhost support-files]# ls
magic mysqld_multi.server mysql-log-rotate mysql.server
[root@localhost support-files]# vim mysql.server
46 basedir=/usr/local/mysql
47 datadir=/opt/data
- 启动脚本
[root@localhost support-files]# /usr/local/mysql/support-files/mysql.server start
Starting MySQL.Logging to '/opt/data/localhost.localdomain.err'.
SUCCESS!
[root@localhost support-files]# ss -atnl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
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 127.0.0.1:6010 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 128 [::1]:6010 [::]:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 [::]:111 [::]:*
[root@localhost support-files]#
- 登录数据库,并修改临时密码为1
[root@localhost support-files]# mysql -uroot -pjd*Vud.mY2S-
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 3
Server version: 5.7.34
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> set password = password('1');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> exit
Bye
[root@localhost support-files]#
[root@localhost ~]# vim .my.cnf
[root@localhost ~]# cat .my.cnf
[client]
user=root
password=1
[root@localhost ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
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)]>
- 用service服务控制mysql
[root@localhost system]# cd /usr/lib/systemd/system
[root@localhost system]# vim mysqld.service
[root@localhost system]# cat mysqld.service
[Unit]
Description=mysql server daemon
After=network.targe
[Service]
Type=forking
ExecStart=/usr/local/mysql/support-files/mysql.server start //指定路径
ExecStop=/usr/local/mysql/support-files/mysql.serve stop
ExecReload=/bin/kill -HUP $MAINPID
[Install]
WantedBy=multi-user.target
[root@localhost system]#
- 重新加载,并开机自启mysql服务
[root@localhost system]# systemctl daemon-reload
[root@localhost system]# systemctl enable --now mysqld.service
Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.
[root@localhost system]#
- 验证效果
[root@localhost system]# /usr/local/mysql/support-files/mysql.server stop
Shutting down MySQL.. SUCCESS!
[root@localhost system]# systemctl start mysqld
[root@localhost system]# ss -atnl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
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 127.0.0.1:6010 0.0.0.0:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 5 [::1]:631 [::]:*
LISTEN 0 128 [::1]:6010 [::]:*
[root@localhost system]# systemctl stop mysqld.service
[root@localhost system]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
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 127.0.0.1:6010 0.0.0.0:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 5 [::1]:631 [::]:*
LISTEN 0 128 [::1]:6010 [::]:*
[root@localhost system]#
7.mysql的程序组成
客户端
- mysql:CLI交互式客户端程序
- mysql_secure_installation:安全初始化(建议安装完以后执行此命令)
- mysqldump:mysql备份工具
- mysqladmin
服务端
- mysqld
mysql工具使用
语法:mysql [OPTIONS] [database]
常用的OPTIONS:
-uUSERNAME //指定用户名,默认为root
-hHOST //指定服务器主机,默认为localhost,推荐使用ip地址
-pPASSWORD //指定用户的密码
-P# //指定数据库监听的端口,这里的#需用实际的端口号代替,如-P3307
-V //查看当前使用的mysql版本
-e //不登录mysql执行sql语句后退出,常用于脚本
实例:
查看当前使用的mysql版本
[root@localhost ~]# mysql -V
mysql Ver 15.1 Distrib 10.3.28-MariaDB, for Linux (x86_64) using readline 5.1
指定用户名及指定服务器主机,默认localhost,推荐使用ip地址
[root@localhost ~]# mysql -u root -p1 -h127.0.0.1
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 11
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)]>
不登录mysql执行sql语句后退出,常用于脚本
[root@localhost ~]# mysql -u root -p1 -h127.0.0.1 -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
[root@localhost ~]#
8.MySQL客户端工具
mysql
navicat:商业的,收费,支持中英文
workbench:官方的,免费,英文版
navicat官方文档:
https://www.navicat.com.cn/
下面进行navicat安装及使用操作:
1.打开文档点击产品找到需要下载的mysql页面:
2.是什么系统对应下载什么系统就行
3.连接mysql之前给用户登录授权
[root@localhost ~]# mysql -u root -p1
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
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)]> grant all privileges on *.* to 'root'@'%' identified by '1' with grant option;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]>
4.连接mysql
5.查看是否连接成功