mysqld daemon must be running and you must be in the mysql directory. Type the following at the
command line:
bin/mysql –p < /home/mark/New_Data.sql
Note Daemon is a UNIX term, which equates, for the most part, with Windows NT
services.
You will be prompted for a password, and then the file will be routed into the mysql program. mysql
will process every line as if you had typed it into the monitor. This is an extremely fast and effective way
of processing SQL statements. As long as your statements are correct, they will be processed.
The USE statement is required because the default database was not stated. You could have stated the
database and left the USE out. It's used here to show you how many commands you could type into the
monitor program can be used in a file.Now you may be asking yourself, "Why in the world would I want to type all those SQL statements into a
file and then run them through the program? That seems like a lot of work." Well, you're probably right.
But what if you had a log that generated all these commands for you? Now that would be pretty cool,
huh? Most databases generate a log of events that occurred in the database. Most of these logs contain
the raw SQL commands that were used. So, if you couldn't export the data from your current database
to use in your new MySQL database, you could use that log and the batch processing feature of MySQL
to import your data easily and quickly. Sure beats typing.LOAD DATA INFILE
There is one last way of importing data into a MySQL database. It is quite similar to the mysqlimport utility,
but this method can be run from the MySQL monitor command line. This means that it can also be run from
any of the applications that you write using the APIs. That way, you could import data through your
application if you wanted.
To begin this process, the mysqld daemon must be running and you must be in the mysql directory.
Start the MySQL monitor. In case you have forgotten, type the following from the command line:
bin/mysql –p
You will be prompted for a password. After you have successfully started the monitor, type the following
command to use the Meet_A_Geek database.
USE Meet_A_Geek;
The monitor will tell you that the database has changed. From the command line, type the following
commands:
LOAD DATA INFILE "/home/mark/data.sql"
INTO TABLE Orders;
Basically, this will take the contents of the data.sql file and import it into the Orders table, just as
with the mysqlimport utility. This statement, like the utility, also has some optional arguments. For
example, if the file that you needed to import resided on your personal computer and the database
server was in another building, you could run the following command:
LOAD DATA LOCAL INFILE "C:\MyDocs\SQL.txt"
INTO TABLE Orders;
This would take the file's contents and import them into the Orders table. No need to transfer the file,
MySQL will do it for you.
You can also set the priority of the INSERT. If you marked this as a LOW_PRIORITY, MySQL would wait
until there is no one else reading the table before it INSERTs the data. That command would look like
the following:
LOAD DATA LOW_PRIORITY INFILE "/home/mark/data.sql"
INTO TABLE Orders;
You can also specify whether or not you want to REPLACE or IGNORE items with duplicate key values,
much like you could with the mysqlimport utility. To REPLACE duplicate values, use the following
syntax:
LOAD DATA LOW_PRIORITY INFILE "/home/mark/data.sql"
REPLACE INTO TABLE Orders;It looks kind of awkward, but that's where the keyword needs to go for the parser to understand what
you want.