mysql 修改单表导入大小_MySQL更改大库大表存储引擎方案

一. 概述

检查库中myisam的表, sql如下:

SELECT * FROM `tables` WHERE table_schema = 'UAR_STATISTIC' AND ENGINE = 'MyISAM';

由于数据库UAR_STATISTIC有大部分表存储引擎是myisam, 部分表存储引擎是innodb, 需要将myisam引擎更改为innodb, 更改思路如下:

导出表结构sql

手动更改表结构sql, 将myisam改为innodb

导出表数据sql

删除原始数据库

导入表结构

导入表数据

其他库的myisam表很少, 使用alter语句更改即可,

二. 测试环境

主库 10.101.67.15

从库 10.101.67.13 10.101.67.14 10.10.32.20

MySQL版本 5.7.11

注意: 备份和恢复期间, 需要将后端服务停掉

三. 测试前服务器状态

free –m

top

iostat –d –k 1

io –d –k –x 1 4

mysql配置

server-id=13

max_allowed_packet = 1024M

max_connections=5000

interactive_timeout=28800

wait_timeout=28800

log_warnings=1

long_query_time=1

slow_query_log=1

expire_logs_days=15

user=uar

四. 备份数据库

备份表结构脚本并更改sql文件

/usr/local/mysql/bin/mysqldump -h'10.10.32.26' -uuar_db_user -p'xxx' -B

UAR_STATISTIC --no-data --set-gtid-purged=OFF > /root/db/table_stru.sql &

sed -i 's/MyISAM/INNODB/g' table_stru.sql

备份表数据命令(花费时间再6分钟左右)

/usr/local/mysql/bin/mysqldump -h'10.10.32.26' -uuar_db_user -p'xxx' -B

UAR_STATISTIC --no-create-info --no-create-db > /root/db/table_data.sql &

五. 删除原始数据库

drop database UAR_STATISTIC;

六. 导入表结构和数据

导入表结构

/usr/local/mysql/bin/mysql -uroot –pxxx -e'source /root/db/table_stru.sql'

>> stru.log

大约20s执行完成

导入表数据

/usr/local/mysql/bin/mysql -uroot -pxxx -e'source /root/db/table_data.sql'

>> /root/db/data.log &

记录服务器性能

top

记录磁盘性能(主库)

iostat -k -d 2 4

iostat -k -d -x 2 4

du -sh /usr/local/mysql/data/UAR_STATISTIC/

mysql> show processlist\G;

1. row

Id: 4

User: repl

Host: node1:9519

db: NULL

Command: Binlog Dump

Time: 1027

State: Master has sent all binlog to slave; waiting for more updates

Info: NULL

2. row

Id: 5

User: repl

Host: node2:47596

db: NULL

Command: Binlog Dump

Time: 1021

State: Master has sent all binlog to slave; waiting for more updates

Info: NULL

3. row

Id: 6

User: repl

Host: BD-APP-07:39140

db: NULL

Command: Binlog Dump

Time: 1016

State: Master has sent all binlog to slave; waiting for more updates

Info: NULL

4. row

Id: 12

User: root

Host: localhost

db: UAR_STATISTIC

Command: Query

Time: 0

State: update

Info: INSERT INTO \`app_at_appv_dev_day\` VALUES

('UAR-000184_358','v1.9.36','HTC+M8Et',20170816,15,1,2,103.

记录执行时间

start_time: Wed Aug 1 09:50:02 CST 2018

end_time: Wed Aug 1 23:24:01 CST 2018

sql文件大小: 15G SQL

备份数据时间: 6分钟

导入数据花费时间: 13小时34分钟

总花费时间: 13小时40分钟

分析: 导入这么慢, 应该是由于binlog日志写入以及同步从库导致, 瓶颈在于io,

数据导入时, 监控io, 基本属于满负载运行

七. 总结和风险

更改全部业务库的存储引擎为myisam, 导入数据总共大概需要13小时54分钟, 导入数据时,

需要停止web和后端服务线上机器配置和负载和测试环境相差不大,

预计导入时间和测试相差不大.