当前位置:Gxlcms > 数据库问题 > MySQL-基础

MySQL-基础

时间:2021-07-01 10:21:17 帮助过:18人阅读

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   

人气教程排行