MySQL Replication is a built-in feature of MySQL that enables data from one MySQL database server (the master) to be copied to one or more MySQL database servers (the slaves).
I have explained the architecture and variables used in my previous blogs Replication Architecture and Replication Variables respectively.
Binlog-do-db is an replication filter which is used in the master server. It is used to capture the changes happened in the mysql to binary logs only for the databases mentioned in it.
ie . binlog_do_db=test (# where test is the database name)
What happens :
If your replication is running with the above variable declared only the changes happening to tables under test schema should be logged.
But if you trying to create an user and provide grants, mysql will log those changes also to the binary logs.
Below is the master status of my server.
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mydb07-bin.000001 | 154 | test | | |
+-------------------+----------+--------------+------------------+-------------------+
You can see i have declared binlog-do-db to logs for schema test.
My binlog contents:-
[root@mydb07 ~]# mysqlbinlog --base64-output=DECODE-ROWS /var/lib/mysql/mydb07-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170920 10:48:55 server id 1 end_log_pos 123 CRC32 0x942281e9 Start: binlog v 4, server v 5.7.19-log created 170920 10:48:55 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
P/rBWQ8BAAAAdwAAAHsAAAABAAQANS43LjE5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAA/+sFZEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AemBIpQ=
'/*!*/;
# at 123
#170920 10:48:55 server id 1 end_log_pos 154 CRC32 0xc85f522a Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@mydb07 ~]#
Creating an user :
mysql> create user test_user@localhost identified by 'Test@789';
Query OK, 0 rows affected (0.01 sec)
mysql>
Checking binlog contents:-
[root@mydb07 ~]# mysqlbinlog --base64-output=DECODE-ROWS /var/lib/mysql/mydb07-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170920 10:48:55 server id 1 end_log_pos 123 CRC32 0x942281e9 Start: binlog v 4, server v 5.7.19-log created 170920 10:48:55 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
P/rBWQ8BAAAAdwAAAHsAAAABAAQANS43LjE5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAA/+sFZEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AemBIpQ=
'/*!*/;
# at 123
#170920 10:48:55 server id 1 end_log_pos 154 CRC32 0xc85f522a Previous-GTIDs
# [empty]
# at 154
#170920 11:00:59 server id 1 end_log_pos 219 CRC32 0xb2cddbf2 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#170920 11:00:59 server id 1 end_log_pos 412 CRC32 0x14a2986a Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1505885459/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
CREATE USER 'test_user'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*BC93E6B8DE8D55D91B316057474434B4E6CEC4CC'
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@mydb07 ~]#
You can see the user creation statement was also logged.
When you are in a replication environment where slave already have that user will give an replication failure.
So how to overcome this scenario,
Whenever creating an user or providing grants or dropping a user do the below.
First select an schema apart from the schema involved in the binlog-do-db, it will be better to use mysql schema.
Creating an user :
mysql>
mysql> use mysql
Database changed
mysql> create user test_user_new@localhost identified by 'Test_new@123';
Query OK, 0 rows affected (0.00 sec)
On selecting mysql schema, it will not allow mysql to log the changes to binary logs as test schema is not involved here.
Also creating a table on test schema on using it (For test purpose)
mysql> use test
Database changed
mysql> create table tbl_a (id int not null);
Query OK, 0 rows affected (0.02 sec)
mysql>
Checking binlog contents:-
[root@mydb07 ~]# mysqlbinlog --base64-output=DECODE-ROWS /var/lib/mysql/mydb07-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170920 10:48:55 server id 1 end_log_pos 123 CRC32 0x942281e9 Start: binlog v 4, server v 5.7.19-log created 170920 10:48:55 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 123
#170920 10:48:55 server id 1 end_log_pos 154 CRC32 0xc85f522a Previous-GTIDs
# [empty]
# at 154
#170920 11:00:59 server id 1 end_log_pos 219 CRC32 0xb2cddbf2 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#170920 11:00:59 server id 1 end_log_pos 412 CRC32 0x14a2986a Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1505885459/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
CREATE USER 'test_user'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*BC93E6B8DE8D55D91B316057474434B4E6CEC4CC'
/*!*/;
# at 412
#170920 11:18:48 server id 1 end_log_pos 477 CRC32 0xb6c4f262 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 477
#170920 11:18:48 server id 1 end_log_pos 587 CRC32 0x039a506b Query thread_id=6 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1505886528/*!*/;
create table tbl_a (id int not null)
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
Here you can see only the table creation statement logged and not user creation.
Similarly when you are creating a table on test schema while schema mysql is used it will also not be logged.
mysql>
mysql> use mysql
Database changed
mysql> create user test_user_new_2@localhost identified by 'Test_new@123';
Query OK, 0 rows affected (0.00 sec)
mysql> create table tbl_b (id int not null);
Query OK, 0 rows affected (0.01 sec)
Checking binlog contents:-
[root@mydb07 ~]# mysqlbinlog --base64-output=DECODE-ROWS /var/lib/mysql/mydb07-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170920 10:48:55 server id 1 end_log_pos 123 CRC32 0x942281e9 Start: binlog v 4, server v 5.7.19-log created 170920 10:48:55 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 123
#170920 10:48:55 server id 1 end_log_pos 154 CRC32 0xc85f522a Previous-GTIDs
# [empty]
# at 154
#170920 11:00:59 server id 1 end_log_pos 219 CRC32 0xb2cddbf2 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#170920 11:00:59 server id 1 end_log_pos 412 CRC32 0x14a2986a Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1505885459/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
CREATE USER 'test_user'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*BC93E6B8DE8D55D91B316057474434B4E6CEC4CC'
/*!*/;
# at 412
#170920 11:18:48 server id 1 end_log_pos 477 CRC32 0xb6c4f262 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 477
#170920 11:18:48 server id 1 end_log_pos 587 CRC32 0x039a506b Query thread_id=6 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1505886528/*!*/;
create table tbl_a (id int not null)
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@mydb07 ~]#
So when you want to create a table in test schema you need to always select (use) it (use test) .
No comments:
Post a Comment