天下第一数据库mysql

DBA数据管理员

工作内容

- 数据管理：增删改查
- 用户管理授权grant
- 集群管理
- 数据备份恢复
- 监控

什么是数据

在计算机中数据以0，1的二进制形式表示
定义：是对客观事物的性质状态等进行记录，是抽象的

数据库管理系统DBMS

MySQL安装

源码安装

https://downloads.mysql.com/archives/community/

获取源码

wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.40.tar.gz
#解压缩
tar xzvf mysql-5.6.40.tar.gz
#进入目录
cd mysql-5.6.40
#安装依赖软件
yum install -y ncurses-devel libaio-devel cmake gcc gcc-c++ glib
#创建mysql用户
useradd -M -s /sbin/nologin mysql

编译并安装

mkdir /application
cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.6.40 \
-DMYSQL_DATADIR=/application/mysql-5.6.40/data \
-DMYSQL_UNIX_ADDR=/application/mysql-5.6.40/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITH_ZLIB=bundled \
-DWITH_SSL=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLE_DOWNLOADS=1 \
-DWITH_DEBUG=0
#输出结果如果是0 说明上条命令执行没错
echo $?
make -j 2 && make install

创建配置文件

ln -s /application/mysql-5.6.40/ /application/mysql
cd /application/mysql/support-files/
cp my-default.cnf /etc/my.cnf
cp：是否覆盖"/etc/my.cnf"？ y

创建启动脚本

cp mysql.server /etc/init.d/mysqld

初始化数据库

cd /application/mysql/scripts/
yum -y install autoconf
./mysql_install_db --user=mysql --basedir=/application/mysql --
datadir=/application/mysql/data

启动数据库

mkdir /application/mysql/tmp
chown -R mysql.mysql /application/mysql*
/etc/init.d/mysqld start

配置环境变量

vim /etc/profile.d/mysql.sh
export PATH="/application/mysql/bin:$PATH"
source /etc/profile

systemd管理mysql

vim /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000

启动mysql并设置开机自启动

systemctl enable --now mysqld

设置mysql密码并且测试登录

mysqladmin password "123456"
mysql -uroot -p123456
mysql> exit
bye

二进制安装

#下载二进制包并解压
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.40-linuxglibc2.12-x86_64.tar.gz
tar xzvf mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz
# 后面和编译安装差不多
mkdir /application
mv mysql-5.6.40-linux-glibc2.12-x86_64 /application/mysql-5.6.40
ln -s /application/mysql-5.6.40 /application/mysql
cd /application/mysql/support-files
cp my-default.cnf /etc/my.cnf
cp：是否覆盖"/etc/my.cnf"？ y
cp mysql.server /etc/init.d/mysqld
cd /application/mysql/scripts
useradd mysql -s /sbin/nologin -M
yum -y install autoconf
./mysql_install_db --user=mysql --basedir=/application/mysql --
data=/application/mysql/data
vim /etc/profile.d/mysql.sh
export PATH="/application/mysql/bin:$PATH"
source /etc/profile
#需要注意，官方编译的二进制包默认是在 /usr/local 目录下的，我们需要修改配置文件
sed -i 's#/usr/local#/application#g' /etc/init.d/mysqld
/application/mysql/bin/mysqld_safe
#创建systemd管理文件，并且测试是否正常使用
vim /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
vim /etc/my.cnf
basedir = /application/mysql/
datadir = /application/mysql/data
systemctl daemon-reload
systemctl start mysqld
systemctl enable mysqld
mysqladmin -uroot password '123456'
mysql -uroot -p123456

MySQL体系结构管理

C/S模型

mysql自带客户端程序：mysqladmin，mysql，mysqldump
mysql是一个二进制程序，是以后台守护进程的形式

mysql连接方式

tcp/ip形式
mysql -uroot -p123456 -h127.0.0.1
套接字形式
mysql -uroot -p123456 -S /tmp/mysql.sock
套接字有两种类型：文件类型，网络类型
windows navicat连接

mysqldump备份工具

mysql启动流程

先拉其mysqld_safe 然后由它拉起mysqld进程
/etc/init.d/mysqld [start/stop]

不过systemd启动是直接拉起的mysqld，反正最终靠的是mysqld进程。

mysql服务器构成

mysqld服务结构

客户端发出连接请求，通过连接器，然后转给分析器分析sql语句，查询缓存，如果有缓存命中直接返回结果，如果没有到优化器哪里执行计划生成，最后走到执行器操作引擎，返回结果，这些都是在server端的，client有好多存储引擎来端存储数据、

mysql逻辑结构

MySQL数据存放以二维表的形式。

mysql库文件

.ibd 存放数据
.frm 存放表结构

mysql存储引擎

myisam
innodb
查看表引擎
show create table test\G

可以看到MySQL默认的表的存储引擎是myisam，但是我们自己创建的表里面的默认引擎是innodb，后面我们会讲到为什么和两者区别。
里面字符集是latin1 拉丁输入中文可能会乱码，如果向要输入中文可以在创建表的时候把字符集设置为utf-8

mysql用户权限管理

用户管理

# 创建用户指定网段并指定登录密码
create user user01@"192.168.208.%" identified by "123456";
# 查询
select user,host,password from mysql.user;
#进入
mysql -uuser01 -p123456 -h192.168.208.10
#删除用户
drop user user01@"192.168.208.10";
#改密码
mysql> set password=PASSWORD("123456")
mysqladmin -uroot -p123456 password("123456")
grant all on *.* to root@"*" identified by "123456";

用户权限介绍

一般给开发人员增删改查权限即可

# 权限
INSERT,SELECT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS,
FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY
TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE
VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER,
CREATE TABLESPACE

mysql启动关闭流程

在/application/mysql/bin/目录下有两个进程
start---> mysqld_safe ----> mysqld

# 启动 
/etc/init.d/mysqld start
systemctl start mysqld
# 关闭 
/etc/init.d/mysqld stop
# 强制关闭
mysqladmin -uroot -p123 shutdown
systemctl stop mysqld
kill -9 pid
pkill mysqld

mysql配置文件读取顺序

/etc/my.cnf
/etc/mysql/my.cnf
$MYSQL_HOME/my.cnf
defaults-extra
~/.my.cnf #是个隐藏文件最后读的是这个文件
可以通过mysqld_safe --defaults-file=/tmp/a.txt来指定默认配置文件

可以看到默认的/etc/my.cnf指定的环境变量中server_id是0
我们来配置以下/tmp/a.txt然后用mysqld_safe指定默认配置文件

vim /tmp/a.txt
[mysqld]
server_id=9

我们不难看出mysql的server_id变成了9
说明他的默认配置文件改变了。

多实例

分析以下mysql的配置文件

vim /etc/my.cnf
[server] #服务端配置
.......
[client] #客户端配置
user=root
password=123456
port=3306
host=localhost
#更改完配置文件记得重启下mysql进程
mysql就可以直接登录了

好了现在了解了这些之后我们开始多实例案例

mkdir -p /data/330{7..9}
touch /data/330{7..9}/my.cnf
touch /data/330{7..9}/mysql.log
vim /data/3307/my.cnf
 [mysqld]
 basedir=/application/mysql
 datadir=/data/3307/data
 socket=/data/3307/mysql.sock
 log_error=/data/3307/mysql.log
 log-bin=/data/3307/mysql-bin
 server_id=7
 port=3307
 [client]
 socket=/data/3307/mysql.sock
vim /data/3308/my.cnf
 ......
vim /data/3309/my.cnf
同理可得
#初始化3307数据
/application/mysql/scripts/mysql_install_db \--user=mysql \--defaults-file=/data/3307/my.cnf \--basedir=/application/mysql --datadir=/data/3307/data
#初始化3308数据
/application/mysql/scripts/mysql_install_db \--user=mysql \--defaults-file=/data/3308/my.cnf \--basedir=/application/mysql --datadir=/data/3308/data
#初始化3307数据
/application/mysql/scripts/mysql_install_db \--user=mysql \--defaults-file=/data/3309/my.cnf \--basedir=/application/mysql --datadir=/data/3309/data
#连接
mysql -S /data/3307/mysql.sock
#关闭
mysqladmin -S /data/3307/mysql.sock shutdown

至此三个实例都启动好了
好了我们现在开始连接进入mysql mysql -S /data/3307/mysql.sock

如何关闭我试过了如果这个放在前台用ctrl+c打不断

可以用pkill mysqld或mysqladmin -S /data/3307/mysql.sock shutdown来打断

mysql客户端工具

mysql

\c 打断
\s 查看状态、
\q = exit 退出
!ip a 可以执行一些linux命令
\G 格式化json格式

使用\s status可以看出我们连接mysql的时候的连接形式是以套接字的形式连接的mysql -uroot -p123456 -S /tmp/mysql.sock，如果想以tcp/ip形式连接mysql -uroot -p123456 -hlocalhost

当我们输错的时候不想一个一个按回车键删除可以用\c进行打断

\!可以用来在mysql里执行一些linux的命令
## mysqldump 备份工具

#备份
mysqldump -uroot -p123456 --all-databases > /root/all_databasess.db
mysqldump -uroot -p123456 test > /root/test.db
#恢复
mysql> use test;
mysql> source /root/test.db;
mysql -uroot -p123456 test < /root/test.db

开始备份

先查看数据库里面的test库里面有个test01表，现在我们模拟数据不小心被删除了

还原

可以看到我们的数据回来了

mysqladmin 管理工具

用的不怎么多好像

 [root@localhost ~]# mysqladmin -uroot -p1 create hellodb
 [root@localhost ~]# mysqladmin -uroot -p1 drop hellodb
 [root@localhost ~]# mysqladmin -uroot -p1 ping 检查服务端状态的
[root@localhost ~]# mysqladmin -uroot -p1 status 服务器运行状态
[root@localhost ~]# mysqladmin -uroot -p1 status 服务器状态 --sleep 2 --count 
10 每两秒钟显示
⼀次服务器实时状态⼀共显示10次
Uptime:是mysql正常运行的时间。
Threads:指开启的会话数。
Questions： 服务器启动以来客户的问题(查询)数目  （应该是只要跟mysql作交互：不管你查询
表，还是查询服务器状态都问记一次）。
Slow queries：按字面意思是慢查询的意思，不知道musql认为多久才足够算为长查询，这个先放
着。
Opens： 服务器已经打开的数据库表的数量
Flush tables: 服务器已经执行的flush ...、refresh和reload命令的数量。
open tables：通过命令是用的 数据库的表的数量，以服务器启动开始。
Queries per second avg：select语句平均查询时间
[root@localhost ~]# mysqladmin -uroot -p1 extended-status 显示状态变量
[root@localhost ~]# mysqladmin -uroot -p1 variables 显示服务器变量
[root@localhost ~]# mysqladmin -uroot -p1 flush-privileges 数据库重读授权表，等
同于reload
 [root@localhost ~]# mysqladmin -uroot -p1 flush-tables 关闭所有已经打开的表
[root@localhost ~]# mysqladmin -uroot -p1 flush-threds 重置线程池缓存
[root@localhost ~]# mysqladmin -uroot -p1 flush-status 重置⼤多数服务器状态变量
[root@localhost ~]# mysqladmin -uroot -p1 flush-logs ⽇志滚动。主要实现⼆进制和中
继⽇志滚动
[root@localhost ~]# mysqladmin -uroot -p1 flush-hosts 清楚主机内部信息
[root@localhost ~]# mysqladmin -uroot -p1 kill 杀死线程
[root@localhost ~]# mysqladmin -uroot -p1 refresh 相当于同时执⾏flush-hosts 
flush-logs
 [root@localhost ~]# mysqladmin -uroot -p1 shutdown 关闭服务器进程
[root@localhost ~]# mysqladmin -uroot -p1 version 服务器版本以及当前状态信息
[root@localhost ~]# mysqladmin -uroot -p1 start-slave 启动复制，启动从服务器复制
线程
[root@localhost ~]# mysqladmin -uroot -p1 stop-slave 关闭复制线程

sql语句

种类

DDL
数据定义语言
对数据库和表进行操作，包括创建，删除，修改
DCL
数据控制语言
针对用户的权限进行控制包括授权，回收，查看
DML
对表里面的数据进行插入修改删除
数据操作语言
insert update delete
DQL
select
数据查询语言

DDL

对数据库和表进行创建删除和修改

针对库的

create database db01;
drop database db01;
alter database db01 charset utf-8;
show create database db01;

mysql创建出来的库默认字符集是latin1,可以通过alter进行修改，utf8支持中文，latin1输入中文数据可能会出现乱码。

注意：mysql里面是不区分大小写的这点和我们在windows上面不一样，可以通过以下环境变量进行查看

也可以修改环境变量来设置区分大小写，具体百度搜索

针对表的

create stu(
sid int,
name varchar(20),
sage tinyint,
sgender enum("m","f"),
comtime datetime
);

我们来分析以下，创建的学生表，sid 学号是整形，名字是可变长的字符串类型，年龄是tinyint最高到127岁，然后性别是枚举类型，只能输入枚举的字符串，入校时间是日期

可以通过desc 或complain 来查看表结构

插入数据进行测试以下

没毛病
指定属性

create table student(
 sid int not null primary key auto_increment comment '学号',
 sname varchar(20) not null comment '学生姓名',
 sage tinyint unsigned not null comment '年龄',
 sgender enum('m','f') not null default 'm' comment '学生性别',
 cometime datetime not null comment '入学时间'
 )charset utf8 engine innodb;

测试插入数据

不填sid和sgerder也没关系我们在定义表的时候设置了一个可以自增，一个有默认值
修改表的定义

 alter table student rename stu;
 # 修改表名
alter table stu add age int;
 # 添加列和列数据类型的定义
alter table stu add test varchar(20),add qq int;
 # 添加多个列
alter table stu add classid varchar(20) first;
 # 指定位置进行添加列(表首)
 alter table stu add phone int after age;
 # 指定位置进行添加列（指定列）
alter table stu drop qq;
 # 删除指定的列及定义
alter table stu modify sid varchar(20);
 # 修改列及定义（列属性）
alter table stu change phone telphone char(20);
 # 修改列及定义（列名及属性）

默认是在最后面插入，也可以指定

可以通过alter table drop [列名]进行删除某一列定义

alter table student change/modify的区别
modify修改表的列的属性 change修改列属性并且重命名列

DCL

数据控制语言，包括权限的授予回收查看

授权

grant all on *.* root@"192.168.208.%" identified by "123456" with grant option;
show grants for root@"192.168.208.%";

授权之后就可以用root账户进行登录

点击连接测试测试能不能连通

回收权限

revoke

revoke delete on *.* from root@"192.168.208.%";

回收前
[]
(http://101.37.22.37/wp-content/uploads/2023/12/wp_editor_md_567dd68da365f64388654af75c6a193c.jpg)
回收后

可以看到从原来的all变成了少一个delete权限了
可以用show grants for root@"192.168.208.%"来查看权限

DML

数据操作语言
包括对数据的插入，更新，删除

#插入
mysql> insert into stu values(2,"lisi",20,12331,"f",now());
#更新
mysql> update stu set sgender="f" where sname="zhangsan"
update mysql.user set password=PASSWORD('123456') where user='root' and 
host='localhost';
#删除
delete from stu where sid=1;

修改数据

修改密码数据


可以看到第一行的zhangsan就被我们删除了