笔记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 “[e-mail]”; 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 “[e-mail]”; // 修改字段位置，也要原样抄下来 alter table t2 modify email varchar(50) default “[e-mail]” 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