قالب وردپرس درنا توس
Home / Tips and Tricks / Check a value in a MySQL database from a Linux Bash script – CloudSavvy IT

Check a value in a MySQL database from a Linux Bash script – CloudSavvy IT



Make bash scripts run differently based on the contents of a MySQL database by connecting to it from the command line and sending a query.

You can make your bash scripts run differently based on the contents of a MySQL database by connecting to it from the command line and sending a query that you can use in if blocks to branch based on a value.

Note: Our examples show that the root user is used on MySQL, but you want to replace another user account there.

Run SQL queries from a Bash script

The syntax for running inside a bash script is the same as accessing a database from the command line:

mysql -u root -pPassword -h hostname -D dbname -e 'query'

If your database is running locally, you can omit it -h flag. If you run a development database, you can omit it -p flag if your database does not have a password.

The result of the query is sent to STDOUT, which you can beep into a file:

mysql -u root -D dbname -e 'SELECT * FROM table' > file

… or store in a variable with $( ) construct:

variable=$(mysql -u root -D dbname -e 'SELECT * FROM table')

But the output will not be very beautiful by default, so you can make it easier to work with by using -B flag for printing in TSV (Tab Separated Values) format and -N flag to omit column headings.

Use the -B flag to print in Tab Separated Values ​​(TSV) format and the -N flag to omit column headings.

TSV files are the lesser used cousin of CSV files, which you can convert from the command line.

Control of a specific value and branching

If you want to branch based on a value in your database, you can query for the specific row and column and store the answer in a variable. You can then use a bash if block to branch based on the content of the variable.

field=$(mysql -u root -BNe 'USE test; SELECT label FROM test WHERE id=1')

if [ $field == 'TEST' ]; then
    //do stuff
fi

This only works when you request a specific value and make a simple comparison. If you need more manipulation than bash provides, you can use awk, which can select individual columns from text and make comparisons:

if [ $(echo $field | awk '{if ($1 == value && $2 == value2) print 1; else print 0}') == "1" ]; then
    //do stuff
fi

Here, it awk the command prints true or false (1 or 0), which bash if can use to compare and branch out.

Both of these examples assume that your SQL query returns only one line, which you can assure by selecting based on ID. However, if you are running a more complex query, you can limit the answer to the first line with the SQL command LIMIT 1.

Error check: Check if there is a database or table

While you are probably writing this script specifically for your server, no error checking will ever hurt anyone. You want to verify that the database and tables you are working with actually exist before joining them. If the connection to the database does not work, it will also be wrong here.

The SQL command 'USE dbname' will give an error if dbname does not exist (return code> 0). You can use this in one if block to check if the database is configured correctly:

if mysql -u root -e 'USE mydbname'; then
  
    //database exists, do stuff

fi

You can invert if block by starting boolean with one !, which only runs the code when the database is not configured, which is useful for the first time installation.

Similarly, you can check for tables by trying to access the first row:

if mysql -u root -e 'USE mydbname; SELECT * FROM tablename LIMIT 1'; then

   //database and table exist, do more stuff

fi

Because it if block checks the return code of the command you are chucking into it, and MySQL commands return an error code if they encounter problems, you can use any command in a if block to account for errors.


Source link