笔记MySQL

搭建数据库服务器
什么是数据库？
存储数据的仓库

部署一台数据库，提供数据库存储服务
1、购买服务器 （硬件主机 云主机 ）
2、安装操作系统： Unix Linux( redhat、ubuntu、centos ) Windows

3、装包（软件包的来源？）
官网下载 XXX.主.次.修改次数 (次版本号是偶数的是稳定版，奇数是测试版)
操作系统光盘自带软件

软件包封包类型
(1)源码 可以自定义配置和安装 必须解决依赖关系
(2)RPM 软件是封装好不可以自定义安装 安装卸载简单 rpm -ivh rpm -e

开源软件还是商业软件？(开源不等于免费)
使用那种版本软件？（提供数据库服务的软件有哪些？）
(1)关系型数据库(RDBMS)：Mysql、 Oracle、 SQL server、DB2
// 按照一定的规则存数据，SQL server只能在Windows上装
(2)非关系型数据(NOSQL)：Redis、Mongodb、Memcached

Mysql特点：
1、开源且跨平台：unix linux windows上都可以安装
2、支持主流的编程语言：
静态网站开发语言：html、css
动态网站开发语言：java、php
运维程序开发语言：python、ruby、perl
3、可移植性好

数据库服务通常和网站服务一起使用：
LAMP、LNMP M（Mysql、Mongodb）

MySQL
软件包mysql-5.7.17.tar // 社区开源版
部署MySQL服务器
// 提供mysql服务的软件有两个，mysql、mariadb，但是不能同时存在
安装软件
]# tar -xf mysql-5.7.17.tar
]# ls .rpm
mysql-community-server-5.7.17-1.el7.x86_64.rpm // 提供服务
mysql-community-client-5.7.17-1.el7.x86_64.rpm // 管理命令
]# rpm -qa | grep -i mariadb // 查找是否有mariadb相关的包
// 如果有
]# rpm -e --nodeps mariadb-libs // 卸载，一定会有这个包
]# systemctl stop mariadb // 先停掉服务
]# rpm -e --nodeps mariadb-server // 删除软件
]#rm -rf /etc/my.cnf // 删除配置文件
]#rm -rf /var/lib/mysql // 删除目录下的内容
]# rpm -Uvh mysql-community-.rpm // 有依赖安装依赖
U：升级安装、v：查看安装过程、h：以#号方式显示安装进度
配置文件
]# ls /etc/my.cnf // 不修改
启动服务
]# systemctl restart mysqld
]# systemctl enable mysqld
查看数据库目录
]# ls /var/lib/mysql
查看端口号
]# netstat -nultp | grep 3306
查看进程号
]# ps -C mysqld
]# ps aux | grep mysqld
SQL指令
— 结构化查询语言
— 数据库定义、查询、操纵、授权语句
基本注意事项
— 操作指令不区分大小写（密码、变量值除外）
— 每条SQL指令以；(分号)结束或分隔
— 不支持Tab键自动补齐
— \c 废气当前编写错的操作指令
— quit 断开数据库连接
常用的SQL操作指令
— DDL 数据定义语言（create、alter、drop）
— DML 数据操作语言（insert、update、delete）
— DCL 数据控制语言（grant、revoke）
— DTL 数据事物语言（commit、rollback、savepoint）

面试题：用过什么SQL指令
答：DDL、DML、DCL、DTL
初次连接数据库、改密码
初始密码在日志文件
]# grep password /var/log/mysqld.log
连接数据库服务器
]# mysql -hlocalhost -uroot -p'查到的密码'
mysql> // 所在位置/var/lib/mysql
修改密码策略(临时)

set global validate_password_policy=0;
set global validate_password_length=6;
修改密码策略(永久)
]# vim /etc/my.cnf
validate_password_policy=0
validate_password_length=6
修改密码
alter user root@"localhost" identified by "123456";
库
— 相当于系统的文件夹
数据库命名规则
— 可以使用数字、字母、下划线，但不能纯数字
— 区分大小写。具有唯一性
— 不可使用指令关键字、特殊字符
查看库
show databases;

Information_schema、mysql、performance_schema、sys
// 系统库，不能删
创建库

create database 库名;
show databases;
// sql命令建库，所属主和组为 mysql
// 在数据库目录下建库，所属主和组为 root
进入/切换库
use 库名;
查看当前所在库
select database();
删除库
drop database 库名；
表
— 相当于系统文件
— *表必须在库里创建
— 先建表结构，再插入记录
— 建表之后数据库目录下会有frm、ibd生成
查看表
show tables;
删除表
drop table 表名;
修改表名
alter table 原表名 rename 新表名；
alter tanle t2 rename stuinfo;

创建表结构

create database studb;
use studb;
create table stuinfo(
-> name char(15),
-> sex char(10),
-> age int(2),
-> tel char(11)
-> );
创建表结构时指定字段值可以为中文
create table t1(name char(4),age int) DEFAULT CHARSET=utf8;
查看表结构
desc stuinfo;
插入表记录
— 第一列叫字段名，其余列叫字段值，默认不能用中文，其余都可以
insert into stuinfo values("bob","boy",21,"88888"),("lucy","girl",20,"66");
字段值设置为中文
DEFAULT CHARSET=utf8;
查看建表的命令
show create table stuinfo;
查看表记录
select * from stuinfo;
修改表记录（批量）
update stuinfo set sex="girl"; // 全部改为girl
删除表记录（所有）
delete from stuinfo;

MySQL数据类型
常见的信息种类
数值型：体重、身高、成绩、工资
字符型：姓名、工作单位、通信地址
枚举型：兴趣爱好、性别
日期时间型：出生日期、注册时间
数值类型
整型、浮点型
类型 大小 范围（有符号） 范围（无符号） 用途
TINYINT 1字节 -128～127 0~255 微小整数
SMALLINT 2字节 -32768～32767 0~65535 小整数
MEDIUMINT 3字节 -2^23~2^23-1 0~2^24-1 中整数
INT 4字节 -2^31~2^31-1 0~2^32-1 大整数
BIGINT 8字节 -2^63~2^63-1 0~2^64-1 极大整数
FLOAT 4字节 单精度浮点数
DOUBLE 8字节 双精度浮点数
DECIMAL 对DECIMAL(M,D),其中M为有效位数，D为小数位数，M应大于D，占用M+2字节

建表结构时指定范围（有符号 | 无符号）：

create table t1(level int signed | unsigned);
desc t1;

浮点类型建表结构：

create table t1(pay float(7,2));
// 7 表示整数位和小数位的总个数，最大数为99999.99

数值类型的宽度 是显示宽度，不能限制字段赋值的大小，字符值的大小由 数据类型决定，int不指定宽度，默认是11，不够宽度默认用空格从左补 位。

例子：不用默认空格，用0补位，只保存正数

create table t2(id int(4) unsigned zerofill);

字符类型
定长：char（字符数） *常用
— 最大长度为255字符
— 不够指定字符数在前边用空格补齐
— 字符数超出时，不能写入数据
— 缺点：浪费存储空间 优点：处理速度快
变长：varchar（字符数）
— 最大长度为65532字符
— 按数据实际大小分配存储空间，存几个是几个，不用空格补齐
— 字符数超出时，不能写入数据
— 优点：节省存储空间
— 缺点：大量写入数据时，占用CPU，访问变慢
枚举类型
// 让字段的值，在规定的范围内选择
enum（值列表） 单选
set（值列表） 多选

create table t1 (name char(10),sex enum(“boy”,”girl”),likes set(“eat”,”play”,”eat”));
日期时间类型
年 year YYYY 2018
日期 date YYYYMMDD 20181010
时间 time HHMMSS 083030
日期时间 datetime YYYYMMDDHHMMSS 20181002103020
timestamp
例子：
create table t1(name char(15),birthday date,work time,s_year year,meetingtime datetime);
insert into t1 values(“bob”,20160520,103030,2016,20200621093030);

当未给TIMESTAMP字段赋值时，自动以当前系统时间赋值，而DATETIME字段默认赋值为NULL；

MYSQL服务时间函数（内置命令）

select now(); // 获取系统日期时间
select year(now()); // 获取系统年份
select month(20150210); // 获取指定月份
例子：
insert into t1 values("bob",date(now()),time(now()),year(now()),now());

管理表结构
// 对已经创建的表的结构做修改
添加字段 add

alter table 表名 add 字段名 字段类型 (约束条件);
alter table t2 add email char(50) default “stu@tedu.cn”;
alter table t2 add stu_num char(9) first,
grade int(3) after email;
修改字段类型
alter table 表名 modify 字段名 字段类型 约束条件;
// 不修改的部分，原样抄下来，否则会还原
alter table t2 modify email varchar(50) default “stu@tedu.cn”;
// 修改字段位置，也要原样抄下来
alter table t2 modify email varchar(50) default “stu@tedu.cn” after stu_num;
修改字段名
alter table 表名 change 原字段名 新字段名 字段类型 (约束条件)；
// 字段类型、约束条件也要抄下来
alter table t2 change stu_num stu_id char(9);
删除字段
alter table 表名 drop 字段名;
alter table t2 drop email;
修改表名
alter table 原表名 rename 新表名；
alter tanle t2 rename stuinfo;

管理表记录
添加新纪录 insert
1.>insert into user values(42,”bob”,”x”,2000,2000,”student”,”/home/bob”, ”/bin/bash”); // 给所有字段赋值
2.>insert into user(name,shell,uid) // 给某些字段赋值 values(“lucy”,”/bin/bash”,1998),(“tom”,”/bin/bash”,1997);
查询新纪录 select

select 字段名列表 from 表where 条件;
select * fom user;
select name,uid,shell from user;
select name uid,shell from user where shell=”/bin/bash”;
修改记录字段值 update
update 表名 set 字段名=值，字段名=值 where 条件：
update user set password=”A”; // 改所有行
update user set password=”1”,uid=”100” where name=”root”;
删除记录 delete
delete from表 where 条件；
delete from t1; // 删除所有记录(慎用，一般不给程序员删的权限)
delete from t1 where name=”root”;

匹配条件
基本匹配条件
数值比较

= < <= !=
select name,id from user where id <=10;
select name,uid,gid from user where uid=gid;

字符比较
= !=

select name,shell from user where shell!=”/bin/bash”;

匹配空
is null is not null

insert into user(id,name) values(51,””),(52,”null”),(53,null);
select id,name from user where name=””;
select id,name from user where name=”null”;
select id,name from user where name is null;

逻辑比较
and(非) or(或) !(非) ()(优先级)

select from user where name="root" and shell="/bin/bash";
select from user where name="root" or shell="/bin/bash";
select name,uid from user where name="root" or name="bin" and uid=1; // and比or优先级高
select name,uid from user where (name="root" or name="bin") and uid=1;

范围内匹配
in（值列表）在..里 between 数1 and 数2（在..之间）
not in（值列表）不在..里 distinct 字段名 (去重显示)

select name from user where name in (“root”,”bin”);
select name,shell from user where shell not in (“/bin/bash”,”/sbin/nologin”);
select * from where uid between 10 and 20;
select distinct shell from user; // 显示字段值有几种类型
select distinct shell from user where uid <=500;

高级匹配条件
模糊查询
— where 字段名 like ‘通配符’
— _ 匹配单个字符、% 匹配0～N个字符

select name from user where name like ‘___’; // 名字包含三个字
select name from user where name like '%a%'; // 名字里有a
正则匹配
— where 字段名 regexp ‘正则表达式’
— 正则元字符 ^ $ . [] |
select name from user where name regexp '^a.t$';
select name from user where name regexp ‘[0-9]’;
四则运算

• • • / %
      update user set uid=uid+1 where id<=10;
      select name,uid,gid,uid+gid as jieguo from user where name="root";
      select name,age,2018-age year from user where name=”root”;
      select name,uid,gid,(uid+gid)/2 from user where name=”root”;
      聚集函数
      — avg（字段名） // 统计字段平均值
      — sum（字段名） // 统计字段之和
      — min（字段名） // 统计字段最小值
      — max（字段名） // 统计字段最大值
      — count（字段名） // 统计字段值个数
      select avg(uid),max(uid),min(uid) from user where id <=10;
      select count(name) from user where shell="/bin/bash";

操作查询结果
group查询分组
— SQL查询 group by 字段名;

select shell from user group by shell;
order by查询结果排序
— SQL查询 order by 字段名 [asc | desc]; //升序、降序,默认从小到大
select name,uid from user where uid <=500 order by uid desc;
select name,uid from user where uid <=500 order by uid;
limit限制查询结果显示行数
— SQL查询 limit 数字; // 显示查询结果的前几行
— SQL查询 limit 数字1，数字2; // 显示指定范围内的行
select name,shell from user where uid <=500 limit 5;
select name,shell from user where uid <=500 limit 5,10;
// 从第六行开始，显示10行
having条件在查询结果里查找数据
— SQL查询 having 条件表达式；
— SQL查询 where 条件 having 条件表达式；
— SQL查询 group by 字段名 having 条件表达式；
select id from user where id <=10 having where name=”bin”;

约束条件
// 作用：控制如何给字段赋值
NuLL 是否允许赋空值

create table t1(name char(10) not null); // 赋值时不能写null
“”不代表空值，代表一个字符
“null”也不代表空值，代表一个值
null表示空
Default 默认值
create table t2(sex enum(“boy”,”girl”) default “boy”);
Extra 额外设置
create table t1(id int(9) primary key auto_increment,name char(10) not null);
KEY 键值
MYSQL键值
普通索引 index
— 给字段值排序的树形结构，排序用算法(btree、b+tree、hash)
// 给字段加了索引后，会排序，当where name=”bob”,可以迅速找到， 否则要从一开始全部查找。
// 优点：加快查询数据的速度。
// 缺点：减慢写入数据时的速度，占用磁盘空间，新写入时还要再重新排序。
// 一个表中可以有多个index字段
// 字段的值允许有重复，也可以赋空值
// INDEX字段的KEY标志是MUL

在已有表中设置INDEX字段

create index 索引名 on 表名(字段名);
删除指定表的索引字段
drop index 索引名 on 表名;
创建时指定index字段
create table t1(name char(10),age(2),index(name),index(age));
删除索引
drop index 字段名on 表名;
查看表名信息
show index from 表名\G;
// 主要看表名.索引名.字段名.算法。默认用btree算法(二叉树算法)
主键 primary key
— 控制如何给字段赋值
// 一个表中只能有一个primary key字段
// 通常与auto_increment连用,让字段的值自动增长,前提(主键,数值)
// 字段值不能重复，且不能为空
// 主键字段的KEY标志是PRI
// 如果有多个字段都作为主键，称为复合主键，必须一起创建
建表时创建主键
1.>create table t1(id int(9) primary key auto_increment,name char(10) not null,age int(2) unsigned default 19);
insert into t1(name,age) values(“tom”,20); // id字段自增1
insert into t1 values(null,"bob",20); // null表示空,也会自增1
2.>create table t1(id int(9),name char(4),primary key(id));

在已有表里创建主键

alter table t2 add primary key(id); // 前提是id没有重复

删除主键

alter table t1 drop primary key;
有自动增长的情况下删除主键的操作
alter table t1 modify id int(9) not null; // 先删除自动增长
alter table t1 drop primary key;
创建复合主键
// 表中的多个字段一起做主键，赋值时，两个主键字段的值不同时重复
例子：同时重复（不可以）
PRI PRI
Name class pay
Bob nsd1804 yes
Bob nsd1804 no
create table t2(name char(4),class char(5),pay enum(“yes”,”no”),primary(name,class));
insert into t2 values(“bob”,”nsd1804”,”yes”);
insert into t2 values(“bob”,”nsd1804”,”no”); //会报错
insert into t2 values(“bob”,”nsd1805”,”yes”);
在已有表里创建复合主键
alter table t2 add primary key(name,class); //两个主键不重复

删除复合主键

alter table t1 drop primary key;

外键 foreign外键
— 让当前表字段的值在另一个表中字段值的范围内选择
// 表的存储引擎必须是innodb
// 字段类型要一致
// 被参照字段必须要是索引类型的一种(primary key)
例子：假如只有三名员工，结果给四个人发了工资，为了防止这种情况给 gz_id加外键，以yg_id作为参考
yginfo gztab
yg_id name gz_id pay
1 bob 1 30000
2 bob 2 40000
3 lucy 3 20000
4 30000
被参考的yginfo

create table yginfo(yg_id int(2) primary key auto_increment,name char(15))engine=innodb;
insert into yginfo(name) values(“bob”);
insert into yginfo(name) values(“bob”);
insert into yginfo(name) values(“lucy”);

创建外键gztab

create table gztab(gz_id int(2),pay float(7,2),primary key foregin key(gz_id) references yginfo(yg_id) on delete cascade on update cascade)engine=innodb;
// 为保持数据一致，同步删除，同步更新
// 设置为主键，不重复发工资，不能为空值
insert into gztab values(1,30000)
.....
insert into gztab values(4,30000)
// 给编号为4的员工存的时候报错，因为yg_id里没有4

删除外键

show create table gztab;
// CONSTRAINT gztab_ibfk_1外键名称
alter table gztab drop foreign key gztab_ibfk_1;

数据导入与导出
// 导出、导入数据 文件默认存储路径为 /var/lib/mysql-files

show variables like “secure_file_priv”;
自定义数据导入导出文件存储的目录
]# mkdir /mydata
]# chown mysql /mydata
]# vim /etc/my.cnf
[mysqld]
secure_file_priv=”/mydata”
]# systemctl restart mysqld

数据导入
— 把系统文件的内容存储到数据库服务器的表里(有格式规律)
// 字段分隔符要与文件内的一致
// 指定导入文件的绝对路径
// 导入数据的表字段类型要与文件字段匹配
// 禁用selinux保护机制

把系统文件拷贝到指定的目录下(在mysql登录状态下执行系统命令)

system cp /etc/passwd /mydata
在系统命令下执行mysql命令
]# mysql -uroot -p123456 -e “sql命令”
例子：mysql -uroot -p123456 -e “show slave status\G”
创建存储文件的表
use db3;
create table user(name char(50),password char(1),uid int(2),gid int(2),comment varchar(150),homedir char(150),shell char(50),index(name));
导入数据
load data infile “/mydata/passwd” into table db3.user fields terminated by “:” lines terminated by “\n”;
数据导出
— 把数据库服务器的表里的记录存储到系统文件里
// 导出的内容由SQL查询语句决定
// 导出的是表中的记录，不包括字段名
// 禁用selinux

SQL查询条件 into outfile “/指定目录/自定义文件名.txt” fields terminated by “分隔符” lines terminated by “\n”;
select * from t1 into outfile “/mydata/t1.txt” // 默认 tab \n

多表查询
复制表
备份表

create table 新表名 sql查询
create table user1 select from user; // 键值不会被复制
create table user3 select from user where 1=2;
// 后面跟一个不成立的条件，只复制表结构，不复制记录
快速建表
create table user2 select name from user order by uid limit 5;

多表查询
笛卡尔集 两个表相乘的积

create table t1 select name,uid,shell from user limit 5;
create table t2 select name,uid,shell,gid from user limit 3;
— select 字段名列表 from 表名列表 where 条件;
select from t1,t2;
select t1.,t2.uid,t2.password from t1,t2 where t1.uid = t2.uid;
嵌套查询
把内层的查询结果作为外层的查询条件
— select 字段名列表 from 表名 where 条件(select 字段名列表 from 表名 where 条件)
select name,uid from user where uid < (select avg(uid) from user); // 找出uid比uid平均值小的用户名字
select name from user where name in (select name from db.t3);
// 查找user表里有哪些名字在db库里的t3表里
连接查询
左连接查询
以左边的表为主显示查询结果
create table t1 select name,uid,shell from user limit 5;
create table t2 select name,uid,shell from user limit 3;
— select 字段名列表 from 表A left join 表B on条件
select from t1 left jion t2 on t1.uid = t2.uid
// 以t1表为主，显示5行
右连接查询
以右边的表为主显示查询结果
— select 字段名列表 from 表A right join 表B on条件
select from t1 right jion t2 on t1.uid = t2.uid
// 以t2表为主，显示3行
phpMyAdmin-2.11.11-all-languages.tar.gz软件
(MySQL图形管理工具)
常见的MySQL管理工具
类型 界面 操作系统 说明
mysql 命令行 跨平台 MySQL官方bundle包自带
MySQL-Workbench 图形 跨平台 MySQL官方提供
MySQL-Front 图形 Windows 开源，轻量级客户端软件
phpMyadmin 浏览器 跨平台 开源，需LAMP平台
Navicat 图形 Windows 专业、功能强大、商业版

部署LAMP/LNMP运行环境
// 安装 httpd、mysql、php、php-mysql
]# yum -y install httpd
]# systemctl start httpd
]# systemctl enable httpd

]# tar -xf mysql-5.7.tar.gz
]# rpm -Uvh mysql-community-*
]# systemctl start mysqld
]# systemctl enable mysqld

// phpMyadmin是用php语言开发的,想要服务器运行就要装这两个包
]# yum -y install php // 用来解释php代码的程序
]# yum -y install php-mysql
// 用来连接mysql数据库的连接命令的软件包
]# systemctl restart httpd
// 加载安装的php模块
安装软件
]# tar -zxvf phpMyAdmin-2.11.11-all-languages.tar.gz
// 解压即可
]# ls phpMyAdmin-2.11.11-all-languages
// 网页文件
]# mv phpMyAdmin-2.11.11-all-languages /var/www/html/phpadmin
// 拷贝到httpd默认目录下，可以直接访问phpadmin

创建配置文件
// 指定管理数据库服务器
]# cd /var/www/html/phpadmin/
]# cp config.sample.inc.php config.inc.php
]# vim config.inc.php
17 $cfg['blowfish_secret'] = '自定义'; // 随便写东西
31 $cfg['Servers'][$i]['host'] = 'localhost'; // 不用修改
客户端访问
]# firefox http://192.168.4.50/phpadmin
// 用户名、密码和命令行登录mysql时相同
修改、恢复root密码
命令行改密码（操作系统管理员有权限）
]# mysqladmin -hlocalhost -uroot -p旧密码 passwrod “新密码”
恢复登录密码（操作系统管理员有权限）
// 忘记密码时

修改配置文件
]# vim /etc/my.cnf 追加写入
skip_grant_tables // 跳过授权库

重起mysqld服务
]# systemctl restart mysqld

登录mysql修改密码

update mysql.user set authentication_string=passwrod(“123456”) where user=”root” and host=”localhost”;
// password() 加密函数
更新权限
flush privileges;

注释配置文件中添加的那行，重起mysqld服务

用户授权
// 在数据库服务器上添加连接时使用的用户名
创建授权
— grant 权限列表 on 数据库名 to 用户名@”客户端地址” identified by “密码” [with grant option];
// with grant option 让添加的用户连接服务器后，也有授权权限
权限列表表示方式
// 所有权限 all
某种权限 select,insert,delete
客户端地址表示方式
// 所有主机 %
网段 192.168.4.%
指定主机 192.168.4.51
本机 localhost
数据库名表示方式
// 所有库、所有表 .
库下的所有表 库名.*
某张表 库名.表名

grant select,update(name,uid) on gamedb. to admin@"192.168.4.%" identified by "123456";
grant all on .* to root@”192.168.4.51” identified by “123456” with grant option; // 对所有库表有完全权限，且有授权权限
服务器查看授权信息
// 授权库mysql库记录授权信息，使用不同的表记录不同的授权信息
use mysql; // 进入mysql库，有如下四个表
user 已经添加的连接用户
db 记录已添加的连接用户对库的访问权限
tables_priv 记录已添加的连接用户对表的访问权限
columns_priv 记录已添加的连接用户对表中字段的访问权限
select user,host from mysql.user;
— show grants for 用户@”客户端地址”;
show grants for root@”localhost”;
客户端连接后查看信息
select @@hostname; // 客户端查看自己连接的主机的名字
select user(); // 显示自己以什么名字连接的服务器
select grants; // 客户端查看自己的被授权权限

做一个授权模版
查看代理信息

show variables like "%proxy%";
将代理授权打开proxy(临时)
set global check_proxy_users=”on”;
set global mysql_native_password_proxy_users=”on”;
将代理授权打开proxy(永久)
]# vim /etc/my.cnf
[mysqld]
check_proxy_users=on
mysql_native_password_proxy_users=on
]# systemctl restart mysqld
创建用户mysqladmin
create user mysqladmin identified by “123456”;
给用户mysqladmin授权
grant all on bbsdb.* to mysqladmin@"%";
将用户mysqladmin的权限映射给用户will、tom
create user will identified by “123456”;
create user tom identified by “123456”;
grant proxy on mysqladmin to will;
grant proxy on mysqladmin to tom;

权限撤销
revoke撤销
— revoke 某权限 on 数据库名 from 用户名@”ip”

select user,host from mysql.user;
show grants for 用户名@”ip”;
revoke grant option on . from root@”192.168.4.51”;
show grants for root@”192.168.4.51”; // 1、看权限信息
修改记录改权限
修改记录改权限 (将N改为Y，Y改为N)
select * from mysql.user where user=”root” and host=”192.168.4.51”\G; // 2、看权限信息
update mysql.user set Delete_priv=”N” where user=”root” and host=”192.168.4.51”;
flush privileges; // 刷新权限
删除授权用户
— drop user 用户名@”ip”;
select user,host from mysql.user;
drop user root@”192.168.4.52”;
权限说明
RELOAD // 有重新载入授权 必须拥有reload权限，才可以执行flush
FILE // 导入、导出数据
REFERENCES // 创建外键
SUPER // 关闭属于任何用户的线程
LOCK TABLES // 允许使用LOCK TABLES语句
EXECUTE // 执行存在的Functions,Procedures
REPLICATION SLAVE // 从主服务器读取二进制日志
REPLICATION CLIENT //允许在主/从数据库服务器上使用show status
CREATE ROUTINE // 创建存储过程
ALTER ROUTINE // 修改存储过程
EVENT // 有操作事件的权限
TRIGGER // 有操作触发器的权限
授权用户密码的修改
授权用户连接后修改连接密码
set password=password(“新密码”);
管理员重置授权用户的连接密码
set password for 用户名@”客户端地址”=password(“新密码”);
MySQL存储引擎
什么是存储引擎？

• MySQL服务软件自带的功能程序，处理表的处理器
• 不同的存储引擎有不同的功能和数据存储方式
  工作中建表时如何决定表使用存储引擎
  执行写操作多的表适合使用innodb存储引擎，这样并发访问量大。
  // 当有人只修改一行的话，如果用innodb，只锁一行，别人可以同时 访问这张表，如果用myisam，锁一张表的话，要等第一个改完， 才能第二个人访问。
  执行查询操作多的表适合使用myisam存储引擎，节省系统资源。
  // 当select * from t1 where id <=10,，如果用innodb，会给前10 行加锁，每一行都要锁一遍，占用cup，如果用myisam，锁一张 表，就只加锁一遍。
  常见存储引擎（MyISAM、InnoDB）
  MyISAM特点
  1、支持表级锁（客户端连接数据库服务器后对表的数据做访问时，若表的 存储引擎是myisam时，会给整张表加锁）
  2、不支持事务和事务回滚
  3、每个表对应三个表文件
  表.frm // 对应表结构 desc 表
  表.MYD // 对应表数据
  表.MYI // 对应表的index索引信息
  InnoDB特点
  1、支持行级锁（客户端连接数据库服务器后对表的数据做访问时，若表的 存储引擎是innodb时，只给表中被访问的行加锁）
  2、支持事务和事务回滚
  事务日志文件会记录所有的sql操作,启动mysql服务时自动创建
  ]# cd /var/ww/mysql
  1、ibdata1 // 记录未执行成功的sql命令
  2、ib_logfile0 // 记录执行成功的sql命令
  3、ib_logfile1 // 记录执行成功的sql命令
  3、每个表对应两个表文件
  表.frm // 对应表结构 desc 表
  表.ibd // 对应表数据和表的index索引信息
  锁粒度（锁表的范围）
  行锁 表锁 页级锁

锁类型
读锁(共享锁)：支持并发读
当对一张表做查询(select)操作时，会加读锁
写锁(排他锁、互斥锁)
当对一张表做写(insert update delete)操作时，会加写锁

事务 （Transcations）
— 一次sql操作从建立连接到操作完成断开连接的过程称作事务
// 支持事务的表可以做事务回滚
事务回滚
一次sql操作只要有任意一步没有成功，会恢复所有操作 // 转账例子

事务特点（ACID）* 面试必问
Atomic：原子性
— 事务的整个操作是一个整体，要么全部成功，要么全部失败
Consistency：一致性 — 事务操作的前后，表中的记录没有变化
Isolation：隔离性 — 事务操作是相互隔离不受影响的
Durability：持久性 — 数据一旦提交，不可改变，永久改变表数据
查看存储引擎

show engines; // 默认为InnoDB
建表时指定存储引擎
create table 表名(id int(2)) engine=存储引擎名;
修改默认存储引擎
修改配置文件
]# vim /etc/my.cnf 追加写入
default-storage-engine=myisam
重起mysqld服务
]# systemctl restart mysqld
修改表使用的存储引擎
— alter table 表名 engine=存储引擎名; // 一般不会建表以后修改
关闭自动提交(默认自动提交)
show variables like "autocommit";
set autocommit=off;
手动提交
commit;
回滚操作
rollbak;
数据备份与恢复
// 方式分为物理备份和逻辑备份，逻辑备份又分为完全备份和增量备份，完 全备份又分为mysqldump和innobackupex，两种方式增量备份 又分启用binlog日志和第3方软件percona提供的 innobackupex命令两种方式。
// 增量备份：只备份新产生的数据，备份上次备份后，所有新产生的数据
// 差异备份：只备份新产生的数据，备份完全备份后，所有新产生的数据

// 生产环境下一般用完全备份+增量备份
物理备份
缺点：
— 跨平台性差
— 备份时间长、冗余备份、浪费存储空间
(50)备份库和表对应系统文件
1、]# cp -r /var/lib/mysql/ /root/mysql.bak
2、]# tar zcvf /mysql.tar.gz /var/lib/mysql/
]# scp -r /root/mysql.bak 192.168.4.51:/root
物理恢复
(51)把备份文件拷贝回对应的数据库目录，所有者和组改为mysql，重 起数据库服务器
]# systemctl stop mysqld
]# rm -rf /var/lib/mysql
]# cd /root/mysql.bak
mysql.bak]# cp -r /var/lib/mysql
]# chown -R mysql.mysql /var/lib/mysql
]# systemctl start mysqld

完全备份mysqldump
缺点：
— 效率较低，备份和还原速度慢
— 备份过程中，数据插入和更新操作会被挂起
执行备份命令时，根据备份的库表生产对应的sql命令，把命令保存到指定的文件里。（备份时会锁表）
备份所有数据的3个对象：
一台数据库服务器的所有数据
一个库里的所有表
一张表里的所有数据
— ]# man mysqldump // man帮助
— ]# mysqldump -uroot -p123456 库名 > [目录]/文件名.sql
库名表示方式：
--all-databases 或 -A // 所有库、所有表
库名 // 备份库下的所有表
库名 表名 // 备份一张表的所有记录
-B 库1 库2 // 备份几个库的数据
例子(50)
]# mkdir /bakdir
]# mysqldump -uroot -p123456 db55 --master-data=1 > /bakdir/db55.sql
// --master-data=1解决在备份时有人写入数据，会丢一部分数据, 加入这句会记录当时的偏移量，配置从库时，写这个偏移量不丢 失那部分数据
]# vim /bakdir/db55.sql
// CHANGE MASTER TO MASTER_LOG_FILE='db60.000001', MASTER_LOG_POS=980;
这行不会注释，如果--master-data=2，这行会被注释
]# scp /bakdir/db55.sql 192.168.4.51:/root
完全恢复mysql
执行保存sql命令的备份文件，把数据写回数据库里。（恢复时会锁表）
1、— ]# mysql -uroot -p123456 库名 < [目录]/文件名.sql
2、— >source [目录]/文件名.sql
例子(51)
先创建一个库名为db55的数据库（提前建库、建表）

create database db55;
quit;
再完全恢复
1、]# mysql -uroot -p123456 db55 < /root/db55.sql
2、> use db55;
source /root/db55.sql
增量备份启用binlog日志文件
日志介绍：MySQL服务自带，是服务日志文件中的一种（默认没有启用）， 记录除查询之外的sql命令
日志文件记录格式（三种）
statment：每一条修改数据的sql命令都记录再binlog日志中
row：不记录sql语句上下文相关信息，仅保存哪条记录被修改
mixed：两种方式的结合
查看当前日志记录格式
show variables like "binlog_format"; // 默认row
启用日志
]# vim /etc/my.cnf
[mysqld]
server_id=50
log-bin=/logdir/db50
// log-bin后面可以不写，不写就是默认目录和名称，一般 目录和文件名一起写，只写名字也可以，目录还是默认 数据库目录，只写目录(/logdir)，就会认为/ 是目录， logdir是文件名
binlog-format=”mixed”
重起服务
]# systemctl restart mysqld

查看日志文件 和 内容
]# ls /var/lib/mysql/host50-bin.*
// host50-bin.00001 日志文件 >500M会变为00002
// host50-bin.index 索引文件
]# mysqlbinlog /var/lib/mysql/host50-bin.00001
增量恢复binlog日志文件
日志记录sql命令格式（两种）
mysqlbinlog 选项 /logdir/db50-bin.00001 | mysql -uroot -p123456 // 中间包含一个commit
偏移量：--start-position= --stop-position
时间点：--start-datetime=”yyyy-mm-dd hh:mm:ss”
--stop-datetime=”yyyy-mm-dd hh:mm:ss”
例子：

use db55;
delete from t2 where id in (1,2); // 模拟误删除
]# mysqlbinlog /logdir/db50-bin.00001
// 查看创建时的偏移量或时间点
]# mysqlbinlog --start-position=1702 --stop-position=1831 /logdir/db50-bin.00001 | mysql -uroot -p123456
// mysql进去多了id=1
]# mysqlbinlog --start-datetime="2018-08-24 15:18:31" --stop-datetime="2018-08-24 15:18:34" /logdir/db50-bin.00001 | mysql -uroot -p123456
// mysql登录进去查看多了id=2
手动生成新的日志文件（默认大于500M自动生成）
// 新的记录会记录在编号最大的日志文件里
1、]# mysqldump -uroot -p --flush-logs db55 > /bakdir/db55.sql
// --flush-logs完全备份后生成新的日志文件
2、> flush logs; // 执行一次生成一个新的日志文件
3、]# mysql -uroot -p123456 -e “flush logs”
查看正在使用的日志文件
show master status;
删除已有的日志文件
purge master logs to “db50-bin.000004” // 删除4之前所有的
reset master;

]# rm -rf /logdir/*

percona-xtrabackup-24-2.4.7-1.el7.rpm
libev-4.15-1.el6.rf.x86_64.rpm // 依赖包
一款强大的在线热备份工具（xtrabackup工具）
— 备份过程中不锁库表，适合生产环境
— 由专业组织Percona提供
主要包含两个组件
— xtrabackup：C程序，支持InnoDB/XtraDB
— innobackupex：以Perl脚本封装xtrabackup，还支持MyISAM
Innobackupex增量备份时一定是innodb存储引擎
第3方软件完全备份和恢复

安装软件包(percona、libev)
]# rpm -ivh percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm
// 安装时有依赖就安装依赖，so代表模块，不是软件名
]# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm
]# rpm -ql percona-xtrabackup-24 // 查看安装的列表

查看备份帮助
]# man innobackupex
Innobackupex基本选项
--host 主机名
--user 用户名
--port 端口号
--password 密码
--no-timestamp 不用日期命名备份文件存储的子目录名
--databases 数据库名
// 什么也不写 所有库
--databases=“库名” 单个库
--databases=“库1 库2” 多个库
--databases=“库.表” 单个表

完全备份innobackupex
]# innobackupex --user root --password 123456 --databases=" mysql sys performance_schema gamedb" /allbak --no-timestamp
// 用innobackupex完全备份时的一个缺点时，恢复时要求数据库目 录/var/lib/mysql为空，所以备份的时候要将系统的三个库也备份 上，有一个是虚拟库，不用备份
// /allbak这个目录，如果之前有，要求里面也为空，不然会报错，没 有就会自动创建
]# ls /allbak
// 里面包含两部分，备份的数据和日志文件，配置文件就是备份数据时 的备份信息，统称日志文件
— xtrabackup_checkpoints
// 记录当前备份目录的备份类型和日志序列号的范围（lsn）
from_lsn=
to_lsn=
// 序列号对应的sql命令存放在xtrabackup_logfile里

]# cat /allbak/xtrabackup_checkpoints // 类型是full-backuped

完全恢复innobackupex
准备恢复数据（回滚备份目录下的日志信息）
]# innobackupex --user root --password 123456 --database=”mysql sys performance_schema gamedb” --apply-log /allbak
]# cat /allbak/xtrabackup_checkpoints // 类型是full-prepared

停掉服务，清空数据库目录
]# systemctl stop mysqld
]# rm -rf /var/lib/mysql/*
]# ls /var/lib/mysql

把备份目录下的数据拷贝回数据库目录下
]# innobackup [--user root --password 123456] --databases=”mysql sys performance_schema gamedb” --copy-back /allbak

修改所属主、组
]# chown -R mysql.mysql /var/lib/mysql

启动mysqld服务
]# systemctl start mysqld
增量备份innobackupex
// Innobackupex增量备份时表一定是innodb存储引擎

首先有一次完全备份
]# innobackupex --user root --password 123456 --databases=”sys mysql performance_schema gamedb” /fullbak --no-timestamp

完全备份后向表中存储新数据

insert into gamedb,a values(666);
insert into gamedb,a values(888);

对数据做增量备份
]# innobackupex --user root --password 123456 --incremental /new1dir --incremental-basedir=/fullbak --no-timestamp
// incremental 增量备份数据存放的目录
// incremental-basedir 以上次备份的目录为基准
// /newdir目录要求为空，没有此目录时自动创建

]# ls /new1dir 增量备份目录下有.meta 完全备份没有

]# cat /allbak/xtrabackup_checkpoints //类型是full-incremental

增量备份后向表中再次存储新数据

insert into gamedb,a values(456);
insert into gamedb,a values(789);

对数据做增量备份
]# innobackupex --user root --password 123456 --incremental /new2dir --incremental-basedir=/new1dir --no-timestamp

]# ls /new2dir

增量恢复innobackupex
// 将完全备份和两次增量备份的日志合并在一起(才是所有的数据)

准备恢复数据（回滚备份目录下的日志信息） 从最原始备份的那一次开始恢复
]# innobackupex --user root --password 123456 --apply-log --redo-only /fullbak/
]# innobackupex --user root --password 123456 --apply-log --redo-only /fullbak/ --incremental-dir=/new1dir
]# innobackupex --user root --password 123456 --apply-log --redo-only /fullbak/ --incremental-dir=/new2dir

停掉服务，清空数据库目录
]# systemctl stop mysqld
]# rm -rf /var/lib/mysql/*
]# ls /var/lib/mysql

把备份目录下的数据拷贝回数据库目录下
]# innobackupex --copy-back /fullbak/

修改所属主、组
]# chown -R mysql.mysql /var/lib/mysql/

启动mysqld服务
]# systemctl start mysqld

使用innobackupex完全备份文件恢复单个表
// 恢复单个表时不需要清空数据库目录，所以备份时也不需要备份系统库， 但是需要提前把表结构建出来
备份gamedb库
]# innobackupex --user root --password 123456 --databases="gamedb" /gamedbdir --no-timestamp
模拟a表被删除

drop table gamedb.a;
提前把a表的表结构建出来
create table gamedb.a(id int(2));
删除表空间(.ibd文件)
alter table gamedb.a discard tablespace;
导出表信息
]# innobackupex --user root --password 123456 --apply-log --export /gamedbdir/ // 多出了 表.cfg、表.exp、表.ibd
将三个文件拷贝到gamedb目录下
]# cp /gamedbdir/gamedb/a.{cfg,exp,ibd} /var/lib/mysql/gamedb
修改导入内容的所属主、组
]# chown mysql.mysql /var/lib/mysql/gamedb/a.*
导入表空间
alter table gamedb.a import tablespace; 恢复完成

mysqldump完全备份脚本
]# vim /root/db55.sh
#!/bin/bash
if [ ! -e /bakdir ];then
mkdir /bakdir
fi
x=date +F
mysqldump -uroot -p密码 db55 > /bakdir/db55_${x}.sql
]# chmod +x /root/db55.sh
]# /root/db55.sh
]# ls /bakdir

]# ctrontab -e
00 23 1 /root/db55.sh &> /dev/null

监视从库IO线程和SQL线程脚本
]# mkdir /root/bin
]# vim /root/bin/checkslave.sh
#!/bin/bash
io=mysql -uroot -p123456 -e "show slave status\G" 2> /dev/null | grep -i Slave_IO_Running: | head -1 | awk '{print $2}'
sql=mysql -uroot -p123456 -e "show slave status\G" 2> /dev/null | grep -i Slave_SQL_Running: | tail -1 | awk '{print $2}'
echo $io
echo $sql
if [ $io == "Yes" ];then
echo "io is running"
else
echo "io is down"
fi
if [ $sql == "Yes" ];then
echo "sql is running"
else
echo "sql is down"
fi

Innobackupex完全、增量备份脚本（一起用）
提取年月日 ]# date +%F
提取年 ]# date +%Y
提取月 ]# date +%m
提取日 ]# date +%d
Innobackupex完全备份脚本
]# vim /root/allbak.sh
#!/bin/bash
day=date +%F
innobackup --user root --password 123456 /fullbak_${day} --no-timestamp
]# chmod +x /root/allbak.sh
]# /root/allbak.sh

]# crontab -e
00 23 1 /root/allbak.sh
Innobackupex增量备份脚本
]# vim /root/newallbak.sh
#!/bin/bash
jt=date +%d
zt=expr $jt - 1
m=date +%m
Innobackupex --user root --password 123456 --incremental /new${m}${jt}dir --incremental-basedir=/new${m}${zt}dir --no-timestamp
]# chmod +x /root/newallbak.sh
]# /root/newallbak.sh

]# crontab -e
00 23 1 /root/newallbak.sh

转载于:https://blog.51cto.com/13858237/2164637