MYNITOR.COM

If you work on command-line often, then this reference guide is for you. This is a quick reference of emacs and vi while in command-line using bash as your shell. To enable command-line editing for vi is set -o vi and for emacs it is set -o emacs.

Emacs Editing Mode

• CTRL-B – Move backward one character (without deleting)
• CTRL-F – Move forward one character
• DEL – Delete on character backward
• CTRL-D – Delete on character forward
• ESC-B – Move one word backward
• ESC-F – Move one word forward
• ESC-DEL – Kill one word backward
• ESC-CTRL-H – Kill one word backward
• ESC-D – Kill one word forward
• CTRL-Y – Retrieve (“yank”) last item killed
• CTRL-A – Move to beginning of line
• CTRL-E – Move to end of line
• CTRL-K – Kill forward to end of line
• TAB – Attempt to perform general completion of the text
• ESC-? – List the possible completions
• ESC-/ – Attempt filename completion
• CTRL-X / – List the possible filename completions
• ESC- ~ – Attempt username completion
• CTRL-X ~ – List the possible variable completions
• ESC-$ – Attempt variable completion
• CTRL-X $ – List the possible variable completions
• ESC-@ – Attempt hostname completion
• CTRL-X @ – List the possible hostname completion
• ESC-! – Attempt command completion
• CTRL-X ! – List the possible command completions
• ESC-TAB – Attempt completion from previous commands in the history list
• CTRL-J – Same as RETURN
• CTRL-L – Clears the screen
• CTRL-M – Same as RETURN
• CTRL-O – Same as RETURN, then display next line in command history
• CTRL-T – Transpose two chracters on either side of point and move point forward by one
• CTRL-U – Kills the line from the beginning to point
• CTRL-V – Quoted insert
• CTRL-[ – Same as ESC
• ESC-C – Capitalize word after point
• ESC-U – Change word after point to all capital letters
• ESC-L – Change word after point to all lowercase letters
• ESC-. – Insert last word in previous command line after point
• ESC-_ – Same as ESC.

Vi Editing Mode

• DEL – Delete previous character
• CTRL-W – Erase previous word
• CTRL-V – Quote the next character
• ESC – Enter control mode
• h – Move left one character
• l – Move right one character
• w – Move right one word
• b – Move left one word
• W – Move to beginning of next non-blank word
• B – Move to beginning of preceding non-blank word
• e – Move to end of current word
• E – Move to end of current non-blank word
• O -Move to beginning of line
• ^ – Move to first non-blank character in line
• $ – Move to end of line
• i – Text inserted before current character (insert)
• a – Text inserted after current character (append)
• I – Text inserted at beginning ofline
• A – Text inserted at end of line
• R – Text overwrites existing text
• dh – Delete one character backwards
• dl – Delete on character forwards
• db – Delete one word backwards
• dw -Delete one word forwards
• dB – Delete one non-blank word backwards
• dW – Delete one non-blank word forwards
• d$ – Delete to end of line
• d0 – Delete to beginning of line
• k or - – Move backward one line
• j or + – Move forward one line
• G – Move to line given by repeat count
• /string – Search backward for string
• ?string – Search forward for string
• n – Repeat search in some direction as previous
• N – Repeat search in opposite direction of previous

These examples were taken from O’reilly bash book. It’s one of my favorite book and you can purchase it on amazon. It’s the best book available for scripting in bash in my opinion.

My previous article, Quick MySQL on-liners provided many neat tricks to interact with MySQL directly from the command line. In this article, I’ve added some more things you can do from shell. Running MySQL queries via shell prompt or shell script is quite easy. It can become very handy for script automation such as backups, log audit, system stats, user creations and so on. You can simply embed any MySQL query or command in your shell script to get things done faster.

In addition, you can store the account info in a ~/.my.cnf file in your home dir and you won’t have to pass any user/pass info via command line when querying the database. Here are some examples to get you started:

$ mysql -e "\s" -u -p
$ echo "\s" | mysql -u -p

- Shows quick status of your mysql server. You can exchange “\s” to any mysql command such as “show processlist”, “show databases” and etc. to get output on your screen without logging into mysql manually. Here are some lists:

"show status" - Displays detailed status of your current mysql setup.

"show processlist" - Shows active queries in mysql server

"kill " - The output of "show processlist" will display Id of the processes
running. You can use that id against the 'kill' command to kill
that process in database.

"show databases" - Shows all databases in mysql server

"reset master" - Clean up binary log files.

"rollback" - Rollback undoes everything and commit will save.

"show binlog events" - Display history of recent queries (insert and delete)
that were executed.

$ mysql -e "select * from user" -u -p mysql

- Display all users and their privilege details from mysql.user table. You exchange anything between the quotes (“”) to any type of query.

$ mysql -e "show create table users" -u -p sales

- Display the create statement used to create table “users” in “sales” database.

$ tcpdump -i eth0 -nN -vvv -xX -s 1500 port 3306

- Monitor mysql port, 3306.

$ my_print_defaults client mysql

- Print defaults for the current client connection. You can put settings /etc/my.cnf for client connections.

$ mysqladmin shutdown

- Restart the mysql server

$ mysqlshow

- A command run from the command prompt that will quickly display database, table and column information.

$ cd /var/lib/mysql/salesdb
$ myisamchk *

- myisamchk helps with damaged files. It’s best to stop database before running it.

$ mysql -e "GRANT ALL PRIVILEGES ON *.salesdb TO salesguy1@localhost IDENTIFIED
BY 'today'" -u -p

- You can create a shell script to easily add new mysql users by executing the above syntax. The above example adds user ‘salesguy’ with password ‘today’ to mysql server and gives full privilege to database ‘salesdb’.

$ mysql -X -e "select * from users" salesdb -u -p

- The above will give you XML output with the “-X” option. For html output use the “-H” option.

$ mysql --skip-column-names -e "select * from users" salesdb -u -p

- Display result with no heading.

To prevent being prompted for password, create ~/.my.cnf with mysql user and password.  With this you no longer have to pass -u and -p flags at shell prompt anymore. Here is a sample ~/.my.cnf:

[client]
user=root
password=test12

$ ssh remotehost 'mysql -u -p salesdb -e "show tables"'

- The query “show tables” is run via SSH against remotehost server. You can follow this syntax for all the examples we’ve given above to run mysql queries against remote server via SSH directly.

$ mysqldump salesdb > /tmp/salesdb.exp

- Dump the entire salesdb schema and data into /tmp/salesdb.exp file. You can also dump individual tables. Simply put table name after the database. You can pass the -u and -p flags if you don’t have a ~/.my.cnf file that has mysql root user and password hard-coded.

$ mysql salesdb < /tmp/salesdb.exp

- Import salesdb.exp file back into salesdb database. You can pass the -u and -p flags if you don’t have a ~/.my.cnf file that has mysql root user and password hard-coded.

You can do many things with mysqladmin such as:

$ mysqladmin create salesdb

- Creates database called salesdb

$ mysqladmin kill id, id ..

- Instruct mysqladmin to kill a process id.

$ mysqladmin new-password

- Change old password to new password.

$ mysqladmin ping

- Check if mysqld is alive

$ mysqladmin shutdown

- Shutdown mysql server

$ mysqladmin variables

- Prints variables available

$ mysqladmin version

- Get version info from server

Most important thing to remember is the man command. You can run man mysqladmin or any other binary that mySQL comes with to get a more detailed explanation of it. There are many many more commands available for mysql to be used via command line shell or script for automation. Please visit mysql documentation to learn all the details.

All websites seem to have some sort of dynamic feature which means it probably makes a backend call to a database like mysql to retrieve and display data. In my case, I have a website that displays top 20 most voted names or some other random data on front page of my website.

So every time a user hits index.php, my code makes a db call to get this statistical data. When traffic increased, my server performance took a nose dive and I had to raise mysql max connections to support the number of hits it was getting for each user visit but I was only prolonging and avoiding the issue.

So i figured, why not convert the front door to static html instead?  I renamed index.php (dyanmic version) to index2.php and wrote the following shell script:

#!/bin/bash
cd /home/httpd/vhosts/<www.mywebsite.com>/httpdocs
wget -O temp.index.php --timeout=60 http://www.mywebsite.com/index2.php
if [ "`stat -c%s temp.index.php`" -gt 500 ]
then
    cp temp.index.php index.php
else
echo "Cached size too small"
exit 0
fi

The value of ’500′ above means if file size for temp.index.php is greater, then it’s legitimate otherwise something happened and wget failed. I saved the above script to a file named /bin/cache_main.sh.

And created a crontab entry that would republish index.php every 5 minutes:

*/5 * * * *      /bin/clear_main.sh > /dev/null 2>&1

So with the above setup, the cron runs every 5 minutes hitting index2.php (dynamic version of the site) and generates index.php, static version. Users coming in to the site always gets the index.php served which is 100% static. The data of course will be 5 minutes old but I don’t mind this trade-off at all.

It definitely beats changing code to support caching or buying new hardware to support traffic spike. You use this same method for other db intensive pages on your site just as easily.

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.

I like vi.  Yes I do.  Not Emacs.  But VI.  Here is a list of some basic commands to move around and some serious stuff.

h l k j	character left, right, line up, down
b w	word/token left, right
ge e	end of word/token left, right
{ }	beginning of previous, next paragraph
( )	beginning of previous, next sentence
0 gm	beginning, middle of line
^ $	first, last character of line
nG ngg	line n, default the last, first
n%	percentage n of the file (n must be provided)
n	column n of current line
%	match of next brace, bracket, comment, #define
nH nL	line n from start, bottom of window
:g/RE/p	Where grep came from (RE being Regular Expression)
:10,20d	Delete lines 10 to 20 inclusive
:g/pattern/d	Delete lines that contain pattern
:g/^$/d	Delete all empty lines:
:20,30/pattern/d	Delete lines in range that contain pattern
:%s/pattern/new/	Substitute all lines for first occurance of pattern
:%s/pattern/new/g	Substitute all lines for pattern globally (more than once on the line)
:%s/\(.*pattern.*\)/\1-new/g	Find all lines containing pattern and then append -new to the end of each line
:20,30s/pattern/new/g	Substitute range
:s/\(pattern1\)\(pattern2\)/\2\1/	Swap two patterns on a line
:s/\([a-z]\)/\u\1/	Capitalize the first lowercase character on a line
:s/[a-z]/\u&/	more concisely
:s/\([a-z]\)/\u\1/g	Capitalize all lowercase characters on a line
:s/[a-z]/\u&/g	more concisely
:s/\(.*\)/\U\1\E/	Capitalize all characters on a line:
:s/\<[a-z]/\u&/g	Capitalize the first character of all words on a line:
:s/\<[A-Z]/\l&/g	Uncapitalize the first character of all words on a line
~	Change case of character under cursor
g~~	Change case of all characters on line
g~w	Change case of remaining word from cursor
:set nu	Turn on line numbering
:set nonu	Turn it off
:%!cat -n	Number lines (filter the file through a unix command and replace with output)
:%!sort	Sort lines
:%!sort -u	Sort and uniq
:r !ls -l	Read output of command into buffer
:e!	Refresh file from version on disk
n	Open a new window
s	Open a new window with the same file (split)
v	Split window vertically
c	Close current window
:set textwidth=80	Set textwidth for automatic line-wrapping as you type
:syn on	Turn on syntax highlighting
:set filetype=php	Force the php for syntax highlighting:
:set background=dark	Use lighter coloring scheme for a dark background
:set filetype=php	Force the php for syntax highlighting:

Here are some external VI references:

• http://ungerhu.com/jxh/vi.html
• http://www.unix-manuals.com/refs/vi-ref/vi-ref.htm
• http://tru2life.info/docs/v/vi/vi-cheats.html
• http://www.selectorweb.com/vi.html
• http://www.eng.hawaii.edu/Tutor/vi.html
• http://limestone.truman.edu/~dbindner/mirror/
• http://www.washington.edu/computing/unix/viqr.html
• http://www.linuxfocus.org/~guido/vi/viref.html

There are different ways to monitor a remote service but when it comes to databases, it could get a bit tricky.  It’s inaccurate to do a port check to verify db server availability because at times it’s not enough for TCP port 3306 to respond. What matters is whether you’re able to run a mysql query to retrieve data.

This script can be placed anywhere on your web docroot which you can hit via your website (i.e http://www.yourwebsite.com/db.php).  If the mysql server is up, you’d get a response with db up time, number of threads in use, number of slow queries, average query response in seconds and etc.

You can easily change this script to include other queries.  Copy and paste the following script on your web server.  It can be accessed via http or run via cmd line php.

<?php
$dbhost = 'mysqlhost';
$dbuser = 'user';
$dbpass = 'password';
$please = mysql_connect($dbhost,$dbuser, $dbpass);
if (!$please) {
die('Could not connect: ' . mysql_error());
}
echo "Connected successfully to $dbhost<br>";
print "MySQL status = ".mysql_stat()."\n";
mysql_close($con);
?>