I needed to do this, and found the existing documentation somewhat lacking. My work will be on a file in the original MDB access format (not the newer XML based ones). I'm working on Debian Linux. Instructions for derivatives (such as Ubuntu) should be identical. Other distributions (Redhat Based) may use different package names (using the yum package manager should simplify this, but I have not tested these instructions under Redhat/CentOS/etc).
For the purposes of my example, I'll call my MySQL database "taco" and my Access file "nacho.mdb".
Where I say "(creds)" you place any connection information you may usually need – an example would be "-h database.yourdomain.com -u youruser -pyourpassword"
First, install the mdbtools package:
apt-get install mdbtools
Answer the questions as appropriate.
Create a new database to hold these files:
optional, but suggested. You can always move tables around later.
mysqladmin (creds) create taco
Build empty tables to house all your data, using the Access schema:
This creates empty tables with the same names, structures, and datatypes (or their equivalents) as the access database.
mdb-schema nachos.mdb mysql | mysql (creds) taco
Copy all the data over:
I asked for a tables list, comma delimited. Then I removed the trailing comma, then told xargs to run mdb-export for each table name in the file. BLAH is used as a placeholder, and is replaced by whatever table name it's working on. Please note the positions of commas and spaces are crucial to the proper operation of this command.
mdb-tables -d, -t table nacho.mdb |sed 's/,$//'|xargs -d, -I BLAH mdb-export -I mysql nacho.mdb BLAH|mysql (creds) taco
If you make a mistake:
I know, we're all perfect, right? But let's pretend …your friend… makes a mistake, or encounters a correctable error in processing. The easiest thing to do is dump the mysql database and start all over (you did create a new database just for this, right?)
mysqladmin (creds) drop taco
Answer "Y" and push forward. Only by losing everything are we free to do anything. That's what you'll tell yourself to feel better about the time wasted by starting over. Unless you know what you're doing, I promise you this is the easiest, quickest way.
3 thoughts on “Importing Microsoft Access databases into MySQL under Linux”
Nicely explained. Thank you.
I was hoping for a smooth conversion but I am getting date format errors :/
ERROR 1292 (22007) at line 1: Incorrect datetime value: ’07/31/16 00:00:00′ for column ‘EffectiveTo’ at row 1
xargs: mdb-export: terminated by signal 13
Check the format of the field you're importing into MySQL – chances are that, without conversion, you'll need to make EffectiveTo a VARCHAR – it's likely that the conversion is using a MySQL DATE field, which expects YYYY-MM-DD format as opposed to MM/DD/YY HH:ii:ss which is what Access is showing it as being. Even if the MySQL import field was configured as a TIMESTAMP, you'd still need to convert the date into the expected format.