Monday 11 November 2013

MySQL Replication implementation details

The previous and current week I am dealing with MySQL replication.

Currently, I have a master-slave setup, with slave operating in readonly mode.

According to MySQL manual page, replication in slave is implemented using two threads:
  • Thread I/O is polling the Master for binlog updates: this thread polls master for any updates written to its binlog. If master has updates, I/O thread puts them in a queue.
  • Thread SQL reads the updates queue and executes them in the slave.
To assist my study, I created the following pictures illustrating the MySQL replication threads operations. Here we have to state that those operations are for the simple NON-Clustered replication as described above.  
This picture describes Slave I/O replication thread:

And the following one describes the operations of the Slave SQL replication thread:

The basic parameters for replication can be viewed from the MySQL manual here.

Thursday 7 November 2013

MySQL: "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)"

Sometimes even the easy things like master-slave resync may break your nerves....

I faced that error while I was trying to resynchronize my master-slave  servers by doing an export from master/import to slave procedure.

This error was due to the fact that slave was started after an automated installation of MySQL with Opscode Chef. Without taking a look to the Chef recipe, the slave server was re-created with nothing:
no users, no databases

 To cut the long story, when I tried login to it I faced this issue:
MySQL: "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)"

Meaning, even if /etc/my.cnf was set without password:
[client]
# By default ask for a password
#password

when I tried to connect I got:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Treatment

1. Stop MySQL service
/etc/init.d/mysqld stop

2. Start the service without the grant tables:
mysqld_safe --skip-grant-tables &

3. On the MySQL command prompt enter the following commands:

use mysql;
select * from user;

Now here is the interesting point:
if you see root user from the result set of previous select, you just have to update the root password:
update user set `Password`=PASSWORD('some_pass');

If you don't see root user  then issue the following insert command:
INSERT INTO `user` VALUES(
'localhost','root',PASSWORD('some_pass'),
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'','');

More on creating mysql root user you  can see on MySQL user accounts manual page.

Then flush the MySQL tables and leave:
flush tables;
quit;

4. Next step is to stop the service that runs without grants tables:
/etc/init.d/mysqld stop
Shutting down MySQL....131107 13:11:38 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
                                             [  OK  ]
[1]+  Done                    mysqld_safe --skip-grant-tables

5. Restart MySQL normally:
/etc/init.d/mysqld start

6. Try to log with the password you previously set:
mysql -uroot -psome_pass
Welcome to the MySQL monitor. ....

Hope it helps!


Tuesday 5 November 2013

A simple MySQL monitor for detecting InnoDB X-Locks

To monitor blocking transactions and lock waits on MySQL I use the following simple script:

root@my-db-master:~[1400]#cat monitor-x-locks.sh
#!/bin/sh
######################
# MySQL Monitoring script
# Use me when you need to log MySQL txns and detect x-locs
#####################
DBPASS=mypass

while true
do
 date +'%Y-%m-%d %H:%M:%S,%3N' >> locks.txt
 date +'%Y-%m-%d %H:%M:%S,%3N' >> lock_waits.txt
 date +'%Y-%m-%d %H:%M:%S,%3N' >> tables.txt
 echo "select * from information_schema.INNODB_LOCK_WAITS " | mysql -u root -p$DBPASS >> lock_waits.txt
 echo "select lock_id,Lock_trx_id,lock_mode,lock_table
        from information_schema.INNODB_LOCKS where lock_mode='X'" | mysql -u root -p$DBPASS >>  tables.txt
 echo "select r.trx_id waiting_trx_id,
       r.trx_mysql_thread_id waiting_thread,
       r.trx_query waiting_query,
       b.trx_id blocking_trx_id,
       b.trx_mysql_thread_id blocking_thread,
       b.trx_query blocking_query
   from information_schema.innodb_lock_waits w
   inner join information_schema.innodb_trx b  on
    b.trx_id = w.blocking_trx_id
   inner join information_schema.innodb_trx r  on
    r.trx_id = w.requesting_trx_id;"  | mysql -u root -p$DBPASS >>  locks.txt
 sleep 1 ;
done


This script creates three files and updates them with a timestamp every second.  Those files are:

locks.txt : shows which locs occurred by showing the transaction ID, thread ID from the blocked transaction and  transaction ID, thread ID from the blocking transaction

tables.txt Shows which transactions have placed an exclusive lock

lock_waits.txt: Shows transactions waiting for a lock over a table

 Use it as follows:

root@my-db-master:~[1402]#./monitor-x-locks.sh &
[1] 23184
root@my-db-master:~[1403]#fg
./monitor-x-locks.sh
^C
root@my-db-master:~[1396]#ls -l
total 60
-rw-r--r--  1 root root   216 Nov  5 20:39 locks.txt
-rw-r--r--  1 root root   216 Nov  5 20:39 lock_waits.txt
-rwxr--r--  1 root root  1230 Nov  5 20:38 monitor-x-locks.sh
-rw-r--r--  1 root root   216 Nov  5 20:39 tables.txt

JBoss Monitoring from JConsole

This was a very simple issue pointed to me by a friend:
"OK.. it would be interesting to restrict jmx-console over http and instead, use JConsole to monitor our JBoss 5.0.1.GA from a VPN tunneled port"

JConsole is a very important tool for monitoring the JVM. It is located in you JAVA_HOME/bin.

This is very simple for the local JBoss process, in other words, when JConsole and JBoss are running on the same machine. You just have to start JBoss and run JConsole.

In case when JBoss is running on a different IP you just have to find the following line in your server.log:

2012-03-16 04:58:30,047 INFO  [org.jboss.mx.remoting.service.JMXConnectorServerService] (main) JMX Connector server: service:jmx:rmi://kandath/jndi/rmi://kandath:1090/jmxconnector

Get the connector server URL:
 service:jmx:rmi://kandath/jndi/rmi://kandath:1090/jmxconnector
paste it to the JConsole  as the following picture instructs:
JBoss as a local process and remote using remote JMX service

JBoss 5.1.0.GA comes preconfigured with the following: 
1.  JMX service is on and port to use for RMI by default is set to 1090.
2. No security on JMX console!
Consider seriously to secure the access to the JMX service.

Also read the following interesting bug on JBoss jira


My Linux Shell Cook-Book

How many times we struggle ourselves to remember how to write correctly a for loop in shell ?
When was the last time we did a search for string comparison in bash and test for a file timestamp?

My favourite treatment for weak memory or "shell syntax blackouts" is a simple text file called
MyBashCooks.txt which takes proudly always the first tab in Notepad++ when I am coding scripts.

I think it is void to mention that this small file is hand on the web and travels with me when I am visiting clients, generally, it is one of the very few things I have handy and don't move a second step without it.

The recipes in my cookbook share a common secret: single command line, so you can use them on the command prompt as is or break them in several lines inside your scripts.

I think we say too much on this, please enjoy and if you find a mistake, please drop me a line:

# If then else in a single line
r=ee; if [ "$r" == "" ] ;  then  echo "is null" ; else  echo "not null"; fi

# If then else ARITHMETIC Operations
x=0;if [ $x -eq 0 ]; then echo "x=0";else echo "other"; fi

# Check if word belongs to string and if it does, run a shell command 
# Compatible with all shells
string="this is a string "; word="str";test "${string#*$word}" != "$string" && echo "$word found in $string"

# Negative of the previous
string="this is a string "; word="xxx";test "${string#*$word}" != "$string" || echo "$word not found in $string"

# Combined case of found and not found....
string="this is a string "; word="xxx";test "${string#*$word}" != "$string" && echo "Executes when found." || echo "Executes when NOT found."


# single line while loop
while true; do  echo '----'; sleep 1 ; done

while true; do top -b -n 1  |  egrep  'top|Tasks|Cpu|Mem|Swap|PID|mysql' | grep -v root   ;sleep 10; echo '----' ; done

# single line for loop
for h in {app0,app1,app2,app3,index0,index1,index2,index3};  do echo $h; sleep 1;  done

# Tests the existence  of word in a string: works in all POSIX shells (bash, dash, korn...) 
string="my name is MyBighrase";word="e i"; echo "search [$word] in [$string]";test "${string#*$word}" != "$string" && echo "$word found in $string"

# From UNIX timestamp to date:
echo 1365436826 | gawk '{print strftime("%c", $0)}'

# From Date to timestamp:
date --date='04/04/2013 20:30:01' +"%s"

# Format Date output
date +"%m-%d-%y"

# Search for a string across directories with grep
find -RH theString /this/directory

# Serach for line numbers and cut a file only from line to line:
this one searches for all line numbers with date 20130607
cat  slow.log | grep -n 130607  
we get the lines of this date exists and isolate those numbers in a new file.
cat  slow.log | sed -n '16582739,17369638p' > slow_20130607.txt

# Monitoring Linux Processes

while true ; do top -b -n 1 -p 2163 >> mysql_3000.txt ; sleep 15 ;  done &

while true ; do date >> tmpfs_3000.txt ; df -h | grep mysql ; sleep 15 ;  done >> tmpfs_3000.txt &

while true ; do date >> process_3000.txt;  echo "show full processlist;" |  mysql -uroot -pmypass | grep -v Sleep >> process_3000.txt  ; sleep 15 ; done &

Thursday 31 October 2013

Digging into MySQL InnoDB Transactions

A week ago I faced a really difficult situation: one of our MySQL innodb databases was slowing down in response times really a lot. With a bit of debugging, I figure out that there were some X-locks (eXclusive locks) that did not allowed some of the later transactions to finish. Here is the procedure I followed:

1. Use MySQL processlist to find out slow operations. The first one show us the slow queries on a specific database:



select * from information_schema.processlist 
              where db='alfresco' and command='Query' order by 'time' desc; 

Since I understood that the specific slow queries are utilizing two tables I also used:



select * from information_schema.processlist 
      where db='alfresco' and info like '%alf_%_assoc%' 



2. Now since we have one idea about heavy MySQL threads, lets take a look to the transactions:
First of all see all transactions that have placed an exclusive lock on one of the tables:
Second find the lock wait transactions


select * from information_schema.INNODB_TRX 
         where trx_id in ( 
             select Lock_trx_id from information_schema.INNODB_LOCKS where lock_mode='X'  )

select * from information_schema.INNODB_TRX where trx_id in (
         select distinct blocking_trx_id 
            from information_schema.INNODB_LOCK_WAITS ) 


3. Finally find the threads along with conflicting transactions with the following query:


SELECT r.trx_id waiting_trx_id,  
       r.trx_mysql_thread_id waiting_thread,
       r.trx_query waiting_query,
       b.trx_id blocking_trx_id, 
       b.trx_mysql_thread_id blocking_thread,
       b.trx_query blocking_query
   FROM       information_schema.innodb_lock_waits w
   INNER JOIN information_schema.innodb_trx b  ON  
    b.trx_id = w.blocking_trx_id
  INNER JOIN information_schema.innodb_trx r  ON  
    r.trx_id = w.requesting_trx_id;
Beware: The thread ID (waiting and blocking) is the ID in the information_schema.processlist







In this case, clearly the transaction with id 194D110F is the blocker!
To inspect the transaction use this query:



select * from information_schema.INNODB_TRX where trx_id='194D110F'









From what we see, there is a transaction running since 19:10:57 with id 194D110F. The query or operation carried out of this transaction is null because it was deleted from the transaction cache since it is very old. To retrieve the information about the SQL statement carried from this transaction we have to use the MySQL binlog.

The transaction 194D110F was observed during a  load test performed on our system. The lock was not released for 2 h.
The locking thread in MySQL was seen as follows from the slow.log:

---TRANSACTION 194D110F, ACTIVE 7431 sec
74 lock struct(s), heap size 14776, 101 row lock(s), undo log entries 64
MySQL thread id 7526, OS thread handle 0x7fd28ec38700, query id 10005877 application-host X.X.44.28 alfresco
Trx read view will not see trx with id >= 194D1110, sees < 194C03CD