SQL Server data migration to MySQL

Background:

My company's official website uses PHP for re-development, abandoned the original. NET development environment, but need to use some of the old official website data, so need to do data migration.

The environment is as follows:

Old official website: Windows server R2, SQL Server R2

The Web: RedHat 6.5,mysql,php ...

==================== I'm a split line =========================

There are a lot of software on the network claiming to be able to do SQL server-> my SQL data migration, such as obdb2db, but I try to convert the unsuccessful, other similar small software, I did not go to test. Here I use MySQL's official MySQL WorkBench, a powerful software that can either design a development database or transfer data. Specific links, please go to MySQL official website to find.

MySQL Workbench installation process is slightly, the specific migration process is as follows:

Open MySQL workbench->database->migration wizard->start migration.

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/77/99/wKiom1ZqNKCQtWm4AACwJWN0rPI090.png "title=" 1.png " alt= "Wkiom1zqnkcqtwm4aacwjwn0rpi090.png"/>

Select Microsoft SQL Server in the Database system option, select ODBC (native) In the connection method, enter the appropriate server, user name, password, click the "Test Connection "To test if the connection is successful.

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/77/99/wKiom1ZqNhrC29VRAAGEF3pljLU037.png "title=" 2.png " alt= "Wkiom1zqnhrc29vraagef3pljlu037.png"/>

In the "Target Selection" page, fill in the target MySQL information and test the connection, then click Next.

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/77/99/wKiom1ZqN_2S0g4pAAGWWT2Jo6o713.png "title=" 3.png " alt= "Wkiom1zqn_2s0g4paagwwt2jo6o713.png"/>

On the Fetch Schemas list page, the software checks the source database for data and obtains the appropriate information.

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/77/98/wKioL1ZqOMSDD0J5AAC66Ndm9Ko262.png "title=" 4.png " alt= "Wkiol1zqomsdd0j5aac66ndm9ko262.png"/>

In the "Schemas Selection" Select the database you want to migrate, click Next.

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/77/98/wKioL1ZqOvDBmGpMAAD-T6kD9kA778.png "style=" float: none; "title=" 5.png "alt=" Wkiol1zqovdbmgpmaad-t6kd9ka778.png "/>

In the "Reverse Engineer Source" option, click Next.

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/77/9A/wKiom1ZqOvCzIs0BAAC3MrF1VNA864.png "style=" float: none; "title=" 6.png "alt=" Wkiom1zqovczis0baac3mrf1vna864.png "/>

In "Source Objects" Select the type of source data you want to migrate, by default the "Migrate Table Objects" is selected, by default, click Next.

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/77/98/wKioL1ZqOvLi7b_4AADikynUbHw590.png "style=" float: none; "title=" 7.png "alt=" Wkiol1zqovli7b_4aadikynubhw590.png "/>

In "Migration", select Next.

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M00/77/98/wKioL1ZqOvOQI3HIAAC4RPLxmzc094.png "style=" float: none; "title=" 8.png "alt=" Wkiol1zqovoqi3hiaac4rplxmzc094.png "/>

In "Manual editing", review the possible errors, warnings for the migration. Errors are almost impossible to migrate, and warnings are possible.

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/77/98/wKioL1ZqOvWR6jTLAACkUQ9Yfek528.png "style=" float: none; "title=" 9.png "alt=" Wkiol1zqovwr6jtlaackuq9yfek528.png "/>

Select "Create Schema in Target RDBMS" from the "target Creation option", whether you need to create "SQL Script" to see your needs. Default is not selected.

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M01/77/98/wKioL1ZqOvayDiQnAADOixvTm4A649.png "style=" float: none; "title=" 10.png "alt=" Wkiol1zqovaydiqnaadoixvtm4a649.png "/>

In the "Create Schemas" page, the software automatically creates the corresponding Schemas and objects in the target MySQL.

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/77/98/wKioL1ZqOvfjfoQHAADQhhNNMLc136.png "style=" float: none; "title=" 11.png "alt=" Wkiol1zqovfjfoqhaadqhhnnmlc136.png "/>

Create Target Results can view the results of the creation, and the success status will appear on the right.

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M01/77/9A/wKiom1ZqOviA3DsoAAE9EycyGXQ224.png "style=" float: none; "title=" 12.png "alt=" Wkiom1zqovia3dsoaae9eycygxq224.png "/>

"Data Transfer Setup" page, the software will copy the source data to the target database, there are three options to choose, if the network allows, directly select the "Online copy of the table Data to target RDBMS", that is, an online migration.

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/77/9A/wKiom1ZqOvrintp3AADbj9QfKXg059.png "style=" float: none; "title=" 13.png "alt=" Wkiom1zqovrintp3aadbj9qfkxg059.png "/>

You can see the process of data migration in the "Bulk Data Transfer" page, and if the migration has been normal, the migration is successful.

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/77/98/wKioL1ZqOvyTjqRcAACvQbzm2kc514.png "style=" float: none; "title=" 14.png "alt=" Wkiol1zqovytjqrcaacvqbzm2kc514.png "/>

Unfortunately, I have problems with this migration, remember the previous page warning, it is likely that the problem. However, only 1 table migration problems, other tables are not a problem, you can connect to the target database to view.

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/77/99/wKioL1ZqPWmxq1cqAAAT8areXE8838.png "title=" 15.png "alt=" Wkiol1zqpwmxq1cqaaat8arexe8838.png "/>

If only 1 tables have failed to migrate and the data in the table is not many, we can manually migrate through the log to see that a table migration failed. We then manually migrate the table.

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M00/77/99/wKioL1ZqPkLTmOY8AABCaic-tXE681.png "title=" 16.png "alt=" Wkiol1zqpkltmoy8aabcaic-txe681.png "/>

Open MS SQL Server Management, find the corresponding database, right-click on the database "task", "Generate script"

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/77/9B/wKioL1ZqRFHj77QDAAIsqCZ_KZc161.png "style=" float: none; "title=" 17.png "alt=" Wkiol1zqrfhj77qdaaisqcz_kzc161.png "/>

In the Select object, select Select a specific database object, locate the table you want to export, and click Next.

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M00/77/9C/wKiom1ZqRFKCehB5AAC26KolVs0199.png "style=" float: none; "title=" 18.png "alt=" Wkiom1zqrfkcehb5aac26kolvs0199.png "/>

On the Set Scripting Options page, click Advanced Options.

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M02/77/9B/wKioL1ZqRFSBB3PUAADopUKYmws955.png "style=" float: none; "title=" 19.png "alt=" Wkiol1zqrfsbb3puaadopukymws955.png "/>

"Find the type of data you want to write the specimen to," and select "Schema and Data".

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M01/77/9C/wKiom1ZqRFOwOdXsAABlT6khMMg710.png "style=" float: none; "title=" 19-1.png "alt=" Wkiom1zqrfowodxsaablt6khmmg710.png "/>

Summary options, review the relevant information, and click Next, the script will be generated automatically.

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/77/9B/wKioL1ZqRFWjdgnaAACGrJCxnkw420.png "style=" float: none; "title=" 20.png "alt=" Wkiol1zqrfwjdgnaaacgrjcxnkw420.png "/>

After a moment, you can find the script file in the Save directory.

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M01/77/9B/wKioL1ZqReqQgCFsAABzB0Q40wo413.png "style=" float: none; "title=" 21.png "alt=" Wkiol1zqreqqgcfsaabzb0q40wo413.png "/>

That is, the exported MS SQL script, but because the exported SQL script only applies to SQL Server, the import of MySQL needs to be further modified, because the syntax difference is not small, slightly modified, please do your own La La ...

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/77/9D/wKiom1ZqReuz_h82AAEscwSntSk845.png "title=" 22.png "Style=" Float:none; "alt=" Wkiom1zqreuz_h82aaescwsntsk845.png "/>

The modified statement, you can automatically execute the data inserted in the MySQL command line, the next step please do it yourself.

============== I'm a split line =========================

Mom eggs, the above pictures are white mosaic, the last one all to burst the leak ....

What the hell%>_<%

This article is from the "Mama Sister" blog, please make sure to keep this source http://smallc.blog.51cto.com/926344/1721922

SQL Server data migration to MySQL