Networking

Unix and Linux network configuration. Multiple network interfaces. Bridged NICs. High-availability network configurations.

Applications

Reviews of latest Unix and Linux software. Helpful tips for application support admins. Automating application support.

Data

Disk partitioning, filesystems, directories, and files. Volume management, logical volumes, HA filesystems. Backups and disaster recovery.

Monitoring

Distributed server monitoring. Server performance and capacity planning. Monitoring applications, network status and user activity.

Commands & Shells

Cool Unix shell commands and options. Command-line tools and application. Things every Unix sysadmin needs to know.

Home » MySQL, Scripts

Passing MySQL Commands from Shell Script

Submitted by on June 25, 2009 – 3:30 am 5 Comments

Running MySQL commands from a shell script is a relatively simple task that has a lot of people baffled. Some say its too complicated and suggest using PHP or Perl, others claim doing so is a security risk (a favorite excuse of the ignorant), and some resort to using a shell script to writing SQL commands to a text file that MySQL would use as input. Below is a much more simple and direct way of generating and running complex SQL queries directly from a shell script without temporary files and without any security issues.

Let’s start with the basic idea:

echo "SELECT * FROM table_name" | mysql -u -p db_name

Using this method, you can pass any shell variables to MySQL. Aha, some will say, you have to put your password in the shell script and that is definitely not secure. You don’t have to: you can have your script prompt you for a password:

#!/bin/bash
echo -n "Enter username: " ; read db_user
echo -n "Enter $db_user password: " ; stty -echo ; read db_passwd ; stty echo ; echo ""
echo "SHOW DATABASES ;" | mysql --skip-column-names -u$db_user -p$db_passwd
echo -n "Enter database name: " ; read db_name
echo "SHOW TABLES ;" | mysql --skip-column-names -u$db_user -p$db_passwd $db_name
echo -n "Enter table name: " ; read table_name
echo "SELECT * FROM $table_name ;" | mysql -t -u$db_user -p$db_passwd $db_name

The script above will prompt your for the username and password (password will not be visible as you type it). It will then show you the list of available database and prompt you to select one. The script will then show you all the tables in that database and ask you to specify the name of the table you want to use. Finally, the script will select everything from that table. Everything is very simple, secure, and straightforward.

Print Friendly, PDF & Email

5 Comments »

  • sixthfore@gmail.com says:

    Thank you, Igor!

  • Paul says:

    Really Great Thanks Please post more coding in shell scrip it will b really help full

  • altair says:

    I’ve installed mysql 5.5 to work with php.From the workbench I tried to connect first time,impossible.I’ve allowed the program through the firewall,nothing.I read all the tutorials on the subject, still I don’t understand one thing. How do I set admin privileges if I cannot connect? If I run the scripts on the command prompt I get error. Some tutorials talk about installing Wamp or other software, why do I need more junk on my pc to have the damn mysql working? And when they talk about the shell, where is the damn shell on the workbench?It does not work.I like my sql express and management studio better, do I need mysql to work with php? Getting frustrated.Thanks for your help.

  • baldy eire says:

    How to write a shell script that will print the following structure:

    empty space…….5
    empty space….4 5 4
    empty space.3 4 5 4 3
    empty space….4 5 4
    empty space…….5

    I only need the numbers in this structure.
    Can someone give me the shell script for this problem?

  • John says:

    How to write a small address book application using shell scripting
    * Application should ask whether to search or add
    * Searching should display the entity

Leave a Reply

%d bloggers like this: