---

Update The original article mentioned in this post is no longer available, but if you are still interested in query profiling in MySQL, check out this http://dev.mysql.com/doc/refman/5.0/en/show-profiles.html
I will write up an quick start guide to profiling myself here, one day.
 
-- original post
this article is a great way to get started using the mysql query profiler.

If you've never used it before take some time to check it out, it will change the way you think about the database forever.

---

---

Use these two commands to get the top ten and top ten repeaters from MySQL's slow query log.

mysqldumpslow -t 10 ./slow-log > /home/danny/top_ten_slow_query.txt

mysqldumpslow -a -s c -t 10 ./slow-log > /home/danny/top_ten_repeat_slow_query.txt

---

---

I've often wondered, idly, how to get MySQL to output a row number column, which would rank sorted and grouped results.
Well thanks to this excellent post I've found out how:

SELECT *, @rownum:=@rownum+1 as rank FROM ( 
-- Your Query Goes in Here
        SELECT t.*
        FROM table t
        WHERE something
        group by 2,3,4
        order by 1 desc limit 20
    ) day_rank, (SELECT @rownum:=0) r;

---

---

This is a useful one if, like us, you find that the account used to create the procedure doesn't have the privileges required to execute it, or perhaps you've revoked the original definers grant. (DoH!)

ALTER PROCEDURE procedure_name SQL SECURITY DEFINER;javascript:void(0)

---

---

Another piece of DBA hoodoo that I seldom need!

SELECT @@global.read_only;
SET GLOBAL read_only=1;
SELECT @@global.read_only;

---

---

Another one that I have to google every time.
Not being a DBA I don't do this very often, but now and again I find myself in the Hot Seat.

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
START SLAVE;
SHOW SLAVE STATUS;

---

---

We moved some hardware about last night.
Well actually Jim and Marcin did.

This AM I got to the office to discover everything apparently working (its early days yet!) apart from one mysql replication.

Now; we've set up this replication to replicate one db using replicate-do-db which has very many of its own crazy gotcha's that I'm not going to get into here.

We also set the slave params by executing a command, rather than in my.cnf.
This allows us to meddle without having to restart mysqld, and protects us from accidentally reverting the settings if mysqld should require a restart.

my.cnf only contains the id of the slave, skip-slave-start, and replicate-do-db that affect replication.

This morning, h'wever, the slave was compaining that it couldn't open the relay log, which was annoying. I found a post here which suggested flush logs that didn't work. The next suggestion is reset slave.

Now I'm afraid of reset slave, but I didn;t think I had many options, so I forged ahead and here's how I got the job done.

NOTE-- I take no responsibility for anything misguded and foolish that you may choose to do in the light of reading this, or any other, bit of misleading nonsense on the internet.

If you have any doubts about your ability to resist doing this without putting a roll back strategy in place before hand then read no further. You have been warned!

Working completely on the slave:
1/ stop the slave .
2/ show slave status
3/ make a note of Read_Master_Log_Pos, which should equal Exec_Master_Log_Pos, and of Master_Log_File
4/ now execute reset slave
5/ now set the params you noted..

CHANGE MASTER TO MASTER_HOST='xxxx.killerbees.co.uk',
MASTER_USER='xxxx',
MASTER_PASSWORD='xxxx',
MASTER_LOG_FILE='Master_Log_File',
MASTER_LOG_POS= Read_Master_Log_Pos

6/ start slave

Job Done.

You can also use this approach to start a second slave on the same master without having to affect the master's operation:
stop slave one,
note the params,
set slave two params,
export-import the data from slave one to slave two,
start slave two.

---

---

Taking time out from the Big Migration job to share this with you, and before you say anything *I Know* this is old news, but forgive me because I've been away from MySQL for 4 years, and just because you know this doesn't mean that there aren't other "late adopters" like me out there.

Over the past few weeks I've come across quite a few posts on the net from folks who wanted to use oracle style links between MySQL databases (I don't need to, I just spoted a theme). Researching the storage engines in Mysql 5 (we're upgrading as well as migrating) it seems there is now an answer in the form of the Federated storage engine.

The article also provided me an answer for the perennial problem of needing several instances of MySQL in dev and test, often for exactly the task currently at hand, migrating from one version to another, that answer is the mysql sandbox. Neat. Its a shame that I've already set up my dev environment, I'd like to play with that.

---