MySQL Preacher

If you have multiple database servers with strange names, or if you have to hop over multiple machines to connect to any mysql database server, then you know what a pain it can be to administer such a setup. Thanks to some scripting, you can automate such tasks as follows:

Create an expect script:
/path/to/sshmysql.exp

#!/usr/bin/expect -f
#script by darren cassar
#mysqlpreacher.com

set machine [lindex $argv 0]

set timeout -1

spawn ssh username@$machine
match_max 100000
expect -exact “assword: ”
send — “password\r”
send — “sudo -k; sudo su – mysql\r”
expect -exact “sudo -k; sudo su – mysql”
expect -exact “assword:”
send — “password\r”
interact

# you should change the word password in ’send — “password\r”‘ to your login password
# if you have the same password for each environment you could also script logging into mysql directly from the same expect script BUT that is not recommended.

Create a bash script:
/path/to/login.sh

#!/bin/bash
#script by darren cassar
#mysqlpreacher.com

sm=’/path/to/sshmysql.exp’

menu() {
echo ” 101 – dev.databaseserver1 ”
echo ” 102 – dev.databaseserver2 ”
echo ” 103 – dev.databaseserver3 ”
echo ” 201 – qa.databaseserver1 ”
echo ” 301 – uat.databaseserver1 ”
echo ” 302 – uat.databaseserver2 ”
echo ” 401 – prod.databaseserver1 ”
echo ” ”
}

ARGUMENT=notmenu

if [ -z "$1" ]
then
ARGUMENT=menu
else
choice=$1
fi

if [ $ARGUMENT = "menu" ]
then
menu
else
case “$choice” in
101|dev.databaseserver1 ) $sm dev.databaseserver1;;
102|dev.databaseserver2 ) $sm dev.databaseserver2;;
103|dev.databaseserver3 ) $sm dev.databaseserver3;;
201|qa.databaseserver1 ) $sm qa.databaseserver1;;
301|uat.databaseserver1 ) $sm uat.databaseserver1;;
302|uat.databaseserver2 ) $sm uat.databaseserver2;;
401|prod.databaseserver1 ) $sm prod.databaseserver1;;
* ) echo “Wrong value passed to script”
menu ;;
esac
fi

alias l=’/path/to/login.sh’

Output:

[darrencassar@mymachine ~ ]$ l
101 – dev.databaseserver1
102 – dev.databaseserver2
103 – dev.databaseserver3
201 – qa.databaseserver1
301 – uat.databaseserver1
302 – uat.databaseserver2
401 – prod.databaseserver1

Output:
The below command would log you into the first development database server as mysql user.

[darrencassar@mymachine ~ ]$ l 101

On each machine place aliases for each instance in the .profile

alias use3306=’mysql -u root -p -h 127.0.0.1 -P 3306 –prompt=”mysql \D> “‘

The above setup can be used using any client/server OS: Linux, Solaris, MAC OS or Windows(running Cygwin)

NOTE: If you store the password in clear text inside the expect script, you should at least save the scripts inside an encrypted partition on your machine and make sure that folder is not shared or accessible by anyone. Another way of doing it would be to use either SSHKeys OR save the password inside a file and encrypt it using OpenSSL

Enjoy!

Databases, Intermediate, OS, Uncategorized access, automation, bash, expect, MySQL

A MySQL is running happily on a machine situated in a land far far away. I grant access to a user@machine_aaaaaa (grant select on db.* to ‘user’@'machine_aaaaa’ identified by ‘password’; flush privileges;), send an email to the user saying it should run fine and happily go off my way. Mistake!

It seems this user can’t connect to the mysql gets access denied:
Access denied for user ‘user’@'machine_bbbbb’ (using password: YES)

Note that the machine the user is being seen from is totally different from the one I set up in the grant!! WHY?

run a reverse lookup on the ip of machine_aaaaa, turns out it shows machine_bbbbb. So I figure a big bad guy messed up /etc/hosts, I was right! `cat /etc/hosts` just to find an entry for machine_aaaaa blehh

Ok, solution is to remove the entry from /etc/hosts (after finding out it wasn’t even necessary and wasn’t even supposed to be there in the first place), restart nscd.

Retry
AGAIN – Access denied for user ‘user’@'machine_bbbbb’ (using password: YES)

What the ….

What’s wrong now? — yeah silly me forgot to `flush hosts` :)

Retry
YOHOO I’m in!

I’ve seen quite a few blogs about disabling name resolve in mysql with skip-name-resolve and granting privileges using IPs – something which would also have avoided the above (but still not found the root of the problem):

http://jeremy.zawodny.com/blog/archives/011421.html
http://www.mysqlperformanceblog.com/2008/05/31/dns-achilles-heel-mysql-installation/

Databases, MySQL, Networking, OS, Solaris cache, dns, grants, hosts, Networking, skip-name-resolve

WIKIPEDIA -

Irony (from the Ancient Greek εἰρωνεία eirōneía, meaning hypocrisy, deception, or feigned ignorance) is a situation, literary technique or rhetorical device, in which there is an incongruity, discordance or unintended connection that goes beyond the most evident meaning. It is the expression of one’s meaning by using language that normally signifies the opposite.

Types of irony
Modern theories of rhetoric distinguish among verbal, dramatic and situational irony.
▪ Verbal irony is a disparity of expression and intention: when a speaker says one thing but means another, or when a literal meaning is contrary to its intended effect. An example of this is sarcasm.
▪ Dramatic irony is a disparity of expression and awareness: when words and actions possess a significance that the listener or audience understands, but the speaker or character does not.
▪ Situational irony is the disparity of intention and result: when the result of an action is contrary to the desired or expected effect. Likewise, cosmic irony is disparity between human desires and the harsh realities of the outside world (or the whims of the gods). By some definitions, situational irony and cosmic irony are not irony at all.

Databases, MySQL mysql database connection failed

MySQL instance running an Infobright engine


mysql> explain SELECT COUNT(ac.UID) FROM ACTIVE ac JOIN ALL a;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
| 1 | SIMPLE | ac | ALL | NULL | NULL | NULL | NULL | 124426 | |
| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 7594256 | |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
2 rows in set (0.01 sec)

mysql> SELECT COUNT(ac.UID) FROM ACTIVE ac JOIN ALL a ON ac.UID=a.UID;
+---------------+
| COUNT(ac.UID) |
+---------------+
| 17466 |
+---------------+
1 row in set (0.23 sec)

mysql> set @tot = (SELECT COUNT(ac.UID) FROM ACTIVE ac JOIN ALL a ON ac.UID=a.UID);

^CQuery aborted by Ctrl+C


Took more than 60seconds —- what the …..
Why did it take a long?


mysql> set @a=2;
Query OK, 0 rows affected (0.00 sec)

mysql> select @a;
+------+
| @a |
+------+
| 2 |
+------+
1 row in set (0.00 sec)

mysql> set @tot = (SELECT COUNT(*) FROM ACTIVE);
Query OK, 0 rows affected (0.13 sec)

mysql> select @tot;
+--------+
| @tot |
+--------+
| 124426 |
+--------+
1 row in set (0.00 sec)

mysql> set @tot = (SELECT COUNT(ac.UID) FROM ACTIVE ac);
Query OK, 0 rows affected (0.22 sec)

mysql> select @tot;
+--------+
| @tot |
+--------+
| 124426 |
+--------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(ac.UID) FROM ACTIVE ac, ALL a;
+---------------+
| COUNT(ac.UID) |
+---------------+
| 944922897056 |
+---------------+
1 row in set (0.05 sec)

mysql> set @tot=(SELECT COUNT(ac.UID) FROM ACTIVE ac JOIN ALL a);
^CQuery aborted by Ctrl+C
ERROR 1317 (70100): Query execution was interrupted


Reason? …. the query is using the MySQL optimiser rather than the IB one! Why? good question (will have to ask IB devs though).

Work around, use ac temporary table to store the result and setting the variable to the result field, but it’s really ugly isn’t it?

Databases, Intermediate infobright, MySQL, optimiser, variables

I want to highlight the importance of reviewing mysql’s initial set of accounts.
Say you have a mysql on abc.def.ghi.jkl running on port 3306 anonymous account with privileges without a password, then:
1. mysql (if issued on localhost)
2. mysql -h abc.def.ghi.jkl
3. mysql -u ” -h abc.def.ghi.jkl
4. mysql -u ” -h abc.def.ghi.jkl -P 3306
5. mysql -u user_which_does_not_exist -h abc.def.ghi.jkl

will all manage to get into mysql given the way mysql authenticates users is against your username and client host from where you are connecting.

This verification is done versus the following columns in the mysql.user table, i.e., User,Host and Password columns.
An entry in the mysql.user table with the following values User=”, Host=’%’ will accept ANY user connecting from ANYWHERE in the world, thus disabling ANY security. Hence the reason for this blog post highlighting the importance of dropping such accounts, at least in all environments apart from dev.

Further information at:

http://dev.mysql.com/doc/refman/5.1/en/connection-access.html

Beginner, MySQL %, accounts, anonymous, authentication, security, threat, usernames

I am publishing my MySQL related bookmark collection http://www.mysqlpreacher.com/bookmarks/.

Feel free to send me links you think might be good to add in order to help others.

Remember, SHARING IS CARING!!! …. we get so much for free, why shouldn’t we give some back?

Cheers,
Darren

Beginner, Databases, MySQL administration, benchmarking, bookmarks, cluster, collection, configuration, general knowledge, ha, Information, innodb, monitoring, MySQL, performance analysis, security, slow queries

A colleague at work asked me “how can I run a case sensitive select on a case insensitive table?” out of curiosity and for a moment I hesitated, then said, yeah why not :) ….

Below are two different approaches (one of which is quite inefficient) and if anyone has another way, better or worse, please do leave a comment with your suggested approach :).

Cheers,
Darren

Preparation


mysql [localhost] {root} (test) > create table t1(a varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql [localhost] {root} (test) > insert into t1 (a) values ('darren');
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {root} (test) > insert into t1 (a) values ('Darren');
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {root} (test) > insert into t1 (a) values ('DarRen');
Query OK, 1 row affected (0.00 sec)


Method 1

mysql [localhost] {root} (test) > select * from t1 where a ='darren';
+--------+
| a |
+--------+
| darren |
| Darren |
| DarRen |
+--------+
3 rows in set (0.00 sec)

mysql [localhost] {root} (test) > create temporary table t3(a varchar(20)) Engine=MyISAM , COLLATE latin1_general_cs;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {root} (test) > insert into t3 select * from t1;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql [localhost] {root} (test) > select * from t3 where a ='darren';
+--------+
| a |
+--------+
| darren |
+--------+
1 row in set (0.00 sec)

mysql [localhost] {root} (test) > select * from t3 where a ='Darren';
+--------+
| a |
+--------+
| Darren |
+--------+
1 row in set (0.00 sec)

mysql [localhost] {root} (test) > select * from t3 where a ='DarreN';
Empty set (0.00 sec)

Method 2

mysql [localhost] {root} (test) > select * from t1 where CONVERT(a USING latin1) COLLATE latin1_general_cs ='darren';
+--------+
| a |
+--------+
| darren |
+--------+
1 row in set (0.00 sec)

mysql [localhost] {root} (test) > select * from t1 where CONVERT(a USING latin1) COLLATE latin1_general_cs ='Darren';
+--------+
| a |
+--------+
| Darren |
+--------+
1 row in set (0.00 sec)

MySQL case insensitive, case sensitivity, collation, select, temporary table

Just a small note to advise that Securich reached 0.1.4.
Some new tools include:
* Added Password complexity
* Enhanced `set_password` – Old password is now necessary to replace it by a new one
* Enhanced Revoke privileges to accept regexp
* Added Block user@hostname on a database level
* Added Creation of reserved usernames
* Added Help stored procedure displays help for each stored proc
* Enhanced `create_update_role` to include the removal of privilages from roles
* Enhanced `grant_priveleges` on `alltables` for a database without tables would terminate with an error instead of gracefully (now fixed)
* Added Restore user@hostname on a database level
* Removed ’show warnings’ from sql installation

The database design using workbench is also available in the db folder (for easier understanding of what lies beneath.

Cheers,
Darren

Beginner, MySQL administration, grant, group, groups, password complexity, password expiry, revoke, role, roles, securich, security, user, users