Some basic MySQL

I don’t feel like doing a long post today so I’ll do some basic MySQL. First lets change the MySQL prompt. Here is a default MySQL prompt.

mysql> use test;
Database changed
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2010-08-17 06:41:00 | 
+---------------------+
1 row in set (0.00 sec)

mysql> 

Pretty boring, right? Lets spice this a little bit. Type prompt mysql \u@\d> from the MySQL prompt and lets see what happens.

mysql> prompt mysql \u@\d>
PROMPT set to 'mysql \u@\d>'
mysql root@test> select now() \G
*************************** 1. row ***************************
now(): 2010-08-17 06:44:56
1 row in set (0.00 sec)

mysql root@test>

What we just did is modify the MySQL prompt to show us the user and the database being used. This is much more informative than the previous MySQL prompt.

Now what if we wanted to know the uptime of the database. Pretty simple type status; and that will give us the time the database has been up.

 mysql root@test>status;
--------------
mysql  Ver 14.12 Distrib 5.0.75, for debian-linux-gnu (i486) using readline 5.2

Connection id:          39
Current database:       test
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.75-0ubuntu10.5-log (Ubuntu)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/run/mysqld/mysqld.sock
Uptime:                 10 min 54 sec

Threads: 1  Questions: 125  Slow queries: 0  Opens: 34  Flush tables: 1  Open tables: 28  Queries per second avg: 0.191
--------------

mysql root@test>

If we wanted to know the running processes just type: show full processlist \G.

mysql root@test>show full processlist \G
*************************** 1. row ***************************
     Id: 39
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 0
  State: NULL
   Info: show full processlist
1 row in set (0.00 sec)

mysql root@test>

This is all. I know this is very simple stuff, but didn’t felt like doing a long and complex post. As usual suggestions are always welcome.
References:

  1. https://www.ultraquantix.com/blog/2008/12/making-the-mysql-prompt-more-useful/”
  2. http://dev.mysql.com/doc/refman/5.0/en/mysql-commands.html

Leave a Reply