数据库（三）—— 数据库存储引擎、日志、备份、主从复制、高可用架构

数据库存储引擎、日志、备份、主从复制、高可用架构

一、存储引擎

1、存储引擎作用

存储引擎和磁盘上的数据交互

2、mysql存储引擎类型

Innodb存储引擎
	ibd：存储表的数据行和索引
	frm：表基本结构信息

Myisam存储引擎
	frm：表基本结构信息
	myi：存储索引
	myd：存储数据行

二、Innodb存储引擎特性

1、事务

Atomic（原子性）
	所有语句作为一个单元全部成功执行或全部取消。不允许出现中间过程.

Consistent（一致性）
	如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态。 

Isolated（隔离性）
	事务之间不相互影响。
	两个方面:  修改同一行 , 一致性读

Durable（持久性）
	事务成功完成后，所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。

（1）ACD特性的实现

对数据进行操作时，现将数据从数据库中读入内存，其中undo是记录操作以前的数据，redo是记录数据修改的记录，存入数据库。如果在修改数据时，断电，那么内存中的数据全部丢失，然而之前有没有提交。所以，只要结合redo和原始数据，即可恢复修改后的数据。实现了ACD特性。

（2）I特性的实现

行级锁：事务修改行，会锁定这行(持有这行的锁)

隔离级别：

RU    读未提交
RC    读已提交
RR    可重复读    默认
 S     串行化

2、事务的控制语句

begin;
xxx
xxx
commit;

begin;
xxx
xxx

begin;
xxxxx
xxx
rollback;

自动提交

autocommit = 1   # mysql 中默认是开启 事务，自动提交

三、日志

1、错误日志

mysql的错误日志存储在 /var/log/mysql.log

DDL、DCL ：以语句方式(statement)记录
DML(已提交的事务语句)：默认是以行模式记录(row模式，数据行的变化)

2、二进制日志

记录所有变更类的语句，可以做数据恢复和操作的审计

（1）配置日志

log_bin = /opt/mysql/data/mysql-bin
binlog_format = row
server_id = 5

（2）查看日志信息

mysql> show binary logs;
mysql> show master status;

（3）日志内容查看

# 按事件查看日志内容
mysql> show binlog events in 'mysql-bin.000012';

# 直接查看日志内容
mysqlbinlog --base64-output=decode-rows -vvv /opt/mysql/data/mysql-bin.000012 |more

（4）截取二进制日志

[root@standby data]# mysqlbinlog --start-position=219 --stop-position=186613 /opt/mysql/data/mysql-bin.000012 >/tmp/binlog.sql

3、慢日志

记录慢语句的日志文件，默认人关闭状态

slow_query_log_file=/opt/mysql/data/standby-slow.log     # 慢日志所在文件目录

slow_query_log=1     # 开启慢日志
long_query_time=1	   # 设置慢操作的时间，小于1s，视为慢操作
log_queries_not_using_indexes=1      # 不走索引的语句视为慢语句

使用Box Anemometer基于pt-query-digest将MySQL慢查询可视化

四、备份恢复

1、备份的种类

逻辑备份：SQL语句的备份
物理备份：数据页备份

2、mysql数据导入redis

# 将mysql数据导出到文件中
	# 将目标文件添加为安全文件，修改mysql配置文件
    secure-file-priv = /tmp
    # 导出
    mysql> select xxxx from t1  into outfile '/tmp/redis.txt'
    

# 一键将mysql数据导redis
mysql -uroot -p123 -e "select concat('hmset city_',id,' id ', id,' name ',name,' countrycode ',countrycode,' district ',district,' population ',population) from world.city limit 10 "|redis-cli

3、利用mysqldump备份

# -A 全库备份
mkdir backup
mysqldump -uroot -p123 -A >/backup/aaa.sql

# -B 备份指定数据库 
mysqldump -uroot -p123 -B world BBS >/backup/bbb.sql   # 备份world和BBS数据库

# 备份表
mysqldump -uroot -p123 BBS user article >/backup/ccc.sql   # 备份BBS库中的user和article表

4、备份时记录参数

--master-data=2       # 备份时记录二进制日志的状态
--single-transaction  # 开启innodb热备功能
-R
--triggers

# 完整的备份语句，带参数
mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R --triggers >/backup/aaa.sql

五、主从复制

1、主库开启二进制日志

vim /data/3307/my.cnf
	log_bin=/data/3307/mysql-bin

# 重启
[root@standby 3307]# systemctl restart mysqld3307

2、授权用户

[root@standby backup]# mysql -S /data/3307/mysql.sock
grant replication slave on *.* to repl@'10.0.0.%' identified by '123';

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

3、从库开启复制功能

[root@standby 3307]# mysql -S /data/3308/mysql.sock

mysql> CHANGE MASTER TO
  MASTER_HOST='10.0.0.200',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;


# 启动从库
mysql> start slave;
# 查看从库是否启动
mysql> show slave statusG
	# 成功，可看到
    Slave_IO_Running: Yes
	Slave_SQL_Running: Yes

六、”高可用“架构

1、MySQL高可用架构

（1）MHA：需要一主两从，自愈

（2）MGR

（3）galera

（4）PXC

2、高性能架构

（1）读写分离

（2）分布式架构