0

I’m looking to create a deploy script that I can run from a terminal and it automatically deploys my site from a repository. The steps I’ve identified are:

  • Connect to remote server via SSH
  • Fetch latest version of site from remote repository
  • Run any SQL patches
  • Clean up and exit

I’ve placed the SSH connection and git pull commands in my shell file, but what I’m stuck with is MySQL with it being an (interactive?) shell itself. So in my file I have:

#!/bin/bash

# connect to remote server via SSH
ssh $SSH_USER@$SSH_HOST

# update code via Git
git pull origin $GIT_BRANCH

# connect to the database
mysql --user $MYSQL_USER --password=$MYSQL_PASSWORD --database=$MYSQL_DBNAME

# run any database patches
# disconnect from the database
# TODO
exit 0

As you can see, I’m connecting to the database, but not sure how to then execute any MySQL statements.

At the moment, I have a directory containing SQL patches in numerical order. So 1.sql, 2.sql, and so on. Then in my database, I have a table that simply records the last patch to be run. So I’d need to do a SELECT statement, read the last patch to be ran, and then run any neccesary patches.

  1. How do I issue the SELECT statement to the mysql prompt in my shell script?
  2. Then what would be the normal flow? Close the connection and re-open it, passing a patch file as the input? Or to run all required patches in one connection?
  3. I assume I’ll be checking the last patch file, and doing a do loop for any patches in between?

Help here would be greatly appreciated.

Martin Bean
  • 38,379
  • 25
  • 128
  • 201
  • The `ssh` command will also give you an interactive shell. You need to pipe commands into SSH as well, or tell it to execute a specific file on the remote computer. – Erik Ekman Jan 08 '13 at 14:30
  • This maybe a good answer for you: [How to execute Mysql command from a shell script?](http://stackoverflow.com/questions/8055694/how-to-execute-mysql-command-from-a-shell-script) * from another stackoverflow question – tech_me Jan 08 '13 at 14:07

3 Answers3

1

Assuming you want to do all the business on the remote side:

ssh $SSH_USER@$SSH_HOST << END_SSH
git pull origin $GIT_BRANCH
mysql --user $MYSQL_USER --password=$MYSQL_PASSWORD --database=$MYSQL_DBNAME << END_SQL
<sql statements go here>
END_SQL
END_SSH
glenn jackman
  • 238,783
  • 38
  • 220
  • 352
0

You could get the output from mysql using Perl or similar. This could be used to do your control flow.

Put your mysql commands into a file as you would enter them.

Then run as: mysql -u <user> -p -h <host> < file.sqlcommands.

You can also put queries on the mysql command line using '-e'. Put your 'select max(patch) from .' and read the output in your script.

ethrbunny
  • 10,379
  • 9
  • 69
  • 131
  • How would this work as I’d need to then loop over the file system, parse the contents of my patch files and then execute them as MySQL statements? – Martin Bean Jan 08 '13 at 14:04
  • You put the whole patch file as input. Take a look at the output from 'mysqldump' to see. – ethrbunny Jan 08 '13 at 14:05
  • But the patches would be individual files, not one whole one. So say, the last patch to be ran may be **5.sql** (as recorded in the database table), but I’ve since created **6.sql** and **7.sql** — I’d like **6.sql** and **7.sql** to therefore be ran. – Martin Bean Jan 08 '13 at 14:10
  • You need to run each patch sequentially? Run the first query to get 'latest unrun patches' then use this output to loop through these and run them in order. Your 'unrun patches' could be a join between 'available patches' and 'patches run for a given host'. Select for 'ifnull' (make it a left join). – ethrbunny Jan 08 '13 at 14:21
0
cat *.sql | mysql --user $MYSQL_USER --password=$MYSQL_PASSWORD --database=$MYSQL_DBNAME
bobwienholt
  • 17,420
  • 3
  • 40
  • 48