Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysql: Restore backup that was created with "--tab" option in mysqldump

mysqldump has the "--tab" option to split up the dump separate files for each table. mysql creates a .sql file (with schema) and a .txt file (with data) for each table.

The .sql files work fine, but how do I import the data from the .txt files?

like image 572
Robby75 Avatar asked Nov 27 '13 17:11

Robby75


People also ask

How do I restore a MySQL backup file?

In Database Explorer, right-click the server connection on which you want to restore the database and select Backup and Restore > Restore Database. In the Database Restore Wizard, select the backup file and click Restore.

How do I restore a MySQL workbench backup?

Configuring MySQL Workbench to Restore (Import) Your Database. Click the box for the database connection that you just set up. Click the “Data Import/Restore” link. For this tutorial, we're assuming you are restoring a “Self-Contained File” backup.

When working with the Mysqldump program which prefix provides a way to disable an option?

By default, this option is enabled. However, you may want to disable it in some instances, such as when sending the output of mysql into another program that expects to see only data and not an initial header line. The --disable and --skip prefixes and the =0 suffix all have the same effect: They turn the option off.


1 Answers

Official documentation doesn't covert the case of importing these *.txt and foreign key constrains. There is still open long-lived bug#19996 to implement foreign key ignore in mysqlimport. So it has to be done manually.

#!/bin/bash -e

DIR=/path/to/csv/backup
DATABASE=database
USER=user
PASSWORD=password

for filename in $DIR/*.txt
do
tablename=`basename $filename .txt`
mysql --user=$USER --password=$PASSWORD $DATABASE <<EOF
  SET FOREIGN_KEY_CHECKS=0;
  LOAD DATA INFILE '$filename' INTO TABLE \`$tablename\`;
EOF
done

Also I would like to note that there's no much sense in storing these per-table *.sql files produced by mysqldump --tab, because there's also a foreign key issue on fields. As a schema is always known I suggest just to delete them after backup is completed.

like image 115
saaj Avatar answered Oct 11 '22 15:10

saaj