MySQL Storage engine conversion,Support mutual conversion between MyISAM and InnoDB engines.

Related tags

econvert
Overview

econvert

MySQL Storage engine conversion

简介

​ 此工具用于MySQL存储引擎转换,支持CTAS和ALTER两种模式,目前只支持MyISAM和InnoDB存储引擎相互转换,其它引擎尚不支持。

​ 注意:当对表进行引擎转换时,建议业务停止访问或者极少量访问时进行。

原理

​ CTAS模式会创建一个新表,然后把业务表数据insert到新表中。

​ ALTER是直接对业务表执行alter字句来进行转换。

GO版本要求

​ GO版本要求在1.10以上

依赖

module econvert

go 1.15

require (
	github.com/go-sql-driver/mysql v1.6.0
	golang.org/x/sys v0.0.0-20210902050250-f475640dd07b // indirect
)

​ 注意:构建时应打开go mod模式(set GO111MODULE=on),打开后将自动联网下载相关依赖包

编译

  1. 构建

$ go env -w GO111MODULE=on
$ go env -w GOPROXY=https://goproxy.io,direct
# 因为国内的网络限制,需要使用代理拉取相关依赖代码。

# ls -l
-rw-r--r--. 1 root root 21924 Sep  3 11:11 econvert.go
-rw-r--r--. 1 root root   265 Sep  3 11:11 go.mod
-rw-r--r--. 1 root root  1606 Sep  3 11:11 go.sum
-rw-r--r--. 1 root root   536 Sep  3 11:11 makefile
-rw-r--r--. 1 root root 14511 Sep  3 11:11 README.md

# make
go build -gcflags=all='-l -N' -ldflags " \
	-X 'main.Platform=linux amd64' \
	-X 'main.BuildTime=Fri Sep  3 11:14:13 EDT 2021' \
	-X 'main.GoVersion=go version go1.14 linux/amd64' \
	-X 'main.VERSION=1.02'" -o ../build/econvert/bin/econvert

​ 提示:环境及版本信息会写入到二进制文件,编译完成后不可更改

  1. 二进制文件

​ 编译完成后将在此目录中生成二进制执行文件。

# ls -l ../build/econvert/bin/econvert 
-rwxr-xr-x. 1 root root 6673893 Sep  3 11:14 ../build/econvert/bin/econvert

# ldd ../build/econvert/bin/econvert 
	linux-vdso.so.1 =>  (0x00007fff23dff000)
	libpthread.so.0 => /lib64/libpthread.so.0 (0x0000003a3a600000)
	libc.so.6 => /lib64/libc.so.6 (0x0000003a3a200000)
	/lib64/ld-linux-x86-64.so.2 (0x0000003a39a00000)

​ 注意:此执行文件不可以进行任何编辑,否则将发生损坏导致无法运行。

示例

  1. 转换ucds下所有表从MyISAM引擎到InnoDB引擎

#econvert -host 172.168.120.112 \
-user root \
-password 'root' \
-cdb ucds \
-fromengine myisam \
-toengine innodb \
-convert yes
  1. 转换ucds下所有表从MyISAM引擎到InnoDB引擎,并删除由于转换引擎时所产生的临时表,默认不删除。

#econvert -host 172.168.120.112 \
-user root \
-password 'root' \
-cdb ucds \
-fromengine myisam \
-toengine innodb \
-convert yes \
-clean yes
  1. 只对某一个表进行引擎转换

./econvert -host 172.168.120.112 \
-user root \
-password 'root' \
-cdb ucds \
-fromengine myisam \
-toengine innodb \
-convert yes \
-clean yes \
-table test01
  1. 基于全库排除某一个表

./econvert -host 172.168.120.112 \
-user root \
-password 'root' \
-cdb ucds \
-fromengine myisam \
-toengine innodb \
-convert yes \
-clean yes \
-exclude test02
  1. 选择转换引擎模式,默认是CTAS模式,CTAS模式会产生一个临时表,并且当业务进行时可能导致数据丢失。当使用CTAS模式时,默认情况下当表小于300M时强制使用ALTER模式来进行引擎转换。当配置-size 0 时,将会禁用强制alter模式,那么此时所有表都将采用CTAS模式来转换。-size参数只有当CTAS模式才有效,alter模式是无效的。

./econvert -host 172.168.120.112 \
-user root \
-password 'root' \
-cdb ucds \
-fromengine myisam \
-toengine innodb \
-convert yes \
-clean yes \
-exclude test02
-size 0
  1. 转换引擎失败后继续,默认情况下一旦发生失败,所有转换操作立即终止,例如期望当发生3次失败后终止,可使用如下参数。

./econvert -host 172.168.120.112 \
-user root \
-password 'root' \
-cdb ucds \
-fromengine myisam \
-toengine innodb \
-convert yes \
-clean yes \
-method alter \
-errcount 3
  1. 终端:重新加载表

+-----+--------------+--------------------------+------------+--------+---------+--------+----------+------------+
| SEQ | TABLE_SCHEMA |               TABLE_NAME | TABLE_TYPE | ENGINE | SIZE_MB | METHOD |    STATE |  WAIT_TIME |
+-----+--------------+--------------------------+------------+--------+---------+--------+----------+------------+
|   1 |         ucds |          lender_set_1001 | BASE TABLE | MyISAM |    1209 |        |    Ready |          0 |
|   2 |         ucds | ent_record_callback_info | BASE TABLE | MyISAM |     242 |        |    Ready |          0 |
|   3 |         ucds |              ipvs_tb1010 | BASE TABLE | MyISAM |      37 |        |    Ready |          0 |
|   4 |         ucds |              ipvs_tb1011 | BASE TABLE | MyISAM |      24 |        |    Ready |          0 |
|   5 |         ucds |                   test06 | BASE TABLE | MyISAM |      19 |        |    Ready |          0 |
|   6 |         ucds |                   test02 | BASE TABLE | MyISAM |      17 |        |    Ready |          0 |
|   7 |         ucds |                  test991 | BASE TABLE | MyISAM |      17 |        |    Ready |          0 |
|   8 |         ucds |                      bak | BASE TABLE | MyISAM |      11 |        |    Ready |          0 |
|   9 |         ucds |                   test10 | BASE TABLE | MyISAM |       2 |        |    Ready |          0 |
|  10 |         ucds |                   test09 | BASE TABLE | MyISAM |       2 |        |    Ready |          0 |
|  11 |         ucds |                   test08 | BASE TABLE | MyISAM |       2 |        |    Ready |          0 |
|  12 |         ucds |                   test13 | BASE TABLE | MyISAM |       2 |        |    Ready |          0 |
|  13 |         ucds |                   test12 | BASE TABLE | MyISAM |       2 |        |    Ready |          0 |
+-----+--------------+--------------------------+------------+--------+---------+--------+----------+------------+

The available command sets are as follows
 y    Perform storage engine conversion
 e    Exit storage engine conversion
 p    Re output engine conversion table
 c    Re output database configuration information
 r    Reload transformation engine table
 h    Output command information
 			------->> 如果此时test01表被删除了,我们可以在不退出进程的情况下输入r指令来重载表。
QNCLI> r
Table reloading is completed. A total of 12 tables are loaded


QNCLI> p	------->> 重载完成后可以输入p指令查看表集,此时将会发生test01表已经消失
Database table information statistics:
+-----+--------------+--------------------------+------------+--------+---------+--------+----------+------------+
| SEQ | TABLE_SCHEMA |               TABLE_NAME | TABLE_TYPE | ENGINE | SIZE_MB | METHOD |    STATE |  WAIT_TIME |
+-----+--------------+--------------------------+------------+--------+---------+--------+----------+------------+
|   1 |         ucds |          lender_set_1001 | BASE TABLE | MyISAM |    1209 |        |    Ready |          0 |
|   2 |         ucds | ent_record_callback_info | BASE TABLE | MyISAM |     242 |        |    Ready |          0 |
|   3 |         ucds |              ipvs_tb1010 | BASE TABLE | MyISAM |      37 |        |    Ready |          0 |
|   4 |         ucds |              ipvs_tb1011 | BASE TABLE | MyISAM |      24 |        |    Ready |          0 |
|   5 |         ucds |                   test06 | BASE TABLE | MyISAM |      19 |        |    Ready |          0 |
|   6 |         ucds |                   test02 | BASE TABLE | MyISAM |      17 |        |    Ready |          0 |
|   7 |         ucds |                      bak | BASE TABLE | MyISAM |      11 |        |    Ready |          0 |
|   8 |         ucds |                   test13 | BASE TABLE | MyISAM |       2 |        |    Ready |          0 |
|   9 |         ucds |                   test12 | BASE TABLE | MyISAM |       2 |        |    Ready |          0 |
|  10 |         ucds |                   test10 | BASE TABLE | MyISAM |       2 |        |    Ready |          0 |
|  11 |         ucds |                   test09 | BASE TABLE | MyISAM |       2 |        |    Ready |          0 |
|  12 |         ucds |                   test08 | BASE TABLE | MyISAM |       2 |        |    Ready |          0 |
+-----+--------------+--------------------------+------------+--------+---------+--------+----------+------------+

QNCLI> 

			------->> 如果此时创建了一个新表,同样也可以输入r键来重载。
QNCLI> r
Table reloading is completed. A total of 13 tables are loaded

QNCLI> p
Database table information statistics:
+-----+--------------+--------------------------+------------+--------+---------+--------+----------+------------+
| SEQ | TABLE_SCHEMA |               TABLE_NAME | TABLE_TYPE | ENGINE | SIZE_MB | METHOD |    STATE |  WAIT_TIME |
+-----+--------------+--------------------------+------------+--------+---------+--------+----------+------------+
|   1 |         ucds |          lender_set_1001 | BASE TABLE | MyISAM |    1209 |        |    Ready |          0 |
|   2 |         ucds | ent_record_callback_info | BASE TABLE | MyISAM |     242 |        |    Ready |          0 |
|   3 |         ucds |              ipvs_tb1010 | BASE TABLE | MyISAM |      37 |        |    Ready |          0 |
|   4 |         ucds |              ipvs_tb1011 | BASE TABLE | MyISAM |      24 |        |    Ready |          0 |
|   5 |         ucds |                   test06 | BASE TABLE | MyISAM |      19 |        |    Ready |          0 |
|   6 |         ucds |                   test02 | BASE TABLE | MyISAM |      17 |        |    Ready |          0 |
|   7 |         ucds |                  test991 | BASE TABLE | MyISAM |      17 |        |    Ready |          0 |
|   8 |         ucds |                      bak | BASE TABLE | MyISAM |      11 |        |    Ready |          0 |
|   9 |         ucds |                   test10 | BASE TABLE | MyISAM |       2 |        |    Ready |          0 |
|  10 |         ucds |                   test09 | BASE TABLE | MyISAM |       2 |        |    Ready |          0 |
|  11 |         ucds |                   test08 | BASE TABLE | MyISAM |       2 |        |    Ready |          0 |
|  12 |         ucds |                   test13 | BASE TABLE | MyISAM |       2 |        |    Ready |          0 |
|  13 |         ucds |                   test12 | BASE TABLE | MyISAM |       2 |        |    Ready |          0 |
+-----+--------------+--------------------------+------------+--------+---------+--------+----------+------------+

QNCLI> 	
  1. 终端:其它指令

QNCLI> h			------->> h指令输出帮助信息
The available command sets are as follows
 y    Perform storage engine conversion
 e    Exit storage engine conversion
 p    Re output engine conversion table
 c    Re output database configuration information
 r    Reload transformation engine table
 h    Output command information

QNCLI> c			------->> c指令重新输出数据库配置信息
Database Connection Authentication Information:
      HOST: 172.168.120.112
      Port: 3306
      User: root
  PASSWORD: root
       CDB: ucds
FROMENGINE: myisam
  TOENGINE: innodb
   CONVERT: yes
     TABLE: 
   EXCLUDE: 
    METHOD: alter
ERRORCOUNT: 3
     CLEAN: yes
      SIZE: 300 (In CTAS mode, the table is less than 300MB, and the ALTER mode is forced)

QNCLI> y			------->> y指令执行引擎转换
SQL> alter table `ucds`.`lender_set_1001` engine=innodb;
Table altered. 6485000 rows affected (498439 Millisecond)

SQL> alter table `ucds`.`ent_record_borror_info` engine=innodb;
Table altered. 1297000 rows affected (208690 Millisecond)

SQL> alter table `ucds`.`ipvs_tb1010` engine=innodb;
Table altered. 1933312 rows affected (27352 Millisecond)

SQL> alter table `ucds`.`ipvs_tb1011` engine=innodb;
Table altered. 1933312 rows affected (52270 Millisecond)

SQL> alter table `ucds`.`test06` engine=innodb;
Table altered. 165888 rows affected (28560 Millisecond)

SQL> alter table `ucds`.`test02` engine=innodb;
Table altered. 147456 rows affected (20803 Millisecond)

SQL> alter table `ucds`.`test991` engine=innodb;
Table altered. 147456 rows affected (21911 Millisecond)

SQL> alter table `ucds`.`bak` engine=innodb;
Table altered. 92160 rows affected (18722 Millisecond)

SQL> alter table `ucds`.`test10` engine=innodb;
Table altered. 18432 rows affected (14028 Millisecond)

SQL> alter table `ucds`.`test09` engine=innodb;
Table altered. 18432 rows affected (2724 Millisecond)

SQL> alter table `ucds`.`test08` engine=innodb;
Table altered. 18432 rows affected (11810 Millisecond)

SQL> alter table `ucds`.`test13` engine=innodb;
Table altered. 18432 rows affected (2462 Millisecond)

SQL> alter table `ucds`.`test12` engine=innodb;
Table altered. 18432 rows affected (2616 Millisecond)

Database table information statistics:
+-----+--------------+--------------------------+------------+--------+---------+--------+----------+------------+
| SEQ | TABLE_SCHEMA |               TABLE_NAME | TABLE_TYPE | ENGINE | SIZE_MB | METHOD |    STATE |  WAIT_TIME |
+-----+--------------+--------------------------+------------+--------+---------+--------+----------+------------+
|   1 |         ucds |          lender_set_1001 | BASE TABLE | MyISAM |    1209 |  ALTER |  SUCCESS |     498439 |
|   2 |         ucds | ent_record_callback_info | BASE TABLE | MyISAM |     242 |  ALTER |  SUCCESS |     208690 |
|   3 |         ucds |              ipvs_tb1010 | BASE TABLE | MyISAM |      37 |  ALTER |  SUCCESS |      27352 |
|   4 |         ucds |              ipvs_tb1011 | BASE TABLE | MyISAM |      24 |  ALTER |  SUCCESS |      52270 |
|   5 |         ucds |                   test06 | BASE TABLE | MyISAM |      19 |  ALTER |  SUCCESS |      28560 |
|   6 |         ucds |                   test02 | BASE TABLE | MyISAM |      17 |  ALTER |  SUCCESS |      20803 |
|   7 |         ucds |                  test991 | BASE TABLE | MyISAM |      17 |  ALTER |  SUCCESS |      21911 |
|   8 |         ucds |                      bak | BASE TABLE | MyISAM |      11 |  ALTER |  SUCCESS |      18722 |
|   9 |         ucds |                   test10 | BASE TABLE | MyISAM |       2 |  ALTER |  SUCCESS |      14028 |
|  10 |         ucds |                   test09 | BASE TABLE | MyISAM |       2 |  ALTER |  SUCCESS |       2724 |
|  11 |         ucds |                   test08 | BASE TABLE | MyISAM |       2 |  ALTER |  SUCCESS |      11810 |
|  12 |         ucds |                   test13 | BASE TABLE | MyISAM |       2 |  ALTER |  SUCCESS |       2462 |
|  13 |         ucds |                   test12 | BASE TABLE | MyISAM |       2 |  ALTER |  SUCCESS |       2616 |
+-----+--------------+--------------------------+------------+--------+---------+--------+----------+------------+

参数说明

  1. 参数帮助信息

Usage of ../build/econvert/bin/econvert:
  -cdb string
    	Specify the DB to be converted
  -clean string
    	Clear temporary table (default "no")
  -convert string
    	Perform engine conversion? (default "no")
  -errcount string
    	Stop running after reaching the number of times, 0 disabled (default "0")
  -exclude string
    	Specify excluded tables, multiple tables are separated by commas
  -fromengine string
    	Specify conversion source engine (default "myisam")
  -host string
    	Specify the database host address (default "localhost")
  -method string
    	Select engine conversion method. Available values: CTAS, ALTER (default "CTAS")
  -password string
    	Specify database authentication password
  -port int
    	Specify the database host port (default 3306)
  -size string
    	Force alter when the table size is MB?. Only when method is CTAS (default "300")
  -table string
    	Specify the tables to be converted. Multiple tables are separated by commas
  -toengine string
    	Specify conversion target engine (default "innodb")
  -user string
    	Specify database authentication user (default "root")
  1. 终端指令说明

 y    Perform storage engine conversion
 e    Exit storage engine conversion
 p    Re output engine conversion table
 c    Re output database configuration information
 r    Reload transformation engine table
 h    Output command information
go mysql driver, support distributed transaction

Go-MySQL-Driver A MySQL-Driver for Go's database/sql package Features Requirements Installation Usage DSN (Data Source Name) Password Protocol Address

Open Transaction 26 Sep 21, 2021
A MySQL-compatible relational database with a storage agnostic query engine. Implemented in pure Go.

go-mysql-server go-mysql-server is a SQL engine which parses standard SQL (based on MySQL syntax) and executes queries on data sources of your choice.

DoltHub 622 Sep 21, 2021
MySQL Binlog 增量实时同步工具

简介 go-mysql-transfer是一款MySQL数据库实时增量同步工具。 能够监听MySQL二进制日志(Binlog)的变动,将变更内容形成指定格式的消息,实时发送到接收端。从而在数据库和接收端之间形成一个高性能、低延迟的增量数据同步更新管道。 特性 1、简单,不依赖其它组件,一键部署 2、

wangjie 439 Sep 13, 2021
a powerful mysql toolset with Go

go-mysql A pure go library to handle MySQL network protocol and replication. Call for Committer/Maintainer Sorry that I have no enough time to maintai

siddontang 3.2k Sep 23, 2021
Sync MySQL data into elasticsearch

go-mysql-elasticsearch is a service syncing your MySQL data into Elasticsearch automatically. It uses mysqldump to fetch the origin data at first, the

siddontang 3.6k Sep 24, 2021
Universal command-line interface for SQL databases

usql A universal command-line interface for PostgreSQL, MySQL, Oracle Database, SQLite3, Microsoft SQL Server, and many other databases including NoSQ

XO 6.8k Sep 25, 2021
db-recovery is a tool for recovering MySQL data.

db-recovery is a tool for recovering MySQL data. It is used in scenarios where the database has no backup or binlog. It can parse data files and redo/undo logs to recover data.

null 17 Sep 23, 2021
Zero boilerplate database operations for Go

(Now compatible with MySQL and PostgreSQL!) Everyone knows that performing simple DATABASE queries in Go takes numerous lines of code that is often re

null 316 Sep 8, 2021
A Go rest API project that is following solid and common principles and is connected to local MySQL database.

This is an intermediate-level go project that running with a project structure optimized RESTful API service in Go. API's of that project is designed based on solid and common principles and connected to the local MySQL database.

Kıvanç Aydoğmuş 11 Sep 7, 2021
Interactive terminal user interface and CLI for database connections. MySQL, PostgreSQL. More to come.

?? dbui dbui is the terminal user interface and CLI for database connections. It provides features like, Connect to multiple data sources and instance

Kanan Rahimov 51 Sep 15, 2021
Golang MySql binary log replication listener

Go MySql binary log replication listener Pure Go Implementation of MySQL replication protocol. This allow you to receive event like insert, update, de

Pavel <Ven> Gulbin 174 Jul 14, 2021
OctoSQL is a query tool that allows you to join, analyse and transform data from multiple databases and file formats using SQL.

OctoSQL OctoSQL is a query tool that allows you to join, analyse and transform data from multiple databases, streaming sources and file formats using

Jacob Martin 2.5k Sep 15, 2021
database to golang struct

中文文档 mysql database to golang struct conversion tools base on gorm(v1/v2),You can automatically generate golang sturct from mysql database. big Camel-

xxj 1.4k Sep 19, 2021
A flexible and powerful SQL string builder library plus a zero-config ORM.

SQL builder for Go Install Usage Basic usage Pre-defined SQL builders Build SQL for MySQL, PostgreSQL or SQLite Using Struct as a light weight ORM Nes

Huan Du 633 Sep 20, 2021
Bifrost ---- 面向生产环境的 MySQL 同步到Redis,MongoDB,ClickHouse,MySQL等服务的异构中间件

Bifrost ---- 面向生产环境的 MySQL 同步到Redis,ClickHouse等服务的异构中间件 English 漫威里的彩虹桥可以将 雷神 送到 阿斯加德 和 地球 而这个 Bifrost 可以将 你 MySQL 里的数据 全量 , 实时的同步到 : Redis MongoDB Cl

brokerCAP 871 Sep 18, 2021
CLI tool that can execute SQL queries on CSV, LTSV, JSON and TBLN. Can output to various formats.

trdsql CLI tool that can execute SQL queries on CSV, LTSV, JSON and TBLN. It is a tool like q, textql and others. The difference from these tools is t

Noboru Saito 637 Sep 21, 2021
Podman based development-only dependency manager for Linux.

Tent is a CLI tool for running development dependencies such as MySQL, Mongo, ElasticSearch etc inside pre-configured containers using simple one

Farhan Hasin Chowdhury 95 Sep 19, 2021
Innotop for MySQL 8 written in Go

Innotop Go Innotop for MySQL 8 written in Go Project started to learn Go and doing something useful (I hope). Additionaly the official Innotop written

Frédéric Descamps 13 Jul 16, 2021
write APIs using direct SQL queries with no hassle, let's rethink about SQL

SQLer SQL-er is a tiny portable server enables you to write APIs using SQL query to be executed when anyone hits it, also it enables you to define val

Mohammed Al Ashaal 1.9k Sep 15, 2021