How to access mysql database using shell script?

Question

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.

Answer 1

This link seems to have the information you want.

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

mysql -u user -p dbnane

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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.