菜鸟 linux mysql_笔记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