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