存储引擎操作类命令

存储引擎操作类命令

5.1 使用 SELECT 确认会话存储引擎

SELECT @@default_storage_engine;

mysql> show variables like '%engine%';

默认存储引擎设置(不代表生产操作)

会话级别:

set default_storage_engine=myisam;

全局级别(仅影响新会话):

set global default_storage_engine=myisam;

重启之后,所有参数均失效.

如果要永久生效:

写入配置文件

vim /etc/my.cnf

[mysqld]

default_storage_engine=myisam

存储引擎是表级别的,每个表创建时可以指定不同的存储引擎,但是我们建议统一为innodb.

扩展：

在线修改MySQL参数：

会话级别，例如：

set default_storage_engine=myisam;

功能：只会影响到当前会话

全局级别，例如：

set global default_storage_engine=myisam;

功能: 不影响当前和历史会话，只影响到新开的会话

以上两种方法，在重启之后会失效，除非参数添加至my.cnf

5.3 SHOW 确认每个表的存储引擎：

SHOW CREATE TABLE City\G;

SHOW TABLE STATUS LIKE 'CountryLanguage'\G

5.4 INFORMATION_SCHEMA 确认每个表的存储引擎

[world]>select table_schema,table_name ,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema');

Master [world]>show table status;

Master [world]>show create table city;

5.5 修改一个表的存储引擎

db01 [oldboy]>alter table t1 engine innodb;

注意：此命令我们经常使用他，进行innodb表的碎片整理

生产需求：

将oldboy数据库下的所有1000表，存储引擎从MyISAM替换为innodb

select concat("alter table ",table_name," engine innodb;")

from information_schema.tables

where table_schema='oldboy'

into outfile '/tmp/alter.sql';

5.6 平常处理过的MySQL问题--碎片处理

环境:centos7.4,MySQL 5.7.20,InnoDB存储引擎

业务特点:数据量级较大,经常需要按月删除历史数据.

问题:磁盘空间占用很大,不释放

处理方法:

以前:将数据逻辑导出,手工drop表,然后导入进去

现在:

对表进行按月进行分表(partition,中间件)

业务替换为truncate方式

定期执行：

alter table t1 engine='innodb';

5.6 扩展:如何批量修改存储引擎

需求:将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';