Who is this article for?
Anyone looking for how to import large MS Access DB to MySQL DB effectively.
Before You Begin
There are several options which we can use to import the data to MySQL. Few of those are listed in these articles below -
These options work well if the data that needs to be imported is small but is very slow for an extensive database. The reason the import is very slow is that it creates one insert statement per record. I recently had to convert a relatively large MS Access DB file to MySQL. The import process was prolonged, and I let it run overnight, but it didn't complete. So I had to come up with a different solution which can import the data much faster.
For this post, we can use the data provided by IPEDS. The file can be downloaded from here - https://nces.ed.gov/ipeds/use-the-data/download-access-database. We will use the 2017-18 Access file, which is the latest release at the time of writing this article.
Also, we are going to use mdbtools. I believe you can install it on Windows Operating system too - https://github.com/brianb/mdbtools/issues/107. Since I run a Linux distribution installing it was an easy setup.
When I used this script, I was able to import the data in less than a few minutes. A general outline of how the script works.
login pathto suppress warnings.
- Drops tables if they exist from the specified database.
- Recreate the table metadata from MS Access.
- Export each table to
- Bulk import the
.csvdata to the specified table.
- The above script is tested on a Linux distribution.
- mdbtools does not create indexes or foreign key references.