MariaDB or MySQL:

MariaDB or MySQL:

DBMS
RDBMS：关系型数据库管理系统
C/S：通过专有协议
关系模型：表(行，列)，二维关系

范式：第一范式、第二范式、第三范式；
关系运算：
选择
投影

数据库：表，索引，视图(虚表)
SQL: Structure Query Language
DDL, DML
编程接口：
存储过程
存储函数
触发器
事件调度器
过程式编程：选择、循环

三层模型：
物理层
逻辑层
视图层

解决方案：
Oracle, Sybase, Infomix, DB2
MySQL, MariaDB, PostgreSQL, SQLite

MySQL --> 5.1 --> 5.5 --> 5.6 --> 5.7
MariaDB
插件式存储引擎
单进程多线程
连接线程
守护线程

配置文件：集中式的配置，能够为mysql的各应用程序提供配置信息
[mysqld]
[mysqld_safe]
[mysqld_multi]
[server]
[mysql]
[mysqldump]
[client]

parameter = value

skip-name-resolve
skip_name_resolve

查找路径：/etc/my.cnf --> /etc/mysql/my.cnf --> $MYSQL_HOME/my.cnf --> --default-extra-file=/path/to/somedir/my.cnf --> ~/.my.cnf

安装方法：
os vendor: rpm
MySQL:
rpm
展开可用
源码

安装后的设定：
(1) 为所有root用户设定密码；
mysql> SET PASSWORD
mysql> update mysql.user SET password=PASSWORD('your_pass') WHERE cluase;
# mysqlamin

(2) 删除所有匿名用户
mysql> DROP USER ''@'localhost';

上述两步骤可运行命令：mysql_secure_installation

(3) 建议关闭主机名反解功能；

元数据数据库：mysql
user, host等；

mysql --> mysqld
客户端程序：
mysql: 交互式的CLI工具；
mysqldump: 备份工具，基于mysql协议向mysqld发起查询请求，并将查得的所有数据转换成insert等写操作语句保存文本文件中；
mysqladmin：基于mysql协议管理mysqld；
mysqlimport: 数据导入工具；
非客户端类的管理工具：
myisamchk, myisampack

如何获取程序默认使用的配置：
mysql --print-defaults
mysqld --print-defaults

客户端类应用程序的可用选项：
-u, --user=
-h, --host=
-p, --passowrd=
-P, --port=
--protocol=
-S, --socket=
-D, --database=
-C, --compress

mysql -e "SQL"

mysql的使用模式：
交互式模式：
可运行命令有两类：
客户端命令：
\h, help
服务器端命令：
SQL, 需要语句结束符；
脚本模式：
# mysql -uUSERNAME -hHOST -pPASSWORD < /path/from/somefile.sql
mysql> source /path/from/somefile.sql

服务器端(mysqld)：工作特性有多种定义方式
命令行选项
配置文件参数

获取可用参数列表：
mysqld --help --verbose

获取运行中的mysql进程使用各服务器参数及其值：
mysql> SHOW GLOBAL VARIABLES;
mysql> SHOW [SESSION] VARIABLES;

注意：其中有些参数支持运行时修改，会立即生效；有些参数不支持，且只能通过修改配置文件，并重启服务器程序生效；
有些参数作用域是全局的，且不可改变；有些可以为每个用户提供单独的设置；

修改服务器变量的值：
mysql> help SET

全局：
mysql> SET GLOBAL system_var_name=value;
mysql> SET @@global.system_var_name=value;
会话：
mysql> SET [SESSION] system_var_name=value;
mysql> SET @@[session.]system_var_name=value;

状态变量：用于保存mysqld运行中的统计数据的变量；
mysql> SHOW GLOBAL STATUS;
mysql> SHOW [SESSION] STATUS;

SQL：ANSI SQL
SQL-86, SQL-89, SQL-92, SQL-99, SQL-03

MySQL 数据类型：
字符型
数值型
日期时间型
内建类型

字符型：
CHAR, BINARY：定长数据类型；
VARCHAR, VARBINARY：变长数据类型；需要结束符；
TEXT：TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT
BLOB: TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB
ENUM, SET
数值型：
精确数值型：
整型：TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
十进制型：DECIMAL
近似数值型
浮点型：
FLOAT
DOUBLE

BIT

日期时间型：
DATE
TIME
DATETIME
TIMESTAMP
YEAR(2), YEAR(4)

字符类型修饰符：
NOT NULL: 非空约束；
NULL:
DEFAULT 'STRING': 指明默认值；
CHARACTER SET ''：使用的字符集；
COLLATION：使用的排序规则

mysql> SHOW CHARACTER SET;
mysql> SHOW COLLATION;

整型数据修饰型：
NOT NULL
NULL
DEFAULT NUMBER

AUTO_INCREMENT:
UNSIGNED
PRIMARY KEY|UNIQUE KEY
NOT NULL

mysql> SELECT LAST_INSERT_ID();

日期时间型修饰符：
NOT NULL
NULL
DEFAULT

内建类型SET和ENUM的修饰符：
NOT NULL
NULL
DEFAULT

SQL MODE：定义mysqld对约束等的响应行为；
修改方式：
mysql> SET GLOBAL sql_mode='MODE';
mysql> SET @@global.sql_mode='MODE';

需要修改权限；仅对修改后新创建的会话有效；对已经建立的会话无效；

mysql> SET SESSION sql_mode='MODE';
mysql> SET @@session.sql_mode='MODE';

常用MODE：TRADITIONAL, STRICT_TRANS_TABLES, or STRICT_ALL_TABLES

SQL: DDL, DML
DDL: 数据定义语言；
CREATE, ALTER, DROP

DB组件：数据库、表、索引、视图、用户、存储过程、存储函数、触发器、事件调度器等

CREATE相关的常用命令：
CREATE DATABASE
CREATE EVENT
CREATE FUNCTION
CREATE FUNCTION UDF
CREATE INDEX
CREATE PROCEDURE
CREATE SERVER
CREATE TABLE
CREATE TABLESPACE
CREATE TRIGGER
CREATE USER
CREATE VIEW

DML：数据操作语言；
INSERT, DELETE, UPDATE, SELECT

数据库：
CREATE, ALTER, DROP
{DATABASE|SCHEMA}
[IF EXISTS]
[IF NOT EXISTS]

表：二维关系
设计表：遵循规范；

定义：字段，索引
字段：字段名，字段数据类型，修改符
约束，索引：应该创建在经常用作查询条件的字段上；
索引：实现级别在存储引擎；
分类：
稠密索引、稀疏索引
B+索引、hash索引、R树索引、FULLTEXT索引
聚集索引、非聚集索引
简单索引、组合索引

创建表：CREATE TABLE
(1) 直接创建；
(2) 通过查询现存的表创建；新表会被直接插入查询而来的数据；
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) }

注意：Storage Engine是指表类型，也即在表创建时指明其使用的存储引擎；
同一个库中表要使用同一种存储引擎类型；

查看表结构：
DESCRIBE tbl_name;

查看表状态信息：
SHOW [FULL] TABLES [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]

修改表：ALTER TABLE
删除表：DROP TABLE

MySQL基础

单进程多线程：
用户连接：连接线程

MySQL数据文件类型：
数据文件、索引文件
重做日志、撤消日志、二进制日志、错误日志、查询日志、慢查询日志、(中继日志)

DDL & DML:
索引管理：
按特定数据结构存储的数据；

索引类型：
聚集索引、非聚集索引：数据是否与索引存储在一起；
主键索引、辅助索引
稠密索引、稀疏索引：是否索引了每一个数据项；
B+ TREE、HASH、R TREE
简单索引、组合索引

左前缀索引
覆盖索引

管理索引的途径：
创建索引：创建表时指定；CREATE INDEX
创建或删除索引：修改表的命令
删除索引：DROP INDEX

查看表上的索引：
SHOW {INDEX | INDEXES | KEYS}
{FROM | IN} tbl_name
[{FROM | IN} db_name]
[WHERE expr]

EXPLAIN

视图：VIEW
虚表

创建方法：
CREATE
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

删除视图：
DROP VIEW [IF EXISTS]
view_name [, view_name] ...
[RESTRICT | CASCADE]

视图中的数据事实上存储于“基表”中，因此，其修改操作也会针对基表实现；其修改操作受基表限制；

DML:
INSERT, DELETE, UPDATE, SELECT

INSERT：
一次插入一行或多行数据；
Syntax:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]

INSERT tbl_name [(col1,...)] VALUES (val1,...), (val21,...)

Or:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]

Or:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]

DELETE:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

注意：一定要有限制条件，否则将清空表中的所有数据；
限制条件：
WHERE
LIMIT

UPDATE：

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

注意：一定要有限制条件，否则将修改所有行的指定字段；
限制条件：
WHERE
LIMIT

SELECT:

Query Cache

查询执行路径中的组件：查询缓存、解析器、预处理器、优化器、查询执行引擎、存储引擎；

SELECT语句的执行流程：
FROM Clause --> WHERE Clause --> GROUP BY --> HAVING Clause --> ORDER BY --> SELECT --> LIMIT

单表查询：
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[SQL_CACHE | SQL_NO_CACHE]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[FOR UPDATE | LOCK IN SHARE MODE]

DISTINCT: 数据去重；
SQL_CACHE: 显式指定存储查询结果于缓存之中；
SQL_NO_CACHE: 显式查询结果不予缓存；

query_cache_type的值为'ON'时，查询缓存功能打开；
SELECT的结果符合缓存条件即会缓存，否则，不予缓存；
显式指定SQL_NO_CACHE，不予缓存；
query_cache_type的值为'DEMAND'时，查询缓存功能按需进行；
显式指定SQL_CACHE的SELECT语句才会缓存；其它均不予缓存；

字段显示可以使用别名：
col1 AS alias1, col2 AS alias2, ...

WHERE子句：指明过滤条件以实现“选择”的功能：
过滤条件：布尔型表达式；

算术操作符：+, -, *, /, %
比较操作符：=, !=, <>, <=>, >, >=, <, <=

BETWEEN min_num AND max_num
IN (element1, element2, ...)
IS NULL
IS NOT NULL
LIKE:
%: 任意长度的任意字符；
_：任意单个字符；
RLIKE：
REGEXP：匹配字符串可用正则表达式书写模式；

逻辑操作符：

NOT
AND
OR
XOR

GROUP：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算：
avg(), max(), min(), count(), sum()

HAVING: 对分组聚合运算后的结果指定过滤条件；

ORDER BY: 根据指定的字段对查询结果进行排序；
升序：ASC
降序：DESC

LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制；

对查询结果中的数据请求施加“锁”：
FOR UPDATE: 写锁，排他锁；
LOCK IN SHARE MODE: 读锁，共享锁

练习：导入hellodb.sql生成数据库
(1) 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄；
(2) 以ClassID为分组依据，显示每组的平均年龄；
(3) 显示第2题中平均年龄大于30的分组及平均年龄；
(4) 显示以L开头的名字的同学的信息；
(5) 显示TeacherID非空的同学的相关信息；
(6) 以年龄排序后，显示年龄最大的前10位同学的信息；
(7) 查询年龄大于等于20岁，小于等于25岁的同学的信息；用三种方法；

多表查询：
交叉连接：笛卡尔乘积；
内连接：
等值连接：让表之间的字段以“等值”建立连接关系；
不等值连接
自然连接
自连接
外连接：
左外连接：
FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
右外连接
FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col

子查询：在查询语句嵌套着查询语句
基于某语句的查询结果再次进行的查询

用在WHERE子句中的子查询：
(1) 用于比较表达式中的子查询；子查询仅能返回单个值；
SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age) FROM students);
(2) 用于IN中的子查询：子查询应该单键查询并返回一个或多个值从构成列表；
SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);
(3) 用于EXISTS；

用于FROM子句中的子查询；
使用格式：SELECT tb_alias.col1,... FROM (SELECT clause) AS tb_alias WHERE Clause;
示例：
SELECT s.aage,s.ClassID FROM (SELECT avg(Age) AS aage,ClassID FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID) AS s WHERE s.aage>30;

联合查询：UNION
SELECT Name,Age FROM students UNION SELECT Name,Age FROM teachers;

练习：导入hellodb.sql，以下操作在students表上执行
1、以ClassID分组，显示每班的同学的人数；
2、以Gender分组，显示其年龄之和；
3、以ClassID分组，显示其平均年龄大于25的班级；
4、以Gender分组，显示各组中年龄大于25的学员的年龄之和；

练习：导入hellodb.sql，完成以下题目：
1、显示前5位同学的姓名、课程及成绩；
2、显示其成绩高于80的同学的名称及课程；
3、求前8位同学每位同学自己两门课的平均成绩，并按降序排列；
4、显示每门课程课程名称及学习了这门课的同学的个数；

思考：
1、如何显示其年龄大于平均年龄的同学的名字？
2、如何显示其学习的课程为第1、2，4或第7门课的同学的名字？
3、如何显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学？
4、统计各班级中年龄大于全校同学平均年龄的同学。

回顾：DDL, DML
索引

MySQL存储引擎：

表类型：
CREATE TABLE ... ENGINE=

InnoDB：
处理大量的短期事务；
数据存储于“表空间(table space)”中；
(1) 所有InnoDB表的数据和索引放置于同一个表空间中；
表空间文件：datadir定义的目录下
数据文件：ibddata1, ibddata2, ...
(2) 每个表单独使用一个表空间存储表的数据和索引；
innodb_file_per_table=ON

数据文件(存储数据和索引)：tbl_name.ibd,
表格式定义：tbl_name.frm

基于MVCC来支持高并发，支持所有的四个隔离级别，默认级别为REPEATABLE READ; 间隙锁防止幻读；
使用聚集索引
支持“自适应hash索引”
锁粒度：行级锁

MariaDB (XtraDB (percona))

数据存储：表空间
并发：MVCC, 间隙锁
索引：聚集索引、辅助索引
性能：预计操作、自适应hash、插入缓存区
备份：支持热备(xtrabacup)

MyISAM：
支持全文索引(FULLTEXT index)、压缩、空间函数(GIS); 但不支持事务，且为表级锁；
崩溃后无法安全恢复

适用场景：只读（或者写较少）、表较小（可以接受长时间进行修复操作）
Aria：crash-safe

文件：
tbl_name.frm: 表格式定义
tbl_name.MYD: 数据文件
tbl_name.MYI: 索引文件

特性：
加锁和并发：表级锁
修复：手工或自动修复、但可能丢失数据
索引：非聚集索引
延迟更新索引键：
压缩表

行格式：dynamic, fixed, compressed, compact, redundent

其它的存储引擎：
CSV：将普通的CSV(字段通过逗号分隔)作为MySQL表使用；
MRG_MYISAM：将多个MyISAM表合并成为一个虚拟表；
BLACKHOLE：类似于/dev/null，不真正存储任何数据；
MEMORY：所有数据都保存于内存中，内存表；支持hash索引；表级锁；
临时表
PERFORMANCE_SCHEMA：伪存储引擎；
ARCHIVE：只支持SELECT和INSERT操作；支持行级锁和专用缓存区；
FEDERATED：用于访问其它远程MySQL服务器一个代理，它通过创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，而后完成数据存取；
在MariaDB的上实现是FederatedX

MariaDB支持的其它存储引擎：
OQGraph
SphinxSE
TokuDB
Cassandra
CONNECT
SQUENCE

并发控制：
锁：
读锁：共享锁
写锁：独占锁

锁粒度：
表级锁
行级锁

锁策略：在锁粒度及数据安全性寻求的平衡机制；
每种存储引擎都可以自行实现其锁策略和锁粒度；
MySQL在服务器级也实现了锁，表级锁；用户可显式请求；
(1) LOCK TABLES
tbl_name [[AS] alias] lock_type
[, tbl_name [[AS] alias] lock_type] ...

lock_type:
READ [LOCAL]
| [LOW_PRIORITY] WRITE

UNLOCK TABLES

(2) FLUSH TABLES tb_name[,...] [WITH READ LOCK] [FOR UPDATE]

(3) SELECT clase [FOR UPDATE] [WITH READ LOACK]

分类：
隐式锁：由存储引擎自动施加锁；
显式锁：

MySQL事务：

事务：一组原子性的SQL查询，或者说一个独立工作单元。

事务日志：

ACID测试：
A：atomicity，原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚；
C：consistency, 一致性；数据库总是从一个一致性状态转换为另一个一致性状态；
I：Isolation，隔离性；一个事务所做出的操作在提交之前，是不能为其它所见；隔离有多种隔离级别；
D：durability: 持久性；一旦事务提交，其所做的修改会永久保存于数据库中；

事务：
启动事务：START TRANSACTION
...
...
结束事务：
(1) COMMIT：提交
(2) ROLLBACK: 回滚

注意：只有事务型存储引擎方能支持此类操作；

建议：显式请求和提交事务，而不要使用“自动提交”功能；
autocommit={1|0}

事务支持savepoint
SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier

事务隔离级别：
READ UNCOMMITTED (读未提交)
READ COMMITTED (读提交)
REPEATABLE READ (可重读)
SERIALIZABILE (可串行化)

可能存在问题：
脏读；
不可重复读；
幻读；
加锁读；

tx_isolation：服务器变量，默认为REPEATABLE-READ；可在SESSION级进行修改；

SET tx_isolation=''
READ-UNCOMMITTED
READ-COMMITTED
REPEATABLE-READ
SERIALIZABLE

MVCC: 多版本并发控制

死锁：
两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态；

事务日志：
事务日志的写入类型为“追加”，因此其操作为“顺序IO”；此日志通常也被称为“预写式日志(write ahead logging)”；

innodb_log_file_size
innodb_log_files_in_group
innodb_log_group_home_dir

MySQL用户和权限管理

权限类别：
库级别
表级别
字段级别
管理类
程序类

管理类：
CREATE TEMPORARY TABLES
CREATE USER
FILE
SUPER
SHOW DATABASES
RELOAD
SHUTDOWN
REPLICATION SLAVE
REPLICATION CLIENT
LOCK TABLES
PROCESS

程序类：
FUNCTION
PROCEDURE
TRIGGER

CREATE, ALTER, DROP, EXCUTE

库和表级别：TABLE or DATABASE
ALTER
CREATE
CREATE VIEW
DROP
INDEX
SHOW VIEW
GRANT OPTION：能够把自己获得的权限赠经其他用户一个副本；

数据操作：
SELECT
INSERT
DELETE
UPDATE

字段级别：
SELECT(col1,col2,...)
UPDATE(col1,col2,...)
INSERT(col1,col2,...)

所有有限：ALL PRIVILEGES, ALL

元数据数据库：mysql
授权表：
db, host, user
columns_priv, tables_priv, procs_priv, proxies_priv

用户账号：
'USERNAME'@'HOST'：
@'HOST':
主机名；
IP地址或Network;
通配符：
%, _: 172.16.%.%

创建用户：CREATE USER
CREATE USER 'USERNAME'@'HOST' [IDENTIFIED BY 'password']；

查看用户获得的授权：SHOW GRANTS FOR
SHOW GRANTS FOR 'USERNAME'@'HOST'

用户重命名：RENAME USER
RENAME USER old_user_name TO new_user_name

删除用户：DROP USER 'USERNAME'@'HOST'

修改密码：
(1) SET PASSWORD FOR
(2) UPDATE mysql.user SET password=PASSWORD('your_password') WHERE clause;
(3) mysqladmin password
mysqladmin [OPTIONS] command command....
-u, -h, -p

忘记管理员密码的解决办法：
(1) 启动mysqld进程时，为其使用：--skip-grant-tables --skip-networking
(2) 使用UPDATE命令修改管理员密码
(3) 关闭mysqld进程，移除上述两个选项，重启mysqld;

授权：GRANT
GRANT priv_type[,...] ON [{table|function|procedure}] db.{table|routine} TO 'USERNAME'@'HOST' [IDENTIFIED BY 'password']
[REQUIRE SSL] [WITH with_option]

with_option:
GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count

取消授权：REVOKE
REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level FROM user [, user] ...

练习：授权test用户通过任意主机连接当前mysqld，但每秒钟最大查询次数不得超过5次；此账户的同时连接次数不得超过3次；

查询缓存：

如何判断是否命中：
通过查询语句的哈希值判断：哈希值考虑的因素包括
查询本身、要查询的数据库、客户端使用协议版本，...

查询语句任何字符上的不同，都会导致缓存不能命中；

哪此查询可能不会被缓存？
查询中包含UDF、存储函数、用户自定义变量、临时表、mysql库中系统表、或者包含列级权限的表、有着不确定值的函数(Now());

查询缓存相关的服务器变量：
query_cache_min_res_unit: 查询缓存中内存块的最小分配单位；
较小值会减少浪费，但会导致更频繁的内存分配操作；
较大值会带来浪费，会导致碎片过多；
query_cache_limit：能够缓存的最大查询结果；
对于有着较大结果的查询语句，建议在SELECT中使用SQL_NO_CACHE
query_cache_size：查询缓存总共可用的内存空间；单位是字节，必须是1024的整数倍；
query_cache_type: ON, OFF, DEMAND
query_cache_wlock_invalidate：如果某表被其它的连接锁定，是否仍然可以从查询缓存中返回结果；默认值为OFF，表示可以在表被其它连接淘宝的场景中继续从缓存返回数据；ON则表示不允许；

查询相关的状态变量
SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 16759688 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+----------+

缓存命中率的评估：Qcache_hits/(Qcache_hits+Com_select)

MySQL中的索引：

基本法则：索引应该构建在被用作查询条件的字段上；

索引类型：
B+ Tree索引：顺序存储，每一个叶子节点到根结点的距离是相同的；左前缀索引，适合查询范围类的数据；

可以使用B-Tree索引的查询类型：全键值、键值范围或键前缀查找；
全值匹配：精确某个值, "Jinjiao King"；
匹配最左前缀：只精确匹配起头部分，"Jin%"
匹配范围值：
精确匹配某一列并范围匹配另一列：
只访问索引的查询

不适合使用B-Tree索引的场景：
如果不从最左列开始，索引无效； (Age,Name)
不能跳过索引中的列；(StuID,Name,Age)
如果查询中某个列是为范围查询，那么其右侧的列都无法再使用索引优化查询；(StuID,Name)

Hash索引：基于哈希表实现，特别适用于精确匹配索引中的所有列；
注意：只有Memory存储引擎支持显式hash索引；

适用场景：
只支持等值比较查询，包括=, IN(), <=>;

不适合使用hash索引的场景：
存储的非为值的顺序，因此，不适用于顺序查询；
不支持模糊匹配；

空间索引（R-Tree）：
MyISAM支持空间索引；

全文索引(FULLTEXT)：
在文本中查找关键词；

索引优点：
索引可以降低服务需要扫描的数据量，减少了IO次数；
索引可以帮助服务器避免排序和使用临时表；
索引可以帮助将随机I/O转为顺序I/O；

高性能索引策略：
独立使用列，尽量避免其参与运算；
左前缀索引：索引构建于字段的左侧的多少个字符，要通过索引选择性来评估
索引选择性：不重复的索引值和数据表的记录总数的比值；
多列索引：
AND操作时更适合使用多列索引；
选择合适的索引列次序：将选择性最高放左侧；

冗余和重复索引：
不好的索引使用策略

通过EXPLAIN来分析索引的有效性：

EXPLAIN SELECT clause
获取查询执行计划信息，用来查看查询优化器如何执行查询；

输出：
id: 当前查询语句中，每个SELECT语句的编号；

复杂类型的查询有三种：
简单子查询；
用于FROM中的子查询；
联合查询：UNION；

注意：UNION查询的分析结果会出现一外额外匿名临时表；

select_type：
简单查询为SIMPLE
复杂查询：
SUBQUERY: 简单子查询；
DERIVED: 用于FROM中的子查询；
UNION：UNION语句的第一个之后的SELECT语句；
UNION RESULT: 匿名临时表；

table：SELECT语句关联到的表；

type：关联类型，或访问类型，即MySQL决定的如何去查询表中的行的方式；
ALL: 全表扫描；
index：根据索引的次序进行全表扫描；如果在Extra列出现“Using index”表示了使用覆盖索引，而非全表扫描；
range：有范围限制的根据索引实现范围扫描；扫描位置始于索引中的某一点，结束于另一点；
ref: 根据索引返回表中匹配某单个值的所有行；
eq_ref：仅返回一个行，但与需要额外与某个参考值做比较；
const, system: 直接返回单个行；

possible_keys：查询可能会用到的索引；

key: 查询中使用了的索引；

key_len: 在索引使用的字节数；

ref: 在利用key字段所表示的索引完成查询时所有的列或某常量值；

rows：MySQL估计为找所有的目标行而需要读取的行数；

Extra：额外信息

Using index：MySQL将会使用覆盖索引，以避免访问表；
Using where：MySQL服务器将在存储引擎检索后，再进行一次过滤；
Using temporary：MySQL对结果排序时会使用临时表；
Using filesort：对结果使用一个外部索引排序；

回顾：用户和权限、缓存、字段类型的选择

用户:
sock，/var/lib/mysql/mysql.sock, /tmp/mysql.sock
权限：管理类权限、库、表、字段
GRANT, REVOKE

缓存：
query_cache
Qcache

MySQL日志：
查询日志：
慢查询日志：查询执行时长超过指定时长的查询操作所记录日志
错误日志：
二进制日志：
中继日志：
事务日志：
ACID, 随机I/O转换为顺序I/O

查询日志：
log_output = {TABLE|FILE|NONE}
log_output = TABLE,FILE
FILE: gerenal_log
general_log = {ON|OFF}: 是否启用查询日志
general_log_file = www.log：当log_output有FILE类型时，日志信息的记录位置；

慢查询日志：
SELECT @@GLOBAL.long_query_time;
+--------------------------+
| @@GLOBAL.long_query_time |
+--------------------------+
| 10.000000 |
+--------------------------+

slow_query_log = {ON|OFF}：是否启用慢查询日志
slow_query_log = /path/to/somefile: 日志文件路径

log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
log_slow_rate_limit =
log_slow_verbosity =

错误日志：
mysqld启动和关闭过程中输出的信息；
mysqld运行中产生的错误信息；
event scheduler运行一个event时产生的日志信息；
在主从复制架构中的从服务器上启动从服务器线程时产生的日志信息；

log_error = /path/to/somefile
log_warnings = {ON|OFF}：是否记录警告信息于错误日志中；

二进制日志：
SHOW {BINARY | MASTER} LOGS：查看主服务器端处于由mysqld维护状态中的二进制日志文件；

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]：显示指定的二进制日志文件中的相关事件

日志记录格式：
基于“语句”记录；statement
基于“行”记录；row

“混合” ：mixed

二进制日志文件的构成：
日志文件：文件名前缀.文件名后缀
索引文件：文件名前缀.index

服务器变量：
log_bin = /path/to/somefile
binlog_format = MIXED
sql_log_bin = ON
max_binlog_size = 1073741824
二进制日志文件的单文件上限；
max_binlog_cache_size = 18446744073709547520
max_binlog_stmt_cache_size = 18446744073709547520

sync_binlog = 0：设定多久同步一次二进制日志文件；0表示不同步；任何正值都表示记录多少个语句后同步一次；

二进制日志的格式：

# at 19364
#140829 15:50:07 server id 1 end_log_pos 19486 Query thread_id=13 exec_time=0 error_code=0
SET TIMESTAMP=1409298607/*!*/;
GRANT SELECT ON tdb.* TO tuser@localhost
/*!*/;
# at 19486

事件发生的日期和时间；(140829 15:50:07)
事件发生在服务器的标识（server id）
事件的结束位置：（end_log_pos 19486）
事件的类型：(Query)
事件发生时所在的服务器执行此事件的线程的ID：（thread_id=13）
语句的时间戳与将其写入二进制文件中的时间差：（exec_time=0）
错误代码：（error_code=0)
事件内容：（SET TIMESTAMP=1409298607/*!*/;
GRANT SELECT ON tdb.* TO tuser@localhost）

GTID事件专属：
事件所属的全局事务的GTID：（GTID 0-1-2）

二进制日志的查看命令：
mysqlbinlog
-j, --start-position=#：从指定的事件位置查看
--stop-position=#：只显示到指定的事件位置

--start-datetime=name
--stop-datetime=name
YYYY-MM-DD hh:mm:ss

-u
-h
-p

中继日志：

事务日志（innodb存储引擎）：

练习：启用慢查询日志

 

补充材料：日志相关的服务器参数详解：

expire_logs_days={0..99}
设定二进制日志的过期天数，超出此天数的二进制日志文件将被自动删除。默认为0，表示不启用过期自动删除功能。如果启用此功能，自动删除工作通常发生在MySQL启动时或FLUSH日志时。作用范围为全局，可用于配置文件，属动态变量。

general_log={ON|OFF}
设定是否启用查询日志，默认值为取决于在启动mysqld时是否使用了--general_log选项。如若启用此项，其输出位置则由--log_output选项进行定义，如果log_output的值设定为NONE，即使用启用查询日志，其也不会记录任何日志信息。作用范围为全局，可用于配置文件，属动态变量。

general_log_file=FILE_NAME
查询日志的日志文件名称，默认为“hostname.log"。作用范围为全局，可用于配置文件，属动态变量。

binlog-format={ROW|STATEMENT|MIXED}
指定二进制日志的类型，默认为STATEMENT。如果设定了二进制日志的格式，却没有启用二进制日志，则MySQL启动时会产生警告日志信息并记录于错误日志中。作用范围为全局或会话，可用于配置文件，且属于动态变量。

log={YES|NO}
是否启用记录所有语句的日志信息于一般查询日志(general query log)中，默认通常为OFF。MySQL 5.6已经弃用此选项。

log-bin={YES|NO}
是否启用二进制日志，如果为mysqld设定了--log-bin选项，则其值为ON，否则则为OFF。其仅用于显示是否启用了二进制日志，并不反应log-bin的设定值。作用范围为全局级别，属非动态变量。

log_bin_trust_function_creators={TRUE|FALSE}
此参数仅在启用二进制日志时有效，用于控制创建存储函数时如果会导致不安全的事件记录二进制日志条件下是否禁止创建存储函数。默认值为0，表示除非用户除了CREATE ROUTING或ALTER ROUTINE权限外还有SUPER权限，否则将禁止创建或修改存储函数，同时，还要求在创建函数时必需为之使用DETERMINISTIC属性，再不然就是附带READS SQL DATA或NO SQL属性。设置其值为1时则不启用这些限制。作用范围为全局级别，可用于配置文件，属动态变量。

log_error=/PATH/TO/ERROR_LOG_FILENAME
定义错误日志文件。作用范围为全局或会话级别，可用于配置文件，属非动态变量。

log_output={TABLE|FILE|NONE}
定义一般查询日志和慢查询日志的保存方式，可以是TABLE、FILE、NONE，也可以是TABLE及FILE的组合(用逗号隔开)，默认为TABLE。如果组合中出现了NONE，那么其它设定都将失效，同时，无论是否启用日志功能，也不会记录任何相关的日志信息。作用范围为全局级别，可用于配置文件，属动态变量。

log_query_not_using_indexes={ON|OFF}
设定是否将没有使用索引的查询操作记录到慢查询日志。作用范围为全局级别，可用于配置文件，属动态变量。

log_slave_updates
用于设定复制场景中的从服务器是否将从主服务器收到的更新操作记录进本机的二进制日志中。本参数设定的生效需要在从服务器上启用二进制日志功能。

log_slow_queries={YES|NO}
是否记录慢查询日志。慢查询是指查询的执行时间超出long_query_time参数所设定时长的事件。MySQL 5.6将此参数修改为了slow_query_log。作用范围为全局级别，可用于配置文件，属动态变量。

log_warnings=#
设定是否将警告信息记录进错误日志。默认设定为1，表示启用；可以将其设置为0以禁用；而其值为大于1的数值时表示将新发起连接时产生的“失败的连接”和“拒绝访问”类的错误信息也记录进错误日志。

long_query_time=#
设定区别慢查询与一般查询的语句执行时间长度。这里的语句执行时长为实际的执行时间，而非在CPU上的执行时长，因此，负载较重的服务器上更容易产生慢查询。其最小值为0，默认值为10，单位是秒钟。它也支持毫秒级的解析度。作用范围为全局或会话级别，可用于配置文件，属动态变量。

max_binlog_cache_size{4096 .. 18446744073709547520}
二进定日志缓存空间大小，5.5.9及以后的版本仅应用于事务缓存，其上限由max_binlog_stmt_cache_size决定。作用范围为全局级别，可用于配置文件，属动态变量。

max_binlog_size={4096 .. 1073741824}
设定二进制日志文件上限，单位为字节，最小值为4K，最大值为1G，默认为1G。某事务所产生的日志信息只能写入一个二进制日志文件，因此，实际上的二进制日志文件可能大于这个指定的上限。作用范围为全局级别，可用于配置文件，属动态变量。

 

max_relay_log_size={4096..1073741824}
设定从服务器上中继日志的体积上限，到达此限度时其会自动进行中继日志滚动。此参数值为0时，mysqld将使用max_binlog_size参数同时为二进制日志和中继日志设定日志文件体积上限。作用范围为全局级别，可用于配置文件，属动态变量。

innodb_log_buffer_size={262144 .. 4294967295}
设定InnoDB用于辅助完成日志文件写操作的日志缓冲区大小，单位是字节，默认为8MB。较大的事务可以借助于更大的日志缓冲区来避免在事务完成之前将日志缓冲区的数据写入日志文件，以减少I/O操作进而提升系统性能。因此，在有着较大事务的应用场景中，建议为此变量设定一个更大的值。作用范围为全局级别，可用于选项文件，属非动态变量。

innodb_log_file_size={108576 .. 4294967295}
设定日志组中每个日志文件的大小，单位是字节，默认值是5MB。较为明智的取值范围是从1MB到缓存池体积的1/n，其中n表示日志组中日志文件的个数。日志文件越大，在缓存池中需要执行的检查点刷写操作就越少，这意味着所需的I/O操作也就越少，然而这也会导致较慢的故障恢复速度。作用范围为全局级别，可用于选项文件，属非动态变量。

innodb_log_files_in_group={2 .. 100}
设定日志组中日志文件的个数。InnoDB以循环的方式使用这些日志文件。默认值为2。作用范围为全局级别，可用于选项文件，属非动态变量。

innodb_log_group_home_dir=/PATH/TO/DIR
设定InnoDB重做日志文件的存储目录。在缺省使用InnoDB日志相关的所有变量时，其默认会在数据目录中创建两个大小为5MB的名为ib_logfile0和ib_logfile1的日志文件。作用范围为全局级别，可用于选项文件，属非动态变量。

relay_log=file_name
设定中继日志的文件名称，默认为host_name-relay-bin。也可以使用绝对路径，以指定非数据目录来存储中继日志。作用范围为全局级别，可用于选项文件，属非动态变量。

relay_log_index=file_name
设定中继日志的索引文件名，默认为为数据目录中的host_name-relay-bin.index。作用范围为全局级别，可用于选项文件，属非动态变量。

relay-log-info-file=file_name
设定中继服务用于记录中继信息的文件，默认为数据目录中的relay-log.info。作用范围为全局级别，可用于选项文件，属非动态变量。

relay_log_purge={ON|OFF}
设定对不再需要的中继日志是否自动进行清理。默认值为ON。作用范围为全局级别，可用于选项文件，属动态变量。

relay_log_space_limit=#
设定用于存储所有中继日志文件的可用空间大小。默认为0，表示不限定。最大值取决于系统平台位数。作用范围为全局级别，可用于选项文件，属非动态变量。

slow_query_log={ON|OFF}
设定是否启用慢查询日志。0或OFF表示禁用，1或ON表示启用。日志信息的输出位置取决于log_output变量的定义，如果其值为NONE，则即便slow_query_log为ON，也不会记录任何慢查询信息。作用范围为全局级别，可用于选项文件，属动态变量。

slow_query_log_file=/PATH/TO/SOMEFILE
设定慢查询日志文件的名称。默认为hostname-slow.log，但可以通过--slow_query_log_file选项修改。作用范围为全局级别，可用于选项文件，属动态变量。

sql_log_bin={ON|OFF}
用于控制二进制日志信息是否记录进日志文件。默认为ON，表示启用记录功能。用户可以在会话级别修改此变量的值，但其必须具有SUPER权限。作用范围为全局和会话级别，属动态变量。

sql_log_off={ON|OFF}
用于控制是否禁止将一般查询日志类信息记录进查询日志文件。默认为OFF，表示不禁止记录功能。用户可以在会话级别修改此变量的值，但其必须具有SUPER权限。作用范围为全局和会话级别，属动态变量。

sync_binlog=#
设定多久同步一次二进制日志至磁盘文件中，0表示不同步，任何正数值都表示对二进制每多少次写操作之后同步一次。当autocommit的值为1时，每条语句的执行都会引起二进制日志同步，否则，每个事务的提交会引起二进制日志同步。

 

备份和恢复

为什么要备份？

灾难恢复：硬件故障、软件故障、自然灾害、黑客攻击；误操作；
测试：

要注意的要点：
可容忍丢失多少数据；
恢复需要在多长时间内完成；
需要恢复什么；

备份类型：
完全备份，部分备份：
部分备份：仅备份其中的一张表或多张表；

完全备份，增量备份，差异备份
增量备份：仅备份从上次完全备份或增量备份之后变化的数据部分；

热备份、温备份和冷备份：
热备份：在线备份，读写操作不受影响；
温备份：在线备份，读操作可继续进行，但写操作不允许；
冷备份：离线备份，数据库服务器离线，备份期间不能为业务提供读写服务；

MyISAM: 温备
InnoDB: 热备

物理备份和逻辑备份：
物理备份：直接复制数据文件进行的备份；
逻辑备份：从数据库中“导出”数据另存而进行的备份；

逻辑备份：
与存储引擎无关

规则备份时需要考虑的因素：
持锁的时长
备份过程时长
备份负载
恢复过程时长

备份什么以？
数据、额外的数据（二进制日志和InnoDB的事务日志）、代码（存储过程和存储函数、触发器、事件调度器等）、服务器配置文件

设计备份方案：
完全备份+增量备份

备份工具：
mysqldump: 逻辑备份工具，适用于所有存储引擎，温备；完全备份，部分备份；对InnoDB存储引擎支持热备；
cp, tar等文件系统工具：物理备份工具，适用于所有存储引擎；冷备；完全备份，部分备份；
lvm2的快照：几乎热备；借助于文件系统工具实现物理备份；
mysqlhotcopy: 几乎冷备；仅适用于MyISAM存储引擎；

备份方案之备份工具的选择：
mysqldump+binlog: mysqldump：完全备份，通过备份二进制日志实现增量备份；
lvm2快照+binlog：几乎热备，物理备份
xtrabackup:
对InnoDB：热备，支持完全备份和增量备份
对MyISAM引擎：温备，只支持完全备份

回顾：
mysql> SHOW {BINARY|MASTER} LOGS;
mysql> SHOW BINLOG EVENTS IN 'mysql-bin.xxxxxx' FROM position;

# mysqlbinlog
--start-position
--stop-position

--start-datetime
--stop-datetime

客户端程序：-u,-h,-p

逻辑备份：mysqldump, mydumper, phpMyAdmin

Schema和数据存储一起、巨大的SQL语句、单个巨大的备份文件

mysqldump: 客户端，通过mysql协议连接至mysqld；

-A, --all-databases

MyISAM, InnoDB: 温备
-x, --lock-all-tables：锁定所有表
-l, --lock-tables：锁定备份的表

InnoDB:
--single-transaction：启动一个大的单一事务实现备份

-B, --databases db_name1 db_name2 ...：备份指定的数据库

-C, --compress：压缩传输；

命令的语法格式：
mysqldump [OPTIONS] database [tables]：备份单个库，或库指定的一个或多个表
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]：备份一个或多个库
mysqldump [OPTIONS] --all-databases [OPTIONS]：备份所有库

其它选项：

-E, --events：备份指定库的事件调度器；
-R, --routines：备份存储过程和存储函数；
--triggers：备份触发器

--master-data[=#]：
1：记录CHANGE MASTER TO语句；此语句未被注释；
2：记录为注释语句；

--flush-logs, -F：锁定表之后执行flush logs命令；

注意：二进制日志文件与数据文件不应该放置于同一磁盘；为什么？

使用案例：每周完全，每日增量
hellodb单个库：

作业：写脚本, 用mysqldump实现对指定数据库的备份；
写脚本，实现利用binary logs完成增量备份；

物理备份：数据文件的时间一致性？
冷备：
几乎热备：lvm2快照

1、请求锁定所有表：
mysql> FLUSH TABLES WITH READ LOCK;

2、记录二进制日志文件及事件位置：
mysql> SHOW MASTER STATUS;

3、创建快照：
lvcreate -L SIZE -s -p r -n NAME /dev/VG_NAME/LV_NAME

4、释放锁：
mysql> UNLOCK TABLES

5、挂载快照卷，复制数据进行备份；
cp, rsync, tar等命令复制数据；

6、备份完成之后，删除快照卷；

思考？mysqldump做的备份，数据还原时，是否会产生二进制日志？
mysql> SET SESSION sql_log_bin=0;
mysql> SOURCE /path/from/somefile.sql;
mysql> SET SESSION sql_log_bin=1;

Xtrabackup：
percona, www.percona.com

innobackupex: 客户端工具, 以mysql协议连入mysqld，不支持离线备份

--user=
--password=

完整备份策略：完整备份 + 增量备份 + 二进制日志

注意：
1、将数据和二进制文件放置于不同的设备；二进制日志也应该周期性地备份；
2、将数据和备份分开存放，建议不在同一设备、同一主机、同一机房、同一地域；
3、每次灾难恢复后都应该立即做一次完全备份；
4、备份后的数据应该周期性地做还原测试；

从备份中恢复应该遵循的步骤：
1、停止MySQL服务器；
2、记录服务器配置和文件权限；
3、将备份恢复到MySQL数据目录；此步骤依赖具体的备份工具；
4、改变配置和文件权限；
5、以限制方式启动MySQL服务器：比如通过网络访问；
[mysqld]
skip-networking
socket=/tmp/mysql-recovery.sock
6、载入额外的逻辑备份；而检查和重放二进制日志；
7、检查已经还原的数据；
8、以完全访问模式重启服务器；

其它的备份工具：
SELECT ... INTO OUTFILE ''
LOAD FILE

mylvmbackup

 

回顾：备份、恢复

备份方式：
mysqldump
lvm snapshot
xtrabackup

备份策略：10G
xtrabackup：全备+增备
mysqldump：全备+增备（二进制日志）

复制：

扩展的方式：
Scale up
Scale Out

复制：
向外扩展
二进制日志
单向

复制功用：
数据分布
负载均衡：读操作，适用于读密集型的应用
备份
高可用和故障切换
MySQL升级测试

主从复制：
从服务器：
I/O线程：从master请求二进制日志信息，并保存至中继日志；
SQL线程：从relay log中读取日志信息，在本地完成重放；

异步模式：async
1、从服务器落后于主服务器；
2、主从数据不一致；

二进制日志的格式：SET datetime = now()
1、基于行
2、基于语句
3、混合

双主：
读：负载均衡
写：无法均衡

Perlcona-Galera Cluster

配置过程：
1、master
(1) 启用二进制日志；
(2) 设置一个在当前集群中惟一的server-id；
(3) 创建一个有复制权限(REPLICATION SLAVE, REPLICATION CLIENT)账号；

2、slave
(1) 启用中继日志；
(2) 设置一个在当前集群中惟一的server-id；
(3) 使用有复制权限用户账号连接至主服务器，并启动复制线程；

传统上：一主多从，但一从一主；
而今：一从多主；

服务器程序版本：最好相同；从高；

总结：
master: binlog dump
slave: IO thread, SQL thread

slave: 如果连接至主服务器：
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)

主主：
互为主从：
1、数据不一致；
2、自动增长id
定义一个节点使用奇数id
auto_increment_offset=1
auto_increment_increment=2
定义另一个节点使用偶数id
auto_increment_offset=2
auto_increment_increment=2

(1) 各自使用不同的server id
(2) 都启用binlog和relay log
(3) 定义自动增长的id字段的增长方式
(4) 都授权有复制权限的用户账号
(5) 各自把对方指定为主服务器

GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'172.16.%.%' IDENTIFIED BY 'replpass';

复制如何开始？
主节点运行很长时间，且已经有一定规模的数据，如何启动复制？

在主节点做一个完全备份，并记录二进制日志文件及位置；
在从节点恢复此完全备份，并在启动复制时从记录的二进制日志文件和位置开始；

复制时应该注意的问题：
1、如何限制从服务器只读？
在从服务器启动read_only；但仅对非具有SUPER权限的用户有效；

阻止所有用户 ：MariaDB> FLUSH TABLES WITH READ LOCK;

2、如何保证主从复制时的事务安全？
在master节点启用参数：
sync_binlog = on

如果用到的为InnoDB存储引擎：
innodb_flush_logs_at_trx_commit
innodb_support_xa=on

在slave节点：
skip_slave_start

主节点：
sync_master_info = 1

从节点
sync_relay_log = 1
sync_relay_log_info = 1

3、半同步复制
主节点：
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.05 sec)

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | OFF |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
4 rows in set (0.00 sec)

MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_timeout=2000;
Query OK, 0 rows affected (0.00 sec)


从节点：
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.05 sec)

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)

MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)

如果从服务器已经启动，还需要重启IO_THREAD;

4、复制过滤器
让slave仅复制有限的几个数据库，而非所有；

有两种实现思路：

(1) 主服务器仅向二进制日志中记录有特定数据库相关的写操作；
问题：即时点还原将无法全面实现；

binlog_do_db= # 数据库白名单
binlog_ignore_db= # 数据库黑名单

(2)从服务器的SQL_THREAD仅在中断日志中读取特定数据相关的语句并应用在本地；
问题：会造成网络带宽和磁盘IO的浪费；

Replicate_Do_DB=
Replicate_Ignore_DB=
Replicate_Do_Table=
Replicate_Ignore_Table=
Replicate_Wild_Do_Table=
Replicate_Wild_Ignore_Table=

5、基于SSL的复制
前提：支持SSL
(1) 主服务器端配置证书和私钥，并创建一个要求必须使用SSL连接的复制账号(REQUIRE SSL);
(2) SLAV端连接master时，使用MASTER_SSL相关的选项来配置证书等信息；

6、跟复制功能相关的文件：
master.info：用于保存slave连接至master时的相关信息；
relay-log.info：保存了当前slave节点上已经复制的当前二进制日志和本地relay log日志对应关系；

7、复制的监控和维护
(1) 清理日志：PURGE

(2) 复制监控
SHOW MASTER STATUS
SHOW BINLOG EVENTS
SHOW BINARY LOGS

SHOW SLAVE STATUS

(3) 如何判断slave是否落后于master
Seconds_Behind_Master: 0

(4) 如何确定主从节点数据是否一致？
通过表自身的CHECKSUM检查
使用percona-tools中pt-table-checksum

(5) 数据不一致的修复方法：
重复复制；

回顾：
replication：横向扩展
主从、主主

master/slave
master: 写
slave：只读

复制形式：
row
statement
mixed

master: binlog dump
slave: IO_THREAD, SQL_THREAD

半同步复制：
INSTALL PLUGIN

博客作业：
主从、主主、半同步、SSL复制、过滤器

复制(2)

由于写操作只能发往主节点
通常读操作都发往从节点

读写分离器：
mysql-proxy (C++) --> Qihoo (atlas)
amoeba (java)
dbrelay

sharding framework 2.2.1-2.el6
cobar
gizzard

mysql-proxy:
epel源：yum -y install mysql-proxy
使用官方二进制格式文件中的rw-splitting.lua脚本进行读写分离

配置文件：/etc/mysql-proxy.cnf
[mysql-proxy]
daemon = true
pid-file = /var/run/mysql-proxy.pid
log-file = /var/log/mysql-proxy.log
log-level = debug
max-open-files = 1024
plugins = admin,proxy
user = mysql-proxy
#
#Proxy Configuration
proxy-address = 0.0.0.0:3306 #代理服务监听的地址和端口
proxy-backend-addresses = 172.16.100.7:3306 #支持读写操作的后端mysql
proxy-read-only-backend-addresses = 172.16.100.8:3306 # 只读后端
#proxy-lua-script =
proxy-lua-script = /usr/lib64/mysql-proxy/lua/rw-splitting.lua #读写分离脚本
#proxy-skip-profiling = true
#
# Admin Configuration
admin-address = 0.0.0.0:4041
admin-lua-script = /usr/lib64/mysql-proxy/lua/admin.lua
admin-username = admin
admin-password = admin

启动服务：/etc/init.d/mysql-proxy

amoeba：
先配置java环境

配置文件：
dbServers.xml
amoeba.xml

http://docs.hexnova.com/amoeba/

mmm, percona-Galera cluster

读写分离：mysql-proxy, amoeba
mmm: MySQL Master-Master Replication Manager

复制的问题和解决方案：
(1) 数据损坏或丢失
从服务器：master.info

(2) 混合使用存储引擎

(3) 不唯一的server id

(4) 复制延迟

切分：sharding
垂直切分：按表进行切分
水平切分：按表中的行进行的切分

MySQL数据库的向外扩展方式：复制、拆分、sharding

拆分方式：
按功能拆分：也即按应用拆分；
数据分片：
选择切分键
把数据存储在哪里？
从哪儿读数据？

数据库切分框架：
cobar
gizzard
Hibernat Shards
HiveDB

qps, tps

重点：备份/恢复（备份策略，备份脚本，备份恢复测试）、复制（主从、读写分离、主主、MMM或MHA）