标签（空格分隔）： Linux 运维 MySQL
sql文件hellodb.sql
架构
MySQL被设计为一个单进程多线程架构的数据库.其数据库实例在系统上的表现就是一个进程.
这张图大致将MySQL分为三层(模型)从上到下依次为:
❶ 视图层: 即Connectors
❷ 逻辑层: Pluggable Storage Engines以上部分
❸ 物理层: Pluggable Storage Engines及以下部分即都是文件形式(插件式存储引擎)
具体说明如下
❶ Connectors指的是不同语言中与SQL的交互
❷ Management Serveices & Utilities： 系统管理和控制工具
❸ Connection Pool: 连接池。
管理缓冲用户连接，线程处理等需要缓存的需求
❹ SQL Interface: SQL接口。
接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface
❺ Parser: 解析器。
SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。
主要功能：
A. 将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的
B. 如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的
❻ Optimizer: 查询优化器。
SQL语句在查询之前会使用查询优化器对查询进行优化。他使用的 是“选取-投影-联接”策略进行查询。
用一个例子就可以理解： select uid,name from user where gender = 1;
这个select 查询先根据where 语句进行选取，而不是先将表全部查询 出来以后再进行gender过滤
这个select查询先根据uid和name进行属性投影，而不是将属性全部取 出以后再进行过滤
将这两个查询条件联接起来生成最终查询结果
❼ Cache和Buffer： 查询缓存。
如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。
这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key 缓存，权限缓存等
❽ Engine ：存储引擎。
存储引擎是MySql中具体的与文件打交道的子系统。也是Mysql最具有特色的一个地方。
Mysql的存储引擎是插件式的。它根据MySql AB公司提供的文件访问层的一个抽象接口来定制一种文件访问机制（这种访问机制就叫存储引擎）
现在有很多种存储引擎，各个存储引擎的优势各不一样，最常用的 MyISAM,InnoDB,BDB
默认下MySql是使用MyISAM引擎，它查询速度快，有较好的索引优化和数据压缩技术。但是它不支持事务。
InnoDB支持事务，并且提供行级的锁定，应用也相当广泛。 Mysql也支持自己定制存储引擎，甚至一个库中不同的表使用不同的存储引擎，这些都是允许的。
执行流程
配置文件
安装MySQL或MariaDB后执行# my_print_defaults命令可以查看其配置文件加载顺序,CentOS7中如下:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf
越靠后的文件的优先级越高
数据类型
整数
浮点数
日期时间
字符串
文本
二进制
ENUM类型
即枚举类型.使用格式: FIELD_NAME enum('VALUE1', 'VALUE2', ...)
枚举类型的取值范围以列表的形式指定了,而且其值只能取列表中的一个元素,列表最多有65535个.同set一样其数据都不是直接将数据存入数据库,而是将其列表中的编号存入数据库.
SET类型
在创建表时,set类型的取值范围就以列表的形式指定了.使用格式:FIELD_NAME set('VALUE1','VALUE2',...);set类型最多只能有64个元素
字符集
mysql> show character set;
事务
事务就是一组原子性的查询语句,也即将多个查询当作一个独立的工作单元.
- A: 原子性
- C: 一致性
- I: 隔离性
- D: 持久性
事务常用的命令,需要关闭Innodb的autocommit功能
mysql> start transaction; # 启动事务
mysql> savepoint IDENTIFIER; # 设置存档
mysql> rollback | rollback to IDENTIFIER; # 回滚[到指定存档]
mysql> commint # 提交事务
隔离级别
mysql> show global variables like 'tx_isolation';查看事务级别
① 读未提交: read uncommitted # 2个此事务,其中一个对同一数据的修改,对方即时能看到变化
② 读提交: read committed # 2个此事务对同一数据的操作,只有一方提交后对方才能看到变化
③ 可重读: repeatable read # mysql默认为此隔离级别,2个此事务对同一数据的操作,每个事务相当于有各自一份独立的此数据的复本(快照),只有当2个此事务都commit时,2个事务对数据的操作会进行汇总
④ 串行化: serializable,一个事务对数据的操作时,任何别的事务都无法对些数据同时进行操作
存储引擎
MySQL为插件式存储引擎,执行
mysql> show engines;可查看其支持的引擎类型
mysql> show variables like 'storage_engine' # 查看默认的存储引擎
mysql> show table status like 'TABNAME';查看某张表的属性,包括存储引擎
MySQL存储引擎为表类型的,即每个表都可选择不同的存储引擎.
InnoDB
InnoDB存储引擎支持事务,其设计目标主要面向在线事务处理(OLTP)的应用.其特点是行锁设计 支持外键,并支持类似于Oracle的非锁定读,即默认读取操作不会产生锁.MySQL5.5.8版本开始,InnoDB是默认的存储引擎.
.frm 表结构
.ibd 表空间(数据与索引)
InnoDB特点
1.默认InnoDB表的数据和索引存储于同一个表空间中.即datadir目录中的ibdata1, ibdata2, ... 表结构文件也在此目录中,名为tbl_name.frm
2.事务型,适合对事务要求较高的场景中,适用于处理大量短期事务
3.支持高并发,4个隔离级别(默认为可重读),使用间隙锁以防止幻读
MyISAM
1.支持全文索引,占用空间小,处理速度快
2.不支持事务,不支持外键
3.锁粒度为表级锁
4.崩溃无法保证表安全恢复
5.其缓冲池只缓存索引文件,而不缓冲数据文件.
适用场景: 只读或读多写少 较小的表
每个表有3个文件,存储于数据库目录中
tbl_name.frm: 定义表结构
tbl_name.MYD: 数据文件
tbl_name.MYI: 索引文件
❸ MEMORY: 使用存储在内在中的内容来创建表,所有数据放在内存中.一个表对应一个与表名相同类型为frm的文件.该文件只存储表的结构而数据文件是存储在内存中,这样有利于对数据的快速处理.
设置MySQL默认存储引擎:
set @@session.storage_engine='InnoDB' # 临时修改
vim /etc/my.cnf # 配置文件修改重启后永久生效
default-storage-engine= MyISAM|InnoDB|...
重启mysql服务
范式
① 第一范式: 数据库表中不能有重复字段
② 第二范式: 有主键,非主键字段依赖主键(唯一性,一个表只说明一个事物)
③ 第三范式: 非主键字段不能相互依赖
如果一个数据库能够满足第三范式的要求,数据库中基本上没有冗余的内容了.但要记住满足范式不是我们的目标,优化设计提高整个系统的性能才是我们的目标.
约束
❶ 主键约束: 主键是唯一且非空的,一张表只能有一个
❷ 惟一键约束: 可以为空,一张表可以存在多个
❸ 外键约束: 字段数据受另一张表主键约束
❹ 检查性约束: 数据类型对数据进行的约束
源码CMAKE安装MySQL5.5.33
如何编译
1、使用cmake编译mysql-5.5
cmake指定编译选项的方式不同于make，其实现方式对比如下：
./configure cmake .
./configure --help cmake . -LH or ccmake .
指定安装文件的安装路径时常用的选项：
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql
-DMYSQL_DATADIR=/data/mysql
-DSYSCONFDIR=/etc
默认编译的存储引擎包括：csv、myisam、myisammrg和heap。若要安装其它存储引擎，可以使用类似如下编译选项：
-DWITH_INNOBASE_STORAGE_ENGINE=1
-DWITH_ARCHIVE_STORAGE_ENGINE=1
-DWITH_BLACKHOLE_STORAGE_ENGINE=1
-DWITH_FEDERATED_STORAGE_ENGINE=1
若要明确指定不编译某存储引擎，可以使用类似如下的选项：
-DWITHOUT_<ENGINE>_STORAGE_ENGINE=1
比如：
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1
-DWITHOUT_FEDERATED_STORAGE_ENGINE=1
-DWITHOUT_PARTITION_STORAGE_ENGINE=1
如若要编译进其它功能，如SSL等，则可使用类似如下选项来实现编译时使用某库或不使用某库：
-DWITH_READLINE=1
-DWITH_SSL=system
-DWITH_ZLIB=system
-DWITH_LIBWRAP=0
其它常用的选项：
-DMYSQL_TCP_PORT=3306
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock
-DENABLED_LOCAL_INFILE=1
-DEXTRA_CHARSETS=all
-DDEFAULT_CHARSET=utf8
-DDEFAULT_COLLATION=utf8_general_ci
-DWITH_DEBUG=0
-DENABLE_PROFILING=1
如果想清理此前的编译所生成的文件，则需要使用如下命令：
make clean
rm CMakeCache.txt
编译安装
yum -y install cmake28 # 需要epel源base包中只提供2.6版本
yum -y install readline-devel zlib-devel openssl-devel
useradd -r -d /mydata/data -s /sbin/nologin mysql
tar xf mysql-5.5.33.tar.gz
cd mysql-5.5.33
cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.5.33 \
-DMYSQL_DATADIR=/mydata/data \
-DSYSCONFDIR=/etc \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
-DMYSQL_TCP_PORT=3306 \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci
make && make install
cd /usr/local
ln -s mysql-5.5.33 mysql
chown -R mysql.mysql mysql;chown -R mysql.mysql mysql-5.5.33
mkdir -pv /mydata/data
chown -R mysql.mysql /mydata/data
cd mysql
scripts/mysql_install_db --user=mysql --datadir=/mydata/data 查看数据目录下是否生成初始化数据 ls /mydata/data
cp support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
chkconfig --add mysqld
cp support-files/my-large.cnf /etc/my.cnf; echo "datadir = /mydata/data" >> /etc/my.cnf
echo "export PATH=/usr/local/mysql/bin:$PATH" > /etc/profile.d/mysql.sh;
. /etc/profile.d/mysql.sh
安装后初始化
mysql> select User,Host,Password from mysql.user; # 查看用户
mysql> drop user ""@localhost; # 删除匿名用户,host部分可用% _通配符
mysql> update user set password = PASSWORD('magedu') where user='root'; # 给用户添加密码
或者
mysqladmin -u root password "xxxx" # 给用户设置密码的另一种方法
mysql> flush privileges;
优化设置
安装并启动后的初始化:mysql_secure_installation
运行前常修改的参数:
innodb_file_per_table=1
skip_name_resolve=1
InnoDB的所有表数据默认是放在一个文件里的/var/lib/mysql/ibdata1
工作模式
① mysql < /path/to/scripts.sql 脚本模式
② mysql 交互式shell
mysql命令
mysql
-u, --user
-h, --host
-p, --password
--port
--protocol
--database DATABASE, -D
MySQL服务器参数/变量(设定MySQL/MariaDB的运行参数)
mysql> show global[session] variables [like clause]; # 查看服务器参数变量
mysql> show global[session] status [like clause]; # 查看状态参数变量
显示单个服务器参数变量设定值的方法:
mysql> select @@[global.|session.]SYS_VAR_NAME;
如 select @@session.tx_isolation;
运行时修改参数/变量,其中global设定仅对修改后新建立的会话有效,而session仅对当前会话有效,且立即生效.
set [global|session] SYS_VAR_NAME = EXPR
或者
set @@[global.|session.]SYS_VAR_NAME = EXPR
设定系统参数sql_mode以提高数据的严格性,此参数默认为空(即不符合表定义类型的数据也会被插入到表中).常用的mode如下:
TRADITIONAL # 传统
STRICT_TRANS_TABLES # 仅对事务型表严格限制,如InnoDB,不允许向其插入非法数据
STRICT_ALL_TABLES # 所有的非法数据都不允许
修改方式
set global sql_mode=MODE
set @@global.sql_mode=MODE
创建表
create table tb_name1(stu_id int primary key, stu_name varchar(20), stu_sex boolean) ;
create table tb_name2(s_id int, c_id int, grade float, primary key(stu_id,course_id)); # 多字段组合主键
create schema if not exists students character set 'gbk' collate
create table tab1 like tab2; # 复制表结构
创建tb_name2的子表(tb_name2是tb_name3的父表)
create table tb_name3(id int primary key,
stu_id int, course_id int,
constraint c_fk foreign key(stu_id,course_id)
references tb_name2(s_id, c_id));
# stu_id course_id为表tb_name3的外键,c_fk是外键的别名.子表的外键关联的必须是父表的主键或组合主键中的一个且类型必须为一致
表数据的完整性约束
unsigned # 无符号的.对数字类型
primary key # 主键
foreigh key # 外键
not null # 非空
unique # 惟一
auto_increment # 自增
default # 默认
查看表结构、定义
mysql> describe tbl_name或者desc tbl_name # 查看表的基本定义
mysql> show create table tbl_name # 查看表的详细定义
mysql> show table status [{from|in} db_name] [like 'pattern' | where expr]
修改表(结构属性层面 alter&drop)
alter table OLDTABNAME rename [to] NEWTABNAME; # 修改表名
alter table TABNAME modify FIELDNAME TYPE; # 修改字段数据类型
alter table TABNAME change OLDFIELDNAME NEWFIELDNAME TYPE; # 修改字段名
alter table TABNAME add FIELD1 TYPE [约束条件] [first|after FIELD2]; # 添加字段
alter table TABNAME drop FIELDNAME; # 删除字段
alter table TABNAME modify FIELD1 TYPE first|after FIELD2; # 修改字段的排列位置
alter table TABNAME engine=xxxx; # 修改表的存储引擎
alter table TABNAME charset=utf8; # 修改表的数据字符集
alter table TABNAME drop foreign key 外键别名; # 删除表的外键约束
drop table TABNAME; # 删除表(没有被其它表关联)
如果与其它表有关联的表,正确的方法应该是删除子表的外键约束然后再删除父表.这样不会影响子表的其他数据,可以保证数据库的安全.
假如example4表(外键为stu_id,别名为d_fk)是example1表的子表
alter table example4 drop foreign key d_fk;
drop table example1;
索引
索引是一种特殊的数据库结构,可以有来快速查询数据库表中的特定记录.索引同数据库表中一列或多列组合而成,其作用提高对表中数据的查询速度,是提高数据库性能的重要方式.MySQL中所有的数据类型都可以被索引,其索引分类如下:
❶ 普通索引
在创建普通索引时,不附加任何限制条件.此类索引可以创建在任何数据类型中,其值是否唯一和非空由字段本身的完整性约束条件决定.
❷ 唯一性索引
使用unique参数可以设置索引为唯一性索引.限制该索引的值必须是唯一的.主键就是> 一种唯一性索引.其字段的每一个值都是唯一的.
❸ 全文索引
使用fulltext参数可以设置索引为全文索引.全文索引只能创建在char varchar或text类型的字段上.查询数据量圈套的客串类型的字段时,使用全文索引可以提高查询速度.(目前只有MyISAM支持)
❹ 单列索引
在表中的单个字段上创建索引.其可以是以上3种中的任意一种索引.
❺ 多列索引
多列索引是在表的多个字段上创建一个索引.该索引指向创建时对应的多个字段,可以通过这几个字段进行查询.但是只有查询条件中使用了这些字段中第一个字段时,索引才会被使用.
❻ 空间索引
使用spatial参数可以设置索引为空间索引.空间索引只能建立在空间数据类型上,样可以提高系统获取空间数据的效率.MySQL中的空间数据类型包括geometry和point linestring polygon,目前只有MyISAM存储引擎支持空间检索,而且索引的字段不能为空值.
索引的缺点:
创建和维护索引需要耗费时间,耗费时间的数量随着数据量的增加而增加.索引需要占用物理空间.索引可以提高查询的速度,但是会影响插入记录的速度.插入大量记录的速度影响更加明显.
索引的设计原则
- 选择唯一性索引: 其值是唯一的,可以更快地通过该索引来确定某条记录.
- 为经常需要排序、分组和联合操作的字段建立索引
- 为常作为查询条件的字段建立索引
- 限制索引的数目
- 尽量使用数据量少的索引
- 尽量使用前缀来索引
- 删除不再使用或者很少使用的索引
创建索引
❶ 创建表的时候直接创建索引
create table TABNAME(属性名 数据类型 [完整性约束条件],..,[unique|fulltext|spatial] index|key [别名] (属性名 1 [(长度)] [asc|desc]));
属性名 1: 前面定义好的某个字段的名称.
长度: 可选参数,其指索引的长度,必须是客串类型才可以使用
asc|desc: 表示升序或降序排列
❷ 在已经存在的表上创建索引
create [unique|fulltext|spatial] index 索引名 on 表名 (属性名 [(长度) [asc|desc]]);
❸ 在已经存在的表上通过alter table语句来创建索引
alter table 表名 add [unique|fulltext|spatial] index 索引名 (属性名 [(长度) [asc|desc]]);
创建普通索引
create table index1(id int,
name varchar(20),
sex boolean,
index(id)
);
使用explain语句可以查看索引是否被使用
explain select * from index1 where id=1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: index1
type: ref
possible_keys: id # 此行及以下一行说明索引起作用了
key: id
key_len: 5
ref: const
rows: 1
Extra:
1 row in set (0.01 sec)
创建唯一性索引
create table index2(
id int unique,
name varchar(20),
unique index index2_id(id asc)
);
创建全文索引
create table index3(
id int,
info varchar(20),
fulltext index index3_info(info)
)engine=MyISAM;
创建单列索引
create table index4(
id int,
subject varchar(30),
index index4_st(subject(10))
);
创建多列索引
create table index5(
id int,
name varchar(20),
sex char(4),
index index5_ns(name,sex)
);
创建空间索引
create table index6(
id int,
space geometry ont null,
spatial index index6_sp(space)
)engine=MyISAM;
创建唯一键索引 全文索引 多列索引示例:
create table user(
userid int(10) primary key not null unique auto_increment, username varchar(20) not null,
passwd varchar(20) not null,
info text,
unique index index_uid(userid desc),
index index_user(username,passwd),
fulltext index index_info(info)
)engine=MyISAM default charset=utf8;
索引何时不生效
① 像like "%xxx"这样%号为第一个字符时索引不会被使用.
② 如果是多列索引,但查询条件并不是多列索引的第一个字段时,索引也不会起作用.示例如下:
mysql> create index index_birth_department on student(birth,department);
mysql> explain select * from student where birth=1991; # 多列索引生效
mysql> explain select * from student where deartment='CS'; # 多列索引不生效
③ 查询语句只有or关键字时,如果or前后的两个条件的列都是索引时,索引生效,否则索引不生效.
删除索引
mysql> drop index 索引名 on 表名;
临时禁用/开启索引
mysql> alter table TABNAME disable|enable keys;
视图(view)
视图它是一个虚表,存储下来的select语句.常见的操作如下
create view VIEW_NAME [(column_list)] as select_statement; # 创建
alter view VIEW_NAME [(column_list)] as select_statement; # 修改
drop view VIEW_NAME [if exists] VIEW_NAME [, VIEW_NAME] ...; # 删除
触发器
查询语句
基本语法
select [distinct] FIELDS from TABNAME
where 条件
group by FIELDS having 条件
order by FIELDS [asc|desc]
limit 初始位置,记录数
集合函数
count()
sum()
avg()
max()
min()
连接查询
连接查询是将两个或多个表按某个条件连接起来,从中选取需要的数据.它包括❶内连接查询与❷外连接查询
内连接查询: 只查询出指定字段取值相同的记录.主键与外键交集
select num,name,employee.d_id,age,sex,d_name,function \ from employee, department \ where employee.d_id=department.d_id;
外连接查询: 主外键字段值不相等的记录也可以查询出来.分为左外连接与右外连接(分别表示以哪此表为主)
select FIELD1,FIELD2,...
from TAB1 left|right join TAB2
on TAB1.FIELD=TAB2.FIELD;
子查询(嵌套select,但效率不高)
子查询的效率不高源自需要为内层的查询结果建立临时表,查询完毕后还要撤销这些临时表.但连接查询不需要,所以后者查询效率更高.
in (select子语句): 在select子语句中出现过的值
比较运算符 (select子语句):
exists (select子语句): 判断子语句为真时执行,否则不执行主select
比较符 any (select子语句): 与子语句查询出的任意一个值比较
比较符 all (select子语句): 只有满足内层子select子语句返回的所有结果才执行外层查询语句.
合并查询结果
取所有select语句的结果的交集部分.
select 语句1 union|union all
select 语句2 union|union all
select 语句3 union|union all
...
select 语句1 union select 语句2;
使用正则表达式查询
select FIELD1,FIELD2,... from TAB where FIELD regexp|rlike "XX";
插入、更新、删除数据(数据内容层面 insert update delete)
insert into TABNAME values(v1,v2,..);
insert into TABNAME(FIELD1,FIELD2,...) values(v1,v2,...),(x1,x2,...);
update TABNAME set FIELD1= ,FIELD2= where 条件;
delete from TABNAME where 条件; # 不跟where子名会删除整表内容
MySQL运算符
❶算术运算符
❷比较运算符
❸逻辑运算符
❹位运算符
MySQL函数
- 数学函数
-
字符串函数
字符串函数
忘记管理员密码的解决办法
- 启动mysqld进程时，使用--skip-grant-tables和--skip-networking选项(systemctl管理的话要编辑mariadb.service,并执行systemctl daemon-reload;CentOS6的话需要更改/etc/init.d/mysqld文件)
- 通过update命令修改管理员密码,flush privileges加载授权表.
- 改回1步骤以正常方式启动mysqld进程
MySQL用户帐号与权限管理
mysql -u USERNAME -h HOSTNAME -P PORT -p PASSWD -e "SQL语句" 登录语句
创建用户:
mysql> create user 'user'@'host' identified by [PASSWORD] 'password';
mysql> insert into mysql.user(User,Password) values('xx','xxx',PASSWORD('pwd'));
grant priv_type on DB.TAB to USER identified by [PASSWORD]"PWD";
删除用户:
mysql> drop user 'user'@'host' ...;
mysql> delete from mysql.user where Host='xxx' User='xx';
重命名用户:
rename user OLD to NEW [OLD to NEW]...;
修改用户密码:
❶ set password [for 'user'@'host']=PASSWORD('xxxxx'); flush privileges;
❷ update mysql.user set Password=PASSWORD('xxxx') where User='USERNAME' and Host='HOST'
❸ mysqladmin -uUSERNAME -hHOST -p password 'NEW_PASS'
用户权限
安装MySQL时会自动安装一个名为mysql的数据库.mysql数据库下面存储的都是权限表(如user db host tables_priv columns_priv proc_priv).用户登录以后,MySQL数据库系统会根据这些权限表的内容为每个用户赋予相应的权限.
MySQL中权限分配是按照user表、db表、table_priv表和columns_priv表的顺序依次判断各表中的值是否为Y,是的话就不需要检查后面的表了.
user表
Host: 主机名
User: 用户名
Password: 密码
xxx_priv: 以priv结尾的字段决定了用户的权限.
ssl_type: #以下4个都与安全加密相关
ssl_cipher:
x509_issuer:
x509_subject
max_questions # 每小时可以允许多少次查询,0表示不限制
max_updates # 更新
max_connections # 连接
max_user_connections# 单个用户可以同时具有的连接数.
db表
db表存储了某个用户对一个数据库的权限.
host表
较少使用
tables_priv表
可以对单个表进行权限设置
columns_priv表
此表可以对单个数据列进行权限设置
procs_priv表
此表可以对存储过程和存储函数进行权限设置.
用户权限
MySQL权限说明(user表)
授权语句
语法:
GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO USER [, USER] ...
[REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
[WITH with_option ...]
GRANT PROXY ON user_specification
TO user_specification [, user_specification] ...
[with GRANT OPTION]
with后跟的选项:
GRANT OPTION: 被授权的用户可以将这些权限赋予给别的用户
MAX_QUERIES_PER_HOUR count: 设置每小时可以允许执行count次查询.
MAX_UPDATES_PER_HOUR count:
MAX_CONNECTIONS_PER_HOUR count:
MAX_USER_CONNECTIONS count
object_type:
TABLE
| FUNCTION
| PROCEDURE
priv_level:
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
user_specification:
user
[
IDENTIFIED BY [PASSWORD] 'password'
| IDENTIFIED WITH auth_plugin [AS 'auth_string']
]
ssl_option:
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'
with_option:
GRANT OPTION
| MAX_QUERIES_PER_HOUR count # 0表示不限次数
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
mysql> grant all on db_name.tbl_name to 'user'@'host' ; # 授权
取消授权
mysql> revoke update on db_name.tbl_name from 'user'@'host'; # 收回授权
显示用户权限
mysql> show grants for 'user'@'host';
数据备份与还原
备份
❶ 备份:mysqldump命令(属于逻辑备份,性能较差)
将数据库中的数据备份成一个文本文件.其原理是查出需要备份的表的结构,分析出需要什么create语句与insert语句可以生成这张表,将这些个create与这个庞大的insert语句保存在文本文件中.应该在MySQL服务器压力最小的时间段执行,并且需要考虑到备份需要影响到的时间有.当数据库相当大时，不建议此种备份方式。
缺点: 会丢失数据精度，无法备份索引
备份类型
备份什么:
数据 配置文件 二进制日志 事务日志
备份策略:
完全+增量 完全+差异
按备份数据库的内容
❶ 完全备份: 对数据库进行一个完整的备份.
❷ 增量备份: 在上次完全备份的基础上,对于更改的数据进行备份.
❸ 差异备份: 仅备份自上一次完全备份以来变量的那部分数据；
❹ 日志备份: 对MySQL数据库二进制日志的备份.
物理备份、逻辑备份
❶ 物理备份：复制数据文件进行备份,也称祼文件备份.恢复时间较短.不需要
❷ 逻辑备份：从数据库导出数据另存在一个或多个文件中；恢复所需要的时间往往较长.但适用于任意存储引擎.移植性较好,且方便使用文本处理工具直接对其处理
根据数据服务是否在线
❶ 热备：读写操作均可进行的状态下所做的备份.MySQL官方称之为Online Backup.
❷ 温备：可读但不可写状态下进行的备份；
❸ 冷备：读写操作均不可进行的状态下所做的备份；一般只需要复制相关的数据库物理文件即可.MySQL官方称之为Offline Backup
几种备份工具
❶ mysqldump(逻辑备份|)
mysqldump命令用法:
# mysqldump -u USERNAME -h HOSTNAME -p DBNAME [tab1 tab2 ...] > backup.sql,如果只备份一个数据库时,mysqldump不会自动生成数据库,还原时需要手动创建数据库,即下面2步操作
mysql> create database db;
# mysql db < backup.sql
# mysqldump -u USERNAME -h HOSTNAME -p --databases db1 db2 ... > backup.sql 备份多个指定数据库
# mysqldump -u USERNAME -p -h HOSTNAME --all-databases > all.sql 备份所有数据库
-p: 要求输入密码
USERNAME: 用户名
DBNAME: 数据库名
MyISAM存储引擎：支持温备 备份时要锁定表
-x,--lock-all-tables:锁定所有库的所有表,读锁
-l,--lock-tables: 锁定指定库所有表
InnoDB存储引擎: 支持热备与温备
--single-transaction: 创建一个事务,基于此快照执行备份.InnoDB务必加上,不要与--lock-all-tables一同使用
-R,--routines: 存储过程和存储函数
--triggers: 备份触发器
-E,--events: 备份事件
--routines:
--triggers:
--master-data[=NUM]: 记录备份文件时,binlog所处的时间 偏移量也会被记录
0: 不记录二进制日志及其位置
1: 记录为chage master to语句,此语句不被注释
2.: 记录为chage master to语句,此语句被注释
记录的内容示例:
-- CHANGE MASTER TO MASTER_LOG_FILE='master-log.000001', MASTER_LOG_POS=245;
--flush-logs: 锁定表完成后,即进行二进制日志滚动操作.
tab: 指定数据库中的各表,没有的话将备份整个数据库.
backup.sql: 备份的文本文件(并非必须得以.sql结尾)
--lock-all-tables: 备份前锁定所有库所有表
mysqldump真实场景备份操作
备份所有数据库: 每周完全备份+每日增量
# mysqldump -uroot -p --lock-all-tables --flush-logs --all-databases --master-data=2 > /root/all.sql
查看all.sql中(--master-data)记录的二进制日志文件,在其之前的二进制日志文件可备份后都删除.
mysql> prege binary logs to "mysql-bin.00000x"; # 备份后删除旧的二进制日志文件
增量备份
mysql> flush logs;
# cd /mydata/data
# mysqlbinlog mysql-bin.0000011 > /root/mon-incremental.sql # 可以改名以便知道具体是哪个完全备份后的哪天的增量备份
# cd /usr/local/mysql
# scripts/mysql_install_db --user=mysql --data-dir=/mydata/data
# /etc/init.d/mysqld start
# mysql -uroot -p < /root/all.sql # 还原完全备份的数据
# mysql -uroot -p < /root/mon-incremental.sql # 还原增量备份的数据
# mysql -uroot -p < /root/tues-incremental.sql
# ...
❷ xtrabackup开源备份工具
支持对InnoDB做热备,是一款开源的物理备份工具.下载percona-xtrabackup(CentOS7)
yum -y install ./percona-xtrabackup-2.3.2-1.el7.x86_64.rpm
xtrabackup --backup | --prepare [options]
--print-defaults: 打印程序参数列表
--no-defaults: 不从任何文件是读取默认选项
--defaults-file= :
--target-dir= : 备份的目标目录
--backup: 创建备份实例
--stats:
xtrabackup
❸ 备份:直接复制整个数据库中目录(InnoDB不适用)
最简单的备份方法,就是将MySQL中的数据库文件直接复制出来.使用这种方法前最好将数据库服务停止以便数据不会发生变化.
❹ 备份:使用mysqlhotcopy工具快速备份(InnoDB不适用)
mysqlhotcopy(一个Perl脚本,需要安装)可以在不停止MySQL服务器下进行备份,其速度快于mysqldump.其主要使用lock tables,flush tables,cp来进行快速备份.工作原理是,先将需要备份的数据库加上一个读操作锁,然后用flush tables将内在中的数据写回到硬盘上的数据库中,最后用cp将需要备份的数据库文件复制到目标目录.
# mysqlhotcop [option] dbname1 dbname2 .. backDir/
--help: mysqlhotcopy帮助
--allowold: 如果备份目录下存在相同的备份文件,则将旧的备份文件名加上_old
--keepold: 如果备份目录下存在相同的备份文件,则将旧的备份更名而不是删除.
--flushlog: 本次备份之后,将对数据库的更新记录到日志中.
--noindices: 只备份数据文件,不备份索引文件.
--user=用户名: 用来指定用户名,可以用-u替代
--password=密码: 指定密码,可以用-p替代但须与-p紧挨着
--port=端口号: 指定端口号,可以用-P替代
--socket=socket文件: 用来指定socket文件,可以用-S替代
备份策略:
1.完全+差异+binlog 或 完全+增量+binlog
- 多久一次完全备份
MySQL备份脚本
db_backup.sh
#!/bin/bash
. /etc/init.d/functions
# 要备份的数据库名，多个数据库用空格分开
databases=(db1 db2 db3)
# 备份文件要保存的目录
basepath='/root/backup/b3log.org/mysql/'
if [ ! -d "$basepath" ]; then
mkdir -p "$basepath"
fi
# 循环databases数组
for db in ${databases[*]};do
# 备份数据库生成SQL文件
/bin/nice -n 19 /usr/bin/mysqldump -uUSER -pPASSWORD --master-data=2 --database $db > $basepath$db-$(date +%Y%m%d).sql
# 将生成的SQL文件压缩
/bin/nice -n 19 tar zPcf $basepath$db-$(date +%Y%m%d).sql.tar.gz $basepath$db-$(date +%Y%m%d).sql
done
# 删除7天之前的备份数据
find $basepath -mtime +7 -name "*.sql.tar.gz" -exec rm -rf {} \;
# 删除生成的SQL文件
rm -rf $basepath/*.sql
设置计划任务来执行MySQL备份
crontab -e
# mysql backup
00 03 * * * /bin/bash /root/db_backup.sh
还原
❶ mysql命令还原
mysql -u root -p[dbname] < backup.sql
❷ 直接复制数据库文件到数据库目录
必须保证两个MySQL数据库的主版本号是相同的.
数据库迁移
❶ 相同版本的MySQL数据库之间
mysqldump -h host1 -u root --password=password1 --all-databases | mysql -h host2 -u root --password=password2
❷ 不同版本的MySQL数据库之间
高版本的MySQL数据库通常都会兼容低版本.
日志
MySQL日志是记录数据库日常操作和错误信息的文件.当数据库遭到意外的损害时,可以通过日志文件来查询出错原因,并且可以通过日志文件进行数据恢复.MySQL中日志可分为:
❶ 二进制日志: 以二进制文件的形式记录数据库中的操作,但不记录查询语句
❷ 错误日志: 记录MySQL服务器的启动、关闭、 运行错误信息(文本)
❸ 通用查询日志: 记录用户登录和记录查询的信息(文本)
❹ 慢查询日志: 记录执行时间超过指定时间的操作(文本)
❺ 中继日志: 在当前从服务器上要求执行一遍的日志(格式同二进制日志一样)
❻ 事务日志: 将随机IO转化
二进制日志
也叫做变更日志,主要用于记录数据库的变化情况以及还原数据.任何可能引起数据变化的操作都会记录.通过其可以查询MySQL数据库中进行了哪些改变.MySQL的每一次重启都会生成一个相应的二进制日志文件.
二进制日志的格式的两种:1基于语句 2基于行 3前两种的混合模式(binlog_format=mixed)
二进制日志文件最好不要与数据文件放在同一块硬盘上,应该放置在有冗余能力的磁盘上
mysql-bin.index文本文件记录了所有的二进制日志文件数量
启动与设置
vim /etc/my.cnf
log-bin=mysql-bin # 名称示例:mysql-bin.000001
/etc/init.d/mysqld restart 或修改MySQL服务器参数"log_bin":
set session log_bin = 1;
查看并输出指定二进制日志内容
mysqlbinlog 二进制日志文件
选项
--start-datetime
--stop-datetime
--start-position
--stop-position
示例
msyqlbinlog --start-datetime "2015-03-26 15:14:39" mysql-bin.000003 > /root/a.sql
查看所有二进制日志文件名与大小
mysq> show binary logs;
查看当前正在使用的二进制日志文件
mysql> show master status;
查看二进制日志事件
mysql> show binlog events in 'mysql-bin.000005';
mysql> show binlog events in 'mysql-bin.000005' from 107; # 从指定位置查看
删除二进制日志
mysql> reset master; # 删除所有二进制日志
mysql> purge master logs to "mysql-bin.000007"; # 删除小于此编号的所有二进制日剧
mysql> purge master logs to "yyyy-mm-dd hh:MM:ss;" # 删除指定时间之前创建的二进制日志
或设置expire_logs_days
用二进制日志还原数据库
如果数据库遭到意外损坏,首先应该使用最近的备份文件加上二进制日志来还原.备份文件时间之前的二进制日志可以删除.备份文件的恢复上面提到.
二进制日志还原
mysqlbinlog filename.number | mysql -u root -p
使用mysqlbinlog命令进行还原操作时,必须是编号小的先还原
暂时停止二进制日志功能
在配置文件中设置log-bin选项后MySQL服务器将会一直开启二进制日志功能.但有命令可以临时暂停二进制日志功能(不希望某些sql语句记录在二进制日志中).
mysql> set sql_log_bin=0; # 暂停二进制日志功能
mysql> set sql_log_bin=1; # 开启二进制日志功能
截取掉部分二进制日志内容
mysqlbinlog --stop-position=695 /var/lib/mysql/master-log.000001 > new.sql
手动滚动二进制日志
mysql> flush logs; # 会滚动生成一个新的二进制日志文件,在从服务器上会滚动中继日志
二进制日志的同步参数
sync_binlog=#
设定多久同步一次二进制近几年至磁盘文件中,0表示不同步,任何正数值都表示对二进制日志每多少次写操作之后同步一次.当autocommit的值为1时,每条语句的执行都会日志同步,否则,每个事务的提交会引起二进制近几年同步.
错误日志
错误日志记录着开启与关闭MySQL服务的时间,以及服务运行过程中出现哪些异常等信息.配置文件里若没有配置的话,一般名为hostname.err
设置错误日志
vim /etc/my.cnf
log-error=/PATH/TO/FILE #
log-warning={1|0}
/etc/init.d/mysqld restart
删除错误日志
# mysqladmin -u root -p flush-logs # 会将旧日志文件更名为filename.err-old
开启的错误日志
mysql> flush logs;
通用查询日志
默认情况下,通用查询日志功能是关闭的.开启后如果没有指定日志文件名的话则默认名为HOSTNAME.log
开启通用查询日志
vim /etc/my.cnf
[mysqld]
log = [/PATH/TO/FILE]
/etc/init.d/mysqld restart
删除通用查询日志
mysqladmin -u root -p flush-logs 或手工删除
慢查询日志
默认情况下,慢查询日志功能是关闭的.在配置文件开启后如果没有指定文件名,则名为HOSTNAME-slow.log
开启慢查询日志
vim /etc/my.cnf
[mysqld]
log-slow-queries[=/PATH/TO/FILE]
long_query_time=SECOND # 如果查询超过这个时间,这个查询语句将被记录到慢查询日志.
删除慢查询日志
mysqladmin -u root -p flush-logs 或手工删除
中继日志
从主服务器的二进制日志文件中复制而来的事件,并保存为日志文件.
事务日志
就像一个快照,同时记录了新旧数据.事务性存储引擎用于保证原子性 一致性 隔离性和持久性.即主要用于保证事务的可靠性.
innodb_flush_log_at_trx_commit
0: 每秒同步,并执行磁盘flush操作
1: 每事务同步,并执行磁盘flush操作
2: 每事务同步,但不执行磁盘flush
性能优化
优化查询
分析查询语句
explain select 语句
id: select语句的编号
select_type: select语句的类型
simple:简单查询
primary:主查询或最外层的查询语句
union: 连接查询的第二个或后面的查询语句
table: 查询的表
type: 表示连接类型
system:表中只有一条记录
const: 表中有多条记录
all: 表示对表进行了完整的扫描
eq_ref: 表示多表连接时,后面的表使用了unique或者primary key;
ref:表示多表查询时,后面的表使用了普通索引
unique_subquery: 表示子查询中使用了unique或者primary key
index_subquery:表示子查询中使用了普通索引
range: 表示查询语句中给出了查询范围
index: 表示对表中的索引进行了完整的扫描
possible_keys: 表示查询中可能使用的索引
key: 表示查询使用到的索引
key_len: 表示索引字段的长度
ref: 表示使用哪个列或常数与索引一起来查询记录
rows: 表示查询的行数
Extra: 表示查询过程的附件信息
describe select 语句也可简写成desc
索引对查询速度的影响
相当于新华字典的目录可以大大加快你查找一个字的速度一样,索引对于MySQL的功能也是类似,可以提高数据库查询的速度
使用索引查询记录时,一定要注意索引的使用情况.例如,like关键字配置的字符串不能以"%"开头;使用多列索引时,查询条件必须要使用这相索引的第一个字段;使用or关键字时,or关键字连接的所有条件都必须使用索引.
优化子查询
尽量用连接查询代替子查询
优化数据库结构
① 将字段很多的表分解成多个表**
② 增加中间表
③ 增加冗余字段.虽然有背三范式,但可以提高查询速度.(因为不需要常连接多张表来查询,一张表就可搞定)
④ 优化插入记录的速度
1.临时禁用\开启索引
mysql> alter table TABNAME disable|enable keys;
2.禁用唯一性检查
mysql> set unique_checks=[0|1] # 0为关闭,1为开启
3.优化insert语句: 将多条对同一表进行操作的insert语句汇合成单条,可以减少与数据库之间的连接等操作.
⑤ 分析表、检查表和优化表
1.分析表
mysql> analyze table TAB1 [,TAB2 ...];
2.检查表: 执行过程会给表加上只读锁
mysql> check table TAB1 [,TAB2 ..] [OPTION];
OPTION: 按执行效率从高到低(只对MyISAM类型表有效)
QUICK
FAST
CHANGED
MEDIUM
EXTENDED
3.优化表: 可以消除删除和更新造成的磁盘碎片,执行过程中也会给表加上只读锁
mysql> optimize table TAB1 [,TAB2...];
服务器优化
- 增大MySQL服务器内存,并选择my-innodb-heavy-4G.ini作为MySQL数据库的配置文件.
- 添加多块磁盘来存储数据
- 优化MySQL参数
vim /etc/my.cnf
[mysqld]
key_buffer_size:
table_cache:
query_cache_size=0:
query_cahce_type:
max_connections=100: 最大连接数
sort_buffer_size:
read_buffer_size:
read_rnd_buffer_size:
innodb_buffer_pool_size:
innodb_flush_log_at_trx_commit:
