Using Mysql From Shell Scripts
Running mysql from a shell is not really a bash specific concern, but what we will be getting into for starters is running mysql from a shell script using the bash scripting syntax. For starters some of the concerns we may have are protecting passwords from the history file, and how to specify parameters on the command line without being prompted. Continue on the next section to find out more.
Using Mysql From Shell Scripts Details
The Mysql Client command line parameters
For starters, we if we want to use mysql in a script, we should set up a variable for the mysql client program somewhere near the top of our script so that if the location changes then we only need to update one location. If you want to know why the location might change, think about making your script portable between linux versions, flavors, or even other unix&squo;s. To set up the variable use the following:
This will also negate the worry about a having to set up a proper path being setup before running the script should that be a concern. To find out what all of the command line options are for the mysql client, we simply need to type the following into an interactive shell:
This should probably be piped to the more or less programs and optionally to grep if we know what we are looking for. I would like to highlight a couple of entries that will help us off for starters.
-B, –batch Don&squo;t use history file. Disable interactive behavior. (Enables –silent)
-p, –password[=name] Password to use when connecting to server. If password is not given it&squo;s asked from the tty.
-u, –user=name User for login if not current user.
-D, –database=name Database to use.
These command line parameters will allow us, at a minimum, to specify what user we would like to use, what their password is, and to protect ourselves from the history file. This will not protect us from the bash history file which will save the information if we are typing this into the interactive shell. The final command line parameter allows us to specify which database we would like to use.
Piping content to the mysql client
In order for us to be doing anything useful with scripting we will need to be able to use some stored commands. This will start off for us as a simple text file with a few sql commands embeded within. ( NOTE: mysql dump files have sql commands in them. ) We will start off with some simple ones.
CREATE DATABASE test;
CREATE TABLE testtable ( id INTEGER, name varchar(64));
INSERT INTO testtable VALUES ( 1,&squo;test 1&squo; );
INSERT INTO testtable VALUES ( 2,&squo;test 2&squo; );
INSERT INTO testtable VALUES ( 3,&squo;test 3&squo; );
If we save the previous commands to a file called test.sql, then we can perform the following command to run the script.
cat test.sql | mysql –user=user –password=password -B
Another method we might choose to use is to simply echo the commands directly to mysql:
echo “SELECT * FROM testtable” | mysql –user=user –password=password -B
And optionally if we need multiline we could either use a variation of the echo command or not include the closing delimiter to the ”
echo "SELECT * FROM testtableINSERT INTO testtable VALUES (4&#44;&squo;test4&squo;)"|mysql --user=user --password=password -B echo -e "SHOW TABLES\nSELECT * FROM testtable" |mysql --user=user --password=password -B
More Useful mysql scripting helpers
Now that we have a few basics down, lets get more into some moderate lifting power. In order to save the results of our queries and make them more useful for scripting, we will need to use a few more command line tricks:
mysql –help will return the following information for us:
–column-names Write column names in results. -N, –skip-column-names Don&squo;t write column names in results. WARNING: -N is deprecated, use long version of this options instead.
-s, –silent Be more silent. Print results with a tab as separator, each row on new line.
You may be wondering why some of the previous lines might be helpful. Well, lets say we wanted to actually get some of these values into a variable instead of a file that we might have to parse to make useful. We might not want the column name to appear, especially for a single column. This will become more apparent if we think about the following potentials.
When we use the backtick operator and parenthesis we can save the output of a command into an array, or simply view the next command:
TABLES=(`echo -e “USE testnSHOW TABLES” |$MYSQL`)
The previous command will store all of the table names in the test database as an array in bash. Now we can potentially use a for loop or a while loop to iterate through the array and perform some action on the tables like analyzing, backing up, vaccuming, etc.
Using Variables and replacing using mysql template files
Suppose that we want to go one step further in our scripting and use our scripts to perform the same set of actions on generic tables and or databases. Or for instance say that we want to make our scripts a little more dynamic. We have two options: one would be to use simple variable replacements in script, and the other would be to use a template file and use a pattern replacement program like sed. For the former lets try the following:
TABLE="test" echo "SELECT * FROM $TABLE" |$MYSQL # or TABLE="test" LIMIT=0 OFFSET=1 echo "SELECT * FROM $TABLE WHERE $WHERE LIMIT $LIMIT&#44;$OFFSET" |$MYSQL
A larger commands and options can only be supported easily in a file and using sed to replace them. We will start by performing the following using the heredoc syntax from the command line.
cat &lt;&lt; EOF &gt;template.sql USE __DATABASE__;SELECT MAX(id) FROM __TABLE1__ WHERE name="__NAME__" EOF
now in a script we need at least:
DATABASE="test"; NAME="test name"; TABLE1="table1" ; cat template.sql |sed -e &squo;s/__DATABASE__/&squo;"$DATABASE"&squo;/g&squo; -e &squo;s/__TABLE1__/&squo;"$TABLE1"&squo;/g&squo; -e &squo;s/__NAME__/&squo;"$NAME"&squo;/g&squo; |$MYSQL
The previous command will globally ( &squo;g&squo; ) substitute our custom replacement strings __DATABASE__, __TABLE1__, and __NAME__ with the values that are within the variables $DATABASE, $TABLE1, $NAME respectively.