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.

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.

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