最近2个月的work

------------- 2个月工作

 

1.ssis 包 数据迁移

 

 

2.mysql windows replication slave 制作

 

3. mssql  procedure

 

---- ddl   trigger
create table xwj_loong_update_version
(id bigint identity(1,1) ,
 EventType sysname,
 ObjectName sysname,
 PostTime char(10),
 CommandText nvarchar(1000),
 UserName   sysname,
 eventdata xml
)
;
alter trigger tr_table_event on database
for ddl_table_events
as
declare
 @eventdata xml
,@PostTime char(10)
,@EventType sysname
,@ObjectName sysname
,@ObjectType sysname
,@CommandText  nvarchar(1000)
,@UserName sysname
select @eventdata=eventdata();
select @EventType=t.c.value('(EventType)[1]','sysname')
   ,@ObjectName=t.c.value('(ObjectName)[1]','sysname')
   ,@PostTime=convert(char(10),t.c.value('(PostTime)[1]','char(10)'),120)
   ,@CommandText=t.c.value('(TSQLCommand/CommandText)[1]','nvarchar(1000)')
      ,@UserName=t.c.value('(UserName)[1]','sysname')
 from @eventdata.nodes('EVENT_INSTANCE') as t(c)
if @EventType=N'CREATE_TABLE'
begin
 if @ObjectName not like 'xwj%'
 begin
  raiserror('not allowed to create this type table',16,1)
  rollback
  insert into xwj_loong_update_version(EventType,ObjectName,PostTime,CommandText,UserName,eventdata)
   select @EventType,@ObjectName,@PostTime,@CommandText,@UserName,@eventdata
 end
end
if @EventType=N'DROP_TABLE'
begin
 if @ObjectName like 'xwj%'
 begin
  raiserror('not allowed to drop this type table',16,1)
  rollback
 end
end
go

--- check db version
if user_id(N'p_check_version','U') is not null
drop procedure p_check_version;
create procedure p_check_version
(@dbname nvarchar(100)
,@checktype tinyint=1    ----- full 1  tabledata 2
)
as
begin
 declare
  @database sysname
 ,@num tinyint;
 if object_id(N'tempdb..#','U') is not null
 drop table #;
 with xwj
  as
  (select id=1
   union all
   select id+1 from xwj where id<101
  )
 select px=identity(int,1,1),substring(dbname,id,charindex(',',dbname+',',id)-id)
  into #
  from (select @dbname as dbname) x1
  inner join xwj as x2 on  len(x1.dbname)>=id and substring(','+dbname,id,1)=','
  option(maxrecursion 0);
 create clustered index in_#_id on #(id);
 select top(1) @dataname=dbname from # ;
 while @@rowcount>0
 begin
   /*
   statement
   */
  
 
  select @num+@num+1
 end

 

Ssis  运用于 mysql 5.1_37

Mysql 字符集最好是utf8(可以再 my.ini /my.cnf  中更改)

Mysql connecter 5.1 (mysql 5.1 odbc ）使用 datareader 

String=driver={myssqlodbc3.51driver};server=%servername%;database=%database%,uid=%username%;password=%password%

或者 本机建立 dns 

一般 常规的数据etl ods 都可以用ssis 来实现

Data flow 

Control flow

The others

Mysql replication 

Grant replication slave  on *.*  to replicusr1@'slaveip 'identified by '123456' 

在从机上面 cmd 

Mysql -hreplicationip -ureplicuser1 -p123456 能否登陆

Replication 

My.ini 

Server-id=1

Log-bin=mysql-bin.log

Binlog-do-db=db1

Binlog-do-db=db2

Expire_logs_days=3

Restore  mysql server

Show master status

---- 清空日志

Flush tables with read lock

Reset master 

Buckup data -------move data to  slave 

Stop mysql server

---------------------------------

Slave 

My.ini

Server-id=2

Master-host=replicationip

Master-user=replicuser1

Master-password=123456

Master-port=3306

Masetr-coonect-retry=60

Replicate-do-db=db1

Replicate-do-db=db2

Slave-skip-errors=all

Report-host=replicationip

Expire_logs_days=7

Stop slave mysql server

Start replication server

Start slave server 

Slave server

Start slave 

Show slave status

Slave_io =yes

Running_sql yes

 

 

 

总结 : ssis 很强大,也想有更多的数据迁移项目去实践，乃至 ssas ssrs 和 ssis 配套使用.

mysql 在 windwos 上真 不适合跑复杂的业务 逻辑.