虚拟主机 mysql 数据迁移至本地 sql server 一例

• 整体思路是使用odbc和链接服务器

1. 后台虚拟主机的sql备份功能，备份到ftp或指定目录为.sql文件
2. 安装 Connector/ODBC，最新版，https://dev.mysql.com/downloads/connector/odbc/，控制面板->管理工具->ODBC 数据源(64位)，ip/db/user/pwd，datasource name是关键，比如叫 mysql_aaa
3. 安装 MySQL Community Server，最新版，https://dev.mysql.com/downloads/mysql/，把 workbench 挑上
4. workbench 连接数据库，administration->management->data import->import self-contained file，选第一步备份的.sql文件
5. default target schema -> new db_xx，start import
6. sql server 打开 ssms，用语句创建链接服务器，创建完以后在链接服务器那里应该能看到一个名为MYSQL的服务器链接，并且下边有 db_xx 及相关的表

7. EXEC master.dbo.sp_addlinkedserver   
   @server = N'MYSQL',   
   @srvproduct=N'MySQL',   
   @provider=N'MSDASQL',   
   @provstr=N'DRIVER=DRIVER={MySQL ODBC 8.0 Unicode Driver}; SERVER=127.0.0.1; DATABASE=db_xx; USER=u; PASSWORD=p; OPTION=3'  

8. 回到 mysql workbench，查找所有需要转移的表，执行下边这句
9. SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='db_xx'
10. 上步应该得到了所有的表及记录行数，根据需要，进入下一步迁移操作
11. 在sql server中创建一个空库，比如叫 temp1，执行以下语句可从链接服务器直接连结构带数据一起提走
12. select * into test.dbo.table_x from openquery(MYSQL, 'select * from db_xx.table_x')
13. 具体哪些表需要执行迁移，以及脚本的批量生成参照第9步的结果
14. 中途遇到 链接服务器 OLE DB 访问接口 'xx' 返回的数据与列 '[xx].[xx].[dbo].[xx].xx' 所需的数据长度不匹配。所需的(最大)数据长度为 x，但返回的数据长度为 x。有木有？执行下句之后再迁
15. DBCC TRACEON (8765) WITH NO_INFOMSGS
16. 下面来几句干货你看香不香

17. select * into #tbs from openquery(MYSQL, 'SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=''db_xx''')
    where table_rows > 0 order by table_rows desc

18. select * from openquery(MYSQL, 'SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=''db_xx''')
    where table_rows > 0 order by table_rows desc

19. begin
        declare @a int,@error int    
        declare @temp varchar(1500)
        declare @sql_str varchar(500)
        set @a=1
        set @error=0
        --申明游标
        declare order_cursor cursor
        for (select [TABLE_NAME] from #tbs)
        --打开游标--
        open order_cursor
        --开始循环游标变量--
        fetch next from order_cursor into @temp
        while @@FETCH_STATUS = 0    --返回被 FETCH语句执行的最后游标的状态--
            begin      
                set @sql_str = 'select * into test.dbo.' + @temp + ' from openquery(MYSQL, ''select * from db_xx.' + @temp + ''')'      
                print @sql_str
                exec (@sql_str)
                set @a=@a+1
                set @error= @error + @@ERROR   --记录每次运行sql后是否正确，0正确
                fetch next from order_cursor into @temp   --转到下一个游标，没有会死循环
            end   
        close order_cursor  --关闭游标
        deallocate order_cursor   --释放游标
    end
    go

20. with x as (
    SELECT a.name, b.rows FROM sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.id
    WHERE (a.type = 'u') AND (b.indid IN (0, 1)))
    
    select a.*,b.table_rows,b.table_name from x a left join #tbs b on a.name = b.[TABLE_NAME]