Chinaunix首页 | 论坛 | 博客
  • 博客访问: 373104
  • 博文数量: 75
  • 博客积分: 1732
  • 博客等级: 上尉
  • 技术积分: 812
  • 用 户 组: 普通用户
  • 注册时间: 2008-04-11 16:02
个人简介

博客很久没有更新了,原因是大多数时间都忙在研究技术上,却懒得腾时间出来把技术分享,最近在开源力量上开课《Mongodb管理与维护》,让屌丝们从0到精通,敬请关注。本博客技术原创更新滞后一些,找时间更新有关mysql,mongodb等内容,谢谢大家关注。

文章分类

全部博文(75)

文章存档

2021年(1)

2011年(20)

2010年(40)

2009年(7)

2008年(7)

分类: Mysql/postgreSQL

2010-06-25 09:03:59

一、迁移步骤
1、迁移时间:早上9点至12点
9点时停止前台游戏程序,停止充值等。
2、数据库迁移
   执行备份,把最新备份拷至samba保存
 1).SQLSERVER数据库各数据表导出为文本文件
2).把导出的文本文件上传至mysql服务器。
3).dos2unix文件转换
4).iconv编码转换
5).备份gls的account_billing表数据,迁移至本地数据库。
6).执行文本文件数据导入mysql脚本过程
7).导入完毕,备份迁移后的数据库。
8).数据迁移后测试
9).迁移完成
二、详细过程
1.SQLSERVER用以下语句导出t_game_Character文本文件,其它全表导出为文本文件。
SELECT [AccountId]
      ,[CharacterId]
      ,[CharName]
      ,[Lev]
      ,[Money]
      ,[DataInfo]
      ,[OtherData]
      ,[CharSlot]
      ,[IsDel]
      ,[OnlineTime]
      ,convert(char(19),[DelTime],20) as DelTime
      ,[KillValue]
      ,[KillFlag]
      ,[KillerStateTime]
      ,[Exp]
      ,[jinding]
      ,convert(char(19),[UpdateTime],20) as UpdateTime
      ,[ExtendData]
      ,[StorageMoney]
      ,convert(char(19),[create_time],20) as create_time
      ,[jinpiao]
  FROM [t_game_Character]
GO
2.上传至linux服务器,进行字符集编码转换
/*##字符集转换
iconv -c -f GB18030 -t UTF-8 t_game_character.txt > t_game_character
iconv -c -f GB18030 -t UTF-8 relation.txt > relation
iconv -c -f GB18030 -t UTF-8 t_game_gminfo.txt > t_game_gminfo
iconv -c -f GB18030 -t UTF-8 t_game_guild.txt > t_game_guild
iconv -c -f GB18030 -t UTF-8 t_game_guildmember.txt > t_game_guildmember
iconv -c -f GB18030 -t UTF-8 t_game_item.txt > t_game_item
iconv -c -f GB18030 -t UTF-8 t_game_mount.txt > t_game_mount
iconv -c -f GB18030 -t UTF-8 t_game_visualinfo.txt > t_game_visualinfo
*/
3.设置环境变量,导入至mysql数据库
##导入脚本##

set character_set_client =utf8;
set character_set_connection =utf8;
set character_set_database=utf8;
set character_set_filesystem=utf8;
set character_set_results=utf8;
set character_set_server=utf8;

alter table t_game_character drop index idx_CharName;
alter table t_game_character modify characterid int;
alter table t_game_character drop primary key;
load data infile '/export/mysqldata362/t_game_character' into table t_game_character fields terminated by '\t' escaped by '';
alter table t_game_character modify characterid int auto_increment primary key;
create index idx_CharName on t_game_character(CharName);

alter table relation drop primary key;
load data infile '/export/mysqldata362/relation' into table relation fields terminated by '\t' escaped by '';
alter table relation add primary key(SelfDBID,RelationDBID);

alter table t_game_gminfo drop primary key;
load data infile '/export/mysqldata362/t_game_gminfo' into table t_game_gminfo fields terminated by '\t' escaped by '';
alter table t_game_gminfo add primary key(RoleName);

load data infile '/export/mysqldata362/t_game_guild' into table t_game_guild fields terminated by '\t' escaped by '';

alter table t_game_guildmember drop primary key;
alter table t_game_guildmember drop index idx_GuildID;
load data infile '/export/mysqldata362/t_game_guildmember' into table t_game_guildmember fields terminated by '\t' escaped by '';
alter table t_game_guildmember add primary key(name);
create index idx_GuildID on t_game_guildmember(GuildID);


alter table t_game_item drop primary key;
load data infile '/export/mysqldata362/t_game_item' into table t_game_item fields terminated by '\t' escaped by '';
alter table  t_game_item add constraint  primary key( CharacterId,ItemGUID);

load data infile '/export/mysqldata362/t_game_itemdropcontrol' into table t_game_itemdropcontrol fields terminated by '\t' escaped by '';

alter table t_game_mount drop primary key;
load data infile '/export/mysqldata362/t_game_mount' into table t_game_mount fields terminated by '\t' escaped by '';
alter table t_game_mount add constraint  primary key(CharacterId,GUID);


alter table t_game_visualinfo drop primary key;
load data infile '/export/mysqldata362/t_game_visualinfo' into table t_game_visualinfo fields terminated by '\t' escaped by '';
alter table t_game_visualinfo add constraint  primary key(CharacterId);

select characterid,charname  from t_game_character where charname in (
select charname  from t_game_character group by charname having count(*)>=2 );
select a.accountid,a.characterid,a.charname,a.lev,b.accountid,b.characterid,b.charname,b.lev from t_game_character a,t_game_character b where a.charname=b.charname and a.characterid<>b.characterid;



阅读(2217) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~