POSTED IN MYSQL, ACCESS, DATABASE, BLOG
How to effectively import large MS Access DB to MySQL DB
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.
#!/bin/bash #=========================================================== # FILE : script.sh # DESCRIPTION : Convert MS Access to MySQL. #=========================================================== set -o nounset # help avoid bugs shopt -s extglob PATH=/bin:/usr/bin:/sbin # for consistency # variables declaration DB_NAME=ENTER_YOUR_DB_NAME DB_UN=ENTER_YOUR_DB_USER PATH_TO_ACCESS_FILE=ENTER_THE_ABSOLUTE_PATH_TO_ACCESS_FILE # set login path to suppress warnings mysql_config_editor remove --login-path=local mysql_config_editor set --login-path=local --host=localhost --user=$DB_UN --password # get all the tables TABLES=$(mdb-tables -1 $PATH_TO_ACCESS_FILE) # drop tables if exits echo "$(date +%Y%m%d_%H%M) DROPPING TABLE IF EXISTS" for t in $TABLES do mysql --login-path=local $DB_NAME -Bse "DROP TABLE IF EXISTS $t;" done # create meta definition for the tables rm -rf meta.sql mdb-schema $PATH_TO_ACCESS_FILE mysql > meta.sql # create the tables using the meta.sql file generated above echo "$(date +%Y%m%d_%H%M) CREATING TABLES;" mysql --login-path=local $DB_NAME < meta.sql rm -rf meta.sql echo "$(date +%Y%m%d_%H%M) IMPORTING DATA INTO TABLES;" for t in $TABLES do echo "$(date +%Y%m%d_%H%M) IMPORTING DATA FOR $t;" rm -rf $t.csv mdb-export -D '%Y-%m-%d %H:%M:%S' $PATH_TO_ACCESS_FILE $t > $t.csv mysqlimport --login-path=local --ignore-lines=1 --fields-terminated-by=, --fields-optionally-enclosed-by='"' --local $DB_NAME $t.csv mysql --login-path=local $DB_NAME -Bse "ALTER TABLE $t CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;" rm -rf $t.csv done echo "$(date +%Y%m%d_%H%M) COMPLETED;"
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.