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?
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.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With