11

Is there a way I can access MySQL database using shell script? want to do some selects and some inserts on multiple tables?

It will be great if you can give some sample code as I am new to scripting.

TemplateRex
  • 69,038
  • 19
  • 164
  • 304
Bhushan
  • 18,329
  • 31
  • 104
  • 137

6 Answers6

7

This link seems to have the information you want.

http://www.cyberciti.biz/faq/using-mysql-in-shell-scripts/

mysql -u user -p dbnane
Lactose
  • 695
  • 1
  • 7
  • 15
  • This will prompt for a password, if you're using a script it is likely that the password is known from some secrets store, and so it would be useful to provide it `mysql -u ${username} -p${password} ${dbname}` - but also you'll probably need to specify the host and maybe port. – Peter Kionga-Kamau Mar 15 '23 at 12:34
6

try this

#!/bin/bash
echo "show all tables"
mysql -uroot -p'password' dbname<<EOFMYSQL
show tables;
EOFMYSQL
echo "Count of all records"
mysql -uroot -p'password' dbname<<EOFMYSQL
select count(*) from tbname;
EOFMYSQL
androidevil
  • 9,011
  • 14
  • 41
  • 79
ishimwe
  • 1,216
  • 12
  • 13
3

You can use the mysql command-line tool, from your shell-script.

Pascal MARTIN
  • 395,085
  • 80
  • 655
  • 663
1

For example, select all the name field in table tablename of database dbname and redirect all name to /tmp/all_name.txt.

#!/bin/sh

mysql -uuser_name -puser_pwd -h10.10.10.10 -A --default-character-set=utf8 \
     -e "select name from dbname.tablename;"  > /tmp/all_name.txt

Note, -uuser_name not -u user_name, -puser_pwd not -p user_pwd

Jayhello
  • 5,931
  • 3
  • 49
  • 56
1

Actually, I achieved it using Perl. I wrote a Perl script which was able to access the MySQL database.

All I had to do was include this in my Perl script:

# PERL MODULES USING
use DBI;
use DBD::mysql;

But just make sure that these modules are properly installed. I don't know how to do it since my System Administrator did it for me.

You can access db like this:

# MYSQL CONFIG VARIABLES
$platform = "mysql";
$host = "<your db server ip>";
$database = "<db name>";
$org_table = "<table name>";
$user = "<username>";
$pw = "<password>";

# DATA SOURCE NAME
$dsn = "dbi:$platform:$database:$host:$port";

# PERL DBI CONNECT
$connect = DBI->connect($dsn, $user, $pw);

Now suppose you create a query string, then you want to execute it:

#SELECT THE ORG SHORT NAMES
$select_org = "SELECT id, short_name FROM $org_table";
$org_handle = $connect->prepare($select_org);

$org_handle will have the resultset.
Bhushan
  • 18,329
  • 31
  • 104
  • 137
0

I'd recommend using --parameters instead of -u and -p just because they are explicit, also the default --port is 3306 and --host is localhost so if you are using a different --port or --host you'll also need to specify those values. I'd also recommend setting a timeout on the command to prevent hangs if there is no response from the server for whatever reason:

#!/bin/bash

mysql_user="your_username" # should be in a secret store
mysql_pass="your_password" # should be in a secret store
mysql_host="localhost"
mysql_port="3306"
mysql_query="SHOW DATABASES;" # for example

mysql_result=$(timeout 5 mysql \
    --host="${mysql_host}" \
    --user="${mysql_user}" \
    --password="${mysql_pass}" \
    --port="${mysql_port}" \
    -e "${mysql_query}")

if [ $? -ne 0 ]; then 
    printf "MySQL connect/query failed.\n"
    # add whatever you need to do when a failure happens here.
fi

for row in ${mysql_result[@]}; do
    printf $row
done

If you don't have MySQL installed on the machine that is running the script, you'll need to install the MySQL client, the procedure is slightly different depending on your platform. For example:

Ubuntu:

sudo apt install -y default-mysql-client

Windows (via Chocolatey):

choco install -y mysql-cli

MacOS: This SO link describes install methods that have worked

Peter Kionga-Kamau
  • 6,504
  • 2
  • 17
  • 13