4.8.12 How to Run SQL Commands from a Text File

The mysql client typically is used interactively, like this:

shell> mysql database

However, it's also possible to put your SQL commands in a file and tell mysql to read its input from that file. To do so, create a text file `text_file' that contains the commands you wish to execute. Then invoke mysql as shown here:

shell> mysql database < text_file

You can also start your text file with a USE db_name statement. In this case, it is unnecessary to specify the database name on the command line:

shell> mysql < text_file

If you are already running mysql, you can execute an SQL script file using the source command:

mysql> source filename;

For more information about batch mode, section 3.6 Using mysql in Batch Mode.

User Comments

For gzipped files:

gunzip -c file.gz | mysql database

Yes, you can run a script from the MySQL command
line by typing "\. <script Name>"

Is it possible to execute a SQL script file when
you are already in the mysql prompt?

Is it possible to pass variables from the shell to a mysql script.

Say each day I have a list of clients to check, then I want to do something like:

for x in client1 client2 client3 ...
do
mysql < script-to-run
done

obviously this will not pass 'x' to the script-to-run, but is there a way of getting mysql to do this.

All I can think of is to create a table which holds a client name only. Then set the client name, then run the script which reads the client to use.

- Any easier / cleaner methods?

Paul

Add your own comment.