Three ways to recover a root user password:

The order of solutions here under gets more creative on the way down :)

1. obviously, before starting messing around check my.cnf or scripts for passwords entries, then try home directories for password files
2. secondly – can you restart mysql? if yes, restart with –skip-grant-tables, log into mysql, change your password and restart without –skip-grant-tables
3. third option – (on linux / unix ONLY)
If you haven’t found the password anywhere and can’t afford to restart your mysql.

cd data/mysql
cp -rp user.MYD bck_user.MYD_`date +%Y%m%d`
cp -rp user.MYD /tmp/user.MYD
vi /tmp/user.MYD #(edit the hashed passwords next to root*)
cp -rp /tmp/user.MYD user.MYD
sudo kill -HUP `pidof mysqld`

Note that the latter method of recovering a root password CAN be easily used maliciously leaving no trace! The only way to avoid such an attack is to make the …

Scenario Master – Master replication

MasterA is a client facing server
MasterB is a warm standby server (read only)

MasterB restarted abruptly and when instances were braught back up MasterA (it’s slave) was showing the following error:

MasterA has the following error in show slave status:
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’

Solution:

Slave: stop slave;

Master: flush logs
Master: show master status; — take note of the master log file and master log position

Slave: CHANGE MASTER TO MASTER_LOG_FILE=’log-bin.00000X′, MASTER_LOG_POS=106;
Slave: start slave;

CRIB is a CentRal Information Base for MySQL, a long time coming project since I worked on it on and off for a few weeks and now I decided it’s time to hand it over to the global MySQL community.

So, what is CRIB?

CRIB is a central database which collects information about all your MySQL instances which you set as clients. It is monitoring in a way, but not the typical number of connections, memory, index usage, table scans, cpu usage and such, but rather consists of a repository where, if you have tens, hundreds or even thousands of clients, you can see where a particular user was created, where a certain database name features or which tables does a database consists of. It also features a script which logs table sizes periodically (customizable) so you can graph disk usage over time and be able to forecast future disk space requirements easily.

Download the latest code with: bzr branch lp:crib

First …

Scenario: Master-Master replication
Description: Master A is the active db server whilst Master B is a read only swappable db server hence both are creating binary logs. During backup I run “FLUSH LOGS” in order to have a simpler point in time recovery procedure if that case arises.
Problem: Flush logs is mean mean command :) …. it rotates not only my binary logs but my error log too (since I user error-log=blahblahblah in my my.cnf). Well given I flush logs every night my error log is cycled through every night, but unlike binary logs which have an incrimental number attached to the fine, error logs only have a `-log` attached to the filename and a second “FLUSH LOG” would just clear all error logs permanently. That is really not fun believe me!

So what is the solution? you could either:
1. Not use “FLUSH LOGS” (nah that aint happenin)
2. Not use –error-log (that aint happenin either cos I need to …

Sometimes we have some special need for a stored procedure to call another to do something. That is fine, but what if the second stored proc failed for some reason? Maybe you want to halt the first stored procedure (the caller) and not proceed with the work until the problem is verified and resolved. So How do you do it?

Simple, get a return code from the called procedure to determine if it worked or not!

Here’s a sample piece of code to explain better:


DROP PROCEDURE IF EXISTS `test`.`testing123`;
DELIMITER $$

CREATE
PROCEDURE `test`.`testing123`(OUT a INT)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET a=2;
END;
SET a=0;

# toggle the below as comment or not to see the call at the bottom working
# if you uncomment select abc you'll see the error, otherwise all is cool

# select abc;

…

Are you doing some datetime manipulation or maybe you are migrating from some database technology to MySQL or possibly using milliseconds?
Here is an example on how to go about it:

Say you have the following date: MAR 16 2008 09:12:51:893AM
SELECT DATE_FORMAT(STR_TO_DATE('MAR 16 2008 09:12:51:893AM','%M %d %Y %h:%i:%s:%f%p'),'%Y%m%d%k%i%s.%f'); --> 2008031691251.893000


What if its PM rather than AM
SELECT DATE_FORMAT(STR_TO_DATE('MAR 16 2008 09:12:51:893PM','%M %d %Y %h:%i:%s:%f%p'),'%Y%m%d%k%i%s.%f'); --> 20080316211251.893000


Ok so this is just simple string manipulation where:
%M is the month name
%d is day number
%Y is the year
%h is the hour
%i is the minute
%s is the second
%f is the microsecond
%p is the period: ante or post meridiem
In the DATE_FORMAT part we se a %k which is …

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 …

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)

…

Once upon a time`stamp`, in a `data`base far far away, someone filed a bug named: `Microseconds precision is not retained by TIME, DATETIME, and TIMESTAMP field types.` – Bug Number 8523. This was the beginning of 2005, yet now that we are approaching the end of 2009, after 4.5 years, many (including myself) are still asking for this.

In fairness sake, MySQL have indeed supplied a way to retain milli and micro seconds in a decimal field `DECIMAL(17,3)`, and it is also queryable as if it were a timestamp BUT why isn’t it possible to store in a `DATETIME` or `TIMESTAMP` field? Why can’t we run a ’select now()’ or ’select curtime()’ etc and get a full timestamp including milli / micro seconds?

I have counted 37 different usernames asking for this feature, spanning from 15th Feb 2005 to recently. (list found below)

Some have suggested UDFs, others suggested using log4j while others, pretty annoyed, allegedly went …

Lately there has been quite some talk about security on MySQL, and I've decided to GPL a package I wrote, implmenting Roles on MySQL. This technology has been available on other databases for quite some time, but hasn't quite yet made it to MySQL's feature list and apart from this tool, the only solution I know of is google's patches for MySQL 5.0.