Anyone looking for how to import large MS Access DB to MySQL DB effectively.
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.
.csvdata to the specified table.
POSTED IN MYSQL, ACCESS, DATABASE, BLOG