一、迁移步骤
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;
阅读(2282) | 评论(0) | 转发(0) |