Running Mysql commands from Bash script

Eventually, you’ll need to automate queries on your Mysql database, let’s say, for reporting purposes. Hee’s how to put SQL queries into a shell script, and possibly including variables based on the date or whatever fits your needs:

$ cat execmysql.sh
#!/bin/sh
ids="3,4"
table="NMS.main"
qry="select id,data from $table where id in ($ids)"
echo "Executing the query..."
echo $qry
/usr/bin/mysql -u root << eof
$qry
eof

And the execution output:

$ ./execmysql.sh
Executing the query...
select id,data from NMS.main where id in (3,4)
id data
3 pizza
4 bash

Tags:

Last rowcount in MySQL

#mysql

Suppose you want to count the number of lines returned by the last SQL statement issued. For **select** statements you can use the *FOUND_ROWS* construct: ```sql SELECT SQL_CALC_FOUND_ROWS something FROM your_table WHERE whatever; SELECT FOUND_ROWS(); ``` This will return the number of rows in the last **select** query (or if the first query ... Read more

20 Sep 2016 - less than 1 minute read

MySQL punch user creation DDL

#mysql

You can get the DDL necessary to duplicate an existing user with the following system command: ``` MYSQL_CONN="-uroot -ppassword" mysql ${MYSQL_CONN} --skip-column-names -A -e "SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user'' " | mysql ${MYSQL_CONN} --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants... Read more

20 Jul 2016 - less than 1 minute read

InnoDB table rebuild

#mysql

### PROBLEM On a **MySQL 5.6** database server, the *ibdata1* file includes 5 InnoDB tables in the mysql schema. ```sql mysql> select table_name from information_schema.tables -> where table_schema='mysql' and engine='InnoDB'; +----------------------+ | table_name | +----------------------+ | innodb_index_stats | | innodb_table... Read more

20 Jun 2016 - 1 minute read

Convert FLAC to mp3 like a hacker

#system #linux

So you need to convert a bonch of FLAC files to mp3 and your best search engine only recommands you online file converters that nobody trusts ore require a paid subscription to work. Don't worry, *there's always a way*, and even better, a true ***hacker style*** way to achieve this! This procedure works for MacOS through Brew, but you should b... Read more

20 Feb 2019 - less than 1 minute read

Blockchain and IoT

#blockchain #linux

Here's a small script to open a JavaScript interface to interact with an IoT device. This interface can then be easily incorporated in a larger Blockchain project with real tangible everyday life objects. I'll cover the Blockchain aspect of this project in another article, focusing on *Hyperledger*. The IoT device I'll be using for this project... Read more

20 Aug 2017 - 2 minute read

Image compression from the command line

#linux

Here's a small script to compress JPEG images in a folder. Useful to save a few kilobytes of bandwith when serving images from your website. I might improve this script in the future to include more compression tools and/or file extensions. ```bash #!/bin/bash for f in *.jpg *.JPG *.jpeg *.JPEG do echo -en "Converting ${f} ... " kb_or... Read more

20 Jul 2017 - less than 1 minute read