stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
# Connecting to master...
CREATE DATABASE db1;
CREATE DATABASE db2;
USE db1;
CREATE TABLE t11(id INT NOT NULL PRIMARY KEY, name VARCHAR(10));
CREATE TABLE t12(a CHAR(5));
INSERT INTO t11 VALUES(1,'jon'),(2,'jil'),(5,'jack'),(6,'ku');
INSERT INTO t12 VALUES('a'),('b');
USE db2;
CREATE TABLE t21 (id INT);
CREATE TABLE t22 (a CHAR (10));
INSERT INTO t21 VALUES (3), (4);
INSERT INTO t22 VALUES ('c'), ('d');
# Remove all entries in the backup logs.
FLUSH BACKUP LOGS;
PURGE BACKUP LOGS;
# connecting to slave...
# Check the server option --binlog-do-db=db1 works
SELECT * FROM db1.t11;
id      name
1       jon
2       jil
5       jack
6       ku
SELECT * FROM db1.t12;
a
a
b
# The selects from db2 & db3 will not work as they are ignored
# for replication
SELECT * FROM db2.t21;
ERROR 42S02: Table 'db2.t21' doesn't exist
SELECT * FROM db2.t22;
ERROR 42S02: Table 'db2.t22' doesn't exist
# connecting to master again
# Perform backup
BACKUP DATABASE db1 to 'db1.bak';
backup_id
#
# Get master's binlog position before restore.
RESTORE FROM 'db1.bak' OVERWRITE;
backup_id
#
# Check the master status for incident event that is generated
SHOW MASTER STATUS;
File    Position        Binlog_Do_DB    Binlog_Ignore_DB
master-bin.000002       107     db1
# Show the incident event issued as a result of restore.
Log_name        Pos     Event_type      Server_id       End_log_pos     Info
master-bin.000001       #       Rotate  1       #       master-bin.000002;pos=4
SHOW SLAVE STATUS;;
Slave_IO_State  #
Master_Host     127.0.0.1
Master_User     root
Master_Port     MASTER_PORT
Connect_Retry   1
Master_Log_File #
Read_Master_Log_Pos     #
Relay_Log_File  #
Relay_Log_Pos   #
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     #
Relay_Log_Space #
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   #
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
# See for any error that has occured because of incident event
# (you will not see any)
Last_SQL_Error

# Conencting to master again to check replication is ON
USE db1;
INSERT INTO db1.t11 VALUES(10,'pp'),(11,'kiy');
USE db2;
INSERT INTO db2.t21 VALUES(4),(9);
SELECT * FROM db1.t11;
id      name
1       jon
2       jil
5       jack
6       ku
10      pp
11      kiy
SELECT * FROM db2.t21;
id
3
4
4
9
SELECT * FROM db1.t11;
id      name
1       jon
2       jil
5       jack
6       ku
10      pp
11      kiy
SELECT * FROM db2.t21;
ERROR 42S02: Table 'db2.t21' doesn't exist
# Cleanup
# Connecting to master...
FLUSH BACKUP LOGS;
PURGE BACKUP LOGS;
DROP DATABASE db1;
DROP DATABASE db2;
# Connecting to slave...
FLUSH BACKUP LOGS;
PURGE BACKUP LOGS;