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 中整型 -2 23 ~ 2 23 -1 0 ~ 2 24 -1 int 大整型 -21 31 ~ 2 31 -1 0 ~ 2 32 -1 bigint 极大整型 -2 63 ~ 2 63 -1 0 ~ 2 64 -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 ~ 2 32 -1 double 双精度 0 ~ 2 64 -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 0 ~ 9999-12-31 23:59:59 如果未给此类字段赋值，则会为NULL(空)。 timestamp 范围：1970-01-01 0 ~ 2038-01-19 0 如果未给此类字段赋值，则自动以当前系统时间赋值。 日期 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   

查看更多关于MySQL-基础的详细内容...