MYNITOR.COM

A large percentage of small to medium sized websites depend on Mysql server to support their db infrastructure. Working with it is as easy is saying it and for some reason there are numerous web and non-web administration software written specifically to manage a Mysql server and sites running on it. This article lists quite a few of them which you may find useful.

1. NG-Admin – designed for the content management of MySQL databases. It allows the user to browse, add, edit, and delete data. It is somewhat similar to phpMyAdmin, but specializes in editing the content of Web sites, not the database structures. Its features are very easy to use and highly tunable.
2. PHP Mini SQL Admin – a light, standalone script for accessing MySQL databases. It is intended for Web developers.
3. FlashMyAdmin - Flash-based MySQL administration project. It features multiple database management, import/export (SQL, XML, CSV), internationalization, and help. It also allows video, audio, images, and movieclip files to be shown directly within the interface.
4. phpMyAdmin - a tool written in PHP intended to handle the administration of MySQL over the Web. It can create, rename, and drop databases, create/drop/alter tables, delete/edit/add fields, execute any SQL statement, manage keys on fields, create dumps of tables and databases, export/import CSV data and administrate one single database and multiple MySQL servers.
5. jspMyAdmin – a clone of the very popular phpMyAdmin, but written in JSP. It provides MySQL database administration over the Web.
6. RUIDB – a simple tool for working with MySQL using a Web browser. It was made to enable users with little knowledge of MySQL to add/edit/delete/view table rows. It is also a nice tool for admins or Web site authors.
7. KooDB – a MySQL database administration frontend that features an intuitive interface. It aims to give substantial control over MySQL without adding all the complicated options of phpMyAdmin.
8. MySQL Administrator – a powerful visual administration console for MySQL, which allows the user to easily administer their MySQL environment, and gain better visibility of how their databases are operating. It is available with native GUI interfaces for Windows, Linux, and Mac OS X.

There are others but who cares once you get used to administration via command line, nothing comes even close to beating it.

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.

The Ultimate Linux Reference Guide for Newbies
Click Here to download the Ultimate Linux Guide PDF!
FILE AND DIRECTORY BASICS	This cateogry also includes utilities that change file/directory properties and permissions
ls	List files/directories in a directory, comparable to dir in windows/dos.
ls -la	Shows all files (including ones that start with a period), directories, and details attributes for each file.
ls -lSrh	Find the biggest files in current directory in human readable format.
cd	Change directory (e.g cd /usr/local/bin)
cd ~	Go to your home directory
cd -	Go to the last directory you were in
cd ..	Go up a directory
cat	Print file contents to the screen
cat filename.txt	Print the contents of filename.txt to your screen
du -kx | egrep -v “\./.+/” | sort -n	Find largest directories in the filesystem.
tail	Similar to cat, but only reads the end of the file
tail /var/log/messages	See the last 20 (by default) lines of /var/log/messages
tail -f /var/log/messages	Watch the file continuously, while it’s being updated
tail -200 /var/log/messages	Print the last 200 lines of the file to the screen
head	Similar to tail, but only reads the top of the file
head /var/log/messages	See the first 20 (by default) lines of /var/log/messages
head -200 /var/log/messages	Print the first 200 lines of the file to the screen
more	Llike cat, but opens the file one screen at a time rather than all at once
more /etc/userdomains	Browse through the userdomains file. hit Spaceto go to the next page, q to quit
less	Page through files
od	View binary files and data
xxd	Also view binary files and data
gv	View Postscript/PDF files
xdvi	View TeX DVI files
nl	Number lines
touch	Create an empty file
touch /home/burst/public_html/404.html	Create an empty file called 404.html in the directory /home/burst/public_html/
file	Attempts to guess what type of file a file is by looking at it’s content.
file *	Prints out a list of all files/directories in a directory
cp	Copy a file
cp filename filename.bak	Copies filename to filename.bak
cp -a /etc/* /root/etc/	Copies all files, retaining permissions form one directory to another.
cp -av * ../newdirectory	Copies all files and directories recurrsively in the current directory INTO newdirectory
mv	Move a file command
mv oldfilename newfilename	Move a file or directory from oldfilename to newfilename
rm	delete a file
rm filename.txt	deletes filename.txt, will more than likely ask if you really want to delete it
rm -f filename.txt	deletes filename.txt, will not ask for confirmation before deleting.
rm -rf tmp/	recursively deletes the directory tmp, and all files in it, including subdirectories.
chmod	Changes file access permissions. The set of 3 go in this order from left to right: USER – GROUP – EVERONE 0 = — No permission 1 = –X Execute only 2 = -W- Write only 3 = -WX Write and execute 4 = R– Read only 5 = R-X Read and execute 6 = RW- Read and write 7 = RWX Read, write and execute
chmod 000	No one can access
chmod 644	Usually for HTML pages
chmod 755	Usually for CGI scripts
chown	Changes file ownership permissions The set of 2 go in this order from left to right: USER – GROUP
chown root myfile.txt	Changes the owner of the file to root
chown root.root myfile.txt	Changes the owner and group of the file to root
stat	Display file attributes
grep	Llooks for patterns in files
grep root /etc/passwd	Shows all matches of root in /etc/passwd
grep -v root /etc/passwd	Shows all lines that do not match root
ln	Create’s “links” between files and directories
ln -s /usr/local/apache/conf/httpd.conf /etc/httpd.conf	Now you can edit /etc/httpd.conf rather than the original. changes will affect the orginal, however you can delete the link and it will not delete the original.
wc	Word count
wc -l filename.txt	Tells how many lines are in filename.txt
find	Utility to find files and directories on your server.
find / -name “filename”	Find the file called “filename” on your filesystem starting the search from the root directory “/”.
locate filename	Find the file name and path of which contains the string “filename”. Run ‘updatedb’ to build index.
rename .html .php *.html	Rename all *.html file as *.php
for file in *.html ; do mv $file `echo $file | sed ’s/\(.*\.\)html/\1php/’` ; done	Rename all *.html files as *.php Of course this is more work than previous command…why bother.
find . -uid 320 -exec chown 350 {} \;	Change all files with uid 320 to 350. For all real life find examples check out: http://johnmeister.com/CS/UNIX/FIND/find-usage.html
EDITORS	Most popular editors available on UNIX platforms.
pico / nano	Friendly, easy to use file editor
nano /home/burst/public_html/index.html	Edit the index page for the user’s website.
vi	Popular editor, tons of features, harder to use at first than pico.  Check out So you want to learn VI?
vi filename.txt	Edit filename.txt. All commands in vi are preceded by pressing the escape key. Each time a different command is to be entered, the escape key needs to be used. Except where indicated, vi is case sensitive.  H — Upper left corner (home) M — Middle line L — Lower left corner h — Back a character j — Down a line k — Up a line ^ — Beginning of line $ — End of line l — Forward a character w — Forward one word b — Back one word fc — Find c ; — Repeat find (find next c) :q! — This force quits the file without saving and exits vi :w — This writes the file to disk, saves it :wq — This saves the file to disk and exists vi :LINENUMBER : EG :25 — Takes you to line 25 within the file :$ — Takes you to the last line of the file :0 — Takes you to the first line of the file
emacs	Another popular editor. For more commands go to http://www.hsrl.rutgers.edu/ug/emacs_qref.html C-\ t — Tutorial suggested for new emacs users. C-x C-c exit emacs
emacs filename.txt	Edit filename.txt. While you’re in emacs, use the following quickies to get around: C-x C-f — read a file into emacs C-x C-s — save a file back to disk C-x i — insert contents of another file into this buffer C-x C-v — replace this file with the contents of file you want C-x C-w — write buffer to specified file C-f — move forward one character C-b — move backward one character C-n — move to next line C-p — move to previous line C-a — move to beginning of line C-e — move to end of line M-f — move forward one word M-b — move backword one word C-v — move forward one screen M-v — move backward one screen M-< — go to beginning of file M-> — go to end of file
sed ’s/Today/Yesterday/g’ infile > outfile	Replace all occurance of ‘Today’ with ‘Yesterday’.  Yes days go by so quickly.  Today is already yesterday.  Heck it maybe a few years ago since I’ve written this.
cat file | awk '{NR >=5 && NR <= 10 }'	Print out each line between 5 and 10.
NETWORK	Some of the basic networking utilities.
w	Shows who is currently logged in and where they are logged in from.
who	This also shows who is on the server in an shell.
netstat	Shows all current network connections.
netstat -an	Shows all connections to the server, the source and destination ips and ports.
netstat -rn	Shows routing table for all ips bound to the server.
netstat -an |grep :80 |wc -l	Show how many active connections there are to apache (httpd runs on port 80)
netstat -lnp	Display all open ports with service name.
top	Shows live system processes in a formatted table, memory information, uptime and other useful info. While in top, Shift + M to sort by memory usage or Shift + P to sort by CPU usage
top -u root	Show processes running by user root only.
route -n	Shows routing table for all ips bound to the server.
nslookup yahoo.com	Query your default domain name server (DNS) for an Internet name (or IP number) host_to_find.
traceroute yahoo.com	Have a look how you messages travel to yahoo.com
ifconfig	Display info on the network interfaces.
ifconfig -a	Display into on all network interfaces on server, active or inactive..
ping	Sends test packets to a specified server to check if it is responding properly
tcpdump	Print all the network traffic going through the network.
arp	Command mostly used for checking existing Ethernet connectivity and IP address
SYSTEM TOOLS	Many of the basic system utilities used to get things done.
ps	ps is short for process status, which is similar to the top command. It’s used to show currently running processes and their PID. A process ID is a unique number that identifies a process, with that you can kill or terminate a running program on your server (see kill command).
ps U username	Shows processes for a certain user
ps aux	Shows all system processes
ps aux –forest	Shows all system processes like the above but organizes in a hierarchy that’s very useful!
kill	terminate a system process
kill -9 PID	Immediately kill process ID
killall program_name	Kill program(s) by name. For example to kill instances of httpd, do ‘killall httpd’
du	Shows disk usage.
du -sh	Shows a summary of total disk space used in the current directory, including subdirectories.
du / -bh | more	Print detailed disk usage for each subdirectory starting at the “/”.
last	Shows who logged in and when
last -20	Shows only the last 20 logins
last -20 -a	Shows last 20 logins, with the hostname in the last field
pwd	Print working directory, i.e., display the name of my current directory on the screen.
hostname	Print the name of the local host. Use netconf (as root) to change the name of the machine.
whoami	Print my login name.
date	Print or change the operating system date and time
time	Determine the amount of time that it takes for a process to complete + other info.
uptime	Show the number days server has been up including system load averages.
uname -a	Displays info on about your server such as kernel version.
free	Memory info (in kilobytes).
lsmod	Show the kernel modules currently loaded. Run as root.
dmesg | less	Print kernel messages.
man topic	Display the contents of the system manual pages (help) on the topic. Do ‘man netstat’ to find all details of netstat command including options and examples.
man -k ssh	Search all man pages for keyword ’ssh’
reboot / halt	Halt or reboot the machine.
mount	Mount local drive or remote file system.
mount -t auto /dev/fd0 /mnt/floppy	Mount the floppy. The directory /mnt/floppy must exist.
mount -t auto /dev/cdrom /mnt/cdrom	Mount the CD. The directory /mnt/cdrom must exist.
lsof +D /nfs/mount	Return process id of all tasks currently utilizing /nfs/mount directory.  Useful when you’re stuck trying to unmount an NFS mount.
lsof -p <pid>	See EVERYTHING that process is doing.  What files are opened, what port it’s listening to and etc.
cat /proc/cpuinfo	Display cpu details such as make, model etc.
sudo	The super-user do command that allows you to run specific commands that require root access.
fsck	Check a disk for errors
COMPRESSION UTILITIES	There are many other compression utilities but these are the default and most widely utilized.
tar	Creating and Extracting .tar.gz and .tar files
tar -zxvf file.tar.gz	Extracts the file
tar -xvf file.tar	Extracts the file
tar -cf archive.tar contents/	Takes everything from contents/ and puts it into archive.tar
gzip -d filename.gz	gzip -d filename.gz
zip	Compress files into.zip
unzip file.zip	Extracting .zip files shell command
compress	Compress files. compress filename
uncompress	Uncompress compressed files. uncompress filename.Z
bzip2	Compress files in bzip2 format
THE (DOT) FILES	The good old dot files. Let’s clear up some confusion here by defining each.
.bash_login	Treated by bash like .bash_profileif that doesn’t exist.
.bash_logout	Sourced by bash login shells at exit.
.bash_profile	Sourced by bash login shells after /etc/profile
.bash_history	The list of commands executed previously.
.profile	Treated by bash like ~/.bash_profile if that and .bash_login don’t exist.
.vimrc	Default “Vim” configuration file.
.emacs	Read by emacs at startup
CONFIGURATION FILES	Listing everything is beyond the scope of this article.
/etc	This directory contains most of the basic Linux system-configuration Files.
/etc/init.d	Contains the permanent copies of System V–style run-level scripts. These scripts are often linked to files in the /etc/rc?.d directories to have each service associated with a script started or stopped for the particular run level. The ? is replaced by the run-level number (0 through 6). (Slackware puts its run-level scripts in the /etc/rc.d directory.)
/etc/cron*	Directories in this set contain files that define how the crond utility runs applications on a daily (cron.daily), hourly (cron.hourly), monthly (cron.monthly), or weekly (cron.weekly) schedule.
/etc/cups	Contains files used to configure the CUPS printing service.
/etc/default	Contains files that set default values for various utilities. For example, the file for the useradd command defines the default group number, home directory, password expiration date, shell, and skeleton directory
/etc/skel	Any files contained in this directory are automatically copied to a user’s home directory when that user is added to the system.
/etc/mail	Contains files used to configure your sendmail mail service.
/etc/security	Contains files that set a variety of default security conditions for your computer.
/etc/sysconfig	Contains important system configuration files that are created and maintained by various services (including iptables, samba, and most networking services).
/etc/passwd	Holds some user account info including passwords (when not “shadowed”).
/etc/shadow	Contains the encrypted password information for users’ accounts and optionally the password aging information.
/etc/xinetd.d	Contains a set of files, each of which defines a network service that the xinetd daemon listens for on a particular port.
/etc/syslogd.conf	The configuration file for the syslogd daemon. syslogd is the daemon that takes care of logging (writing to disk) messages coming from other programs to the system.
/var	Contains variable data like system logging files, mail and printer spool directories, and transient and temporary files.
/var/log	Log files from the system and various programs/services, especially login (/var/log/wtmp, which logs all logins and logouts into the system) and syslog (/var/log/messages, where all kernel and system program message are usually stored).
/var/log/messages	System logs. The first place you should look at if your system is in trouble.
/var/log/utmp	Active user sessions. This is a data file and as such it can not be viewed normally.
/var/log/wtmp	Log of all users who have logged into and out of the system. The last command can be used to access a human readable form of this file.
Apache Shell Commands	Some of the basic and helpful apache commands.
httpd -v	Outputs the build date and version of the Apache server.
httpd -l	Lists compiled in Apache modules
httpd status	Only works if mod_status is enabled and shows a page of active connections
service httpd restart	Restarted Apache web server
ab -n 20 -c 2 http://www.mynitor.com/	Send 20 requests with concurrency of 2 to server mynitor.com.  Apache benchmark is pretty useful tool for load testing.  Check out 25 free tools to measure website performance.
httpd -k start -c "DocumentRoot /docs/html	Start Apache with alternative docroot. Useful for debugging.
httpd -k start -f conf/httpd.conf	Start Apache with different config file.
httpd -X	Run Apache in debug mode.  This’ll start only one process on which you can use lsof/strace and whatever else to figure out issues.
MySQL Shell Commands	Some of the basic and helpful MySQL commands.
mysqladmin processlist	Shows active mysql connections and queries
mysqladmin processlist |wc -l	Show how many current open connections there are to mysql
mysqladmin drop database	Drops/deletes the selected database
mysqladmin create database	Creates a mysql database
mysql -u username -p password databasename < data.sql	Restores a MySQL database from data.sql
mysqldump -u username -p password database > data.sql	Backup MySQL database to data.sql
echo “show databases” | mysql -u root -p password|grep -v Database	Show all databases in MySQL server.
mysqldump -u root -p password database > /tmp/database.exp	Dump database including all data and structure into /tmp/database.exp
mysqlcheck -u <user> -p<pass> -o <database_name>	Optimize all tables in database.
mysql -u root -pmypass -e STATUS | grep -i uptime	Check mysql uptime.

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.

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);
?>