MySQL数据库介绍
MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一
MySQL数据库安装
yum安装
安装MySQL
由于MySQL被Oracle公司收购后用作商用,所以一般安装的MySQL数据库默认都是8.0版本(需要收费),想要安装免费的5.7版本需要先下载安装MySQL5.7的yum源
#配置MySQL的yum源
[root@test1 ~]# cd /usr/src/
[root@test1 src]# wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
[root@test1 src]# ls
debug kernels mysql57-community-release-el7-11.noarch.rpm
#更新源,如果本地有就更新,没有就安装
[root@test1 src]# rpm -Uvh mysql57-community-release-el7-11.noarch.rpm
#更新后查看yum源发现多了两个MySQL开头的yum源
[root@test1 src]# ls /etc/yum.repos.d/
CentOS-Base.repo epel-next.repo epel-playground.repo epel-testing-modular.repo mysql-community.repo
epel-modular.repo epel-next-testing.repo epel.repo epel-testing.repo mysql-community-source.repo
#安装MySQL5.7
#先禁用MySQL模块
[root@test1 src]# yum module disable mysql
#下面命令下载的4个包分别是:MySQL客户端、MySQL服务端、MySQL命令行、MySQL工具包,--nogpgcheck不检查包的来源合法性
[root@test1 src]# yum -y install mysql-community-server mysql-community-client mysql-community-common mysql-community-devel --nogpgcheck
MySQL使用systemctl来管理
[root@test1 src]# systemctl enable --now mysqld
[root@test1 src]# ss -anlt
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 [::]:22 [::]:*
[root@test1 src]#
在日志文件中将MySQL安装时生成的默认密码过滤出来
[root@test1 src]# grep "password is " /var/log/mysqld.log
2023-01-17T09:18:33.905003Z 1 [Note] A temporary password is generated for root@localhost: <)ZjjF5+ehE!
//最后的“<)ZjjF5+ehE!”就是生成的默认密码,每次安装时的密码是随机的
使用随机密码登录到MySQL数据库
[root@test1 src]# mysql -uroot -p //这里的root用户只是指在MySQL数据库中的root账户
Enter password: //密码是上面生成的随机密码
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.40
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>
首次登录后需要先修改密码才能进行其他操作。注意!MySQL5.79后删除了password字段,所以在5.79后不能使用password修改密码
#修改MySQL密码(只有yum安装的才会用到下面的三条命令)
set global validate_password_policy=0; //设置密码强度为0
set global validate_password_length=1; //设置密码长度为1,并不是指长度为一,是根据数据库里的算法
alter user'root'@'localhost' identified by 'sakura123!';//修改root用户密码为sakura123!
//还可以使用set password = password('sakura123!'); 修改密码
exit //修改后可以退出
再使用新密码登录
[root@test1 ~]# mysql -uroot -psakura123!
修改密码后使用数据库就不会报错
mysql> show databases; //查看数据库,默认的有四个
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql>
删除下载的MySQL5.7的源
rpm -e mysql57-community-release
安装mariadb
如果MySQL安装不成功,可以直接安装mariadb
yum -y install -y mariadb* //安装mariadb
systemctl enabled --now mariadb //设置开机自启
mariadb安装后要进行安全初始化
mysql_secure_installation //安全初始化数据库
Enter current password for root (enter for none): //输入当前root用户的密码,没有就回车
Set root password? [Y/n] Y //设置root密码?
New password: sakura123! //输入新密码,密码在终端中不会显示出来
Re-enter new password: sakura123! //再次输入密码
Password updated successfully!
Reloading privilege tables..
... Success! //成功
登录mariadb
[root@test2 ~]# mysql -uroot -psakura123!
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 14
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)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.002 sec)
MariaDB [(none)]>
二进制安装
二进制安装与源码安装的区别就是在于“编译三部曲”
关闭防火墙和selinux
[root@test1 ~]# systemctl stop firewalld
[root@test1 ~]# systemctl disable firewalld
[root@test1 ~]# setenforce 0
MySQL5.7.40 下载地址:https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.40-linux-glibc2.12-x86_64.tar.gz
在MySQL官网下载5.7版本的源码包,或者复制下载链接,使用命令下载
[root@test1 ~]# cd /usr/src/
[root@test1 src]# ls
debug kernels mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
检查有没有mysql系统用户,如果没有就创建
[root@test1 src]# id mysql
id: ‘mysql’: no such user
[root@test1 src]# useradd -M -s /sbin/nologin mysql
解压源码包
[root@test1 src]# tar -xf mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@test1 src]# cd /usr/local/
[root@test1 local]# ls
bin haproxy lib64 sbin
etc include libexec share
games lib mysql-5.7.34-linux-glibc2.12-x86_64 src
解压后的源码包名字太长不方便,可以重命名或者做软连接
[root@test1 local]# ln -sv mysql-5.7.34-linux-glibc2.12-x86_64 mysql
'mysql' -> 'mysql-5.7.34-linux-glibc2.12-x86_64'
[root@test1 local]# ls
bin haproxy lib64 mysql-5.7.34-linux-glibc2.12-x86_64 src
etc include libexec sbin
games lib mysql share
[root@test1 local]#
设置与MySQL相关的目录属主、属组为系统用户MySQL,防止后面安装时没有权限
[root@test1 local]# chown -R mysql.mysql mysql*
[root@test1 local]# ll | grep mysql
lrwxrwxrwx 1 mysql mysql 35 Feb 21 14:54 mysql -> mysql-5.7.34-linux-glibc2.12-x86_64
drwxr-xr-x 9 mysql mysql 129 Feb 21 14:52 mysql-5.7.34-linux-glibc2.12-x86_64
[root@test1 local]#
设置环境变量,让系统在任意位置都可以调用与MySQL相关的内容
[root@test1 local]# cd mysql/bin/
[root@test1 bin]# pwd
/usr/local/mysql/bin
[root@test1 bin]# echo 'export PATH=$PATH:/usr/local/mysql/bin' > /etc/profile.d/mysql.sh
[root@test1 bin]# source /etc/profile.d/mysql.sh
链接头文件
[root@test1 bin]# cd ..
[root@test1 mysql]# ls
bin docs include lib LICENSE man README share support-files
[root@test1 mysql]# pwd
/usr/local/mysql
[root@test1 mysql]# ln -sv /usr/local/mysql/include /usr/include/mysql
'/usr/include/mysql' -> '/usr/local/mysql/include'
[root@test1 mysql]#
设置lib文件
[root@test1 mysql]# vim /etc/ld.so.conf.d/mysql.conf
[root@test1 mysql]# cat /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/lib/
[root@test1 mysql]# ldconfig //刷新一下
添加man文档
[root@test1 mysql]# echo 'MANDATORY_MANPATH /usr/local/mysql/man' >> /etc/man_db.conf
创建数据存放目录,位置可以任意指定,修改属主和属组为mysql
[root@test1 mysql]# mkdir /opt/data
[root@test1 mysql]# chown -R mysql.mysql /opt/data/
初始化数据库
[root@test1 mysql]# mysqld --initialize --user mysql --datadir /opt/data/
2023-02-21T08:10:15.729303Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. ........
2023-02-21T08:10:16.630081Z 1 [Note] A temporary password is generated for root@localhost: (USif;kUS2rE
//最后一行的 (USif;kUS2rE,是生成的随机密码,需要记下
[root@test1 mysql]# cd
[root@test1 ~]# echo '(USif;kUS2rE' > password
[root@test1 ~]# cat password
(USif;kUS2rE
[root@test1 ~]#
二进制和源码安装没有配置文件,需要手动添加
[root@test1 ~]# vim /etc/my.cnf
[root@test1 ~]# cat /etc/my.cnf
[mysqld] #MySQL主程序名
basedir = /usr/local/mysql #MySQL解压目录
datadir = /opt/data #数据存放目录
socket = /tmp/mysql.sock #套接字存放目录
port = 3306 #端口
pid-file = /opt/data/mysql.pid #进程存放目录
user = mysql #用户
skip-name-resolve #跳过-名称-解析
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION #centos8默认安装MySQL8.0,此处安装的是5.7,如果不添加此行,可能会报模块错误
启动MySQL
方法一(原始启动):MySQL启动需要通过自带的脚本启动,存放在support-files中,名为mysql.server
[root@test1 ~]# /usr/local/mysql/support-files/mysql.server start
Starting MySQL.Logging to '/opt/data/test1.err'.
SUCCESS!
[root@test1 ~]# ss -anltup | grep 3306
tcp LISTEN 0 80 *:3306 *:* users:(("mysqld",pid=2511,fd=19))
[root@test1 ~]#
方法二(contos-6系统管理方法):通过service管理
[root@test1 ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@test1 ~]# vim /etc/init.d/mysqld
basedir=/usr/local/mysql
datadir=/opt/data
[root@test1 ~]# service mysqld start
Starting MySQL. SUCCESS!
[root@test1 ~]# ss -anltup | grep mysql
tcp LISTEN 0 80 *:3306 *:* users:(("mysqld",pid=2873,fd=19))
[root@test1 ~]#
方法三(centos-7、8 系统管理方法):使用systemctl来管理
[root@test1 ~]# cp /usr/lib/systemd/system/sshd.service ./mysqld.service //拷贝一份模板文件进行修改
[root@test1 ~]# vim mysqld.service
[root@test1 ~]# cat mysqld.service
[Unit]
Description=mysqld server daemon
After=network.target sshd-keygen.target
[Service]
Type=forking
ExecStart=/usr/local/mysql/support-files/mysql.server start
ExecStop=/usr/local/mysql/support-files/mysql.server stop
ExecReload=/bin/kill -HUP $MAINPID
[Install]
WantedBy=multi-user.target
[root@test1 ~]# mv mysqld.service /usr/lib/systemd/system/
[root@test1 ~]# systemctl daemon-reload
[root@test1 ~]# systemctl start mysqld.service
[root@test1 ~]# ss -anltup | grep mysql
tcp LISTEN 0 80 *:3306 *:* users:(("mysqld",pid=3261,fd=30))
[root@test1 ~]#
安装依赖包
[root@test1 ~]# yum -y install ncurses-compat-libs
使用随机密码,登录MySQL数据库
[root@test1 ~]# cat password
(USif;kUS2rE
[root@test1 ~]# mysql -uroot -p'(USif;kUS2rE'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
........
mysql>
修改密码
方法一:使用password修改(MySQL5.79之后没有password字段)
mysql> set password = password('sakura123!');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> exit
Bye
[root@test1 ~]# mysql -uroot -psakura123!
.........
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
方法二:使用alter修改
mysql> alter user 'root'@'localhost' identified by 'sakura123'; --connect-expired-password
设置免密登录MySQL
#在家目录上编辑名为my.cnf的隐藏文件
[root@test1 ~]# cd
[root@test1 ~]# vim .my.cnf
[root@test1 ~]# cat .my.cnf
[client] #client指定在客户端
user=root #登录的用户为root
password=sakura123! #密码
[root@test1 ~]# mysql #不需要输入用户密码,直接MySQL登录
......
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
数据库密码破解
#编辑数据库配置文件,在skip-name-resolve(跳过名称解析)下添加一行:skip-grant-tables(跳过所有的授权表)
[root@test1 ~]# vim /etc/my.cnf
[root@test1 ~]# 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
skip-grant-tables #添加此行
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[root@test1 ~]# systemctl restart mysqld
[root@test1 ~]# mysql
.......
mysql> update mysql.user set authentication_string = password('sakura') where User = 'root' and Host = 'localhost'; #修改mysql库里面的user表,将authentication_string(此字段记录的是加密后的密码)改为修改后的密码,匹配的条件是用户名为root,主机为本机(localhost)
[root@test1 ~]# vim /etc/my.cnf #修改配置文件,将之前添加的skip-grant-tables删除
[root@test1 ~]# systemctl restart mysqld
[root@test1 ~]# mysql -uroot -psakura #使用新密码登录
mysql: [Warning] Using a password on the command line interface can be insecure.
......
mysql>
MySQL工具的使用
mysql [] [] [].....
-u(用户名) //指定登录的用户
-p(密码) //小写p,登录用户的密码
-P(端口) //大写P,指定数据库监听的端口
-h(套接字) //指定服务器主机,默认为localhost,一般用IP
-V //查看MySQL的版本
-e"(命令)" //不登陆 mysql,执行sql语句后退出,一般跟双引号,双引号中是sql语句
DDL操作
数据库操作
show databases; //列出当前数据库
create database test1; //创建一个名为test1的数据库
create if not exists database test1; //如果没有test1数据库就创建,有就退出
drop database test1; //删除test1数据库
表操作
创建表需要进入到数据库中创建
use test1; //进入test1数据库
create table text2(id int not null,name varchar(10),age tinyint(3)); //创建名为test2的表,包含id、姓名、年龄字段,id字段为整型并且不能为空,姓名字段为字符串类型并且长度为10,年龄字段为短整型长度为3
desc test2; //查看表的结构
show tables; //查看当前数据库下有哪些表
select * from test2; //查看test2表中的所有内容
drop table test2; //删除test2表
用户操作
用户账户由两部分组成,例如 ‘root‘@’host‘ ,@前面的root表示的是root用户,@后面的host是指定在哪个主机上登录,可以写IP或通配符
create user 'tom'@'localhost' indentified by 'sakura123!'; //创建tom用户允许在本机登录,设置密码为sakura123!
drop user 'tom'@'localhost'; //删除tom用户
查看命令
show tables form test1; //不进入test1数据库,查看test1数据库下有哪些表
desc test1.test; //查看test1库下的test表的数据结构,如果当前在test1库下,可以省略test1
show create table test1.test; //查看test1库下test表的创建过程,如果当前在test1库下,可以省略test1
show table status like 'test'\G; //查看test表的状态,”\G“是将内容安装规范形式显示出来
DML操作
为表添加数据
insert test(id,name,age) values(1,'tom',20),(2,'jerry',21),(3,'black',22);
//为test表添加id、name、age字段,values后跟的是需要添加的数据
查看表的数据
select * from test1.test; //查看test1库下test表的所有内容。如果只需要查看部分内容将*改为对应字段即可,多个字段逗号隔开
select id,name as 姓名,age as 年龄 from test; //这里的as是别名,最后显示出来的内容中的“name”就变为了“姓名”,“age”变为“年龄”
条件判断
where:条件判断
select * from test where name = 'tom' and age = 23; //查看test表的内容,并且要求姓名为tom,年龄为23,多个条件用and隔开,除了“=”,还有< > <= >= != ,and表示和,or表示或者,not表示不
select * from test where age between 20 and 24; //between表示区间,此处意思为查找年龄在20到24(包括20和24)
select * from test where name like '%m'; //like表示模糊查找,查找所有姓名以m结尾的,此处的“%”类似与通配符中的“*”。'%m%'表示带有m的内容,'m%'表示以m开头的内容
添加
alter table test add gender varchar(4) null; //为test表添加gender字段,类型为varchar,长度4,可以为空
更新
update test set age = 100 where name = 'tom'; //更新test表,并且修改name为tom的age字段为100
排序
select * from test order by age; //将test表按照age字段升序排列
select * from test order by age desc; //将test表按照age字段降序排列
select * from test order by age limit 3; //取按照age字段升序排列的test表的前3个数据
select * from test order by age limit 1,4; //按照age字段升序排列test表,跳过前1行数据取后四行
select * from test order by age desc limit 1,2 ; //按照age字段降序排列test表,跳过前1行取后两行
删除
delete from test where id = 1; //删除test表中id为1的数据
delete form test; //删除test表,但是只删除表里的数据,数据可以恢复,结构字段不会删除
truncate test; //删除test表只删除数据,但是无法恢复,结构字段不会删除
DCL操作
权限简介
权限 | 权限级别 | 权限说明 |
CREATE | 数据库、表或索引 | 创建数据库、表或索引权限 |
DROP | 数据库或表 | 删除数据库或表权限 |
GRANT OPTION | 数据库、表或保存的程序 | 赋予权限选项 |
ALTER | 表 | 更改表,比如添加字段、索引等 |
DELETE | 表 | 删除数据权限 |
INDEX | 表 | 索引权限 |
INSERT | 表 | 插入权限 |
SELECT | 表 | 查询权限 |
UPDATE | 表 | 更新权限 |
CREATE VIEW | 视图 | 创建视图权限 |
SHOW VIEW | 视图 | 查看视图权限 |
ALTER ROUTINE | 存储过程 | 更改存储过程权限 |
CREATE ROUTINE | 存储过程 | 创建存储过程权限 |
EXECUTE | 存储过程 | 执行存储过程权限 |
FILE | 服务器主机上的文件访问 | 文件访问权限 |
CREATE TEMPORARY TABLES | 服务器管理 | 创建临时表权限 |
LOCK TABLES | 服务器管理 | 锁表权限 |
CREATE USER | 服务器管理 | 创建用户权限 |
RELOAD | 服务器管理 | 执行flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload等命令的权限 |
PROCESS | 服务器管理 | 查看进程权限 |
授权
grant all on *.* to 'root'@'localhost' identified by 'sakura123!'; //给本机的root用户授权数据库的所有库所有表的所有权限,root的登录密码为sakura123!
取消授权
revoke all on *.* from 'tom'@'localhost'; //取消tom用户在本机上对所有库、所有表的所有权限
查看授权
show grants for root;
show grants for 'root'@'192.168.1.1';
刷新权限
flush privileges; //授权后需要刷新权限才能生效