Database migration (SQL Server import data to MySQL)

background : The project started with a SQL Server database, and the business requirements are now to tune the database to MySQL. Online collection of a number of tutorials, found a relatively simple and convenient (temporarily did not find obvious BUG), here to organize to share to everyone.

1, first go to the MySQL official website to download an ODBC driver, because MSSQL has not integrated MySQL driver, this thing needs to be downloaded and installed separately. Click I download

I am downloading Windows uncompressed package windows (x86, 64-bit), ZIP Archive, free install version (it is said that the green version is better than the installation version).

2. Open ODBC Data source in Control Panel

If the installation succeeds, there are 2 corresponding options in the ODBC data source driver, such as:


3. Click on the User DSNand add a MySQL connection (provided you have installed a MySQL database).


The following prompt box appears after double-clicking:



Ah, well, it's half done!

4, if it is SQL server2000 with DTS, it can be used directly. The key steps are below


The following interface is familiar:

OK the import is complete.
Of course, you can also use File DSN, the connection string into a file, and then import the time to select the file DSN, as long as the path to find the connection string can be, not repeat.

After the driver is loaded, you can start to guide the library.

I am using the MySQL client tool SQLyog, the installation is very simple, the function interface is much like SQL Server 2008, easy to get started. (because I installed is the Chinese cracked version, in order to more convenient English version of children's shoes reading comprehension, it is directly that the original Bo to use, hehe, attached to the original Bo address http://www.jb51.net/softjc/158485.html)

1. SQLyog after installation of a new empty database, right-click "Import", "Import extenal Data";

(Figure1:import)

2. Select "Start a new job" and click "Next";

(Figure2:start a new job)

3. Below is the DSN setup interface, if you do not have a DSN to export the SQL Server database in your down list, you will need to create a new DSN connection to SQL Sever via "Create a new DSN";

(Figure3:create a New DSN)

(Figure4: Creating a new data source)

(FIGURE5: Select a data source type)

4. Save the above settings as a LOCAL.DSN file;

(Figure6: Choose a data source to save the path)

(Figure7: Select SQL Server server)

(Figure8: Set SQL Server account number and password)

(Figure9: Select SQL Server database)

(FIGURE10: test data source)

(FIGURE11: Test succeeded)

5. Select "File DSN", select the DSN we just created in the browse, then fill in the account number and password to log in to SQL Server;

(Figure12: Select DSN)

6. Next, select the target MySQL server, fill in the IP address and account password, and select the target database;

(Figure13: Set MySQL account and password target database)

(Figure14: Table copy)

7. This step is similar to the import and export function of SQL Server, where you can copy a table or multiple tables. You can also set specific filtering details, which include the corresponding relationship between the SQL Server table field and the MySQL table field "Map", the Advanced Option "advance", and Filter "WHERE". Our current requirement is to copy all, so the following filtering steps (8th Step) can be ignored, click "Next" to start copy. The whole process is like creating a time tunnel for SQL Server and MySQL, where the corresponding relationship between the SQL Server table and the MySQL table is the same as telling it what I'm sending (for example, people or animals), telling him to click on "Next" It's time to travel ^w^

(FIGURE15: Select Table Correspondence)

8. Figure16, the data source "source", describes "Destination", with particular attention to the data type "type" here, which means to convert the target data type, but does not need to be the same as the target table, because here is a temporary storage of data type, Similar to Findon in SQL Server is DateTime, this is converted to MySQL timestamp, in fact, the MySQL target table data type is datetime, such a setting can also be converted successfully.

(Figure16: Table field conversion)

(Figure17: Advanced option)

(Figure18:error)

(Figure19:log log information)

(FIGURE20: Execution information)

(FIGURE21: Execution result)

(FIGURE22: Data list for raw SQL Server)

(FIGURE23: Data list transferred to MySQL)

。 Compared to Figure22 and Figure23, we found that our data has been successfully transferred! there are shortcomings, but also look at the way the great God more guidance ^w^

Database migration (SQL Server import data to MySQL)