List:General Discussion« Previous MessageNext Message »
From:Douglas Sims Date:January 14 2004 2:45am
Subject:Re: Importing a dumpfile
View as plain text  
Hi Mat

mysqldump produces files containing SQL statements.  mysqlimport allows 
you to load data from comma-delimited (or other) text files.

For example, the following line will dump the contents of the table 
'goat_painters' in the database 'the_goat_database' into a file called 
'goat_painters.sql':

mysqldump the_goat_database.goat_painters -u mat  > goat_painters.sql 

And the file 'goatowners.sql' will contain something like this:

INSERT INTO table1 (name, address, number_of_goats_painted) VALUES 
('Hayes', '123 Maple Street', 3);
INSERT INTO table1 (name, address, number_of_goats_painted) VALUES 
('Delaney', '123 Birch Blvd', 4253);
INSERT INTO table1 (name, address, number_of_goats_painted) VALUES 
('Wesley', '418 Oak Ave', 92);
INSERT INTO table1 (name, address, number_of_goats_painted) VALUES 
('Doug', '418 Oak Ave', 7);
INSERT INTO table1 (name, address, number_of_goats_painted) VALUES 
('Rachel', '123 Maple Street', 326);
INSERT INTO table1 (name, address, number_of_goats_painted) VALUES 
('Amy', '123 Maple Street', 0);
...

And you could recreate this information into the database with:

mysql the_goat_database -u mat < goat_painters.sql

whereas if you executed the command (e.g., from within the mysql 
command-line client):

SELECT INTO OUTFILE 'goat_painters.txt' name, address, 
number_of_goats_painted FROM goat_painters;

you would get a file called goat_painters.txt containing something like 
this:

'Hayes', '123 Maple Street', 3
'Delaney', '123 Birch Blvd', 4253
'Wesley', '418 Oak Ave', 92
'Doug', '418 Oak Ave', 7
'Rachel', '123 Maple Street', 326
'Amy', '123 Maple Street', 0
...

And you could then import that file directly into mysql using mysqlimport.

If you've used mysqldump, you will have a sql script (batch) file and 
you should use the method like this:
mysql the_goat_database -u mat < goat_painters.sql

I hope that helps.




Matthew Stuart wrote:

> Right having just got to grips with the mysqldump command, I would 
> like to be able to know how to import the database back in to MySQL 
> should anything happen to my PC.
>
> Does mysqlimport have to be done in the command line window like 
> mysqldump, and if so, how? It's just that I tried to import stating 
> terminated, enclosed, escaped, etc and by the time I had come to list 
> the db name to import in to and the path to the file I wish to import, 
> the window wouldn't let me type anymore. Why? Did it get as bored as I 
> did?
>
> What syntax do you people out there use?
>
> Mat
>
>


Thread
Importing a dumpfileMatthew Stuart14 Jan
  • Re: Importing a dumpfileDaniel Kasak14 Jan
  • Re: Importing a dumpfileDouglas Sims14 Jan
  • Re: Importing a dumpfileRoger Baklund14 Jan
    • why: mysqldump and mysqlimport?EP14 Jan
      • Re: why: mysqldump and mysqlimport?Dan Nelson14 Jan
      • Re: why: mysqldump and mysqlimport?Tobias Asplund14 Jan
      • Re: why: mysqldump and mysqlimport?Daniel Kasak14 Jan