[MDEV-31712] replication to slave not working, relay log on slave is NOT corrupt Created: 2023-07-16  Updated: 2023-07-17  Resolved: 2023-07-17

Status: Closed
Project: MariaDB Server
Component/s: Replication
Affects Version/s: 10.9.7
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: malcolm taylor Assignee: Kristian Nielsen
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

centos7


 Description   

i have the following set up on MASTER server -

server_id = 1
 
report_host = live1
 
replicate-do-db=live1,test1
 
log_bin = /var/lib/mysql/mariadb-bin
 
log_bin_index = /var/lib/mysql/mariadb-bin.index
 
relay_log = /var/lib/mysql/relay-bin
 
relay_log_index = /var/lib/mysql/relay-bin.index

the following set up on SLAVE server -

server_id = 2
 
report_host = live2
 
replicate-do-db=live1,test1
 
log_bin = /var/lib/mysql/mariadb-bin
 
log_bin_index = /var/lib/mysql/mariadb-bin.index
 
relay_log = /var/lib/mysql/relay-bin
 
relay_log_index = /var/lib/mysql/relay-bin.index

The relay-bin file update ARE copying to the slave server and mysqlbin on the relay log does not show any problems, but the relay logs are NOT being applied for some reason.

[root@live2 mysql]# mysqlbinlog relay-bin.000002
 
/*!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
 
#230716 15:54:28 server id 2  end_log_pos 256 CRC32 0xa129a410  Start: binlog v 4, server v 10.9.7-MariaDB-log created 230716 15:54:28
 
BINLOG '
 
pAS0ZA8CAAAA/AAAAAABAABAAAQAMTAuOS43LU1hcmlhREItbG9nAAAAAAAAAAAAAAAAAAAAAAAA
 
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA
 
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
 
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
 
AAAAAAAAAAAEEwQADQgICAoKCgEQpCmh
 
'/*!*/;
 
# at 256
 
#700101  1:00:00 server id 1  end_log_pos 0 CRC32 0x7563961d    Rotate to mariadb-bin.000011  pos: 571
 
# at 305
 
#230716 15:36:34 server id 1  end_log_pos 0 CRC32 0xd1a49b9c    Start: binlog v 4, server v 10.8.8-MariaDB-log created 230716 15:36:34
 
BINLOG '
 
cgC0ZA8BAAAA/AAAAAAAAAAAAAQAMTAuOC44LU1hcmlhREItbG9nAAAAAAAAAAAAAAAAAAAAAAAA
 
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA
 
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
 
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
 
AAAAAAAAAAAEEwQADQgICAoKCgGcm6TR
 
'/*!*/;
 
# at 557
 
#230716 15:54:46 server id 1  end_log_pos 613 CRC32 0x7dc4c551  GTID 0-1-13269 ddl
 
/*!100101 SET @@session.skip_parallel_replication=0*//*!*/;
 
/*!100001 SET @@session.gtid_domain_id=0*//*!*/;
 
/*!100001 SET @@session.server_id=1*//*!*/;
 
/*!100001 SET @@session.gtid_seq_no=13269*//*!*/;
 
# at 599
 
#230716 15:54:46 server id 1  end_log_pos 799 CRC32 0x28a15666  Query   thread_id=33    exec_time=0     error_code=0    xid=1477
 
use `live1`/*!*/;
 
SET TIMESTAMP=1689519286/*!*/;
 
SET @@session.pseudo_thread_id=33/*!*/;
 
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0, @@session.explicit_defaults_for_timestamp=0/*!*/;
 
SET @@session.sql_mode=1411383296/*!*/;
 
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 
/*!\C utf8mb3 *//*!*/;
 
SET @@session.character_set_client=utf8mb3,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
 
SET @@session.lc_time_names=0/*!*/;
 
SET @@session.collation_database=DEFAULT/*!*/;
 
CREATE TABLE hello9a (
 
AuthorID INT NOT NULL AUTO_INCREMENT,
 
AuthorName VARCHAR(100),
 
PRIMARY KEY(AuthorID)
 
)
 
/*!*/;
 
DELIMITER ;
 
# End of log file
 
ROLLBACK /* added by mysqlbinlog */;
 
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
 
[root@live2 mysql]#



 Comments   
Comment by Kristian Nielsen [ 2023-07-16 ]

From https://mariadb.com/kb/en/replication-filters/#replicate_do_db:

"on the command-line or in a server option group in an option file, the system variable does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the system variable multiple times."

When you say the slave is setup with "replicate-do-db=live1,test1", is that in the configuration file? In that case you need to split it into two as described in the documentation link. Otherwise the configuration will ignore everything but the single database schema named "live1,test", which does not exist so nothing will be applied.

Comment by malcolm taylor [ 2023-07-16 ]

Hi @kristian

ahhhh thats it! Im sure i had done with commas before (maybe on an old version of mysql i cant remember now), but anyway yes thanks this solves the problem ive just tried it now.

thanks so much for your help!

Comment by malcolm taylor [ 2023-07-16 ]

the other thing i had done wrong was in the /etc/my.cnf i had put it in wrong replicate-do-db is incorrect (i saw from your link) i should have put replicate_do_db with underscores.

im not sure how to close this issue, i cant see an option to do so....

Comment by Kristian Nielsen [ 2023-07-17 ]

Glad that you got it solved, I'll close it.
I think it should work either way, replicate_do_db or replicate-do-db.

- Kristian.

Generated at Thu Feb 08 10:25:54 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.