MySQL Preacher

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

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

It”s not the most common task in the world, but you might want to view processes from a particular user and once in a while you might even need to kill processes from a single user, be it during an attack or because you simply got a bug in an application bombarding your db server with connections!

Here is a small stored procedure which does exactly that!

call process_list("show","username","hostname");
– shows all processes owned by username@hostname
call process_list("kill","username","hostname");
– kills all processes owned by username@hostname

The code for this stored procedure can be found below. If you have any comments / suggestions feel free to comment below.

######################################################################
## ##
## Stored Procedure: process_list & kill user ##
## call process_list("show","%") show processlist for all users ##
## call process_list("show","root") show processlist for root user ##
## call process_list("kill","user1") kill connections for user1 ##
## ##
## by Darren Cassar http://www.mysqlpreacher.com ##
## ##
######################################################################

DROP PROCEDURE IF EXISTS process_list;

DELIMITER $$

CREATE PROCEDURE "process_list"( choice char(4), usernamein varchar(16), hostnamein varchar(60))
BEGIN

DECLARE CURCONN int;

IF choice <> "show" AND choice <> "kill" then
select "wrong choice";
END IF;

IF usernamein = "" then
set usernamein = "%";
END IF;

IF hostnamein = "" then
set hostnamein = "%";
END IF;

SET CURCONN=(select connection_id());

IF choice = "show" then

select *
from information_schema.processlist
where ID <> CURCONN and
USER like usernamein and
( HOST like CONCAT(hostnamein ,":%") or
HOST like hostnamein );

ELSEIF choice = "kill" then

IF usernamein = "root" then
select "Illegal username when killing processes";
ELSE
SET @CNT = (
select count(*)
from information_schema.processlist
where ID <> CURCONN and
USER like usernamein and
( HOST like CONCAT(hostnamein ,":%") or
HOST like hostnamein )
);

SET @VAR=1;

WHILE ( @VAR <= @CNT) DO

SET @TID = (
select id
from information_schema.processlist
where ID <> CURCONN and
USER like usernamein and
( HOST like CONCAT(hostnamein ,":%") or
HOST like hostnamein ) limit 1
);

SET @k = CONCAT("kill " , @TID);
PREPARE killcom FROM @k;
EXECUTE killcom;
set @k=NULL;

SET @VAR=@VAR+1;

END WHILE;

END IF;

END IF;

END$$

DELIMITER ;


Beginner, Databases, MySQL kill, mysql stored procedure, processes, processlist, sp, stored proc, stored procedure

Earlier this year I had published a small blog about being efficient when using mysql prompt. This is a small continuation of it highlighting a couple of other cool features which I really find very useful when working command line (i.e. always!).

The first I’m gonna list here is setting the prompt itself by typing – \R mysql \D > thus enabling date and time display at each comand which is great for auditing and record keeping. There is a whole bunch of prompt values which you can find here:

Option Description
\c //A counter that increments for each statement you issue
\D //The full current date
\d //The default database
\h //The server host
\l //The current delimiter (new in 5.0.25)
\m //Minutes of the current time
\n //A newline character
\O //The current month in three-letter format (Jan, Feb, …)
\o //The current month in numeric format
\P //am/pm
\p //The current TCP/IP port or socket file
\R //The current time, in 24-hour military time (0–23)
\r //The current time, standard 12-hour time (1–12)
\S //Semicolon
\s //Seconds of the current time
\t //A tab character
\U //Your full user_name@host_name account name
\u //Your user name
\v //The server version
\w //The current day of the week in three-letter format (Mon, Tue, …)
\Y //The current year, four digits
\y //The current year, two digits
\_ //A space
\ //A space (a space follows the backslash)
\' '//Single quote
\" "//Double quote
\\ //A literal “\” backslash character
\x //x, for any “x” not listed above

The second thing is the \e or edit which lets you edit a long query you might have been writing, in any editor you have set on your machine, most often vi, a must know editor for all of you out there.

Enjoy
Darren

Beginner, Databases, MySQL

A few interesting KPIs about certified people and their current location (the fact that many people travel from one country to the other for work is very popular these days especially in the IT sector):

Before starting to read, I am hereby assuming the MySQL list of MCDBAs is on a residence basis not citizenship.

There are at the time of this writing a total of 796 MCDBAs in the world, living in 57 countries.

When analyzing the number of MCDBAs / country, it is pretty obvious the scene is totally dominated by the US:

+----------------------+-------------+
| Country              | No of Certs |
+----------------------+-------------+
| UnitedStates         |         346 | 
| Unitedkingdom        |          47 | 
| Germany              |          46 | 
| India                |          34 | 
| Italy                |          28 | 
| Japan                |          26 | 
| Canada               |          23 | 
| China                |          20 | 
| Brazil               |          18 | 
| Australia            |          16 | 
| France               |          16 | 
| Netherlands          |          13 | 
| Switzerland          |          13 | 
| SouthKorea           |          12 | 
| Spain                |          11 | 
| HongKong             |          10 | 
| Sweden               |          10 | 
| SouthAfrica          |          10 | 
| Singapore            |           9 | 
| Taiwan               |           8 | 
| Poland               |           7 | 
| Philippines          |           6 | 
| Norway               |           5 | 
| Russia               |           5 | 
| Finland              |           5 | 
| Ireland              |           4 | 
| Israel               |           3 | 
| Srilanka             |           3 | 
| Ukraine              |           3 | 
| Austria              |           3 | 
| Belgium              |           3 | 
| Denmark              |           2 | 
| Egypt                |           2 | 
| Mexico               |           2 | 
| Malta                |           2 | 
| Thailand             |           2 | 
| Unitedarabemirates   |           2 | 
| Latvia               |           2 | 
| Bangladesh           |           1 | 
| Croatia              |           1 | 
| TrinidadandTobago    |           1 | 
| BosniaandHerzegovina |           1 | 
| Malaysia             |           1 | 
| Slovenia             |           1 | 
| SaudiArabia          |           1 | 
| Indonesia            |           1 | 
| Jordan               |           1 | 
| Kenya                |           1 | 
| Lithuania            |           1 | 
| Luxembourg           |           1 | 
| Guatemala            |           1 | 
| Nepal                |           1 | 
| Gibraltar            |           1 | 
| Newzealand           |           1 | 
| Pakistan             |           1 | 
| Portugal             |           1 | 
| Virginislands        |           1 | 
+----------------------+-------------+ 


Advanced, Beginner, Databases, Intermediate, MySQL certification, exam, kpis, MCDBA, MySQL certified dba, statistics

Save time and energy: How to:

Listening to “Highway to Hell” in an Oxford University computer lab on a sunday afternoon writing a MySQL blog can be legitimally defined by some as sick but thats what a geek calls a relaxing yet productive sunday afternoon.

For the sake of all those CLI ambassadors like myself, I wanted to share a couple of tips and tricks I use quite often when doing my mysql db administration work / scripting. I strongly suggest any mysql dba / dev use CLI simply because thats the one thing you should always have on any machine on which mysql is installed, be it Unix, Linux, Mac, Microsoft or whatever platform you are using.

Cancelling a query you are typing without exiting to the terminal:
`\c` – How many times you ended up hitting `CTRL+C` (default process kill in most OSes) in order to cancel a command, finding yourself going back to the console and having to connect again? Nuisence? Hell yeah! Especially if you had a number of temp tables waiting to be used.


A `CTRL+C` would bring send you to console:
mysql> SELECT
-> User, Host
-> OOPS I DID A MISTAKE
-> FROM --Hit `CTRL+C` here
-> Aborted
mysqlpreacher:~ darrencassar$

A `\c` in the mysql program would just cancel the wrong query:
mysql> SELECT
-> User, Host
-> OOPS I DID A MISTAKE
-> FROM
-> \c
mysql>

BTW it doesn’t work with the USE command – it gives an error but at least doesn’t terminate the connection:

mysql> USE m\c
ERROR 1049 (42000): Unknown database 'mc'
mysql>


Running a console command from within a mysql terminal session:
`\!` – Need to run a console command while you are logged in a mysql terminal session? There is no need to exit the current session or to dupplicate the terminal session and run the command, just use `\!`


mysql> \! pwd
/var/root/sandboxes/mysql_5130
mysql> \! ls
COPYING VERSION data my restart stop
README clear grants.mysql my.sandbox.cnf send_kill use
USING current_options.conf load_grants proxy_start start
mysql> \! ps -ef | grep mysql | grep -v grep
0 36319 1 0 0:00.01 ttys002 0:00.02 /bin/sh /mysql/releases/5.1/5.1.30/bin/mysqld_safe --defaults-file=/var/root/sandboxes/mysql_5130/my.sandbox.cnf
0 36389 36319 0 0:00.14 ttys002 0:01.48 /mysql/releases/5.1/5.1.30/bin/mysqld --defaults-file=/var/root/sandboxes/mysql_5130/my.sandbox.cnf --basedir=/mysql/releases/5.1/5.1.30 --datadir=/var/root/sandboxes/mysql_5130/data --user=root --log-error=/var/root/sandboxes/mysql_5130/data/msandbox.err --pid-file=/var/root/sandboxes/mysql_5130/data/mysql_sandbox3308.pid --socket=/tmp/mysql_sandbox3308.sock --port=3308
0 36987 26344 0 0:00.01 ttys002 0:00.02 mysql -u root -p --auto-rehash -h 127.0.0.1 -P 3308
0 36992 36987 0 0:00.00 ttys002 0:00.00 sh -c ps -ef | grep mysql
mysql>


Autocompletion on command line mysql:
`\#`, `rehash` or `–auto-rehash` – Autocompletion is fast, handy and help you produce more in less.
Autocompletion in MySQL is enabled by default, and there is a need to use –disable-auto-rehash (note that –no-auto-rehash or -A are deprecated) in order to disable rehashing which makes mysql start faster. If this is done, then it can be called upon using either of the following `\#`, `rehash` or using `–autu-rehash` depending on where you are and what you are comfortable with. If you are already in a mysql terminal session, then type in `\#` or `rehash`. Another option is to connect to mysql using: `mysql -u user -p –auto-rehash` as a startup option. Having done that, remember to initialise the hashing by issueing a `USE DATABASENAME` as otherwise the autocompletion won’t work. Autocompleting a word / name can be done in a similar way to what you’d do in CL UNIX, LINUX or DOS, typing the first part of the word and then pressing `TAB`.

–auto-rehash:

mysqlpreacher:~ darrencassar$ mysql -u root -p --auto-rehash -h 127.0.0.1 -P 3308
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.30 MySQL Community Server (GPL)

Database changed
mysql> use m
max_connections max_questions max_updates max_user_connections modified mysql
mysql> use mysql
Database changed


\# or rehash:

mysqlpreacher:~ darrencassar$ mysql -u root -p -h 127.0.0.1 -P 3308
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.30 MySQL Community Server (GPL)

Database changed
mysql> use m
max_connections max_questions max_updates max_user_connections modified mysql
mysql> use mysql


more tips and tricks will follow in future blogs,

Asta la vista mysql user!

Beginner, Databases, Intermediate, MySQL auto completion, cli, rehash

Giving a bunch of mysql instances something you do everyday and you might think ….. how should I do it? Write a bunch of selects and inserts manually? nahh that takes s**tload of time, should I run binlogs collected from a live system on my test server? nahh thats not practical nor is it real since it doesn’t contain selects, should I gather the general query log and try that out? nahhh  …..

MySQL has been kind enough to supply us with their mysql_slap which does the job for us and given I needed to do a proof of concept on monitoring a group of 4 circular replicated servers I wrote a small script which does the job of slapping them with a varying level of concurrancy, iterations, number of queries and connections for as long as you like.

Here it is and I hope some of you might find it useful for slapping their own test servers :).

#!/bin/bash

NumberOfConcurrentLoads=4
LOGFILE=randomslap-4n-circularreplication

load () {

run=0
#Obtaining pid of the current process
PID=$$

echo "MySQL Slap random loading MySQL instances" >> $LOGFILE-$PID.log
echo "Loading child $a out of $NumberOfConcurrentLoads" >> $LOGFILE-$PID.log
echo "Starting at: `date`" >> $LOGFILE-$PID.log
echo ""

while [ 1 ]
do

#Generate a few values for the actual command to run through
prt=$((RANDOM%4+17001)) #ports being 17001,17002,17003,17004
cnc=$((RANDOM%50+1)) #number of concurrent sessions
itr=$((RANDOM%9+1)) #number of iterations
noq=$((RANDOM%100+50)) #number of queries
sec=$((RANDOM%30+10)) #seconds idle after finishing the process

echo "Run number $run" >> $LOGFILE-$PID.log
echo `date +%Y/%m/%d---%H:%M | sed 's/---/ /'` >> $LOGFILE-$PID.log

echo "Port=$prt" >> $LOGFILE-$PID.log
echo "Concurrency=$cnc" >> $LOGFILE-$PID.log
echo "Iterations=$itr" >> $LOGFILE-$PID.log
echo "Number-Of-Queries=$noq" >> $LOGFILE-$PID.log
echo "Breaktime=$sec" >> $LOGFILE-$PID.log

echo "Output:" >> $LOGFILE-$PID.log
mysqlslap --user=root --password='msandbox' --auto-generate-sql -vv -h hostname -P $prt --concurrency=$cnc --iterations=$itr --number-of-queries=$noq >> $LOGFILE-$PID.log

run=`expr $run + 1`
sleep $sec

done

}
#Fork a number of concurrent scripts to emulate more of a realistic load rather than one process connecting to just one server at a time
for ((a=1;a<=NumberOfConcurrentLoads;a++))
do
load &
done

If you are wondering … yes I totally indendate my code :) but the html version came out this way and I didn’t bother with indenting it using html really.

Beginner, Databases, Intermediate, MySQL, Uncategorized benchmarking, monitoring, mysqlslap, testing