Query to find the count based on 15 minutes on datetime column

mysql> show create table test3 \G
*************************** 1. row ***************************
       Table: test3
Create Table: CREATE TABLE `test3` (
  `eno` int(11) NOT NULL AUTO_INCREMENT,
  `created_date` datetime DEFAULT NULL,
  `errno` int(11) DEFAULT NULL,
  PRIMARY KEY (`eno`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)


+-----+---------------------+-------+
| eno | created_date        | errno |
+-----+---------------------+-------+
|   1 | 2011-12-19 11:00:28 |   100 |
|   2 | 2011-12-19 11:14:31 |   101 |
|   3 | 2011-12-19 11:14:38 |   103 |
|   4 | 2011-12-19 11:00:28 |   100 |
|   5 | 2011-12-19 12:00:59 |   100 |
|   6 | 2011-12-19 11:15:06 |   101 |
|   7 | 2011-12-19 11:15:33 |   100 |
|   8 | 2011-12-19 11:15:34 |   100 |
|   9 | 2011-12-19 11:30:28 |   100 |
|  10 | 2011-12-19 11:30:28 |   100 |
+-----+---------------------+-------+
10 rows in set (0.00 sec)

output Method 1:


mysql> SELECT
    -> DATE(created_date) AS Date,
    -> CONCAT(
    -> HOUR(created_date), ':',
    -> CASE
    -> WHEN MINUTE(created_date) BETWEEN 0 AND 14 THEN '00'
    -> WHEN MINUTE(created_date) BETWEEN 15 AND 29 THEN '15'
    -> WHEN MINUTE(created_date) BETWEEN 30 AND 44 THEN '30'
    -> WHEN MINUTE(created_date) BETWEEN 45 AND 59 THEN '45'
    -> END
    -> )AS Per15min, COUNT(*) as 'Error Counter'
    -> FROM test3
    -> where errno=100
    -> GROUP BY Per15min;
+------------+----------+---------------+
| Date       | Per15min | Error Counter |
+------------+----------+---------------+
| 2011-12-19 | 11:00    |             2 |
| 2011-12-19 | 11:15    |             2 |
| 2011-12-19 | 11:30    |             2 |
| 2011-12-19 | 12:00    |             1 |
+------------+----------+---------------+
4 rows in set (0.00 sec)




method2:

mysql> select x.created_date,x.hdate,count(errno),x.minute1,if(x.minute1=1,
    -> concat(x.hdate, "-", x.hdate, ":15") ,
    -> if(x.minute1=2,concat(x.hdate,":15", "-" , x.hdate,":30"),
    -> if(x.minute1=3,concat(x.hdate,":30", "-", x.hdate,":45"),
    -> if(x.minute1=4,concat(x.hdate,":45", "-", x.hdate + 1),"xx")))) as xxx  from
    -> (
    ->  select date(created_date) as created_date ,hour(created_date) hdate ,errno ,case when minute(created_date) < 15 then 1
    ->  when minute(created_date) < 30 then 2 when minute(created_date) < 45 then 3 when minute(created_date) < 59 then 4 end as minute1
    ->  from test3   where errno =100
    ->  ) x
    ->  group by x.created_date,x.hdate,x.minute1;
+--------------+-------+--------------+---------+-------------+
| created_date | hdate | count(errno) | minute1 | xxx         |
+--------------+-------+--------------+---------+-------------+
| 2011-12-19   |    11 |            2 |       1 | 11-11:15    |
| 2011-12-19   |    11 |            2 |       2 | 11:15-11:30 |
| 2011-12-19   |    11 |            2 |       3 | 11:30-11:45 |
| 2011-12-19   |    12 |            1 |       1 | 12-12:15    |
+--------------+-------+--------------+---------+-------------+
4 rows in set (0.00 sec)

SELECT
DATE(created_date) AS Date,
CONCAT(
HOUR(created_date), ':',
CASE
WHEN MINUTE(created_date) BETWEEN 0 AND 14 THEN '00'
WHEN MINUTE(created_date) BETWEEN 15 AND 29 THEN '16'
WHEN MINUTE(created_date) BETWEEN 30 AND 44 THEN '31'
WHEN MINUTE(created_date) BETWEEN 45 AND 59 THEN '46'
END,
' TO ',
HOUR(created_date), ':',
CASE
WHEN MINUTE(created_date) BETWEEN 0 AND 14 THEN '15'
WHEN MINUTE(created_date) BETWEEN 15 AND 29 THEN '30'
WHEN MINUTE(created_date) BETWEEN 30 AND 44 THEN '45'
WHEN MINUTE(created_date) BETWEEN 45 AND 59 THEN '59'
END
) AS Per15min, COUNT(*) as 'Error Counter'
FROM test3
GROUP BY Per15min;
--- OUTPUT ---
+------------+----------------+---------------+
| Date | Per15min | Error Counter |
+------------+----------------+---------------+
| 2011-12-19 | 11:16 TO 11:30 | 1 |
| 2011-12-19 | 11:31 TO 11:45 | 1 |
| 2011-12-19 | 11:46 TO 11:59 | 3 |
| 2011-12-19 | 12:00 TO 12:15 | 1 |
| 2011-12-19 | 12:16 TO 12:30 | 2 |
| 2011-12-19 | 12:31 TO 12:45 | 1 |
| 2011-12-19 | 12:46 TO 12:59 | 2 |
| 2011-12-19 | 13:00 TO 13:15 | 1 |
+------------+----------------+---------------+
The easies way of writing the query is as follows:

#####15 minutes query #######
SELECT date(creation_date) as day1,HOUR(creation_date) AS hour,
CONCAT(LPAD(FLOOR(MINUTE(creation_date)/15)*15, 2, '0'), '-',LPAD((FLOOR(MINUTE(creation_date)/15)+1)*15,2,'0')) AS range1,
COUNT(*) AS 'ErrorCounter'
FROM   transaction
WHERE DATE(creation_date) = '2011-06-26' 
GROUP BY day1, hour,range1
ORDER BY day1, hour,range1 ;

### 5 minutes query ####

SELECT date(creation_date) as day1,HOUR(creation_date) AS hour,
CONCAT(LPAD(FLOOR(MINUTE(creation_date)/5)*5, 2, '0'), '-',LPAD((FLOOR(MINUTE(creation_date)/5)+1)*5,2,'0')) AS range1,
COUNT(*) AS 'ErrorCounter'
FROM   transaction
WHERE DATE(creation_date) = '2011-06-26'
GROUP BY day1, hour,range1
ORDER BY day1, hour,range1 ;

steps for configurations SSL in mysql

1.) Step one SSL should be enabled if it is not enabled then we need to enable "have_openssl" and "have_ssl"
mysql> show variables like '%ssl%';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
| ssl_ca        |          |
| ssl_capath    |          |
| ssl_cert      |          |
| ssl_cipher    |          |
| ssl_key       |          |
+---------------+----------+
7 rows in set (0.00 sec)

2.) in my.cnf add ssl below [mysqld] as follow and restart the server.
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
#### ssl configurations
ssl

3.) after restart the server check the ssl is enabled or not
mysql> SHOW variables LIKE '%ssl%';
+---------------+---------------------------+
| Variable_name | Value                     |
+---------------+---------------------------+
| have_openssl  | YES                       |
| have_ssl      | YES                       |
| ssl_ca        |
| ssl_capath    |                           |
| ssl_cert      |                           |
| ssl_cipher    |
| ssl_key       |                           |
+---------------+---------------------------+
7 rows in set (0.00 sec)

4.) creating the certificates.
 for creating the certificates we first need to install openssl
 check wheather openssl is installed or not
 openssl
OpenSSL>
5. creating the certficates.
shell> mkdir -p /etc/mysql/cert 
shell> cd /etc/mysql/cert
Create CA certificate:
shell> openssl genrsa 2048 > ca-key.pem
shell> openssl req -new -x509 -nodes -days 9999 -key ca-key.pem > ca-cert.pem
Create server certificate:
shell> openssl req -newkey rsa:2048 -days 9999 -nodes -keyout server-key.pem > server-req.pem
shell> openssl x509 -req -in server-req.pem -days 9999 -CA ca-cert.pem -CAkey ca-key.pem –set_serial 01 > server-cert.pem
Create client certificate:
shell> openssl req -newkey rsa:2048 -days 9999 -nodes -keyout client-key.pem > client-req.pem
shell> openssl x509 -req -in client-req.pem -days 9999 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem
verify the certificates
openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem
Now we have following  certificates/keys got created.
ca-cert.pem
ca-key.pem
client-cert.pem
client-key.pem
client-req.pem
server-cert.pem
server-key.pem
server-req.pem

6.) now we need to add certificates in my.cnf as follows:
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
#### ssl configurations
ssl
ssl-ca = /etc/cert/ca-cert.pem
ssl_capath = /etc/cert
ssl-cert = /etc/cert/server-cert.pem
ssl-key = /etc/cert/server-key.pem
[client]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
ssl-ca    =   /etc/cert/ca-cert.pem
ssl-cert  =  /etc/cert/client-cert.pem
ssl-key   = /etc/cert/client-key.pem
7.) restart the mysql
shell> /etc/init/mysql restart
 run the command as follows:
mysql> SHOW variables LIKE '%ssl%';
+---------------+---------------------------+
| Variable_name | Value                     |
+---------------+---------------------------+
| have_openssl  | YES                       |
| have_ssl      | YES                       |
| ssl_ca        | /etc/cert/ca-cert.pem     |
| ssl_capath    | /etc/cert                 |
| ssl_cert      | /etc/cert/server-cert.pem |
| ssl_cipher    |                           |
| ssl_key       | /etc/cert/server-key.pem  |
+---------------+---------------------------+
7 rows in set (0.00 sec)

9.) given the grant privileges uing ssl.
GRANT ALL ON *.* TO 'test123'@'localhost' IDENTIFIED BY 'test123' REQUIRE SSL;

10.) suppose we want to connect to another server (from system2).
copy the ca-cert.pem,client-cert.pem,client-key.pem to sytem2
11.) go  to system2 type the following command
mysql -u test123 -h system1 --ssl-ca=/etc/cert1/ca-cert.pem --ssl-cert=/etc/cert1/client-cert.pem --ssl-key=/etc//cert1/client-key.pem -p
mysql>
In this way the ssl configuration is established.
for vericication the command is as follows:

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.5.13, for Linux (x86_64) using readline 5.1
Connection id:          25
Current database:
Current user:           root@localhost
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.5.13 MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 23 hours 50 min 36 sec
Threads: 2  Questions: 10559  Slow queries: 0  Opens: 1261  Flush tables: 1  Open tables: 42  Queries per second avg: 0.123
If we want to set up replication using ssl keys.
The commands are as follows:

mysql> GRANT REPLICATION SLAVE ON *.* TO "repl"@"10.%" IDENTIFIED BY 'XXXXXX'  REQUIRE SSL;
mysql> FLUSH PRIVILEGES;
the replication command are as follows:

CHANGE MASTER TO
MASTER_HOST=,
MASTER_USER='repl',
MASTER_PASSWORD='',
MASTER_LOG_FILE='',
MASTER_LOG_POS= ,
MASTER_SSL=1,
MASTER_SSL_CA = '/etc/mysql/cert/ca-cert.pem',
MASTER_SSL_CERT = '/etc/mysql/cert/client-cert.pem',
MASTER_SSL_KEY = '/etc/mysql/cert/client-key.pem';

For more details the url are as follows:
http://dev.mysql.com/doc/refman/5.5/en/secure-using-ssl.html

META-DATA queries in MYSQL

1.) display all the table name and tableschemas
select TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,ENGINE,TABLE_ROWS from information_Schema.tables;

2) displaying the table name and the column name
select TABLE_SCHEMA,TABLE_NAME, group_concat(COLUMN_NAME) colname,count(COLUMN_NAME) cnt  from information_schema.columns
where table_schema='test' group by TABLE_SCHEMA,TABLE_NAME ;
3) dislaying all the views and their structure .
select TABLE_SCHEMA,TABLE_NAME from information_schema.views where table_Schema like 'test'
select TABLE_SCHEMA,TABLE_NAME,VIEW_DEFINITION from information_schema.views where table_Schema like 'test' \G
4. display all the triggers and their structure
select TRIGGER_SCHEMA,TRIGGER_NAME,EVENT_MANIPULATION,EVENT_OBJECT_TABLE,ACTION_Timing,ACTION_STATEMENT from information_Schema.triggers \G
 select TRIGGER_SCHEMA,TRIGGER_NAME,EVENT_MANIPULATION,EVENT_OBJECT_TABLE,ACTION_Timing from information_Schema.triggers;
5. indexes
select table_schema,table_name,index_name,index_type,column_name from information_schema.STATISTICS where table_schema='test';
select table_schema,table_name,COLUMN_NAME,index_name,SEQ_IN_INDEX,NON_UNIQUE from information_schema.STATISTICS where table_schema='test';
select table_schema,table_name,COLUMN_NAME,group_concat(index_name),count(index_name) from information_schema.STATISTICS where table_schema='test' group by table_schema,table_name,COLUMN_NAME;
6.) routines
select ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE,ROUTINE_BODY from information_Schema.routines;
select ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE,ROUTINE_BODY,Routine_definition from information_Schema.routines;
7.) Primary key and their foreign  keys
SELECT CONCAT( table_name, '.', column_name, ' -> ', referenced_table_name, '.', referenced_column_name ) AS list_of_fks  FROM INFORMATION_SCHEMA.key_column_usage  WHERE referenced_table_schema = 'test' AND referenced_table_name IS NOT NULL  ORDER BY table_name, column_name;

8) count all the table and column count and record count
mysqlshow -u kiran -pkiran@123  test --count
mysqlshow -u kiran -pkiran@123  --count

Getting errors while revoking table level privileges

mysql> show grants for 'kiran'@'localhost';
>+-------------------------------------------------------------------------------------------------------------------------------------------------------+
>| Grants for kiran@localhost |
>+-------------------------------------------------------------------------------------------------------------------------------------------------------+
 GRANT USAGE ON *.* TO 'kiran'@'localhost' IDENTIFIED BY PASSWORD '*C57074CED5D2747BA86351977028B255F1AD6914' |
 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `Database1`.* TO 'kiran'@'localhost' |
 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `Database2`.* TO 'kiran'@'localhost' |


mysql> REVOKE UPDATE ON Database1.transaction FROM 'kiran'@'localhost';
ERROR 1147 (42000): There is no such grant defined for user 'kiran' on host 'localhost' on table 'transaction'


### Giving privileges on each table based on schema

SELECT CONCAT( 'GRANT SELECT, INSERT, XXX ON ', TABLE_SCHEMA, '.', TABLE_NAME, " ON `Database1`.* TO 'kiran'@'localhost'" ) FROM TABLES WHERE TABLE_SCHEMA='Database1';

## giving privileges on all schema and all tables

mysql -Bse"select CONCAT(\"GRANT SELECT, INSERT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER, UPDATE ON \",TABLE_SCHEMA,\".\",TABLE_NAME, \" TO 'kiran'@'localhost' identified by 'kiran';\") from information_schema.tables where TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')"

## giving privileges on all schemas ( schema level privileges)
mysql -Bse"select CONCAT(\"GRANT SELECT, INSERT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER, UPDATE ON \",SCHEMA_NAME,\".*\" \" TO 'kiran'@'localhost' identified by 'kiran';\") from information_schema.SCHEMATA where SCHEMA_NAME NOT IN ('mysql', 'information_schema', 'performance_schema')"


For more details about the privileges the url is as follows:

http://dev.mysql.com/doc/refman/5.5/en/grant.html#grant-database-privileges
http://dev.mysql.com/doc/refman/5.5/en/grant.html#grant-table-privileges

finding difference in data between two tables

mysql> show create table emp \G
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `sal` int(11) DEFAULT NULL,
  `job` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> show create table emp1 \G
*************************** 1. row ***************************
       Table: emp1
Create Table: CREATE TABLE `emp1` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(100) DEFAULT NULL,
  `sal` int(11) DEFAULT NULL,
  `job` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from emp;
+----+------+------+------+
| id | name | sal  | job  |
+----+------+------+------+
|  1 | a    | 1000 | a    |
|  2 | b    | 5000 | b    |
|  3 | c    | 6000 | c    |
|  4 | d    | 2000 | d    |
+----+------+------+------+
4 rows in set (0.00 sec)
mysql> select * from emp1;
+----+------+------+------+
| id | name | sal  | job  |
+----+------+------+------+
|  1 | a    | 1000 | a    |
|  2 | b    | 2000 | b    |
|  3 | c    | 6000 | c    |
|  4 | d    | 2000 | c    |
+----+------+------+------+
4 rows in set (0.00 sec)

### first method
### The final query output is as follows:

mysql> SELECT emp.*, emp1.* FROM emp NATURAL LEFT JOIN emp1 AS x JOIN emp1 ON emp.id = emp1.id WHERE x.id IS NULL;
+----+------+------+------+----+------+------+------+
| id | name | sal  | job  | id | name | sal  | job  |
+----+------+------+------+----+------+------+------+
|  2 | b    | 5000 | b    |  2 | b    | 2000 | b    |
|  4 | d    | 2000 | d    |  4 | d    | 2000 | c    |
+----+------+------+------+----+------+------+------+
2 rows in set (0.00 sec)

### second method
mysql> SELECT * FROM emp AS a INNER JOIN emp1 AS b ON a.id = b.id AND (a.name, a.sal, a.job) != (b.name, b.sal, b.job);
+----+------+------+------+----+------+------+------+
| id | name | sal  | job  | id | name | sal  | job  |
+----+------+------+------+----+------+------+------+
|  2 | b    | 5000 | b    |  2 | b    | 2000 | b    |
|  4 | d    | 2000 | d    |  4 | d    | 2000 | c    |
+----+------+------+------+----+------+------+------+
2 rows in set (0.00 sec)

### third method


DELIMITER $$
DROP PROCEDURE IF EXISTS test.comparetables ;
CREATE PROCEDURE comparetables (schema1 varchar(100), table1 varchar(100), schema2 varchar(100), table2 varchar(100), excludecol1 varchar(100) )
begin
DECLARE q1 varchar(4000);
DECLARE q2 varchar(4000);
DECLARE q3 varchar(4000);
select group_concat('a.', column_name) into q1 from information_Schema.columns where table_name = table1 and table_schema = schema1  and column_name not like excludecol1 ;
select group_concat('b.', column_name) into q2 from information_Schema.columns where table_name = table2 and table_schema =schema2  and column_name not like excludecol1;
set q3 =concat('select * from ', schema1 , '.', table1,  ' AS  a  inner join ' ,schema2, '.',  table2, ' as b on a.', excludecol1, '=b.' , excludecol1, ' and (' , q1 , ') != (' , q2 , ')' );
select q1;
select q2;
select q3 ;
set @q4 = q3 ;
#prepare stmt from @q4;
#execute stmt;
#drop prepare stmt;
end ;
$$
delimiter ;

### execute the stored proc
call comparetables('test','emp','test','emp3', 'id');

Multi-Master single slave for mysql

given below are the steps for setting up the multi master single slave setup.

steps for having multi-master single slave setup

1. generally one mysql server can have only one master but in order to achieve multi-master single slave we can have some work around methods

1. we can have multiple instances in mysql using mysqld_multi command for details give below are the url
http://dev.mysql.com/doc/refman/4.1/en/mysqld-multi.html

2. we have to configure multiple instances say for eg ( mysql running on different ports 3306 and 3307).

3. for example 3306 is the slave of server-1 and 3307 is the slave of server-2

4. since 3306 and 3307 are lying on the same server

5. we can create a federated tables on 3306 servers for 3307 server.

6. given below are the scripts for generating federated tables for each databases.
7. store the output in federated.sql files
8. execute the federated.sql file on 3306 server


####to create the federated tables the code is as follows #############

#!/bin/sh

USER=root
DATABASE_NAME=test

# The default connection string to use
CONNECTION="MYSQL://127.0.0.1:3306/test2"

# Get the list of table names
TABLE_LIST=`mysql -u$USER $DATABASE_NAME -e 'SHOW TABLES\G' | sed -n "/^Tables_in_${DATABASE_NAME}:/s/^.*: //p"`
#echo $TABLE_LIST

# For each table in the list
for TABLE in $TABLE_LIST; do
                # Get the SHOW CREATE TABLE
                mysqldump -u$USER --no-data $DATABASE_NAME $TABLE \
                | sed "s!^) ENGINE=.*!) ENGINE=FEDERATED CONNECTION='${CONNECTION}/${TABLE}';!"
done


########################The sample my.cnf file is as follows ######################

[mysqld]

datadir=/var/lib/mysql/
socket=/var/lib/mysql/mysql.sock
user=mysql

# Server ID should always be there preferably IP address
server-id=111

log-slow-queries=/var/log/mysql/slow-log
long_query_time = 0.5
log-queries-not-using-indexes

# 50-80% of RAM
innodb_buffer_pool_size=4G

# innodb_data_file_path=ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_file_size=100M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit=2
#innodb_lock_wait_timeout = 50
innodb_additional_mem_pool_size=16M
innodb_flush_method=O_DIRECT

########## Multiple mysql instances #########

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user       = multi_admin
password   = multipass

[mysqld3307]
socket     = /tmp/mysql.sock3307
port       = 3307
pid-file   = /var/lib/mysql3307/hostname.pid3307
datadir    = /var/lib/mysql3307

[mysqld3308]
socket     = /tmp/mysql.sock3308
port       = 3308
pid-file   = /var/lib/mysql3308/hostname.pid3308
datadir    = /var/lib/mysql3308

[mysqld_safe]
# default 1024
open_files_limit=4096
log-error=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid

################################To start the instance the command is as follows###########

mysqld_multi start 3307
mysqld_multi start 3307

##########################################################################################

For more details the url is as follows:

 http://dev.mysql.com/doc/refman/4.1/en/mysqld-multi.html
http://dev.mysql.com/doc/refman/5.0/en/federated-use.html
http://dev.mysql.com/doc/refman/5.0/en/federated-limitations.html

Important commands used for analying general logs

grep 'Connect' /var/log/mysql/general.log |  awk -F'Connect' '{print $2}' | sort | uniq -c | sort -n
grep 'Connect' /var/log/mysql/general.log | grep '`date "+\%y\%m\%d\ \%H\%M"`'
grep 'Connect' /var/log/mysql/general.log | grep '`date "+\%y\%m\%d\ \%H"`'
grep 'Connect' /var/log/mysql/general.log | grep "`date '+%y%m%d %H%M'`" 
grep 'Connect' /var/log/mysql/general.log | grep "`date '+%y%m%d %H%M'`" | awk -F'Connect' '{print $2}' | sort | uniq -c | sort -n 
tail -100000 /var/lib/mysql/general.log | grep 'Connect' | grep "`date '+%y%m%d %H%M'`" | awk -F'Connect' '{print $2}' | sort | uniq -c | sort -n
tail -100000 /var/log/mysql/general.log | grep 'Connect' | grep "`date '+%y%m%d %H%M'`" | awk -F'Connect' '{print $2}' | sort | uniq -c | sort -n
tail -100000 /var/log/mysql/general.log | grep 'Connect' | grep "`date '+%y%m%d %H'`" | awk -F'Connect' '{print $2}' | sort | uniq -c | sort -n
grep 'Connect' /mysql-data/logs/general.log | awk '{print $5}' | sort | uniq -c | sort -n
grep 'Connect' /var/log/mysql/general.log | grep `date "+%y%m%d"` 
grep 'Connect' /var/log/mysql/general.log | grep `date '+%y%m%d'` 
grep 'Connect' /var/log/mysql/general.log | awk '{print $5}' | sort | uniq -c | sort -n

his command is used to tell the each user connected how many times

rep 'Connect' /var/log/mysql/general.log | grep `date "+%y%m%d"`
rep 'Connect' /var/log/mysql/general.log | grep "`date '+%y%m%d %H'` --CORRECT
rep 'Connect' /var/log/mysql/general.log | grep "`date '+%y%m%d %H'`" | awk -F'Connect' '{print $2}' | sort | uniq -c | sort -n

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