Database migration (SQL Server imports data to MySql)

Database migration (SQL Server imports data to MySql)

Background: At the beginning of the project, the SQL Server database is used, and the database needs to be adjusted to MySQL. I collected some tutorials on the Internet and sorted out a simple and convenient way to share them with you.

1. Go to the mysql official website to download an odbc driver. Because MSsql has never been integrated with the mysql driver, you need to download and install it separately. Click here to download the windows decompressed package.Windows (x86, 64-bit), ZIP Archive, No installation version (it is said that the green version is better than the installation version ).

2. Choose Control Panel> open ODBC data source.

If the installation is successful, there are two options in the ODBC data source driver, for example:


3. Click the user DSN to add a mysql connection (provided that you have installed a mysql database ).


The following prompt box is displayed after double-clicking:



Ah, ha, it's half done now!

4. If DTS is used for SQL server2000, it can be used directly. The key steps are as follows:



The following interface is very familiar:

OK is imported.
Of course, you can also use the File dsn to save the connection string as a file, and then select the file dsn during import. You only need to find the path of the connection string ..

After the driver is installed, you can start importing the database.

I am using SQLyog, a MySQL Client tool. It is easy to install and has a functional interface similar to SQL Server 2008. (Because I installed is a Chinese cracked version, in order to facilitate the English version of the children's shoes reading comprehension, directly take the original Bo to use, hee, with the original Bo address http://www.jb51.net/softjc/158485.html)

1. After SQLyog is installed, create an empty database and right-click Import> Import Extenal Data ];

(Figure1: Import)

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

(Figure2: Start a new job)

3. the following figure shows the DSN settings page. If you do not need to export the DSN of the SQL Server database from the list, create a New DSN to connect to the SQL server;

(Figure3: Create a New DSN)

(Figure4: Create a new data source)

(Figure5: select the data source type)

4. Save the preceding settings as a local. dsn File;

(Figure6: select the data source storage path)

(Figure7: Select SQL Server)

(Figure8: Set the SQL Server account and password)

(Figure9: Select SQL Server database)

(Figure10: Test Data Source)

(Figure11: test successful)

5. Select File DSN, select the DSN we just created in browsing, and enter the account and password used to log on to SQL Server;

(Figure12: Select DSN)

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

(Figure13: Set the MySQL account and password to the target database)

(Figure14: Table copy)

7. This step is similar to the SQL Server Import and Export function. You can copy one or more tables here. You can also set specific filtering details, including the ing between SQL Server table fields and MySQL table fields [Map], Advanced options [Advanced], and filter [WHERE ]. Our current requirement is to copy all of the data. Therefore, the subsequent filtering steps (step 1) are not considered. Click "Next" To Start copying. The entire process is like creating a time tunnel for SQL Server and MySQL, here, the ing between the SQL Server table and the MySQL table is like telling it What I Want To transmit (such as people or animals ), after telling him, you can click "Next" to start time shuttle ^ w ^

(Figure15: select the table ing)

8. in Figure16, the data Source [Source] describes [Destination]. Pay special attention to the Data Type [Type] here, which means to convert the Data Type of the target, but it does not need to be the same as that of the target table, this is used as a temporary storage data type, similar to that of FindOn in SQL Server, which is converted to MySQL timestamp. In fact, the Data Type of the MySQL target table is datetime, this setting can also be converted successfully.

(Figure16: Table field conversion)

(Figure17: advanced options)

(Figure18: Error)

(Figure19: Log information)

(Figure20: Execution Information)

(Figure21: execution result)

(Figure22: Data list of the original SQL Server)

(Figure23: list of data transferred to MySQL)

. Compared with Figure22 and Figure23, we found that all data has been transferred successfully! If you have any shortcomings, please kindly advise on them ^ w ^