mysql 转换引擎脚本mysql_convert_table_format,myisam转成innodb方法

MyISAM与InnoDB是mysql目前比较常用的两个数据库存储引擎,MyISAM与InnoDB的主要的不同点在于性能和事务控制上。这里简单的介绍一下两者间的区别和转换方法:
MyISAM:MyISAM是MySQL5.5之前版本默认的数据库存储引擎。MYISAM提供高速存储和检索,以及全文搜索能力,适合数据仓库等查询频繁的应用。但不支持事务、也不支持外键。MyISAM格式的一个重要缺陷就是不能在表损坏后恢复数据。
InnoDB:InnoDB是MySQL5.5版本的默认数据库存储引擎,不过InnoDB已被Oracle收购,MySQL自行开发的新存储引擎Falcon将在MySQL6.0版本引进。InnoDB具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎,InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。尽管如此,但是InnoDB包括了对事务处理和外来键的支持,这两点都是MyISAM引擎所没有的。

mysql官方提供了一个批量转换存储引擎的脚本工具 mysql_convert_table_format,今天安装mysql 5.7.14时发现已经被移除掉了这个引擎转换脚本,具体是从哪个版本开始移除的不做深究,还能正常使用才是关键。

记得先安装DBI组件哦,  yum install perl-DBD-mysql

mysql数据库存储引擎转换

方法一: /usr/local/webserver/mysql/bin/mysql_convert_table_format xxxzzz --user=root --password=xxxzzz --socket=/tmp/mysql.sock

脚本代码:


#!/usr/bin/perl
# Copyright (c) 2000-2002, 2006, 2007 MySQL AB, 2009 Sun Microsystems, Inc.
# Use is subject to license terms.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA

# Convert given tables in a database to MYISAM

use DBI;
use Getopt::Long;

$opt_help=$opt_version=$opt_verbose=$opt_force=0;
$opt_user=$opt_database=$opt_password=undef;
$opt_host="localhost";
$opt_socket="";
$opt_engine="MYISAM";
$opt_port=0;
$exit_status=0;

GetOptions(
"e|engine|type=s" => \$opt_type,
"f|force" => \$opt_force,
"help|?" => \$opt_help,
"h|host=s" => \$opt_host,
"p|password=s" => \$opt_password,
"u|user=s" => \$opt_user,
"v|verbose" => \$opt_verbose,
"V|version" => \$opt_version,
"S|socket=s" => \$opt_socket,
"P|port=i" => \$opt_port
) || usage(0);

usage($opt_version) if ($#ARGV < 0 || $opt_help || $opt_version);

$opt_database=shift(@ARGV);

if (grep { /^$opt_engine$/i } qw(HEAP MEMORY BLACKHOLE))
{
print "Converting to '$opt_engine' would delete your data; aborting\n";
exit(1);
}

$connect_opt="";
if ($opt_port)
{
$connect_opt.= ";port=$opt_port";
}
if (length($opt_socket))
{
$connect_opt.=";mysql_socket=$opt_socket";
}

$dbh = DBI->connect("DBI:mysql:$opt_database:${opt_host}$connect_opt",
$opt_user,
$opt_password,
{ PrintError => 0})
|| die "Can't connect to database $opt_database: $DBI::errstr\n";

my @tables;

push(@ARGV, "%") if(!@ARGV);

foreach $pattern (@ARGV)
{
my ($sth,$row);
$sth=$dbh->prepare("SHOW TABLES LIKE ?");
$rv= $sth->execute($pattern);
if(!int($rv))
{
warn "Can't get tables matching '$pattern' from $opt_database; $DBI::errstr\n";
exit(1) unless $opt_force;
}
while (($row = $sth->fetchrow_arrayref))
{
push(@tables, $row->[0]);
}
$sth->finish;
}

print "Converting tables:\n" if ($opt_verbose);
foreach $table (@tables)
{
my ($sth,$row);

# Check if table is already converted
$sth=$dbh->prepare("show table status like '$table'");
if ($sth->execute && ($row = $sth->fetchrow_arrayref))
{
if (uc($row->[1]) eq uc($opt_engine))
{
print "$table already uses the '$opt_engine' engine; Ignored\n";
next;
}
}
print "converting $table\n" if ($opt_verbose);
$table=~ s/`/``/g;
if (!$dbh->do("ALTER TABLE `$table` ENGINE=$opt_engine"))
{
print STDERR "Can't convert $table: Error $DBI::errstr\n";
exit(1) if (!$opt_force);
$exit_status=1;
}
}

$dbh->disconnect;
exit($exit_status);
sub usage
{
my($version)=shift;
print "$0 version 1.1\n";
exit(0) if ($version);

print <<EOF;

Conversion of a MySQL tables to other storage engines

Usage: $0 database [table[ table ...]]
If no tables has been specifed, all tables in the database will be converted.
You can also use wildcards, ie "my%"

The following options are available:

-f, --force
Continue even if there is some error.

-?, --help
Shows this help

-e, --engine=ENGINE
Converts tables to the given storage engine (Default: $opt_engine)

-h, --host=HOST
Host name where the database server is located. (Default: $opt_host)

-p, --password=PASSWORD
Password for the current user.

-P, --port=PORT
TCP/IP port to connect to if host is not "localhost".

-S, --socket=SOCKET
Socket to connect with.

-u, --user=USER
User name to log into the SQL server.

-v, --verbose
This is a test specific option that is only used when debugging a test.
Print more information about what is going on.

-V, --version
Shows the version of this program.
EOF
exit(1);
}

至于是将Myisam转换成InnoDB还是InnoDB转换成Myisam关键在于$opt_engine="MYISAM";脚本默认是将InnoDB转换成Myisam

方法二:如果要更改整个数据库表的存储引擎,采用先把数据库导出,得到SQL,把MyISAM全部替换为INNODB,再导入数据库的方式。

单表引擎转换

将wp_posts表修为InnoDB存储引擎(也可以此命令将InnoDB换为MyISAM):
mysql> ALTER TABLE wp_posts ENGINE=INNODB;

您可以选择一种方式赞助本站

支付宝转账赞助

支付宝扫一扫赞助

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

图片 表情