Best way to automate running several MySQL commands

Question

We have several core db tables on our system and allow our employees access to these tables via views that just show them the data they need to see.

We created these views in a new db specific to the new user. Creating all of these views requires running several mysql queries.

I would like to automate this process into a script, but feel there must be tools already out there that take a list of queries and run them.

Can anyone recommend any such tools or even any built-in features in mysql that could assist me here ?

Thanks!

Answer 1

Create a text file containing all your commands. Call it newuser.sql. Then when you want to set up a new user, let's call him gaius, you would simply do

$ cat newuser.sql|mysql --user=gaius --password=abc123 somedb

Obviously you could even create new users in a script, let's call it newuser.sh

#!/bin/bash

echo "create user '$1'@'%' identified by password('$2');"|mysql --user=root --password=secret
cat newuser.sql|mysql --user=$1 --password=$2 somedb

Then do

$ ./newuser.sh gaius abc123

Answer 2

Try cron jobs, also known as crontab, cron etc etc.

Answer 3

Creating a view is nothing more than

create db.view as select ...

What do you want to automate here?

Answer 4

Stored procedures

Perl

PHP (especially handy from web page)

GRANT access to each user down at the column level. (As an alternative to VIEWs.)