MYNITOR.COM

I always look for one liners for mysql to quickly take a dump, import, update/delete record, drop table or add user. For these quickies, I don’t want to be logging into DB every time to get it done. I put together the following one liners you can quickly copy and paste to your shell prompt.

First if you don’t have mysql, instal it:

$ sudo apt-get install mysql-client
$ sudo apt-get install mysql-server
$ sudo mysqladmin -u root password 'mypass'

Now you’re set with mysql on your system. Onto the quickies…

$ mysql -u root -pmypass -e STATUS | grep -i threads

Threads: 1  Questions: 7687175  Slow queries: 0  Opens: 97110  Flush tables: 1
Open tables: 64  Queries per second avg: 1.150

Display mysql uptime:

$ mysql -u root -pmypass -e STATUS | grep -i uptime

Uptime:                 77 days 10 hours 1 min 14 sec

Show all active queries:

$ mysqladmin -u root -pmypass processlist

+--------+-------+-----------+------+---------+------+-------+------------------+
| Id     | User  | Host      | db   | Command | Time | State | Info             |
+--------+-------+-----------+------+---------+------+-------+------------------+
| 361096 | root  | localhost | NULL | Query   |    0 | NULL  | show processlist |
+--------+-------+-----------+------+---------+------+-------+------------------+

Drop database name ‘mynitor_DB’

$  mysqladmin -u root -pmypass drop mynitor_DB

Here is an example of running ‘show tables’ from command line to display tables with prefix wp_:

$ mysql -u root -pmypass -e "use mynitor_com; show tables like 'wp_%'";

+------------------------------+
| Tables_in_mynitor_DB (wp_%) |
+------------------------------+
| wp_btpi                      |
| wp_comments                  |
| wp_links                     |
| wp_options                   |
| wp_postmeta                  |
| wp_posts                     |
| wp_term_relationships        |
| wp_term_taxonomy             |
| wp_terms                     |
| wp_usermeta                  |
| wp_users                     |
+------------------------------+

Create user ‘mysite’ with password ‘YES!’ with all privileges to mynitor_DB database:

$ mysql -u root -pmypass -e "grant all privileges on mynitor_DB.* to
mysite@localhost identified by 'YES!';

Output the query result to output.txt:

$ mysql -u root -pmypass mynitor_DB -e "select * from wp_users" > output.txt 

So you get the idea here.  By having a ‘-e’ flag, you can pretty much execute any mysql command/query without logging into it interactively.

Here is a quick shell script to run multiple queries:

#!/bin/bash

mysql -u root -pmypass <<SQL
show databases;
use mynitor_DB;
show tables;
use mysql;
select * from user where user='mysite';
update user set password='NewPassword' where user='mysite';
quit
SQL

Save and execute script as follows:

$ chmod +x run.sh
$ ./run.sh

Of course you’re limited to how much you can accomplish from command line using shell scripts.  I recommend you use php command line interface instead of shell to get more complex tasks done.  On my next article, I will provide some examples of PHP cli.