CODEWITHSUNDEEP
mysqlshell
Martin Bean
Martin Bean

Reputation: 39439

Executing MySQL commands in shell script?

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:

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.

Upvotes: 0

Views: 7936

Answers (3)

glenn jackman
glenn jackman

Reputation: 247210

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

Upvotes: 1

bobwienholt
bobwienholt

Reputation: 17610

cat *.sql | mysql --user $MYSQL_USER --password=$MYSQL_PASSWORD --database=$MYSQL_DBNAME

Upvotes: 0

ethrbunny
ethrbunny

Reputation: 10479

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.

Upvotes: 0

Related Questions