simulate the Dead Lock issue

use test ;
create table table_a ( emp int, ename varchar(100));
create table table_b ( emp int, ename varchar(100));
mysql> insert into table_a values (1,'anil');
mysql> insert into table_a values (2,'sunil');
mysql> insert into table_b values (2,'sunil');
mysql> insert into table_b values (1,'anil');

open two sessions

first session

prompt session1>
session1> start transaction;
session1> update table_a set ename='xxx' where eno=1;
session1> update table_2 set ename='xxx' where emp=1;

second session

prompt session2>
session2> use test;
session2> start transaction;
session2> update table_2 set ename='xxx' where emp=1;
session2> update table_a set ename='xxx' where eno=1;
session2>  update table_a set ename='zzz' where emp=1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

The resolutions are as follows:
######################

By default MySQL will wait for 50 seconds for the "lock wait timeout" error .
In some rare cases InnoDB would commit queries partially after waiting for 50 seconds.
In order to avoid this, we should add  "innodb_rollback_on_timeout" and increase the
"innodb_lock_wait_timeout" status in my.cnf which are as follows:
innodb_lock_wait_timeout = 120 seconds
innodb_rollback_on_timeout

For more details about the deadlock

the usl is as follows:
http://dev.mysql.com/doc/refman/5.0/en/innodb-lock-modes.html

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation

1.) When we are tring to take the back and restore using different mysql versions then we will come across the issue. sometimes the error message shows different way
2.) when we try to updated the records connecting to different mysql using different client version
then this type of issue occurs

There are two ways to solve the problem.

1.) setting the session variables as  follows:
set @@session.collation_connection='latin1_swedish_ci'

2.) changes in the config file as follows:
    The simplest way would be to add a line to the [client] section of your config file:
    default-caracter-set=latin1;


The different collations and characters are as follows for different version.

##########################mysql version 5.0 #############################

mysql> select version();
+------------+
| version()  |
+------------+
| 5.0.77-log |
+------------+
1 row in set (0.15 sec)

mysql> show variables like '%coll%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

###################################mysql 5.5.11 version ##################3


mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.11    |
+-----------+
1 row in set (0.00 sec)

mysql> show variables like '%colla%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)


For more details about the collation and character set the url is as follows:

http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html

Semi synchronous replication in mysq 5.5

Semi synchronous replication
About MySQL Semi-Synchronous Replication
MySQL replication by default is asynchronous. The master writes events to its binary log but does not know whether or when a slave has retrieved and processed them. With asynchronous replication, if the master crashes, transactions that it has committed might not have been transmitted to any slave. Consequently, failover from master to slave in this case may result in failover to a server that is missing transactions relative to the master.
Semi synchronous replication can be used as an alternative to asynchronous replication:
• A slave indicates whether it is semi synchronous-capable when it connects to the master.
• If semi synchronous replication is enabled on the master side and there is at least one semi synchronous slave, a thread that performs a transaction commit on the master blocks after the commit is done and waits until at least one semi synchronous slave acknowledges that it has received all events for the transaction, or until a timeout occurs.
• The slave acknowledges receipt of a transaction's events only after the events have been written to its relay log and flushed to disk.
• If a timeout occurs without any slave having acknowledged the transaction, the master reverts to asynchronous replication. When at least one semi synchronous slave catches up, the master returns to semi synchronous replication.
• Semi synchronous replication must be enabled on both the master and slave sides. If semi synchronous replication is disabled on the master, or enabled on the master but on no slaves, the master uses asynchronous replication.


Scenario:
Here we have one master (Master) and two slaves (Slave1 and Slave2). Semi sync replication plug-ins installed and configured on both slaves in semi synchronous replication mode.
Created a database 'sakila' and a table 'aa' under it with few rows of data
Install Plug-ins:
On Master
We need to first install the plug-in to enable Semi Synchronous Replication.
mysql> show plugins;
+-----------------------+----------+--------------------+---------+---------+
| Name                  | Status   | Type               | Library | License |
+-----------------------+----------+--------------------+---------+---------+
| binlog                | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| mysql_native_password | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| mysql_old_password    | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| MyISAM                | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MEMORY                | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MRG_MYISAM            | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| CSV                   | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| BLACKHOLE             | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| ARCHIVE               | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| InnoDB                | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| INNODB_TRX            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCKS          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCK_WAITS     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_RESET      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM_RESET   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| PERFORMANCE_SCHEMA    | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| FEDERATED             | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
| partition             | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
+-----------------------+----------+--------------------+---------+---------+
20 rows in set (0.00 sec)
Install semi sync plug-in
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.00 sec)
 
mysql> show plugins;
+-----------------------+----------+--------------------+--------------------+---------+
| Name                  | Status   | Type               | Library            | License |
+-----------------------+----------+--------------------+--------------------+---------+
| binlog                | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| mysql_native_password | ACTIVE   | AUTHENTICATION     | NULL               | GPL     |
| mysql_old_password    | ACTIVE   | AUTHENTICATION     | NULL               | GPL     |
| MyISAM                | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| MEMORY                | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| MRG_MYISAM            | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| CSV                   | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| BLACKHOLE             | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| ARCHIVE               | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| InnoDB                | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| INNODB_TRX            | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_LOCKS          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_LOCK_WAITS     | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMP            | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMP_RESET      | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMPMEM         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMPMEM_RESET   | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| PERFORMANCE_SCHEMA    | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| FEDERATED             | DISABLED | STORAGE ENGINE     | NULL               | GPL     |
| partition             | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| rpl_semi_sync_master  | ACTIVE   | REPLICATION        | semisync_master.so | GPL     |
+-----------------------+----------+--------------------+--------------------+---------+
21 rows in set (0.00 sec)

On Slave - 1
On every Slave we need to install the Semi Synchronous Replication Plug-in
mysql> show plugins;
+-----------------------+----------+--------------------+---------+---------+
| Name                  | Status   | Type               | Library | License |
+-----------------------+----------+--------------------+---------+---------+
| binlog                | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| mysql_native_password | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| mysql_old_password    | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| MyISAM                | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MEMORY                | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MRG_MYISAM            | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| CSV                   | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| BLACKHOLE             | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| ARCHIVE               | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| InnoDB                | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| INNODB_TRX            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCKS          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCK_WAITS     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_RESET      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM_RESET   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| PERFORMANCE_SCHEMA    | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| FEDERATED             | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
| partition             | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
+-----------------------+----------+--------------------+---------+---------+
20 rows in set (0.00 sec)

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.04 sec)
mysql> show plugins;
+-----------------------+----------+--------------------+-------------------+---------+
| Name                  | Status   | Type               | Library           | License |
+-----------------------+----------+--------------------+-------------------+---------+
| binlog                | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| mysql_native_password | ACTIVE   | AUTHENTICATION     | NULL              | GPL     |
| mysql_old_password    | ACTIVE   | AUTHENTICATION     | NULL              | GPL     |
| MyISAM                | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| MEMORY                | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| MRG_MYISAM            | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| CSV                   | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| BLACKHOLE             | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| ARCHIVE               | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| InnoDB                | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| INNODB_TRX            | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_LOCKS          | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_LOCK_WAITS     | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_CMP            | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_CMP_RESET      | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_CMPMEM         | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_CMPMEM_RESET   | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| PERFORMANCE_SCHEMA    | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| FEDERATED             | DISABLED | STORAGE ENGINE     | NULL              | GPL     |
| partition             | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| rpl_semi_sync_slave   | ACTIVE   | REPLICATION        | semisync_slave.so | GPL     |
+-----------------------+----------+--------------------+-------------------+---------+
On Slave - 2
Same as mentioned in Slave - 1
Enable SS Replication
After we install the plug-in we need to enable it on both Master and Slave(s).
On Master
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'rpl%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_recovery_rank                  | 0     |
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 10000 |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
5 rows in set (0.00 sec)
 
mysql> show global status like 'rpl%';
+--------------------------------------------+-------------+
| Variable_name                              | Value       |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients               | 0           |
| Rpl_semi_sync_master_net_avg_wait_time     | 0           |
| Rpl_semi_sync_master_net_wait_time         | 0           |
| Rpl_semi_sync_master_net_waits             | 0           |
| Rpl_semi_sync_master_no_times              | 0           |
| Rpl_semi_sync_master_no_tx                 | 0           |
| Rpl_semi_sync_master_status                | ON          |
| Rpl_semi_sync_master_timefunc_failures     | 0           |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0           |
| Rpl_semi_sync_master_tx_wait_time          | 0           |
| Rpl_semi_sync_master_tx_waits              | 0           |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0           |
| Rpl_semi_sync_master_wait_sessions         | 0           |
| Rpl_semi_sync_master_yes_tx                | 0           |
| Rpl_status                                 | AUTH_MASTER |
+--------------------------------------------+-------------+
15 rows in set (0.00 sec)
On Slave [both slaves]
On every slave we should install the Semi Synchronous plug-in. After the plug-in is installed we need to enable the SS replication on the slave.
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'rpl%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_recovery_rank               | 0     |
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+

mysql> show global status like 'rpl%';
+----------------------------+-------------+
| Variable_name              | Value       |
+----------------------------+-------------+
| Rpl_semi_sync_slave_status | ON         |
| Rpl_status                 | AUTH_MASTER |
+----------------------------+-------------+

Testing of SS Replication
Let’s assume the slaves are already installed with Semi Synchronous Replication and already enabled. How we will insert the record on Master.
On Master
mysql> use sakila
Database changed
mysql> select * from aa;
+------+------+
| id   | name |
+------+------+
|    1 | asa  |
|    2 | eter |
|    3 | dfr  |
|    4 | eee  |
|    5 | eee  |
|    6 | deee |
|    7 | eefe |
+------+------+
7 rows in set (0.00 sec)

Here you can notice that the record is inserted immediately in the Master and the SS replication is enabled between Master and Slaves and they are performing as expected.

mysql> insert into aa values(8,'test'),(9,'foo');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from aa;
+------+------+
| id   | name |
+------+------+
|    1 | asa  |
|    2 | eter |
|    3 | dfr  |
|    4 | eee  |
|    5 | eee  |
|    6 | deee |
|    7 | eefe |
|    8 | test |
|    9 | foo  |
+------+------+
9 rows in set (0.00 sec)
On Slave1
mysql> use sakila
Database changed
mysql> select * from aa;
+------+------+
| id   | name |
+------+------+
|    1 | asa  |
|    2 | eter |
|    3 | dfr  |
|    4 | eee  |
|    5 | eee  |
|    6 | deee |
|    7 | eefe |
|    8 | test |
|    9 | foo  |
+------+------+
9 rows in set (0.00 sec)

On Slave2

mysql> use sakila
Database changed
mysql> select * from aa;
+------+------+
| id   | name |
+------+------+
|    1 | asa  |
|    2 | eter |
|    3 | dfr  |
|    4 | eee  |
|    5 | eee  |
|    6 | deee |
|    7 | eefe |
|    8 | test |
|    9 | foo  |
+------+------+
9 rows in set (0.00 sec)
 
Semi-Synchronous Failure Testing
To test the failure of semi-synchronous we need to stop the Slave thread on at least one Slave. Here we will stop it on Slave 1
On Slave1
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
So, once slave is stopped its semi sync slave status also disabled automatically.
mysql> show global status like 'rpl%';
+----------------------------+-------------+
| Variable_name              | Value       |
+----------------------------+-------------+
| Rpl_semi_sync_slave_status | OFF         |
| Rpl_status                 | AUTH_MASTER |
+----------------------------+-------------+
2 rows in set (0.00 sec)

On Master
Now we need to insert few rows on Master to test the replication behavior.
mysql> insert into aa values(10,'tedd'),(11,'food');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

We can see the rows are inserted to database immediately as the Slave – 2 is still in semi synchronous mode.

mysql> select * from aa;
+------+------+
| id   | name |
+------+------+
|    1 | asa  |
|    2 | eter |
|    3 | dfr  |
|    4 | eee  |
|    5 | eee  |
|    6 | deee |
|    7 | eefe |
|    8 | test |
|    9 | foo  |
|   10 | ted  |
|   11 | food |
+------+------+
11 rows in set (0.00 sec)

mysql> show global status like 'rpl%';
+--------------------------------------------+-------------+
| Variable_name                              | Value       |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients               | 1           |
| Rpl_semi_sync_master_net_avg_wait_time     | 515         |
| Rpl_semi_sync_master_net_wait_time         | 515         |
| Rpl_semi_sync_master_net_waits             | 1           |
| Rpl_semi_sync_master_no_times              | 0           |
| Rpl_semi_sync_master_no_tx                 | 0           |
| Rpl_semi_sync_master_status                | ON          |
| Rpl_semi_sync_master_timefunc_failures     | 0           |
| Rpl_semi_sync_master_tx_avg_wait_time      | 510         |
| Rpl_semi_sync_master_tx_wait_time          | 510         |
| Rpl_semi_sync_master_tx_waits              | 1           |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0           |
| Rpl_semi_sync_master_wait_sessions         | 0           |
| Rpl_semi_sync_master_yes_tx                | 1           |
| Rpl_status                                 | AUTH_MASTER |
+--------------------------------------------+-------------+
15 rows in set (0.00 sec)

At this point, the transaction has committed on the master side, and receipt of its events has been acknowledged(Rpl_semi_sync_master_yes_tx) by at least one slave. If we notice the above results, insert is completed without any delay on master. So it means master has received ack from one of the slaves (here the slave is slave2)
Now I am stopping slave threads on Slave2 also to simulate all slaves’ failure.
On  Slave2
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show global status like 'rpl%';
+----------------------------+-------------+
| Variable_name              | Value       |
+----------------------------+-------------+
| Rpl_semi_sync_slave_status | OFF         |
| Rpl_status                 | AUTH_MASTER |
+----------------------------+-------------+
2 rows in set (0.00 sec)
Now i have inserted few rows on Master to test the replication behavior
On Master
mysql> insert into aa values(12,'bar'),(13,'bar1');
Query OK, 2 rows affected (10.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from aa;
+------+------+
| id   | name |
+------+------+
|    1 | asa  |
|    2 | eter |
|    3 | dfr  |
|    4 | eee  |
|    5 | eee  |
|    6 | deee |
|    7 | eefe |
|    8 | test |
|    9 | foo  |
|   10 | ted  |
|   11 | food |
|   12 | bar  |
|   13 | bar1 |
+------+------+
13 rows in set (0.00 sec)

If we notice the above insert,it took 10 sec to complete. This is due to our all slaves are down and there is no ack received from any of the slaves.
So it waited until the timeout(rpl_semi_sync_master_timeout) and fall back to async mode automatically.
mysql> show global variables like 'rpl%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_recovery_rank                  | 0     |
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 10000 |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
5 rows in set (0.00 sec)

mysql> show global status like 'rpl%';
+--------------------------------------------+-------------+
| Variable_name                              | Value       |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients               | 1           |
| Rpl_semi_sync_master_net_avg_wait_time     | 1969        |
| Rpl_semi_sync_master_net_wait_time         | 7878        |
| Rpl_semi_sync_master_net_waits             | 4           |
| Rpl_semi_sync_master_no_times              | 1           |
| Rpl_semi_sync_master_no_tx                 | 1           |
| Rpl_semi_sync_master_status                | OFF         |
| Rpl_semi_sync_master_timefunc_failures     | 0           |
| Rpl_semi_sync_master_tx_avg_wait_time      | 400         |
| Rpl_semi_sync_master_tx_wait_time          | 400         |
| Rpl_semi_sync_master_tx_waits              | 1           |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0           |
| Rpl_semi_sync_master_wait_sessions         | 0           |
| Rpl_semi_sync_master_yes_tx                | 1           |
| Rpl_status                                 | AUTH_MASTER |
+--------------------------------------------+-------------+
15 rows in set (0.00 sec)
Now the subsequent transactions will not wait for acknowledgement as the semi sync master status(Rpl_semi_sync_master_status) is OFF.
The Master will resume the Rpl_semi_sync_master_status to ON if anyone its semi sync enabled slave reconnect (typically recover from failure).

Conclusion:
From this test, it is very obvious that we have to consider Performance Vs Redundancy before implementing semi synchronous replication as the master have to wait until it receives the acknowledgement from one of the semi sync enabled slave.
Considering there is no network and IO latency between master and slave, semi synchronous replication can be used to make sure at least one of the slaves is current with the master and it guarantees that the data exists on master and slave is consistent.

The url is as follows:
http://blogs.oracle.com/mysql/entry/mysql_55_whats_new_in_replication

Masking the database from proddb and removing the definer clause after taking up and changing the DB Names

Masking the database from proddb and removing the definer clause after taking up and changing the DB Names

1.) Massking the database
2.) removing the definer clause
3.) Changing the DBname in the backup file

vi masking.sh

#!/bin/sh
mydate=`date '+%b%d'`
prefix="prd_"
user='kiran'
password='kiran'
port='3306'
# run the masking commands on proddb1
# take the backup, remove definer

mysql -u$user -p$password proddb1 -P$port << proddb1
set foreign_key_checks =0;
select max(id) into @cardid from table1 ;
select max(id)  into @userid from user ;
set @cardid = @cardid + 100 ;
set @userid = @userid + 100 ;
update transaction_log   set Card_ID = Card_ID + @cardid, Second_Card_ID   = Second_Card_ID   + @cardid;                                            
update transaction_log_details  set Card_ID = Card_ID + @cardid;                                       
update transaction_summary set Card_ID = Card_ID + @cardid, xyz =reverse(xyz);                                            
 
### run the update commands you want to update
proddb1
mysql -u$user -p$password proddb2 -P$port << proddb2
set foreign_key_checks =0;
select max(id) into @accountid from table1 ;
set @accountid = @accountid + 200 ;
update table1 set id= id + @accountid ;
update table2 set account = account + @accountid;     
update transaction set masked_number='XXXX';
update transaction_log set masked_number='XXXX';
##run the update command here
proddb2
cat > dumper.pl << "heredoc"
#!/usr/bin/perl
use strict;
use warnings;
use Getopt::Long qw(:config no_ignore_case );
my $replace = undef;
my $delete  = undef;
my $help    = 0;
GetOptions (
   'replace|r=s' => \$replace,
   'delete|d'    => \$delete,
   'help|h'      => \$help,
) or help('unrecognized options');
help() if $help;
if ($delete and $replace) {
    help( 'you must choose EITHER "delete" OR "replace". Not both');
}
$delete = 1 unless $replace;
while (my $line = <STDIN>) {
    if ($delete) {
        $line =~ s{(/\*!\d+ )\s*definer\s*=\s*\S+}{$1}i;
    }
    elsif ($replace) {
        $line =~ s{(/\*!\d+\s+definer\s*=\s*)(\S+)}{$1 $replace}i;
    }
    print $line;
}
sub help {
    my ($msg) = @_;
    if ($msg) {
        print "*** $msg\n";
    }
    print "dump_filter - mysqldump filter \n",
          "(C) Giuseppe Maxia, 2009\n",
          "removes/changes DEFINER clauses from MySQL dumps\n",
          "USAGE: dump_filter [options]\n",
          "    -d|--delete    removes the DEFINER clauses\n",
          "    -r|--replace=s replaces every DEFINER clause with the \n",
          "                   new value provided\n",
          "    -h|--help      This text\n";
    exit(1);
}
heredoc
# backup the DB
mysqldump -u$user -p$password --databases proddb1 proddb2  --routines --single-transaction | perl dumper.pl --delete > all_backup.sql
# "sed" is used to replace the current DB name with "prd_" or any other word.
sed -i '/^CREATE DATABASE\|^USE/{
s/proddb1/'$prefix'proddb1/;
s/proddb2/'$prefix'proddb2/}' all_backup.sql
grep '^CREATE DATABASE' all_backup.sql | head -10

# all_backup.sql file from the current folder can be safely send to development Team

### if we want to replace the definer then we have to replace like this

 perl /home/dumper.pl mailto:--replace='root@%60localhost%60'

Logging only failed queries using proxy

logging only failed queries using proxy

vi failed.query.lua

local log_file = '/home/mysql.log'
local fh = io.open(log_file, "a+")

function read_query(packet)
if string.byte(packet) == proxy.COM_QUERY then
query = string.sub(packet, 2)
proxy.queries:append(1, packet, {resultset_is_needed = true} )
return proxy.PROXY_SEND_QUERY
end
end

function read_query_result (inj)
local res = assert(inj.resultset)

-- if res.query_status == proxy.MYSQLD_PACKET_ERR then

if (res.query_status == proxy.MYSQLD_PACKET_ERR) or (res.warning_count > 0) then

local query = string.sub(inj.query, 2)
local err_code     = res.raw:byte(2) + (res.raw:byte(3) * 256)
local err_sqlstate = res.raw:sub(5, 9)
local err_msg      = res.raw:sub(10)

fh:write(string.sub(inj.query, 2), "\n")
fh:write(res.raw:sub(10), "\n")
fh:flush()

print("Query Received -", query)
print("Query Error code -", err_code)
print("Query Error Sqlstate -", err_sqlstate)
print("Query Error message -", err_msg)
print("Query warnings -", res.warning_count)

end
end

This is the command to run the proxy
sh /kiran/mysql/mysql-proxy-0.8.1-linux-rhel5-x86-64bit/bin/mysql-proxy --plugins=proxy
--proxy-lua-script=/kiran/mysql/mysql-proxy-0.8.1-linux-rhel5-x86-64bit/share/doc/mysql-proxy/failed-query.lua
>> /home/failed_query.log &

For more details the url is as follows:
http://dev.mysql.com/tech-resources/articles/proxy-gettingstarted.html

Steps for installing mysql in different ways

MySQL is a small, fast and highly configurable DBMS. It supports a number of different table fileformats and different engines
MySQL is not ACID compliant except for innodb engines.
To connect to remote mysql database mysql has different engine called as federated engine.
The different tools available in mysql are as follows:
mysqld - MySQL server daemon
safe_mysqld - Server process monitor
mysqlaccess - Tool for creating MySQL users
mysqladmin - Utility for mysql administering
mysqldump - This tool is used to take the backup of mysqldatabases 
mysql - Command line interface to connect to MySQL
mysqlshow - List all MySQL database and the count

For installing mysql on linux machines we can install mysql in three different ways.
Of all the three methods "RPM Installation"  is the easiest method
1.) Binary Installation
2.) source distribution
3.) RPM Installation

A.) steps for intalling Binary Installation.

1.) create mysql user and group.
# groupadd mysql
# useradd -g mysql mysql
# cd /usr

2. download the mysql
http://dev.mysql.com/downloads/mysql/5.1.html

3.) Then unzip the file
#  gunzip  < /usr/mysql-VERSION.tar.gz | tar xvf -
#  ln -s full-path-to-mysql-VERSION  mysql
# cd mysql
# scripts/mysql_install_db
# chown -R root .
# chown -R mysql data
# chgrp -R mysql .
# bin/mysqld_safe --user=mysql &

B.) steps for intalling Source distribution.

1.) create mysql user and group.
# groupadd mysql
# useradd -g mysql mysql

2. download the mysql
http://dev.mysql.com/downloads/mysql/5.1.html

3. unzip the file 
gunzip < mysql-5.0.45.tar.gz | tar -xvf -

4. let say it is under folder (/data01/sources/mysql-5.0.45/)

cd /data01/sources/mysql-5.0.45/
To check options to configure, see
./configure --help
For older versions, enabling federated,
./configure --prefix=/data01/lamp/mysql --enable-thread-safe-client --with-federated-storage-engine
For latest versions, enabling federated is as below
./configure --prefix=/data01/lamp/mysql --enable-thread-safe-client –with-plugins=innobase,myisam,federated
Make
Make install

Follow the below steps to configure mysql as service.
cp /data01/sources/mysql-5.0.45/support-files/my-huge.cnf  /etc/my.cnf
cp /data01/sources/mysql-5.0.45/support-files/mysql.server  /etc/rc.d/init.d/mysql
cd /etc/rc.d/init.d/
chmod 755 mysql
Installing database for the “mysql” User
cd /data01/lamp/mysql
chown -R mysql .
chgrp -R mysql .
bin/mysql_install_db --user=mysql
chown -R root .
chown -R mysql var
bin/mysqld_safe --user=mysql &
To Configure mysql as service, follow the below steps
cd /usr/bin/
ln -s /data01/lamp/mysql/bin/mysql .
ln -s /data01/lamp/mysql/bin/mysql_config .
ln -s /data01/lamp/mysql/bin/mysqldump .
ln -sf  -s /data01/lamp/mysql/bin/mysql .
cd /usr/lib/
ln -sf /data01/lamp/mysql/lib/mysql/lib* .
cd /usr/include/
mkdir mysql
cd /usr/include/mysql/
ln -sf /data01/lamp/mysql/include/mysql/* .

C.) steps for intalling  using "RPM" Installation.

1.) create mysql user and group.
# groupadd mysql
# useradd -g mysql mysql

2. download the mysql
http://dev.mysql.com/downloads/mysql/5.1.html

3.) Let say you have donwloaded the following RPM (mysql-5.5.11 version).
MySQL-client-5.5.11-1.linux2.6.x86_64.rpm
MySQL-devel-5.5.11-1.linux2.6.x86_64.rpm
MySQL-server-5.5.11-1.linux2.6.x86_64.rpm
MySQL-shared-5.5.12-1.linux2.6.x86_64.rpm
perl-DBD-MySQL-4.018-1.wc.x86_64.rpm

4.) Run the above RPM packages you have downloaded.
The command is as follows:

rpm -iUh MySQL-devel-5.5.11-1.linux2.6.x86_64.rpm
rpm -iUh MySQL-shared-5.5.12-1.linux2.6.x86_64.rpm
rpm -iUh MySQL-client-5.5.11-1.linux2.6.x86_64.rpm
rpm -iUh MySQL-server-5.5.11-1.linux2.6.x86_64.rpm
rpm -iUh perl-DBD-MySQL-4.018-1.wc.x86_64.rpm

5.) if we want to check the rpm pacages has installed correctly

rpm -qa | grep -i mysql

MySQL-shared-5.5.12-1.linux2.6
perl-DBD-MySQL-4.018-1.wc
MySQL-devel-5.5.11-1.linux2.6
MySQL-server-5.5.11-1.linux2.6
MySQL-client-5.5.11-1.linux2.6

6.) check wheather the mysql is instance is working or not

/etc/init.d/mysql status

7.) start the mysql instance if not started.
/etc/init.d/mysql start.

8.) check wheather my.cnf exists or not in /etc/my.cnf ( if not exists copy the my.cnf file and fine tune the parameters)
 locate my-huge
/usr/share/doc/MySQL-server-5.5.11/my-huge.cnf
cp /usr/share/doc/MySQL-server-5.5.11/my-huge.cnf /etc/my.cnf

9.) if my.cnf parameters have changed restart the mysql server.
/etc/init.d/mysql restart
/etc/init.d/mysql status

steps for removing definer clause whie taking backup

steps for removing the definer clause in mysqlbackup.

When we take the mysql backup we have some unwanted users in the definer clause.
We want to remove the definer clause in mysql.

1. Take the backup.

mysqldump -u kiran -pkiran --all-databases --single-transaction > mysqldump.sql

vi dumper.pl


#!/usr/bin/perl
use strict;
use warnings;
use Getopt::Long qw(:config no_ignore_case );

my $replace = undef;
my $delete  = undef;
my $help    = 0;

GetOptions (
   'replace|r=s' => \$replace,
   'delete|d'    => \$delete,
   'help|h'      => \$help,
) or help('unrecognized options');

help() if $help;

if ($delete and $replace) {
    help( 'you must choose EITHER "delete" OR "replace". Not both');
}

$delete = 1 unless $replace;

while (my $line = <STDIN>) {
    if ($delete) {
        $line =~ s{(/\*!\d+ )\s*definer\s*=\s*\S+}{$1}i;
    }
    elsif ($replace) {
        $line =~ s{(/\*!\d+\s+definer\s*=\s*)(\S+)}{$1 $replace}i;
    }
    print $line;
}

sub help {
    my ($msg) = @_;
    if ($msg) {
        print "*** $msg\n";
    }
     print "dump_filter - mysqldump filter \n",
          "(C) Test Definer, 2009\n",
          "removes/changes DEFINER clauses from MySQL dumps\n",
          "USAGE: dump_filter [options]\n",
          "    -d|--delete    removes the DEFINER clauses\n",
          "    -r|--replace=s replaces every DEFINER clause with the \n",
          "                   new value provided\n",
          "    -h|--help      This text\n";
    exit(1);
}

3.) Remove the definer clause

cat mysqldump.sql | perl dumper.pl --delete > mysqldump-ver01.sql