June 10, 2011 
[updated 2011-06-10 because a friend pointed out a flaw in my assumption that I needed to enter the password every time – see bottom for updates]
I am architecting a project that is a C# front end, hitting SOAP web services, that communicate with a C# core, and has a MySQL database backend (accessed using a buddy’s custom ORM solution). I use MySQL Workbench to manage the schema and model of my database and I store all DDL and procedures in SVN, along with the seed scripts to put development data into the database.
I need to be able to make changes to the schema, add procedures, or seed data scripts, save them into SVN, and rebuild it on my integration server with a single command. I need other developers to be able to generate the database on their local instances quickly as changes are made. For modularity, each file is a separate script meant to be run, in order, to generate the database, procedures, and seed data. A sample directory listing looks like this:
Directory listing (click to enlarge)
As you can see – I have a a lot of scripts to run, and the list grows daily as new procedures and seed data are added. Scripts to create the database, scripts for procedures, scripts for seed data… I initially tried to run them all individually:
mysql -h localhost -u root -p < 001_Create.sql mysql -h localhost -u root -p < 002_AppUserProcs.sql mysql -h localhost -u root -p < 003_InterestProcs.sql etc….
The only problem with that is that it asked me for my password on every single execution.
I looked around for ways to put the password “in memory” or to require it only once, but after 15 minutes of Google research my ADHD kicked in and I needed to do something else. What I am about to suggest may not be the only way to accomplish this, but it’s what I came up with. It works, and it’s fast.
The solution: combine all the sql files into a single script file and then execute that single script file. I wrote a DOS batch called generate.bat that combines all of the script files into a single file called sqlrun.txt and then I execute the mysql command line utility against that file. This way, I only get asked a single time for my password. Here is what v1.0 of generate.bat looks like:
@ECHO OFF ECHO Deleting Previous Files DEL sqlrun.txt ECHO Creating script to run FOR %%X IN (*.sql) DO type %%X >> sqlrun.txt ECHO Running script mysql -h localhost -u root -p < sqlrun.txt ECHO Deleting temporary files DEL sqlrun.txt ECHO Finished!
The secret to this is that it loops through every .sql file in the folder and creates a new file called sqlrun.txt, which then gets executed. Because scripts have to be run in a VERY specific order, I utilize the fact that the DOS FOR command will iterate through the folder alphabetically and I name each of my files numerically so it builds sqlrun.txt in the order I want it to. Also, the output file (sqlrun.txt) is a .txt file and not a .sql file so it doesn’t get picked up in the FOR statement.
Viola – problem solved. I am asked only one time for my password and my development team can quickly regenerate the database and seed data on any machine as often as changes are made.
Update 2011-06-10
A follower, previous boss, co-worker, and friend, Gary pointed out a flaw in my initial logic. The mysql command line utility will NOT ask you for your password every time if you use it correctly (which I did not). I was putting a space after the -p in the password argument, which is why it did not work for me.
Still, the method I proposed works if you don’t want to hard code the password into your file. If you don’t mind having the password in your file you don’t need to make a single, massive, sql file and you can change your DOS batch file to the following:
@ECHO OFF ECHO Running scripts FOR %%X IN (*.sql) DO mysql -h localhost -u root -pMYPASSWORD < %%X ECHO Finished!
This works VERY well for automated build and deployment scenarios where you push the code automatically and without human intervention. Thanks, Gary, for the correction.
The Binary Biker 
mysql -h localhost -u root -pmypassword
Try that… 🙂
Actually – let me amend my blog post because you are right, Gary. I initially though it asked for my password every single time because I put a space after the -p before my password.
I also wanted the script to find all new files and I did not want to code in the password as each developer machine has different password.
I really don’t know it could be used in such ways. Great!
Thanks for the post. Wanted to do this using My SQL Workbench, but can’t find a way to get it to allow multiples to be run sequentially. I got really tired of selecting and pushing the button after about 5 files. 😉 Your script did the trick.
You create new connection for every script. If there are interrelations among scripts (user variables, temporary tables etc.), this won’t work.
Than you would say: well combine scripts into one file. But what if each script is hundreds of MB large? Combining into one file is not an easy task than…
Do you have any solution to run multiple scripts within one connection?
when I run your scripts , it gave me error : database not selected.
I edited this line, and it works!
FOR %%X IN (*.sql) DO mysql -h localhost -u root -pMYPASSWORD MYDBNAME < %%X
Thanks for the clarification, Indra. The reason my scripts worked without the DB name is that all of my scripts had “use DBNAME;” in them already. Your modification is a great way to make sure it runs on the right database if that line is missing from the script itself.