一.Explain输出的信息解释：
MariaDB [hellodb]> explain select * from students\G
id: 1
select_type: SIMPLE
table: students
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 24
Extra:
id：当前查询语句中，每一个select语句的编号。
复杂类型的查询有三种：
subquery简单子查询，enrived用于from的子查询，联合查询：union.
注：每次union查询的分析结果会出现一个额外的匿名临时表。
select_type：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将会使用覆盖索引，以避免访问表。
usering where:mysql服务器将在存储引擎检索后，再进行一次过滤。
using temporary:mysql对结果培训时会使用临时表。
using filesort:对结果使用一个外部索引排序。
二.Mysql的日志文件
查询日志：
慢查询日志：查询执行时长超过指定时长的查询操作所记录的日志。
查询时间大于以下结果的为慢查询日志。
MariaDB [mysql]> select @@global.long_query_time;
+————————–+
| @@global.long_query_time |
+————————–+
| 10.000000 |
+————————–+
1 row in set (0.00 sec)
错误日志：
mysqld启动和关闭程序中输出的信息，运行中产生的错误信息，event scheduler的在运行一个event时产生的日志信息。
在主从复制架构中的从服务器上启动服务器线程的日志信息。
/dara/***.err
二进制文件：
是由日志文件和索引文件。
日志记录格式：
基于“语句”，基于“行”记录，混合模式
二进制日志格式：
mysqlbinlog查看二进制日志工具.
[[email protected] data]# mysqlbinlog mysql-bin.000008
# at 1199
#150615 3:37:28 server id 1 end_log_pos 1242 Rotate to mysql-bin.000007 pos: 4
DELIMITER ;
事件发生的日期好时间：（150615）
事件发生在服务器的标识（server id）
事件的结束位置：（end_log_pos 1242）
事件的类型（rotate）
事件发生时所在的服务器执行此事件的线程id
语句的时间戳与将其写入二进制文件中的时间差）
错误代码（eroor=0）
事件所属的全局事务的GTID
在配置文件可以修改二进制文件的路径：
log_bin=/path/to/file
binlog_format=mixed
sql_log_bin=on
max_binlog_size=1073741824
二进制日志文件的单文件上线：
MariaDB [mysql]> show global variables like ‘%log%’;
max_binlog_cache_size= 18446744073709547520
max_binlog_stmt_cache_size =18446744073709547520
常用命令：
set session sql_log_bin=0|off 关闭二进制日志文件
MariaDB [hellodb]> show binary logs;
MariaDB [hellodb]> show master logs;
[[email protected] ~]# cat /data/mysql-bin.index 查二进制文件的索引文件
MariaDB [(none)]> show binlog events in ‘mysql-bin.000001’ \G
可以让日志滚动：
MariaDB [(none)]> flush logs;
中继日志：
查看缓存
MariaDB [hellodb]> show global variables like “%query%”;
MariaDB [hellodb]> set query_cache_type=off;
MariaDB [hellodb]> select @@query_cache_type;
事务日志：
三.Mysql的备份和恢复
备份类型：
完全备份，
部分备份：
部分备份，仅备份其中的一张表或者多张表。
完全备份，增量备份：
增量备份，仅备份从上一次完全备份或者增量备份之后变化部分的数据。
热备份，温备份和冷备份：
热备份：在线备份，读写操作不受影响。
温备份：在线备份，读操作可继续进行，但写操作不允许。
冷备份：离线备份，数据库服务器离线，备份期间不能为业务提供读写服务。
myisam:温备
innodb:热备
物理备份和逻辑备份：
物理备份：直接复制数据文件进行备份。
逻辑备份：从数据库中导出数据另存而进行的备份。
规则备份时需要考虑因素：
持锁的时长：
备份过程时长：
备份负载：
恢复过程时长：
数据，额外的数据（二进制日志和INondb的事务日志），代码（存储过程和存储函数，触发器，事件调度器等），服务器配置文件。
备份方案：
完全备份+增量备份
备份工具：
msqldump：逻辑备份工具，适用于所有存储引擎，温备：完全备份，部分备份。对innodb存储引擎支持热备。
cp,tar等文件系统工具：物理备份工具，适用于所有存储引擎：冷备：完全备份。部分备份。
lvm2快照备份：基于逻辑卷备份
mysqldump+binlog 完全备份，通过备份二进制日志实现。
xtrabaclup:对innodb热备，支持完全备份和增量备份。对imyisam引擎温备，只支持完全备份。
mysqldump的用法客户端通过mysql协议连接mysqld
myisam,innodb:温备
-x：–lock-all-tables：锁定所有表
-l：–lock-tables：锁定备份的表。
-B：指定数据库（database1,2,3） 若加B就自动创建库，不加则没有。
-C：–compress 压缩传输
其他选项：
-E：–events 备份指定的事件调度器。
-R：–routines备份存储过程和存储函数。
–triggers触发器
–master-data[=#] 0|1|2
（master-data跟flush logs有点类似。会重新生成一个二进制文件进行记录）
1,记录change,matser to语句，此语句未被注释。
2,记录为注释语句。
仅限于innodb,–single-transaction:启动一个大的单一事务实现备份
–flush-logs, -F:锁定表之后执行flush logs命令。
查看当前所在的二进制文件：
MariaDB [mysql]> show master logs;
确定当前的二进制文件：
MariaDB [mysql]> show binlog events in ‘mysql-bin.000010’;
滚动日志相会生成一个新文件。
MariaDB [mysql]> flush logs;
备份案例：
mybinlog命令备份二进制文件：
导入完整备份->增量备份->当前的截止的二进制
mysqldump -A -uroot -h localhost -p > /tem/1.sql
完整备份
[[email protected] data]# mysqldump -uroot -p123abc -x –master-data=2 > /back/db1-`date +%F`.sql
增量备份
[[email protected] ~]# mysqlbinlog –start-datetime ‘2015-06-16 10:30:00’ –stop-datetime ‘2015-06-16 10:40:00’ /data/mysql-bin.* > /back/db2-`date +%F`.sql
二进制文件恢复
[[email protected] ~]# mysqlbinlog /data/mysql-bin.000011 > /back/last.sql
找到最后一次操作数据：
[[email protected] back]# vim last.sql
drop database hellodb
将三个文件依次导入还原：
[[email protected] back]# mysql -uroot -p < /back/db1 -2015-06-16.sql
[[email protected] back]# mysql -uroot -p < /back/db2-2015-06-16.sql
[[email protected] back]# mysql -uroot -p < /back/last.sql
逻辑备份属于数据量较小的情况下实行。
如果用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
一.安装前准备：
由于此程序是perl开发，需要安装两个依赖包：
[[email protected] data]# yum install perl-Time-HiRes perl-DBD-MySQL
二.安装percona-xtrabackup
[[email protected] data]# rpm -ivh p percona-xtrabackup-2.1.4-656.rhel6.x86_64.rpm
warning: percona-xtrabackup-2.1.4-4982.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing… ########################################### [100%]
1:percona-xtrabackup ########################################### [100%]
安装完成后，innobackupex客户端工具，以mysql协议接入mysqld，不支持离线备份。
三.从完全备份中恢复数据。
1.执行一次（full-backuped）完整备份
–apply-log选项。
一般情况下，在备份完成后，数据且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据库中的事务，因为此时数据文件仍处理不一致状态，“准备”的主要作用正是通过回滚未提交的事务及同步已提交的事务至数据文件使得数据文件处于一致性状态。 所以该选项是此功能。
[[email protected] //]# innobackupex –user=root –password=123.com /backup/
[[email protected] backup]# innobackupex –apply-log /backup/2015-06-17_08-48-20/
2.恢复数据库要离线。
[[email protected] data]# service mysqld stop
[[email protected] data]# innobackupex –copy-back /backup/2015-06-17_09-46-26/
3.还原完数据库之后修改数据库用户和组，再启动mysq。
二进制文件是用于提供时间点还原的。
[[email protected] data]# chown -R mysql.mysql ./*
[[email protected] data]# serivce mysqld start
四.使用innobackupex进行（incremental）增量备份
每一个innodb页面都包含一个LSN信息，每当相关的数据发生改变，相关的页面LSN就会自动增长，这正是innodb表可以增量备份的基础，即innobackupex通过备份上次完全备份之后发生改变的页面来实现。
1）要实现第一次增量备份，可以使用下面的命令进行：
[[email protected] backup]# innobackupex –user=root –password=123.com /backup/ [[email protected] backup]# innobackupex –user=root –password=123.com –incremental /backup/ –incremental-basedir=/backup/2015-06-18_09-53-50/
若是InnoDB的话是增量备份，Myisam的话则是完全备份。
2）数据库有变动之后，再一次执行增量备份。
[[email protected] backup]# innobackupex –user=root –password=123.com –incremental /backup/ –incremental-basedir=/backup/2015-06-18_09-53-50/
3）模拟数据库服务器宕机，停掉mysql。把数据库目录备份。
[[email protected] 2015-06-18_10-01-21]# more xtrabackup_binlog_info
mysql-bin.000003 9268 查看最后一次增量备份的截止点。
4）然后用mysqlbinlog工具导出/data/mysql-bin.000003
[[email protected] data]# mysqlbinlog –start-position=9268 mysql-bin.000003 > /backup/timepoint.sql
5）删除数据目录。
[[email protected] data]# rm -Rf *
6）把三个增量合并成一个之后导入数据即可：
合并第一个完整备份:
[[email protected] /]# innobackupex –apply-log -redo-only /backup/2015-06-18_09-53-50/
接着合并第一个增量：
[[email protected] backup]# innobackupex –apply-log -redo-only /backup/2015-06-18_09-53-50/ –incremental-dir=/backup/2015-06-18_09-57-15/
合并第二个增量：
[[email protected] backup]# innobackupex –apply-log /backup/2015-06-18_09-53-50/ –incremental-dir=/backup/2015-06-18_10-01-21/
-redo-only:只能做提交不能做回滚
7）把相关的数据文件恢复，只用把第一个备份恢复即可。
[[email protected] backup]# innobackupex –copy-back /backup/2015-06-18_09-53-50/
8）恢复完成之后，把data目录的属主属组修改成mysql.启动mysql即可！
[[email protected] data]# chown -R mysql.mysql ./*
[[email protected] data]# service mysqld start
Starting MySQL.. [ OK ]
9）登录数据库，把备份的二进制日志文件恢复回来。
MariaDB [(none)]> use hellodb;
MariaDB [hellodb]> show tables;
MariaDB [hellodb]> set session sql_log_bin=0;
MariaDB [hellodb]> source /backup/timepoint.sql;
MariaDB [hellodb]> set session sql_log_bin=1;
查看数据ok~ 建议：一般恢复完成之后，做一次完全备份。
五. xtrabackup单张表的导入导出
默认情况下。innodb表不能通过直接复制表文件的方式在mysql服务器之间进行移植，即便使用了innodb_file_per_table选项，而使用xtrabackup工具就可以实现这种功能，不过，需要导出表的mysql服务器启用了innodb_file_per_table选项，（严格的说，钥匙导出的表在其创建之前，mysql服务器就启用了innodb_file_per_table选项）并导入表的服务器同时启用了innodb_file_per_table和innodb_expand_inport选项。
1）首先备份数据库，并且修改表的存储引擎：
[[email protected] backup]# mysqldump -uroot -p123.com –databases hellodb –lock-all-tables >/backup/hello.sql
:%[email protected][email protected][email protected]
登录数据库删除此库
MariaDB [mysql]> drop database hellodb;
导入数据库
[[email protected] backup]# mysql -uroot -p123.com < /backup/hello.sql
登录数据库，查看表的结构：
MariaDB [hellodb]> show table status\G
*************************** 8. row ***************************
Name: toc
Engine: InnoDB
Version: 10
Row_format: Compact
备份一次数据库。
[[email protected] backup]# innobackupex –user=root -password=123.com /backup/
2）导出表
导出表在备份的perpare阶段进行，因此一旦完全备份完成，就可以在prepare过程中通过–export选项将其某表导出了。
[[email protected] hellodb]# innobackupex –apply-log –export 2015-06-18_10-51-00/
此命令会为每一个innodb表的表空间创建一个.exp结尾的文件，这些.exp的文件可以用于导入其他服务器。
[[email protected] hellodb]# ls
classes.cfg coc.cfg courses.cfg db.opt scores.ibd students.ibd t2.ibd teachers.ibd toc.ibd
classes.exp coc.exp courses.exp scores.cfg students.cfg t2.cfg teachers.cfg toc.cfg
classes.frm coc.frm courses.frm scores.exp students.exp（导出格式文件） t2.exp teachers.exp toc.exp
classes.ibd coc.ibd courses.ibd（表空间） scores.frm students.frm t2.frm teachers.frm toc.frm
3）导入表
要在mysql服务器上导入来自于其他服务器的某innodb表，首先要在当前服务器上创建一个跟原表结构一直的表，而后才能实现将表导入。
查看是否支持单表存储格式：
MariaDB [mydb]> show global variables like ‘innodb_file%’;
+————————–+———-+
| Variable_name | Value |
+————————–+———-+
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| innodb_file_per_table | ON |
+————————–+———-+
4 rows in set (0.00 sec)
>show create table tablename;
MariaDB [(none)]> create database mydb;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use mydb;
MariaDB [mydb]> CREATE TABLE `coc` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ClassID` tinyint(3) unsigned NOT NULL,
`CourseID` smallint(5) unsigned DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;
4）然后将此表的空间删除：
MariaDB [mydb]> alter table coc discard tablespace;
5）接下来，将来来自于导出表的服务器的mytable表的mytable.idb和mytable.exp文件复制到当前服务器的数据目录，然后使用如下命令将其导入：
[[email protected] hellodb]# scp t2.exp t2.ibd 172.16.249.152:/data/mydb
[[email protected] mydb]# chown -R mysql.mysql ./*
恢复表结构
MariaDB [mydb2]> alter table coc import tablespace;
ok!
六.备份单个数据库
创建部分备份
创建部分备份的方式有三种，正则表达（–include），枚举文件（–tables-file）和列出要备份的数据库（–databases）。
1）使用include
使用–include时候，要求为其指定要备份的表的完整名称，即称为databasesname,tablename。如：
#innobackupex –include -‘hellodb.coc’ /backup
2）使用–tbales-file
此选项的参数需要是一个文件名，此文件中包含一个备份的表的完整名称。
如：#echo -e ‘hellodb.coc\hellodb.students’ >/backup/db.txt
#innobackupex –tables-file=/bakup/
[[email protected] mysql]db.txt /backup
3）使用–databases
此选项接受的参数为数据库名，如果要指定多个数据库，彼此之间要以空格隔开同时，在指定某数据库时，也可以只指定其中的某张表，此外，此时选项也可以接受一个文件为参数，文件中的每一行为一个要备份的对象如：
# innobackupex –databases=”mysql” –user=root –password=123.com /backup/
