I am not interested in a collection of links to sites that offer all
sorts of explanations of all the command line switches for doing this
(I could find these myself).

I want a researcher who knows the answer with confidence to respond to
this question with brevity/clarity and most importantly, accuracy in
mind.

I have two MySQL databases located on a server somewhere.  I connect
via secure shell.  I don't know all of the details about the
configuration of this particular server but it obviously has MySQL
installed/configured properly and you can assume that any other 'very
likely' items would also be resident.

I need to completley copy one database into the other (one is
currently quite large, the second is empty).

I know I have done dumps in the past with this sort of thing:
mysqldump -u USER -pPASSWORD DATABASE > filename.sql

so perhaps my primary question is how do I import this dump into the
second database.  I have had very little luck with the "mysqlimport"
command - which the MySQL documentation states is for importing text
files (which the sql dump seems to be) but it is not of simple
delimitation (if thats a word).

I should note that while the two databases exist on the same server
the username/passwords for them are different.

I ask because I am pressed for time and need to get this small detail
out of the way and don't have time to play around with it.  If your
answer does the trick for me, you get the money and a good rating.

Hello,

I manage a couple forums in MySQL which have different tables & data
which need to be backed up and every now and again restored.

Here's the simple command to restore the database using the file you
dumped in the first step:

mysql -u USER -p DBNAME < dump.sql

You might have to make sure the database you're importing into is
EMPTY because the import command listed above will not overwrite
structures existing already. Use PHPMyAdmin or another set of
delete/remove table commands to make sure the structures you're
importing do not already exists in the database you're importing into.
Other than that, it's simple as pie.


Additional Links:

http://www.he.net/faq/mysql.html#Anchor1020


Search Strategy:

mysql dump restore on google:
://www.google.com/search?q=mysql+dump+restore


Thank you for the opportunity to answer your question, if you require
more information, please clarify the question, or if you find this
answer satisfactory, please feel free to rate it. Thank you!

skermit-ga

To the point and most importantly: it worked.

I'm a little embarassed about just how easy that was.  I was trying to
do that exact thing with the 'mysqlimport' command as opposed to the
'mysql' command.

In any case, thanks a lot.  Your help came through in a state of
emergency.

If you wanted to get showy you could do it all on one line:

user@host1$ mysqldump mydbname | ssh host2 "mysql mydbcopy"

you may have to add user and password arguments.

If anyone is wondering why 'mysqlimport' is unsuitable for importing a
MySQL dump, the reason is that it's intended for importing from
various database formats such as CSV, tab separated, pipe separated
(popular among Unix programmers), files where each row of data exists
on an individual line and is separated by a character (such as the
comma) and terminated by the linefeed character.

The file mysqldump creates is a plain text file where the contents are
entirely SQL statements. Data from the original table, for each table
in the database, is exported as SQL statements, data from the original
table is contained in INSERTS and table structure is contained in
CREATE TABLE statements, etc. This is an excellent format to dump data
in, because in theory any SQL server can read SQL statements and thus
import the dump. Even if an important program were unavailable, you
are likely to get the server to read the SQL statements from the
command line monitor. If you have telnet/SSH access to MySQL, you can
start mysql and just copy and paste the contents of a dump into the
window. Not recommended for large amounts of dumped text, but I do it
for creating a table of a few lines (such as options that will be read
into a web form select menu) when I'm in a hurry.

The way that is described above is indeed a very sloppy way!

If you have access to the other box from that machine (if they're on
the same network/ on the 'net) you can use the following - this
essentially pipes the output from mysqldump directly into the other
databased - very handy indeed - instead of just dumping it into a file
and then manually ftp it to the other box the mysql < .... it in.

The first host is where you want to copy FROM and the second is where
its going TO.

mysqldump --opt --compress --user=USERHERE --password=PWHERE
--host=SOURCE.HOST.HERE SOURCE_DB_NAME | mysql --user=USERHERE
--password=PWHERE --host=TARGET.HOST.HERE -D TARGET_DB_NAME -C
TARGET_DB_NAME

Cheers
Phil