大数据库 MYSQL数据4.0升级到5.1心得

贝贝博客 10.02.04 网站建设 1214 0 条(抢沙发)
Foxalt工作室 T4主题

mysql数据库升级无非是用mysqldump -A > xxxx.sql 导出,然后mysql --default-charset=xxx < xxxx.sql 导入即可,但我的这次升级工作确并没有想象中顺利.
mysql5.1不认mysql4.0dump出来的注释所以要加--comments=false参数
如下:
mysql -f -A -u root -p --comments=false --default-character-set=gbk < d:/data.sql > e:/error.log

mysqldump -B 数据库名 -u root -p --comments=false --default-character-set=gbk > f:/mysqldump/data.sql


/usr/local/mysql/bin/mysql -uroot -p --default-character-set=gbk < /root/mysqlimport/data.sql

/usr/local/mysql/bin/mysql -uroot -p --default-character-set=gbk < /root/mysqlimport/数据库名.sql

背景介绍

1.项目有两个数据库系统,表类型都是myisam, 1000个数据库,60000多张表 一个在本地 用于备份和存放原始数据 一个在外网服务器用于存放加密数据并对外提供访问。系统升级原因, 外网服务器磁盘空间已满,考虑到外网服务器过于陈旧,于是购置新服务器,然新服务器64位CPU下mysql4.0安装不上,只好安装最新的mysql5.1(这个问题不知什么原因,google无果)

2.升级经历
1.原有系统改造
2.升级本地数据库,数据库大小160G。
第一次
mysqldump -A > xxxx.sql
时间:18小时

无奈中途有个mysql数据表错误导致导出失败。

遂写修复程序一个
set_time_limit(0);
$conn = mysql_connect("localhost", "root", "");
$result = mysql_list_dbs($conn);
while($record = mysql_fetch_row($result)) {
$rs = mysql_list_tables($record[0], $conn);
while($row = mysql_fetch_row($rs)) {
print "\nREPAIR TABLE {$record[0]}.{$row[0]}\n";
$return = mysql_query("REPAIR TABLE {$record[0]}.{$row[0]}");
print_r(mysql_fetch_assoc($return));
}
mysql_free_result($rs);
}
mysql_free_result($result);
mysql_close($conn);

修复完成。

第二次,24小时后完成。

导入
第一次
一条sql造成导入中断
无奈之下查看mysql命令,发现有一个-f参数(强制导入,出现错误不中断),甚喜
mysql -f --default-charset=gbk < data.sql > error.txt

第二次导入成功。查看error.txt 修复其中错误

3.服务器导出
由于服务没有空间,对外服务又不能停。所以导出工作很是费了一番周折。

第一次,下午6点携带机器去机房,导出mysqldump 中段, 外网服务器长期提供查询服务,mysql 损坏比率较高。
尝试本地的办法 repair table, repair table 时会生成一个.TMD的临时文件,由于服务器已经没有空间, 修复工作虽显示完成但实际没有真正修复。

第一次熬到后半夜无功而返。

查手册发现 repair table 对于有些错误并不能完全修复,改用myisamchk -r -f 才可以

第二次,下午6点,到机房,直接拷贝mysql/data下文件。数小时拷贝完成。顺利返回。

在本地安装mysql4.0, 数据库路径指向拷贝回的data目录
1.修复所有表
导出修复命令
repair.php
<?php
set_time_limit(0);
$conn = mysql_connect("localhost", "root", "");
$result = mysql_list_dbs($conn);
while($record = mysql_fetch_row($result)) {
print "myisamchk -r -f data/{$record[0]}/*.MYI\n";
}
mysql_free_result($result);
mysql_close($conn);
?>

php -f repair.php > repair.sh
chmod u+x repair.sh

./repair.sh


2.逐个数据库导出
mysqldump.php
<?php
set_time_limit(0);
$conn = mysql_connect("localhost", "root", "");
$result = mysql_list_dbs($conn);
while($record = mysql_fetch_row($result)) {
print "mysqldump -B -f --comments=false {$record[0]} > data/{$record[0]}.sql\n";
}
mysql_free_result($result);
mysql_close($conn);
?>

php -f mysqldump.php > mysqldump.sh
chmod u+x mysqldump.sh

./mysqldump.sh

3.逐个数据库导入
mysqlimport.php
<?php
set_time_limit(0);
$conn = mysql_connect("localhost", "root", "");
$result = mysql_list_dbs($conn);
while($record = mysql_fetch_row($result)) {
print "mysql -f --default-character-set=gbk < data/{$record[0]}.sql\n";
}
mysql_free_result($result);
mysql_close($conn);
?>

php -f mysqlimport.php > mysqlimport.sh
chmod u+x mysqlimport.sh

./mysqlimport.sh

4.导入完成后
sync.php
<?php
set_time_limit(0);
$d_link = mysql_connect("192.168.1.108", "import", "1234");
$s_link = mysql_connect("192.168.1.107", "export", "1234");

mysql_query("set names 'gbk'");

$s_db_result = mysql_list_dbs($s_link);
$d_db_result = mysql_list_dbs($d_link);

//dest database list
$d_db_list = array();
while($record = mysql_fetch_row($d_db_result)) {
$d_db_list[] = $record[0];
}

while ($record = mysql_fetch_row($s_db_result)) {
$s_db_name = $record[0];

//filter db
if($s_db_name == "system" || preg_match("/^c\d{4}/", $s_db_name)) {
if(!in_array($s_db_name, $d_db_list)) {
$sql = "create database $s_db_name";
mysql_query($sql, $d_link);

print "database $s_db_name isn't exists,create OK!\n";
}

$s_tb_result = mysql_list_tables($s_db_name, $s_link);
while ($record = mysql_fetch_row($s_tb_result)) {
$s_tb_name = $record[0];
$sql = "select count(*) from $s_db_name.$s_tb_name";
$rs = mysql_query($sql, $d_link);
if($rs == false) {
$sql = "drop table $s_db_name.$s_tb_name";
mysql_query($sql, $d_link);
print "drop table $s_db_name.$s_tb_name\n";
mysql_query($sql, $d_link);
print "drop table $s_db_name.$s_tb_name\n";
sleep(3);
}

//dest database's table list
$d_tb_list = array();
$d_tb_result = mysql_list_tables($s_db_name, $d_link);

while ($record = mysql_fetch_row($d_tb_result)) {
$d_tb_list[] = $record[0];
}

//dest table exists
if(!in_array($s_tb_name, $d_tb_list)) {
mysql_select_db($s_db_name, $d_link);

$sql = "show create table $s_db_name.$s_tb_name";
print $sql."\n";
$record = mysql_fetch_row(mysql_query($sql, $s_link));
$struct = $record[1];
mysql_query($struct, $d_link);
print "table $s_db_name.$s_tb_name isn't exists, create OK!\n";
}

//data compare
$sql = "select count(*) from $s_db_name.$s_tb_name";
$s_tb_count = current(mysql_fetch_row(mysql_query($sql, $s_link)));
$d_tb_count = current(mysql_fetch_row(mysql_query($sql, $d_link)));

if($s_tb_count != $d_tb_count) {
if($d_tb_count > 0) {
//empty table when import error
$sql = "truncate table $s_db_name.$s_tb_name";
mysql_query($sql, $d_link);
}
//import by line
$sql = "select * from $s_db_name.$s_tb_name";
$s_import_result = mysql_query($sql, $s_link);
while ($record = mysql_fetch_assoc($s_import_result)) {
$keys = implode("`,`",array_keys($record));
$values = implode("','",array_values($record));

$values = get_magic_quotes_gpc() ? $values : array_map("addslashes",$values);

$sql = "INSERT INTO `$s_db_name`.`$s_tb_name` (`".$keys."`) VALUES ('".$values."')";
//print $sql."\n";
mysql_query($sql, $d_link);
}
mysql_free_result($s_import_result);

print "$s_db_name.$s_tb_name repair OK!\n";
} else {
print "$s_db_name.$s_tb_name is OK!\n";
}
}
}
}

mysql_close($s_link);
mysql_close($d_link);
?>
修复导入导出过程中的错误。

3.总结
简单的问题,当工作量大到一定程度的时候也变得不简单,面对大数据量导入导出,我总结到的最有效的办法,就是本文后半部分的方法,欢迎广大网友提供更彪悍的方法。

 

相关文章

Foxalt工作室 T4主题

(¬_¬) 还不快点抢沙发!!!

联络贝贝