mysql4.0升级,mysql4.0升级到5.0

前段时间对数据库进行了升级，总结一下。

刚开始直接把4.0的数据直接拷贝到5.0的data目录下，发现很多表不能用，用mysqlcheck --all-databases --auto-repair,进入数据

库，看到似乎表都没有问题，但是仔细一看，就会发现有一些字段的长度发生了变化。如(int(10)－>int(5))。很奇怪，就问题

坛子里的兄弟，。以下是一位兄弟给出的解释。

Incompatible change: MySQL interprets length specifications in character column definitions in characters. (Earlier

versions interpret them in bytes.) For example, CHAR(N) means N characters, not N bytes.

For single-byte character sets, this change makes no difference. However, if you upgrade to MySQL 4.1 and configure

the server to use a multi-byte character set, the apparent length of character columns changes. Suppose that a 4.0

table contains a CHAR(8) column used to store ujis characters. Eight bytes can store from two to four ujis characters.

If you upgrade to 4.1 and configure the server to use ujis as its default character set, the server interprets

character column lengths based on the maximum size of a ujis character, which is three bytes. The number of three-byte

characters that fit in eight bytes is two. Consequently, if you use SHOW CREATE TABLE to view the table definition,

MySQL displays CHAR(2). You can retrieve existing data from the table, but you can only store new values containing up

to two characters. To correct this issue, use ALTER TABLE to change the column definition. For example:

ALTER TABLE tbl_name MODIFY col_name CHAR(8);

看来直接拷贝数据是不行了。接下来只能导出导入数据了。

写了一个脚本来升级。

#!/bin/sh

dbname=dbname    #要导出的数据库

mysql=/usr/local/mysql/bin/mysql

mysqldump=/usr/local/mysql/bin/mysqldump

myisamchk=/usr/local/mysql/bin/myisamchk

datahome=/usr/local/mysql/data/$dbname ##########要导出数据库的目录

dumpdata=/home/dumpdata

logfile=/home/sqldump.report

for i in `$mysql -e "use $dbname;show tables;" | sed -e '1d'`

do

$mysqldump $dbname $i  --default-character-set=gbk --fields-optionally-enclosed-by='"' --fields-terminated-by=','

--lines-terminated-by='\n' --tab=$dumpdata

#如果dump不能成功很大原因是在于表坏了。所以修一下。

if [ $? -ne 0 ]

then

echo "repair table $i" >>$logfile

cd $datahome

$myisamchk -r -f $i

if [ $? -ne 0 ]

then

echo "cannot repair $i" >>$logfile

continue

else

rm -rf /home/mysql/$i.sql

$mysqldump $dbname $i  --default-character-set=gbk --fields-optionally-enclosed-by='"'

--fields-terminated-by=',' --lines-terminated-by='\n' --tab=$dumpdata

if [ $? -ne 0 ]

then

echo "cannot dump $i" >>$logfile

continue

fi

fi

else

echo "dump $i done" >>$logfile

fi

done

没有问题之后，要做的就是把5.0的环境架设起来。好了，接下来要导入了。

#!/bin/sh

dbname=dbname

dumpdata=/home/dumpdata

mysql=/usr/local/mysql/bin/mysql

logfile=/home/load.report

$mysql -e "create database $dbname ;"

cd $dumpdata

for i in `ls | grep '\.sql'`

do

echo "create table $i" >>$logfile

$mysql $dbname    if [ $? -ne 0 ]

then

echo  "$i error" >>$logfile

exit

fi

done

for i in `ls | grep '\.txt'`

do

lenght=`expr ${#i} - 4`

table_name=${i:0:$lenght}

echo $table_name

j=/home/mysql/$i

$mysql -e "set names gbk;use $dbname;load data infile  '$j' into table $table_name fields terminated by ','

optionally enclosed by '\"' lines termintaed by '\n'"

if [ $? -ne 0 ]

then

echo "load $table_name error" >>$logfile

continue

else

echo "load $table_name done">>$logfile

fi

done

一切都没有问题的话就设置权限了。