MySQL更改大库大表存储引擎方案

2018-08-08 本文已影响0人其实我很dou

一. 概述

检查库中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

iostat命令详解: https://www.cnblogs.com/xiuluo--angel/p/7086637.html

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和后端服务线上机器配置和负载和测试环境相差不大,
预计导入时间和测试相差不大.