I recently showed how to load data into MySQL with foreign key constraint issues when loading a backup from tab delimited text files. This post provides a BASH command line script to generate the SQL to load all the files to save some time.
Example database
The example database in this post has three tables: products, categories and products2categories. There were dumped to text files like so:
mysqldump -u[username] -p -t -T/tmp/mysql-backup [databasename]
-u specifies the username and you would subsitute [username] for the actual username.
-p specifies that you wish to enter a password. mysqldump will prompt for the password when run.
-t specifies you do not want .sql files created which have the table creation information for each table individually. Instead we will do a second dump for table creation information only in a single file.
-T specifies the directory to write the files to. It must be writeable as the user the MySQL server runs as. In practise this means changing that directory to be writeable by all. In my example, the data is being written to /tmp/mysql-backup so do chmod 0777 /tmp/mysql-backup
Finally, change [databasename] to the name of the database to dump data for.
Now dump the database structure into a single file:
mysqldump -u[username] -p -d [databasename] > [databasename].sql
The only flag different frm the first example is -d which specifies we do not want the data to be dumped as well: just the database schema.
Files created by mysqldump
The following files are created for the example database using the two commands above:
[databasename].sql categories.txt products2categories.txt products.txt
Create the tables
Before loading the data the tables need to be created first. Do this like so:
mysql -u[username] -p [otherdatabasename] < [databasename].sql
When using mysqldump to create the txt files, it will by default create a .sql file for each table. I prevented it from doing this with the -t flag and created a single sql file instead.
The reason for this is that it simplifies creating the tables because doing "mysql … < *.sql" does not work, resulting in a "-bash: *.sql: ambiguous redirect" error. Rather than use xargs or some other workaround it’s easier to simply dump the table creation all into a single file.
The script to generate the load SQL
We’ve finally got to the point of this post. The aim is to create a SQL file that looks like this:
SET FOREIGN_KEY_CHECKS = 0; load data infile '/tmp/mysql-backup/categories.txt' INTO TABLE categories; load data infile '/tmp/mysql-backup/products2categories.txt' INTO TABLE products2categories; load data infile '/tmp/mysql-backup/products.txt' INTO TABLE products;
The BASH script to do this looks like so:
#!/bin/sh
echo "SET FOREIGN_KEY_CHECKS = 0;"
for f in `ls -1 *.txt`; do
table=${f/.txt/}
echo "load data infile '`pwd`/$f' INTO TABLE $table;"
done;
What it does it to get a list of all the .txt files in the directory and for each one create a "load data infile" line. The "table=${f/.txt/}" line creates a variable with the .txt part stripped from the filename.
Save the above script into the same directory that the data files were dumped, naming it e.g. sql.sh and then do this:
chmod 0700 sql.sh ./sql.sh > load.sql mysql -u[username] -p [otherdatabasename] < load.sql
Line 1 changes the permissions so it can be run. Line 2 runs it and dumps the resulting lines into the file load.sql. Line 3 then runs the generated SQL file into the other database.
Because the first line of the script contains the set foreign check keys part then all foreign constraints are ignored while the data is loaded from the files. It needs to be run as a SQL script like this rather than using the mysql command line utility because it cannot be told to ignore foreign key checks (at least as far as I have been able to determine).