1、总结mysql常见的存储引擎以及特点。
MyISAM引擎
MyISAM引擎特点
- 不支持事务
- 表级锁定
- 读写相互阻塞,写入不能读,读时不能写
- 只缓存索引
- 不支持外搜约束
- 不支持聚簇索引
- 读取数据较快,占用资源较少
- 不支持MVCC(多版本并发控制机制)高并发
- 崩溃恢复性较差
MyISAN存储引擎适用场景
- 只读(或者写较少)
- 表较小(可以接受长时间进行修复操作)
MyISAM引擎文件
- tbl_name.frm 表格式定义
- tbl_name.MYD 数据文件
- tbl_name.MYI 索引文件
InnoDB引擎
InnoDB引擎特点
- 行级锁
- 支持事务,适合处理大量短期事务
- 读写阻塞与事务隔离级别相关
- 可缓存数据和索引
- 支持聚簇索引
- 崩溃恢复性更好
- 支持MVCC高并发
- 从MySQL5.5后支持全文索引
- 从MySQL5.5.5开始为默认的数据库引擎
InnoDB数据库文件
- 数据文件:Ibdata1,ibdata2,存放在datadir定义目录下
- 表格式定义:tb_name.frm,存放在datadir定义的每个数据库对应的目录下
其他的存储引擎
memory
- 所有数据都保存在内存中
- Memory 表的结构在重启以后还会保留,但数据会丢失
archive
- 针对高写入压缩做了优化的简单引擎
- 适合日志和数据采集应用
blackhole
- 没有实现任何存储机制,它会舍弃所有写入数据,但是服务器会记录 Blackhole 表的日志。(不推荐)
federated
- 访问其他 MySQL 服务器的一个代理,默认禁用。
2、MySQL日志各类总结。
事务日志:transaction log
- Redo log:实现WAL(Write Ahead Log),数据更i性能前先记录redo log
- Undo log:保存与执行的操作相反的操作,用于实现rollback
事务日志性能优化:
- 1 次维默认值,日志缓冲区将写入日志文件,并在每次事务后执行刷新到磁盘。
- 0 提交时没有写磁盘的操作;而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。这样可提供更好的性能,但服务器崩溃可能丢失最后一秒的事务
- 2 每次提交后都会写入OS的缓冲区,但每秒次才会进行一次刷新到磁盘文中。性能比0略差一些,但操作系统活停电可能导致最后一秒的交易丢失。
>show variables like ‘%innodb_log%’;
>show variables like ‘innodb_flush_log_at_trx_commit’;
错误日志:
- mysqld启动和关闭过程中输出的事件信息
- mysqld运行中产生的错误信息
- Event scheduler 运行一个event时产生的日志信息
- 在主从复制架构中的从服务器上启动从服务器线程时产生的信息
- Log_error,Log_warnings
>show global variables like ‘log_error’;
通用日志
- 通用日志:记录对数据库的通用操作,包括:错误的SQL语句
- 通用日志可以保存在:file(默认值)或table
- 通用日志相关设置
General_log=ON|OFF
General_log_file=HOSTNAME.log
Log_output=TABLE|FILE|NONE
# vim /etc/my.cnf.d/server.cnf
慢查询日志
- 慢查询日志:记录执行查询时长超出指定时长的操作
- 慢查询相关变量
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,tmp_talbe_on_disk
Log_queries_not_using_indexes=ON
Log_slow_rate_limit=1
Log_slow_verbosity=Query_plan,explain
Log_slow_queries=OFF
- 慢查询分析工具mysqldumpslow,profile
二进制日志(备份)
- 记录导致数据改变或潜在导致数据改变的SQL语句
- 记录已提交的日志
- 不依赖于存储引擎类型
- 功能:通过“重放”日志文件中的事件来生成数据副本
- 注意:建议二进制日志和数据文件分开存放
二进制日志记录三种格式
- 基于“语句”记录:statement,记录语句,默认模式,日志量较少
- 基于“行”记录:row,记录数据,日志量较大,更加安全,建议适用的格式
- 混合模式:mixed,让系统自行判定该基本那种方式进行,默认模式。
二进制日志文件的构成
- 日志文件
- 索引文件
二进制日志相关的服务器变量:
Sql_log_bin=ON|OFF;
Log_bin=/PATH/BIN_LOG_FILE;
Binlog_format=STATEMENT|ROW|MIXED;
Max_binlog_size=1073741824;
Binlog_cache_size=4m
Max_binlog_cache_size=512m
Sync_binlog=1|0;
Expire_logs_days=N;
二进制日志管理工具
Mysqlbinlog,
>show variables like ‘binlog_format’;
# vim /etc/my.cnf.d/server.cnf
3、主从复制及主主复制的实现
主从复制的作用
- 做数据的热备,作为后备数据库,主数据库服务器故障后,可切换到从数据库继续工作,避免数据丢失。
- 架构的扩展。业务量越来越大,I/O访问频率过高,单机无法满足,此时做多库的存储,降低磁盘I/O访问的频率,提高单个机器的I/O性能。
- 读写分离,使数据库能支撑更大的并发。在报表中尤其重要。由于部分报表sql语句非常的慢,导致锁表,影响前台服务。如果前台使用master,报表使用slave,那么报表sql将不会造成前台锁,保证了前台速度
主从复制及其原理
- 什么是主从复制?
主从复制,是用来建立一个和主数据库完全一样的数据库环境,称为从数据库;主数据库是业务数据库, 从库相当于主库的备份。
- 主从复制原理(实现过程)
步骤一:主库db的更新事件(update、insert、delete)被写到主库的binlog
步骤二:从库发起连接,连接到主库
步骤三:此时主库创建一个binlog dump 线程,把binlog的内容发送到从库
步骤四:从库启动之后,创建一个I/O线程,读取主库传过来的binlog内容并写入到relay log
步骤五:从还会创建一个SQL线程,从relaylog里面读取内容,从Exec_Master_Log_Pos位置开始执行读取到的更新事件,将更新内容写入到slave的db
简单来说就是:主库db的更新事件被写入到主库的bin-log;从库读取主库的bin-log,并将bin-log的内容写到到自己的relad log;从库读取relady log,解析成sql语句,将更新的内容写到的从库的db。
- 主从复制存在的问题及解决办法
mysql主从复制存在的问题:
主库宕机后,数据可能丢失
从库只有一个sql Thread,主库写压力大,复制很可能延时
解决方法:
半同步复制—解决数据丢失的问题 (5.5集成到mysql,以插件的形式存在,需要单独安装。事务在主库写完binlog后需要从库返回一个已接受,才返回给客户端;确保事务提交后binlog至少传输到一个从库。但是性能有一定的降低。)
并行复制—-解决从库复制延迟的问题
MySQL主从复制搭建
- 1、一主一从
- 先准备两台服务器:主从
- 在两台服务器上面安装MySQL,启动服务并设置服务开机自起
- 在主服务器上授权,从服务器保存授权的信息之后在从服务器会产生授权信息文件master.info.
- 开启从服务器start slave
- 在主服务器上创建数据库,在从服务器上会同步出现;在从服务器上创建个表,在主服务器上不会查看到。
- 主主复制
- 先准备两台服务器:
- 配置主配置文件/etc/my.cnf:开启二进制日志和其他内容
- 重启服务
- 在主服务器A上授权,在从服务器B上保存授权信息:
- 在主服务器B授权,在从服务器A上保存授权信息:
- AB都执行start slave
4、xtrabackup实现全量+增量+binlog恢复库
- 安装
#yum install -y percona-xtrabackup*
- 进行备份操作
#xtrabackup -uroot --backup --target-dir =/backup/
#scp -r /backup/* root@172.16.0.71:/backup
- 恢复测试
#mkdir /backup
#scp -r root@172.16.0.71:/backup /backup/
# xtrabackup --prepare --target-dir=/backup/
# xtrabackup --copy-back --target-dir=/backup/
# chown -R mysql:mysql /var/lib/mysql
- 增量备份
# xtrabackup -uroot --backup --target-dir=/backup/base
# xtrabackup -uroot --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
- 增量备份恢复
#xtrabackup --prepare --apply-log-only --target-dir=/backup/base
#xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc1
# xtrabackup --copy-back --target-dir=/backup/base
# chown -R mysql:mysql /var/lib/mysql
# systemctl start mariadb