Restore data from a tab delimited file to MySQL

Yesterday I looked at using mysqldump to save data to CSV files, which by default saves the exported data as tab delimited files. Today we will look at how to restore data from these files into a MySQL database. It's very simple to do, using the mysqlimport command line tool.

Let's say for example that we had saved data using the mysqldump command line tool to a directory called /tmp/mysqldump, and that this directory contained the following tab delimited text files, containing data from several MySQL tables:

customers.txt
products.txt
categories.txt
products2categories.txt

To import the data from these files into MySQL, using the mysqlimport command line tool, you could do this one at a time like so:

mysqlimport -u [username] -p [database] /tmp/mysqldump/customers.txt
mysqlimport -u [username] -p [database] /tmp/mysqldump/products.txt
mysqlimport -u [username] -p [database] /tmp/mysqldump/categories.txt
mysqlimport -u [username] -p [database] /tmp/mysqldump/products2categories.txt

The -u flag specifies the username to log into MySQL as (where [username] is the username required). The -p flag tells mysqlimport that you will enter a password; when the command executes it will prompt you for the password before doing the import. Substitute [database] for the database you wish to import this data into.

The last part of the commands above is the full path to the text file. The filename specified must match a table in the specified database, with, optionally, any extension following the table name. The records are imported into the table specified by this filename.

Note that you must provide the full path to the file, otherwise the mysqlimport command will complain that it can't find the file because it uses the directory the MySQL database files are in if an absolute path is not used.

For example, if you did this, in the directory the files are located:

mysqlimport -u [username] -p test customers.txt

then mysqlimport would complain that:

mysqlimport: Error: File '/var/lib/mysql/test/customers.txt' not found (Errcode: 2), when using table: customers

If you did this:

mysqlimport -u [username] -p test ./customers.txt

then mysqlimport would complain that:

mysqlimport: Error: Can't get stat of '/var/lib/mysql/customers.txt' (Errcode: 2), when using table: customers

The exact value of /var/lib/mysql will vary depending on the actual location of the MySQL database files on your Linux system, but the rest of the error message should be the same. The solution is to simply pass the full path of the text files as part of the filename as given in the first example.

Deleting existing data as part of the import

The above examples will import data but will not make any changes to existing data that may already be in the database tables. If you have primary keys in the data files which conflict with primary keys currently in the tables then you will get errors. To delete all existing records in a table before the import starts, use the -d flag like so:

mysqlimport -u [username] -p -d [database] /tmp/mysqldump/customers.txt

Warning: this will delete all records in the table, so use this with care. I am not responsible for you deleting all records in your database tables!

Passing the password on the command line

If you are calling mysqlimport multiple times, it may be easier to pass the user's password on the command line instead of having to interactively enter it each time the command is run (this is particularly so for the examples in the next section). This can be done by entering the the password directly after the -p flag as shown in the example below. This works for all MySQL command line programs where you can specify a password.

mysqlimport -u [username] -p[password] ...

Note that there must not be a space inbetween the -p and the password itself. If there is a space then you will still be prompted to enter the password.

While the import is running, the password is masked to other processes (eg if someone else were to do a "ps -aux") but the password is stored as plain text in your bash history file, so be careful about using this.

Importing multiple files at once

Instead of having to enter the command multiple times (or hitting the up arrow and then changing the filename each time), it is possible to use a little BASH magic like so:

for filename in `ls -1 /path/to/database/files/*.txt`;
  do mysqlimport -u [username] -p[password] [database] /path/to/database/files/$filename;
done

What this does is to run the mysqlimport command for each file that matches the pattern /path/to/database/files/*.txt. If you are in the directory the files are in, you could make the command shorter by doing this:

for filename in `ls -1 *.txt`;
  do mysqlimport -u [username] -p[password] [database] `pwd`/$filename;
done

The `pwd` part will be replaced with the current directory, which saves you having to type it in.

Specifying the password means you don't need to enter on each loop, but read my above notes about it being stored in the BASH history file.