MMysql2

并发性
    锁
        表锁 MyISAM
        页锁
        行锁 InnoDB
    
读锁：共享锁
写锁：独占锁

根据锁的施加方式
    显式锁：lock  tables tbname... {read|write}
    隐式锁：

mysql> help lock
Syntax:
LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

lock_type:
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE

解锁 UNLOCK TABLES


mysql> show global variables like "autocommit";　　使用事务时需要关掉，并显式提交
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+


start transaction　　启动事务
....
savepoint point_name 回滚点设置
....
rollback [to point_name ]
commit  (提交了就不能rollback了)


RDBMS事务隔离级别
　　READ-UNCOMMITED    能读到尚未提交的数据，出现幻读
　　READ-COMMITED      读到提交的数据，也会出现幻影行（rollback），在当前事务执行时，发生数据改变的情况
　　REPEATABLE-READ    ( 默认的，可重读)。
　　SERIALIZABLE       隔离级别下，读写冲突，因此并发度急剧下降，在MySQL/InnoDB下不建议使用

  mysql> select @@global.tx_isolation;

+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+

mysql> set session tx_isolation='READ-UNCOMMITTED';

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED       |
+------------------------+

存储引擎介绍

InnoDB
    事务：事务日志
    外键：
    MVCC：多版本并发控制
    聚簇索引
    间隙锁 防止幻读
    支持辅助索引
    自适应hash索引
    支持热备 mysql enterprise backup/percona Xtrabackup
    行级锁
    支持独立表空间innodb_file_per_table
        tbname.frm
        tbname.ibd 


MyISAM    适用只读数据（数据仓库），较小的表，能够忍受修复操作
    支持全文索引
    压缩
    空间索引（空间函数）
    表级锁
    延迟更新索引键
    缺点
        不支持事务和行级锁
        崩溃后无法安全恢复
    存储：tbname.frm/tbname.MYD/tbname.MYI

ARCHIVE
    仅支持insert和select，不支持更新和修改，支持很好的压缩功能。
    适用存储日志信息，或其他按时间序列实现的数据采集类应用

CSV
    将数据保存为CSV格式，不支持索引，一般适用于数据交换

BLACKHOLE
    没有存储机制，任何数据都会被丢弃，但是会记录二进制日志

FEDERATED
    访问远程服务器上数据的存储引擎

MEMORY
    内存存储引擎，比MyISAM至少块一个数量级，通常用于实现临时表

MRG_MyISAM
    合并多个MyISAM表的存储引擎

NDB
    集群存储引擎，专用mysql cluster

第三方存储引擎
    OLTP类
        XtraDB（innoDB增强版）
        PBXT (社区不再活跃)
        TokuDB: 支持分形数索引结构（海量存储）

    列式存储引擎
        Infobright    是开源MySQL数据仓库解决方案，引入了列存储方案，高强度的数据压缩，优化的统计计算。需定制
        InfiniDB
        MonetDB
        LucidDB

    社区存储引擎
        Aria
        SphinxSE
        Groona    全文索引引擎
        QQGraph    支持图，由Open query研发
        Spider    支持分片 
        VPForMySQL 支持垂直分区

引擎选择：
    事务
    备份
    奔溃恢复

 

Mysql日志

1、错误日志:
        log_error = /path/to/file 默认在数据目录下，hostname.err
        log_warnings = {0|1}
    记录启动和关闭过程中的信息
    运行过程中的错误信息
    事件调度器运行一个事件时产生的信息
    在从服务器上穷从服务器进程时产生的信息
mysql> show variables like "%log_error%";
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| log_error     | /data/node84.err |
+---------------+------------------+
mysql> show variables like "%log_warnin%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_warnings  | 1     |
+---------------+-------+
  
    
2、查询日志
    log    {OFF|ON}　　(包括慢查询)
    log_output        FILE/TABLE
    general_log =  {OFF|ON}
    general_log_file = /path/to/file

3、慢查询日志(时常包括阻塞时间)
    slow_query_log = {OFF|ON}
    slow_query_log_file  = /path/to/file
    long_query_time     =    秒（精确到微秒）

4、事务日志(轮转)  随机I/O转为顺序I/O
    -rw-rw---- 1 mysql mysql 5.0M Feb  2 13:11 ib_logfile0
    -rw-rw---- 1 mysql mysql 5.0M Feb  1 03:47 ib_logfile1
innodb_log_group_home_dir       | ./
innodb_log_files_in_group       | 2
innodb_log_file_size            | 5242880
innodb_flush_log_at_trx_commit  | 1    事务提交，立即从内存中写入事务日志，默认每隔1秒刷
innodb_mirrored_log_groups      | 1

5、二进制日志（建议和数据分开放）
log_bin        　　　　　　　 | ON             #也可以是文件名，不加后缀
binlog_format  　　　　　　　　 {MIXED|statement|row}
binlog_cache_size   　　　　 | 32768
binlog_stmt_cache_size      | 32768        基于语句的cache
expire_logs_days            | 0 　　　　　　 日志过期时间，自动清理          
max_binlog_cache_size       | 18446744073709547520  
max_binlog_size             | 1073741824   binlog多大后自动滚动          
max_binlog_stmt_cache_size  | 18446744073709547520

sql_log_bin      | ON    　　控制会话级别二进制日志功能的开启或关闭，恢复数据库时可临时关闭mysql> set session sql_log_bin=0;
sync_binlog      | 0        事务提交时是否立即将cache日志到事务日志 (性能下降，但是安全性提交）,重要参数
这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。
对于“sync_binlog”参数的各种设置的说明如下：
sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。
sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

在MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。
因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。
因为当设置为1的时候，即使系统Crash，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。



mysql> show binary logs;      或 mysql> show binary logs; 查看目前使用的binlog文件及大小
mysql> show master status;     文件及位置点


查看binlog中的事件
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
mysql> show binlog events in 'mysql-bin.000006';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000006 |   4 | Format_desc |         1 |         107 | Server ver: 5.5.49-log, Binlog ver: 4 |
| mysql-bin.000006 | 107 | Query       |         1 |         202 | create database binlogtest            |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
=>107 是下一个事件开始的位置

mysql> flush logs;    #会刷新binlog及relaylog日志，默认1G自动滚动或服务器重启时
或# mysqladmin flush-logs

mysql> help purge　　清除binlog命令
Name: 'PURGE BINARY LOGS'
Description:
Syntax:
PURGE { BINARY | MASTER } LOGS
    { TO 'log_name' | BEFORE datetime_expr }
Examples:
    PURGE BINARY LOGS TO 'mysql-bin.010';　　#10不会被删除
    PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

binlog内容
    事件的日期和时间
    服务器的ID
    事件的结束位置
    事件类型
    原服务器生成此事件的线程ID
    exec_time 语句的时间戳和写入二进制文件的时间差


mysqlbinlog命令行接口工具，注意别打开当前binlog
    常用选项（也可以远程读取）
    start-datetime                    (No default value)
    start-position                    4
    stop-datetime                     (No default value)
    stop-position                     18446744073709551615

6、中继日志

 

 

 

 

MySQL备份==========================================================================

为什么要备份
    灾难恢复
    需求改变
    测试
需要考虑的问题
    可以容忍数据丢失多长时间的数据
    恢复要在多长时间内完成
    是否需要持续提供服务
    需要恢复内容  整个数据库/单个数据库/一个或多个表

备份类型
根据是否离线    
    冷备 cold backup
        停服务，无法进行读写服务
    温备 warm backup
        只能进行读请求，无法写入
    热备 (MyISAM不支持）hot backup
        备份的不影响业务
根据数据范围
    完全 　　full backup
    增量    increment backup 基于完全备份或增量备份
    差异    differential backup 基于完全备份

备份方案
    完全+增量+binlog
    完全+差异+binlog

根据备份的数据还是文件
    物理备份：直接备份数据文件
        备份和恢复都比较简单
        恢复速度快

    逻辑备份：逻辑sql语句
        恢复简单
        备份为ASCII文件，sql语句
        与存储引擎无关
        可通过网络备份与恢复
        
        备份或恢复都需要mysqld服务器进程参与
        备份结果可能占据更多空间，但是可以压缩
        浮点数可能丢失精度
        还原之后，索引重建耗时

备份对象
    数据
    配置文件
    代码：存储过程、存储函数、触发器
    OS相关的配置文件（内核参数等）

 

备份工具

mysqldump 单线程 （mydumper多线程）
　　是逻辑备份工具
　　InnoDB热备、MyISAM温备
　　备份和恢复较慢
　　是传统的Mysql备份工具，整库，单库多库，单表多表，表中某些行，存储过程，存储函数，触发器
　　能记录备份时的binlog文件及相应的位置点
        
　　　　-B, --databases　　指定数据库(会加create加use),不加的话需要自己建库，use库
　　　　-x, --lock-all-tables
　　　　-l, --lock-tables
        　　　　mysql>flush tables with read lock （刷新到磁盘并施加读锁）适用MyISAM，但是对于innodb不合适(mysql> show engine innodb status\G)
　　　　--master-data[=#]    1是启用，2是注释 （change master to）
　　　　--single-transaction    实现innodb热备，不用lock table，会启动一个大事务。
　　　　-A     备份所有库 一般加--events
　　　　--add-drop-database Add a DROP DATABASE before each create.
　　　　-C     远程备份时启用压缩
       -E, --events        Dump events. 备份数据的同时备份事件调度器代码
       -R, --routines      Dump stored routines (functions and procedures).
       -d, --no-data       No row information. 慎用，只备份表结构
　　　　-F, --flush-logs    
       --opt    启用各种高级选项

锁表备份
        mysqldump -uroot -ppasswd -B dbA dbB --lock-all-tables > /path/to/backfile.sql
        或
        mysql>flush tables with read lock
        #mysqldump -uroot -ppasswd -B dbA dbB > /path/to/backfile.sql
        mysql>unlock tables

====================================================================================================================   
lvm-snapshot：
　　使用逻辑卷的快照功能备份，几乎热备，备份和恢复较快
前提：
　　1.数据文件要在逻辑卷上；
　　2.此逻辑卷所在组必须有足够空间使用快照卷；
　　3.数据文件和事务日志必须在同一个卷上。binlog最好是分开放置的
　　
　　vgcreate myvg /dev/sda5 /dev/sda6
　　lvcreate -L 300M -n myvg_lvm1 myvg
　　mkfs.ext4 /dev/myvg/myvg_lvm1 
　　mkdir /data
　　echo "/dev/myvg/myvg_lvm1  /data  ext4  defaults 0 0 "  >> /etc/fstab 
　　mount -a
　　#vi /etc/my.cnf
　　datadir = /data
　　innodb_file_per_table = 1
　　sync_binlog = 1
　　启动mysql，写入测试数据


备份步骤：备份目录/backup/
　　1.打开会话，施加读锁，锁定所有表
    　　mysql>flush tables  with read lock;　　等待当前事务完成，加锁成功
    　　mysql>flush logs; 或show master status
　　　　　　　　sync_binlog 可设置为1，防止事务日志丢失
　　2.通过另一个终端，保存二进制日志文件及相关位置信息
    　　# mysql -uroot -e "show master status" >> /backup/bakmaster.info     
　　3.创建快照卷
    　　# lvcreate -L 100M -s -p r -n back_snap /dev/myvg/myvg_lvm1 　　为数据所在的卷创建快照
    　　-s|--snapshot：指定创建快照
    　　[-p|--permission {r|rw}]：设定创建的快照具有被读或读写特性
    　　-n：指定快照名字
　　4.释放锁
　　　　mysql>unlock tables;
　　5.挂载快照卷，备份,并删除快照卷
　　　　# mount /dev/myvg/back_snap /mnt
　　　　# cp -a /mnt/* /backup/
　　　　# umount /mnt 
　　　　# lvremove /dev/myvg/back_snap 

　　6.增量备份二进制日志
　　　　#cat  //backup/bakmaster.info;查看当前二进制日志位置
　　　　#mysqlbinlog --start-position=107 mysql-bin.000005 >/backup/bak.sql
　　当一个事务跨越不同的二进制文件时，开启通过时间备份--start-datetime
　　　　#mysqlbinlog --start-datetime=’2016-05-06 14:55:34’ mysql-bin.00005 mysql-bin.000006 > /backup/bak-`date +%F-%H-%M-%S`.sql


　　还原：
　　$cp -a  /backup/*  /data 注意mysql目录下的文件属主是mysql
　　$chown -R mysql.mysql /data
　　$/etc/init.d/mysqld restart
　　mysql>set sql_log_bin=0;关闭二进制日志
　　mysql>source /backup/bak-`date +%F-%H-%M-%S`.sql；还原数据增量
　　mysql>set sql_log_bin=1;



=============================================================================================================================
select备份
        select *** into outfile ‘path to filename’ 纯文本信息
        load data infile ‘path to filename’ into table tbname
        逻辑备份工具，略快于mysqldump
        通常用于单表
    
=============================================================================================================================   
商业ibbackup

开源xtrabackup  https://www.percona.com/
　　物理备份工具，快速可靠，压缩功能
　　备份过程不会打断正在执行的事务
　　自动实现备份检查
　　InnoDB热备、MyISAM温备
　　建议设置独立表空间innodb_file_per_table = 1


xtrabackup安装
　　#yum install -y perl-DBD-MySQL perl-DBI  perl-Time-HiRes libaio*
　　#rpm -ivh percona-xtrabackup-2.2.10-1.el6.x86_64.rpm  

主要包含两个工具：
　　xtrabackup：  是用于热备份innodb, xtradb表中数据的工具，不能备份其他类型的表，也不能备份数据表结构； 
　　innobackupex：是将xtrabackup进行封装的perl脚本，提供了备份myisam表的能力。

　　使用innobackupex备份时，其会调用xtraback备份所有的InnoDB表，并复制所有关于表结构定义的相关文件（.frm）、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件
　　同时还会备份触发器和数据库配置信息相关的文件，这些文件会被保存至一个以时间命名的目录中

基本语法
　　innobackupex --user=DBUSER --password=DBUSERPASS  /path/to/BACKUP-DIR/

授权
　　CREATE USER 'bkuser'@'localhost' IDENTIFIED BY 'newpasswd'; 
　　REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'bkpuser'; 
　　GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkuser'@'localhost'; 
　　FLUSH PRIVILEGES; 


# innobackupex --user=bkuser --password=newpasswd /backup/
[root@node84 tools]# ls -l /backup/2017-05-11_07-30-18/
total 18476
-rw-r--r-- 1 root root      188 May 11 07:30 backup-my.cnf
drwxr-xr-x 2 root root     4096 May 11 07:30 binlogtest
-rw-r----- 1 root root 18874368 May 11 07:30 ibdata1
drwxr-xr-x 2 root root     4096 May 11 07:30 lost+found
drwxr-xr-x 2 root root     4096 May 11 07:30 mysql
drwxr-xr-x 2 root root     4096 May 11 07:30 performance_schema
drwxr-xr-x 2 root root     4096 May 11 07:30 test
drwx------ 2 root root     4096 May 11 07:30 testdb
-rw-r--r-- 1 root root       24 May 11 07:30 xtrabackup_binlog_info
-rw-r----- 1 root root       89 May 11 07:30 xtrabackup_checkpoints
-rw-r--r-- 1 root root      557 May 11 07:30 xtrabackup_info
-rw-r----- 1 root root     2560 May 11 07:30 xtrabackup_logfile

各文件说明：
　　(1)xtrabackup_checkpoints 　　—— 备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息；
　　　　　　　　　　　　　　　　　　　每个InnoDB页(通常为16k大小)都会包含一个日志序列号，即LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的。
准备阶段
　　已经提交事务同步至数据文件
　　未提交的事务回滚
LSN:
　　fullbackuup　　0-16000
　　increment1　　16001-17000　　
　　increment2　　17001-19000　　
　　increment3　　19001-21000 (未提交的回滚，提交的应用，依赖mysql自身存储引擎)

　　(2)xtrabackup_binlog_info 　　　　　 —— 记录mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。
　　(3)xtrabackup_binlog_pos_innodb 　　—— 记录二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position。
　　(4)xtrabackup_binary 　　　　　　　　 —— 备份中用到的xtrabackup的可执行文件；
　　(5)backup-my.cnf　　　　　　　　　　　 —— 备份命令用到的配置选项信息；
在使用innobackupex进行备份时，还可以使用--no-timestamp选项来阻止命令自动创建一个以时间命名的目录；如此一来，innobackupex命令将会创建一个BACKUP-DIR目录来存储备份数据。  


准备(prepare)一个完全备份
　　一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。
　　因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
　　innobakupex命令的--apply-log选项可用于实现上述功能。如下面的命令：
　　在实现“准备”的过程中，innobackupex通常还可以使用--use-memory选项来指定其可以使用的内存的大小，默认通常为100M。如果有足够的内存可用，可以多划分一些内存给prepare的过程，以提高其完成速度。
# innobackupex --apply-log /backup/2017-05-11_07-30-18/

模拟故障，删除data目录，重新初始化数据库之后进行恢复
# innobackupex --copy-back /backup/2017-05-11_07-30-18/
# chown -R mysql.mysql /data/
# /etc/init.d/mysqld start
# innobackupex --user=bkuser --password=newpasswd /backup/   在生产中，完整恢复后建议再做个完整备份
# ls /backup/
2017-05-11_07-30-18  2017-05-11_08-13-54

mysql> select * from students;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu  |  22 | M      |       2 |         3 |
|     2 | Shi Potian   |  22 | M      |       1 |         7 |
|     3 | Xie Yanke    |  53 | M      |       2 |        16 |
|     4 | Ding Dian    |  32 | M      |       4 |         4 |
|     5 | Yu Yuntong   |  26 | M      |       3 |         1 |
|     6 | Shi Qing     |  46 | M      |       5 |      NULL |
|     7 | Xi Ren       |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu    |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying |  22 | F      |       6 |      NULL |
|    10 | Yue Lingshan |  19 | F      |       3 |      NULL |
|    11 | xuxian       |  19 | F      |    NULL |      NULL |
|    12 | bainiangzi   |  17 | F      |    NULL |      NULL |
+-------+--------------+-----+--------+---------+-----------+

mysql> delete from students where Age>25;
mysql> select * from students;           
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu  |  22 | M      |       2 |         3 |
|     2 | Shi Potian   |  22 | M      |       1 |         7 |
|     7 | Xi Ren       |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu    |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying |  22 | F      |       6 |      NULL |
|    10 | Yue Lingshan |  19 | F      |       3 |      NULL |
|    11 | xuxian       |  19 | F      |    NULL |      NULL |
|    12 | bainiangzi   |  17 | F      |    NULL |      NULL |
+-------+--------------+-----+--------+---------+-----------+
8 rows in set (0.00 sec)


使用innobackupex进行增量备份
每个InnoDB的页面都会包含一个LSN信息，每当相关的数据发生改变，相关的页面的LSN就会自动增长。这正是InnoDB表可以进行增量备份的基础，即innobackupex通过备份上次完全备份之后发生改变的页面来实现。

基本语法：
增量备份前应进行一次完全备份。增量备份语法如下：
　　innobackupex --incremental /backup --incremental-basedir=BASEDIR
　　其中，BASEDIR指的是完全备份所在的目录，此命令执行结束后，innobackupex命令会在/backup目录中创建一个新的以时间命名的目录以存放所有的增量备份数据。
　　另外，在执行过增量备份之后再一次进行增量备份时，其--incremental-basedir应该指向上一次的增量备份所在的目录。

需要注意的是，增量备份仅能应用于InnoDB或XtraDB表，对于MyISAM表而言，执行增量备份时其实进行的是完全备份。

进行增量备份：
# innobackupex --incremental /backup --incremental-basedir=/backup/2017-05-11_08-13-54/  基于完整备份/backup/2017-05-11_08-13-54/
ls -l /backup/             
total 12
drwxr-xr-x 8 root root 4096 May 11 07:39 2017-05-11_07-30-18
drwxr-xr-x 8 root root 4096 May 11 08:13 2017-05-11_08-13-54　　完整备份
drwxr-xr-x 8 root root 4096 May 11 08:29 2017-05-11_08-29-11　　增量备份一

准备进行第二次增量，创建测试数据，在student表中插入数据
mysql> insert into students (Name,Age,Gender,ClassID,TeacherID )values ('gtms',50,"F",10,null);  
Query OK, 1 row affected (0.01 sec)

mysql> select * from students;                             
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu  |  22 | M      |       2 |         3 |
|     2 | Shi Potian   |  22 | M      |       1 |         7 |
|     7 | Xi Ren       |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu    |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying |  22 | F      |       6 |      NULL |
|    10 | Yue Lingshan |  19 | F      |       3 |      NULL |
|    11 | xuxian       |  19 | F      |    NULL |      NULL |
|    12 | bainiangzi   |  17 | F      |    NULL |      NULL |
|    13 | gtms         |  50 | F      |      10 |      NULL |
+-------+--------------+-----+--------+---------+-----------+

　　进行第二次增量备份：
　# innobackupex --incremental /backup --incremental-basedir=/backup/2017-05-11_08-29-11　　基于第一次增量/backup/2017-05-11_08-29-11

# ls -l /backup/
drwxr-xr-x 8 root root 4096 May 11 07:39 2017-05-11_07-30-18
drwxr-xr-x 8 root root 4096 May 11 08:13 2017-05-11_08-13-54
drwxr-xr-x 8 root root 4096 May 11 08:29 2017-05-11_08-29-11　　第一次增量
drwxr-xr-x 8 root root 4096 May 11 08:41 2017-05-11_08-41-53　　第二次增量

再对student表做一次修改（此操作未纳入增量备份，需要从binlog恢复，所以操作完成后将binlog抽取备份# cp /data/mysql-bin.* /tmp/binlog/ 
mysql>insert into students (Name,Age,Gender,ClassID,TeacherID )values ('gtms2',50,"F",10,null);  



准备恢复
　　“准备”(prepare)增量备份与整理完全备份有着一些不同，尤其要注意的是： 
　　　　(1)需要在每个备份(包括完全和各个增量备份)上，将已经提交的事务进行“重放”。“重放”之后，所有的备份数据将合并到完全备份上。 
　　　　(2)基于所有的备份将未提交的事务进行“回滚”。
　　于是，操作就变成了，执行完全备份的redo：
　　## innobackupex --apply-log --redo-only /backup/2017-05-11_08-13-54

接着执行第一个增量：
# innobackupex --apply-log --redo-only /backup/2017-05-11_08-13-54 --incremental-dir=/backup/2017-05-11_08-29-11
而后是第二个增量：
# innobackupex --apply-log --redo-only /backup/2017-05-11_08-13-54 --incremental-dir=/backup/2017-05-11_08-41-53/
其它依次类推，即如果有多次增量备份，每一次都要执行如上操作。

之后进行还原即可,参考前面
 innobackupex --copy-back  /path/to/BACKUP-DIR
最后从binlog截取后续日志即可
　　# cat /backup/2017-05-11_08-41-53/xtrabackup_binlog_info 
　　mysql-bin.000001        579
　　# mysqlbinlog --start-position=579 /tmp/binlog/mysql-bin.000001 >/tmp/binlog_increment.sql
　　mysql>set sql_log_bin=0
　　mysql>source /tmp/binlog_increment.sql
　　mysql>set sql_log_bin=1

5、Xtrabackup的“流”及“备份压缩”功能
　　Xtrabackup对备份的数据文件支持“流”功能，即可以将备份的数据通过STDOUT传输给tar程序进行归档，而不是默认的直接保存至某备份目录中。要使用此功能，仅需要使用--stream选项即可。如：
　　# innobackupex --stream=tar  /backup | gzip > /backup/`date +%F_%H-%M-%S`.tar.gz

　　甚至也可以使用类似如下命令将数据备份至其它服务器：
　　# innobackupex --stream=tar  /backup | ssh user@www.magedu.com  "cat -  > /backups/`date +%F_%H-%M-%S`.tar" 

　　此外，在执行本地备份时，还可以使用--parallel选项对多个文件进行并行复制。此选项用于指定在复制时启动的线程数目。当然，在实际进行备份时要利用此功能的便利性，
　　也需要启用innodb_file_per_table选项或共享的表空间通过innodb_data_file_path选项存储在多个ibdata文件中。对某一数据库的多个文件的复制无法利用到此功能。其简单使用方法如下：

　　# innobackupex --parallel  /path/to/backup
　　同时，innobackupex备份的数据文件也可以存储至远程主机，这可以使用--remote-host选项来实现：

　　# innobackupex --remote-host=root@www.magedu.com  /path/IN/REMOTE/HOST/to/backup    

6、导入或导出单张表
　　默认情况下，InnoDB表不能通过直接复制表文件的方式在mysql服务器之间进行移植，即便使用了innodb_file_per_table选项。而使用Xtrabackup工具可以实现此种功能，
　　不过，此时需要“导出”表的mysql服务器启用了innodb_file_per_table选项（严格来说，是要“导出”的表在其创建之前，mysql服务器就启用了innodb_file_per_table选项），
　　并且“导入”表的服务器同时启用了innodb_file_per_table和innodb_expand_import(5.5没有此选项)选项。

6.1 “导出”表
　　导出表是在备份的prepare阶段进行的，因此，一旦完全备份完成，就可以在prepare过程中通过--export选项将某表导出了：
　　# innobackupex --apply-log --export /path/to/backup
　　此命令会为每个innodb表的表空间创建一个以.exp结尾的文件，这些以.exp结尾的文件则可以用于导入至其它服务器。

6.2“导入”表
　　要在mysql服务器上导入来自于其它服务器的某innodb表，需要先在当前服务器上创建一个跟原表表结构一致的表，而后才能实现将表导入：
　　mysql> CREATE TABLE mytable (...)  ENGINE=InnoDB;

　　然后将此表的表空间删除：
　　mysql> ALTER TABLE mydatabase.mytable  DISCARD TABLESPACE;

　　接下来，将来自于“导出”表的服务器的mytable表的mytable.ibd和mytable.exp文件复制到当前服务器的数据目录，然后使用如下命令将其“导入”：
　　mysql> ALTER TABLE mydatabase.mytable  IMPORT TABLESPACE;

　　ERROR 1030 (HY000): Got error -1 from storage engine　　未测试成功

7、使用Xtrabackup对数据库进行部分备份
Xtrabackup也可以实现部分备份，即只备份某个或某些指定的数据库或某数据库中的某个或某些表。但要使用此功能，必须启用innodb_file_per_table选项，即每张表保存为一个独立的文件。
同时，其也不支持--stream选项，即不支持将数据通过管道传输给其它程序进行处理。
此外，还原部分备份跟还原全部数据的备份也有所不同，即你不能通过简单地将prepared的部分备份使用--copy-back选项直接复制回数据目录，而是要通过导入表的方向来实现还原。
当然，有些情况下，部分备份也可以直接通过--copy-back进行还原，但这种方式还原而来的数据多数会产生数据不一致的问题，因此，无论如何不推荐使用这种方式。

7.1 创建部分备份
创建部分备份的方式有三种：正则表达式(--include), 枚举表文件(--tables-file)和列出要备份的数据库(--databases)。

7.1.1 使用--include
使用--include时，要求为其指定要备份的表的完整名称，即形如databasename.tablename，如：
# innobackupex --include='^mageedu[.]tb1'  /path/to/backup

7.1.2 使用--tables-file
此选项的参数需要是一个文件名，此文件中每行包含一个要备份的表的完整名称；如：

# echo -e 'mageedu.tb1\nmageedu.tb2' > /tmp/tables.txt 
# innobackupex --tables-file=/tmp/tables.txt  /path/to/backup

7.1.3 使用--databases
此选项接受的参数为数据名，如果要指定多个数据库，彼此间需要以空格隔开；同时，在指定某数据库时，也可以只指定其中的某张表。此外，此选项也可以接受一个文件为参数，文件中每一行为一个要备份的对象。如：
# innobackupex --databases="mageedu testdb"  /path/to/backup

7.2 整理(preparing)部分备份
prepare部分备份的过程类似于导出表的过程，要使用--export选项进行：
# innobackupex --apply-log --export  /pat/to/partial/backup

此命令执行过程中，innobackupex会调用xtrabackup命令从数据字典中移除缺失的表，因此，会显示出许多关于“表不存在”类的警告信息。同时，也会显示出为备份文件中存在的表创建.exp文件的相关信息。
7.3 还原部分备份
还原部分备份的过程跟导入表的过程相同。当然，也可以通过直接复制prepared状态的备份直接至数据目录中实现还原，不要此时要求数据目录处于一致状态。

    mysqlhotcopy
        冷备
        速度慢

从备份中恢复需要的操作
    停止MySQL服务
    记录服务的配置和文件权限
    复制备份文件至备份目录
    按需调整配置
    按需改变文件权限
    尝试启动服务
    装载逻辑备份
    检查和重放二进制日志
    确定数据还原正常完成
    以完全权限重启MySQL

 

 

缓存

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_limit 
MySQL允许缓存的单个缓存对象的最大值，不过MySQL只有在查询的所有结果都返回后才知道其是否超出大小，但其查询一开始便会尝试使用缓存存储查询结果，一旦发现超出缓存最大值则会从缓存中将其删除，并增大Qcache_not_cached的值，因此，如果知道某查询的结果会超出可缓存的最大值设置，可在查询语句中使用SQL_NO_CACHE

query_cache_min_res_unit
存储缓存的最小内存块,如果设置过小，会减少空间浪费，但是会导致频繁的内存块申请操作，设置的过大，会有更高的碎片产生率，可以通过（query_cache_size- Qcache_free_memory）/ Qcache_queries_in_cache来获得一个接近理想的值，同时，如果Qcache_free_blocks存在空闲块，但Qcache_lowmem_prunes仍然在增长，则表明碎片过多导致了缓存结果会过早删除

query_cache_size
查询缓存的内存总大小，必须是1024的整数倍，单位为字节，Mysql启动时，一次性分配并且初始化。如果改变气其值，mysql会立刻删除所有的缓存对象并重新配置初始化新设值。在性能较强的通用服务器上，查询缓存可能是影响服务器扩展的因素，因为它存在成为服务器资源竞争单点的可能性，在多核心的服务器上甚至还可能导致服务进程宕掉

query_cache_type
是否打开查询缓存，{ON|OFF|DEMAND} ，DEMAND仅在查询语句中显示使用SQL_CACHE时才会使用缓存

query_cache_wlock_invalidate
如果某个数据表被其他连接锁住，是否仍然从查询缓存中返回结果，OFF表示返回



mysql> show global status like "qcache%";
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 2        |
| Qcache_free_memory      | 16750728 |
| Qcache_hits             | 7        |
| Qcache_inserts          | 27       |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 23       |
| Qcache_queries_in_cache | 6        |
| Qcache_total_blocks     | 19       |
+-------------------------+----------+


Qcache_free_blocks    已分配出去，目前未被使用的块
Qcache_free_memory        未被申请划分的内存大小
Qcache_hits            命中次数
Qcache_inserts            插入缓存对象的个数
Qcache_lowmem_prunes    因为中间空闲块太小导致无法缓存个数，大的话可能内存碎片多
Qcache_not_cached        查询没有被缓存的个数
Qcache_queries_in_cache     保存在缓存中的查询个数
Qcache_total_blocks    已经划分的块数量



命中率的估算，未必很反应很真实
mysql> show  global status where variable_name="Qcache_hits" or Variable_name="Com_select";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 56    |    如果使用的缓存，则不会增加
| Qcache_hits   | 7     |


也可以查看另一个指标 命中率和写入缓存的比例
Qcache_hits/ Qcache_inserts    一般大于3时查询缓存有效，大于10属于优良


通用缓存优化思路
1、批量写入而非单个写入，批量写入仅一次性影像缓存
2、过大的缓存空间，会使得在大量缓存对象过期失效时导致服务器假死
3、比较时，使用SQL_CACHE/SQL_NO_CACHE手动控制缓存动作
4、对写密集型的场景来说，禁用缓存可以提高性能

FLUSH QUERY CACHE。用于清理查询缓存碎片以提高内存使用性能。该语句不从缓存中移出任何查询，但是可能会导致服务僵死一段时间
RESET QUERY CACHE。用于清空查询缓存所有内容。

不缓存的内容：
    用户自定义函数
    用户自定义变量
    临时表
    mysql库的系统表
    列级别的权限
    存储函数
    不确定数据