1. Setup 2 MySQL, one to be a master (with binlogs), the other to be a slave;
2. On the slave: "STOP SLAVE; DROP DATABASE IF EXISTS test_jfg; RESET SLAVE ALL;";
3. On the master: "DROP DATABASE IF EXISTS test_jfg; RESET MASTER;";
4. On the master: "CREATE DATABASE test_jfg;";
5. On the slave: "CHANGE MASTER TO MASTER_HOST='<master_hostname>', MASTER_USER='<user>', MASTER_PASSWORD='<password>'; START SLAVE IO_THREAD;";
6. On the slave: "pkill -9 mysqld";
7. On the slave: start mysqld with relay_log_recovery = 1.


1. Setup 2 MySQL, one to be a master (with binlogs), the other to be a slave;

/ Initialize

scripts/mysql_install_db --basedir=/data/ushastry/server/mysql-advanced-5.6.20 --datadir=/tmp/master
scripts/mysql_install_db --basedir=/data/ushastry/server/mysql-advanced-5.6.20 --datadir=/tmp/slave

/ Bring up

bin/mysqld --no-defaults --basedir=/data/ushastry/server/mysql-advanced-5.6.20 --datadir=/tmp/master --log_bin=master-bin --port=3306 --socket=/tmp/master.sock --server_id=1 --pid-file=/tmp/master/mysqld.pid --user=root &
bin/mysqld --no-defaults --basedir=/data/ushastry/server/mysql-advanced-5.6.20 --datadir=/tmp/slave --relay-log=slave-bin --port=3307 --socket=/tmp/slave.sock --server_id=2 --pid-file=/tmp/slave/mysqld.pid --user=root &

/ Setup replication

	// master
	CREATE USER 'repl'@'localhost' IDENTIFIED BY 'slavepass';
	GRANT REPLICATION SLAVE ON *.* TO 'repl'@'localhost';

	CREATE USER 'repl'@'127.0.0.1' IDENTIFIED BY 'slavepass';
	GRANT REPLICATION SLAVE ON *.* TO 'repl'@'127.0.0.1';
	FLUSH PRIVILEGES;

	mysql> show master status;
	+-------------------+----------+--------------+------------------+-------------------+
	| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
	+-------------------+----------+--------------+------------------+-------------------+
	| master-bin.000001 |      813 |              |                  |                   |
	+-------------------+----------+--------------+------------------+-------------------+
	1 row in set (0.00 sec)

	
	// slave - captured ls from slave's dir BEFORE/AFTER CHANGE...

	[root@cluster-repo mysql-advanced-5.6.20]# ls -l /tmp/slave
	total 110612
	-rw-rw---- 1 root root       56 Sep 14 02:41 auto.cnf
	-rw-rw---- 1 root root 12582912 Sep 14 02:41 ibdata1
	-rw-rw---- 1 root root 50331648 Sep 14 02:41 ib_logfile0
	-rw-rw---- 1 root root 50331648 Sep 14 02:36 ib_logfile1
	drwx------ 2 root root     4096 Sep 14 02:37 mysql
	-rw-rw---- 1 root root        6 Sep 14 02:41 mysqld.pid
	drwx------ 2 root root     4096 Sep 14 02:37 performance_schema
	drwx------ 2 root root     4096 Sep 14 02:36 test

	CHANGE MASTER TO
	     MASTER_HOST='127.0.0.1',
	     MASTER_PORT=3306,
	     MASTER_USER='repl',
	     MASTER_PASSWORD='slavepass',
	     MASTER_LOG_FILE='master-bin.000001',
	     MASTER_LOG_POS=813;


		[root@cluster-repo mysql-advanced-5.6.20]# ls -l /tmp/slave
		total 110628
		-rw-rw---- 1 root root       56 Sep 14 02:41 auto.cnf
		-rw-rw---- 1 root root 12582912 Sep 14 02:41 ibdata1
		-rw-rw---- 1 root root 50331648 Sep 14 02:41 ib_logfile0
		-rw-rw---- 1 root root 50331648 Sep 14 02:36 ib_logfile1
		-rw-rw---- 1 root root       90 Sep 14 02:49 master.info
		drwx------ 2 root root     4096 Sep 14 02:37 mysql
		-rw-rw---- 1 root root        6 Sep 14 02:41 mysqld.pid
		drwx------ 2 root root     4096 Sep 14 02:37 performance_schema
		-rw-rw---- 1 root root       51 Sep 14 02:49 relay-log.info
		-rw-rw---- 1 root root      120 Sep 14 02:49 slave-bin.000001
		-rw-rw---- 1 root root       19 Sep 14 02:49 slave-bin.index
		drwx------ 2 root root     4096 Sep 14 02:36 test

        START SLAVE;

		[root@cluster-repo mysql-advanced-5.6.20]# ls -l /tmp/slave
		total 110632
		-rw-rw---- 1 root root       56 Sep 14 02:41 auto.cnf
		-rw-rw---- 1 root root 12582912 Sep 14 02:41 ibdata1
		-rw-rw---- 1 root root 50331648 Sep 14 02:41 ib_logfile0
		-rw-rw---- 1 root root 50331648 Sep 14 02:36 ib_logfile1
		-rw-rw---- 1 root root      126 Sep 14 02:50 master.info
		drwx------ 2 root root     4096 Sep 14 02:37 mysql
		-rw-rw---- 1 root root        6 Sep 14 02:41 mysqld.pid
		drwx------ 2 root root     4096 Sep 14 02:37 performance_schema
		-rw-rw---- 1 root root       53 Sep 14 02:50 relay-log.info
		-rw-rw---- 1 root root      167 Sep 14 02:50 slave-bin.000001
		-rw-rw---- 1 root root      284 Sep 14 02:50 slave-bin.000002
		-rw-rw---- 1 root root       38 Sep 14 02:50 slave-bin.index
		drwx------ 2 root root     4096 Sep 14 02:36 test



2. On the slave: "STOP SLAVE; DROP DATABASE IF EXISTS test_jfg; RESET SLAVE ALL;";


		[root@cluster-repo mysql-advanced-5.6.20]# ls -l /tmp/slave
		total 110620
		-rw-rw---- 1 root root       56 Sep 14 02:41 auto.cnf
		-rw-rw---- 1 root root 12582912 Sep 14 02:41 ibdata1
		-rw-rw---- 1 root root 50331648 Sep 14 02:41 ib_logfile0
		-rw-rw---- 1 root root 50331648 Sep 14 02:36 ib_logfile1
		drwx------ 2 root root     4096 Sep 14 02:37 mysql
		-rw-rw---- 1 root root        6 Sep 14 02:41 mysqld.pid
		drwx------ 2 root root     4096 Sep 14 02:37 performance_schema
		-rw-rw---- 1 root root      143 Sep 14 03:03 slave-bin.000001
		-rw-rw---- 1 root root       19 Sep 14 03:03 slave-bin.index
		drwx------ 2 root root     4096 Sep 14 02:36 test
		[root@cluster-repo mysql-advanced-5.6.20]#

3. On the master: "DROP DATABASE IF EXISTS test_jfg; RESET MASTER;";

	mysql> show master status;
	+-------------------+----------+--------------+------------------+-------------------+
	| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
	+-------------------+----------+--------------+------------------+-------------------+
	| master-bin.000001 |      120 |              |                  |                   |
	+-------------------+----------+--------------+------------------+-------------------+
	1 row in set (0.00 sec)

4. On the master: "CREATE DATABASE test_jfg;";

5. On the slave: "CHANGE MASTER TO MASTER_HOST='<master_hostname>', MASTER_USER='<user>', MASTER_PASSWORD='<password>'; START SLAVE IO_THREAD;";

		CHANGE MASTER TO
		     MASTER_HOST='127.0.0.1',
		     MASTER_PORT=3306,
		     MASTER_USER='repl',
		     MASTER_PASSWORD='slavepass';
		     
		     [root@cluster-repo mysql-advanced-5.6.20]# ls -l /tmp/slave
		     total 110628
		     -rw-rw---- 1 root root       56 Sep 14 02:41 auto.cnf
		     -rw-rw---- 1 root root 12582912 Sep 14 02:41 ibdata1
		     -rw-rw---- 1 root root 50331648 Sep 14 02:41 ib_logfile0
		     -rw-rw---- 1 root root 50331648 Sep 14 02:36 ib_logfile1
		     -rw-rw---- 1 root root       85 Sep 14 03:09 master.info
		     drwx------ 2 root root     4096 Sep 14 02:37 mysql
		     -rw-rw---- 1 root root        6 Sep 14 02:41 mysqld.pid
		     drwx------ 2 root root     4096 Sep 14 02:37 performance_schema
		     -rw-rw---- 1 root root       32 Sep 14 03:09 relay-log.info
		     -rw-rw---- 1 root root      120 Sep 14 03:09 slave-bin.000001
		     -rw-rw---- 1 root root       19 Sep 14 03:09 slave-bin.index
		     drwx------ 2 root root     4096 Sep 14 02:36 test


		     START SLAVE IO_THREAD;
		
			[root@cluster-repo mysql-advanced-5.6.20]# ls -l /tmp/slave
			total 110632
			-rw-rw---- 1 root root       56 Sep 14 02:41 auto.cnf
			-rw-rw---- 1 root root 12582912 Sep 14 02:41 ibdata1
			-rw-rw---- 1 root root 50331648 Sep 14 02:41 ib_logfile0
			-rw-rw---- 1 root root 50331648 Sep 14 02:36 ib_logfile1
			-rw-rw---- 1 root root      126 Sep 14 03:10 master.info
			drwx------ 2 root root     4096 Sep 14 02:37 mysql
			-rw-rw---- 1 root root        6 Sep 14 02:41 mysqld.pid
			drwx------ 2 root root     4096 Sep 14 02:37 performance_schema
			-rw-rw---- 1 root root       32 Sep 14 03:09 relay-log.info
			-rw-rw---- 1 root root      167 Sep 14 03:10 slave-bin.000001
			-rw-rw---- 1 root root      390 Sep 14 03:10 slave-bin.000002
			-rw-rw---- 1 root root       38 Sep 14 03:10 slave-bin.index
			drwx------ 2 root root     4096 Sep 14 02:36 test



			[root@cluster-repo mysql-advanced-5.6.20]# bin/mysqlbinlog --base64-output=decode-rows /tmp/slave/slave-bin.000002
			..
			..
			CREATE DATABASE test_jfg
			/*!*/;
			DELIMITER ;
			# End of log file
			ROLLBACK /* added by mysqlbinlog */;
			/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
			/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

			^^ slave-bin.000002 contains CREATE DATABASE test
			
6. On the slave: "pkill -9 mysqld";



7. On the slave: start mysqld with relay_log_recovery = 1.

 bin/mysqld --no-defaults --basedir=/data/ushastry/server/mysql-advanced-5.6.20 --datadir=/tmp/slave --relay-log=slave-bin --port=3307 --socket=/tmp/slave.sock --server_id=2 --pid-file=/tmp/slave/mysqld.pid --user=root --relay_log_recovery=1 --skip_slave_start=1 &

			mysql> show slave status\G
			*************************** 1. row ***************************

				       Relay_Log_File: slave-bin.000001
					Relay_Log_Pos: 4
				Relay_Master_Log_File:
				  Exec_Master_Log_Pos: 0

			[root@cluster-repo mysql-advanced-5.6.20]# ls -l /tmp/slave
			total 110636
			-rw-rw---- 1 root root       56 Sep 14 02:41 auto.cnf
			-rw-rw---- 1 root root 12582912 Sep 14 03:15 ibdata1
			-rw-rw---- 1 root root 50331648 Sep 14 03:15 ib_logfile0
			-rw-rw---- 1 root root 50331648 Sep 14 02:36 ib_logfile1
			-rw-rw---- 1 root root      126 Sep 14 03:15 master.info
			drwx------ 2 root root     4096 Sep 14 02:37 mysql
			-rw-rw---- 1 root root        6 Sep 14 03:15 mysqld.pid
			drwx------ 2 root root     4096 Sep 14 02:37 performance_schema
			-rw-rw---- 1 root root       32 Sep 14 03:15 relay-log.info
			-rw-rw---- 1 root root      167 Sep 14 03:10 slave-bin.000001
			-rw-rw---- 1 root root      390 Sep 14 03:10 slave-bin.000002 <---------- CREATE DATABASE
			-rw-rw---- 1 root root      120 Sep 14 03:15 slave-bin.000003
			-rw-rw---- 1 root root       57 Sep 14 03:15 slave-bin.index
			drwx------ 2 root root     4096 Sep 14 02:36 test

			START SLAVE IO_THREAD;

			[root@cluster-repo mysql-advanced-5.6.20]# ls -l /tmp/slave
			total 110640
			-rw-rw---- 1 root root       56 Sep 14 02:41 auto.cnf
			-rw-rw---- 1 root root 12582912 Sep 14 03:15 ibdata1
			-rw-rw---- 1 root root 50331648 Sep 14 03:15 ib_logfile0
			-rw-rw---- 1 root root 50331648 Sep 14 02:36 ib_logfile1
			-rw-rw---- 1 root root      126 Sep 14 03:23 master.info
			drwx------ 2 root root     4096 Sep 14 02:37 mysql
			-rw-rw---- 1 root root        6 Sep 14 03:15 mysqld.pid
			drwx------ 2 root root     4096 Sep 14 02:37 performance_schema
			-rw-rw---- 1 root root       32 Sep 14 03:15 relay-log.info
			-rw-rw---- 1 root root      167 Sep 14 03:10 slave-bin.000001
			-rw-rw---- 1 root root      390 Sep 14 03:10 slave-bin.000002 <-- CREATE DATABASE
			-rw-rw---- 1 root root      167 Sep 14 03:23 slave-bin.000003
			-rw-rw---- 1 root root      284 Sep 14 03:23 slave-bin.000004
			-rw-rw---- 1 root root       76 Sep 14 03:23 slave-bin.index
			drwx------ 2 root root     4096 Sep 14 02:36 test


			[root@cluster-repo mysql-advanced-5.6.20]# bin/mysqlbinlog --base64-output=decode-rows /tmp/slave/slave-bin.000002
		
			CREATE DATABASE test_jfg
			/*!*/;
			DELIMITER ;
			# End of log file
			ROLLBACK /* added by mysqlbinlog */;
			/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
			/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
			
			[root@cluster-repo mysql-advanced-5.6.20]# bin/mysqlbinlog --base64-output=decode-rows /tmp/slave/slave-bin.000003
			/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
			/*!40019 SET @@session.max_insert_delayed_threads=0*/;
			/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
			DELIMITER /*!*/;
			# at 4
			#140914  3:15:02 server id 2  end_log_pos 120 CRC32 0xb86c0093  Start: binlog v 4, server v 5.6.20-enterprise-commercial-advanced created 140914  3:15:02
			# at 120
			#140914  3:23:41 server id 2  end_log_pos 167 CRC32 0x76482c6e  Rotate to slave-bin.000004  pos: 4
			DELIMITER ;
			# End of log file
			ROLLBACK /* added by mysqlbinlog */;
			/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
			/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
			[root@cluster-repo mysql-advanced-5.6.20]# bin/mysqlbinlog --base64-output=decode-rows /tmp/slave/slave-bin.000004
			/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
			/*!40019 SET @@session.max_insert_delayed_threads=0*/;
			/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
			DELIMITER /*!*/;
			# at 4
			#140914  3:23:41 server id 2  end_log_pos 120 CRC32 0x5f622fda  Start: binlog v 4, server v 5.6.20-enterprise-commercial-advanced created 140914  3:23:41
			# at 120
			#700101  5:30:00 server id 1  end_log_pos 0 CRC32 0x43032188    Rotate to master-bin.000001  pos: 226
			# at 168
			#140914  3:04:49 server id 1  end_log_pos 0 CRC32 0xd67bffae    Start: binlog v 4, server v 5.6.20-enterprise-commercial-advanced-log created 140914  3:04:49
			DELIMITER ;
			# End of log file
			ROLLBACK /* added by mysqlbinlog */;
			/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
			/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;


	START SLAVE SQL_THREAD;
	
			[root@cluster-repo mysql-advanced-5.6.20]# ls -l /tmp/slave
			total 110636
			-rw-rw---- 1 root root       56 Sep 14 02:41 auto.cnf
			-rw-rw---- 1 root root 12582912 Sep 14 03:15 ibdata1
			-rw-rw---- 1 root root 50331648 Sep 14 03:15 ib_logfile0
			-rw-rw---- 1 root root 50331648 Sep 14 02:36 ib_logfile1
			-rw-rw---- 1 root root      126 Sep 14 03:23 master.info
			drwx------ 2 root root     4096 Sep 14 02:37 mysql
			-rw-rw---- 1 root root        6 Sep 14 03:15 mysqld.pid
			drwx------ 2 root root     4096 Sep 14 02:37 performance_schema
			-rw-rw---- 1 root root       53 Sep 14 03:29 relay-log.info
			-rw-rw---- 1 root root      167 Sep 14 03:23 slave-bin.000003
			-rw-rw---- 1 root root      284 Sep 14 03:23 slave-bin.000004 <-- Doesn't have CREATE DATABASE
			-rw-rw---- 1 root root       38 Sep 14 03:29 slave-bin.index
			drwx------ 2 root root     4096 Sep 14 02:36 test
			drwx------ 2 root root     4096 Sep 14 03:29 test_jfg

SHOW SLAVE STATUS don't error out as reported

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 226
               Relay_Log_File: slave-bin.000004
                Relay_Log_Pos: 284
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 226
              Relay_Log_Space: 451
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 86f0f3d0-3b8a-11e4-8c73-00163e44510c
             Master_Info_File: /tmp/slave/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)