bin/mysql -u username -p < '/path/to/filename.txt'
Since you will presumably be running the queries on a particular database, you will often use the -D option to select it in advance:
bin/mysql -u username -p -D databasename < '/path/to/filename.txt'
As just one example of this technology, I will run a batch script that creates a table and populates it.
Download the SQL commands for this book from the book's corresponding Web site (www.dmcinsights.com/mysql2
Every SQL command I use in the entire book is available in a single text file. You'll find this on the Extras page.
Unzip the downloaded file.
I've archived the text file in Zip format, so you'll need to expand it.
Open the text file in a text editor.
You can edit this in any text application, although Notepad (and perhaps some others) will not handle the line breaks properly.
Copy any SQL commands you want to run into a new text document.
You absolutely, positively should not run the entire SQL file as is! Doing so would take forever and make all sorts of messes. Instead, copy just the SQL commands you need at this time.
In this specific example, I will use a batch file to re-create and populate the expense_categories table, so I will need to copy its CREATE and INSERT statements. You can find them under the "Chapter 4" and "Chapter 5" headings in the text file.
Save the new file as ec.sql.
It doesn't really matter what name you use or where you save it, as long as you know where it is and what it's called.
Access your server from a command-line interface and move into the MySQL bin directory.
These instructions are in the second chapter, if you do not know how to do this.
If desired, create a new database (Figure 13.29
./bin/mysqladmin -u root -p create accounting2
Since I already have an accounting
database with this table (that I don't want to mess up), I'll create a new database.
Figure 13.29. In order to practice using batch files without altering my existing database, I'll create a new database using mysqladmin.
Run the batch file (Figure 13.30
./bin/mysql -u root -p -D accounting2 < '/path/to/ec.sql'
You'll need to change the /path/to/ec.sql
part so that it corresponds to the name and location of the file on your server. This might be C:\Documents and Settings\My Name\Desktop\ec.sql
on Windows or ~/Desktop/ec.sql
on Mac OS X (where ~
is your home directory).
Figure 13.30. To re-create the expense_categories table without retyping my SQL, I can use the batch mode.
Confirm that the SQL commands worked (Figure 13.31
./bin/mysql -u root -p accounting2
SELECT * FROM expense_categories;
Figure 13.31. Verify the results using the mysql client.