MySql Webiars - On Demand

http://www.mysql.com/news-and-events/on-demand-webinars/

mysql hostname prompt when host is localhost

mysql hostname prompt when host is localhost: "
I manage several mysql servers and often on these servers for security reasons the SUPER account is not allowed external network access, so access is made to localhost. When connecting to several hosts at the same time, for example from different ssh sessions, this can be inconvenient as the \h prompt only ever shows localhost and not the hostname of the server to which I’m connected.

The following small patch against 5.1.36 which can also be found here adds a new \H option which behaves the same as \h except in this case the hostname is shown.

diff --git a/Docs/mysql.info b/Docs/mysql.info
index 7747201..dffacfd 100644
--- a/Docs/mysql.info
+++ b/Docs/mysql.info
@@ -20512,6 +20512,8 @@ sequences.
`\D'        The full current date
`\d'        The default database
`\h'        The server host
+`\H'        Same as `\h' except that if the server host is localhost
+            the client's hostname will be used
`\l'        The current delimiter (new in 5.1.12)
`\m'        Minutes of the current time
`\n'        A newline character
diff --git a/client/mysql.cc b/client/mysql.cc
index 5f360b8..b24fb14 100644
--- a/client/mysql.cc
+++ b/client/mysql.cc
@@ -39,6 +39,10 @@
#include <signal.h>
#include <violite.h>

+#ifdef SOLARIS
+extern "C" int gethostname(char *name, int namelen);
+#endif
+
#if defined(USE_LIBEDIT_INTERFACE) && defined(HAVE_LOCALE_H)
#include <locale.h>
#endif
@@ -4657,12 +4661,27 @@ static const char* construct_prompt()
case 'd':
processed_prompt.append(current_db ? current_db : "(none)");
break;
+      /* \H is the same as \h except if \h returns localhost in which case *
+       * we provide the client's hostname.                                 *
+       */
case 'h':
+      case 'H':
{
const char *prompt;
+        char  myhostname[255];
prompt= connected ? mysql_get_host_info(&mysql) : "not_connected";
-    if (strstr(prompt, "Localhost"))
-      processed_prompt.append("localhost");
+
+    if (strstr(prompt, "Localhost") || strstr(prompt, "localhost"))
+          if ( *c == 'h' ) {
+        processed_prompt.append("localhost");
+          }
+          else
+          {
+            if (gethostname(myhostname,sizeof(myhostname)) < 0)
+              processed_prompt.append( "gethostname(3) returned an error" );
+            else
+              processed_prompt.append( myhostname );
+          }
else
{
const char *end=strcend(prompt,' ');

This gives the output as shown below:

$ hostname
my.computer.domain.com
$ client/mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6892
Server version: 5.0.xx MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> prompt \h
PROMPT set to '\h '
localhost prompt \H
PROMPT set to 'prompt \H '
prompt my.computer.domain.com quit
Bye
$

As this uses the FQDN it may be necessary to add another option to show only the host’s short name but for now this patch is useful for me. I expect the patch should work against most other versions of MySQL. Perhaps this is useful enough to warrant putting in the main MySQL sources?

PlanetMySQL Voting:
Vote UP /
Vote DOWN"

How to find un-indexed queries in MySQL, without using the log

How to find un-indexed queries in MySQL, without using the log: "
You probably know that it’s possible to set configuration variables to log queries that don’t use indexes to the slow query log in MySQL. This is a good way to find tables that might need indexes.


But what if the slow query log isn’t enabled and you are using (or consulting on) MySQL 5.0 or earlier, where it can’t be enabled on the fly unless you’re using a patched server such as Percona’s enhanced builds? You can still capture these queries.


The key is knowing what it really means for a query to “not use an index.” There are two conditions that trigger this — not using an index at all, or not using a “good” index. Both of these set a bit. If either bit is set, the query is captured by the filter and logged. Both of these bits also set a corresponding bit in the protocol, so the TCP response to the client actually says “here comes the result of your query, and by the way it didn’t use an index.” This is very useful information.


I’m sure you can see where this is going. Let’s use tcpdump to capture queries, consume the output with mk-query-digest, and filter out all but ones that don’t use an index or use no good index:

$ sudo tcpdump -i lo port 3306 -s 65535  -x -n -q -tttt \
| mk-query-digest --type tcpdump \
--filter '($event->{No_index_used} eq "Yes" || $event->{No_good_index_used} eq "Yes")'

If I run a few full table scans now, and then cancel mk-query-digest, I’ll get output like the following (abbreviated for clarity):

#              pct   total     min     max     avg     95%  stddev  median
# Count        100       8
# Exec time    100     5ms   511us   857us   604us   839us   106us   582us
# 100% (8)    No_index_used
select * from t\G

You can see I ran the query 8 times and each time it reported back that it didn’t use an index. This is a dead-easy way to find queries that might not have an index available!


Want to print out tables from those queries? You can do that too. Just add --group-by tables --report-format profile to the command above, and instead of grouping queries together by the query text, it’ll group them by the tables they mention. Then the report will contain one item per table and you’ll just see a summary at the end, like so:

# Rank Query ID           Response time    Calls   R/Call     Item
# ==== ================== ================ ======= ========== ====
#    1 0x                     0.0037 100.0%       8   0.000467 test.t

Aha, looks like test.t is the problem table!


Related posts:

1. Finding queries with duplicate columns A while ag
2. Learn about Maatkit at the MySQL Conference I’m
3. Maatkit version 3119 released Your month

Related posts brought to you by Yet Another Related Posts Plugin.

PlanetMySQL Voting:
Vote UP /
Vote DOWN
"