第22章，mysql数据库-2
本章内容：
MYSQL 用户和权限管理
服务器变量和SQL_MODE
查询缓存
索引
并发控制和事务
mysql日志功能
mysql备份和恢复
主从复制和高可用
压力测试
mysql生产环境配置实例
MYSQL 用户和权限管理：--------------------------------------------------------
元数据数据库：mysql
系统授权表：
db，host，user
columns_priv,tables_priv,procs_priv,proxies_priv
用户创建：
CREATE USER 'user_name'@'host' [IDENTIFIED BY 'password']
默认权限：USAGE
用户重命名：
RENAME USER old_user_name TO new_name
删除用户：
DROP USER 'USERNAME'@'HOST'
修改密码：
1 SET PASSWORD FOR 'username'@'host'=PASSWORD('password')
即时生效，=前后带不带' '都行
2 UPDATE mysql.user SET password=PASSWORD('password') WHERE ...;
需要刷新权限之后生效
FLUSH PRIVILEGES;
3 #mysqladmin -u root -p'oldpassword' password 'newpass'
shell 命令行修改管理员密码
忘记管理员密码的解决办法：
1 vim /etc/my.cnf
[mysqld]
skip-grant-tables=ON
skip-networking=ON
2 systemctl restart mysqld
mysql
UPDATE mysql.user SET password=PASSWORD('password') WHERE user='root';
FLUSH PRIVILEGES;
EXIT
3 vim /etc/my.cnf
删除skip-grant-tables=ON skip-networking=ON
重启服务
4 使用新密码登录root
MYSQL 权限管理：
权限类别：
管理类：
CREATE TEMPORARY TABLES
CREATE USER
FILE
SUPER
SHOW DATABASES
RELOAD
SHUTDOWN
REPLICATION SLAVE
REPLICATION CLIENT
LOCK TABLES
程序类：FUNCTION,PROCEDURE,TRIGGER
CREATE
ALTER
DROP
EXCUTE
库和表级别：DATABASE,TABLE
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
授权：GRANT
授权：
GRANT priv_type[(column_list)].. ON [object_type] priv_level
TO 'user'@'host'
[IDENTIFIED BY PASSWORD('password')] [WITH GRANT OPTION];
选项介绍：
priv_type: ALL[PRIVILEGES]
object_type: TABLE|FUNCTION |PROCEDURE
priv_level: *（所有库）|*.*|db_name.*|db_name.tb_name|
tb_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
示例：
GRANT SELECT,INSERT,UPDATE(stuid,name,age) ON hellodb.students TO 'wang'@'%';
GRANT ALL ON hellodb.students TO user1@'%' IDENTIFIED BY PASSWORD('centos');
回收权限：
REVOKE priv_type[(column_list)][,priv_type(col_list)]...
ON [object_type] priv_level FROM 'user'@'host';
示例：
REVOKE ALL ON laa.* FROM 'wang'@'%'
查看指定用户获得的授权：
SHOW GRANTS FOR 'user'@'host';
SHOW GRANTS FOR CURRENT_USER[()];
注意：
1 mariaDB服务进程启动时会读取mysql库中所有授权表至内存
2 GRANT 或 REVOKE 等执行权限操作会保存于系统表中，mariadb的服务进程
通常会自动重读授权表，使之生效
3 对于不能够或不能及时重读授权表的命令，可手动让mariadb的服务进程重读授权表
FLUSH PRIVILEGES;
MYSQL中的系统数据库：
mysql数据库：
是mysql的核心数据库，类似于sql server中的master库，主要负责存储数据库
的用户，权限设置，关键字等mysql自己需要使用的控制和管理信息
performance_schema:
MYSQL5.5开始新增的数据库，主要用于收集数据库服务器性能参数，库里表
的存储引擎均为PERFORMANCE_SCHEMA,用户不能创建存储引擎为PERFORMANCE_SCHEMA的表
information_schema:
MYSQL5.0之后产生的，一个虚拟数据库，物理上并不存在。information_schema
数据库类似与’数据字典‘，提供了访问数据库元数据的方式，即数据的数据。
比如数据库名或表名，列类型，访问权限更加细化的访问方式）
服务器配置：
mysqld选项，服务器系统变量和服务器状态变量
官方文档地址
https://dev.mysql.com/doc/refman/5.7/en/server-option-variable-reference.html
https://mariadb.com/kb/en/library/full-list-of-mariadb-options-system-and-status-variables/
注意： 其中有些参数支持运行时修改，会立即生效
有些参数不支持，且只能通过修改配置文件，并重启服务器程序生效
有些参数作用域是全局的，且不可改变，有些可以为每个用户提供单独设置（会话级别）
获取mysqld的可用选项列表：
mysqld --help --verbose
mysqld --print-defaults 当前默认服务器配置
服务器系统变量：分全局和会话两种
服务器状态变量：分全局和会话两种
获取运行中的mysql进程使用各服务器参数及其值：
SHOW GLOBAL VARIABLES [LIKE ''];
SHOW [SESSION] VARIABLES [LIKE ''];
设置服务器系统变量的三种方法：
1 在shell命令行设置：
./mysqld_safe --skip-name-resolve=1
2 在配置文件my.cnf中设置
skip_name_resolve=1
3 在mysql客户端使用SET命令
SET [GLOBAL] sql_log_bin=0;
修改服务器变量的值：
help SET;
修改全局变量：仅对修改后心创建的会话有效，对已经建立的会话无效
SET GLOBAL system_var_name=value;
SET @@.global.system_var_name=value;
修改会话变量：
SET [SESSION] system_var_name=value;
SET @@[session.]system_var_name=value;
状态变量（只读）：用于保存mysql运行中的统计数据的变量，不可更改
SHOW GLOBAL STATUS;
SHOW [SESSION] STATUS;
服务器变量SQL_MODE
SQL_MODE：对其设置可以完成一些约束检查的工作，可分别进行全局的设置或当前
会话的设置，
参看：https://mariadb.com/kb/en/library/sql-mode/
如设置：
SET SET SQL_MODE='traditional'
常见mode：
NO_AUTO_CREATE_USER
禁止GRANT创建密码为空的用户
NO_AUTO_VALUE_ON_ZERO
在自增长的列中插入0或NULL将不会是下一个自增长值
NO_BACKSLASH_ESCAPES
反斜杠'\'作为普通字符而非转义字符
PAD_CHAR_TO_FULL_LENGTH
启用后，对于CHAR类型将不会截断空洞数据
PIPES_AS_CONCAT
将"||"视为连接操作符而非"或运算符
查询缓存：---------------------------------------------------------------------
查询指令的执行过程：
客户端指令 ---> mysql server ---> 查询缓存，如果有匹配直接返回结果
如果没有匹配项
---> 解析器 --- 生成解析树 ---> 预处理器 ---> 解析树
---> 查询优化器，有可能改写查询语句 ---> 生成查询执行计划
---> 查询执行引擎，执行指令 ---> API调用 ---> 存储引擎 ---> 读取数据
---> 返回查询结果，并且将结果放入查询缓存之中
查询缓存（query cache）原理：
缓存SELECT操作或预处理查询的结果集和SQL语句，当有新的SELECT语句或预处理查询
语句请求，先去查询缓存，判断是否存在可用的记录集
判断标准：
与缓存的SQL语句，是否完全一致，区分大小写
优缺点：
1 不需要对SQL语句做任何解析和执行，语法解析通过之后，直接从query cache中
获得查询结构，提高查询性能
2 查询缓存的判断规则，不够智能，也即提高了查询缓存的使用门坎，降低了效率
3 查询缓存的使用，会增加检查和清理query cache中记录集的开销
哪些查询可能不会被缓存：
1 查询语句中加了SQL_NO_CACHE 参数
2 查询语句中含有获得值的函数，包含自定义函数，如：now(),curdate(),
get_lock(),rand(),convert_tz(),等
3 对系统数据库的查询：mysql、information_schema 查询语句中使用了SESSION级别变量
或存储过程中的局部变量
4 查询语句中使用了LOCK IN SHARE MODE,FOR UPDATE的语句，查询语句中使用类似
SELECT..INTO..导出数据的语句
5 对临时表的查询操作，存在警告信息的查询语句，不涉及任何表或视图的查询语句；
某用户只有列级别权限的查询语句
6 事务隔离级别为serializable时，所有查询语句都不能缓存
查询缓存相关的服务器变量：
查看有关query变量:
SHOW VARIABLES LIKE '%query%';
query_cache_min_res_unit:
查询缓存中内存块的最小分配单位，默认4k
较小值会减少浪费，但会导致更频繁的内存分配操作
较大值会带来浪费，会导致碎片过多，内存不足
query_cache_limit:
单个查询结果能缓存的最大值，默认为1M
对于查询结构过大而无法缓存的语句，建议使用SQL_NO_CACHE
query_cache_size:
查询缓存总共可用的内存空间；单位字节，必须是1024的整数倍
最小值为40K，低于此值有警报
不过mariadb10.2.15貌似没有警报
query_cache_wlock_invalidate:
如果某表被其他的会话锁定，是否仍然可以从查询缓存中返回结果
默认值为OFF，表示可以在表被其他会话锁定的场景中继续从缓存中返回数据
ON，则表示不允许
query_cache_type:
是否开启缓存功能，取值为ON，OFF,DEMAND
0,OFF时，查询缓存功能关闭
1,ON时，默认值。查询缓存功能打开，SELECT的结果符合缓存条件即会缓存
否则不缓存
显示指定SQL_NO_CACHE时，不缓存
2,DEMAND 只有SQL_CACHE的SELECT语句才会缓存，其他均不缓存
查询缓存相关的状态变量：
查看方式：
SHOW GLOBAL STATUS LIKE 'Qcache%';
Qcache_free_blocks 1
处于空闲状态query cahe中内存block数
Qcache_free_memory 20460888
处于空闲状态的query cache 内存总量
Qcache_hits 17
命中次数
Qcache_inserts 5
向query cache中插入新的query cashe的次数，即没有命中次数
Qcache_lowmem_prunes 0
当前query cache内存容量不够，需要删除旧的query cache 的次数
Qcache_not_cached 13
没有被cache的SQL数，包括无法被cache的以及由于query_cache_type设置的
不会被cache的SQL语句
Qcache_queries_in_cache 1
在query cache中的SQL数量
Qcache_total_blocks 4
query cache中的总block
命中率和内存使用率估算：
1 查询缓存中内存块的最小分配单位query_cache_min_res_unit ：
(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
2 查询缓存命中率 ：Qcache_hits / ( Qcache_hits + Qcache_inserts ) * 100%
3 查询缓存内存使用率：(query_cache_size – qcache_free_memory) /
query_cache_size * 100%
innodb存储引擎的缓冲池：
通常innodb存储引擎缓冲池的命中不应该小于99%
查看innodb相关状态变量：
SHOW GLOBAL STATUS LIKE 'innodb%read%';
Innodb_buffer_pool_reads: 表示从物理磁盘读取页的次数
Innodb_buffer_pool_read_ahead: 预读的次数
Innodb_buffer_pool_read_ahead_evicted: 预读页，但是没有读取就从缓
冲池中被替换的页数量，一般用来判断预读的效率
Innodb_buffer_pool_read_requests: 从缓冲池中读取页次数
Innodb_data_read: 总共读入的字节数
Innodb_data_reads: 发起读取请求的次数，每次读取可能需要读取多个页
优化查询缓存：
1 缓存命中率是否可以接受
2 如缓存命中率可以接受结束，如不能，检查大部分查询是否都不是可缓存的
3 如都是不可缓存，检查query_cache_limit是否足够大，如果是，结束，查询本是
不能被缓存。如果否，增加query_cache_limit
4 如果都是可缓存的。检查是否发生了很多验证工作
如果否，检查缓存是否被启动，没有启动则启动，已启动则结束
如果是，执行5
5 检查缓存是否被碎片化，
如果是，则减少query_cache_res_unit，或者使用FLUSH QUERY CACHE命令减少碎片
如果否，执行6
6 是否有很多因为内存过低而发生的修整工作，即缓存是否频繁被丢弃
如果是，增加query_cache_size。
如果否，执行7
7 是否有很多更新语句。如果是，结束，负载不适合缓存。
如果否，有其他的东西被配置错了，重新检查配置
索引：-------------------------------------------------------------------------
索引是特殊数据结构：定义在查找时作为查找条件的字段
索引实现在存储引擎
优点：
索引可以降低服务需要扫描的数据量，减少io次数
索引可以帮助服务器避免排序和使用临时表
索引可以帮助将随机I/O转为顺序I/O
缺点：
占用额外空间，影响插入速度
索引类型：
B+TREE、HASH、 R TREE
聚簇索引，非聚簇索引：
索引中数据的顺序是否和原表数据一致
主键索引、二级（辅助）索引
稠密索引、稀疏索引：
是否索引了每一个数据项
简单索引、组合索引
左前缀索引：取前面的字符做索引
覆盖索引：从索引中即可取出要查询的数据，性能高
B+ tree索引：
顺序存储，每一个叶子节点到根节点的距离是相同的
左前缀索引，适合查询范围类数据
可以使用B+tree索引的查询类型：
全值匹配：精确所有索引列
匹配最左前缀：即只使用索引的第一列
匹配列前缀：只匹配一列值开头部分，
匹配范围值：
精确匹配某一列并范围匹配另一列：
只访问索引的查询：
限制：
如果不能从最左列开始，则无法使用索引
不能跳过索引中的列、
如果查询中某个列是为范围查询，那么其右侧的列都无法再使用索引
特别提示：
索引列的顺序和查询语句的写法应相匹配，才能更好的利用索引
为优化性能，可能需要针对相同的列但顺序不同创建不同的索引来满足不同
类型的查询需求
Hash索引：
基于哈希表实现，只有精确匹配索引中的所有列的查询才有效，索引自身只存储索引列
对应的哈希值和数据指针，索引结构紧凑，查询性能好
只有Memory存储引擎支持显示hash索引
适用场景：
只支持等值比较的查询，包括=，<=>,IN()
不适用使用hash索引的场景：
不适用于顺序查询：索引的存储顺序不是值的顺序
不支持模糊查询
不支持范围查询
不支持范围查询
不支持部分索引列匹配查找，
空间索引：
R-tree：MyISAM支持空间索引，可是使用任意维度组合查询，使用特有的函数访问
常用于做地理数据存储，使用不多
全文索引：FULLTEXT
在文本中查找关键词，而不是直接比较索引中的值，类似搜索引擎
聚簇和非聚簇索引：
聚簇索引：
生成一份原表数据副本，并且将生成的数据副本按照所选列从新排序
由于数据和索引在一起，查找时找到索引项，即可直接找到数据
非聚簇索引：
索引和数据不放在一起，且存放的顺序不一致
查找时找到索引项，之后通过索引项中指针记录的地址找到数据
冗余和重复索引：
冗余索引：(A),(A,B)
此为不好的索引使用策略，建议扩展索引，而非冗余
（B），（A,B）
此为非冗余
重复索引：已经有索引，再次建立索引
索引优化策略：
1 独立的使用列：
尽量避免其参与运算，独立的列指索引列不能是表达式的一部分，也不能是函数的
参数，在WHERE语句中，始终将索引列单独放在比较符号的一侧
2 左前缀索引：
构建指定索引字段的左侧的字符数，用通过索引选择性来评估
索引选择性：不重复的索引值和数据表的记录总数的比值
3 多了索引：
AND操作时更适合使用多列索引，而非每个列创建单独的索引
4 选择合适的索引列顺序：无排序和分组时，将选择性最高放左侧
索引优化建议：
1 只要列中含有NULL值，就最好不要再此列设置索引
符合索引如果有NULL值，此列在使用时也不会使用索引
2 尽量使用短索引，如果可以，应该制定一个前缀长度
3 对于经常在where子句使用的列，最好设置索引
4 对于有多个列where或者order by子句，应该建立复合索引
5 对于like语句，以'%'或者'_'开头的不会使用索引，以‘%’结尾会使用索引
6 尽量不要再列上进行运算（函数操作和表达式操作）
7 尽量不要使用 not in 和<>操作
8 查询时，能不要*就不用*，尽量写全字段名
9 大部分情况连接效率远大于子查询
10 多表连接时，尽量小表驱动大表，即小表join 大表
11 在千万级分页时使用limit
12 对于经常使用的查询，可以开启缓存
13 多使用explain和profile分析查询语句
14 查看慢查询日志，找出执行时间长的sql语句优化
管理索引：
创建索引：HELP CREATE INDEX
CREATE INDEX index_name ON tb_name(index_col,..);
删除索引：
DROP INDEX index_name ON tb_name;
查看索引：
SHOW INDEX FROM tb_name;
优化表空间：
OPTIMIZE TABLE tb_name;
查看索引的使用：
SET GLOBAL userstat=1；
SHOW INDEX_STATISTICS;
EXPLAIN:
通过EXPLAIN来分析索引的有效性：
EXPLAIN SELECT clause；
获取查询执行计划信息，用来查看查询优化器如何执行查询
输出信息说明：
官方文档地址：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
示例：
MariaDB [hellodb]> EXPLAIN SELECT * FROM students WHERE stuid=15\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: students
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra:
1 row in set (0.00 sec)
id：当前查询语句中，每个SELECT语句的编号
复杂类型的查询有三种：
简单子查询
用于FROM中的子查询
联合查询：UNION
注意：UNION 查询的分析结构会出现一个额外匿名临时表
select_type:
简单查询为:SIMPLE
复杂查询：
SUBQUERY:简单子查询
PRIMARY:最外面的SELECT
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：对结果使用一个外部索引排序
并发控制、事务：------------------------------------------------------------------------
并发控制：
锁粒度： 表级锁，行级锁
锁：
读锁：共享锁，只读不可写，多个读互不阻塞
写锁：独占锁，排它锁，一个写锁会阻塞其他读和写锁
实现：
存储引擎：自行实现其锁策略和锁粒度
服务器级：实现了锁，表级锁；用户可显示请求
分类：
隐式锁：由存储引擎自动施加锁
显式锁：用户手动请求
锁策略：
在锁粒度及数据安全性寻求的平衡机制
显示使用锁：
锁定：
LOCK TABLES tb_name[[AS] alias] lock_type
[,tb_name [[AS] alias] lock_type]...
lock_type READ,WRITE
解锁：
UNLOCK TABLES;
全局读锁：
通常在备份前加全局读锁，关闭正在打开的表（清除查询缓存）
FLUSH TABLES [tb_name[,...]] [WITH READ LOCK]
查询时加写锁或读锁：
SELECT clause [FOR UPDATE|LOCK IN SHARE MODE]
事务：------------------
事务TRANSACTIONS:一组原子性的SQL语句，或一个独立工作单元
事务日志：记录事务信息，实现undo，redo等故障恢复功能
ACID特性:
A: atomicity,原子性，整个事务中的所有操作要么全部成功执行，要么
全部失败后回滚
B： consisitency，一致性，数据库总是从一个一致性状态转换为另一个一致性状态
I： isolation，隔离性，一个事务所做出的操作在提交之前，是不能为其他事务所见
隔离有多种隔离级别，实现并发
D： durability，持久性，一旦事务提交，其所做的修改会永久保存于数据库中
启动事务：
START TRANSACTION
结束事务：
1 COMMIT 提交
2 ROLLBACK 回滚
事务的生存周期：
initial db state ---> START TRANSACTION ---> INSERT,UPDATE
---> COMMIT ---> new db state
---> ROLLBACK ---> initial db state
注意：只有事务型存储引擎的DML语句方能支持此类操作
即 INSERT,UPDATE,DELETE
其他语句如CREATE,DROP,ALTER 均不支持回滚
建议：显示请求和提交事务，而不要使用"自动提交" 功能
SET autocommit={1|0};
事务支持保存点：SAVEPOINT
SAVEPOINT P1;
DELETE|UPDATE|INSERT...
SAVEPOINT P2;
DELETE...
ROLLBACK [WORK] [TO [SAVEPOINT] p1|p2] ;
事务的隔离级别：
从上至下更加严格：
READ UNCOMMITTED: 可读取到未提交数据，产生脏读
READ COMMITED: 可读取到提交数据，但未提交数据不可读，产生不可重复读
即可读取到多个提交数据，导致每次读取数据不一致
REPEATABLE READ: 事务开始之后产生的数据改变将无法读取，始终保持事务开始
之前的数据状态。可重复读，多次读取数据一致
会产生幻读，即使有其他提交的事务修改数据，扔能读取到未修改
前的旧数据。
此为mysql默认设置。
SERIALIZABLE: 可串行化，未提交的读事务阻塞修改事务，或者未提交的修改事务
阻塞读事务。导致并发性能差。
MVCC： 多版本并发控制，和事务级别相关
指定事务隔离级别：
服务器变量tx_isolation指定
默认为REPEATABLE-READ,可在GLOBAL和SESSION 级别进行设置
SET [GLOBAL] tx_isolation=
READ-UNCOMMITTED
READ-COMMITTED
REPEATABLE-READ
SERIALIZABLE
服务器选项中指定:
vim /etc/my.cnf
[mysqld]
transaction-isolation=SERIALIZABLE|..
死锁：
两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态
mysql日志：---------------------------------------------------------------------
日志类型：
事务日志： transaction log
中继日志： reley log
错误日志： error log
通用日志： general log
慢查询日志：slow query log
二进制日志：binary log
事务日志：
事务型存储引擎自行管理和使用，用于系统崩溃之后的数据恢复。
建议和数据文件分开存放
redo log
undo log
事务日志的写入类型为"追加"，因此其操作为“顺序IO”;通常也被称为：
预写入日志：write ahead logging
ib_logfile0,ib_logfile1
innodb事务日志相关配置：
SHOW VARIABLES LIKE '%innodb_log%';
innodb_log_file_size 50331648 大小，默认5M
innodb_log_files_in_group 2 数量，默认2个
innodb_log_group_home_dir ./ 默认数目录下
中继日志：
relay log
主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件
错误日志：
记录内容：
mysqld 启动和关闭过程中输出的事件信息
mysqld 运行中产生的错误信息
event scheduler运行一个event时产生的日志信息
在主从复制架构中的从服务器上启动从服务器线程时产生的信息
错误日志相关配置：
SHOW GLOBAL VARIABLES LIKE 'log_error';
错误文件路径：
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
是否记录警告信息至错误日志文件：
log_warnings=1|0|2 10.2.15默认值2
通用日志：
general_log: 记录对数据库的通用操作，包括错误的SQL语句
默认为关闭，如无特殊需求，不要开启
记录位置：
文件：file，默认值
表： table
通用日志相关设置：
general_log=ON|OFF
general_log_file=HOSTNAME.log
log_output='TABLE'|'FILE'|NONE
慢查询日志：
记录执行查询时长超出指定时长的操作
SHOW VARIABLES LIKE '%slow%' | '%query';
slow_query_log=ON|OFF 开启或关闭慢查询
long_query_time=N 慢查询的阀值
slow_query_log_file=HOSTNAME-slow.log 慢查询日志文件
log_slow_filter = admin,filesort,filesort_on_disk,full_join,
full_scan,query_cache,query_cache_miss,tmp_table_on_disk
上述查询类型且查询时长超过long_query_time，则记录日志
log_queries_not_using_indexes=ON
不使用索引或者全索引扫描，不论是否达到慢查询阀值的语句是否记录日志
默认OFF，即不记录
log_slow_rate_limit=1 多少次查询才记录，mariadb特有
log_slow_verbosity= query_plan,explain 记录内容
log_slow_queries = OFF 同为slow_query_log 新版已废弃
二进制日志：binary log
功能：
记录导致数据改变或潜在导致数据改变的SQL语句
记录已提交的日志
不依赖于存储引擎类型
功能: 通过“重放”日志文件中的事件来生成数据副本
特别注意： 二进制文件和数据文件应该分开存放
记录格式：
SHOW VARIABLES LIKE 'binlog_format';
1 基于"语句"记录：statement，记录语句
2 基于"行"记录，row，记录数据，日志量较大
3 混合模式：mixed，让系统自行判定该基于哪种方式进行
格式配置：
SHOW VARIABLES LIKE 'binlog_format';
二进制日志文件的构成：
日志文件：文件名.000001,二进制格式
索引文件：文件名.index，文本格式
二进制日志相关服务器变量：
sql_log_bin=ON|OFF 是否记录二进制日志，默认ON
log_bin=/path/file 指定文件位置，可以使用相对路径，默认OFF，指定则开启二进制日志
binlog_format=STATAMENT|ROW|MIXED:
二进制日志记录的格式，mariadb10.2.15默认mixed
max_binlog_size= 单个二进制日志文件的最大体积，到达最大值会自动滚动，默认1G
sync_binlog=1|0 设置是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘
expire_logs_days=N 二进制日志可以自动删除的天数。默认为0，即不自动删除
二进制日志相关配置：
SHOW MASTER|BINARY LOGS;
查看mariadb自行管理使用中的二进制日志文件列表，及大小
SHOW MASTER STATUS;
查看使用中的二进制日志文件
SHOW BINLOG EVENT [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
查看二进制文件中的指定内容：
例如：
SHOW BINLOG EVENTS IN 'mysql-bin.000032' FROM 574 LIMIT 1,2;
mysqlbinlog:
二进制日志的客户端命令工具
命令格式：
mysqlbinlog [options] log_file... [ > file.sql]
--start-position= 指定开始位置
--stop-position=
--start-datetime=
--stop-datetime=
时间格式YYYY-MM-DD hh:mm:ss
--base64-output[=name]
示例：
mysqlbinlog --start-position=256 --stop-position=2534 mysql-bin.000010
mysqlbinlog --start-datetime="2018-06-07 15:31:30"
--stop-datetime="2018-06-07 16:23:17" mysql-bin.000010
二进制日志事件的格式：
#180614 15:21:43 server id 1 end_log_pos 3976 CRC32 0xa52dcef8 GTID 0-1-304
/*!100001 SET @@session.gtid_seq_no=304*//*!*/;
BEGIN
/*!*/;
# at 3976
#180614 15:21:43 server id 1 end_log_pos 4088 CRC32 0x6149d2a9 Query thread_id=36 exec_time=0 error_code=0
SET TIMESTAMP=1528960903/*!*/;
UPDATE mysql.user SET password=PASSWORD('centos')
/*!*/;
事件发生的日期和时间：180614 15:21:43
事件发生的服务器标识：server id 1
事件的结束位置：end_log_pos 3976
事件的类型：Query
事件发生时所在服务器执行此事件的线程的ID：thread_id=36
语句的时间戳与将其写入二进制文件中的时间差：exec_time=0
错误代码：error_code=0
事件内容：
GTID：Global Transaction ID，mysql5.6以mariadb10以上版本专属属性：GTID
清除指定二进制日志：
PURGE {BINARY|MASTER} LOGS {TO 'log_name'|BEFORE datetime_expr};
示例:
PURGE MASTER LOGS TO mysql-bin.000031;
PURGE MASTER LOGS BEFORE '2018-06-20 12:00:00';
PURGE MASTER LOGS BEFORE '2018-06-21';
删除所有二进制日志：
RESET MASTER [TO #]
日志文件从#开始重新计数，默认1, index文件重新记数
切换二进制日志文件：
FLUSH LOGS;
mysql 备份和恢复：----------------------------------------------------------------
为什么要备份：
灾难恢复
硬件故障，软件故障，自然灾害，××××××，误操作测试等数据丢失场景
备份注意要点：
能容忍最多丢失多少数据
恢复数据需要在多长时间内完成
需要恢复哪些数据
还原要点：
做还原测试，用于测试备份的可用性
还原演练，确保紧急时刻能够熟练可靠的还原数据
备份类型：
完全备份，部分备份
完全备份：备份整个数据集
部分备份：只备份数据子集，如部分数据库
完全备份，增量备份，差异备份
增量备份： 仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据
备份较快，还原复杂
差异备份： 仅备份最近一次完全备份以来变化的数据
备份较慢，消耗更多磁盘空间，还原简单
冷备，温备，热备：
冷备：读写操作均不可进行
温备：读操作可执行，写操作不可执行
热备：读写操作均可执行
MyISAM 只支持温备
InnoDB：都支持
物理备份和逻辑备份：
物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快
逻辑备份：从数据库中"导出"数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度
备份时需要考虑的因素：
温备的持锁多久
备份产生的负载
备份过程的时长
恢复过程的时长
备份什么：
数据
二进制日志、innodb的事务日志
程序代码（存储过程、函数、触发器、事件调度器）
服务器的配置文件
备份工具：
1 cp，tar等复制归档工具：物理备份工具，适用所有存储引擎，只支持冷备；
完全和部分备份
2 LVM的快照：先加锁，做快照后解锁，几乎热备；借助文件系统工具进行备份
3 mysqldump：逻辑备份工具，适用所有存储引擎，温备；支持完全或部分备份；
对InnoDB存储引擎支持热备，结合binlog的增量北风
4 xtrabackup：由percona提供支持，对InnoDB做热备（物理备份）的工具，支持
完全备份，增量备份
5 MariaDB Bachup：从MariaDB 10.1.26开始集成，基于percona xtrabackup2.3.8
6 mysqlbackup：热备份，mysql enterprise edition组件
7 mysqlhotcopy：几乎冷备，仅适用于MyISAM存储引擎
-------------------------------------------------------------------------------
基于LVM实现备份和恢复：
1 准备初始环境
MYSQL数据目录 /data/mydata 挂载逻辑卷 /dev/vg0/data_mysql
MYSQL二进制文件目录 /data/mylog 挂载逻辑卷 /dev/vg0/binlog_mysql
mysql -uroot -pcentos <hellodb_innodb.sql 导入数据库hellodb
CREATE DATABASE db1; 新建数据库db1
GRANT ALL ON db1.* TO 'user1'@'192.168.65.%' IDENTIFIED BY 'centos';
新建一个用户user1，授权user1 对db1所有权限
2 实现完全备份
请求锁定所有表
FLUSH TABLES WITH READ LOCK;
记录二进制日志文件及事件位置
FLUSH LOGS;
SHOW MASTER STATUS;
mysql -uroot -pcentos -e 'SHOW MASTER STATUS' > /data/backup/binlog_point-0
创建快照：
lvcreate -L 5G -s -p r -n snap_mysql /dev/vg0/data_mysql
释放锁：
UNLOCK TABLES;
挂载快照卷，注意，uuid冲突，无法直接挂载
mount -o ro,nouuid,norecovery /dev/vg0/snap_mysql /mnt
复制数据：
mkdir -pv /data/backup/fullbackup1
cp -a /mnt/* /data/backup/fullbackup1/
删除快照卷：
umount /dev/vg0/snap_mysql
lvremove /dev/vg0/snap_mysql
3 实现增量备份
模拟使用过程：
use db1；
CREATE TABLE stu1 SELECT name,age FROM hellodb.students;
use hellodb；
DROP TABLE students;
记录二进制日志文件及事件位置：
FLUSH LOGS;
SHOW MASTER STATUS;
mysql -uroot -pcentos -e 'SHOW MASTER STATUS' > /data/backup/binlog_point-1
导出二进制文件：
cat /data/backup/binlog_point-0
mysqlbinlog --start-position=385 /data/mylog/mysql-bin.000002
> /data/backup/binlog1.sql
如果已记录多个文件，从上一次备份之后到这一次刷新之前的所有日志均需要导出
后续使用 ">>" 追加即可
4 还原测试：
假设后续使用了一段时间
DROP TABLE classes;
CREATE TABLE te1 SELECT * FROM teachers;
然后系统奔溃，数据全部丢失，
这里使用rm -rf /data/mydata/* 模拟
关闭mysqld：
复制完全备份文件：
cp -a /data/backup/fullbackup1/* /data/mydata/
开启mysqld，但是不能对外提供服务，关闭网络服务，或者前端调度器配合
由于需要做二进制日志还原，但是在重放过程中会生成新的打量二进制日志
所以还需要暂时关闭二进制日志功能
vim /etc/my.cnf
[mysqld]
skip_networking
注释掉 log-bin=/data/mylog/mysql-bin
systemctl start mysqld
使用 mysql -uroot -p 连入数据库
（可选步骤，测试完全备份是否恢复）
use db1;
SHOW TABLES; 可以发现db1存在，TABLE db1.stu1 不存在
SHOW GRANTS FOR 'user1'@'192.168.65.%'; 用户user1权限已恢复
use hellodb;SHOW TABLES; students 还在
恢复增量备份：
msyql -uroot -pcentos < /data/backup/binlog1.sql
可能需要恢复多次
（测试是否恢复）
use db1;SHOW TABLES;SELECT * FROM stu1; 已恢复
use hellodb;SHOW TABLES; students表已不存在
但是classes还在，仍然不是最新状态
恢复最近数据：
由于二进制文件和数据分开存储，所以假设最近的二进制文件未损坏
查看应该还原的二进制日志开始位置
cat /data/binlog_point-1
mysql-bin.000003 385
导出二进制日志：
mysqlbinlog --start-position=385 mylog/mysql-bin.000003 >logfile.sql
通过二进制日志文件，恢复最近数据
mysql -uroot -pcentos <logfile.sql
测试查看：
use hellodb；SHOW TABLES;
发现classes表已删除，数据库恢复最近状态
5 重启，恢复服务
vim /etc/my.cnf
删除 skip_networking
取消注释：log-bin=/data/mylog/mysql-bin
重启服务: systemctl restart mysqld
-------------------------------------------------------------------------------
mysqldump 使用：
mysqldump工具：客户端命令，通过mysql协议连接至mysql服务器进行备份
使用格式：
mysqldump [options] database [tables]
不会备份create table
mysqldump [options] -B db1 [db2 db3..]
mysqldump [options] -A [options]
常见选项：
-A,--all-databases 备份所有数据库，含create databases
-B，--databases db_name... 指定备份的数据库，包含create databases语句
-E，--event: 备份相关的所有event scheduler
-R, --routines：备份所有存储过程和自定义函数
--triggers： 备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
--master-data[=#] 此选项需启用二进制日志
会在备份出的数据之前加一条记录 CHANGE MASTER TO 语句
=1 不注释
=2 显示，但注释
此选项会自动关闭--lock-tables功能，自动打开-x|--lock-all-tables功能
除非开启--single-transaction
-F，--flush-logs:备份前滚动日志，锁定完成后，执行flush logs命令，生成
新的二进制日志文件，配合-A或-B选项时，会导致刷新多次数据库
建议在同一时刻执行转储和日志刷新，可通过和--single-transaction
或-x，--master-data一起使用实现，此时只刷新一次日志
--compact 去掉注释，适合调试，生成不使用
-d，--no-data 只备份表结构
-t，--no-create-info 只备份数据，不备份create table
-n，--no-create-db 不备份create database，可被-A或-B覆盖
--flush-privileges 备份mysql或相关时需要使用
-f, --force 忽略SQL错误，继续执行
--hex-blob 使用十六进制符号转储二进制列（例如，“abc”变为0x616263），
受影响的数据类型包括BINARY， VARBINARY，BLOB，BIT
-q, --quick 不缓存查询，直接输出，加快备份速度
MyISAM备份选项：
支持温备；不支持热备，所以必须先锁定要备份的库，而后启动备份操作
锁定方法如下：
-x,--lock -all-tables：加全局读锁，锁定所有库的所有表，同时加--singletransaction或--lock -tables选项会关闭此选项功能
注意：数据量大时，可能会导致长时间无法并发访问数据库
-l,--lock -tables：对于需要备份的每个数据库，在启动备份之前分别锁定其所
有表，默认为on,--skip-lock -tables选项可禁用,对备份MyISAM的多个库,可能
会造成数据不一致
注：以上选项对InnoDB表一样生效，实现温备，但不推荐使用
InnoDB备份选项：
支持热备，可用温备但不建议用
--single-transaction
此选项Innodb中推荐使用，不适用MyISAM
此选项会开始备份前，先执行START TRANSACTION 指令开启事务
此选项通过在单个事务中转储所有表来创建一致的快照。
仅适用于存储在支持多版本控制的存储引擎中的表（目前只有innodb）
转储不保证与其它存储引擎保持一致
在进行单事务转储时，要确保有效的转储文件（正确的表内容和二进制日志位置）
没有其他连接应该使用以下语句：ALTER TABLE,DROP TABLE,RENAME TABLE,TRUNCATE TABLE
此选项和--lock-tables(此选项隐含提交挂起的事务)选项是相互排斥的
备份大型表时，建议将--single-transaction 选项和 --quick选项结合一起使用
缺点：
速度慢，不适合备份大型数据库
----------------------------------------------------------------------------------
使用mysqldump实现备份与恢复：
1 完全备份
mysqldump -A -F --single-transaction --quick --master-data=2
> /data/backup/full.sql
2 修改数据，并获取二进制日志起始位置
数据修改
获取二进制日志起始位置
FLUSH LOGS;
SHOW MASTER LOGS;SHOW MASTER STATUS;
获取FLUSH之前的日志文件为结束位置
less /data/backup/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=385;
通过查看全备份文件，获取起始位置
3 导出二进制日志
mysqlbinlog --start-position=385 /data/mylog/mysql-bin.000004
>/data/backup/log.sql
mysqlbinlog /data/mylog/mysql-bin.000005 >> /data/backup/log.sql
注意除第一个外，后续的日志文件导出均需要使用追加 >>
4 本机恢复过程
关闭服务：
删除数据目录下所有文件： rm -rf /data/mydata/*
vim /etc/my.cnf
添加skip-networking
注释掉log-bin=/data/mylog/mysql-bin
开启服务：
导入数据：
mysql < /data/backup/full.sql
导入二进制日志数据：
mysql < /data/backup/log.sql
修改配置文件，vim /etc/my.cnf
注释skip-networking
取消注释log-bin=/data/mylog/mysql-bin
重启服务
5 备用机恢复过程，假设原服务器崩溃无法启动，且备份数据单独保存，
在备用机上装好mariadb，将备份数据传到新服务器
开启服务并导入数据：
mysql < /data/backup/full.sql
mysql </data/backup/log.sql
将新服务器接入调度器，开始提供服务
注意： 通过yum安装的mariadb5.5.56 在删除数据目录rm -rf /data/mydata/*
之后，重启服务会自动生成mysql数据库
而通过二进制安装的10.2.15则不会自动生成，需要手动生成，使用
./scripts/mysql_install_db --data= ...
误操作删除，如何恢复数据：
主要操作过程跟上面一样
只是在最后利用二进制日志恢复数据时，需要使用
grep DROP TABLES.... /data/backup/log.sql 定位误操作哪一行
然后使用 sed '/.../d' /data/backup/log.sql 删除错误操作
之后按正常流程恢复数据即可
注意：仍然可能会存在问题，因为后续的操作可能基于此项修改做了变动，导致数据不一致
所以操作需谨慎
使用mysqldump实现单表备份和恢复：
以hellodb.students 表为例
1 备份
mysqldump --single-transaction --quick hellodb.students
> /data/stu.sql
mysql -e 'SHOW CREATE TABLE hellodb.students' >/data/stu.create.sql
2 修改create.sql
vim /data/stu.create.sql
将其中重复的create 语句删除
3 恢复数据
mysql> source /data/stu.create.sql
mysql> source /data/stu.sql
3 恢复数据，也可以使用vim分别 打开/data/stu.create.sql 和/data/stu.sql
分别在create 和insert 语句之前加上
use hellodb；
然后在shell命令行恢复数据
mysql > /data/stu.sql
mysql > /data/stu.create.sql
---------------------------------------------------------------------------------
xtrabackup介绍：
percona官网：www.percona.com
xtrabackup:
percona提供的mysql数据库备份工具，唯一开源的能够对innodb和xtradb数据库进行
热备的工具
手册：https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html
特点：
备份还原过程快速、可靠
备份过程不会打断正在执行的事务
能够基于压缩等功能节约磁盘空间和流量
自动实现备份检验
开源，免费
xtrabackup2.2版之前包括4个可执行文件：
innobackupex: perl脚本
xtrabackup： c，c++编译的二进制
xbcrypt： 加解密
xbsteam： 支持并发写的流文件格式
xtrabackup：是用来备份innodb表的
不能备份非innodb表，和mysql server没有交互
innoback： 用来备份非innodb表，同时会调用xtrabackup命令来备份innodb表
还会和mysql server发送命令进行交互，如加全局读锁（FTWRL）、
获取位点（SHOW SLAVE STATUS）等.
即innobackupex 是在xtrabackup之上做了一层封装实现的
虽然目前一般不用 MyISAM表，只是mysql库下系统表时myisam的，因此备份基本
都通过innobackup命令进行
xtrabackup版本变化：
升级到2.4后，相比之前的2.1有了比较大的变化：
innobackup功能全部集成到xtrabackup里面，只有一个binary程序，另外
为了兼容考虑，innobackupex作为xtrabackup的软链接，即xtrabackup现在
支持费innodb表备份，并且innobackupex 在下一版中移除，建议通过xtrabackup
替换innobackupex
xtrabackup安装：
yum install percona-xtrabackup epel源
最新版本下载安装：
https://www.percona.com/downloads/XtraBackup/LATEST/
Xtrabackup用法：
备份：innobackupex [option] BACKUP-ROOT-DIR
选项说明：https://www.percona.com/doc/percona-xtrabackup/LATEST/genindex.html
--user 表示备份账号
--password 表示备份的密码
--host 表示备份数据库的地址
--databases 接受的参数为数据库名，如要指定多个数据库，彼此间需要以
空格隔开，如:"xtra_test dba_test"
同时，指定某数据库时，也可以只指定其中的某张表，如:
"mydatabase.mytable"。
对innodb引擎表无效，还是会备份所有innodb表
--defaults-file 指定从哪个文件读取MYSQL配置，必须放在命令行第一个选项位置
--incremental 表示创建一个增量备份
需要指定 --incremental-basedir
--incremental-basedir 指定为前一次全备份或增量备份的目录，
与--incremental同时使用
--incremental-dir 该选项表示还原时增量备份的目录
--include=name 指定表名，格式 databasename.tablename
Prepare:还原准备
innobackup --apply-log [option] BACKUP-DIR
选项说明：
--apply-log: 一般情况下，在备份完成后，数据尚且不能用于恢复操作，
因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至
数据库中的事务。
因此，此时数据文件仍处于不一致状态。
此选项作用是通过回滚未提交的事务及同步已经提交事务至数据文件，使
数据文件处于一致性状态。
--use-memory：该选项表示和--apply-log选项一起使用，prepare备份的时候，
xtrabackup和crash recovery 分配的内存大小，单位字节。
也可（1MB,1M,1GB,1G），推荐1G
--export 表示开启可导出单独的表之后再导入其他mysql中
--redo-only 在prepare base full backup ，往其中merge 增量备份时候使用
还原：innobackupex --copy-back [option] BACKUP-DIR
innobackupex --move-back [option] [--defaults-group=GROUP-NAME] BACKUP-DIR
选项说明：
--copy-back：做数据恢复时将备份数据文件拷贝到mysql服务器的datadir
--move-back：这个选项与--copy-back相似，唯一的区别是它不拷贝文件，而是
移动文件到目的地，这个选项移除backup文件，用时候必须小心。
使用场景：没有足够的磁盘空间同时保留数据文件和backup副本
还原注意事项：
1 datadir目录必须为空。除非指定
innobackupex --force-non-empty-directories选项，否则--copy-backup选项不会覆盖
2 在restore之前，必须shutdown MYSQL实例，不能将一个运行中的实例restore 到datadir中
3 由于文件属性会被保留，大部分情况下需要在启动实例之前将文件的属主改为
mysql，这些文件将属于创建备份的用户
chown -R mysql:mysql /data/mysql
以上需要在用户调用innobackupex之前完成
--force-non-empty-directories：指定该参数时候，使得innobackupex --copy-backup
或--move-back选项转移文件到非空目录，已存在的文件不会被覆盖。
如果--copy-back和--move-back文件需要从备份目录拷贝一个在datadir已经存在的
文件，会报错失败
备份生成的相关文件：
使用innobackupex备份时，其会调用xtrabackup备份所有的innodb表，复制所有
关于表结构定义的相关文件（.frm）,以及MyISAM,MERGE,CSV和ARCHIVE表的相关文件，
同时还会备份触发器和数据库配置信息相关的文件，这些文件会被保存至一个以时间
命名的目录中，在备份时，innobackupex还会在备份目录中创建如下文件
1 xtrabackup_info: innobackupex工具执行时的相关信息，包括版本，备份选项，
备份时长，备份LSN，BINLOG的位置
2 xtrabackup_checkpoints: 备份类型（如完全或增量），备份状态（如是否已经为
prepare状态），和LSN（日志序列号）范围信息，每个innodb页(通常为16k大小)都会
包含一个日志序列号，即LSN。LSN是整个数据库系统的系统版本号，每个页面相关
的LSN能够表明此页面最近是如何发生改变的。
3 xtrabackup_binlog_info: MYSQL服务器当前正在使用的二进制日志文件及 至备份
这一刻位置二进制日志事件的位置，可利用实现基于binlog的恢复
4 backup-my.cnf: 备份命令用到的配置选项信息
5 xtrabackup_logfile: 备份生成的日志文件
--------------------------------------------------------------------------------------------
使用xtrabackup实现备份和恢复：
一次完全备份和恢复过程：
准备环境；
centos7.5 yum 安装mariadb-server以及xtrabackup
yum install mariadb-server percona-xtrabackup
注意：版本问题可能导致备份出错
实现步骤：
1 备份过程：在原主机
innobackupex --user=root --password=centos /backup
注意： 会在指定备份目录/backup下生成名称为 日期时间 的目录以存放备份数据
/backup 最好为单独磁盘挂载点，数据和备份分开存放，
也可以使用scp 将备份数据传到远程主机
2 还原过程，在目标主机（也可以是原主机）
prepare（准备）数据
innobackupex --apply-log /backup/2018-06-23_10-44-53/
停止服务并清除数据目录
systemctl stop mariadb
rm -rf /var/lib/mysql
恢复数据：
innobackupex --copy-back /backup/2018-06-23_10-44-53/
chown -R mysql:mysql /var/lib/mysql/
重启服务：
systemctl start mairadb
xtrabackup 实现完全、增量备份及还原
1 备份过程,在原主机：
准备备份目录：
mkdir /backup/{full,incres/{inc1,inc2}}
完全备份
innobackupex --user=root --password=centos /backup/full/
第一次增量：
innobackupex --user=root --password=centos --incremental /backup/incres/inc1/
--incremental-basedir=/backup/full/2018-06-23_11-10-59/
第二次增量：
innobackupex --user=root --password=centos --incremental /backup/incres/inc2
--incremental-basedir=/backup/incres/inc1/2018-06-23_11-16-04/
2 转移备份数据
scp -r /backup/* 192.168.65.155:/backup/
3 恢复数据，在目标主机
确保服务关闭状态: systemctl stop mariadb
清空数据目录： rm -rf /var/lib/mysql/*
准备数据：
innobackupex --apply-log --redo-only /backup/full/2018-06-23_11-10-59/
innobackupex --apply-log --redo-only /backup/full/2018-06-23_11-10-59/
--incremental-dir=/backup/incres/inc1/2018-06-23_11-16-04/
innobackupex --apply-log --redo-only /backup/full/2018-06-23_11-10-59/
--incremental-dir=/backup/incres/inc2/2018-06-23_11-18-59/
恢复数据：
innobackupex --copy-back /backup/full/2018-06-23_11-10-59/
chown -R mysql:mysql /var/lib/mysql/
开启服务：
systemctl start mariadb
xtrabackup 单表导出和导入：
注意：此功能必须开启innodb_file_per_table
1 单表备份
innobackupex --user=root --password=centos --include='hello.students' /backup
2 备份表结构
mysql -uroot -pcentos -e 'show create table hellodb.students' >/backup/students.sql
3 复制表文件到需求主机：
scp -r /backup/* 192.168.65.155:/backup/
4 目标主机
prepare：
innobackupex --apply-log --export /backup/2018-06-23_13-28-49/
创建表：
vim /backup/students.sql
删除重复项，首行添加 use hellodb；
mysql < /backup/students.sql
删除表空间：
ALTER TABLE students DISCARD TABLESPACE;
复制表空间文件：
cp /backup/2018-06-23_13-28-49/hellodb/students.{cfg,exp,ibd}
/var/lib/mysql/hellodb/
chown -R mysql.mysql /var/lib/mysql/hellodb/
导入表：
ALTER TABLE students IMPORT TABLESPACE;
错误提示：Got error -1 from storage engine
mysql复制：-------------------------------------------------------------------------
扩展方式：scale up 纵向扩展 scale out 横向扩展
MYSQL扩展：
读写分离
复制： 每个节点都有相同的数据集
向外扩展
二进制日志
单向
复制的功用：
数据分布
负载均衡读
备份
高可用和故障切换
MYSQL升级测试
主从复制线程：
主节点：dump thread：为每个slave的I/O thread启动一个dump线程，用于向
其发送binary log events
从节点：I/O thread：向Master 请求二进制额日志事件，并保存于中继日志中
SQL trread：从中继日志中读取日志事件，在本地完成重放
跟复制功能相关的文件：
master.info：用于保存slave连接至master时的先关信息，
例如账号，密码、服务器地址等
relay-log.info: 保存在当前slave节点上已经复制的当前二进制日志和本地
repaly log 日志的对应关系
主从复制特点：
异步复制
主从数据不一致比较常见
复制架构：
Master/Slave,Master/Master,环状复制
一主多从
从服务器还可以再有从服务器
一从多主：适用于多个不同数据库
复制需要考虑二进制日志事件的记录格式：
STATEMENT
ROW
MIXED
Mysql主从复制配置：
主从配置过程：
官方介绍：https://mariadb.com/kb/en/library/setting-up-replication/
https://dev.mysql.com/doc/refman/5.5/en/replication-configuration.html
主节点配置：
1 启用二进制日志
[mysqld]
log_bin
2 为当前节点设置一个全局唯一的ID号
server_id=#
log-basename=master 可选项，设置datadir中日志名称，确保不依赖主机名
3 创建有复制权限的用户账号：
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'host' IDENTIFIED BY 'replpass';
从节点配置：
1 启动中继日志
[mysqld]
server_id=# 设置全局唯一的ID号
relay_log=relay-log relay log的文件路径，默认值为hostname-relay-bin
relay_log_index=relay_log.index 默认值为hostname-relay-bin.index
2 使用有复制权限的用户账号连接至主服务器，并启动复制线程
HELP CHANGE MASTER TO
CHANGE MASTER TO
MASTER_HOST='192.168.65.132',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql_binlog.000001',
MASTER_LOG_POS=245,
MASTER_CONNECT_RETRY=10;
如果主节点已经运行了一段时间，且有大量数据时，如何配置并启动salve节点
1 通过备份恢复数据至从服务器
2 复制起始位置为备份时，二进制日志文件及其position
如果要启动级联复制，需要在从服务器启用以下配置
[mysqld]
log_bin
log_slave_updates
复制架构中应该注意的问题：
1 限制从服务器为只读
在从服务器上设置
read_only=ON
注意：此限制对拥有SUPER权限的用户均无效
2 如果要阻止所有用户，可以使用
FLUSH TABLES WITH READ LOCK;
如何取消并清理从服务器：
RESET SLAVE; 在从服务器清楚master.info relay-log.info,relay log,
开始新的relay log，
注意，需要先STOP SLAVE
RESTE SLAVE ALL:清除所有从服务器上设置的主服务器同步信息：
如： PORT,HOST,USER,和password
如何保证主从复制的事务安全：
官方文档：https://mariadb.com/kb/en/library/server-system-variables/
1 在master节点启用参数：
sync_binlog=1 每次修改后立即同步二进制日志到磁盘，会造成性能下降
如果用到的为innodb存储引擎：
innodb_flush_logs_at_trx_commit=1
每次事务提交立即同步日志写磁盘
innodb_support_xa=ON 默认值，分布式事务，10.3.0废除
sync_master_info=# #次事件后master.info同步到磁盘
2 在slave节点启用服务器选项
skip_relay_start=ON 不自动启动slave
3 在salve节点启用参数
sync_relay_log=# #次写入后同步relay log 到磁盘
sync_relay_log_info=# #次事务后同步relay_log.info 到磁盘
从新配置msyql主从同步：
1 准备环境
主ip 192.168.65.132 从ip192.168.65.155
2 主服务器：
修改配置文件：
vim /etc/my.cnf
[mysqld]
log_bin=/data/mylog/mysql-binlog
server_id=1
重启服务：
添加授权账号：
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.65.155'
查看binlog日志文件名称和position
SHOW MASTER STATUS;
记下position位置
3 从服务器：
修改配置文件
vim /etc/my.cnf
[mysqld]
server_id=2
relay_log=relay-log
relay_log_index=relay-log.index
read_only=ON
重启服务
添加连接：
CHANGE MASTER TO
MASTER_HOST='192.168.65.132',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql_binlog.000001',
MASTER_LOG_POS=245,
MASTER_CONNECT_RETRY=10;
4 开启复制
从服务器使用：
START SLAVE; 开启复制
SHOW SLAVE STATUS; 查看连接状态
主服务器可以使用
SHOW SLAVE HOSTS; 查看连接的从服务器
主服务器已使用一段时间之后，配置主从复制：
1 使用主服务器的一个完全备份，先恢复至从服务器
如果使用mysqldump实现备份，通过选项
--master-data=1 会在备份文件中自动生成
CHANGE MASTER TO ..语句，后续只需补全信息即可在完成恢复时
同时添加连接
如果使用LVM快照备份，需要记下备份时的binlog position，且在恢复完成之后
自行添加连接
2 以mysqldump 为例：
主服务器：
修改配置文件：
vim /etc/my.cnf
log_bin=/data/mylog/mysql_binlog
server_id=1
添加授权账号：
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.65.155'
IDENTIFIED BY 'centos';
执行备份操作：
mysqldump -A -F --single-transaction --quick --master-data=1
>/backup/full.sql
将备份复制到从服务器主机
scp -r /backup/full.sql 192.165.65.155:/backup/
3 从服务器：
修改配置文件：
vim /etc/my.cnf
relay_log=relay-log
relay_log_index=relay.index
server_id=2
read_only=ON
修改备份文件
vim /backup/full.sql
在CHANGE MASTER TO 行修改添加
CHANGE MASTER TO
MASTER_HOST='192.168.65.132',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_CONNECT_RETRY=10，
文件自带以下两项，无须修改
MASTER_LOG_FILE=
MASTER_LOG_POS=
导入数据并添加连接：
mysql < /backup/full.sql
开始传送：
START SLAVE;
mysql实现级联复制：
1 主ip：192.168.65.132
从1，ip：192.168.65.155 从2，ip：192.168.65.160
要求从2服务器需要向从1服务器复制数据，从1服务器向主服务器复制
2 这里主和从1 由于已经完成了主从复制，所以只需要将从2 添加为从1的从即可
主服务器配置，不变
3 从1服务器，
修改配置文件：
vim /etc/my.cnf
[mysqld]
log_bin
log_slave_updates
同时去掉read_only 这一项
重启服务：
添加授权账号：
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.1658.65.160'
IDENTIFIED BY 'centos'；
生成备份：（如果都是从新配置，就不需要这一步了，
不过需要SHOW MASTER STATUS查看二进制日志文件以及position），
mysqldump --single-transaction --quick --master-data=1
>/data/full.sql
复制备份文件到从2主机
scp -r /data/full.sql 192.168.65.160:/data/
4 从2服务器
修改配置文件：
vim /etc/my.cnf
[mysqld]
server_id=3
read_only=ON
relay_log=relay-log
relay_log_index=relay.index
修改备份文件，添加：
CHANGE MASTER TO
MASTER_HOST='192.168.65.155',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_CONNECT_RETRY=10,
MASTER_LOG_FILE='mariadb-bin.000002',
MASTER_LOG_POS=245;
启动服务：
恢复备份并添加连接
mysql < /data/full.sql
开启复制：
START SLAVE;
实现mysql 主主复制：
主主复制：互为主从
容易产生的问题：数据不一致，因此慎用
考虑要点：自动增长id
配置一个节点使用奇数id
auto_increment_offset=1 开始点
auto_increment_increment=2 增长幅度
另一个节点使用偶数id
auto_increment_offset=2
auto_increment_increment=2
配置步骤：
1 各节点使用一个唯一server_id
2 都启动binary log 和relay log
3 创建拥有复制权限的用户账号
4 定义自动增长id字段的数值范围各为奇数和偶数
5 均把对方指定为主节点，并启动复制线程
在上面的实验中，已经实现了级联复制
现在此基础上，实现主服务器和从1服务器之间的主主复制
实现流程：
1 从1节点
修改配置文件，
vim /etc/my.cnf
auto_increment_offset=1
auto_increment_increment=2
log_bin
重启服务
授权复制账号：
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.65.132'
IDENTIFIED BY 'centos';
2 主暂时锁定数据库
FLUSH TABLES WITH READ LOCK;
从1获取二进制文件position
SHOW MASTER STATUS;
3 主节点
修改配置文件：
vim /etc/my.cnf
auto_increment_offset=2
auto_increment_increment=2
relay_log=relay-log
relay_log_index=relay.index
添加连接：
CHANGE MASTER TO
MASTER_HOST='192.168.65.155',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_CONNECT_RETRY=10,
MASTER_LOG_FILE='mariadb-bin.000001',
MASTER_LOG_POS=245;
重启服务：
启动复制：START SLAVE
-------------------------------------------------------------------------------
半同步复制：
默认情况下，mysql的复制功能是异步的，异步复制可以提供最佳的性能
主库把binlog日志发送给从库即结束，并不验证从库是否接受完毕。这意味
着当服务器或从服务器端发生故障时，有可能从服务器没有接收到主服务器
发送过来的binlog日志，这就会造成主服务器和从服务器的数据不一致。
甚至在恢复时造成数据的丢失
半同步复制实现：
1 首先，实现主从复制
2 主服务器配置
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SHOW VARIABLES LIKE '%semi%';
SET GLOBAL rpl_semi_sync_master_enabled=1;
SHOW GLOBAL STATUS LIKE '%semi%';
3 从服务器配置：
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SHOW VARIABLES LIKE '%semi%'
SET GLOBAL rpl_semi_sync_slave_enabled=1;
SHOW VARIABLES LIKE '%semi%'
-----------------------------------------------------------------------------
mysql复制过滤器：
让从节点仅复制指定的数据库，或指定数据库的指定表
两种实现方式：
1 服务器选项：主服务器仅想二进制日志中记录于特定数据相关的事件
注意：此项和binlog_format相关
官方文档：https://mariadb.com/kb/en/library/mysqld-options/#-binlogignore-db
相关变量：
binlog_do_db ='db_name' 数据库白名单列表，多个数据库需多行实现
binlog_ingore_db='db_name' 数据库黑名单列表
问题：基于二进制还原将无法事项，
不建议使用此方式
2 从服务器SQL_THREAD在replay日志中的事件时，仅读取与特定数据库（特定表）
相关的事件并应用于本地
问题：会造成网络及磁盘I/O浪费
从服务器上复制过滤相关变量：
replicate_do_db= 指定复制库的白名单
replicate_ignore_db= 指定复制库黑名单
replicate_do_table= 指定复制表的白名单
replicate_ignore_table= 指定复制表的黑名单
replicate_wild_do_table= foo%.bar% 支持通配符
replicate_wild_ignore_table=
mysql 复制的监控和维护：
1 清理日志：
PURGE {BINARY|MASTER} LOGS {TO 'log_name'|BEFORE datetime_expr}
RESET MASTER;
RESET SLAVE;
2 复制监控：
SHOW MASTER STATUS;
SHOW BINLOG EVENTS;
SHOW BINARY LOGS;
SHOW SLAVE STATUS;
SHOW PROCESSLIST;
3 从服务器是否落后于主服务器
Seconds_Behind_Master: 0
4 如何确定主从节点数据是否一致
安装percona-tools
5 数据不一致如何修复：
删除从数据库，重新复制
------------------------------------------------------------------------------
mysql 加密复制：
基于SSL复制：
在默认的主从复制过程或远程连接到MYSQL/mariadb所有的连接通信中的
数据都是明文的，外网里访问数据或者复制，存在安全隐患。
通过SSL/TSL加密的方式进行复制的方法，来进一步提高数据的安全性
官方文档：https://mariadb.com/kb/en/library/replication-with-secureconnections/
配置实现：
1 建立CA 并颁发证书
主节点证书目录如：
tree /etc/my.cnf.d/ssl/
├── cacert.pem
├── master.crt
├── master.csr
└── master.key
从节点证书目录如：
tree /etc/my.cnf.d/ssl/
├── cacert.pem
├── slave.crt
├── slave.csr
└── slave.key
2 主节点修改配置文件：
vim /etc/my.cnf
[mysqld]
server_id=2
log_bin
ssl
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
ssl-cert=/etc/my.cnf.d/ssl/master.crt
ssl-key=/etc/my.cnf.d/ssl/master.key
主节点添加授权账号：
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.65.160'
IDENTIFIED BY 'centos';
查看二进制日志position
SHOW MASTER STATUS;
mariadb-bin.000001 | 409
3 从节点配置文件：
vim /etc/my.cnf
[mysqld]
server_id=3
relay_log=relay-log
relay_log_index=relay.index
read_only=ON
添加连接：
CHANGE MASTER TO
MASTER_HOST='192.168.65.155',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_CONNECT_RETRY=10,
MASTER_LOG_FILE='mariadb-bin.000001',
MASTER_LOG_POS=409,
MASTER_SSL=1,
MASTER_SSL_CA = '/etc/my.cnf.d/ssl/cacert.pem',
MASTER_SSL_CERT = '/etc/my.cnf.d/ssl/slave.crt',
MASTER_SSL_KEY = '/etc/my.cnf.d/ssl/slave.key';
启动复制：
START SLVE
4 查看连接状态：SHOW SLAVE STATUS;
确保这几行：
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /etc/my.cnf.d/ssl/cacert.pem
Master_SSL_CA_Path:
Master_SSL_Cert: /etc/my.cnf.d/ssl/slave.crt
Master_SSL_Cipher:
Master_SSL_Key: /etc/my.cnf.d/ssl/slave.key
注意： 有尝试将slave节点的证书路径放在配置文件中，
结果是，slave status中路径不显示，
------------------------------------------------------------------------------------------
mysql 高可用
1 Master HA或多主模型
2 MMM：multi master mysql ，基于主从复制实现
3 MHA：master high availability,对主节点进行监控，可实现自动故障转移至其他从
节点；通过提升某一个从节点为新的主节点，基于主从复制实现，还需要客户端
配合实现，目前MHA主要支持一主多从的架构，要搭建MHA,要求一个复制集群中心
最少有3台数据库服务器，1主2从，即1台充当master，一台充当备用master，另外
一台充当从库，处于机器成本的考虑，淘宝进行了改造，目前淘宝TMHA已经支持一主
一从
4 galera cluster：wresp
通过wresp协议在全局实现复制，任何一个节点都可读写，不需要主从复制，实现
多主可读可写
MHA：
官方地址：https://code.google.com/archive/p/mysql-master-ha/
工作原理：
1 从宕机崩溃的master保存二进制日志事件
2 识别含有最新更新的slave
3 应用差异的中继日志到其他的slave
4 应用从master保存的二进制日志事件
5 提升一个slave为新的master
6 使其他的slave连接新的master进行复制
MHA软件由两部分组成，manager工具包和node工具包
manager工具包主要包括以下几个工具：
masterha_check_ssh 检查MHA的SSH配置状况
masterha_check_repl 检查MySQL复制状况
masterha_manger 启动MHA
masterha_check_status 检测当前MHA运行状态
masterha_master_monitor 检测master是否宕机
masterha_master_switch 故障转移（自动或手动）
masterha_conf_host 添加或删除配置的server信息
node工具包：这些工具通常由MHA manager的脚本触发，无需人为操作，
主要包括以下几个工具：
save_binary_logs 保存和复制master的二进制日志
apply_diff_relay_logs 识别差异的中继日志事件并将其差异的事件应用
于其他的slave
filter_mysqlbinlog 去除不必要的ROLLBACK事件（MHA已不再使用此工具）
purge_relay_logs 清除中继日志（不会阻塞SQL线程）
注意：为了尽可能的减少主库硬件损坏造成的数据丢失，因此在配置MHA的同时建议
配置成mysql5.5的半同步复制
自定义扩展：
secondary_check_script： 通过多条网络路由检测master的可用性
master_ip_ailover_script： 更新Application使用的masterip
shutdown_script： 强制关闭master节点
report_script： 发送报告
init_conf_load_script： 加载初始配置参数
master_ip_online_change_script：更新master节点ip地址
配置文件：
global配置，为各application提供默认配置
application配置：为每个主从复制集群
---------------------------------------------------------------------------------
实现MHA:
实验环境准备4台虚拟机
主机1 192.168.65.150 manager节点
主机2 192.168.65.132 master节点
主机3 192.168.65.155 slave1
主机4 192.168.65.160 slave2
1 实现主机2,3,4之间主从复制
master节点
vim /etc/my.cnf
[mysqld]
server_id=1
log_bin
重启服务：
记录二进制日志及position：
SHOW MASTER STATUS;
授权复制账号：
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.65.%'
IDENTIFIED BY 'centos';
slave节点1和2：
vim /etc/my.cnf
[mysqld]
server_id=2
relay_log=relay-log
relay_log_index=relay,index
read_only=ON
log_bin
relay_log_purge=0
重启服务：
添加连接：
CHANGE MASTER TO
MASTER_HOST='192.168.65.132',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_CONNECT_RETRY=10,
MASTER_LOG_FILE='mariadb-bin.000001',
MASTER_LOG_POS=245;
启动复制：
START SLAVE;
2 实现主机之间基于key验证
主机1 ，也可以是任意节点
ssh-keygen
ssh-copy-id 192.168.65.150
scp -r .ssh 192.168.65.132:/root/
scp -r .ssh 192.168.65.155:/root/
scp -r .ssh 192.168.65.160:/root/
3 添加mha授权账号：
在master节点：
GRANT ALL ON *.* TO 'mhauser'@'192.168.65.%' IDENTIFIED BY 'centos';
此授权会推送至slave节点，一并添加授权账号
4 安装mha软件，需启用epel源
manager节点：
yum install mha4mysql-manager-0.56-0.el6.noarch.rpm \
mha4mysql-node-0.56-0.el6.noarch.rpm -y
其他节点：
yum install mha4mysql-node-0.56-0.el6.noarch.rpm -y
5 配置管理节点
mkdir -p /etc/mastermha/app1
vim /etc/mastermha/app1.cnf
[server default]
user=mhauser
password=centos
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root
repl_user=repluser
repl_password=centos
ping_interval=1
[server1]
hostname=192.168.65.132
candidate_master=1
[server2]
hostname=192.168.65.155
candidate_master=1
[server3]
hostname=192.168.65.160
6 检查配置
在管理节点使用：
masterha_check_ssh --conf=/etc/mastermha/app1.cnf
masterha_check_repl --conf=/etc/mastermha/app1.cnf
7 启动服务，前台执行
masterha_manager --conf=/etc/mastermha/app1.cnf
排错日志：
/data/mastermha/app1/manager.log
8 如果master节点故障，mha会自动将slave1设置为提升master节点
且退出运行
此时如果前方有调度器，应该将调度器指向slave1节点
如果主机2 即原master节点恢复，将它添加为从服务器，从新投入使用
并重新启动mha
如果slave1故障，主机2（原master）将会提升为master
注意：主节点log_bin 如果在配置文件中更换路径了，
则需要在mha配置文件中注明
master_binlog_dir=/path/to/binlog_directory_of_the_master
或者改回默认地址
-----------------------------------------------------------------------------------
Galera Cluster：
galera cluster:
集成了galera插件的mysql集群，是一种新型的，数据不共享的，高度冗余的高可用
方案，目前galera cluster 有两个版本，分别是percona xtradb cluster 及
mariadb cluster，calera本身是具有多主特性的，即采用multi-master 的集群架构，
是一种既稳健，又在数据一致性，完整性及高性能方面有出色表现的高可用解决方案
架构：
多个节点组成了一个集群，与普通的主从架构不同，它们都可以做作为主节点，三个
节点是对等的，称为multi-master架构，当有客户端要写入或者读取数据时，连接哪
个实例都是一样的，读到的数据是相同的，写入某一个节点之后，集群自己会将新数据
同步到其他节点上面，这种架构不共享任何数据，是一种高冗余架构
Galera Cluster特点：
多主架构：
真正的多点读写的集群，在任何时候读写数据，都是最新的。
同步复制：
集群不同节点之间的数据同步，没有延迟，在数据库挂掉之后，数据不会丢失
并发复制：
从节点apply数据时，支持并行执行，更好的性能
故障切换：
在出现数据库故障时，因支持多点写入，切换容易
热插拔：
在服务期间，如果数据库挂了，只要监控程序发现的够快，不可服务的时间就会
非常少，在节点故障期间，节点本身对集群的影响非常小
自动节点克隆：
在新增节点，或者停机维护时，增量数据或者基础数据不需要人工手动备份提供
galera cluster 会自动拉取在线节点数据，最终集群会变为一致
对应用透明：
集群的维护，对应用程序是透明的
官方文档：
http://galeracluster.com/documentation-webpages/galera-documentation.pdf
http://galeracluster.com/documentation-webpages/index.html
https://mariadb.com/kb/en/mariadb/getting-started-with-mariadb-galera-cluster/
包括两个组件
Galera replication library (galera-3)
WSREP：MySQL扩展Write Set Replication API
wresp复制实现：
percona-cluster
MariaDB-Cluster
注意：都至少需要三个节点，不能安装mariadb-server
安装galera cluster：
配置yum源：
官方rpm：https://downloads.mariadb.org/mariadb/repositories/#mirror=host-europe
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
清华yum源
[mariadb]
name = MariaDB-qinghua
baseurl = https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-5.5.60/yum/centos7-amd64/
gpgcheck=0
安装：
yum install MariaDB-Galera-server -y
实现Galera cluster：
1 修改配置文件,三台主句分别完成相同配置
vim /etc/my.cnf.d/server.cnf
wsrep_provider = /usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.65.132,192.168.65.155,192.168.65.160"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
下面配置可选项
wsrep_cluster_name = ‘mycluster ‘默认my_wsrep_cluster
wsrep_node_name = 'node1'
wsrep_node_address = ‘172.16.0.7'
注意： /usr/lib64/galera/libgalera_smm.so 由galera-25.3.23-1包提供，yum关联安装上的
2 启动服务：
首次启动时，需要初始化集群，在其中一个节点上执行命令：
/etc/init.d/mysql start --wsrep-new-cluster
而后正常启动其他节点：
service mysql start
3 查看集群中相关系统变量和状态变量
SHOW VARIABLES LIKE 'wsrep_%‘;
SHOW STATUS LIKE 'wsrep_%‘;
SHOW STATUS LIKE 'wsrep_cluster_size‘;
-----------------------------------------------------------------------------------
mysql复制的问题和解决方案：
1 数据损坏或丢失
Master： MHA + semi repl
slave: 重新复制
2 混合使用存储引擎
MyISAM:不支持事务
InnoDB:支持事务
3 不唯一的server id
重新复制
4 复制延迟
需要额外的监控工具的辅助
一从多主： mariadb10版后支持
多线程复制：对多个数据库复制
性能衡量指标：
数据库服务衡量指标：
qps: query per second
tps: transaction per second
压力测试工具：
mysqlslap
Sysbench：功能强大
https://github.com/akopytov/sysbench
tpcc-mysql
MySQL Benchmark Suite
MySQL super -smack
MyBench
mysql 压力测试：
Mysqlslap：来自于mariadb包，测试的过程默认生成一个mysqlslap的
schema,生成测试表t1，查询和插入测试数据，mysqlslap库自动生成，如果已
经存在则先删除。用--only-print来打印实际的测试过程，整个测试完成后不会
在数据库中留下痕迹
使用格式：
mysqlslap [options]
常用参数 [options] 说明：
--auto-generate-sql, -a
自动生成测试表和数据，表示用mysqlslap工具自己生成的SQL脚本来测试并发压力
--auto-generate-sql-load-type=type
测试语句的类型。代表要测试的环境是读操作还是写操作还是两者混合的。取值包括：read，key，write，
update和mixed(默认)
--auto-generate-sql-add-auto-increment
代表对生成的表自动添加auto_increment列，从5.1.18版本开始支持
--number -char -cols=N, -x N
自动生成的测试表中包含多少个字符类型的列，默认1
--number -int -cols=N, -y N
自动生成的测试表中包含多少个数字类型的列，默认1
--number -of -queries=N
总的测试查询次数(并发客户数×每客户查询次数)
--query=name,-q
使用自定义脚本执行测试，例如可以调用自定义的存储过程
或者sql语句来执行测试
--create-schema
代表自定义的测试库名称，测试的schema，MySQL中
schema也就是database
--commint=N
多少条DML后提交一次
--compress, -C
如服务器和客户端都支持压缩，则压缩信息
--concurrency=N, -c N
表示并发量，即模拟多少个客户端同时执行select。
可指定多个值，以逗号或者--delimiter参数指定值做为分隔符
如：--concurrency=100,200,500
--engine=engine_name, -e engine_name
代表要测试的引擎，可以有多个，用分隔符隔开。
例如：--engines=myisam,innodb
--iterations=N, -i N
测试执行的迭代次数，代表要在不同并发环境下，各自运行测试多少次
--only-print
只打印测试语句而不实际执行。
--detach=N
执行N条语句后断开重连
--debug-info, -T
打印内存和CPU的相关信息
mysqlslap示例：
单线程测试
mysqlslap -a -uroot -pmagedu
多线程测试。使用–concurrency来模拟并发连接
mysqlslap -a -c 100 -uroot -pmagedu
迭代测试。用于需要多次执行测试得到平均值
mysqlslap -a -i 10 -uroot -pmagedu
mysqlslap ---auto-generate-sql-add-autoincrement -a
mysqlslap -a --auto-generate-sql-load-type=read
mysqlslap -a --auto-generate-secondary-indexes=3
mysqlslap -a --auto-generate-sql-write-number=1000
mysqlslap --create-schema world -q "select count(*) from City”
mysqlslap -a -e innodb -uroot -pmagedu
mysqlslap -a --number -of -queries=10 -uroot -pmagedu
测试同时不同的存储引擎的性能进行对比
mysqlslap -a --concurrency=50,100 --number -of -queries 1000
--iterations=5 --engine=myisam,innodb --debug-info -uroot -pmagedu
执行一次测试，分别50和100个并发，执行1000次总查询
mysqlslap -a --concurrency=50,100 --number -of -queries 1000
--debuginfo -uroot -pmagedu
50和100个并发分别得到一次测试结果(Benchmark)
并发数越多，执行完所有查询的时间越长。
为了准确起见，可以多迭代测试几次
mysqlslap -a --concurrency=50,100 --number -of -queries 1000
--iterations=5 --debug-info -uroot -pmagedu
生产环境my.cnf配置示例：
硬件：内存32G
innodb_file_per_table = 1
打开独立表空间
max_connections = 8000
#MySQL 服务所允许的同时会话数的上限，经常出现Too Many Connections的错误提
示，则需要增大此值
ack_log = 300
#back_log 是操作系统在监听队列中所能保持的连接数
max_connect_errors = 1000
#每个客户端连接最大的错误允许数量，当超过该次数，MYSQL服务器将禁止此主机的连
接请求，直到MYSQL服务器重启或通过flush hosts命令清空此主机的相关信息
open_files_limit = 10240
#所有线程所打开表的数量
max_allowed_packet = 32M
#每个连接传输数据大小.最大1G，须是1024的倍数，一般设为最大的BLOB的值
wait_timeout = 10
#指定一个请求的最大连接时间
sort_buffer_size = 16M
# 排序缓冲被用来处理类似ORDER BY以及GROUP BY队列所引起的排序
join_buffer_size = 16M
#不带索引的全表扫描.使用的buffer的最小值
query_cache_size = 128M
#查询缓冲大小
query_cache_limit = 4M
#指定单个查询能够使用的缓冲区大小，缺省为1M
transaction_isolation = REPEATABLE-READ
# 设定默认的事务隔离级别
thread_stack = 512K
# 线程使用的堆大小. 此值限制内存中能处理的存储过程的递归深度和SQL语句复杂性，
此容量的内存在每次连接时被预留.
log-bin
# 二进制日志功能
log_long_format=row
#二进制日志格式
innodb_buffer_pool_size = 6G
#InnoDB使用一个缓冲池来保存索引和原始数据, 可设置这个变量到服务器物理内存大小
的80%
innodb_file_io_threads = 4
#用来同步IO操作的IO线程的数量
innodb_thread_concurrency = 16
#在InnoDb核心内的允许线程数量，建议的设置是CPU数量加上磁盘数量的两倍
innodb_log_buffer_size = 16M
# 用来缓冲日志数据的缓冲区的大小.
innodb_log_file_size = 512M
在日志组中每个日志文件的大小.
innodb_log_files_in_group = 3
# 在日志组中的文件总数
innodb_lock_wait_timeout = 120
# SQL语句在被回滚前,InnoDB事务等待InnoDB行锁的时间
long_query_time = 2
#慢查询时长
log-queries-not -using-indexes
#将没有使用索引的查询也记录下来
MYSQL配置最佳实践：
高并发大数据的互联网业务，架构设计思路是“解放数据库CPU，将计算转移
到服务层”，并发量大的情况下，这些功能很可能将数据库拖死，业务逻辑放
到服务层具备更好的扩展性，能够轻易实现“增机器就加性能”
参考：
阿里巴巴Java开发手册
58到家数据库30条军规解读
http://zhuanlan.51cto.com/art/201702/531364.htm
笔记整理完成时间：2018年6月24日23:04:00
