Populating database tables

mespejo · December 15th, 2003, 10:55 PM

Hi

Can anyone please tell me how to populate a table in mysql from an external file (i.e. pop_table.sql which I downloaded from this site)?

In reference to Chapter 11, page 420

cmiller · December 16th, 2003, 01:42 AM

the first suggestion is:

http://www.phpmyadmin.net/home_page/

Go get this program...

I don't recall the actual command to do the manual install because I always use this program.. Great for managing MySQL!

Good luck.

----------
~cmiller

mespejo · December 16th, 2003, 02:51 AM

Hey, thanks a lot!

Cheers:)

richard.york · December 16th, 2003, 02:54 AM

phpmyadmin makes things pretty easy if you want a PHP-based solution. However if you are looking for a command line solution to work directly with the MySQL server you could read the MySQL manual entry for the mysqldump utility which shows the syntax for importing SQL from an external file.

Quote:

quote:From: http://www.mysql.com/doc/en/mysqldump.html

The most normal use of mysqldump is probably for making a backup of whole databases. See section 4.5.1 Database Backups.

mysqldump --opt database > backup-file.sql
You can read this back into MySQL with:

mysql database < backup-file.sql
or

mysql -e "source /path-to-backup/backup-file.sql" database

hth,
: )
Rich

:::::::::::::::::::::::::::::::::
Smiling Souls
http://www.smilingsouls.net
:::::::::::::::::::::::::::::::::

mespejo · December 16th, 2003, 03:29 AM

Hi Rich

This is exactly what I am looking for...

Thanks for your input.

Cheers:)
Cel

mespejo · December 16th, 2003, 04:08 AM

Hi Rich

Pardon me for being obtuse but this is in reference again with Chapter 11, page 420. I'm having trouble understanding the manual you have stated above.

I have a database called sample_db with 2 tables in it namely access_log and user. Since these tables needed to be populated, I don't want to type all these data by hand so I am looking for a way on how to copy the SQL codes necessary to populate these tables from an external file called pop_table.sql

Can you pls. show me how to use the correct syntax for importing SQL from this external file?

nikolai · December 16th, 2003, 12:28 PM

cat your_dump_file | /path/to/mysql/bin/mysql -uusername -ppassword databasename

Take care,

Nik
http://www.bigaction.org/

mespejo · December 16th, 2003, 03:53 PM

Thanks Nik! Problem solved...

Cheers
Cel

nikolai · December 17th, 2003, 04:53 PM

Another tip: You can use mysqldump to dump the contents of one database directly into another, even if the databases are located on different hosts:

mysqldump -uuser -ppass -hhostname -q database | mysqldump -uuser -ppass -hhostname database

The -q option to mysqldump means that it doesn't buffer the output until it's all done to display it. Without that flag, mysqldump will create a huge string containing all the dump strings, and then output that string all at once.

With large dumps, it's much quicker to just dump output as it's generated, instead of buffering it all. That way, mysql can start creating the other database as it recieves dump output, instead of waiting for mysqldump to completely finish first.

Take care,

Nik
http://www.bigaction.org/

Andyy · November 30th, 2004, 11:02 AM

Hi

Is it posible to copy data from -tables- between two databases

Andrew