MySQL Notes
MySQL NotesNotes:BASIC Knowledge数据类型以及属性修饰符：Maintenance Command:表修改MYSQL查询单表查询查询顺序多表查询和子查询MySQL视图DDL & DML修改密码：MySQL 系统参数设定：存储引擎存储引擎所使用的数据文件：各种存储引擎特性InnoDB:索引类型：MyISAM:ARCHIVE:CSV:BLACKHOLE:MEMORY:MRG_MYISAM:NDB:三方存储引擎：InnoDB与MyISAM比较存储引擎的选择需求MySQL用户管理：账号管理命令权限管理命令管理权限MySQL锁数据库访问权限MySQL查询缓存MySQL事务MySQL日志查询日志慢查询日志错误日志二进制日志：中继日志事务日志备份与恢复备份实例Xtrabackup: 支持对innodb 增量热备份的工具使用SELECT和LOAD语句进行备份和还原MySQL复制主从复制MySQL GTIDGTID参数设定GTID主从配置多源复制 Multi-Source Replication:MySQL Proxy安装mysql-proxy SysV脚本admin.lua文件补充范式
Notes:
对于Mysql而言，任何的字符是必须要加引号的，数值型一定不能加引号
BASIC Knowledge
数据类型以及属性修饰符：
数值型 tinyinit, smallint,mediumint,int,brgint dicimal float, double
字符型 char, varchar, tinytext, text, mediumtext, longtext binary, varbinary, tinyblob,blob,mediumblob,longblob
字符型常用的属性修饰符： 1. NULL:允许非空 2. NOT NULL：非空约束 3. DEFAULT 'string': 仅能用于char和varchar,不适用于text. 4. CHARACTER SET:字符集 使用SHOW VARIABLES LIKE '%char%' 使用SHOW CHARACTER SET 查看字符集 5. COLLATION: 排序规则 SHOW COLLATION 查看排序规则 note：Binary strings are ordered by each bytes values
数值型常用的属性修饰符：
1 . AUTO_INCREMENT：自动增长
1. 非空且唯一，至少是唯一键，要支持索引 2. 必须为整数，使用UNSIGNED修饰符 3. 必须是integer data type: INT 4. 若为主键则为非空，若指定为唯一键，则要指定INT为非空
2 . UNSIGNED: 无符号 3 . NULL/NOT NULL 4 . DEFAULT int_num
浮点型常用的属性修饰符：
- FLOAT(g,f):定义精度和小数点后保留位数
- DOUBLE(g,f):定义精度和小数点后保留为数
note: g=global,f=float
- NULL/NOT NULL
- UNSIGNED
BOOLEAN：
实际上就是类型tinyint(1)， 1：为真 0：为假
Datatime Data Types:
Date: 三个字节 #1000-01-01 to 9999-12-31 TIME: 三个字节 #-838:59:59 to 838:59:59 DATETIME: 八个字节 #1000-01-01 00:00:00 to 9999-12-31 23:59:59 YEAR(2): 00 to 99 #一个字节 YEAR(4): 1901 to 2155 #一个字节 TIMESTAMP: 四个字节 #1970-01-01 00:00:00 to 2038-01-18 22:14:07 修饰符： 1 . NULL/NOT NULL 2 . DEFAULT
ENUM和SET：枚举和集合 ENUM: 最多可以列举65535 strings SET: 最多支持64个字符，通常为单个字符。 note: SET可以从中选择多个，ENUM只能选择一个 SET通常存储索引 eg: a,b,c,d 1001则表示a,d
表空间： MyISAM表，每表有三个文件，都位于数据库目录中：
- tb_name.frm:表结构定义
- tb_name.MYD:数据文件 mydata
- tb_name.MYI:索引文件 my index
InnoDB表，有两种存储方式 默认方式： 每张表有一个独立文件和一个共享的文件 tb_name.frm:表结构的定义，位于数据库目录中 ibdata#:共享的表空间文件，默认位于数据目录(datadir指向目录)中 独立的表空间文件：每张表有一个表结构文件和一个特有的表空间文件 tb_name.frm: 表结构 tb_name.ibd: 表空间
show global variables like 'innodb%'; innodb_file_per_table | OFF #默认关闭
Maintenance Command:
MYSQL语句：数据库不指定则继承服务器的，表的不指定则继承数据库的，数据的不指定则继承表的 查看warning
show warning;
查看排序规则
SHOW COLLATION;
查看存储引擎
SHOW ENGINES;
创建数据库表文件etc 创建数据库
mysql
Syntax:
CREATE {DATABASE|SCHEMA}[IF NOT EXSITS] db_name [DEFAULT] [CHARACTER SET ''][DEFAULT][COLLATE='']
eg.
CREATE DATABASE IF NOT EXISTS mydb
直接查询出创建表时所使用的命令，之后可以直接使用指令添加表
show create table students;
| students | CREATE TABLE `students` (
`StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar(50) NOT NULL,
`Age` tinyint(3) unsigned NOT NULL,
`Gender` enum('F','M') NOT NULL,
`ClassID` tinyint(3) unsigned DEFAULT NULL,
`TeacherID` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`StuID`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8 |
删除数据库：
DROP {DATABASE|SCHEMA}[IF EXSITS] db_name
修改数据库：
ALTER {DATABASE|SCHEMA} db_name [DEFAULT] [CHARACTER SET=''] [COLLATE='']
数据库名称修改比较复杂
备份库中个表，删除原库，还原库
表创建方式1：
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
create table t1 (ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, Name CHAR(30));
Note: temporary表会在内存中创建 create_defination: 字段的定义、字段名、类型和类型修饰符 键、约束或索引 键都是索引，但是索引未必都是键
eg. CREATE TABLE t3 (Name VarChar(50) NOT NULL, Age TINYINT UNSIGNED NOT NULL,PRIMARY KEY(Name,Age));
table_option
ENGINE [=] engine_name
AUTO_INCREMENT [=] value #指定auto-increment 开始数字大小
[DEFAULT] CHARACTER SET [=] charset_name #设定默认名
[DEFAULT] COLLATE [=] collation_name #排序规则
COMMENT [=] 'string' #注释
DELAY_KEY_WRITE [=] {0 | 1} #频繁修改的表，可以提高性能。防止频繁写入的场景
INDEX DIRECTORY [=] #索引目录位置
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} #表格式
表创建方式2 根据替他表来复制数据，并不复制表结构
mysql
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
select_statement
表创建方式3 根据其他表来创建空表，复制表结构
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
表修改
ALTER TABLE tbl_name
[alter_specification [, alter_specification] ...]
[partition_options]
修改字段定义：
插入新字段
ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ]
eg.
alter table t5 add Age TINYINT UNSIGNED NOT NULL;
alter table t6 add Gender ENUM('M','F') NOT NULL DEFAULT 'M'
添加多个字段使用
ADD [COLUMN] (col_name column_definition,...) note:同CREATE表
删除字段：
DROP [COLUMN] col_name
修改字段：
修改字段名称
CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name] eg. alter table t6 change Name StuName CHAR(30) NOT NULL;
修改字段类型以及属性
MODIFY [COLUMN] col_name column_definition
[FIRST|AFTER col_name]
eg.
alter table t6 modify Gender ENUM('M','F') NOT NULL after Age;
修改约束及索引
查看索引
SHOW INDEXES FROM [Table]
添加索引
ADD {INDEX|KEY} [index_name]
[index_type] (index_col_name,...) [index_option] ...
eg.
alter table t6 add index (StuName)
删除索引
DROP {INDEX|KEY} index_name
eg.
alter table t6 drop index StuName;
创建约束：
ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) [index_option] ...
ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) [index_option] ...
启动/取消键：
DISABLE KEYS ENABLE KEYS
修改表名
RENAME [TO|AS] new_tb1_name
修改表选项：
table_option [[,] table_option] ... (see CREATE TABLE options) note:与create相同 eg. ALTER TABLE t6 engine MYISAM;
指定排序标准的字段
ORDER BY col_name [, col_name] ...
转换字符集以及排序规则
CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
Example：
创建表
create table Lab (ID smallint not null primary key, Host varchar(30) not null,IP varchar(40) not null, Password varchar(10) not null, Format varchar(50) not null);
调整表结构
alter table Lab add Username varchar(20) not null after Host;
添加表结构
insert table Lab (ID,Host,Username,IP,Password,Format)value(1,centos.example.com,root,192.168.48.130,nsadm,ssh root@192.168.48.130);
新增字段：
alter table users add Class tinyint not null auto_increment after Name;
将ID字段名称修改为TID:
alter table users change ID TID not null;
将Age字段放置最后：
alter table users modify ID smallint not null after Class;
MYSQL查询
单表查询
查询执行路径：
查询执行路径
客户端/服务器 SQL
| ——————>查询缓存
| ——————> 解析器
| —————>预处理器
| | |
|—————————————————>| |
| 查询优化器
| |
| |
|—————————————————————————>查询执行引擎
|
| API调用
|
存储引擎
|
数据
查询顺序
- SELECT[DISTINCT]
- FROM
- WHERE
- GROUP BY
- HAVING
- UNION
- ORDER BY
MySQL的查询操作：
单表查询：简单查询 多表查询：连接查询 联合查询 选择和投影： 投影：挑选要显示的字段 选择：挑选要显示的行
SELECT values_to_display
FROM table_name
WHERE expression
GROUP BY how_to_group
Having expression
ORDER BY how_to_sort
LIMIT row_count
投影法： SELECT 字段1，字段2，...FROM tb_name; SELECT * FROM tb_name; 选择： SELECT 字段1，... FROM tb_name WHERE 子句; 布尔条件表达式
布尔条件表达式操作符
= 等值比较
<> != 不等值比较
<=> 空值安全的比较，与空值比较
<;<=;>;>=
BETWEEN AND 判断指定字段的范围是否在制定的范围
IN 是否在集合中
IS NULL 是否为空
IS NOT NULL 是否不为空
LIKE 通配比较
%(任意长度的任意字符)
_(任意单个字符)
note: Like性能较差
REGEXP aka RLIKE 正则表达式匹配
eg.
CREATE TABLE students(SID INT UNSIGNED AUTO_INCREMENT NOT NULL UNIQUE KEY,Name CHAR(30) NOT NULL, Age TINYINT UNSIGNED NOT NULL, Gender ENUM('F','M') NOT NULL DEFAULT 'M', Tutor CHAR(30));
INSERT INTO students VALUES (1,"Guo Jing", 27,"M","Ke Zhen E");
INSERT INTO students VALUES (3,"Guo Polu",21,'M',"Jia Bao Yu";
INSERT INTO students VALUES (3,"Guo Polu",21,'M',"Jia Bao Yu");
INSERT INTO students VALUES (4,'Xue Baochai',19,'F','Jia Baoyu');
INSERT INTO students VALUES (5,'Xia yu he',37,'F','Bababa');
INSERT INTO students VALUES (6,'Wu Yong',51,'M','Lin Dai Yu');
选出年龄在25到40之间的学生：
select * from students where age between 25 and 40;
查看符合列表中条件的项
select Name,Age From students where age in (25,26,27,28,29)
使用通配或者正则表达式匹配比较
select * from students where Name like 'X%'; select * from students where Name rlike 'X.*';
查看是否为空
select name,tutor From students where tutor is NULL;
组合条件测试：
NOT aka! 非运算符 AND aka && 与运算 OR aka || 或运算 XOR 不包含比较
select * from students where Age > 25 and Gender = 'M';
按序输出：
ORDER BY [字段] ASC: 升序 DESC: 降序
select * from students where Age > 25 and Gender = 'M' order by Name;
聚合函数
SUM(): 求和
select SUM(Age) from students;
AVG(): 求平均值
select AVG(Age) from students;
MAX(): 求最大值
select MAX(Age) from students;
MIN(): 求最小值
select MIN(Age) from students;
COUNT(): 统计个数
select COUNT(Age) from students where Age > 25;
分组：
通过一定的规则将一个数据集划分为若干个小的区域，然后针对若干个小区域进行数据处理 一般配合集合函数来运用 GROUP by how_to_group
select SUM(Age),Gender from students GROUP BY Gender;
对分组的条件过滤： Having 只返回有用的行： LIMIT 一个数为显示的行数 两个数字为偏移第一个数字行，显示第二个数字
Select语句的执行流程
from clause -> where clause -> group by -> having clause -> order by -> select -> limit
Select语句的缓存选项：
- DISTINCT 重复的只显示一次
- SQL_CACHE 缓存查询结果
- SQL_NO_CACHE 不缓存查询结果
多表查询和子查询
联结查询：事先将两张或者多张表join, 根据join的结果进行查询 cross join: 交叉联结，容易出现重复行 自然联结：等值联结，把相同的字段进行等值联结 外联结： 左外联结，只保留出现在左外联结元之前(左边)的关系中的元组(以左表为基准)
left_tb LEFT JOIN right_tb ON [condition]
eg. select s.Name,c.Class from students AS s LEFT JOIN classes AS c ON s.ClassID = c.ClassID
右外联结：只保留出现在右外联结元算之后(右边)的关系中的元组(以右表为准)
left_tb RIGHT JOIN right_tb ON [condition]
还有全外联结 别名： AS 表别名和字段别名
子查询：在查询中嵌套的额查询 用于WHERE中的子查询 1 . 用于比较表达式中的子查询，子查询的返回值只能有一个 2 . 用于EXISTS中的子查询，判断存在与否 3 . 用于IN中的子查询 判断存在于指定列表中 用于FROM中子查询：
select alias.col,... FROM (SELECT clause) AS alias WHERE condition
查询案例
1. 显示前5位同学姓名、课程以及成绩： 使用自然联结中的等值联结：
select Name,Course,Score From students AS s, courses AS c,coc,scores AS ss where s.ClassID = coc.ClassID AND coc.CourseID = c.CourseID AND s.StuID <=5 AND s.StuID=ss.StuID AND ss.CourseID=coc.CourseID;
2. 求前8位同学每位同学自己两门课的平均成绩，并按降序排列，使用ORDER BY 进行降序排列
select Name,AVG(Score) From students AS s, coc, courses as c, scores AS ss where s.ClassID=coc.ClassID AND coc.CourseID = c.CourseID AND s.StuID <=8 AND s.StuID=ss.StuID AND coc.CourseID=ss.CourseID GROUP by Name ORDER BY AVG(Score) DESC;
3. 显示每门课程课程名以及学习了这门课程的同学个数
select Course, count(Name) from courses as c, students as s, coc where coc.CourseID=c.CourseID AND coc.ClassID=s.ClassID Group By Course;
4. 查找年龄大于平均年龄的所有同学姓名 定义AVG(Age)的由来，这种嵌套查询称为子查询
select Name From students Where Age > (select AVG(Age) From students);
5 . 查找没有开课班级的同学姓名添加新学生和班级
INSERT INTO students VALUES(26,'TOM',33,'F',8,11),(27,'Jerry',26,'M',9,2); insert into classes values(9,'Liangshan',22);
此处使用左联结，是的没有课程的班级置NULL
select Name From students where ClassID IN (select classes.ClassID from classes LEFT JOIN coc ON classes.ClassID=coc.ClassID where coc.ClassID is NULL);
MySQL联合查询：
使用UNION进行联合：
SELECT clause UNION SELECT clause eg. SELECT Name,Age FROM teachers UNION SELECT Name,Age FROM students;
使用EXPLAIN关键字来查询SQL语句的执行过程
explain select Name,Age FROM students WHERE Age > 25;
查看表的索引
SHOW INDEXES FROM students;
添加表索引
ALTER TABLE students ADD INDEX(Age);
MySQL视图
存储下来的SELECT语句 查看创建视图的帮助： HELP CREATE VIEW
eg. CREATE VIEW stu AS select StuID,Name,Age,Gender FROM students;
查看表的状态
SHOW TABLE status;
查看视图创建信息
SHOW CREATE VIEW stu;
DDL & DML
DDL: Data Defination
- DATABASE
- TABLE
- VIEW
DML: Data Manipulation
- SELECT
- INSERT/REPLACE
- UPDATE
- DELETE
INSERT
第一种：
INSERT INTO tb_name [(col1,col2,...)] {value|values} (val1,val2,...)[,(val21,val22,...)]
第二种：
INSERT INTO tb_name SET col1=val1 col2=val2,...;
第三种：
INSERT INTO tb_name SELECT clause;
insert into t1 (Name) VALUES('tom'),('jerry');
查看最后一次插入的auto_increment 值
SELECT LAST_INSERT_ID();
重置表自动增长参数
TRUNCATE tb_name
REPLACE: 语法格式与INSERT相同，在必要时可以完成替换操作。除了在新插入的数据和表中主键定义或唯一索引定义的数据相同时，会替换老的数据，老的行
UPDATE:
Syntax:
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
eg.
update students set Name='Hugh' where Name='Jerry';
Note: UPDATE通常情况下，必须要使用WHERE子句，或者使用LIMIT限制要修改的行数
--safe-update 防止update错内容
DELETE:
Syntax:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
Note: DELETE同UPDATE,删除时要做限定，如果不限定则清空整个表
MySQL的功能从逻辑上面试在二层实现的，
Query Cache;只有select语句会被缓存下来 Parser; 解释器，分析器 Optimizer; 优化器 插件式存储引擎是在三层实现
修改密码：
UPDATE USER SET PASSWORD= PASSWORD('nsadm') where user='root';
FLUSH privileges;
mysqladmin -uUserName -hHost password 'new_password' -p
mysqladmin -uUserName -hHost -p flush-privileges
MySQL 系统参数设定：
MYSQL SQL_MODE:SQL模式
- TRADITIONAL
- STRICT_TRANS_TABLES
- STRICT_ALL_TABLES
note: 默认使用空模式 SHOW GLOBAL VARIABLES LIKE 'sql_mode'; 设定服务器变量的值：仅用于支持动态的变量 支持修改的服务器变量： 动态变量：可以在MySQL运行时修改 静态变量：于配置文件中配置，并于重启后方能生效 服务器变量从生效范围需求，分两类： 全局变量：服务器级别，修改之后仅对新建立的会话有效，对当前会话无效 会话变量：会话级别，仅对当前会话有效 会话建立时，从全局继承各变量；
查看服务器变量
SHOW (GLOBAL|SESSION) VARIABLES [LIKE '']; Note:默认为SESSION
SELECT @@(global|session).sql_mode;
SELECT @@global.sql_mode;
SELECT * from information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME='sql_mode; 保存于information_schema的表中
使用SET关键字修改变量
前提：修改全局变量，默认仅管理员有权限
SET (GLOBAL|SESSION) VARIABLE_NAME='VALUE';
Note:缺省为session变量
eg.
SET SESSION sql_mode='STRICT_ALL_TABL
Note:不论SESSION还是GLOBAL的VARIABLES，在mysql服务重启之后都会失效。那么久意味着，如果想要其持久生效。则需要修改mysql的配置文件相应段落中[mysqld]
关于MySQL中字符的大小写：
- SQL关键字以及函数名不区分字符大小写；
- 数据库表以及视图名称的大小写，取决于底层os以及fs
- 存储过程，存储函数以及事件调度器，不区分大小写
- 触发器区分大小写
- 别名Alias区分大小写
- 对于字段中的数据，如果字段类型为binary类的类型，则区分大小写，非binary不区分大小写
存储引擎
存储引擎是表级别的， 所以存储引擎也称为“表类型”
查看存储引擎
show engines
修改表的存储引擎
ALTER TABLE classes ENGINE 'InnoDB';
修改表的存储引擎，会带来大量的I/O,生产环境中不建议使用
数据库导入
mysql -uroot -p < mydb.sql
查看表具体信息
show table status like "classes" \G;
Name: classes
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 8
Avg_row_length: 2048
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 8388608
Auto_increment: 9
Create_time: 2016-02-18 17:02:07
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Name:表名
Engine:存储引擎
Version:版本
Row_format:行格式 {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
每种格式存储开销各不同
Rows:表中的行数
MYISAM表是精确的
InnoDB表的行数是一个估计值
Avg_row_length:平均每行所包含的字节数
Data_length:表中数据总体大小，单位是字节 Rows * Avg_row_length
Max_data_length:表的最大占用长度，单位为字节
Index_length:索引大小
Data_free:
对MyISAM表，标示已经分配但尚未使用的空间，其中包含此前删除行之后腾出来的空间
Auto_increment: 下一个自动增长的值
Create_time: 表的创建时间
Update_time: 表数据的最后修改时间
Check_time: 使用CHECK TABLE或myisamchk最近一次检测表的时间
Collation: 排序规则
Checksum:如果启用则为表的校验和
Create_options:创建表时指定使用的其他选项：
Comment:表的注释
存储引擎所使用的数据文件：
MyISAM: 每个表都会在数据库目录下存储三个文件： tb_name.frm 表结构 tb_name.MYD 数据 tb_name.MYI 索引 InnoDB: 两种格式：
1 . innodb_file_per_table = OFF, 即使用共享表空间
每张表有一个独有的表格式定义：tb_name.frm 还有一个默认位于数据木下共享的表空间文件：ibdata# 2 . innodb_file_per_table = on, 即使用独立表空间 每个表在数据库目录下存储两个文件 tb_name.frm #表空间 tb_name.ibd #数据文件 表空间: table space, 由InnoDB管理的特有格式数据文件，内部可以同时存储数据和索引。支持聚簇索引
查看有关ENGINE的变量
SHOW VARIABLES LIKE '%engine%';
+---------------------------+--------+ | Variable_name | Value | +---------------------------+--------+ | default_storage_engine | InnoDB | | engine_condition_pushdown | ON | | storage_engine | InnoDB | +---------------------------+--------+
默认存储引擎为InnoDB，可以通过修改default_storage _engine服务变量实现
各种存储引擎特性
InnoDB:
支持事务：事务日志 ib_logfile0和ib_logfile1顺序存储的log file可以在mydata的目录下找到 他们的大小是固定的，并且是磁盘上一段连续的存储空间。
支持外键
MVCC
聚簇索引： 聚簇索引之外的其他索引，通常称为辅助索引。将索引和数据排列到一起。只要能找到索引就能连续的查询到行。 聚簇索引只能有一个； 辅助索引可以有多个；辅助索引可以查找到聚簇索引，然后通过聚簇索引可以查找到数据存放位置。 聚簇索引通常使用主键来实现，因为主键不允许被重复
支持自适应的hash索引
支持热备份
行级锁：间隙锁
索引类型：
- 聚簇索引
- 辅助索引
- B树索引
- R树索引
- hash索引
MyISAM:
适用于多读少写的场景。较小的表，能够容忍崩溃后数据难以恢复的情况 全文索引** 压缩**：通常只能用来做数据查询，不能进行修改。提高性能，通常使用来做数据仓库
空间索引
表级锁：
延迟更新索引键：Delay Key Right 不支持事物，外键和行级锁 崩溃后难以修复
ARCHIVE:
仅支持INSERT和SELECT，支持很好的压缩功能。适用于存储纯文本的日志信息，或其他按时间序列实现的数据采集类的应用。 不支持事物，不能很好的支持索引
CSV:
将数据存储为CSV格式，(浮点数存储于文本会丢失精度). 不支持索引，仅仅适用于数据交换的场景。 数据以都好分割
BLACKHOLE:
没有存储机制，任何发往此引擎的数据都会被丢弃 在级联复制时有用，作为中转服务器。可以记录二进制日志
MEMORY:
数据保存至内存中，即内存表。 常用于保存数据的中间数据，做临时表。不是保存最终数据的 支持hash索引，使用表级锁，不支持BLOB和TEXT数据类型
MRG_MYISAM:
是MYISAM的变种，能够将多个MyISAM表合并成一个虚表
NDB:
是MySQL CLUSTER中专用的存储引擎，实现分布式，高可用的MySQL集群。但是并没有人用。。
三方存储引擎：
OLTP类: 在线事物处理 XtraDB: 增强的InnoDB，由Percona提供； 编译安装MySQL时，下载XtraDB的源码替换MySQL存储引擎中的InnoDB源码 PBXT: 对固态磁盘有优化，支持事务，支持MVCC。MariaDB自带此引擎 TokuDB: 使用Fractal Trees索引，性能很好。适用于存储大数据，拥有很好的压缩比。同样被引入到新版本的mariaDB.
开源社区存储引擎
Aria: 前身是Maria,可以理解为增强版的MyISAM存储引擎(支持崩溃后安全恢复，支持数据缓存) Groona: 全文索引引擎，Mroonga是基于Groona的二次开发 OQGraph: 由Open Query研发，支持图式结构的存储引擎 SphinxSE: 为Sphinx全文搜索服务器提供了SQL接口 Spider: 能数据切分成不同分片，比较搞笑透明地实现了分片(shared),并支持在分片上支持进行查询
列式存储引擎
按列存储，对按列查询的场景非常有效。Web应用中，常用的恰巧就是列式查询。
- Infobright: 目前较有名的列式引擎，适用于海量数据存储场景，如PB级别，专为数据分析和数据仓库设计
- InfiniDB
- MonetDB
- LucidDB
InnoDB与MyISAM比较
通常来说InnoDB在不断完善后，各种性能都比较优秀。建议使用InnoDB，在多读少写的场景中尽量使用Read Commited的隔离级别。
存储引擎的选择需求
- 是否需要支持事务
- 备份的类型支持；热备份，冷备份
- 崩溃后的数据恢复
- 持有的特性
MySQL用户管理：
用户账号：username@hostname, password
账号管理命令
创建用户：
CREATE USER username@hostname
[
IDENTIFIED BY [PASSWORD] 'password'
]
eg.仅允许用户从192.168.48.100至192.168.48.199的用户登录。使用通配
testuser@'192.168.48.1__'
修改用户名:
RENAME USER old_user TO new_user
创建用户testuser并将其密码设置为testpass
create user testuser@'192.168.%.1__' IDENTIFIED BY 'testpass'; FLUSH PRIVILEGES;
修改用户密码
SET PASSWORD FOR 'bob'@'%.example.org' = PASSWORD('cleartext password')
/usr/bin/mysqladmin -u root password 'nsadm'
DROP USER
RENAME USER
SET PASSWORD
权限管理命令
查看用户能够使用的权限
SHOW GRANTS FOR username@'hostname';
忘记MySQL密码
mysql-safe --skip-grant-tables --skip-networking --datadir=''
MySQL的权限类别：
- 库级别
- 表级别
- 字段级别
- 管理类
管理权限
CREATE TEMPORARY TABLES CREATE USER FILE #读写文件的权限 LOCK TABLES
MySQL锁
执行操作时加的锁模式 读锁：也叫共享锁 写锁：独占锁，排它锁 锁粒度： 表锁：table lock 锁定了整张表 行锁：row lock 锁定了需要的行 粒度越小，开销越大，但是并发性越好； 粒度越大，开销越小，但是并发性越差； 锁的实现位置： MySQL锁：可以使用显式锁 存储引擎锁：自动进行的(隐式锁) 显示锁： LOCK TABLES UNLOCK TABLES 手动施加锁，比如在备份时使用
LOCK TABLES
tbl_name [[AS] alias] lock_type
[, tbl_name [[AS] alias] lock_type] ...
lock_type:
READ [LOCAL]
| [LOW_PRIORITY] WRITE
eg.
lock tables classes READ;
释放所有锁
unlock tables;
InnoDB存储引擎也支持另外一种显式锁(行级锁)： 只锁定部分行，只是在一个语句中加锁，如果语句执行完毕，则锁会自动释放
SELECT ... LOCK IN SHARE MODE; SELECT ... FOR UPDATE;
PROCESS 查看各与用户相关的线程信息 SHOW PROCESSLIST RELOAD #FLUSH和RESET权限 REPLICATION CLIENT REPLICATION SLAVE #复制表的权限 SHOW DATABASES SHUTDOWN SUPER
数据库访问权限
库级别和表级别：
- ALTER
- ALTER ROUTINE
- CREATE
- CREATE ROUTINE
- CREATE VIEW
- DROP
- EXECUTE
- GRANT OPTION
- INDEX
- SHOW VIEW
数据操作类的权限：
- SELECT
- INSERT
- UPDATE
- DELETE
与字段相关的权限：
- SELECT(col1,...)
- UPDATE(col1,...)
- INSERT(col1,...)
所有权限：
- ALL
权限授予
GRANT priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [TABLE|FUNCTION|PROCEDURE] priv_level
TO username@hostname [IDENTIFIED BY 'password'],[username@hostname [],...]
[REQUIRE SSL]
[WITH with_option ...]
priv_level:
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
with_option:
GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
eg.
GRANT select,update,insert,delete on testdb.* to 'testuser1'@'192.168.%.%' IDENTIFIED BY 'testpass';
收回权限
REVOKE
eg.
revoke create on testdb.* from 'testuser1'@'192.168.%.%';
去权限也可以用DROP
DROP USER 'testuser'@'192.168.%.%'
与用户授权先关的表：
- db:库级别权限
- host:主机级别，已经废弃
- tables_priv: 表级别权限
- columns_priv: 列级别权限
- procs_priv: 存储过程和存储函数相关的权限
- proxies_priv: 代理用户权限
若果用户因为登录错误次数过多导致账号被锁，则可以使用flush hosts 来刷新主机缓存
MySQL查询缓存
用于保存MySQL查询语句返回的完整结果。被命中时，会立刻返回结果。省去了解析，优化和执行等阶段
查询缓存未必带来的都是好事，在硬件变得强大之后，多颗CPU之间会争用缓存
如何检查缓存?
MySQL保存结果于缓存中，把SELECT语句本身做hash计算，计算的结果作为key，查询结果做value 什么样的语句会被缓存？ 查询语句中有一些不确定数据时，不会缓存。例如NOW(), CURRENT_TIME(), 一般来说，如果查询中包含用户自定义函数、存储函数、用户自定义变量、临时表、mysql库中的系统表，或者任何包含权限的表，一般都不会缓存 缓存会带来额外开销：
- 每个查询都会先检查是否命中：
- 查询结果要先缓存：额外操作
- 维护缓存空间本身也会消耗额外的资源
查询缓存变量
SHOW GLOBAL VARIABLES LIKE 'query_cache%';
+------------------------------+----------+ | Variable_name | Value | +------------------------------+----------+ | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 16777216 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+----------+
query_cache_type: 查询缓存类型，是否开启缓存功能，开启方式有三种
- ON 启用缓存
- OFF 禁用缓存
- DEMAND 按需缓存 SELECT SQL_CACHE 的语句才会缓存
query_cache_size: 缓存使用的总空间，单位是字节，大小必须是1024的整数倍。MySQL启动时，会一次分配并立即初始化指定大小的内存空间。如果修改此大小会清空命中缓存并重新初始化。
query_cache_min_res_unit: 存储缓存的最小内存块。(query_cache_size-Qcache_free_memory)/Qcache_queries_in_cache 得到平均值来参考，接近理想的值
query_cache_limit: 单个缓存对象的最大值，超出时则不予缓存。手动使用SQL_NO_CACHE可以人为地避免尝试缓存返回结果超出子参数限定值的语句。
query_cache_wlock_invalidate: 如果某个表被其他用户连接锁住，是否仍然从缓存中返回结果。OFF表示返回
判断命中率
次数命中率和字节命中率 查看判断命令命中率
SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 16755376 | | Qcache_hits | 3 | | Qcache_inserts | 3 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 4 | | Qcache_queries_in_cache | 3 | | Qcache_total_blocks | 9 | +-------------------------+----------+
碎片整理
FLUSH QUERY_CACHE
清空缓存
RESET QUERY_CACHE
查看缓存命中率
SHOW GLOBAL STATUS WHERE Variable_name='Qcache_hits' OR Variable_name='Com_sele ct';
计算次数命中率，获取以上参数值，计算方法Qcache_hits/(Com_select + Qcache_hits) 字节命中率很难作为估算，所以计算次数命中率只能作为参考。 命中和写入的比率： Qcache_hits/Qcache_inserts的值，总比值能大于3：1，则说明缓存也是有效的。能达到10：1为比较理想的情况
缓存优化使用思路：
- 批量写入而非多次单个写入
- 保存空间不宜过大，因为大量缓存同时失效时，会导致服务器假死
- 必要时，使用SQL_CACHE和SQL_NO_CACHE手动控制缓存
- 写密集型的应用场景来说，禁用缓存可以提高性能
MySQL事务
事务：Transaction 事务就是一组原子性的查询语句：也即将多个查询党组偶一个独立的工作单元 ACID测试：能满足ACID测试就表示其支持事务、或兼容事务
- A: Atomicity 原子性，一个事务必须是不可分割，要么都执行，要么都不执行
- C: Consistency 一致性，数据库总是一个一致性状态转到另一个一致性状态
- I: Isolation 隔离性， 一个事务所做的修改在提交之前对其他人是不可见的
- D: Durability 持久性， 一旦事务得到提交，其所做的修改就会永久有效
隔离级别：
- READ UNCOMMITED: 读未提交，可以读取别人未提交的数据，隔离性最差，会产生dirty read。两次读取的内容不一样，或者会产生幻读，读取内容与存储内容不一致
- READ COMMITED: 读提交，只有别人提交的额数据才能被看到。市面上大多数的隔离级别是读提交
- REPEATABLE READ: 可重读，解决脏读的问题。所有事物在提交前，所读到的结果都是一样。如果别人也在同时修改此数据，可能导致最终修改的数据结果与自身看到的不同。产生幻读
- SERIALIZABLE: 串行化。事物与事物之间严格隔离，必须串行处理，避免幻读，性能极低。
与事物相关的常用命令：
START TRANSACTION
开启事务
COMMIT:事物提交 ROLLBACK:事物回滚
SAVEPOINT 记录点：
SAVEPOINT identifier ROLLBACK [WORK] TO [SAVEPOINT] identifier RELEASE SAVEPOINT identifier
如果没有显示启动事物，每个语句都会当做一个独立的事物。其执行完全会被自动提交
show global variables like '%commit%'；
select @@session.autocommit;
set global autocommmit = 0;
查看隔离级别：
SHOW GLOBAL VARIABLES LIKE '%iso%';
SELECT @@global.tx_isolation;
SET GLOBAL tx_isolation='READ-UNCOMMITED'
READ-COMMITED
REPEATABLE-READ
READ UNCOMMITED 不可重读，脏读，幻读 READ COMMIT: 不可重读，幻读 REPEATABLE READ: 可重读，幻读 SERIALIZABLE: 阻塞 建议：对事物要求不是特别严格的场景下，可以使用读提交 MVCC: 多版本并发控制 每个事物启动时，InnoDB为每个启动的事物提供一个当下时刻的快照 为实现此功能，InnoDB会为每个表提供两隐藏字段，一个用于保存创建时间，一个用于创建失效时间。 里面存储的是系统版本号：(system version number)
MySQL日志
查询日志：高开销，默认关闭。 慢查询日志：查询执行时长超过指定时长的查询，即为慢查询 错误日志：启动，关闭，复制线程时相关信息都会记录进日志中 二进制日志：记录引起数据改变的所有操作的日志。MySQL的复制功能就依赖此功能 中继日志：将主服务器二进制日志文件保存到从服务器中所记录的日志文件 事务日志：
- 随机I/O转换为顺序I/O
- ACID:持久性
PCI-E的固态硬盘，几乎可以体会接近内存的性能，带宽接近前端总线
- 日志文件组，至少应该有两个日志文件，注意尽可能使用小事务以提升事务引擎的性能
查看innodb的参数
SHOW GLOBAL VARIABLES LIKE 'innodb%';
查询日志
SHOW GLOBAL VARIABLES LIKE '%log%'
log = {ON|OFF}：是否记录所有语句的日志信息于一般查询日志文件(general_log) log_output={TABLE|FILE|NONE} 记录于表中，文件中以及不记录 TABLE和FILE可以同时出现，用逗号分隔即可 general_log：是否启用查询日志，与log参数冲突 general_log_file：定义一般查询日志保存的文件
慢查询日志
long_query_time: 慢查询的时长，查过次时间的时长被称为慢查询 slow_query_log='OFF|ON' 存放位置取决于log_output 参数 slow_query_log_file: 慢查询日志文件 log_slow_rate_limit=1
错误日志
服务器启动和关闭过程中的信息 服务器运行过程中的错误信息 时间调度器运行一个时间时产生的信息 在复制架构中的从服务器上启动从吴福气线程时产生的信息 log_error=‘/path/error_log_file' log_warnings='{1|0}' 是否记录警告信息于日志信息中
二进制日志：
"修改相关的信息"，通常也叫作复制日志 时间点恢复 使用mysqlbinlog工具来查看二进制日志：
- position: 位置偏移，空间记录法
- time: 时间记录法
查看当前正在被使用的二进制文件
SHOW MASTER STATUS;
使用写缓冲区域完成并行操作 日志滚动：
- Size
- Time
手动滚动日志，重启mysqld服务也会滚动日志
FLUSH LOGS;
查看二进制日志
SHOW BINARY LOGS;
手动清除日志文件
Syntax:
PURGE { BINARY | MASTER } LOGS
{ TO 'log_name' | BEFORE datetime_expr }
查看二进制日志文件内容
Syntax: SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW BINLOG EVENTS in 'mysql-bin.000002' FROM 892; mysqlbinlog --start-position=892 mysql-bin.000002
server-id: 服务器的身份标示
MySQL记录二进制日志的格式：
- 基于语句：statement
- 基于行：row
- 混合模式：mixed
日志文件内容格式：
- 事件发生的日期和时间
- 服务器的ID
- 事件的结束位置
- 事件的类型
- 原服务器生成此事件的线程ID号
- 语句的时间戳和写入二进制日志文件的时间差
- 错误代码
- 事件内容
- 事件结束位置，相当于下个事件的开始位置
查看进程的ID号
SHOW PROCESSLIST;
于二进制日志文件相关的服务器变量：
log_bin={ON|OFF},还可以是一个文件路径 log_bin_trust_function_creators sql_log_bin={ON|OFF} sync_binlog：同步时间间隔，从缓冲区同步到日志中，默认为0，说明不靠时间来控制。 binlog_format={statement|row|mixed} note: 切勿将二进制文件与数据文件放于同一磁盘设备 max_binlog_cache_size =: 二进制日志缓存空间大小，仅用于缓冲事务类的语句，单位是字节 max_binlog_stmt_cache_size: 非事务类和事物类共同使用的缓冲空间大小 max_binlog_size: 二进制日志文件的滚动上限，单位是字节 临时关闭二进制日志文件，例如测试需求：
SET SESSION sql_log_bin=0;
中继日志
从服务器从主服务器中读取二进制日志执行时所记录的日志 relay_log_purge={ON|OFF} 自动清理不再需要的中继日志 relay_log_info_file: 中继日志的文件
事务日志
备份与恢复
备份目的
- 灾难恢复
- 审计
- 测试
备份类型： 通过快照备份的时间点可以保证一致 根据备份数据服务器是否在线
- 冷备份：cold backup, down机时间
- 温备份：warm backup
- 热备份：hot backup
根据备份的数据集：
- 完全备份：full
- 部分备份：partial backup
根据备份时的接口(直接备份数据文件还是通过mysql服务器导出数据):
-
物理备份：直接复制(归档)数据文件的备份方式
物理备份跨平台能力没有逻辑备份好
-
逻辑备份：把数据从库中提取出来，保存为文本文件
mysqldump
根据备份时备份整个数据还是仅备份变化的数据：
- 完全备份：full backup
- 增量备份：incremental backup
- 差异备份：differential backup
备份策略：
- 选择备份方式
- 选择备份时间
- 考虑到恢复成本
- 备份成本：
- 锁时间
- 备份时长
- 备份负载
备份对象：
- 数据
- 配置文件
- 代码，存储过程，存储函数，触发器
- 特殊场景中跟复制相关的配置
- 二进制日志文件
备份工具： mysqldump: 逻辑备份工具，能够对innodb的热备，但是代价比较高，对MyISAM只能做到温备, 对Aria也只能做温备，备份和恢复过程比较慢。单线程工具，只能启用一颗CPU进行单线程的备份，性能很差。 mysqldumper: 多线程的mysqldump 此两种方式很难完成增量备份 lvm-snapshot: 接近于热备份的工具。因为要先请求全局锁，然后创建快照，并在快照创建完成之后释放全局锁。 使用cp, tar等工具进行物理备份。备份和恢复速度较快 很难实现增量备份，并且请求全局锁要等待一段时间，在繁忙的服务器上尤其如此。 SELECT clause INTO OUTFILE '/path/to/somefile' 使用SELECT和LOAD来实现部分备份，不备份表格式，只备份数据。也是一种逻辑备份工具，速度略快于mysqldump LOAD DATA INFILE '/path/from/somefile' Innobase: 商业备份工具， innodackup。 物理备份，速度快 Xtrabackup: 由Percona提供的开源备份工具 InnoDB热备，增量备份； MyISAM温备，不支持增量； mysqlhotcopy: 几乎冷备
备份实例
mysqldump: 备份前要加锁
mysqldump [options] [db_name [tb1_name ...]]
备份单个库：mydqldump [options] db_name 恢复时，如果目标库不存在，需要事先手动创建
mysqldump --all-databases -uroot -p> /tmp/all.sql
--all-databases: 备份所有库
--databases db1,db2: 备份指定的多个库
--lock-all-tables: 在备份开始前自动请求锁定所有表(建议使用)，自动请求加读锁。对MyISAM和InnoDB进行温备
--single-transaction: 能够对InnoDB存储引擎实现热备
备份代码常用三选项
--events: 备份事件调度器代码
--routines:备份存储过程和存储函数
--trigger:备份触发器
备份时滚动日志：
--flush-logs:施加锁后，滚动日志
复制时的同步位置标记：
--master-data= [0|1|2]
0:不记录
1:记录为CHANGE MASTER语句
2:记录为注释的CHANGE MASTER语句
mysqldump -uroot -p --all-databases --flush-logs --master-data=2 --lock-all-tables> all.sql
使用mysqldump备份： 请求锁： --lock-all-tables 或使用 --single--transaction进行innodb热备份 滚动日志: --flush-logs 选定要备份的库： --databases 记录二进制日志文件及位置，--master-data= FLUSH table时会将所有表中位于缓冲区的内容全部同步到磁盘中去。 一般使用FLUSH附加锁的方式
FLUSH TABLES WITH READ TABLE
还原日志的案例
首先热备份 mysqldump -uroot -p --databases mydb --single-transaction --flush-logs --master-data=2 > /tmp/mydb.sql 在备份后对数据库进行修改，由于master-data=2 可以记录当前的二进制log的position位置 mysqlbinlog --start-position=107 mysql-bin.000028 将修改内容也进行备份 mysqlbinlog --start-position=107 --stop-position=272 mysql-bin.000028 > /tmp/mydb1.sql mysql -uroot -p < mydb.sql mysql -uroot -p < mydb1.sql
也可以是用source的方法还原数据
set session sql_log_bin=0; source /tmp/mydb.sql set session sql_log_bin=1; show master status;
恢复： 建议,关闭二进制日志，关闭其他用户连接 备份策略：基于mysqldump 备份：mysqldump+二进制日志文件(增量) 周日做一次完全备份，备份的同时滚动日志 mysqladmin flush-logs 周一至周六，备份二进制日志即可 恢复：完全备份+各二进制文件中至此此刻的事件 对于MySQL配置文件，以及与MySQL相关的配置文件在每次修改后都应该直接做备份
基于快照备份
- 事务日志跟数据文件必须在同一个卷上，否则快照无法保证二者的时间点一致
- 创建快照卷之前，要请求MySQL的全局锁，在快照创建完成之后释放锁
- 请求全局锁完成之后，做一次日志滚动。做二进制文件及位置标记(Master data);
步骤：
1、请求全局锁，并滚动日志
FLUSH TABLES WITH READ LOCK; FLUSH LOGS;
2、做二进制日志文件及位置标记(手动进行)
mysql -e 'show master status' > /path/to/somefile
3、创建快照卷
lvcreate -L -s -n 'mysnap' -p r/path/to/some_l
4、释放全局锁
UNLOCK TABLES;
5、挂载快照卷并备份，快照卷只是作为一个访问通路来使用
cp -a 的方式
6、备份完成后，删除快照卷
umount /mnt; lvremove /dev/mapper/mysql_data-mysql--snap
恢复：
- 二进制日志保存好：提取备份之后的所有事件至某sql脚本中
- 还原数据，修改权限及属主属组等，并启动mysql
- 做即时点还原
Xtrabackup: 支持对innodb 增量热备份的工具
innobackupex: 需要MySQL服务处于运行状态，并且需要通过用户验证来进行查询验证
创建最小授权的用户
CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 'bkpass'; REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'bkpuser'; GRANT RELOAD,LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkuser'@'localhost'; FLUSH PRIVILEGES;
使用Xtrabackup的前提是要将innodb的表分离成数据与表结构分离的状态
innodb_file_per_table=ON
LSN: log serial number
执行backup
innobackupex --user=root --password=nsadm /mybackups/
还原数据：
一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。 innobakupex命令的--apply-log选项可用于实现上述功能。如下面的命令：
innobackupex --apply-log /path/to/BACKUP-DIR
恢复操作
innobackupex --copy-back /path/to/BACKUP-DIR
增量备份：
innobackupex --incremental /backup --incremental-basedir=BASEDIR --incremental-basedir 相对增量目录
此后如果又做修改后并没有做相应的增量备份，则查看最后一次增量备份中的xtrabackup_binlog_info 文件 然后进行还原 还原准备(按序执行)及执行：
innobackupex --apply-log --redo-only BASE-DIR innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1 innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-2
将备份文件归档压缩：
innobackupex --stream=tar /backup | gzip > /backup/`date +%F_%H-%M-%S`.tar.gz
note:
- 将数据和备份放在不同的磁盘设备上，异机或异地备份存储较为理想
- 备份的数据应该周期性地进行还原测试
- 每次灾难恢复后，都应该立即做一次完全备份
- 针对不同规模或级别的数据量，应定制好备份策略
- 二进制日志应该跟数据文件在不同磁盘上，并周期性地备份好二进制日志文件
从备份中恢复应该遵循步骤：
- 停止MySQL服务器，mysqldump就必须是服务器在线时可以使用
- 记录服务器的配置和文件权限
- 将数据从备份到MySQL数据目录，其执行方式依赖于工具
- 改变配置和文件权限
- 以限制访问模式重启服务器，mysqld的 --skip-network选项可以跳过网络功能。编辑my.cnf 配置文件，添加如下项：
skip-networking
socket=/tmp/mysql-recovery.sock
- 载入逻辑备份(如果有);检查和重放二进制日志
- 检查已经还原的数据
- 重新以完全访问模式重启服务器
注释前面在my.cnf中添加的选项，并重启
NOTE:默认情况下InnoDB表不能通过直接复制表文件的方式在MySQL服务器之间进行移植，即便使用了innodb_file_per_table选项，而是用xtrabackup工具可以实现此种功能，前提是innodb_file_per_table开启
使用innobackupex导出表
innobackupex --apply-log --export /path/to/backup
使用innobackupex导入表
要在MySQL服务器上导入来自于其他服务器的某innodb表，需要现在当前服务器上创建一个与原表结构一致的表，而后才能实现将表导入
CREATE TABLE mytable (...) ENGINE=InnoDB;
然后将此表的表空间删除(讲自动创建的表空间删除)
ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;
将导出表的服务器的mytable表的mytable.ibd和mytable.exp文件复制到当前服务器的数据目录中，然后使用如下命令将其"导入"
ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;
Xtrabackup 部分备份
创建部分备份的方式有三种：正则表达式(--include), 枚举表文件(--tables-file)和列出要备份的数据库(--databases)
使用SELECT和LOAD语句进行备份和还原
SELECT INTO OUTFILE '' LOAD DATA INFILE '' eg. select * from students where Gender='M' into outfile '/tmp/stu.txt'; CREATE TABLE testtb LIKE students; LOAD DATA INFILE '/tmp/stu.txt' INTO TABLE testtb;
(a)使用--include 使用--include时，要求为其指定要备份的表的完整名称，即形如databasename.tablename，
innobackupex --include='^mageedu[.]tb1' /path/to/backup
(b)使用--tables-file 此选项的参数需要是一个文件名，此文件中每行包含一个要备份的表的完整名称
echo -e 'mageedu.tb1\nmageedu.tb2' > /tmp/tables.txt innobackupex --tables-file=/tmp/tables.txt /path/to/backup
(c)使用--databases 此选项接受的参数为数据名，如果要指定多个数据库，彼此间需要以空格隔开；同时，在指定某数据库时，也可以只指定其中的某张表。此外，此选项也可以接受一个文件为参数，文件中每一行为一个要备份的对象
innobackupex --databases="mageedu testdb" /path/to/backup
整理(preparing)部分备份 prepare部分备份的过程类似于导出表的过程，要使用--export选项进行
innobackupex --apply-log --export /pat/to/partial/backup
MySQL复制
主从复制
扩展： scale on： 向上扩展，垂直扩展 scale out：向外扩展，水平扩展 默认为异步工作模式来完成复制，即有写操作进来时，主服务器先写到数据中，再写二进制文件 SLAVE: IO thread,向主服务器请求二进制日志中的事件 SQL thread,从中继日志读取事件并在本地执行 从服务器的二进制日志绝大多数时间内是没有用处的，所以建议关闭。从服务器一定不可以执行写操作 MASTER: binlog dump,将IO thread请求的事件发送给对方(倾倒线程) 工作架构： 从服务器：有且只能有一个主服务器 MariaDB 08-10:支持多主模型，多源复制(multi-source replication) 一主多从： 写操作只能对主服务器执行，从服务器可以用来负责读操作 读写分离：主从模型下，让前端分发起能识别读/写，并且按需调度至目标主机： 环的方式对2^32取模，对范围内有影响。 虚拟主机的方式，将分布离散化
双主模型(可以分别读写)
-
必须设定双方的自动增长属性，以避免冲突
auto_increment_increment: 步长设定为2
auto_increment_offset 定义自动增长字段的起始值
-
数据会产生不一致。
A: update t1 set Salary=salary+1000 WHERE Age>=30; B: update t1 set Age=Age-3 WHERE Salary<3000;
功用：均衡读请求，写请求双方一致。并不能分摊写负载。
示例，主从复制的配置
版本：
- 双方的MySQL要一致
- 如果不一致，主的要低于从
从哪儿开始复制：
- 都从0开始
- 如果主服务器已经运行一段时间，摒弃额已经存在不小的数据集。把主服务器备份，然后在从服务器恢复，从主服务器备份时所处的位置开始复制
主从服务器配置过程： 主服务器：
1. 修改Server-id
2. 启用二进制日志
3. 创建有复制权限的账号
grant replication slave,replication client ON *.* TO 'repluser'@'192.168.%.%' identified by 'replpass';
SHOW MASTER STATUS;
SHOW PROCESSLIST
在主服务器上查看从服务器
SHOW SLAVE HOST
从服务器： 1 . 修改Server-id 2 . 启用中继日志 3 . 连接主服务器 4 .启动复制线程
连接主服务器的命令：
Syntax:
CHANGE MASTER TO option [, option] ...
option:
MASTER_BIND = 'interface_name'
| MASTER_HOST = 'host_name'
| MASTER_USER = 'user_name'
| MASTER_PASSWORD = 'password'
| MASTER_PORT = port_num
| MASTER_CONNECT_RETRY = interval
| MASTER_HEARTBEAT_PERIOD = interval
| MASTER_LOG_FILE = 'master_log_name'
| MASTER_LOG_POS = master_log_pos
| RELAY_LOG_FILE = 'relay_log_name'
| RELAY_LOG_POS = relay_log_pos
| MASTER_SSL = {0|1}
| MASTER_SSL_CA = 'ca_file_name'
| MASTER_SSL_CAPATH = 'ca_directory_name'
| MASTER_SSL_CERT = 'cert_file_name'
| MASTER_SSL_KEY = 'key_file_name'
| MASTER_SSL_CIPHER = 'cipher_list'
| MASTER_SSL_VERIFY_SERVER_CERT = {0|1}
| IGNORE_SERVER_IDS = (server_id_list)
change master to MASTER_HOST='192.168.48.135',MASTER_USER='repluser',MASTER_PASSWORD='replpass';
SHOW SLAVE STATUS; #默认不启动 Slave_IO_Running: No Slave_SQL_Running: No
启动SLAVE
Syntax:
START SLAVE [thread_types]
START SLAVE [SQL_THREAD] UNTIL
MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos
START SLAVE [SQL_THREAD] UNTIL
RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos
启用中途备份的思路， 先对主服务器进行备份，然后在从服务器中恢复
mysqldump -uroot -p --all-databases --flush-logs --master-data=2 --lock-all-tables> all.sql
然后在记录主服务器的二进制日志的开始位置
CREATE MASTER TO MASTER_HOST='192.168.48.135',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000002',MASTER_LOG_POS=367;
MySQL简单复制应用扩展： 1、主从服务器时间要同步(ntp): */5 * * * * /usr/bin/ntpdate 192.168.48.130 2、限制从服务器只读，应当配置在[mysqld]中 read_only=ON note：仅能限制不具有SUPER权限用户无法执行写操作 想限制所有用户还有一种方式
FLUSH TABLES WITH READ LOCK
3、如何主从复制时的事务安全 在主服务器上配置： sync_binlog=1 (此功能默认不开启，原因是autocommit是开启的状态) 这种级别的日志同步，会降低系统性能，但是从事务安全的角度来看是值得 的 从服务器有可能同步慢于主服务器 Possibly semi-synchronously on MySQL 5.5 (由Google免费提供给MySQL的) 主从： 从多个服务器，以均衡为目的挑选一个响应客户端请求的服务器：
- 轮询 round-robin (均衡性最好，但是缓存命中率最低)
- 取模算法 (可能产生雪崩效应)
- 虚拟环
公共缓存服务器，if 缓存命中则返回，then 使用轮询的方式查询各服务器，工作在旁路模式bypass. memcached: 缓存能力+API
常用的复制拓扑
- 主从模型
- 一主多从模型
- 多主模型
- 多级复制模型，master to slaves to slaves。中继服务器可以将存储引擎设置为blackhole,仅保存二进制日志和发送
- 环状模型
半同步复制
需要安装插件
Syntax: INSTALL PLUGIN plugin_name SONAME 'shared_library_name'
install plugin rpl_semi_sync_master SONAME 'semisync_master.so'; install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so';
show global variables like '%semi%'; SET GLOBAL rpl_semi_sync_master_enable=ON; SET GLOBAL rpl_semi_sync_master_timeout=1000;
rpl_semi_sync_master_enabled rpl_semi_sync_master_timeout (单位是毫秒) rpl_semi_sync_master_trace_level rpl_semi_sync_master_wait_no_slave 在从服务器上面开启半同步的IO_Thread
stop slave IO_THREAD; start slave IO_THREAD;
note:一旦某次等待超时，会自动降级为异步 取消semisync 插件
UNINSTALL PLUGIN rpl_semi_sync_master;
复制过滤器
master: binlog_do_db= binlog_ignore_db= slave: replicate_do_db= replicate_ignore_db= replicate_do_table=db_name.table_name replicate_ignore_table replicate_wild_do_table= 通配方式匹配 replicate_wild_ignore_table=
同时启用时以白名单为准，若同时在白黑名单中出现，则拒绝复制
主主复制
主服务器A上 [mysqld] server-id = 10 log-bin = mysql-bin relay-log = relay-mysql auto-increment-offset = 1 auto-increment-increment = 2
主服务器B上 [mysqld] server-id = 20 log-bin = mysql-bin relay-log = relay-mysql auto-increment-offset = 2 auto-increment-increment = 2
若此两台服务器均为新建立，则个服务器指定另一台服务器为自己的主服务器即可：
serverA|mysql> CHANGE MASTER TO MASTER_HOST='192.168.48.136',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=811 serverB|mysql> CHANGE MASTER TO MASTER_HOST='192.168.48.135',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=710
如果原来的MASTER server无法change master to, 那么执行
RESET SLAVE;
多主，且高可用的解决方案： MMM: Multi Master MySQL MHA: MySQL HA
SSL
基于SSL的复制，在CHANGE MASTER TO 时可以指定：
| MASTER_SSL = {0|1}
| MASTER_SSL_CA = 'ca_file_name'
| MASTER_SSL_CAPATH = 'ca_directory_name'
| MASTER_SSL_CERT = 'cert_file_name'
| MASTER_SSL_KEY = 'key_file_name'
复制相关的文件：
master-info: 文本文件，保存了从服务器连接至主服务器时所需要的所有信息，一般而言一行一个值。对应于show slave status 中的值 relay-log.info: 二进制日志和中继日志的坐标，保存了复制位置。包括二进制日志和中继日志的文件及位置。 为了复制的安全性，应该将以下三项都设置为1
| sync_master_info | 1 | | sync_relay_log | 1 | | sync_relay_log_info | 1 |
从服务器意外崩溃时，建议使用pt-slave-start命令来启动slave
基于行基于语句复制： 基于语句：数据量小，易于查看，适应性强；有写语句无法做到精确复制，无法对使用了触发器、存储过程等代码的应用实现精确复制 基于行：能够精确完成有着触发器、存储过程等代码场景中的复制。能完成几乎所有的复制场景。无法判断执行 了什么样的SQL语句，数据量可能略大。 默认使用mixed混合型的。目前来说单纯使用基于语句的几乎不存在
从服务器落后于主服务器：
Seconds_Behind_Master: 0 从服务器落后于主服务器的时长 评估主从服务器表中的数据是否一致：
pt-table-checksum
如果数据不一致，解决办法在从服务器上 1、重新备份并在从服务器导入数据； 2、pt-table-sync 来手动同步服务器状态 为了提高复制时的数据安全性，在主服务器上的设定： sync_binlog = 1 innodb_flush_log_at_trx_commit=1 此参数的值设定为1，性能下降会较严重，一般设定为2等。此时主服务器崩溃依然有可能导致从服务器无法获取到全部的二进制日志事件 如果master意外崩溃导致二进制日志中的某时间损坏，可以在从服务器使用如下参数忽略： sql_slave_skip_counter = 0 第三方复制解决方案：Tungsten, Galera (高性能多主DB)
MySQL GTID
编译安装mariadb环境
yum groupinstall -y Development Tools yum install -y ncurses-devel openssl-devel openssl yum install cmake
MySQL 5.6之后才引入，使得其赋值功能的配置、监控及管理变得更加易于实现，且更加健壮 GTID: unique identifiers comprising the Server UUID and a transaction number. UUID和事务号的合并 UUID: 128bits Utilities for simplifying Replication https://lunchpad.net/mysql-utilities
-
mysqlreplicate
- Enable fast and simple introduction of replication slaves
-
mysqlrplcheck
-
Provides simple verification of development and fast fault resolution
-
mysqlrplshow
-
Discovers and displays the replication topology on-demand
-
mysqlfailover
-
Enable automatic or manual failover to a slave in the event of an outage to the master
-
mysqlrpladmin
-
perform a switchover to a specific slave
多线程模型中 SQL thread应当以数据库为单位。最多一个数据库只能启动一个线程 I/O thread 应用在一从多主模型中
GTID参数设定
binlog-format：二进制日志的格式，有row、statement和mixed几种类型； 需要注意的是：当设置隔离级别为READ-COMMITED必须设置二进制日志格式为ROW，现在MySQL官方认为STATEMENT这个已经不再适合继续使用；但mixed类型在默认的事务隔离级别下，可能会导致主从数据不一致； log-slave-updates、gtid-mode、enforce-gtid-consistency、report-port和report-host：用于启动GTID及满足附属的其它需求 master-info-repository和relay-log-info-repository：启用此两项，可用于实现在崩溃时保证二进制及从服务器安全的功能； sync-master-info：启用之可确保无信息丢失； slave-paralles-workers：设定从服务器的SQL线程数；0表示关闭多线程复制功能； binlog-checksum、master-verify-checksum和slave-sql-verify-checksum：启用复制有关的所有校验功能； binlog-rows-query-log-events：启用之可用于在二进制日志记录事件相关的信息，可降低故障排除的复杂度； log-bin：启用二进制日志，这是保证复制功能的基本前提； server-id：同一个复制拓扑中的所有服务器的id号必须惟一； report-host： The host name or IP address of the slave to be reported to the master during slave registration. This value appears in the output of SHOW SLAVE HOSTS on the master server.
report-port: The TCP/IP port number for connecting to the slave, to be reported to the master during slave registration.
master-info-repository: The setting of this variable determines whether the slave logs master status and connection information to a FILE (master.info), or to a TABLE (mysql.slave_master_info)
relay-log-info-repository： This option causes the server to log its relay log info to a file or a table.
log_slave_updates： Whether updates received by a slave server from a master server should be logged to the slave's own binary log. Binary logging must be enabled on the slave for this variable to have any effect.
enforce_gtid_consistency：
GTID主从配置
1、配置master节点(MySQL5.6)
[mysqld] binlog-format=ROW log-bin=master-bin log-slave-updates=true gtid-mode=on enforce-gtid-consistency=true master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1 slave-parallel-workers=2 binlog-checksum=CRC32 master-verify-checksum=1 slave-sql-verify-checksum=1 binlog-rows-query-log_events=1 server-id=1 report-port=3306 port=3306 datadir=/Mysql_data/data socket=/tmp/mysql.sock report-host=master.example.com
配置slave节点
[mysqld] binlog-format=ROW log-slave-updates=true gtid-mode=on enforce-gtid-consistency=true master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1 slave-parallel-workers=2 binlog-checksum=CRC32 master-verify-checksum=1 slave-sql-verify-checksum=1 binlog-rows-query-log_events=1 server-id=11 report-port=3306 port=3306 log-bin=mysql-bin.log datadir=/Mysql_data/data socket=/tmp/mysql.sock report-host=slave.example.com
对于mariadb gtid-mode 和 enforce-gtid-consistency 这两项不需要 多线程应改为slave-parallel-threads
2、创建复制用户
GRANT REPLICATION SLAVE,REPLCIATION CLIENT ON *.* TO 'repluser'@'192.168.%.%' IDENTIFIED BY 'repluser'
3、为备节点提供初始数据
锁定主表，备份主节点上的数据，将其还原至从节点：如果没有启用GTID,在备份是需要master上使用show master status 命令查看二进制日志文件名称以及事件位置，以便后面启动slave节点时使用
change master to MASTER_HOST='192.168.48.135',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_USE_GTID=current_pos;
note: MASTER_USE_GTID=current_pos note: skip-slave-start #复制进程不随mysql启动 note: 若直接使用GTID current pos同步失败，则先使用二进制主从复制指定master-bin log的index和pos之后再进行同步即可
MariaDB-10,需要修改
1、不支持的参数
gtid-mod=on
enforce-gtid-consistency=true
2、修改的参数
slave-parallel-workers参数修改为slave-parallel-threads
一个新的参数：MASTER_USER_GTID={current_pos|slave_pos|no}
更换master的方式：
STOP SLAVE; CHANGE MASTER TO master_host='192.168.48.135',master_port=3312; START SLAVE;
多源复制 Multi-Source Replication:
CHANGE MASTER ['connection_name'] ... FLUSH RELAY LOGS ['connection_name'] MASTER_POS_WAIT(...,['connection_name']) RESET SLAVE ['connection_name'] SHOW RELAYLOG ['connection_name'] EVENTS SHOW SLAVE ['connection_name'] STATUS SHOW ALL SLAVES STATUS START SLAVE ['connection_name'....] START ALL SLAVES STOP SLAVE ['connection_name'] STOP ALL SLAVES
查看从节点信息
SHOW SLAVE HOSTs;
多源复制时，每个源应该使用不同的数据库；多源复制目前还不支持半同步复制，只能通过异步的方式完成复制
MySQL Proxy
实现读写分离
安装
https://dev.mysql.com/doc/mysql-proxy/en/mysql-proxy-install.html
源码安装
首先解决依赖关系：
libevent 1.x or higher (1.3b or later is preferred). lua 5.1.x or higher. glib2 2.6.0 or higher. pkg-config. libtool 1.5 or higher. MySQL 5.0.x or higher developer files.
安装：
tar zxf mysql-proxy-0.8.2.tar.gz cd mysql-proxy-0.8.2 ./configure make && make check make install
默认情况下， mysql-proxy安装在/usr/local/sbin/mysql-proxy，而Lua示例脚本安装在/usr/local/share目录中。mysql proxy的各配置参数请参见官方文档，http://dev.mysql.com/doc/refman/5.6/en/mysql-proxy-configuration.html
RPM包安装
使用RPM包在RHEL6上安装mysql-proxy,其会提供配置文件及服务脚本，但没有提供读写分享的脚本。
mysql-proxy启动参数
ADMIN_USER – the user for the proxy's admin interface. You can leave the default admin user. ADMIN_PASSWORD – the password for the admin user in clear text. Change the default password for better security. ADMIN_LUA_SCRIPT – the admin script in the Lua programming language. Without this script the admin interface cannot work. You can leave the default value. PROXY_USER – the system user under which the proxy will work. By default it is mysql-proxy, and it's safe to leave it as is. PROXY_OPTIONS – proxy options such as logging level, plugins, and Lua scripts to be loaded.
其中PROXY_OPTIONS用于定义mysql-proxy工作时的重要参数
PROXY_OPTIONS="--daemon --log-level=info --log-use-syslog --plugins=proxy --plugins=admin --proxy-backend-addresses=192.168.1.102:3306 --proxy-read-only-backend-addresses=192.168.1.105:3306 --proxy-lua-script=/usr/lib/mysql-proxy/lua/proxy/rw-splitting.lua"
mysql-proxy SysV脚本
#!/bin/bash
#
# mysql-proxy This script starts and stops the mysql-proxy daemon
#
# chkconfig: - 78 30
# processname: mysql-proxy
# description: mysql-proxy is a proxy daemon for mysql
# Source function library.
. /etc/rc.d/init.d/functions
prog="/usr/local/mysql-proxy/bin/mysql-proxy"
# Source networking configuration.
if [ -f /etc/sysconfig/network ]; then
. /etc/sysconfig/network
fi
# Check that networking is up.
[ ${NETWORKING} = "no" ] && exit 0
# Set default mysql-proxy configuration.
ADMIN_USER="admin"
ADMIN_PASSWD="admin"
ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"
PROXY_OPTIONS="--daemon"
PROXY_PID=/var/run/mysql-proxy.pid
PROXY_USER="mysql-proxy"
# Source mysql-proxy configuration.
if [ -f /etc/sysconfig/mysql-proxy ]; then
. /etc/sysconfig/mysql-proxy
fi
RETVAL=0
start() {
echo -n $"Starting $prog: "
daemon $prog $PROXY_OPTIONS --pid-file=$PROXY_PID --proxy-address="$PROXY_ADDRESS" --user=$PROXY_USER --admin-username="$ADMIN_USER" --admin-lua-script="$ADMIN_LUA_SCRIPT" --admin-password="$ADMIN_PASSWORD"
RETVAL=$?
echo
if [ $RETVAL -eq 0 ]; then
touch /var/lock/subsys/mysql-proxy
fi
}
stop() {
echo -n $"Stopping $prog: "
killproc -p $PROXY_PID -d 3 $prog
RETVAL=$?
echo
if [ $RETVAL -eq 0 ]; then
rm -f /var/lock/subsys/mysql-proxy
rm -f $PROXY_PID
fi
}
# See how we were called.
case "$1" in
start)
start
;;
stop)
stop
;;
restart)
stop
start
;;
condrestart|try-restart)
if status -p $PROXY_PIDFILE $prog >&/dev/null; then
stop
start
fi
;;
status)
status -p $PROXY_PID $prog
;;
*)
echo "Usage: $0 {start|stop|restart|reload|status|condrestart|try-restart}"
RETVAL=1
;;
esac
exit $RETVAL
创建mysql-proxy用户并提供mysql-proxy配置文件
ADMIN_USER="admin" ADMIN_PASSWORD="admin" ADMIN_ADDRESS="" ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua" PROXY_ADDRESS="" PROXY_USER="mysql-proxy" PROXY_OPTIONS="--daemon --log-level=info --log-use-syslog --plugins=proxy --plugins=admin --proxy-backend-addresses=192.168.48.135:3306 --proxy-read-only-backend-addresses=192.168.48.136:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"
mysql-proxy 配置选项
mysql-proxy的配置选项大致可分为帮助选项、管理选项、代理选项及应用程序选项几类，下面一起去介绍它们。 --help --help-admin --help-proxy --help-all ———— 以上四个选项均用于获取帮助信息； --proxy-address=host:port ———— 代理服务监听的地址和端口； --admin-address=host:port ———— 管理模块监听的地址和端口； --proxy-backend-addresses=host:port ———— 后端mysql服务器的地址和端口； --proxy-read-only-backend-addresses=host:port ———— 后端只读mysql服务器的地址和端口； --proxy-lua-script=file_name ———— 完成mysql代理功能的Lua脚本； --daemon ———— 以守护进程模式启动mysql-proxy； --keepalive ———— 在mysql-proxy崩溃时尝试重启之； --log-file=/path/to/log_file_name ———— 日志文件名称； --log-level=level ———— 日志级别； --log-use-syslog ———— 基于syslog记录日志； --plugins=plugin,.. ———— 在mysql-proxy启动时加载的插件； --user=user_name ———— 运行mysql-proxy进程的用户； --defaults-file=/path/to/conf_file_name ———— 默认使用的配置文件路径；其配置段使用[mysql-proxy]标识； --proxy-skip-profiling ———— 禁用profile； --pid-file=/path/to/pid_file_name ———— 进程文件名；
admin.lua文件
创建admin.lua并保存置/usr/local/mysql-proxy/share/doc/mysql-proxy
--[[ $%BEGINLICENSE%$
Copyright (c) 2007, 2012, Oracle and/or its affiliates. All rights reserved.
This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License as
published by the Free Software Foundation; version 2 of the
License.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
02110-1301 USA
$%ENDLICENSE%$ --]]
function set_error(errmsg)
proxy.response = {
type = proxy.MYSQLD_PACKET_ERR,
errmsg = errmsg or "error"
}
end
function read_query(packet)
if packet:byte() ~= proxy.COM_QUERY then
set_error("[admin] we only handle text-based queries (COM_QUERY)")
return proxy.PROXY_SEND_RESULT
end
local query = packet:sub(2)
local rows = { }
local fields = { }
if query:lower() == "select * from backends" then
fields = {
{ name = "backend_ndx",
type = proxy.MYSQL_TYPE_LONG },
{ name = "address",
type = proxy.MYSQL_TYPE_STRING },
{ name = "state",
type = proxy.MYSQL_TYPE_STRING },
{ name = "type",
type = proxy.MYSQL_TYPE_STRING },
{ name = "uuid",
type = proxy.MYSQL_TYPE_STRING },
{ name = "connected_clients",
type = proxy.MYSQL_TYPE_LONG },
}
for i = 1, #proxy.global.backends do
local states = {
"unknown",
"up",
"down"
}
local types = {
"unknown",
"rw",
"ro"
}
local b = proxy.global.backends[i]
rows[#rows + 1] = {
i,
b.dst.name, -- configured backend address
states[b.state + 1], -- the C-id is pushed down starting at 0
types[b.type + 1], -- the C-id is pushed down starting at 0
b.uuid, -- the MySQL Server's UUID if it is managed
b.connected_clients -- currently connected clients
}
end
elseif query:lower() == "select * from help" then
fields = {
{ name = "command",
type = proxy.MYSQL_TYPE_STRING },
{ name = "description",
type = proxy.MYSQL_TYPE_STRING },
}
rows[#rows + 1] = { "SELECT * FROM help", "shows this help" }
rows[#rows + 1] = { "SELECT * FROM backends", "lists the backends and their state" }
else
set_error("use 'SELECT * FROM help' to see the supported commands")
return proxy.PROXY_SEND_RESULT
end
proxy.response = {
type = proxy.MYSQLD_PACKET_OK,
resultset = {
fields = fields,
rows = rows
}
}
return proxy.PROXY_SEND_RESULT
end
智能管理工具：
mysql -uadmin -padmin -h192.168.48.130 --port=4041
note:端口抓包要保证工作端口工作在混杂模式
创建读写分离lua文件，保存为/usr/lib/mysql-proxy/lua/proxy/rw-splitting.lua
--[[ $%BEGINLICENSE%$
Copyright (c) 2007, 2012, Oracle and/or its affiliates. All rights reserved.
This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License as
published by the Free Software Foundation; version 2 of the
License.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
02110-1301 USA
$%ENDLICENSE%$ --]]
---
-- a flexible statement based load balancer with connection pooling
--
-- * build a connection pool of min_idle_connections for each backend and maintain
-- its size
-- *
--
--
local commands = require("proxy.commands")
local tokenizer = require("proxy.tokenizer")
local lb = require("proxy.balance")
local auto_config = require("proxy.auto-config")
--- config
--
-- connection pool
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 4,
max_idle_connections = 8,
is_debug = false
}
end
---
-- read/write splitting sends all non-transactional SELECTs to the slaves
--
-- is_in_transaction tracks the state of the transactions
local is_in_transaction = false
-- if this was a SELECT SQL_CALC_FOUND_ROWS ... stay on the same connections
local is_in_select_calc_found_rows = false
---
-- get a connection to a backend
--
-- as long as we don't have enough connections in the pool, create new connections
--
function connect_server()
local is_debug = proxy.global.config.rwsplit.is_debug
-- make sure that we connect to each backend at least ones to
-- keep the connections to the servers alive
--
-- on read_query we can switch the backends again to another backend
if is_debug then
print()
print("[connect_server] " .. proxy.connection.client.src.name)
end
local rw_ndx = 0
-- init all backends
for i = 1, #proxy.global.backends do
local s = proxy.global.backends[i]
local pool = s.pool -- we don't have a username yet, try to find a connections which is idling
local cur_idle = pool.users[""].cur_idle_connections
pool.min_idle_connections = proxy.global.config.rwsplit.min_idle_connections
pool.max_idle_connections = proxy.global.config.rwsplit.max_idle_connections
if is_debug then
print(" [".. i .."].connected_clients = " .. s.connected_clients)
print(" [".. i .."].pool.cur_idle = " .. cur_idle)
print(" [".. i .."].pool.max_idle = " .. pool.max_idle_connections)
print(" [".. i .."].pool.min_idle = " .. pool.min_idle_connections)
print(" [".. i .."].type = " .. s.type)
print(" [".. i .."].state = " .. s.state)
end
-- prefer connections to the master
if s.type == proxy.BACKEND_TYPE_RW and
s.state ~= proxy.BACKEND_STATE_DOWN and
cur_idle < pool.min_idle_connections then
proxy.connection.backend_ndx = i
break
elseif s.type == proxy.BACKEND_TYPE_RO and
s.state ~= proxy.BACKEND_STATE_DOWN and
cur_idle < pool.min_idle_connections then
proxy.connection.backend_ndx = i
break
elseif s.type == proxy.BACKEND_TYPE_RW and
s.state ~= proxy.BACKEND_STATE_DOWN and
rw_ndx == 0 then
rw_ndx = i
end
end
if proxy.connection.backend_ndx == 0 then
if is_debug then
print(" [" .. rw_ndx .. "] taking master as default")
end
proxy.connection.backend_ndx = rw_ndx
end
-- pick a random backend
--
-- we someone have to skip DOWN backends
-- ok, did we got a backend ?
if proxy.connection.server then
if is_debug then
print(" using pooled connection from: " .. proxy.connection.backend_ndx)
end
-- stay with it
return proxy.PROXY_IGNORE_RESULT
end
if is_debug then
print(" [" .. proxy.connection.backend_ndx .. "] idle-conns below min-idle")
end
-- open a new connection
end
---
-- put the successfully authed connection into the connection pool
--
-- @param auth the context information for the auth
--
-- auth.packet is the packet
function read_auth_result( auth )
if is_debug then
print("[read_auth_result] " .. proxy.connection.client.src.name)
end
if auth.packet:byte() == proxy.MYSQLD_PACKET_OK then
-- auth was fine, disconnect from the server
proxy.connection.backend_ndx = 0
elseif auth.packet:byte() == proxy.MYSQLD_PACKET_EOF then
-- we received either a
--
-- * MYSQLD_PACKET_ERR and the auth failed or
-- * MYSQLD_PACKET_EOF which means a OLD PASSWORD (4.0) was sent
print("(read_auth_result) ... not ok yet");
elseif auth.packet:byte() == proxy.MYSQLD_PACKET_ERR then
-- auth failed
end
end
---
-- read/write splitting
function read_query( packet )
local is_debug = proxy.global.config.rwsplit.is_debug
local cmd = commands.parse(packet)
local c = proxy.connection.client
local r = auto_config.handle(cmd)
if r then return r end
local tokens
local norm_query
-- looks like we have to forward this statement to a backend
if is_debug then
print("[read_query] " .. proxy.connection.client.src.name)
print(" current backend = " .. proxy.connection.backend_ndx)
print(" client default db = " .. c.default_db)
print(" client username = " .. c.username)
if cmd.type == proxy.COM_QUERY then
print(" query = " .. cmd.query)
end
end
if cmd.type == proxy.COM_QUIT then
-- don't send COM_QUIT to the backend. We manage the connection
-- in all aspects.
proxy.response = {
type = proxy.MYSQLD_PACKET_OK,
}
if is_debug then
print(" (QUIT) current backend = " .. proxy.connection.backend_ndx)
end
return proxy.PROXY_SEND_RESULT
end
-- COM_BINLOG_DUMP packet can't be balanced
--
-- so we must send it always to the master
if cmd.type == proxy.COM_BINLOG_DUMP then
-- if we don't have a backend selected, let's pick the master
--
if proxy.connection.backend_ndx == 0 then
proxy.connection.backend_ndx = lb.idle_failsafe_rw()
end
return
end
proxy.queries:append(1, packet, { resultset_is_needed = true })
-- read/write splitting
--
-- send all non-transactional SELECTs to a slave
if not is_in_transaction and
cmd.type == proxy.COM_QUERY then
tokens = tokens or assert(tokenizer.tokenize(cmd.query))
local stmt = tokenizer.first_stmt_token(tokens)
if stmt.token_name == "TK_SQL_SELECT" then
is_in_select_calc_found_rows = false
local is_insert_id = false
for i = 1, #tokens do
local token = tokens[i]
-- SQL_CALC_FOUND_ROWS + FOUND_ROWS() have to be executed
-- on the same connection
-- print("token: " .. token.token_name)
-- print(" val: " .. token.text)
if not is_in_select_calc_found_rows and token.token_name == "TK_SQL_SQL_CALC_FOUND_ROWS" then
is_in_select_calc_found_rows = true
elseif not is_insert_id and token.token_name == "TK_LITERAL" then
local utext = token.text:upper()
if utext == "LAST_INSERT_ID" or
utext == "@@INSERT_ID" then
is_insert_id = true
end
end
-- we found the two special token, we can't find more
if is_insert_id and is_in_select_calc_found_rows then
break
end
end
-- if we ask for the last-insert-id we have to ask it on the original
-- connection
if not is_insert_id then
local backend_ndx = lb.idle_ro()
if backend_ndx > 0 then
proxy.connection.backend_ndx = backend_ndx
end
else
print(" found a SELECT LAST_INSERT_ID(), staying on the same backend")
end
end
end
-- no backend selected yet, pick a master
if proxy.connection.backend_ndx == 0 then
-- we don't have a backend right now
--
-- let's pick a master as a good default
--
proxy.connection.backend_ndx = lb.idle_failsafe_rw()
end
-- by now we should have a backend
--
-- in case the master is down, we have to close the client connections
-- otherwise we can go on
if proxy.connection.backend_ndx == 0 then
return proxy.PROXY_SEND_QUERY
end
local s = proxy.connection.server
-- if client and server db don't match, adjust the server-side
--
-- skip it if we send a INIT_DB anyway
if cmd.type ~= proxy.COM_INIT_DB and
c.default_db and c.default_db ~= s.default_db then
print(" server default db: " .. s.default_db)
print(" client default db: " .. c.default_db)
print(" syncronizing")
proxy.queries:prepend(2, string.char(proxy.COM_INIT_DB) .. c.default_db, { resultset_is_needed = true })
end
-- send to master
if is_debug then
if proxy.connection.backend_ndx > 0 then
local b = proxy.global.backends[proxy.connection.backend_ndx]
print(" sending to backend : " .. b.dst.name);
print(" is_slave : " .. tostring(b.type == proxy.BACKEND_TYPE_RO));
print(" server default db: " .. s.default_db)
print(" server username : " .. s.username)
end
print(" in_trans : " .. tostring(is_in_transaction))
print(" in_calc_found : " .. tostring(is_in_select_calc_found_rows))
print(" COM_QUERY : " .. tostring(cmd.type == proxy.COM_QUERY))
end
return proxy.PROXY_SEND_QUERY
end
---
-- as long as we are in a transaction keep the connection
-- otherwise release it so another client can use it
function read_query_result( inj )
local is_debug = proxy.global.config.rwsplit.is_debug
local res = assert(inj.resultset)
local flags = res.flags
if inj.id ~= 1 then
-- ignore the result of the USE <default_db>
-- the DB might not exist on the backend, what do do ?
--
if inj.id == 2 then
-- the injected INIT_DB failed as the slave doesn't have this DB
-- or doesn't have permissions to read from it
if res.query_status == proxy.MYSQLD_PACKET_ERR then
proxy.queries:reset()
proxy.response = {
type = proxy.MYSQLD_PACKET_ERR,
errmsg = "can't change DB ".. proxy.connection.client.default_db ..
" to on slave " .. proxy.global.backends[proxy.connection.backend_ndx].dst.name
}
return proxy.PROXY_SEND_RESULT
end
end
return proxy.PROXY_IGNORE_RESULT
end
is_in_transaction = flags.in_trans
local have_last_insert_id = (res.insert_id and (res.insert_id > 0))
if not is_in_transaction and
not is_in_select_calc_found_rows and
not have_last_insert_id then
-- release the backend
proxy.connection.backend_ndx = 0
elseif is_debug then
print("(read_query_result) staying on the same backend")
print(" in_trans : " .. tostring(is_in_transaction))
print(" in_calc_found : " .. tostring(is_in_select_calc_found_rows))
print(" have_insert_id : " .. tostring(have_last_insert_id))
end
end
---
-- close the connections if we have enough connections in the pool
--
-- @return nil - close connection
-- IGNORE_RESULT - store connection in the pool
function disconnect_client()
local is_debug = proxy.global.config.rwsplit.is_debug
if is_debug then
print("[disconnect_client] " .. proxy.connection.client.src.name)
end
-- make sure we are disconnection from the connection
-- to move the connection into the pool
proxy.connection.backend_ndx = 0
end
补充
Apple使用PgSQL MySQL并不区分下划线和破折线
范式
RDMBS设计范式： 设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴德斯科范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般说来，数据库只需满足第三范式(3NF）就行了。
(1) 第一范式（1NF）
所谓第一范式（1NF）是指在关系模型中，对域添加的一个规范要求，所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。即实体中的某个属性有多个值时，必须拆分为不同的属性。在符合第一范式（1NF）表中的每个域值只能是实体的一个属性或一个属性的一部分。简而言之，第一范式就是无重复的域。
说明：在任何一个关系数据库中，第一范式（1NF）是对关系模式的设计基本要求，一般设计中都必须满足第一范式（1NF）。不过有些关系模型中突破了1NF的限制，这种称为非1NF的关系模型。换句话说，是否必须满足1NF的最低要求，主要依赖于所使用的关系模型。
(2) 第二范式(2NF)
第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或记录必须可以被唯一地区分。选取一个能区分每个实体的属性或属性组，作为实体的唯一标识。
第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。简而言之，第二范式就是在第一范式的基础上属性完全依赖于主键。
(3) 第三范式（3NF）
第三范式（3NF）是第二范式（2NF）的一个子集，即满足第三范式（3NF）必须满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个关系中不包含已在其它关系已包含的非主关键字信息。简而言之，第三范式就是属性不依赖于其它非主属性，也就是在满足2NF的基础上，任何非主属性不得传递依赖于主属性。
