Run Mysql scripts in a batch?

Question

Can someone link me to a tutorial or explain if there is a way to create some sort of batch file of mysql scripts / stored procs and run them all at the same time? I can not seem to find any documentation on this online but I feel that I might be searching using the wrong terms.

Answer 1

You can chain mysql scripts by calling them from within a script using the source command (details of command line options)

# my_textfile.sql
# ---------------
USE my_database;
\. subscript1.sql
\. subdir/subscript2.sql
\. /full/path/to/subscript3.sql

Command Line:
mysql < my_textfile.sql

Don't forget the command line options, if you are going to script the files you might need the password/ user account.

mysql -uyouraccount -pyourpassword YourDatabase < mytextfile.sql

This isn't the most secure way to do it because it puts your username/ password on the command line but it works. If you are doing much scripting I suggest you look into .my.cnf and the various options for saving your account/ password in there (and securing that file).

Answer 2

You can simply create a text file with SQL statements separated with ; and then execute all statements with the MySQL command line client:

# my_textfile.sql
# ---------------
USE my_database;
SELECT * FROM table1;
UPDATE table2 SET foo='bar';

Command Line:
mysql < my_textfile.sql