SQLSERVER向mysql迁移

一、迁移步骤
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;