Making a command script
If the script you have to create is just to be executed as-is by people having access to the server and if the data to be put into the DB are delivered to you (so nothing dynamic), you would have to make a bash script like this :
if [ "x$1" == "x" ]; then
echo "Usage: $0 <SQL commands file>"
if [ ! -f "$1" ]; then
echo "The file specified in parameter ($1) does not exist or is not readable"
mysql -u$USER -p$PASS -h$ipMysql $DB < $1
Copy all the above in one test file that you can call dataUpload.sh and then make it executable :
sudo chmod +x dataUpload.sh.
What does this script do?
- Check that you give a mandatory parameter : the path to a text file containing SQL command to update data in the DB (see below)
- Check that the file given in parameter is accessible by the script
- Set some variable with the credential to connect to the DB (attention that this is a quick approach here not taking some security aspect into consideration : for instance the password will be visible in clear text in the script and in the process list -
ps -ef or
ps auxwww when the script will run. You may look for a more secure approach)
- Load the SQL commands of the SQL file into the DB using the command line
A SQL command file
This SQL command file is just a text file containing all the SQL command to
UPDATE data you would have done yourself if you could access the server. One command by line, like in this example:
UPDATE myDb.member SET member_creditDueDate='2025-09-21 12:00:00';
UPDATE myDb.member SET member_credutDueDate='2025-10-11 00:00:00';
and so on.
If you write a file, lets say, called 20140620_dbupdate.sql, then the person who has access to the DB will just have to execute
dataUpload.sh 20140620_dbupdate.sql at the command line prompt.
By splitting the shell and SQL commands, you can:
- easily test the logic that loads data into the DB
- pass only once the executable script
- for later update, just focus on building a SQL commands file
More to do
This is a first quick approach to solve your problem, to be exhaustive I would need to cover :
- Securing the password
- Making use of SQL transactions
- More error detection and reporting in the script