MySQL基础-从零开始
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
- 如果未给此类字段赋值，则自动以当前系统时间赋值。
- datetime
- 日期 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) # 指定字段
- 管理员命令
mysql> show grants for 用户名@"客户端地址" # 管理员查看已有授权用户权限。
mysql> 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、完全备份
- 只恢复某个库时，一般需要先创建对应的库名。
- 在恢复备份时，备份的数据中与现有库有重名时，会覆盖原有数据。
'格式：'
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
[root@h50 ~]# 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
359
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
