MySqlDump to individual files and restoring again

Make sure that your mysql user has permission to access the file system (the FILE privilege). GRANT ALL does not do this. You can check your user by doing:

select user, host, file_priv from mysql.user;

To enable:

grant FILE on *.* to 'root'@'localhost';flush privileges;

Also make sure that mysqld can write to the specified folder (./sql below). To dump:

mysqldump -uroot -p your_database --tab=./sql

Importing back is a two step process, DDL first followed by tab-delimited data:

DDL

cat sql/*.sql | mysql -uroot -p your_database

Tab delimited data

NOTE: The path you provide MUST be absolute. Nevermind, you can use --local to fix
NOTE: The -d parameter means DELETE from the current table before importing!

mysqlimport --local -uroot -p -d your_database sql/*.txt

Additionally, you might want to de-domain your files (for instance, if you’re backing up a WordPress site)

find sql/ -type f -name '*.txt' -exec sed -i 's/old_domain.com/place_holder/g' {} \;

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.