mysqlimport example?

[svar]

I have backup up an entire mysql database with
mysqldump --quick --password=mypassword
database_name >backup-file.sql

Now I need to recreate this with mysqlimport.

I have lloked at the manual and it is not clear to me
where one specifies the database and sql file
As far as I can read mysqlimport works one table at a time where the table is the name before the extention
(e.g. here backup-file). It is a mystery to me how
one specifies the database; Is there no automatic way to
recreate the entire database from the backup files
except with a hack(that is, use a program to parse
backup-files.sql, which consists of SQL statements
and excecute them?)

Thanks, svar

I tried

mysqlimport --host=localhost --password=mypassword
--delete backup-file.sql

 

[rycamor]

You don't user mysqlimport as the counterpart to mysqldump. This is a common (and understandeable misconception). Mysqlimport is for importing delimited text files into existing tables, such as .csv files exported from Access or Excel. it doesn't run any scripts to recreate a database or anything like that.

When you run mysqldump, you are dumping a series of SQL commands, which can re-create the database structure and insert the data, as if you were typing them from the command line. Ergo, to pipe your dumpfile back into the database, you just use "mysql":

mysql -u username -p databasename < backup-file.sql

This will run the contents of the backup-file.sql text file back into the mysql command interpreter, which will essentially "type very fast" and recreate your database.

Note: your initial method of calling mysqldump will contain all the specific table creation info for your database, but will not contain the command to actually create the database, so if a blank database called 'databasename' does not exist, mysql will give you an error message. If you really want transparent backup from mysqldump, try this:

mysqldump --opt -u username -p --databases db1 db2 db3 >backupfile.sql

Notice that you can name more than one database this way. This will create a script that (when piped to mysql) can check for the existence of the database, and if it does not exist, it will create that database. If the database exists, it will drop existing tables, and overwrite them completely with the backup data, thus preventing any commingling of existing data with your backed up data.

Enjoy ;-)