Mysql的存储引擎

关于mysql数据库的存储引擎

1. 有原生的9种。
    innodb, myisam, memory, csv等
2. 可以扩展的第三方的存储引擎
    rocksdb, myrocks, tokudb等（支持数据压缩，支持高速写入的一个引擎，但是不适合update多的场景）

1) 查看

# 查看mysql原生存储引擎
show engines;
# 查看mysql默认存储引擎
select @@default_storage_engine;
# 查看一个数据库下每张表的存储引擎
select table_schema,table_name,engine from information_schema.tables where table_schema=db_name;

2) 修改

# 修改表的存储引擎
alter table users engine=存储引擎名字;
# 查看是否修改成功
show create table table_name;

3) InnoDB和MyISAM的区别(msyql5.7)

1. InnoDB支持事务，外键，行级锁。MyISAM不支持
2. InnoDB的组成部分
    a. ibdata1： 存储数据字典，undo等
    b. ibd : 存储数据行，索引
    c. frm : 存储表的定义
3. MyISAM的组成部分
    a. MYD  : 存储表的数据
    b. MYI  : 存储表的索引
    c. frm  : 存储表的定义

4) 根据InnoDB特性恢复数据的情况

# 前提
1. innodb_file_per_table开启每个表都有自己的单独空间
2. 知道要恢复数据的表结构
3. 要有要恢复数据的ibd文件
# 步骤
1. 进入数据库创建一个和一样一模一样的表
    create table table_name(xxxx);
2. 删除刚刚创建的表的表空间
    alter table table_name discard tablespace;
3. 复制xx.idb数据到相应的数据库下
    cp -a xx.ibd  db_name/
4. 进入数据库导入数据
    alter table table_name import tablespace;
5. 检查数据
    desc table_name;
    select * from table_name limit 1;
    select count(*) from table_name;

5) 批量更换表的存储引擎

1. 单个修改
alter table table_name engine=存储引擎名字;
2. 批量修改
select concat("alter table ",table_schema,".",table_name," engine=tokudb;") from information_schema.tables where table_schema=db_name;

6) 批量删除表空间

1. 单个删除
 alter table table_name discard tablespace;
2. 单个导入
alter table table_name import tablespace;
3. 批量删除
select concat("alter table ",table_schema,".",table_name," discard tablespace;") from information_schema.tables where table_schema=db_name;
4. 批量导入
select concat("alter table ",table_schema,".",table_name," import tablespace;") from information_schema.tables where table_schema=db_name;

7) zabbix数据库数据恢复脚本 restore_zabbix_db_script.sh

#!/bin/bash
## 前提要先创建了所有的要恢复的表    mysql zabbix < zabbix_table.sql

tmp_file="/tmp/discard_import_table.txt"
ibd_dir="/opt/zabbix/"
data_dir="/data/mysql/3306/data/zabbix/"

echo "准备删除表空间"
# 删除创建的空表的表空间 
mysql -Nse 'select concat("alter table ",table_schema,".",table_name," discard tablespace;") from information_schema.tables where table_schema="zabbix";' > ${tmp_file}
while read line
do
    sleep 0.05
    mysql -Nse  "SET FOREIGN_KEY_CHECKS = 0; select sleep(0.05); $line ; select sleep(0.05); SET FOREIGN_KEY_CHECKS = 1;" > /dev/null 2>&1
done < ${tmp_file}

echo "准备移动ibd数据"
# 移动物理文件xx.ibd到数据库目录
cp -r ${ibd_dir}*.ibd ${data_dir}
chown -R mysql:mysql ${data_dir}

echo "准备import表数据"
# 导入表空间
mysql -Nse 'select concat("alter table ",table_schema,".",table_name," import tablespace;") from information_schema.tables where table_schema="zabbix";' > ${tmp_file}
while read line
do
    sleep 0.05
    mysql -Nse  "$line; select sleep(0.05);" > /dev/null 2>&1
done < ${tmp_file}

echo "恢复完成"