innodb存储引擎简介
1.innodb核心特性
MVCC
事务
行级锁 #innodb支持行级锁,myiasm支持表级锁
热备份 #innodb支持热备,myisam不支持热备
自动故障恢复 Crash Safe Recovery
2.存储引擎相关命令
1.查看当前数据库配置的存储引擎
mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB |
+--------------------------+
1 row in set (0.00 sec)
2.查看表的存储引擎
# 查看哪些表时innodb存储引擎
mysql> select TABLE_SCHEMA,TABLE_Name,ENGINE from tables where ENGINE='innodb';
# 查看哪些表时myisam存储引擎
mysql> select TABLE_SCHEMA,TABLE_Name,ENGINE from tables where ENGINE='myisam';
# 查看表信息
mysql> select * from information_schema.tables where table_name='test11'\G
# 查看指定表的存储引擎
mysql> show create table city;
# 查看列信息
mysql> select * from COLUMNS where COLUMN_NAME='name'\G
3.修改存储引擎
# 临时设置(会话级别)
mysql> set @@default_storage_engine=myisam;
Query OK, 0 rows affected (0.00 sec)
# 临时设置(全局级别)
mysql> set global @@default_storage_engine=myisam;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| MyISAM |
+--------------------------+
1 row in set (0.00 sec)
# 永久修改
[root@db03 mysql]# vim /etc/my.cnf
[mysqld]
default_storage_engine=myisam
# 建表时指定存储引擎
mysql> create table innodb(id int) ENGINE=innodb;
3.修改一个表的存储引擎
db01 [oldboy]>alter table t1 engine innodb;
注意:此命令我们经常使用他,进行innodb表的碎片整理
# 平常处理过的MySQL问题--碎片处理
环境:centos7.4,MySQL 5.7.20,InnoDB存储引擎
业务特点:数据量级较大,经常需要按月删除历史数据.
问题:磁盘空间占用很大,不释放
处理方法:
以前:将数据逻辑导出,手工drop表,然后导入进去
现在:
对表进行按月进行分表(partition,中间件)
业务替换为truncate方式
# 扩展:如何批量修改
需求:将zabbix库中的所有表,innodb替换为tokudb
select concat("alter table zabbix.",table_name," engine tokudb;") from
information_schema.tables where table_schema='zabbix' into outfile '/tmp/tokudb.sql';
4.案例
1.项目背景
公司原有的架构:一个展示型的网站,LAMT,MySQL5.1.77版本(MYISAM),50M数据量。
2.升级原因
1、表级锁:对表中任意一行数据修改类操作时,整个表都会锁定,对其他行的操作都不能同时进行。
2、不支持故障自动恢复(CSR):当断电时有可能会出现数据损坏或丢失的问题。
3.解决方案
1.提建议将现有的MYISAM引擎替换为Innodb,将版本替换为5.6.38
1)如果使用MYISAM会产生”小问题”,性能安全不能得到保证,使用innodb可以解决这个问题。
2)5.1.77版本对于innodb引擎支持不够完善,5.6.38版本对innodb支持非常完善了。
4.执行计划
1.准备一台新的数据库,版本为5.6.38
2.就数据库备份数据
[root@db01 ~]# mysqldump -uroot -p123 --triggers -R --master-data=2 -B >/tmp/full.sql
3.将备份的数据库传到新数据库
scp、rsync、NFS、导出导入、sftp
4.修改sql中的存储引擎
sed -i 's#MyISAM#InnoDB#g' /tmp/full.sql
5.将修改后的sql文件导入新数据
mysql < /tmp/full.sql
source
\.
6.将代码中的数据库地址修改为新的数据库地址
7.通过binlog将数据迁移过程中新生成的数据也倒入新库