DBA基础

一、数据库

1、概述

数据库：存储数据的仓库

数据库服务器：通过一定的存储方式、规则来进行管理数据的服务。

一切皆数据

2、分类

---

• 关系型：Oracle、Mysql、SQL-Server、DB2、MariaDB

• 非关系型：Redis、Memcached、MongoDB、CouchDB、Neo4j、FlockDB

软件名	开源	跨平台	厂商
Oracle	否	是	甲骨文
MySQL	是	是	甲骨文
SQL Server	否	否	微软
DB2	否	是	IBM
Redis	是	是	开源软件
Memcached	是	是	开源软件
MongoDB	是	是	开源软件

3、专业术语

---

• DB（DataBase）：数据库
  • 依照某种数据模型进行组织并存放到存储器的数据集合
• DBMS（DataBase Management System）：数据库管理系统
  • 用来操纵和管理数据库的服务软件
• DBS（DataBase System）：数据库系统，即：DB＋DBMS
  • 指带有数据库并整合了数据库管理软件的计算机系统

二、MySQL基础

1、特点

---

• 特点及应用

  • 适用于中小规模。
  • 关系型数据库：数据与数据之间可以关联操作，可以关联反应

• 应用

数据库服务器单独存在没有意义，需要跟其它服务联动。如：

在LNMP：与NGINX组合

在LAMP：与HTTP组合

2、安装

~]# tar -xvf mysql-5.7.17.tar               			# 解压mysql整合包
~]# yum  -y   install    mysql-community-*.rpm   	# yum安装自动解决依赖


~]# systemctl start mysqld                  			# 启动mysql服务
‘第一次启动，需要初始化数据，会比较慢

3、基础环境

---

文件	说明
/etc/my.cnf	主配置文件
/var/lib/mysql	数据库目录
默认端口号	3306
进程名	mysqld
传输协议	TCP
进程所有者	mysql
进程所属组	mysql
错误日志文件	/var/log/mysqld.log

[root@h50 ~]# ls /var/lib/mysql					# 数据库中实际存储数据的目录。
mysql    performance_schema		sys				# 4个初始库。里面存放着数据库运行的基本信息
information_schema									

[root@h50 ~]# ls /var/lib/mysql/db1			# 创建了一个库db1，就会出现一个目录db1。
mylove.frm   mylove.ibd

# 创建了表：mylove。在库的目录下，会出现2个文件：
mylove.frm：这里存储的是表字段的内容。‘表字段：就是表格中的每列列名。‘
mylove.ibd：存储真正的数据。

4、密码配置

---

4.1、初始密码登录

• 数据库管理员名为root
  • 默认仅允许root本机连接
  • 首次登录密码在安装软件时随机生成
  • 随机密码存储在日志文件：/var/log/mysqld.log

[root@h50 ~]# grep password /var/log/mysqld.log							# 查看随机密码
[root@h50 ~]# mysql -hlocalhost -uroot -p‘mtoa?kd<i6d7‘
‘mtoa?kd<i6d7‘是随机密码，因为有特殊字符，所以用单引屏蔽。


# 连接命令的格式
mysql -h服务器IP -u用户 -p密码

4.2、修改密码

• 修改密码：alter user

mysql> alter user root@"localhost"  identified  by  "新密码";

• mysql的密码默认要求一般为：大小写字母、数字、特殊符号，8位以上。 如果需要设置的简单点，那么就需要修改密码策略

• 密码设置有默认策略，即规则。

密码等级	策略名称	验证方式
0		长度
1	MEDIUM（默认）	长度；数字，小写/大写，特殊字符
2		长度；数字，小写/大写和特殊字符；字典文件

• 修改密码策略

mysql> show variables like "%password%";		
# 查看包含password的变量。variables变量；like像；%:通配符。
+----------------------------------+
|validate_password_policy | MEDIUM |				# 默认策略
|validate_password_length | 8      |				# 长度
------------------------------------


----------------------------------------------------------------------
# 修改密码策略：
mysql> set global validate_password_policy=0;
mysql> set global validate_password_length=6;

‘global：全局‘

[root@h50 ~]# vim /etc/my.cnf								# 永久配置。
	[mysqld]
	validate_password_policy=0
	validate_password_length=6

三、库、表、表记录

1、命令管理

---

1.1、数据库存储流程

• 客户端把数据存储到数据库服务器上的步骤
  • 连接数据库服务器
  • 建库
  • 建表
  • 插入记录
  • 断开连接

1.2、SQL命令规则

• SQL命令不区分大小写（密码、变量值除外）
• 每条SQL命令以分号；结束
• 默认命令不支持TAB键自动补齐（但可以安装另外的软件实现）
• \c：终止SQL命令

1.3、SQL命令分类

• DDL（数据定义语言）：create、alter、drop
• DML（数据操作语言）：insert、update、delete
• DCL（数据控制语言）：grant、revoke
• DTL（数据事物语言）：commit、rollback、savepoint

2、库

---

2.1、初始库

[root@h50 ~]# mysql
mysql> show databases;				# 刚安装MYSQL，本身是有4个初始库的。
+--------------------+
| Database           |
+--------------------+
| information_schema |	# 这个是虚拟库：是映射在硬盘中，不在/var/lib/mysql/中，实际是在内存中的。
| mysql              |	# 授权库
| performance_schema |	# 性能结构库
| sys                |	# 系统元数据库
+--------------------+
4 rows in set (0.00 sec)

‘初始库，不能删除的。也不能在里面创建表，写入数据。

2.2、增

• 库名命名规则
  • 仅可使用：数字、字母、下划线，不能纯数字
  • 区分大小写字母。具有唯一性。
  • 不可使用：指令关键字、特殊字符

mysql> create database 库名;

2.3、删

mysql> drop database 库名;

2.4、查

mysql> show databases;								# 显示已有的库
mysql> use 库名;							 				 # 切换库
mysql> select user();									# 显示连接的用户
mysql> select database();							# 显示当前所在的库。

#database() :内置命令。当前所在的库名。      user()  :内置命令。

3、表

---

3.1、增

mysql> create table 库名.表名 ( 字段名1 类型(宽度) , 字段名2 类型(宽度) ) 
		-> default charset=utf8;

mysql> create table db1.stuinfo ( name char(15),homeaddr char(20) ) default charset=utf8;

default charet=utf8;   						# 指定中文字符集。可给字段赋值中文。

3.2、删

mysql> drop table 库名.表名;				# 删除表
mysql> drop table 表名;						# 如果在当前库下删除，可不用标明库

3.3、改

# rename
mysql> alter table 原表名 rename 新表名;

3.4、查

mysql> show tables;				 			# 查看当前库里面的表

4、表记录

---

4.1、增

• 字段值与字段类型必须匹配
• 字符类型的字段，要用双引号" "括起来。
• 没有赋值的字段使用默认值或自增长。

# 如需要一次性输入多行。一个括号就是一行，括号间用逗号隔开。
mysql> insert into 库名.表名 values(值列表);	

mysql> insert into 库名.表名(字段列表)
		-> values(值列表1),(值列表2);

4.2、删

mysql> delete from 库名.表名;								# 删除全部表记录（删除的是表里面的内容，表还在）。
mysql> delete from 表名 where 匹配条件;			# 指定删除某行。

mysql> alter table 库名.表名 drop 表字段;		# 将表里的其中一列整列删除。

4.3、改

mysql> update 库名.表名 set 字段1=值，字段2=值...字段N=值;		# 批量改！
mysql> update 库名.表名 set 字段="值" where 匹配条件;				# 改！

4.4、查

mysql> desc 库名.表名;							# 查看表的‘表字段‘的详情，也叫：表结构。
mysql> desc 表名;									# 查看当前库下的表，可不写库名。
mysql> desc 库名.表名 \G					# 当表字段过多时，加\G可以列表形式展现

mysql> select * from  库名.表名;													# 显示全部表记录。
mysql> select 字段1...字段N  from 库名.表名;							# 查看指定的列。
mysql> select 字段1...字段N  from 库名.表名 where 条件;		# 查看指定的列和行

四、MYSQL数据类型

信息种类

• 数值型：体重、身高、成绩、工资等类
• 字符型：姓名、工作单位、通信地址等类
• 枚举型：兴趣爱好、性别、专业等类
• 日期时间型：出生日期、注册时间等类

预先定义数据的组织结构，先设计好。

1、字符型

---

定长char

• 即：固定长度。最大字符个数：255
• 不够指定字符个数时在右边用空格补全
• 字符个数超出时，无法写入数据

mysql> create table db1.t2 ( name char(15) , home char(20) );		
# 格式：char(指定字符个数)

变长varchar

• 按数据实际大小分配存储空间。最多65532个字符数。（1～65532）
• 字符个数超出时，无法写入数据。

mysql> create table db1.t3 ( name varchar(15) , home varchar(20) );

大文本类型text/blob

• 字符数大于65535存储时才使用。即：不再限制字符数。
• 一般用于存储大文件。
• 但是，在实际生产环境中，视频、音频、高清图像，不放入数据库。
• 不过会将以上大文件的存储路径等描述信息存入数据库。

2、数值型

---

整数型

类型	名称	有符号范围(有负数)	无符号范围(正整数)
tinyint	微小整数	-128 ~ 127	0 ~ 255
smallint	小整数	-32768 ~ 32767	0 ~ 65536
mediumint	中整型	-223 ~ 223-1	0 ~ 224-1
int	大整型	-2131 ~ 231-1	0 ~ 232-1
bigint	极大整型	-263 ~ 263-1	0 ~ 264-1
unsigned	使用无符号存储范围

mysql> create table t6 (name char(10),age tinyint,ipone smallint );
mysql> create table t7 (name char(10),age tinyint unsigned,ipone char(11));

浮点型

• 格式1：字段名 类型；
• 格式2：字段名 类型（总宽度，小数位数）;

注意：浮点型不再有区分是否有符号，如果是负数，前面加上负数的符号即可。

类型	名称	范围
float	单精度	0 ~ 232-1
double	双精度	0 ~ 264-1

mysql>  create table db1.t3(pay float,id double);
mysql>  create table db1.t4(pay float(5,2),id double(4,2) );
# 一般小数位都选择2。当输入数值为整数时，会自动补齐后面的小数位。

3、日期时间型

---

• 日期时间
  • datetime
    • 范围：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
    • 如果未给此类字段赋值，则会为NULL(空)。
  • timestamp
    • 范围：1970-01-01 00:00:00 ~ 2038-01-19 00:00:00
    • 如果未给此类字段赋值，则自动以当前系统时间赋值。
• 日期 date
  • 范围：0001-01-01 ~ 9999-12-31
  • 格式：yyyymmdd
• 年 year
  • 范围：1901 ~ 2155
  • 格式：yyyy
    • 当使用2位数赋值时：
    • 01～69视为：2001～2069
    • 70～99视为：1970～1999
• 时间 time
  • 格式：HH：MM：SS

mysql> create table t5(name char(10),csnf year,up_time time,birthday date,party datetime);

mysql> insert into t5 values("tom",2019,083000);

时间函数

类型	用途
curtime( )	获取当前的系统时间：09:30:25
curdate( )	获取当前的系统日期：2019-10-09
now( )	获取当前系统的日期和时间：2019-10-09 09:30:25
year( )	获取年
month( )	获取月
day( )	获取日
date( )	获取日期
time( )	获取时间

mysql> select curtime();
mysql> select curdate();
mysql> select now();

# 以下均需要有内置值。才能得出相应的结果。
mysql> select year(now());

mysql> select month(now());
mysql> select month(20191015);

mysql> select day(now());
mysql> select day(20191015);

mysql> select date(now());
mysql> select time(now());

mysql> insert into db1.t3 values( "tom",year(now()),curtime(),curdate(),now() );

4、枚举型（列举）

---

单选：enum

• 格式：字段名 enum(值1,值2,值3....值N);
• 仅能选择一个值
• 字段值必须在列表里选择

mysql> create table db1.t5(name char(15) , sex enum("boy","girl","no") );
mysql> insert into db1.t5 values("bob","girl")

多选：set

• 格式：字段名 set(值1,值2,值3....值N);
• 可选择一个或多个值
• 字段值必须在列表里选择

mysql> create table db1.t6(name char(10),likes set("eat","game","music","money"));
mysql> insert into t6 values("tom","eat,music"),("jack","game,eat,money");

五、表结构

1、约束条件

---

• NULL：是否允许内容为空。默认为允许
• Key：键值
• Default：设置默认值，缺少为NULL
• Extra：额外设置

约束条件非必须写的。

mysql> create table db1.t8 (
  name char(10) not null,
  age tinyint unsigned default 20,
  class varchar(20) not null default "nsd1907",
  pay float(7,2) default 28000
  );


mysql> desc t8;							# 查看表结构中的约束条件
+-------+---------------------+------+-----+----------+-------+
| Field | Type                | Null | Key | Default  | Extra |
+-------+---------------------+------+-----+----------+-------+
| name  | char(10)            | NO   |     | NULL     |       |
| age   | tinyint(3) unsigned | YES  |     | 20       |       |
| class | char(7)             | NO   |     | nsd1907  |       |
| pay   | float(7,2)          | YES  |     | 28000.00 |       |
+-------+---------------------+------+-----+----------+-------+

+ 字段名 ｜ 类型					 | 是否允许为空 | 键值 | 默认值	 | 额外设置|



mysql> create table db1.t9 (
  name char(10) not null,
  homeaddr varchar(30) not null default ""
  );
  
mysql> desc db1.t9;					# 此时，默认值不是为空，而是没有字符。
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| name     | char(10) | NO   |     | NULL    |       |
| homeaddr | char(30) | NO   |     |         |       |
+----------+----------+------+-----+---------+-------+

2、修改表结构

---

格式：alter table 库名.表名 执行动作；

• add：添加字段
• modify：修改字段类型
  • 修改的字段类型不能与已有的数据冲突。
• change：修改字段名
• drop：删除字段(整列全部删除)
• rename：修改表名(表内容不受影响)

add：
# 默认是在最后一个字段添加。如需要中间插入，在后面指定。
alter table 库名.表名 add 字段名 类型 约束条件 [after 字段名 | first]
mysql> alter table db1.t1 add name char(15) first;
mysql> alter table db1.t1 add age tinyint unsigned after name;

modify:
# 还可以改变位置。类型写同样的(需要保留的则写，不要的不写会恢复成默认的状态)，然后在后面加：after  字段名
alter table 库名.表名 modify 字段名 类型 约束条件 [after 字段名 | first]
mysql> alter table t1 modify name varchar(20) not null default "";
mysql> alter table t1 modify name varchar(20) not null default "" after age;

change：
# 既能改字段名，同时还可以改类型。
alter table 库名.表名 change 原名 新名 类型 约束条件 [after 字段名 | first]
mysql> alter table t1 change school xuexiao varchar(30) default "tedu"

drop：删除整个字段(整列)
alter table 库名.表名 drop 字段名;
mysql> alter table db1.t1 drop school;

# 以上命令可以统合使用，多条命令之间用逗号，分隔。
mysql> alter table db1.t1 
		-> add qq char(11),
		-> add ipone char(11),
		-> modify name char(20) not null,
		-> change name user varchar(15) not null default "",
		-> drop xuexiao;


rename
# 修改表名，表内容不受影响
alter table 原表名 rename 新表名;
mysql> alter table t1 rename test1;

六、键值

• 键值的作用：限制赋值。

分类

• index：普通索引
• unique：唯一索引
• fulltext：全文索引
• primary key ：主键
• foreign key：外键

一般实际生产环境中，学用的键值为：index、primary key、foreign key。

1、普通索引index

• 一个表中可以有多个index字段
• 字段的值允许重复，且可以是空NULL
• 通常把作为查询条件的字段设置为index字段
• index字段的标志是MUL

# 格式：
create table 库名.表名 (字段名 类型 约束条件,index(字段名1),index(字段名2) );		# 建表时创建。
create index 索引名 on 表名(字段名);			# 在已有表里创建。索引名自定义。


show index from 表名 \G;							# 查看索引。\G 竖着显示。
drop index 索引名 on 表名；						 # 删除

t1.ibd  存储的就是索引和表内容

2、主键primary key

---

• 字段值不允许重复，且不允许赋NULL
• 一个表中只能有一个primary key 字段。
• 多个字段都作为主键，称为复合主键，必须一起创建。（实际上，还是只有一个primary key）
• 标志是：PRI
• 通常把表中唯一标识记录的字段设置为主键：即作为行号。

2.1、创建

建表时创建：
# 以下两条命令是等价的。
mysql> create table db2.t8(
  	-> id int primary key,					# 直接在字段后注明
  	-> name char(10),
  	-> age int,
  	-> class char(7)
  	-> );
mysql> create table db2.t8(
		-> id int,
		-> name char(10),
		-> class char(7)
		-> primary key(id,name)					# 在最后指明
		-> );

在已有表里添加。注意：如果字段的内容中有重复值或NULL值时，是无法添加主键的。
alter table 表名 add primary key(字段名)	

mysql> alter table t8 primary key(id,name)

2.2、与auto_increment连用

# 格式：
create table 库名.表名 (字段名 类型 约束条件 primary key auto_increment);
alter table 表名 add 字段名 primary key auto_increment;

mysql> create table db2.t3(
  	-> id int primary key auto_increment,
  	-> name char(10),
  	-> age tinyint unsigned,
  	-> class char(7) default "nsd1907"
  	-> );
  	
# 可以为空，会自动从上一行自增值后后写入值。

2.3、复合主键

应用场景：当多条记录时，复合主键字段的值，不能同时相同。

mysql> create table db2.pay(
  	-> name char(10),
 		-> class char(7),
  	-> pay enum("yes","no"),
  	-> primary key(name,class,pay)
  	-> );

2.4、主键的删除

alter table 库名.表名 drop primary key;

# 注意，当有设置自增属性：auto_increment时，必须先去掉。用modify
alter table 库名.表名 modify 表字段 类型 约束条件;

3、外键foreign key

---

• 功能：插入记录时，字段值在另一表字段值范围内选择。
• 规则
  • 表存储引擎必须是innodb
  • 字段类型要一致
  • 被参照字段必须是主键：primary key

# 格式：
create table 表名(
	需要创建的字段列表，
	foreign key(字段名) references 被参照表名(字段名)
	on update cascade						# 同步更新
	on delete cascade						# 同步删除
	)engine=innodb;

• 注意：创建了外键的字段，存在以下问题：

  • 本身字段未设置约束。可以赋空值，可以重复。（与外键不冲突）

• 删除外键

alter table 表名 drop foreign key 外键名;

七、导入、导出

1、概述

---

导入：把系统文件的内容存储到数据库的表里

导出：把数据库表里的记录，保存到系统文件

导入导出时默认检索的目录：

通过查询MYSQL中的变量查看：secure_file_priv

mysql> show variables like "secure_file_priv"
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+

• 如果需要修改默认检索目录。
  • 配置文件中自定义变量secure_file_priv
  • 注意权限。

[root@h50 ~]# vim /etc/my.cnf
[mysqld]
secure_file_priv="/myload"

[root@h50 ~]# mkdir /myload
[root@h50 ~]# chown mysql /myload								# 权限。要允许mysql读写。
[root@h50 ~]# systemctl restart mysqld

2、导入

---

• 导入步骤
  • 把需要导入的文件放入默认检索目录下
  • 创建存储数据的库和表
  • 导入数据
  • 查看数据
• 注意事项
  • 分隔符一定要一致
  • 字段的类型与个数，要与文件一致
  • 导入时，必须写绝对路径

格式：
mysql> load data infile "默认检索目录/文件名"
		-> into table  库名.表名
		-> fields terminated by "列的分隔符"				# 指定每列的分隔符。		
		-> lines terminated by "\n";						# 换行符：一般默认用\n。就是ENTER

• 在登录mysql 状态下，在命令前面加system 即可定义此命令为LINUX命令。

mysql> system cp /etc/passwd /myload/
mysql> system ls /myload 

mysql> load data infile "/myload/passwd"
		-> into table db3.user
		-> fields terminated by ":"
		-> lines terminated by "\n";
mysql> alter table db3.user add id int primary key auto_increment first;

3、导出

---

• 注意事项
  • 导出数据行数由SQL查询决定
  • 导出的是表记录，不包括表字段。

# 格式1：默认分隔符：列是TAB，行是\n
mysql> select 命令 into outfile "检索目录/文件名";

# 格式2：自定义列分隔符
mysql> select 命令 into outfile "检索目录/文件名"
		-> fields terminated by "列分隔符";

# 格式3：自定义行与列的分隔符
mysql> select 命令 into outfile "检索目录/文件名"
		-> fields terminated by "列分隔符"
		-> lines terminated by "\n"

mysql> select id,name,uid,shell from user where id<=3
		-> into outfile "/myload/user1.txt";
		
mysql> select * from user where id<=3
		-> into outfile "/myload/user2.txt"
		-> fields terminated by "###";								# 定义列分隔符为：#

八、匹配条件

1、基本匹配

---

1.1、数值比较

• 字段必须是数值类型。

类型	比较	例子
=	相等	id = 3
!=	不等于	id != 3
>	大于	uid > 3
>=	大于等于	uid >= 3
<	小于	gid < 3
<=	小于等于	gid <= 3

1.2、字符比较、空或非空

• 字段必须是字符类型。并且要加双引号" "

类型	比较	例子
＝	相等	name = "root"
!=	不相等	name != "root"
is null	空	shell is null
is not null	非空	shell is not null

1.3、逻辑匹配

• 多个判断条件时使用。

类型	用途	格式
or	逻辑 或	条件1 or 条件2 or 条件3
and	逻辑 与	条件1 and 条件2 and 条件3
！ 或 not	逻辑 非

1.4、范围匹配、去重显示

• 字符要用引号" "

类型	比较
in （值列表）	在......里
not in （值列表）	不在......里
between 数字 and 数字	在......之间
distinct 字段名	去除重复显示

mysql> select name,uid from user where uid in (1,10,25);
mysql> select name from user where name in ("mysql","bin","httpd","root");
mysql> select name,shell from user 
		-> where shell not in ("/bin/bash","/sbin/nologin");
		
mysql> select * from user where id between 10 and 20;
mysql> select name,uid from user where uid between 10 and 20;

mysql> select distinct gid from user;
mysql> select distinct shell from user;

2、高级匹配

适用于select / update / delete

2.1、模糊匹配

格式：
where 字段名 like ‘通配符‘

通配符
1.下划线 _ ：匹配1个字符
2.百分号 %	：匹配0~n个字符

mysql> select name from user where name like ‘_ _ _ _‘;			# 匹配4个任意字符。
mysql> select name from user where name like ‘%a%‘;					# 包含a的字符。
mysql> select name from user where name like ‘%_ _ _ _%‘;		# 匹配4个及以上字符。

2.2、正则

格式：
where 字段名 regexp ‘正则表达式‘

mysql> select name from user where name regexp ‘^a|t$‘;
mysql> select name from user where name regexp ‘^[abc]‘;
mysql> select name from user where name regexp ‘^..$‘;
mysql> select name from user where name regexp ‘^...$‘;

2.3、四则运算

• 字段必须是数值类型

符号	用途	例子
＋	加	uid + gid
-	减	uid - gid
*	乘	uid * gid
/	除	uid / gid
%	取余数（求模）	uid % gid
( )	提高优先级	( uid + gid ) / 2

mysql> select name,2019-age from user where name="root";
mysql> select name,2019-age bir from user where name="root";
# 2019-gae bir：将前者的结果，以后者为名字显示，相当于是定义别名。

3、操作查询结果

---

3.1、聚集函数

• mysql服务内置的对数据做统计的命令
• 注意：针对的都是查询的字段进行处理。

数据统计函数	用途
avg (字段名)	统计字段 平均值
sum (字段名)	统计字段 和
min (字段名)	统计字段 最小值
max (字段名)	统计字段 最大值
count (字段名)	统计字段 值的个数（有多少行）

select avg(uid) from user;
select sum(uid) from user;
select min(uid) from user;
select max(uid) from user;
select max(uid) from user where id <=10;

select count(*) from user;
select count(name) from user where shell="/bin/bash";

3.2、查询结果排序

• 注意：是针对结果，是结果出来后再做的动作。

‘格式：默认是升序‘
SQL查询 order by 字段名 [asc|desc]
asc:升序
desc:降序


select name,uid from user where id<=20 order by uid;			# 后面不加asc也可，默认就是升序
select name,uid from user where id<=20 order by uid desc;


select gid from user group by gid;					# 分组显示。
select shell from user group by shell;
select distinct shell from user;

3.3、查询结果过滤

# 格式：
SQL查询  having 条件表达式;

select name from user where uid>5  having name in ("mysql","shutdown");

3.4、限制查询结果显示行数

# 格式 ：
SQL查询 limit 数字;									# 显示查询结果前多少条记录
SQL查询 limit 数字1,数字2;					# 显示指定范围内的查询记录
	# 数字1  起始行 (0表示第1行)
	# 数字2  总行数

select name,uid from user where id<10;
select name,uid from user where id<10 limit 1;
select name,uid from user limit 2;									# 查询前2条记录

select name,uid from user limit 0,2;								# 显示：第1行开始，总共2行。
select name,uid from user where id<10 limit 2,2;		# 显示：第3行开始，总共2行。

九、用户管理

用户授权：在数据库服务器上添加新的连接用户。

撤销权限：删除添加的用户对数据的访问权限。

删除用户：把新添加的用户删除。

1、grant 授权

# 格式：
mysql> grant  权限列表  on  库名.表名  to  被授权的用户名@"客户端地址"
		-> identified by "密码"
		-> [with grant option];						# 有授权权限，可选项。被授权用户也可grant授权。
		
--------------------------------------------------------------------------------
# 库名的书写格式：
*.*						# 所有库 所有表
库名.*				# 整个库所有表
库名.表名			# 某个库 的 某个表

# 用户名
授权时自定义，要有一定的标识性；
存储在mysql库的user表里。

# 客户端地址
%								# 所有主机
192.168.4.%			# 网段所有主机
192.168.4.1			# 1台主机
localhost				# 本机

• 权限列表

all						# 所有权限
usage					# 无权限
select,update,insert											# 个别权限
select,update(字段1,... 字段N)							# 指定字段

• 管理员命令

show grants for 用户名@"客户端地址";				# 管理员查看已有授权用户权限。
set password for 用户名@"客户端地址" = password("密码");			# 管理员重置密码

revoke 权限列表 on 库名.表名 from 用户名@"客户端IP";			# 撤销权限。用户还在。
drop user 用户名@"客户端地址"													# 删除授权用户(必须有管理员权限)

• 客户端相关命令

select user();										# 显示登录用户名及客户端地址
show grants;											# 用户显示自身访问权限
set password=password("密码");			# 用户修改连接密码

# password()			：这是一个加密明文密码的函数。

• 授权库：mysql库：存储新添加的用户信息和权限信息。主要表如下
  • user 表：记录已有的授权用户及权限。
  • db 表：记录已有授权用户对数据库的访问权限。
  • tables_priv 表：记录已有授权用户对表的访问权限。
  • columns_priv 表：记录已有授权用户对字段的访问权限。
• 通过修改以上表内容的权限，即可修改授权用户对应的权限。一般情况不通过此种方法修改。

当新接手一个新的数据库时，不清楚授权了哪些用户，授权了哪些权限，可通过以下命令查看到所有的授权信息。
select host,user from mysql.user;
select host,user,db from mysql.db;
select host,user,db,table_name from mysql.table_priv;
# 以上3条命令均可查到到授权的用户及对应的地址。再结合以下命令即可清楚对应的权限：
show grants for 用户名@"客户端地址"

# 如修改权限，即是对以上表中，用户对应的权限值进行修改。
mysql> flush privileges;			# 刷新。修改后要刷新才能生效。

2、root 密码

---

• root密码忘记的情况下，恢复步骤：
  • 停止MySQL服务程序
  • 跳过授权表启动MySQL服务程序
  • 修改 root 密码
  • 以正常方式重启MySQL服务程序

[root@h50 ~]# vim /etc/my.cnf
[mysqld]
... ...
skip-grant-tables								# 跳过授权表。

[root@h50 ~]# systemctl restart mysqld
[root@h50 ~]# mysql
mysql> select user,host,authentication_string from mysql.user;
+-----------+-----------+-------------------------------------------+
| user      | host      | authentication_string                     |
+-----------+-----------+-------------------------------------------+
| root      | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+-----------+-------------------------------------------+
# authentication_string 这是密码字段。里面的值是加密后的密码。
# 利用自带函数 password() 可加密明文密码，再用 update 更改密码的值。

mysql> update mysql.user set authentication_string=password("重置的密码")
		-> where
		-> user="root"  and  host="localhost";
		
		
mysql> flush privileges;
mysql> quit;

• 以操作系统管理员ROOT修改数据库管理员ROOT密码

[root@h50 ~]# mysqladmin -hlocalhost -uroot -p"旧密码"  password "新密码"
[root@h50 ~]# mysqladmin -hlocalhost -uroot -p password					//交互式修改
Enter password: "旧密码"

十、备份

1、概述

---

1.1、物理备份

• 冷备：cp、tar、... ...

[root@h50]# 
cp -r /var/lib/mysql  /root/mysql.bak
scp -r /root/mysql.bak  root@192.168.4.51:/tmp/

[root@h51 ~]#
systemctl stop mysqld
rm -rf /var/lib/mysql
cp -r /tmp/mysql.bak  /var/lib/mysql
chown -R mysql:mysql  /var/lib/mysql
systemctl start mysqld

1.2、逻辑备份

数据备份的策略

• 完全备份：备份所有数据。
• 增量备份：备份上次备份后，所有新产生的数据
• 差异备份：备份完全备份后，所有新产生的数据。

2、完全备份

---

• 只恢复某个库时，一般需要先创建对应的库名。
• 在恢复备份时，备份的数据中与现有库有重名时，会覆盖原有数据。

[root@h50 ~]#
mysqldump -uroot -p密码 库名 > 目录/xxx.sql					# 完全备份
mysql -uroot -p密码 [库名] < 目录/xxx.sql						# 完全恢复

# 备份时库名的表示方式：
--all-databases 或 -A				# 所有库
库名												# 单个库
库名.表名										# 单张表
-B  库1  库2							# 多个库
# 注意：无论备份还是恢复，都要验证用户权限！！！

3、增量备份

---

3.1、binlog 日志

• 也称做：二进制日志
• MySQL服务日志文件的一种
• 记录除查询之外的所有SQL命令
• 可用于数据备份和恢复
• 配置MySQL主从同步的必要条件

3.2、启用binlog日志

• binlog日志默认是不启用，需要通过配置文件启用。

[root@h50 ~]# vim /etc/my.cnf
[mysqld]
log_bin[=目录名/文件名]					# 启用binlog日志
server_id=100								# 指定ID值(1-255)，用来标识当前服务器
max_binlog_size=数值m				# 指定日志文件容量，默认是1G。存满后再生成新的。


# 不指定目录文件的话，默认日志文件是/var/lib/mysql/主机名-bin.000001
# 日志的索引文件：/var/lib/mysql/主机名-bin.index

mysql> show master status;				# 显示当前日志。
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| host51-bin.000001 |      154 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+

• 自定义日志文件目录及名称

[root@h50 ~]# vim /etc/my.cnf
[mysqld]
log_bin=/mylog/mydb						# mylog：目录。mydb：日志文件名的头部
server_id=51
... ...

[root@h50 ~]# mkdir /mylog
[root@h50 ~]# chown mysql:mysql /mylog					# 权限(注意)。
[root@h50 ~]# systemctl restart mysqld
[root@h50 ~]# mysql -uroot -p123456
mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| mydb.000001  |      154 |              |                  |                   |
+--------------+----------+--------------+------------------+-------------------+

3.3、手动生成新的日志文件

1、重启服务
[root@h50 ~]# systemctl resteart mysqld

2、更新日志
[root@h50 ~]# mysql -uroot -p123456 -e ‘flush logs‘
mysql> flush logs;

3、完全备份
[root@h50 ~]# mysqldump -uroot -p123456 --flush-logs db5 > /root/db5.sql


‘每一个新生成的日志文件的偏移量都是154‘

• 删除日志

mysql> purge master logs to "binlog文件名";			# 删除指定编号之前的binlog日志文件

mysql> reset master;      # 删除所有的binlog日志，并重新生成新日志。

3.4、使用日志恢复数据

‘基本格式：
[root@h51 ~]# mysqlbinlog  [选项]  日志文件 | mysql -uroot -p密码

‘基本思路：提取日志文件中的历史操作，管道给mysql命令重新执行一遍。‘

• 选择性恢复数据
  • 思路：提取历史操作时指定某一段时间的记录。

选项	用途
--start-datetime="yyyy-mm-dd hh:mm:ss"	起始时间
--stop-datetime="yyyy-mm-dd hh:mm:ss"	结束时间
--start-position=数字	起始偏移量
--stop-position=数字	结束偏移量

‘默认情况下，通过mysqldump提取的日志中的命令是无法直观查看到具体执行的命令的。此时，就需要修改日志的记录格式，改成混合模式MIXED，使之显示可直观查阅。‘

# 查看日志格式：
mysql> show variables like "binlog_format";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format |  ROW  |
+---------------+-------+

# 日志的3种记录格式：
statement				# 报表模式
row							# 行模式(默认模式)
mixed						# 混合模式

# 修改格式：
[root@h50 ~]# vim /etc/my.cnf
[mysqld]
binlog_format=mixed

[root@h50 ~]# systemctl restart mysqld
更改格式后对原有日志不生效，所以需要查看新生成的日志。

• 恢复的操作：

[root@h50 ~]# mysqlbinlog  /mylog/mydb.000001
... ...
# at 375
#191014 15:50:01 server id 50  end_log_pos 470 CRC32 0x1e71e9ae 	Query	thread_id=32	exec_time=0	error_code=0
SET TIMESTAMP=1571039401/*!*/;
create table db5.a(id int)
COMMIT/*!*/;
... ... 

‘首先，在提取命令中查看，其中：at 375 此处数字即为偏移量，下一行即为时间。COMMIT 是回车。‘

2种方式：
[root@h50 ~]# mysqlbinlog --start-position=154 --stop-position=375 
						> /mylog/mydb.000001 | mysql -uroot -p123456

[root@h50 ~]# mysqlbinlog --start-datetime=2019-10-15 11:11:11 
						> --stop-datetime=2019-10-15 15:15:15 
						> /mylog/mydb.000001 | mysql -uroot -p123456

3.5、总结：增量备份

1、完全备份
2、启用日志
数据库数据增量
3、拷贝日志给备份服务器
4、刷新日志
5、通过日志增量备份
--------------------------------------------------------------------
# 例
[root@h50 ~]#
mysqldump -uroot -p123456 -A > /mybak/all.sql
scp /mybak/all.sql  root@192.168.4.51:/tmp/

mysql> reset master;
mysql> create database new;
mysql> create table new.test (name char(10) );
mysql> insert into new.test values ("natsha"),("bob");
mysql> show master status;
+-------------------+----------+
| File              | Position | 
+-------------------+----------+
| host50-bin.000001 |     845  |
+-------------------+----------+

[root@h50 ~]# scp /var/lib/mysql/host50-bin.000001  root@192.168.4.51:/tmp/

[root@h51 ~]# mysql -uroot -p123456 < /tmp/all.sql
[root@h51 ~]# msyqlbinlog /tmp/host50-bin.000001 | mysql -uroot -p123456

十一、Percona 备份

1、备份工具

---

1.1、常用的MySQL备份工具

• 物理备份，缺点：
  • 跨平台性差
  • 备份时间长、冗余备份、浪费存储空间
• mysqldump ，缺点：
  • 效率较低、备份和还原速度慢、锁表
  • 备份过程中，数据插入和更新操作被阻塞

1.2、XtraBackup备份工具

• percona 包含了两个组件
  • xtrabackup：C程序，支持InnoDB／XtraDB引擎。
  • innobackupex：以Perl脚本封装xtrabackup，支持MyISAM引擎。
• XtraBackup 工具是一款强大的在线热备份工具
  • 备份过程中不锁库表，适合生产环境
  • 由专业组织Percona提供（改进MySQL分支）

1.3、安装percona

~]# yum -y install libev-4.15-1.el6.rf.x86_64.rpm 								# 依赖软件
~]# yum -y install percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm 


]# rpm -ql percona-xtrabackup-24 				# 查看命令
/usr/bin/innobackupex										# 用于备份innodb、xtrdb、myisam引擎的表的命令
/usr/bin/xbcloud
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup											# 用于备份innodb、xtrdb引擎的表

2、innobackupex

---

2.1、常用选项

备份常用选项：

常用选项	含义
--host	主机名
--user	用户名
--port	端口号
--password	密码
--databases	数据库名
--no-timestamp	不用日期命名备份文件存储的子目录名

--databases="库名"					# 1个库
--databases="库1 库2"				# 多个库
--databases="库名.表名"				# 单张表

恢复常用选项：

常用选项	含义
--redo-only	日志合并
--apply-log	准备恢复
--copy-back	拷贝数据
--incremental 目录名	增量备份
--incremental-basedir = 目录名	增量备份、指定上一次备份数据存储的目录名
--incrementel-dir = 目录名	准备恢复数据时，指定增量备份数据存储的目录名
--export	导出表信息
import	导入表空间

2.2、完全备份与恢复

‘格式‘
# 注意：备份目录不需要手动创建，会自动生成。
innobackupex --user 用户名 --password 密码 备份目录名 --no-timestamp
innobackupex --apply-log  备份目录名
innobackupex --copy-back  备份目录名

-------------------------------------------------------------------------
[root@h51 ~]#
systemctl stop mysqld										# 停掉的是需要恢复的服务器，非运行中的。
rm -rf /var/lib/mysql/*									# 恢复时要求目录为空
innobackupex --apply-log  /allbak				# 准备恢复
innobackupex --copy-back  /allbak				# 恢复
chown -R mysql:mysql /var/lib/mysql			# 权限
systemctl start mysqld

2.3、在完全备份中恢复单张表的数据

‘分为7个步骤，一步都不能少：‘

‘1.删除表空间‘
mysql> alter table 库名.表名 discard tablespace;

‘2.导出表信息‘
innobackupex --apply-log --export	数据完全备份的目录

‘3.将表信息复拷贝到对应的库名目录‘
cp 完全备份目录下/mysql/表名.{ibd,cfg,exp}  /var/lib/mysql/库名目录/

‘4.修改权限‘
chown -R mysql:mysq /var/lib/mysql/

‘5.导入表空间‘
mysql> alter table 库名.表名 import tablespace;	

‘6.查询是否恢复成功‘
mysql> select * from 库名.表名

‘删除表信息，避免后续影响‘
rm -rf /var/lib/mysql/库名/表名.{cfg,exp}

2.4、增量备份与恢复

• 增量备份时，必须先有一次备份，通常是完全备份。

‘格式‘
# 增量备份
innobackupex --user 用户名 --password 密码 --incremental 参考的上一次目录 --incremental-basedir=增量目录名 --no-timestamp

# 增量恢复
innobackupex --apply-log --redo-only 目录名 --incrememtal-basedir=目录名		# 准备
innobackupex --copy-back 目录名														# 拷贝恢复

• 应用示例

‘周一，做完全备份：‘
innobackupex --user root --password 123456 /fullbak --no-timestamp

‘周二，做增量备份：‘
innobackupex --user root --password 123456 --incremental /fullbak --incremental-basedir=/new1-dir --no-timestamp

‘周三，做增量备份：‘
innobackupex --user root --password 123456 --incremental /new1-dir --incremental-basedir=/new2-dir --no-timestamp

--------------------------------------------------------------------------

scp -r /fullbak  root@192.168.4.51:/tmp/
scp -r /new1-dir  root@192.168.4.51:/tmp/
scp -r /new2-dir  root@192.168.4.51:/tmp/

---------------------------------------------------------------------------

innobackupex --apply-log --redo-only /tmp/fullbak					# 准备恢复

innobackupex --apply-log --redo-only /tmp/fullbak --incremental-dir=/tmp/new1-dir							# 合并日志。

innobackupex --apply-log --redo-only /tmp/fullbak --incremental-dir=/tmp/new2-dir							# 合并日志。将所有需要恢复的日志合并到同一个日志中。

innobackupex --copy-back /tmp/fullbak

---------------------------------------------------------------------------

chown -R mysql:mysql /var/lib/mysql					# 注意权限。每次拷贝后都需要。
systemctl restart mysqld

• 了解备份目录的备份记录文件信息。

[root@h50 ~]# ls /fullbak
backup-my.cnf   ibdata1   					sys       		xtrabackup_info
db5             mysql     xtrabackup_binlog_info  xtrabackup_logfile
ib_buffer_pool  performance_schema  		xtrabackup_checkpoints

[root@h50 ~]# cat /fullbak/xtrabackup_checkpoints
backup_type = full-backuped					# 显示类型为：完全备份
from_lsn = 0												# 开始备份的位置
to_lsn = 3462594										# 备份结束的位置
last_lsn = 3462603
compact = 0
recover_binlog_info = 0

MySQL-基础

标签：文件信息   热备份   影响   术语   glob   删除   表结构   lnmp   tar -xvf