Import into MySQL?

[jhoover667]

Anyone know how to do a text import into MySQL on the futurequest servers since the mysql server and web server are on two different servers?[nbsp][nbsp]Thanks!

[Justin]

mysqldump -u<font color=#FF0000>username</font> -p -hmysql01 <font color=#FF0000>database</font> >./<font color=#FF0000>myfile.txt</font>

Where <font color=#FF0000>username</font> is your MySQL username, <font color=#FF0000>database</font> is the database you wish to do a text dump of, and <font color=#FF0000>myfile.txt</font> is the filename you wish to dump the data into (the example would place it in the current directory).

Hope this helps.

------------------
Justin Nelson
FutureQuest Support

[Shalazar]

A text dump sure sounds nice to fill a new database with some kind of existing list of info you may have lying around.

Do you have any references regarding mysqldump?[nbsp][nbsp]And for example, what kind of format a text list of information has to be in to be faitfully replicated in a table?

[Shalazar]

Ahhh, so it creates a text file from the contents of the database.[nbsp][nbsp]I got it backward.

Is there a way to do the opposite?[nbsp][nbsp]That is, move data from a file into a new database?

Or do entries often have to be entered manually to begin with?

[Dan Kaplan]

You could pay $450 for Links SQL and see how its flatfile -> MySQL import feature is setup...[nbsp][nbsp]I'm sure discussion forums like WWWThreads use something similar, so there's bound to be a common method.[nbsp][nbsp]Maybe the MySQL doc's has something?

Dan

[Justin]

As I mentioned, the text dump is simply a file full of MySQL queries... so just pass the filename to MySQL and it will create and populate the tables. I have been using this method for backups for over a year now...
</font><font face="Courier" size="3">
mysql -uusername -p -hmysql01 databasename <./myfile.txt
</font><font face="Verdana, Arial" size="2">
As for importing an ordinary text dump (eg not created by MySQL), this you'll have to search the documentation for... I'm sure MySQL has some sort of import feature, but since I work exclusively in MySQL, I haven't run accross the need just yet...

------------------
Justin Nelson
FutureQuest (http://www.FutureQuest.net/index.php) Support
[This message has been edited by Justin (edited 02-03-00@5:59 pm)]

[Dan Kaplan]

This is geared toward a specific application as mentioned above, but it might provide some useful insight:

http://www.gossamer-threads.com/scripts/forum/resources/Forum9/HTML/000551.html

Dan

[Justin]

mysqldump simply creates a text file containing queries to create the tables and insert the data:
</font><font face="Courier" size="3">
create table foo (
ID int default 0 not null auto increment,
firstname varchar(30) not null,
...
)

insert into foo values (NULL, &quot;blah&quot;...);
</font><font face="Verdana, Arial" size="2">
It even inserts some comments into the file so you can see what it's doing :)

Hope this helps.

------------------
Justin Nelson
FutureQuest Support

[PaulKroll]

There's also mysqlimport, which is on FutureQuest, though it doesn't have a man page.[nbsp][nbsp]Check out docs at http://www.mysql.com/Manual_chapter/manual_Tools.html#mysqlimport

I've used this on a test machine at work, to pull tab-delimited text into a table.[nbsp][nbsp]It works as advertised, and it's pretty quick too.[nbsp][nbsp]PostgreSQL has similar functionality built into its client, and in fact, I've used the same text file with both DBs whilst testing our database options at work.

[Cale]

I have been trying mysqldump -uusername -p -hmysql01 >./myfile.txt and get an access error(ERROR 1045).

Any help would be appreciated.

Thanks,

Cale[nbsp]

[Justin]

You may want to check out the new tutorial on the subject: http://www.aota.net/PHP_and_MySQL/mysqldump.php3

Hope this helps.

------------------
Justin Nelson
FutureQuest (http://www.FutureQuest.net/index.php) Support