mysql 从5.1升级到5.5.33 后 innodb 表出错 及 innodb表修复
服务器使用的是mysql 5.1,了解到 5.5 系列的版本 innodb 的性能有很大提升,就想升级下。按照查到的步骤:
http://www.myhack58.com/Article/sort099/sort0102/2013/39006.htm
<?php// 数据库文件路径:/usr/local/mysql/var | /home/dbjg 目录存放各个表 Create 语句$dbname = trim($argv[1]); $dbname == '' && exit('no db');$rootPass = ''; // root 密码$bkPath = ""; // .ibd 备份所在路径,不能是现在的数据库存放路径里面!file_exists("/home/dump") && mkdir("/home/dump");!file_exists("/home/temp") && mkdir("/home/temp");$ffs = glob("$bkPath/*.ibd"); `unalias cp`;foreach ($ffs as $ff) {$a = str_replace("$bkPath/", '', $ff);$a = str_replace('.ibd', '', $a); if (file_exists("/home/dump/$a.sql")) continue;`service mysql stop`;`rm -rf /usr/local/mysql/var/*.err /usr/local/mysql/var/ib_* /usr/local/mysql/var/ibdata1 /usr/local/mysql/var/$dbname`; /* */`service mysql start`;`mysql -uroot -p$rootPass -e "CREATE DATABASE $dbname;"`;`mysql -uroot -p$rootPass $dbname < /home/dbjg/$a.sql`;run("ALTER TABLE $a DISCARD TABLESPACE");`cp -f $bkPath/$a.ibd /usr/local/mysql/var/$dbname/`; `chown -cR mysql:mysql /usr/local/mysql/*`; /* */run("ALTER TABLE $a IMPORT TABLESPACE");`mv -f /usr/local/mysql/var/$dbname/$a.ibd /home/temp/`;$txt = `cat /usr/local/mysql/var/*.err`; /* */!preg_match("{ are (\d+) and 0, but in the InnoDB}is", $txt, $r) && exit("$a id not found");echo "$a id {$r[1]}\n";run("DROP TABLE $a");if ($r[1] > 2) {$need = $r[1] - 2; echo "add $need tables\n";system("for i in `seq 1 $need`; do mysql -uroot -p$rootPass $dbname -e \"CREATE TABLE t\$i (id int) ENGINE=InnoDB;\"; done");}run("DROP DATABASE $dbname");`mysql -uroot -p$rootPass -e "CREATE DATABASE $dbname;"`;`mysql -uroot -p$rootPass $dbname < /home/dbjg/$a.sql`;run("ALTER TABLE $a DISCARD TABLESPACE");`cp -f /home/temp/$a.ibd /usr/local/mysql/var/$dbname/`; `chown -cR mysql:mysql /usr/local/mysql/*`; /* */run("ALTER TABLE $a IMPORT TABLESPACE");`mysqldump -uroot -p$rootPass $dbname --table $a > /home/dump/$a.sql`; echo "\n";}function run($sql){global $dbname, $rootPass; `mysql -uroot -p$rootPass $dbname -e "$sql;"`;}