一、总结mysql常见的存储引擎及特点
mysql存储引擎主要使用MYISAM以及InnoDB
MyISAM
MYISAM引擎特点：1、不支持事务，2、表级锁定，3、读写互相阻塞，写入不能读，读时不能写，4、只缓存索引，5、不支持外键约束，6、不支持聚簇索引，7、读取数据较快，占用资源较少，8、不支持MVCC（多版本并发控制机制）高并发，9、崩溃恢复性较差，10、Mysql5.5.5前默认的数据库引擎
MYISAM存储引擎适用场景：1、只读（或者写较少）；2、表较小（可以接受长时间进行修复操作）
MYISAM引擎文件：1、以frm结尾的文件（表示表格式定义），2、以MYD结尾的文件（表示表数据文件），3、以MYI结尾的文件（表示表索引文件）
InnoDB
InnoDB引擎特点：1、行级锁，2、支持事务，适合处理大量短期事务，3、读写阻塞与事务隔离级别相关，4、可缓存数据和索引，5、支持聚簇索引，6、崩溃恢复性好，7、支持MVCC高并发，8、从Mysql5.5后支持全文索引，9、从Mysql5.5.5开始默认的数据库引擎
InnoDB引擎有两种存放数据的情况：
一种是：将所有的InonDB表的数据和索引存放置于同一个表空间中：1、数据文件：ibdata1,ibdata2...存放在datadir定义的目录下；2,、表格式定义：tb_name.frm，存放在datadir定义的每个数据库对应的目录下
另外一种是：将每个表单独使用一个表空间存放表的数据和索引：两类文件放在对应每个数据库独立目录中，1、数据文件（存储数据和索引）:tb_name.ibd；2、表格式定义：tb_name.frm;
版本的不同数据存放的勤快主要由innodb_file_per_table的影响，如果不想把数据都存放在一个目录下（即将所有的InonDB表的数据和索引存放置于同一个表空间中），则在配置文件中，将innodb_file_per_table=ON即可（mariaDB5.5以后版本默认值）
命令：
查看mysql支持的存储引擎：show engines;
查看数据库表的状态：show table status from db_name\G
查看当前默认的存储引擎：show variables like '%storage_engines%';
设置默认的存储引擎：编辑vim /etc/my.cnf文件，添加default_storage_engine=InnoDB
查看库中指定表的存储引擎：1、show table status like 'tb_name'；2、show craete table tb_name；
二、mysql日志各类总结
mysql支持丰富的日志类型：1、事务日志：transacting log，事务日志的写入类型为“追加”，因此其操作为“顺序IO”，通常也被称为：预写式日志write ahead logging，事务日志文件：ib_logfile0,ib_logfile1；2、错误日志 error log；3、通用日志 general log；4、慢查询日志slow query log；5、中继日志 reley log，在主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件
事务日志
Innodb事务日志相关配置：show variables like '%innodb_log%';
innodb_log_file_size 每个日志文件大小；innodb_log_files_in_group 日志组成员个数；innodb_log_group_home_dir 事务文件路径；innodb_flush_log_at_trx_commit 默认为1
事务日志性能优化：innodb_flush_log_at_trx_commit 0|1|2
I/O越多，磁盘性能越差；0和2模式速度比较快（I/O越少；磁盘性能好），但是容易丢失数据（突然断电，1秒钟写一次）；1模式速度慢一点（I/O越多；磁盘性能差，每次事务后执行刷新到磁盘），但是不容易丢失数据
错误日志
错误日志：1、mysql启动和关闭过程中输出的事件信息；2、mysqld运行过程中产生的错误信息；3、event scheduler运行一个event时产生的日志信息；4、在主从复制架构中的从服务器上启动从服务器线程时产生的信息；
错误日志的路径：show global variables like 'log_error'进行查询错误日志的默认路径，亦可以通过修改配置文件修改默认的错误人日志的路径（vim etc/my.cnf.d/mariadb-server.cnf）
log_warings（记录警告信息）有0/1/2/3等值，值越大表示记录在错误日志中的报警信息越详细；CentOS7 mariadb 5.5默认值为1；CentOS8 mariadb 10.3默认值为2；
通用日志
通用日志：记录对数据库的通用操作，包括：错误的SQL语句；通用日志默认不打开，如果打开通用日志，默认存放在/var/lib/mysql/下，对数据库的任何操作都会记录下来
通用日志相关设置：general_log=ON|OFF；general_log_file=HOSTNAME.log；#general_log_file记录了通用日志的文件名；log_output=TABLE|FILE|NONE #默认是用文件的格式记录，亦可以改为以表的形式存储数据，默认存放在mysql这个数据库里
慢查询日志
慢查询日志：记录执行查询时长超出指定时长的操作
慢查询相关的变量：1、slow_query_log=ON|OFF #开启或关闭慢查询，支持全局和会话，只有全局设置才会生成慢查询文件；2、long_query_time=N #慢查询的阀值，单位秒，默认为10s；3、slow_query_log_file=HOSTNAME-slow.log #慢查询日志文件；4、long_slow_filter=admin,filesort,filesort_on_disk,full_join,full_scan；5、query_cache,query_cache_miss,tmp_table,tmp_table_on_disk #上述查询类型且查询时长超过long_query_time,则记录日志；6、log_queries_not_using_indexes=ON #不使用索引或使用全索引扫描，不论是否达到慢查询阀值的语句是否记录日志，默认OFF，即不记录；7、log_slow_verbosity=Qurey_plan..explain #记录内容；8、log_slow_queries=OFF #同slow_query_log,MariaDB 10.0/MySQL 5.6.1版本后已删除
慢查询分析工具：1、mysqldumpslow；2、profile
mysqldumpslow -s c -t 10 /data/mysql/slow.log
profile打开后会显示语句执行的详细过程：set profiling =ON；查看profile的值：show profiles;显示语句的详细执行步骤和时长：show profile for query；显示CPU使用情况：show profile cpu for query ;
二进制日志（备份）
生产中，其他的日志都可以不启用，但是二进制日志（备份）必须启用，二进制日志默认不开启，开启后默认存放在/var/lib/mysql/
二进制日志：1、记录导致数据改变或潜在导致数据改变的SQL语句；2、记录已提交的日志；3、不依赖与存储引擎类型；
功能：通过‘重放’日志文件中的事件来生成数据副本；
注意：建议二进制日志和数据文件分开放
二进制日志记录三种格式：1、基于‘语句’记录：statement，记录语句，默认模式（MariaDB 10.2.3版本以下），日志量较少；2、基于‘行’记录：row，记录数据，日志量较大，更加安全，建议使用该格式；3、混合模式：mixed，让系统自行判定该基于哪种方式进行，默认模式（MariaDB 10.2.4及版本以上）；查看格式配置：show variables like 'binlog_format'；
二进制日志文件构成：1、日志文件：mysql|mariadb-bin.文件名后缀，二进制格式，如：mariadb-bin.000001;mariadb-bin.000002；2、索引文件：mysql|mariadb-bin.index，文本格式，记录当前已有的二进制日志文件列表
注意：二进制文件开启，需要sql_log_bin=ON或1和log_bing不写（须在mysql配置文件中/etc/my.cnf.d/mariadb-server.cnf修改）开启才算开启，其中一个值未开启，则不开启二进制文件
命令
查看二进制文件：show {binary |MASTER} logs;
在线查看二进制文件中的指定内容：show binlog events [in 'log_name'] [from pos] [limit [offset,] row_count]；范例：show binlog events in 'mysql-bin.000001' from 6516 limit 2,3；
mysqlbinlog支持离线观察数据库的二进制文件：
命令格式说明：mysqlbinlog [options] log_file
--start-positon= #指定开始位置；--stop-position= ;--start-datetime= #时间格式：YYYY-MM-DD hh:mm:ss；--stop-datetime= ；--base64-output [=name]；-v -vvv；范例：mysqlbinlog --start-positon=678 --stop-position=752 /var/lib/mysql/mariadb-bin.000003 -v ；mysqlbinlog --stop-datetime="2019-01-30 20:30:10" --stop-datetime="2019-01-30 20:35:10" mariadb-bin.000003 -vvv
生成新的二进制命令（切换新的日志文件）：1、mysqladmin flush logs；2、mysqladmin flush-binary-log;
清除指定的二进制日志：purge {binary|master} logs {to 'log_name' | before datetime_expr}；范例：purge binary logs to 'mariadb-bin.000003';#删除mariadb-bin.000003之前的日志；purge binary logs before '2019-01-30'；purge binary logs before "2019-01-30 20:35:10"；
删除所有二进制日志，index文件重新计数：reset master [to #] #删除所有二进制日志文件，并重新生成日志文件，文件名从#开始计算，默认从1开始计算，以便是master主机第一次启动时执行，mariadb 10.1.6开始支持to #；
三、主从复制及主主复制的实现
主从复制
主节点配置：
1、启用：log_bin；说明：启用二进制日志
2、为当前节点设置一个全局唯一的ID号：server-id=#；说明sever-id的取值范围：第一种：1 to 4294967295(>=mariaDB 10.2.2)默认值为1；第二种：0 to 4294967295(>=mariaDB 10.2.1)，默认值为0，如果从节点为0，所有master都讲拒绝slave的连接；
3、查看主节点从哪里二进制的文件和位置开始进行复制：show master logs;
4、创建有复制权限的用户账号：grant replication slave on *.* to 'repluser'@'host' identified by 'replass'；
从节点配置：
1、启动中继日志：
server_id=# 说明：为当前节点设置一个全局唯一的ID号
log_bin 说明：启用二进制日志
read_only=ON 说明：设置数据库只读，针对supper user无效
relay_log=relay-log 说明：relay log的文件路径，默认值hostname-relay-bin
relay_log_index=relay-log.index 说明：默认值hostname-relay-bin.index
2、使用有复制权限的用户账号连接至主服务器，并启动复制线程
grant master to
master_host='masterhost', 说明：主节点地址
master_user='repluser', 说明：复制权限的用户账号
master_password='replass', 说明：复制权限的用户账号密码
master_port=3306, 说明：端口
master_log_file='mariadb-bin.xxxxx', 说明：主节点的二进制文件名（log_name）
master_log_pos=#; 说明：主节点的二进制（file_size）
start slave; 说明：从节点默认情况下是没有开启IO线程和sql线程的，如果需要开启这两个线程，则需要执行START SLAVE则默认开启这两个线程。
show slave status; 说明：查看slave状态；从节点start slave后，主节点会出现多一个binlog dump线程；从节点则多两个线程，一个I/O线程，一个sql线程；Seconds_behind_master这个值表示的是从节点需要多少时间复制主节点的内容，这个值最好为0，如果是3600秒表示需要1个小时才能复制完成主节点的内容；
主主复制
主主复制：两个节点，都可以更新数据，并且互为主从
容易产生的问题：数据不一致
针对数据不一致可以考虑：自动增长id，但是这只是在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、两个主各自定义自动增长字段的数值范围各为奇偶
5、均把对方指定为主节点，并启动复制线程
四、xtrabackup实现全量+增量+binglog恢复库
xtrabackup
xtrabackup安装：yum install percona-xtrabackpup
xtrabackup工具的备份还原准备：1、备份对数据库做完全备份或增量备份；2、预准备：还原前，先对备份的数据，整理至一个临时目录；3、还原：将整理好的数量，复制回数据库目录中
一：新版xtrabackup完全备份及还原（基于Centos8 的mysql5.7实现，也支持mysql5.5和mariadb5.5）
1、安装xtrabackup包：yum -y install percona-xtrabackpup-xxxx.rpm
2、在原主机做完全备份到 /backup ;/backup目录不需要先创建
xtrabackup -uroot -ptest --backup --target-dir=/backup/
目标主机无需创建/backup目录，直接复制目录本身
scp -r /backup/ 目标主机:/
3、在目标主机上还原
1）预准备：确保数据一致，提交完成的事务，回滚未完成的事务
xtrabackup --prepare --target-dir=/backup/
2)复制到数据库目录；注意：数据库目录必须为空，mysql服务不能启动
xtrabackup --copy--back -target-dir=/backup/
3)还原属性
chown -R mysql:mysql /var/lib/mysql
4)service mysqld start
二：旧版xtrabackup完全备份及还原
1、在源主机备份
innoxtrabackupex --user=root /backup
scp -r /backup/ 目标主机：/data/
2、在目标主机预准备并还原
预准备：innoxtrabackupex --apply-log /data
还原过程：
systemctl stop mariadb
rm -rf /var/lib/mysql/*
innoxtrabackupex --copy-back /data/
chown -R mysql.mysql /var/lib/mysql/
systemctl start mariadb
增量备份
新版xtrabackup完全，增量备份及还原
1、备份过程
1）完全备份：
mkdir /backup/
xtrabackup -uroot -ptest --backup --target-dir=/backup/base
2）第一次修改数据
3）第一次增量备份
xtrabackup -uroot -ptest --backup --target=dir=/backup/inc1 --incremental-basedir=/backup/base
4）第二次修改数据
5）第二次增量
xtrabackup -uroot -ptest --backup --target=dir=/backup/inc2 --incremental-basedir=/backup/inc1
6）scp -r /backup/* 目标主机：/backup/
说明：备份过程中生成三个备份目录
/backup/{base,inc1,inc2}
2、还原过程
1）预准备完成备份，此选项--apply-log-only 阻止回滚未完成的事务
xtrabackup --prepare --apply-log-only --target-dir=/backup/base
2）合并第一次增量备份到完全备份
xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1
3）合并第二次增量备份到完全备份；最后一次还原不需要加选项--apply-log-only
xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2
4）复制到数据库目录，注意数据库目录必须为空，mysql服务不能启动
xtrabacup --copy-back --target-dir=/backup/base
5）还原属性：chown -R mysql:mysql /var/lib/mysql
6）启动服务：service msyqld start
