MySQL数据库
初始化mysql数据库：
1. 安装mysql 可以前往oracle官网下载，装包后就可以启动服务mysqld了
2. mysql安装后初始密码在/var/log/mysqld.log，可以用下面命令获取密码(必须是启动服务后才会有)
awk '/temporary password/{print $11}' /var/log/mysqld.log 3. 如果觉得mysql默认的密码要求太过苛刻，可以修改密码的检测策略
sed -i '/\[mysqld\]/avalidate_password_policy=0' /etc/my.cnf
sed -i '/\[mysqld\]/avalidate_password_length=1' /etc/my.cnf4. 第一次修改密码，需要用初始密码进如数据库，然后用下面SQL语句设定密码
set password = password('密码');| 数据库软件 | |
| maxscale | 读写分离 |
| MHA | MySQL高可用集群架构 |
| Mycat | 基于java的分布式数据库系统 |
数据库管理员密码重置
如果忘记了管理员密码，处理方案
1. 设置配置文件，跳过授权表启动MySQL程序
sed -i '/password/s/^/#==/' /etc/my.cnf
sed -i '/mysqld/askip-grant-tables' /etc/my.cnf2. 重新启动MySQL服务程序(Linux操作的系统命令)
systemctl restart mysqld3. 修改 mysql.user 表的内容(MySQL下的命令)
update mysql.user set authentication_string = password('新密码') where host='localhost' and user='root';
flush privileges;4. 改回配置文件，重启服务(Linux操作的系统命令)
sed -i 's/^#==//' /etc/my.cnf
sed -i 's/^skip-grant-tables//' /etc/my.cnf
systemctl restart mysqldMySQL数据库基本知识
| 数据文件存放位置 | ||
| 库文件 | /var/lib/mysql | 在该目录下存放一个和库名同名的文件夹 |
| 表文件 | /var/lib/mysql/库名 | 在对应的库所在文件夹下有每个表的文件 |
| 每个表的文件存储方式和存储引擎相关，不同的引擎有不同的存储方法 | ||
| MyISAM引擎存储 | 表.frm(表结构)、 表.myd(表数据)、 表.myi(索引信息) | |
| InnoDB引擎存储 | 表.frm(表结构)、 表.ibd(数据和索引) | |
root在命令行操作数据库
| 进入数据库 | mysql -u 用户 -p密码 [库名] |
| 修改数据库密码 | mysqladmin -u root -p旧密码 password '新密码'; |
| 做binlog备份 | mysqldump -u 用户 -p密码 保存的数据 --flush-logs > 路径/备份名.sql |
| 逻辑备份恢复 | mysql -u 用户 -p密码 库名 < 路径/备份名.sql |
对数据库操作的SQL命令汇总(SQL语句中回车没有具体含义，只是方便阅读，具体作用和空格一样)
对系统：
| 查看锁状态 | show status like 'table_lock%'; |
| 查看事务提交状态 | show variables like 'autocommit' |
| 查看数据库读取文件的目录 | show variables like 'secure_file_priv'; |
| 查看当前用户 | select user(); |
| 查看当前主机名 | select @@hostname; |
| 查看当前权限 | show grants; |
| 修改密码 | set password for 用户@'客户端' = password ('密码'); |
| 如果不写for 用户@'客户端' 就是修改自己的密码 | |
| 查看binlog消息 | show master status; |
| 刷新binlog日志信息 | purge master logs to '日志名'; |
| 删除所有日志，重新开始记录 | reset master; |
| 查看当前库下视图信息 | show table status where comment='view'\G; |
| 创建视图 | create or replace algorithm=算法 view 视图名称 as SQL语句 with 限制方式 check option |
| 删除视图 | drop view 视图名 |
对库：
| 查询数据库中所有库名 | show databases; | |
| 进入某个库中进行操作 | use database 库名; | 刚刚登录数据库，默认不在任何数据库下，进入数据库大厅 |
| 查看当前所在库 | select database(); | 如果显示为null，代表当前不在任何库下 |
| 如果想退出当前库，到达数据库大厅，只能退出重新登录 | ||
| 创建库 | create database 库名; | |
| 查询某库中的表情况 | show tables; | 查看当前所在库下所有库的库名 |
| 注意：必须在一个具体库下才能执行 | ||
对表：(所有命令中，如果没有，默认是再当前所在库进行操作，再如果当前不在任何库下，没有库名会报错)
| 创建表 | create table 库名.表名 (字段 数据类型 约束, ……index(字段),primary key(字段名1,……,字段名n),foreign key(自己表中作为外键的字段) references 被参照表(被参照字段) on update cascade on delete cascade)engine=引擎, default charset=utf8; |
| 括号内就是指定表中的所有字段信息，一个字段就是一列数据的标识 | |
| 括号后的，engine是指定引擎，default charset是指定库中用什么字符编码(决定能不能支持表中存中文或者其他特殊字符) | |
| index代表某字段为索引目标,primary key就是主键定义，foreign key是外键定义 | |
| 查看一个表的详细详细 | show create table 库名.表名; |
| 查看表结构 | desc 库名.表名 |
| 查看表中数据 | select 字段 字段别名 from 库名.表名 表别名 where 条件 group by 字段 having 判断条件 order by 字段 asc|desc limit N,M; |
| asc代表升序，desc代表降序 limit是从第N+1行显示M个后不再继续查找了 | |
| select语句是从做往右执行，也就是说，是先判定where才到分组，再到having判断…… | |
| 插入数据 | insert into 库名.表名(字段,……) values(数据,……)，(数据); |
| 表名后面可以不写(字段信息)，不写默认代表所有字段全录入 | |
| values后面，一个括号就是一行数据，而每个括号中数据必须符合相应字段的数据类型 | |
| values后面每个括号中数据个数必须符合前面字段的个数，其中每个数据必须符合相应字段的数据类型 | |
| 如果想某个字段数据设置为空，不能不写用空字符代替，而是用null写在对应的位置 | |
| 删除某行数据 | delete from 库名.表名 where 匹配; |
| 删除匹配条件的的那些行的所有数据 | |
| 如果不写where匹配，那就是删除表中的所有数据，但是表结构不会被删除，表依然存在 | |
| 修改某些行的具体字段 | update 库名.表名 set 字段='值'…… where 条件; |
| 修改所有满足条件的行的某些字段的值，如果不写条件，那就是修改所有行的指定字段的值 | |
| 如果值是数字可以不加引号，但是如果是字符则必须有引号，必然数据库可能理解为变量或者字段 | |
| 修改表名 | alter table 库名.原表名 rename [to] 新表名; |
| to可以不写，写了也对，作用都一样 | |
| 修改表结构 | alter table 库名.表名 执行动作 |
| 添加字段 --> alter table 库名.表名 add 字段 字段相关信息; | |
| 修改某字段信息 --> alter table 库名.表名 modify 字段 字段相关信息; | |
| 修改字段名字 --> alter table 库名.表名 change 原字段名 新字段名 字段相关信息; (如果字段信息和原字段信息一样代表只是换字段名，如果不一样，如果信息不一样，那么就是两个都换了，不过数据不变) | |
| 删除字段 --> alter table 库名.表名 drop 字段; | |
| 复制表 | create table 表名 SQL查询语句 |
| 如果只想要几个字段，SQL查询语句中，就指定这几个字段即可 | |
| 如果想单纯的复制表结构，SQL查询的时候字段部分写*，写一个where false即可，这样就不会有一个数据，而结构却会被复制去 |
索引(MUL)：
| 查看索引 | desc 库名.表名; |
| 查看索引详细详细 | show index from 表名\G; |
| 查看索引是否被使用 | explain select 查询语句; |
| 创建索引 | create index 索引名 on 表名(字段名); |
| 也可以在建表的时候创建好 | |
| 删除索引 | drop index 索引名 on 表; |
主键(PRI)：
| 创建主键 | alter table 表名 add primary key (字段); |
| 主键可以和auto_increment连用，让主键数字字段实现 自加1 | |
| 删除主键 | alter table 表名 drop primart key; |
外键：
| 创建外键 | alter table 库名.表名 add foreign key(自己表中作为外键的字段) references 被参照表(被参照字段) on update cascade on delete cascade; |
| 查看外键 | show create table 表名\G; |
| # 返回数据中，外键有关字样如下 | |
| CONSTRAINT `外键约束名称` FOREIGN KEY (`外键的字段名`) | |
| 删除外键 | alter table 表名 drop foeign key 外键约束名称 |
| 删除的时候，外键名称一定是查看外键的时候查到的值 |
引擎：
| 查看所有引擎 | show engines; |
| 查看某表的引擎 | show create table 库名.表名\G; |
对用户：
| 用户赋权 | grant 权限列表 on 库名.表名 to 用户名@'客户端地址' identified by '密码' with grant opion; |
| with grant opion就是授权权限(允许用户给任何人赋权，只能授予小于等于自己用户的权限) | |
| 查看授权信息 | select * from mysql.user where user='创建的用户名'\G; |
| show grants for 创建的用户名; | |
| 查看某用户权限 | show grants for 用户名@'客户端地址' |
| 移除授权 | revoke 权限列表(或者 grant option) on 库名.表名 from 用户名@'客户端地址'; |
| 删除用户 | drop user 用户名@'客户端地址' |
数据类型
| 数值 | tinyint、smallint、int、bigint 整数类型(宽度) |
| float、double 小数类型 | |
| 数值类型 unsinged 连用时代表无符号类型，如果单数只写数值类型那默认为有符号类型 | |
| 数值后面的宽度代表最少显示多少位，不够的时候会用0补全，如果超过没有关系，默认为11 | |
| 字符类型 | char、varchar、text(大文本存储格式)、bigblob 字符类型(宽度) |
| 类型从左往右，存储范围越来越大 | |
| 字符类型后面的宽度代表字符串存储的位数，必须要写，而且存储字符的时候不能超过这个范围 | |
| char存储效率高，但是浪费存储空间，当前主流的存储字符的格式 | |
| varchar极其借阅存储空间，但是存储效率低 | |
| 枚举类型 | enum('值',……) set('值',……) |
| 指定字段下所有数据只能从指定的值中选择写出 | |
| set代表可以从中选择多个，enum代表从中只能选择写出一个 | |
| 注意： 不管是定义，还是在添加具体值的时候 值都必须用引号 | |
| 如果是set类型，插入数据类型的时候，'数据1,数据2……'这样列入多个数据 | |
| 时间类型 | time、date、year 三个时间参数类型 |
| 时间函数： year(时间字符串)、month(时间字符串)、date(时间字符串)、time(时间字符串) 字符串只需要按照年(4位)月(2位)日(2位)时(2位)分(2位)秒(2位)写数字即可，不用间隔符号 | |
| now()、curdate()、curtime() 获取当前时间 |
约束 day2
| null | 设置该列能否空(就是什么都没有，用null标示) | 默认允许赋null值 | |
| 赋值方法 | 直接在创建表的类型后面空格写上 可以写no null或者null或者不写 | ||
| 如果写not null，代表允许赋null值 | |||
| 如果什么写代表null，代表不允许赋null值 | |||
| key | 键值类型 | 普通索引(index) | |
| 唯一索引(unique) | |||
| 全文索引(fulltext) | |||
| 主键(primary key) | |||
| 外键(foreign key) | |||
| Default 默认值 | 当不被字段赋值时，使用默认值给字段赋值 | ||
| 不设置默认值时候 | 系统定义的是null | ||
| 赋值方法 | default (值，也可以是，用逗号隔开多个值) | ||
| Extra | 额外设置 | 字段是否设置为自动增加,默认没有自动增长功能 | |
索引
KEY标志： MUL
作用： 对记录集的多个字段进行排序，没有限制赋值的作用
类型包括： Btree(默认算法，二叉排序树)、B+tree、bash
注意：
1. 一个可以有多个索引字段；
2. 索引的字段内的值允许重复、为空
3. 索引只有用户进行筛选数据的时候在发挥作用：
意思就是 select * from 表 的时候顺序是存储的顺序，
只有 select * from 表 where 条件 的时候索引的排序才发挥作用
主键
KEY标志： PRI
特点：主键是表中唯一能标识记录的字段，一个表中只能有一个primary key字段，不允许重复和赋空值。
注意：
1. 主键，一定是唯一且非空的。
2. 一个primary key字段可以由一个表字段，也可以由多个表字段组成，如果由多个字段组成，则称之为复合主键，不过必须一起创建。而且创建复合主键，只能在全部字段创建后一起定义
3. 通常和AUTO_INCREMENT连用，这样可以让主键在有新数据的时候，自动那历史最大主键序号加1进行填充，这样就算填写数据的时候没有写，也不会报错，当然也可以手动赋值(必须符合所有的约束条件)。
外键
条件：
1. 表的存储引擎必须是innodb；
2. 两边的字段类型必须要一致；
3. 被参照字段必须要是唯一索引，不一定是主键，不过主键一定是可以作为被参考字段的
注意：
1. 如果创建外键的时候有 on update cascade on delete cascade 代表同步更新与删除，如果修改或删除被参考表中的被参考字段，外键所在表的数据也会随着该表
2. 被参考表创建后必须有engine=innodb
3. 外键也具有唯一性，只要被参考字段拥有非空性，就可以把外键设置为主键，具体就可以仿照已经创建表添加主键的操作
引擎day3
| 组件 | 介绍 | |
| 管理工具 | MySQL软件附带的诸多管理命令 | |
| 连接池 | 接口 | 接收命令 |
| 分析器 | 检查语法正确性 和 是否拥有访问权限 | |
| 优化器 | 做基本的存储检索的优化 | |
| 查询缓存 | 存储部分被查询的数据，当有人访问的时候把返回给用户的数据存在内存中 | |
| 如果用户查询的数据在内存空间的缓存中有，就直接从缓存中把数据反馈给客户端 | ||
| 存储引擎 | MySQL服务自带的功能程序，处理表的处理器 | |
| 不同的存储引擎有不同的功能和数据存储方式 | ||
| 硬盘文件系统 | 物理存储部分 | |
设置默认引擎：
sed -i '/\[mysqld\]/adefault-storage-engine=引擎 /etc/my.cnf 使用情况：
查询操作多的表适用myisam引擎，可以起到节省系统资源的作用
相反写操作多的表适用innodb引擎，可以加大同步访问量
锁机制
| 锁类型 | |||
| 读锁 | 共享锁 | 支持并发读，但不能写 | |
| 触发的操作有：select | |||
| 写锁 | 排他锁、互斥锁 | 独占操作，不能写和读 | |
| 触发的操作有：insert update delete | |||
事务
只有InnoDB引擎支持事务，其余引擎没有事务这个模块内容
默认，事务是自动提交的，也就是当命令执行后就将数据写入磁盘，如果关闭了自动提交，就要手动提交数据，命令是 commit;
如果数据没有提交，可以进行数据回滚，让数据回滚到上次提交时候的状态
| 事务特性(ACID) | ||
| Atomic | 原子性 | 事务整个操作是一个整体，要不全部成功代表成功，要不一个失败代表全部失败 |
| Consistency | 一致性 | 事务操作前后，表中记录没有变化 |
| Isolation | 隔离性 | 事务操作是隔离，相互不受影响 |
| Durability | 持久性 | 数据一旦提交不可改变，但是如果引擎支持回滚，数据提交前可以回滚找回数据 |
数据导入导出
1. 确认数据库导入文件所在目录，因为数据库只能从这个目录中导入文件
show variables like 'secure_file_priv';如果需要，可以修改这个目录，修改配置文件 /etc/my.cnf，然后重启服务
sed -i "/\[mysqld\]/asecure_file_priv='新目录'" /etc/my.cnf 2. 创建一个空表，其表结构和导入的文件的表结构相同
3. 导入数据
load data infile "/必须是系统配置的搜索目录/导入数据的文件名"
into table 已经创建好、每列数据类型相符的表
fields terminated by "列分隔符"
lines terminated by "行分隔符(一般都是 \n 代表换行)";
# 创建好后，我们可以给表中所有行加上序号
# alter table 表 add id int(2) primary key auto_increment first;导出数据
SQL查询语句 into outfile "/必须是系统配置的搜索目录/导出文件名"
fields terminated by "列分隔符"
lines terminated by "行分隔符(一般都是 \n 代表换行)";数据库匹配机制
数据库可以数值间比较大小，支持两个数值类型的字段比较
对于字段，也是可以比较的，可以直接两个字段比较 a!=b，也可以字段和具体字符比较，但是具体字符串必须用' '或者" "引起来 a='hello'
不过所有数据在数据库中，值为空，是用NULL代表，而不是什么都不写，所以 a is null 不等价于 a='' ，也不能用 a = null 来代替。
| 范围匹配 | |
| in (值1,值2) | 匹配这几个值中的值 |
| select 所有想显示的字段 from 表 where 字段 in (值1,值2); | |
| not in (值1,值2) | 匹配不在这几个值的任意值 |
| select 所有想显示的字段 from 表 where 字段 not in (值1,值2); | |
| between 数字1 and 数字2 | 匹配数字在这范围内的所有 |
| 可以等价于： ( 字段>=数字1 and 字段<=数字2 ) | |
| select 所有想显示的字段 from 表 where 字段 between 数字1 and 数字2; | |
| distinct 字段 | 去除重复显示 |
| select distinct 所有想显示的字段 from 表 | |
| 唯一的一种匹配，只能用于select，其他命令都不能用 | |
| 不过建议不要多个字段，因为在去重显示的时候，为了显示所有不同数据，可能有些字段还是会出现重复 | |
模糊匹配
用法： SQL命令 where 字段 like '通配符'
通配符： _ 代表任意的单个字符，可以理解为正则中的 .
% 代表匹配任意多个任意字符，可以理解为正则中的 .*
正则匹配
用法： SQL命令 where 字段 regexp '正则表达式'
数据库计算
支持四则运算、整除(div)、取余
| 聚集函数 | |
| avg(字段名) | 统计字段平均值 |
| sum(字段名) | 统计字段和 |
| min(字段名) | 统计字段最小值 |
| max(字段名) | 统计字段最大值 |
| count(字段名) | 统计字段的个数 |
| 全部的统计操作都是不计算NULL的 | |
| 因为聚集函数中的字段必须是数值类型，而只有字符类型的字段才存在 0字符 的情况，所以聚集函数不需要考虑在个特殊情况 | |
连接查询
多表查询
定义： 让两个表作笛卡尔积运算，得到一个新的大表，如果两个表中有相同含义字段可以，作为连接标示，这样可以去除一些冗余数据
语法： select 表.字段 表.字段 from 表1,……,表n where 条件 …… ;
注意：
1. 一旦有多表查询，每个字段前面必须写上表名
2. 连接如果不加相关条件约束，就是表1的一行信息和表2的每行信息组成若干行，依次类推，得到的就算表1的行数*表2的行数……行数的内容，这就是笛卡尔积
一般情况下 使用自然连接 也就两个表的唯一标识是一样的，所以通常的语法模板就是：
select 表.字段(或者*) from 表 1,……,表n where 表1.主键 = 表2.主键 …… and 表n-1.主键 = 表n.主键 ……其他条件…… 子查询
定义： 把内层查询结果作为外层查询的查询条件，实现较为负载的查询需求
语法： select 字段 from 表 where 条件 (select 字段 from 表 where 条件);
原理： 把内层查询表的输出作为一个具体值，参与外层表筛选条件的组成部分
注意：
1. 内层查询的表不一定和外层查询的表是一个表，随便都可以
2. 不管内层外层表都可以是多表组成
3. 当内层查询的返回结果有可能是多个不同的值的时候，条件只能用 in 或者 not in ，如果确定只可能有一个值，那就可以用=或者!=等
左连接 、右连接
语法：
左连接：select 字段 from 表(左) left join 表(右) on 条件；
右连接：select 字段 from 表(左) right join 表(右) on 条件；
原理：
左连接就是以左表为主，右表为辅 ； 右连接与之相反，右表为主，左表为辅
把主表的所有信息列出来，辅表中有满足条件的值就写上，没有就用NULL代替
注意：
1. 不管条件如何，主表中所有信息都是会显示出来的，而条件成立的情况下，才输出右表数据
2. 如果条件恒成立，那么左连接或者右连接就变成了多表查询
3. 如果条件恒不成立，那么输出所有主表中的所有数据，然后辅表中的所有数据都不输出，所有代替的是null。
用户授权 day4
| 表 | 记录内容 |
| user | 存储授权用户的访问权限 |
| db | 存储授权用户对数据的访问权限，用户对某个数据库拥有的权限 |
| tables_priv | 存储授权用户对表的访问权限 |
| columns_priv | 存储授权用户对字段的访问权限 |
grant 权限列表 on 库名.表名 to 用户名@'客户端地址' identified by '密码' ;
# 如果赋予用户 授权权限(允许用户给任何人赋权，只能授予小于等于自己用户的权限)
grant 权限列表 on 库名.表名 to 用户名@'客户端地址' identified by '密码' with grant opion;| 权限列表( 权限可以理解为， 可以使用 用同名的命令的权力 ) | |
| all | 代表所有权限(也称为完全权限，但是其中不包括授权权限) |
| select | 代表查看权限 |
| update | 代表修改表内容权限 |
| update(字段1，字段2) | 代表具体修改某表中某几个具体字段的修改权限 |
| delete | 代表删除表中信息权限 |
| insert | 代表添加表信息的权限 |
| create | 代表创建权限 |
| *.* | 代表所有库所有表， |
| 具体库.* | 代表某库的所有表， |
| 具体库.具体表 | 代表就算具体到某库某表的权限， |
| 一般不存在 *.具体表 的情况，因为很少所有库中都有同名的表。 | |
| 客户端地址： | |
| localhost | 代表用MySQL服务器进行本地登陆 |
| 192.168.10.100 | 指定一个IP的单个主机 |
| server.Lyu.com | 匹配一个指定域名的主机(能被DNS解析) |
| 192.168.10.% | 指定一个网段 |
| %.Lyu.com | 匹配一个域名(该域名要能被DNS解析) |
| % | 匹配所有主机 |
| 客户端连接数据的时候，权限匹配，从上之下，匹配即停止，然后开始按照其被配置的权限进入数据库 | |
权限部分知识点详细解释：
1. 我们有时候会发现，我们授予权限，已经登陆的用户的权限可能不即时有效，，也许查看权限的时候，权限是不存在的，但实际操作却表现出权限允许，这是时候，我们退出重新登陆一下即可，权限就会同步完成量，当然这只是小概率事件。
2. 权限赋予操作实际上是进行追加。
说具体点就是，假如我现在的当前用户拥有test库的select权限，然后被赋予量test库下的test_table表的修改权限，实际上为对test_table拥有读写权限。换言之，对高层次(库->表->字段)的权限会继承到下层次中，而所有权限只会一直添加，不会覆盖，想要去除，只能删除具体的权限。
3. 权限中USAGE是比较特殊的，它指的是进入数据库或表的权限，他不用进行赋权，默认只要用户有任何一个权限，就默认拥有该权限，如果拥有某库的表的权限，而没有该库的任何权限，也会默认给你该库的usage权限，因为如果没有进入权限，对表的所有权限也就没有了任何意义。
4. 授权可以授予一个不存在的库或者表的权限，这样被授权的用户就可以自己创建一个被授权的库或者表。
说具体点就是，假如我现在的用户拥有test库的完全权限，而这个库并不存在，那么我就可以创建一个名为test的库，并用于其全部的权限
5. 库名为 information_schema，是内存映射出来的，实际不存在，是一个假库，不能进行任何操作
6. 如果用户对mysql库没有insert的权限，那么即使给他 授权权限也没用，他不能创建任何一个用户，
如果没有update权限，那么他不能他不能给任何存在的用户授予任何权限，因为所有的权限信息都是记录在mysql库里面，所以，如果要给授权权限，一定是要对mysql库有相应的权限的。
7. 完全权限指的是除了授权权限外的所有权限，也可以理解为权限列表中的所有权限，而all指代的是完全权限
8. 撤除用户的权限只能一个一个撤销，不能越级撤销，而且撤销的权限必须是的当初创建时候写的权限，必须一模一样。
说具体点就是，假如我删除test数据库的的所有权限，这时候会发现，单独授权给test_table的权限还在，所以还要撤除表的权限
还有一个情况，假如我只授予了用户拥有test数据库所有权限，这时候为想只删除test库下的test_table表的权限，这时候会报错，因为只能撤销当初创建的权限。
9. 即使撤除所有权限，用户只要存在，就有一个登陆的权限，只不过什么都看不到也不能才做，显示权限的时候，就是如下的一个标示。
GRANT USAGE ON *.* TO 'test'@'localhost'
如果用户没有test数据库的任何权限，但是拥有该数据库下一个表test_table的权限，那么查看用户权限的时候，一定是有下面的提示，他标示我可以进入这个数据库
GRANT USAGE ON test.* TO 'test'@'localhost'
10. 删除用户和撤除用户所有权限的区别，如果删除用户，那么其所有权限也将全部消失，再次创建的时候也没有那些权限了，如果撤除用户的所有权限，用户仍然可以登陆，但是什么操作都没有，和删除用户实际上作用差不多，但是一般不是删除用户而是修改其权限。
11. 权限被拒绝的报错信息，看到这些报错，就可以明白，是权限出了问题
# 不拥有授权权限，进行授权操作的报错
ERROR 1045 (28000): Access denied
# 不拥有具体权限，进行权限操作的报错 (举例drop没有权限)
ERROR 1142 (42000): DROP command denied
12. 用户登陆的时候，在主机名匹配中先后优先级的区分，对一个用户的具体的主机名进行的权限设置会覆盖统配
具体说，如果给 mydb用户的localhost 单独设置权限为select， 而我们又设置量 mydb用户的 % 登陆的时候通配权限为all，用户用localhost主机登陆的时候， 拥有的权限就是select。
数据库备份
完全备份(这些备份都是用root进行的系统命令操作的)
物理备份 就是把存储数据库的文件全部拷贝或者压缩保存下来
物理恢复 把备份的数据库文件再拷贝回原来的位置即可
不过有两点要注意：
1. 授予权限，因为所有的操作都是root执行的，其所有者和所有组都是root，我们要改成mysql，不然数据库起不来
2. 恢复前最好先停掉mysqld的服务，然后再做恢复操作。
逻辑备份 mysqldump -u 用户 -p密码 保存的数据 --flush-logs > 路径/备份名.sql
逻辑恢复 mysql -u 用户 -p密码 库名 < 路径/备份名.sql
库名的书写： 具体情况分析，在任何情况下，库名写上都是不会错的，不过有的时候是可以不写库名的，有的时候不写库名会报错，如果备份数据的时候备份有库的信息，那么就不用写库名，如果没有，那就必须写一个已经创建的库名
具体说，当备份的时候，保存数据是 所有库(-A) 或者 多个库(-B) 的时候，不用写库名，其他时候必须写一个已经创建好的库。
差异备份
使用binlog日志，其优点： 记录除查询以外所有SQL命令，实时备份，可以用于数据恢复，是配置mysql主从同步的必要条件
原理： 通过实时记录，所有用户对数据库的操作，在需要恢复数据的时候，可以重新把操作进行一次，因为是差异备份，还原到上次的完全备份后，把到想恢复到的时间点的命令调出执行一遍就可以恢复成功数据
存储binlog有2种文件：
以数字结尾的是记录日志内容的，数据记录在数字最大的哪个文件中，而日志信息也是按照文件后缀名从小到大记录所有修改命令
以index结尾的是记录索引内容的，标示有哪些日志文件。
日志有三种记录格式 day5
配置文件的基本解释
binlog_format="mixed" #选择使用哪个记录格式
server_id=66 # 一个标示，一般用主机名编号
log_bin[=日志存储位置/名字] # 启动binlog日志备份，并指定文件名前缀
# 如果不写日志存储位置，默认存在/var/lib/mysql/下
# 如果不写等于和名字，就启动binlog并以当前主机名作为文件名前缀
max_binlog_size=大小 # 指定日志达到多大存储空间的时候，创建一个新的日志文件存储，默认为500M备份后，查看日志信息的方法就是
mysqlbinlog 路径/日志名 日志信息中有很多行是 # at 数字 这是一个偏移量，可以理解为一个时间点，在选项中用于分隔标示日志信息，如果选项写了开始和结束的偏移量，就是把这两个偏移量间的日志信息返回给客户端
调出指定范围的的日志信息
mysqlbinlog --start-position=偏移量 --stop-position=偏移量 路径/日志名
mysqlbinlog --start-datetime=时间 --stop-datetime=时间 路径/日志名数据恢复 原理很简单，恢复到日志记录前的完全备份，然后把日志中的想要执行的时间取出，重新执行命令
mysqlbinlog --start-position=偏移量 --stop-position=偏移量 路径/日志名 | mysql -u 用户 -p密码
mysqlbinlog --start-datetime=时间 --stop-datetime=时间 路径/日志名 | mysql -u 用户 -p密码 注意：
1. 如果完全备份是用冷备份，而且没有修改日志存储位置的话，要先把日志信息拷贝出来，再恢复完全备份
2. 查看恢复时间点的时候，命令后面要找到commit字段后面的时间才算完整，否则最后一个命令并不会执行完毕
3. 如果以时间作为分隔的标示。开始和结束写的那秒的操作不会进行，可以再多写 1秒 即可
4. 有的时候一秒中有很多操作，如果这秒中有不想要的操作，只能从日志中查看偏移量，以偏移量为分隔标示。
5. 很多时候，我们都是在进行一个误操作然后想恢复，那么我们想恢复数据到的时间点就在最后的时间点，这时候我们可以
总结：
1. 修改配置文件/etc/my.cnf，打开binlog日志记录功能
2. 使用mysqldump进行完全备份
3. 开启服务，在需要的时候先重新恢复完全备份，然后使用日志进行恢复即可。
4. 每隔一段时间更新一下完全备份的数据，并且刷新日志存储。
innobackupex
安装percona
装包，在资源中有上传rpm包，当然包括一个依赖包，两个一起安装，还有一些其他的依赖包，一般的iso镜像中是有的，就没列上
装完就可以使用命令进行备份了
命令格式： innobackupex 选项 备份信息保存的位置
选项 day5 --export 指定导出备份的的存储目录
命令行下进行备份，以及恢复的操作
完全备份命令
innobackupex --user 用户名 --password 密码 其他选项 备份信息保存的位置 注意：
1. 如果没有 --no-timestamp 这选项，备份信息存储在设置的存储位置之下，一个以时间命名的子文件夹下
2. 必须备份mysql、sys、performance_schema这三个系统库
增量备份命令
innobackupex --user 用户 --password 密码 --no-timestamp 其他选项 --incremental /增量备份信息存储位置 --incremental-basedir=/上一次备份存储位置 注意：
1. 上一次备份年具体的存储位置，如果上一次存储没写 --no-timestamp 选项，那么他的具体存储位置是写的位置下的时间命名的目录
2. 如果上一次也是增量备份，也可以继续这个操作，这就是在上次增量备份的基础上，再进行增量备份
恢复数据
1. 停止服务
systemctl stop mysqld2. 删除数据库当前文件内容
rm -rf /var/lib/mysql3. 准备恢复、合并日志
innobackupex --apply-log --redo-only /完全备份位置
innobackupex --apply-log --redo-only /完全备份位置/ --incremental-dir='第一个增量备份位置'
innobackupex --apply-log --redo-only /完全备份位置/ --incremental-dir='第二个增量备份位置' ……4. 开始恢复
innobackupex --copy-back /完全备份位置5. 修改权限
chown -R mysql:mysql /var/lib/mysql6. 重启服务
systemctl restart mysqld* 删除增量备份文件(可不执行该操作)
# 因为所有备份信息合并到完全备份日志文件中了，所以可以删除增量备份文件
rm -rf '第一个增量备份位置' '第二个增量备份位置' 如果只想恢复一个表的数据
0. 进行备份，只要对表所在库进行备份，当然也可以对整个数据库备份
1. 停止服务
2. 在数据库中创建一个和要备份的表的结构一模一样的表，然后删除这个表的表空间
3. 将表信息从备份中导出 (如果是增量备份就先把所有增量备份合并到完备备份中)
innobackupex --user 用户 --password 密码 --databases="库名字" --apply-log --export /备份存储目录4. 将表数据拷贝到数据库文件中
cp 备份目录/库名/表名.{ibd,cfg,exp} /var/lib/mysql/库名/5.修改权限
6. 将数据导入到数据库中(这是mysql命令)
alter table 库名.表名 import tablespace; 对于innobackupex操作总结：
1. 备份的基本操作： 备份的操作很简单，一条命令就能解决问题，如果是增量备份多两个选项指定备份位置和上一个备份存储的位置 innobackupex --user=用户名 --password=密码 选项(--incremental等) 备份存储位置
2. 备份恢复的基本操作： 停 删 准备，拷贝 修改，重启服务，具体解释如下：
基本的库恢复操作： 停 服务，删 除mysql文件，准备 恢复(--apply-log)，拷贝 恢复(--copy-back)，修改 权限，重启服务
完全备份恢复就是一步一个命令完成 ； 增量备份恢复准备恢复阶段需要把所有增量数据合并到最初的完全配置文件中 。
单个表的恢复：停 服务，删 除表空间(手动创建一个表结构)，准备 恢复(--apply-log、--export) , 拷贝 文件(.ibd,.cfg,.exp)，修改 权限，重启服务
各种备份对比：
| 物理备份 | 将所有数据保存下来，简单粗暴，用的不多 |
| 逻辑备份 | mysqldump将数据库备份成一个文件，如果想在另一个服务器上搭建一个一模一样的数据库，用这个备份方法 |
| 日志备份binlog | 做主从同步的时候，用来记录每个数据变化的操作 |
| percona的innobackup备份 | 备份数据库最好的备份方案，可以将备份数据保存到其他存储服务器中，能做一个数据的完全备份以及后来每个阶段的增量备份。 |
主从同步
理论执行过程： 主库开启binlog日志记录自己的所有修改操作的信息，然后从库开启IO和SQL线程监听主库，当发现主库有变化了，就把操作同步过来。
原理： 主库开启binlog，就会有偏移量记录每个修改操作，然后从库在建立主从的时候会记录一个偏移量，当IO线程发现主库最新偏移量和当前从库记录的偏移量不同的时候，就会把自从库偏移量记录位置到主库最新偏移量的代码传送到从库的中继日志中，修改从库激励的偏移量，然后通知SQL线程执行SQL语句。
工作过程： 首先做出从同步前，两个库的数据结构必须一模一样，然后主库开启binlog功能，当有一个修改指令生效后，binlog就会被修改，当其被修改，其当前的pos偏移量就会变化，这时候从库的IO进程发现，偏移量和自己记录的不相同，从库就会通过IO进程远程连接主数据库，获取从自己记录的偏移量开始的binlog中的内容，并记录到从库的中继日志中，最后SQL进行执行日志内容。
配置的排错思路：
主库：
1. 查看自己的mysql库中user表，看看也没有创建给从库连接的用户repluser
2. 查看自己的binlog日志是否正常工作
从库：
1. 查看配置文件中的server_id信息
2. 尝试用命令拿指定的用户和密码连接主库
3. 查看slave状态 ( show slave status )
4. 如果IO出错查看slave的报错信息，可能是编号(偏移量)有问题，也可能是日志名写错了
5. 如果SQL出错，可能是没有做主从同步前让两个数据库的所有内容完全相同
| 模式名称 | 介绍 | 优点 | 缺点 |
| 单向复制(一主一从) | 一个主库一个从库，从库同步记录主库的操作 | 最简单廉价的主从模型 | 任何一个单点故障都会破坏主从结构 |
| 一主多从 | 一个主库多个从库，多个从库一起同步主库 | 多个从库可以提高信息冗余安全 | 如果主库单点故障就会让所有的从库报错 |
| 链式复制(主从从) | 一个主库，一个主从库，一个从库 | 主从库作为主库的从库，保证其数据安全，从库作为主从库的从库，保证其安全，这样，如果主库出故障，主从库和从库依然可以工作 | 如果主从库单点故障就会让从库报错 |
| 互为出从(主主) | 两个数据库互相都是对方的主库，也是对方的从库 | 如果三个设备都实现主主，就可以单点故障 | 严重浪费资源 |
配置主从同步
0. 确认搭建主从同步前必须先要让从库所有数据和主库一样，
如果不一样，用mysqldump对主库进行完全备份，然后在从库上执行同步操作。
1. 配置主库
1.1 启用binlog
修改配置文件： /etc/my.cnf 启动binlog日志功能
log_bin=[ 日志存储位置/ ]日志名(自定义，不过最好每个数据的不一样，有一定标示型；不写日志位置默认存在/var/lib/mysql/下)
server_id=编号(每个数据库要都不一样)
binlog_format="mixed"1.2 用户授权
mysql> grant replication slave on *.* to repluser@'%' identified by '密码';1.* 查看当前正在使用的binlog日志(前往从库，进行远程登陆)
[root@mysql_slave ~]# mysql -h 主库IP -u repluser -p密码 2. 配置从库
2.1 指定server_id
修改my.cnf配置文件，只需要添一行 server_id=编号 ，这编号和主库不能一样
2.2 指定主库信息(对从库自己的数据库进行配置)
mysql> change master to
master_host='主库的IP', master_user='repluser', master_password='密码',
master_log_file='主库的日志', master_log_pos=主库的偏移量;| master_host | 指定主库 |
| master_user | 指定用什么用户登陆 |
| master_password | 配置从库登陆主库的密码(主库配置设置的密码) |
| master_log_file | 指定主库的日志，最好在主库上用show master status查看，然后复制到这里出来 |
| master_log_pos | 指定主库的偏移量，最好在主库上用show master status查看，然后复制到这里出来 |
2.3 启动slave程序
mysql> start slave;
# 停止的命令是 stop slave;2.* 查看slave程序状态(查看 Slave_IO_Running 、 Slave_SQL_Running 是否成功打开)
mysql> show slave status\G; 总结：
配置主库： 开启binlog(包括server_id) 、 授权一个给从库连接的用户
配置从库： 给自己指定一个server_id 、 修改指定主库的信息 、 开启slave服务
注意点：
1. 主从同步中，如果从库出现故障，一段时间没有办法进行同步，在过段时间后，进行再次连接进行同步后，会把原来没有同步的部分也同步上，因为一旦从库发现偏移量不同，就会进行同步，而且同步的范围是从从库记录的偏移量，也就是断开连接前记录的最后一个偏移量，所以他能在恢复连接后完成所有同步操作；
2. 主从同步的命令不会记录在 从库 的binlog日志上，也就是说从库即使打开量binlog，如果没有特殊配置，那么从库同步后执行的所有SQL命令并不会记录到自己的binlog日志中。
一主多从
原理： 多个数据库作为从库，连接一个主库，主库发生修改后，所有从库会立刻跟着修改
配置： 先要配置一个主库，配置完成后让每个从库都像刚刚步骤配置一模一样即可
作用： 任意一个从库出现问题都不会影响主从结构。
主从从
原理： 让一个数据库服务器，即做从库，也做主库，这个主从库连接主库，做这主库的从库，同步其操作，然后再让其他数据库服务器做自己的从库，形成链式结构
举例解释： 假如现在有三个数据库服务器a、b、c ，a作为主库用来和客户端连接，b作为直接连接主库的从库，同步a库中的所有操作，然后c也是从库也想同步a库操作，只不过这次他不做a的从库，而是做b库的从库，这样a库发生数据变化，b库同步a库操作，c库同步b库操作，间接的让c库和a库实现同步。
配置： 先要配置一个主库，然后配置一个连接主库的从库，然后让该从库成为一个主库，最后让另外一个从库连接这个主从库。
其他操作就不具体说，和主从同步的操作一样，单独说一下连接主库后的从库如何开启主库功能，主要就是添加一个配置参数：log_slave_updates
所以配置主从从，唯一要解决的问题就是，主从库同步的命令不会写入主从库的binlog文件中，如果主从库的binlog文件不变，从库也就没办法实现同步。配置中的log_slave_updates就是让IO监听后传递的日志信息不单单发给中继日志，也发给binlog日志。
主主
原理： 两个库都是主库，而且也是对方的从库，也就是说，操作一个库，另外一个库都会同步操作
配置： 两个数据库都把主库和从库的参数写出来，然后写出主从关系
注意： 搭建主主结构的时候一定不能开启链式复制的参数
配置某库的同步权限
在主配置文件中配置
主库：修改配置文件/etc/my.cnf
# 允许同步的库
binlog_do_db=库名列表
# 不允许同步的库
binlog_ignore_db=库名列表从库：修改配置文件/etc/my.cnf
# 指定只同步的库
replicate_do_db=库名列表
# 指定不同步的库
replicate_ignore_db=库名列表| 复制模式 | 模式执行特点 | 优点 | 缺点 |
| 异步复制 | 主库执行完一次事务后，立刻返回数据给客户端，不关心从库是否接收 | 回复速度极快 | 不管主从结构，容易数据丢失 |
| 全同步复制 | 主库执行完一次事务，且所有从库都执行完，再返回数据给客户端 | 主从结构稳定，数据保存完整 | 回复速度极慢 |
| 半同步复制 | 主库执行完一次事务，且至少有一个从库执行完，再返回数据给客户端 | 介于异步和全同步间，所以比较均衡 | |
不做配置的情况下是异步复制
不过就目前看来半同步表现最优，所以，更多的时候会配置为半同步
默认不做配置，都是异步复制的方式。
如何配置半同步复制
临时启动(即时生效)
1. 启动动态加载模块
# 查看是否支持模块 (主库和从库都要查看)
mysql> show variables like 'have_dynamic_loading';
# 如果是yes就不用操作，如果是no就在配置中进行修改 (数据库默认打开了)2. 安装插件
# 主库安装
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
# 从库安装
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';3. 查看插件情况
mysql> select plugin_name, plugin_status from information_schema.plugins where plugin_name like '%semi%';4. 启用半同步复制
# 主库启动
mysql> set global rpl_semi_sync_master_enabled=1;
# 从库启动
mysql> set global rpl_semi_sync_slave_enabled=1;5. 查看半同步状态
mysql> show variables like 'rpl_semi_sync_%_enabled'; 永久启用半同步复制
修改主配置文件 /etc/my.cnf
# 对主库配置
[root@mysql ~]# sed -i '/\[mysqld\]/aplugin-load=rpl_semi_sync_master=semisync_master.so' /etc/my.cnf
sed -i '/\[mysqld\]/arpl_semi_sync_master_enabled=1' /etc/my.cnf
# 对从库配置
[root@mysql ~]# sed -i '/\[mysqld\]/aplugin-load=rpl_semi_sync_slave=semisync_slave.so' /etc/my.cnf
sed -i '/\[mysqld\]/arpl_semi_sync_slave_enabled=1' /etc/my.cnf
# 如果数据库即是主库又是从库
[root@mysql ~]# sed -i '/\[mysqld\]/aplugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"' /etc/my.cnf
sed -i '/\[mysqld\]/arpl_semi_sync_master_enabled=1' /etc/my.cnf
sed -i '/\[mysqld\]/arpl_semi_sync_slave_enabled=1' /etc/my.cnf重启数据库服务即可生效
把从库撤除出来，让其独立工作
主库： (其实没必要修改，撤除从库即可)
1. 删除用于给从库连接的用户repluser
2. 看情况关闭binlog
从库：
1. 停止服务
[root@mysql_slave ~]# systemctl stop mysqld2. 删除相关文件
[root@mysql_slave ~]# cd /var/lib/mysql
[root@mysql_slave ~]# rm -f 主机名-relay-bin.* relay-log.info master.info 3. 重启服务
[root@mysql_slave ~]# systemctl restart mysqld把从库撤除出来，让其独立工作
主库： (其实没必要修改，撤除从库即可)
1. 删除用于给从库连接的用户repluser
2. 看情况关闭binlog
从库：
1. 停止服务
[root@mysql_slave ~]# systemctl stop mysqld2. 删除相关文件
[root@mysql_slave ~]# cd /var/lib/mysql
[root@mysql_slave ~]# rm -f 主机名-relay-bin.* relay-log.info master.info 3. 重启服务
[root@mysql_slave ~]# systemctl restart mysqld读写分离
原理： 两个服务器，进行主从同步，然后客户端访问调度服务器，写操作交给主库处理，读操作交给从库操作，然后数据传递给代理服务器，代理服务器再转发给客户端
构建思路： 搭建好MySQL主从配置，添加一个MySQL代理服务器，客户端通过代理主句访问MySQL数据库，然后代理服务器把写请求发给主库，读请求交给从库
拓展分析 其数据传递和LVS的NAT模式很像，数据的传进传出都是需要经过代理服务器，如果代理服务器有两个网卡，可以实现数据库服务器用内网IP，代理服务器用外网IP和外界进行数据交互
调度服务器：
1. 装包 maxscale
2. 配置
我们开启读写分离，一般是注释掉所有的只读配置，只做读写操作的配置，因为软件会自动根据后台服务器的主从关系，确定读写分离给哪个服务器 (修改配置文件/etc/maxscale.cnf)
[maxscale] # 定义线程个数
threads=auto
[server1] # 定义一个数据库服务器(按照需求，创建多个)
type=server
address=后台数据库的IP # 后台数据库服务器的IP地址
port=3306 # 定义端口一般是(3306)
protocol=MySQLBackend
[MySQL Monitor] # 定义监控服务(检查主从多个服务器的服务情况，以及主从架构是否正常运行)
type=monitor
module=mysqlmon
servers=server1, server2 # 添加监控的服务器
user=监控用户 # 监控时候用于连接后端数据库的用户
passwd=密码 # 用户登陆时候的密码
monitor_interval=10000
[Read-Only Service] # 定义只读数据库服务器
type=service
router=readconnroute
servers=server3, server4 # 添加只读的数据库服务器
user=连接用户 # 添加用于验证客户端输入用户能否登陆的连接用户名
passwd=密码
router_options=slave
[Read-Write Service] # 定义读写数据库服务器
type=service
router=readwritesplit
servers=server1, server2 # 添加读写的数据库服务器
user=连接用户 # 添加用于验证客户端输入用户能否登陆的连接用户名
passwd=密码
max_slave_connections=100%
[MaxAdmin Service]
type=service
router=cli
[Read-Only Listener]
type=listener
service=Read-Only Service
protocol=MySQLClient
port=4008 # 只读服务器的端口号
[Read-Write Listener]
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4006 # 读写服务器的端口号
[MaxAdmin Listener] # 定义管理服务
type=listener
service=MaxAdmin Service
protocol=maxscaled
socket=default
port=自定义一个端口 # 管理服务器的端口号3. 授权
grant replication slave, replication client on *.* to 监控用户@'%' identified by '密码';
grant select on mysql.* to 连接用户@'%' identified by '密码';
grant all on db2.t1 to 客户端访问用户@'%' identified by '密码';4. 启服务(不用systemctl启动，软件命令)
maxscale -f /etc/maxscale.cnf
# 停止服务，只能杀死进程
pkill -9 maxscale5. 测试
# 测试调度服务器的管理服务(查看能否显示出主从服务器的状态，账号密码是默认的)
[root@maxamin ~]# maxadmin -uadmin -pmariadb -P管理服务的端口号
MaxScale> list servers
-------------------+-----------------+-------+-------------+--------------------
Server | Address | Port | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
server1 | 172.100.10.1 | 3306 | 1 | Slave, Running
server2 | 172.100.10.2 | 3306 | 2 | Master, Running
-------------------+-----------------+-------+-------------+--------------------# 测试读写分离是否成功
[root@client ~]# mysql -h调度服务器IP -u 访问用户 -p密码 -P读写服务器端口(如果配置没有修改默认4006)
# 检查读(如果显示从库主机命，那说明读操作没有问题，读的是从库)
mysql> select @@hostname;
# 检查写(添加一个数据，如果读没有问题，添加后，前往主库和从库，都能查看到数据，就说明 读写分离成功)
mysql> insert into 库.表 vlaues(数据)MySQL多实例
多实例： 让一台物理机可以运行多个数据库服务
多实例的作用： 节约成本 、 提高硬件利用率
配置：
1. 装包 安装一个支持多实例的mysql软件版本
2. 修改主配置文件(完整修改/etc/my.cnf)
[mysqld_multi]
mysqld = /软件包存储的位置/mysql_safe
mysqladmin = /软件包存储的位置/mysqladmin
user = root
[mysqld编号] # 创建一个mysql实例
port = 3307
datadir = /用于存储mysql的目录(需要自己创建)
socket = /用于存储mysql的目录/mysql.sorck(自定义存储socket文件名)
pid-file = /用于存储mysql的目录/mysql.pid(自定义存储pid号的文件)
log-error = /用于存储mysql的目录/mysqld.err(自定义存储日志错误信息的文件)
3. 初始化 、 启动服务
3.1 创建存储各个实例的目录
3.2 创建mysql用户
3.3 启动
/软件安装/mysqld_multi start 启动数据库编号4. 检查服务状态
netstat -utnlp | grep mysqld执行：
/软件安装位置/mysql -uroot -p密码 -S sorck文件位置注意：
1. 启动服务前要确认创建量用户mysql
2. 主库挂了就全部挂了，从库挂了就还可以使用，只不过读写都是给主库了
主配置文件/etc/my.cnf的全部配置解析
[mysqld]
# 配置默认参数
default-storage-engine=引擎
# 设置密码复杂性检查参数
validate_password_policy=0
validate_password_length=1
secure_file_priv='新目录'
# 当需要破解密码的时候，添加的参数，不过需要把其他所有password相关的参数注释掉，在破解密码后再恢复哪些password参数，注释掉skip这个参数
skip-grant-tables
# 开启binlog日志、主库的基本配置
log_bin=[ 日志存储位置/ ]日志名(自定义，不过最好每个数据的不一样，有一定标示型；不写日志位置默认存在/var/lib/mysql/下)
binlog_format="mixed"
# 配置主从的时候都必须写的一个数据库编号
server_id=编号(每个数据库要都不一样)
# 配置主从从，链式复制的配置
log_slave_updates
# 主库限定某个库是可以或不可以被同步
binlog_do_db=库名列表
binlog_ignore_db=库名列表
# 从库设置只同步或者只不同步哪些库
replicate_do_db=库名列表
replicate_ignore_db=库名列表
# 主库配置半同步
plugin-load = "rpl_semi_sync_master=semisync_master.so"
rpl_semi_sync_master_enabled=1
# 从库配置半同步
plugin-load = "rpl_semi_sync_master=semisync_slave.so"
rpl_semi_sync_slave_enabled=1
MHA
简介： MHA是一套优秀的实现MySQL高可用软件，其能确保故障切换过程中保持数据的一致性，达到真正意义上的高可用
环境准备：
1. 准备设备 一个管理服务器 、 一个主库 、 至少一台待选主库 、 至少一台从库
2. 所有库都安装好perl基本软件包(资源中MHA压缩包中有)
3. 主库要配置主库配置也要开启从库的功能，不过可以先不开启slave功能，然后所有其他从库都指该主库的IP
4. 待选主库，和主库配置相似，只不过要指定slave主库为上面配置的主库IP
5. 从库配置成主库的从库，实现主从同步
6. 预先设定一个给客户端连接的VIP，然后把VIP绑定到主库的网卡上
原理：
主库 和 待选主库、从库 形成一主多从的结构 ， 管理服务器就是安装 MHA软件 用来管理整个主从集群
当主库出现问题，管理服务器会自动根据主库中的二进制日志，识别所有待选主库中数据最新的更新日志，最接近的那个待选库就升级成为新的主库，自动其他所有库的都指向该库，做该库的从库，当原主库修复后，配置修改后，需要将其配置成为当前主库的从库
举例解释： 假设当前有主库m0 ，待选库ms1、ms2，从库s1、s2，现在ms1、ms2、s1、s2都是m0的从库，现在m0出故障量，管理服务器检测到后会将m0的二进制日志读取出来，并检测ms1和ms2的更新日志，最接近的假如是ms1，那么ms1就会成为集群的主库，ms2、s1、s2都是他的从库，进行工作，当m0修复完成量，他加入集群后，就会成为一个待选主库，必须配置成为ms1的一个从库，在ms1出故障的时候再和，ms2一起竞争主库。
而客户端的访问的是一个预设定的VIP，虚拟IP地址，谁做主库，这IP就被 MHA管理服务器 绑定在网卡上，那么客户端访问的是这个VIP，那么就一定访问的是当前担任主库的那个服务器
相关命令(每个命令后面必须跟上 --conf=配置文件的绝对路径)
| masterha_check_ssh | 检查MHA的SSH配置状况 |
| masterha_check_repl | 检查MySQL主从同步情况 |
| masterha_manager | 启动MHA |
| masterha_check_status | 检测MHA运行状态 |
配置：
1. 在管理服务器上创建管理命令
拿源码包解压，然后进入目录中，进行编译安装，然后进去寻找一些配置文件和管理命令
把perl编译安装的包中bin下有很多命令，将其拷贝到PATH下任意一个位置即可(例如：/usr/local/bin)
2. 创建主配置
源码编译的文件夹中 samples/conf/ 有个app1.cnf ，将其拷贝到自己规划的 MHA工作目录 ，编辑配置文件
[server default]
manager_workdir=/MHA工作目录
manager_log=/MHA工作目录/日志名称
# 当检测主库出现故障，会自动将主库切换给被选库中数据最接近的那个库
master_ip_failover_script=/MHA工作目录/master_ip_failover(MHA错误转移脚本)
# ssh远程的用户和端口号
ssh_user=root
ssh_port=22
# 主从同步使用的用户名和密码
repl_user=repluser
repl_password=密码
# 管理、检测 数据库集群的用户名和密码
user=root
password=密码
[server1]
hostname=主库的IP
candidate_master=1
[server2]
hostname=待选主库的IP
candidate_master=1
[server3]
hostname=从库的IP
no_master=1
修改脚本master_ip_failover(可以直接用tar包中的，也可以自己修改编译安装后，程序中的samples/scripts/的脚本)
my $vip = '预定义的VIP/24';
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down"; 3. 启动服务(必须确保把相关命令拷贝到PATH下，如果没有只能在命令前面加上命令所在的绝对路径)
3.1 检查ssh连接
[root@mha ~]# masterha_check_ssh --conf=/配置文件所在位置/app1.cnf
# 如果返回信息如下则代表成功，否则根据报错信息进行修正
[info] All SSH connection tests passed successfully.3.2 检查主从同步配置
[root@mha ~]# masterha_check_repl --conf=/配置文件所在位置/app1.cnf
# 如果返回信息如下则代表成功，否则根据报错信息进行修正
MySQL Replication Health is OK.3.3 启动管理服务
# 启动服务后，终端会宕死，这是正常的，再开一个终端检查服务状态(当然也可以放入后台，不过会出现提示信息，最好还是不要用这个终端了)
masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover
# --remove_dead_master_conf 代表当服务器出现故障，将其在配置文件中的内容删除
# --ignore_last_failover 短时间内频繁出现问题，就不工作4. 查看状态
masterha_check_status --conf=/etc/mha/app1.cnf
# 如果显示如下，代表服务启动失败
app1 is stopped(2:NOT_RUNNING).
# 如果显示如下，代表服务启动成功
app1 (pid:2298) is running(0:PING_OK), master:
# 如果显示如下，代表正在启动，等一会再查看即可
app1 monitoring program is now on initialization phase(10:INITIALIZING_MONITOR). Wait for a while and try checking again. 附： 如何判断管理服务器如何判断主库服务应该给哪个待选库：
计算机是根据，待选库中的中继日志的修改时间，时间较为新的那个待选库将成为主库
如果有新的从库加入集群，那么他们的日志修改时间就是加入集群的时间，当然如果后来还有数据添加进来，那时间就会一起被覆盖
注意点：
1. 服务MHA是一次性的，他检测到一次问题并调度解决后就不会继续检测了，服务也会立刻停止
2. 如果有 --remove_dead_master_conf 选项，那么可以在服务停止后随即人工再次开启监控服务，不过这时候，集群中不再有那个出现问题的服务器，配置文件中也没有与之相关的信息
3. 如果需要将出现问题的服务器重新假如集群必须，手动把这个服务器配置成为当前集群中主库的从库，如果原先的MHA服务添加了选项(--remove_dead_master_conf)，那么就需要修改配置文件，将这个服务器添加进去，最后手动重新启动 MHA服务。
创建视图命令详细解释
1. create or replace 的主要作用是，如果创建过视图，那么下面就是覆盖之前创建的视图结构
2. algorithm=算法 指定视图检索算法
这个算法产生的原因是：创建视图的时候，我们使用了一个查询语句，而我们视图也会被再次执行一些SQL语句操作，并且视图并不是真正存储数据的，这时候，我们就需要先后处理这两个语句到基表的数据中，那么这两个语句如何执行就成为了问题，这算法就是解决这个问题的，算法原理如下：
| undefined | 未定义模式(默认) | 当没有指定算法的时候使用这个算法，其算法其实merge算法，所以吧他和替换算法放在一起理解 |
| merge | 替换算法 | 在执行对视图的SQL语句的时候，算法会把创建时候的命令和对视图的命令结合起来，替换成为一个新的语句执行在基表上 |
| temptable | 实例算法 | 执行过程是先根据创建视图时候的查询语句将基表数据提取出来，放入内存中，然后再执行当前对视图的SQL语句 |
举个例子：
create view v1 as select r1,r2 from t1;
select r1 from v1;
若我们这么创建一个视图，并执行下面的查询操作，不同算法执行过程不一样：
merge替换算法，是把两个select命令(一个是创建时候的)结合成一个命令，执行于基表
temptable实例算法，是先执行创建时候的select命令把结果放在内存中，然后再以内存中的数据为表进行第二个查询语句
3. with 限制方式 check option 决定查找范围(主要应用在，基表是视图的情况下)
| local | 仅检查当前视图限制 |
| cascaded | 同时满足基表的限制(默认值) |
举例理解：
create view v1 as select r1,r2 from t1 where r1<100;
create view v2 as select r1 from v1 where r1>30 with local check option
create view v3 as select r1 from v1 where r1>30 with check option
这时候，我们的v2和v3视图都是基于v1创建的，其中v3没写具体的限制方式，使用的就是cascaded限制方式，v2视图的限制就只有r1>30，而v3的限制就是30<r1<10，具体来说就是：
如果我们想插入一条数据 r1值为10的值，v2和v3都不能执行，因为他们创建的时候要求r1>30
如果我们想插入一条数据 r1值为150的值，v2能执行，但v3不能执行，因为v2只检查自己的限制，150满足大于30，所以可以执行，而v2不可以，因为150并不小于100，所以v2不满足
如果我们想插入一条数据 r1值为50的值，两个视图都可以执行。
注意： 默认值只是当只写了with check option的时候生效，不写这个命令 视图不可被修改的情况
1. 视图创建的时候包含关键字，如SUM、MIN、MAX、COUNT、DISTINCT、GROUP BY、HAVING、UNION、UNION ALL等
2. 涉及到多表间的操作例如,join或者多表操作的时候
3. 基表是一个不能被更新的视图
注意点：
1. 视图跟着基表中的数据修改而修改，基表跟着视图的数据修改而修改(视图能修改的情况下)，视图可以理解为是基表的一个快捷方式，如果修改了视图信息那么表中信息也会变
2. 视图可以使用多表查询，SQL查询语句查出什么，视图就会创建成什么内容
3. 创建视图中的SQL查询，其中的select后面的数据可以取别名，而视图中的字段也是以这里取别名的样子出现
4. 视图不能创建索引，创建的时候不能使用子查询
5. 可以拿一个视图作为基表，如果只有一个视图，并且该视图可以被修改，那么以这个视图为基表做的视图也是可以进行修改操作的
6. 如果基表被删除，视图还在，只是数据都不见了，也无法读取视图
ERROR 1356 (HY000): View 'test.view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
但是如果之后创建一个同名的表，其中结构满足视图的需求 ，视图又将继续工作
存储过程
数据库SQL命令的判定执行过程：
每一条语句，不管多少行，分隔多少次，直到遇到一个分隔符号，才算一个完整的命令，而且只是一条命令，
在数据库中，命令换行没有任何含义，遇到分隔符算一条命令，一个命令可以写成一行。
命令delimiter 指定SQL语句运行中的分隔符号，默认为 ;
为了让存储过程可以完成写完，我们需要修改分隔符号为别的(自己选个不是特殊字符的符号即可，可以由几个组成，例如\\ // [] )，等后来再改回去即可。
创建存储过程基本模板：
delimiter []
create procedure 库名.存储过程名(参数类型 参数名 数据类型 , 参数类型 参数名 数据类型 ……)
begin
功能代码 ;
……
end[]
delimiter ;
执行存储过程(如果存储过程要求输入参数，必须在括号内写入相应的数据，详细看下面的调用参数的具体分析)
call 库名.存储过程名();删除存储过程
drop procedure 库名.存储过程名;查看服务器上的存储过程信息
show procedure status;
select db,name,type,body from mysql.proc where name='存储过程名'; 附： 对于存储过程的命令，如果进入了某个具体的库，可以不写库名，就以当前所在库为存储过程所在库。
变量
自定义变量:
设置 set @变量名=值;
| 全局变量 | 查看：show global variables like '%变量名的一部分%'; | 如果用set修改变量，那么会影响整个服务器 | |
| 使用： select @@全局变量名; | 通称为系统变量 | ||
| 会话变量 | 设置： set 变量名=值; | ||
| 查看：show session variables like '%变量名的一部分%'; | 如故被修改，只会影响当前连接的整个过程，另开一个连接的时候，变量不会发生变化 | ||
| 用户变量 | 定义与设置： set @变量名=值; | 客户端连接当前数据库的整个过程中有效，和会话变量的区别在于，对数据库性能没有影响 | |
| 使用： select @变量名; | |||
| 局部变量 | 定义：declare 变量名 defaults 默认值; | 这是在存储过程中定义变量的方式，只限于这个存储过程的执行过程中，可以理解为脚本中的内部变量 | |
| 设置： set 变量名=值; | |||
注意点
1. 使用select 后面加变量，代表的就是，输出变量值，如果后面加from 表等，不会等效于变量值的查询操作
举例： select first from table1; 输出的是table1表的first列的所有数据
但是set a = 'first'; select @a from table1; 输出结果就是按照table1的行数，每行输出的都是first字符串
存储过程中调用参数
定义时候的格式： 参数类型 参数名 数据类型
参数类型
| 参数类型 | |||
| in | 输入 | 将执行存储过程中，用户写的参数，赋值给类型后面的参数，并判定是否符合数据类型，如果是变量，执行存储过程中，变量的值可能被修改，但是退出存储过程后，变量还是原来进入存储过程前的值 | 执行时用户可以输入一个符合要求的具体数值也可以时一个变量 |
| out | 输出 | 不管这个参数也没有数据，开始存储过程的时候，这个变量都会变成空null，然后在存储过程内的语句可以修改整个变量中的值，并在结束存储过程后，变量也是以被修改后的值，前提是变量能被修改 | 执行时用户必须输入一个可以被修改的变量 |
| inout | 输入/输出 | 结合in和out，执行存储过程中，参数拥有执行前的变量值，在经过存储过程后，参数变量的值也会被修改 | 执行时用户必须输入一个可以被修改的变量 |
注意：
1. 所有代表参数的变量都可以是空null
2. 如果变量值变成null，那么就返回的时候也就是null
3. 参数类型in，参数变量的值会导入到存储过程中，但在执行完存储过程后，返回的时候，其值不会改变
而参数类型out相反，它的值不会导入到存储过程中，而且以运行存储过程，其值会强制变为空，然后执行存储过程，但执行存储后，返回的时候，其值会根据存储过程中的操作进行修改
参数类型inout就是结合两个的操作
4. 如果写成递归，那么创建的时候不会报错，执行的时候会报错，例子如下：
mysql> delimiter []
mysql> create procedure p4(inout x int)
begin
select x;
set x=x+1;
if x<15 then
call p4(x);
end if;
end[]
mysql> delimiter ;
mysql> set @x=1;
mysql> call p(@x);
+------+
| x |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
ERROR 1456 (HY000): Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine p4
数值运算
| +-*/ | 四则运算 | set @a=@a+1 |
| div | 整除运算 | set @a=10 div 3 ; 得到结果为3 |
| % | 取余运算 | set @a=10%3; 得到结果为1 |
条件测试
| >= > < <= = != | 数值比较大小 |
| between .. and .. | 在..与..之间 |
| or 、and 、 ! | 或 与 非 |
| in 、not in | 在范围内 不在范围 |
| is null | 为空 |
| like | 模糊匹配 |
| regexp | 正则匹配 |
流程控制
判断语句
if 条件 then
代码
else
代码
end if;循环
while 条件判断 do
代码
end while;repeat
代码
until 条件判断 end repeat; while和repeat的区别：
1. while是先判断后执行，再判断执行…… repeat是先执行一次再判断执行，所以while可能一次不执行，而repeat至少执行一次
2. while是条件成立执行，repeat是条件成立退出循环
死循环
loop
代码
end loop;注意：
局部变量必须直接写在begin--end内，必须直接写在这个板块中，不能在子模块内，否则会报错
也就是说循环体内或者判断语句内不能定义变量
Mycat
mycat是基于java的分布式数据库系统，为高并发环境的分布式访问提供解决方案
工作流程
Mycat有很多算法，每个算法都有专属的标识字段，根据这些字段，mycat可以找到数据具体存储的位置。
搭建mycat服务器
1. 装包
1.1 安装jdk，也就是java相关的软件包
1.2 安装 mycat-server 服务软件包
2. 配置
修改配置文件server.xml
<user name="用户名">
<property name="password">密码</property>
<property name="schemas">逻辑库名(用户看到的库名)</property>
<property name="readOnly">true(只读权限设定，不写这一行默认为读写权限)</property>
</user>配置文件schema.xml
<table name="travelrecord" dataNode="dn1,dn2(结点名,根据自己的数据库个数决定)" rule="auto-sharding-long" />
<!-- global table is auto cloned to all defined data nodes ,so can join
with any table whose sharding node is in the same data node -->
<table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2(这两行也是根据数据库个数决定)" />
<table name="goods" primaryKey="ID" type="global" dataNode="dn1,dn2" />
<!-- 指定数据存储节点的数据库名，以及这个数据库中存储数据的库名 -->
<dataNode name="dn1(结点名)" dataHost="localhost1(数据库名)" database="db1" />
<!-- 给每个数据库指定相应的信息 -->
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="数据库服务器IP:端口号(3306)" user="连接使用的用户名"
password="密码">
<!-- can have multi read hosts -->
</writeHost>
</dataHost>3. 对数据库服务器进行相关操作
sed -i '/lower_case_table_names/d' /etc/my.cnf
sed -i '/\[mysqld\]/alower_case_table_names = 1' /etc/my.cnf
systemctl restart mysqld4. 启动服务
mycat start
netstat -ntlup | grep :8066 注意：
1. 客户端连接的时候，发现有很多空表，这些都是要自己修改创建的
2. 不过创建的时候，需要根据数据库的具体算法进行，创建算法要求的字段(列)
系统优化
设置最大并发数
# 查看设定的最大并发数：
show variables like 'max_connections';
# 修改最大并发数
set max_connections=值;
# 查看历史访问最大并发数：
show global status like 'max_used_connections'; 设置的最优值：
max_connections = max_used_connections / 0.85
设定的最大并发值 = 历史最大并发数 / 0.85
查看连接的详细信息：
show processlist;| 连接超时(单位秒) | |
| connect_timeout | 等待连接超时，当用户在TCP三次握手连接的时候相应时间太久就会自动登出，一般时间不宜太高，默认10s |
| wait_timeout | 等待关闭连接不活动超时，当从最后一次SQL语句操作开始计时，到下一个SQL语句间隔时间，过长久断开，不过为了让SQL语句能完整的执行完，不宜太短，默认8小时 |
| 缓存控制(单位字节) | |
| key_buffer_size | 用于MyISAM引擎的关键索引缓存大小 |
| show global status like 'key_read%'; 如果查出来的值较低的时候提高这个缓存值 | |
| sort_buffer_size | 为每个排序的线程分配此大小的缓存空间 |
| 提高此值可以提高order的group的执行速度 | |
| read_buffer_size | 为排序读取表记录保留的缓存大小 |
| 影响SQL查询的相应速度，此值越大查询越快 | |
| tread_buffer_size | 允许保存在缓存中的被重用的线程数量 |
| show gloabal status like 'threads_%'; 查看线程状态，以这个值的常值为线程数最优 | |
| table_open_cache | 为所有线程缓存的打开的表的数量 |
| show global status like 'open%tables'; 查看已经打开的表，是理想比例<=95% | |
| 优化查询缓存设置 | |
| show variables like 'query_cache%'; | |
| query_cache_limit | 缓存最大存储限制 |
| query_cache_min_res_unit | 缓存最小存储单元 |
| query_cache_wlock_invalidate | 是否开启写锁，如果开启，当数据修改，就会自动把缓存中的数据删除，下次用户查看的时候就是到磁盘中找 |
| 优化后作用表现(命中情况越高说明优化的越好) | |
| show global status like 'qcache%'; | |
| Qcache_hits | 查询缓存中的命中情况 |
| Qcache_inserts | 查询请求总数 |
优化思路
1. 查看硬件使用情况(cpu 内存 存储空间)
2. 查看网络接口流量
3. 查看数据库服务运行时，参数配置
show variables;
show global status; 并发连接数量 超时时间 重复使用的线程数量
多个线程同时打开表的数量
查询缓存配置 索引缓存
处理不同查询方式mysql存储使用缓存设置
错误日志
默认已经开启，记录数据库所有错误信息
查询日志
记录mysql数据库中所有执行的命令
启动查询日志，修改配置文件/etc/my.cnf
sed -i '/\[mysqld\]/ageneral_log' /etc/my.cnf 不修改存储位置，默认存在 /var/lib/myql/主机名.log 文件内
如果需要修改查询日志的位置
sed -i '/\[mysqld\]/ageneral_log_file=存储位置' /etc/my.cnf慢查询日志
记录耗时较长或不使用索引的查询操作
启动慢查询日志，修改配置文件/etc/my.cnf
sed -i '/\[mysqld\]/alog-queries-not-using-indexes' /etc/my.cnf
sed -i '/\[mysqld\]/aslow-query-log' /etc/my.cnf 默认存储在 /var/lib/myql/主机名-slow.log
如果想修改存储位置
sed -i '/\[mysqld\]/aslow-query-log-file=存储位置' /etc/my.cnf默认统计的是查询时间超过 10s的查询记录，如果想修改，命令如下
sed -i '/\[mysqld\]/along-query-time=数字' /etc/my.cnf统计查看慢查询日志记录的sql命令
mysqldumpslow /var/lib/mysql/mysql51-slow.log
扫一扫
169
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
