MySQL Forums
Forum List  »  NDB clusters

8.0.20-cluster binlog not recording DML Statement
Posted by: Nooruddin Dalvi
Date: May 28, 2020 03:03AM

Hi,

I have created a MySQL NDB Cluster consisting of 2 data node, 1 mgm node and 1 mysqld for testing. Also i have created a slave for NDB to INNODB replication. My binlog are not recording DML statement because of which i am not able to do Point in time recovery for NDB CLuster and also DML Statements are not replicating on slave, only the tables are being created with no row entries.

ndb_binlog_index table on master is empty with no entries.

#Below is my.cnf file for ndb-cluster mysqld.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log_bin=/home/binlog/ndbcluster-bin
log-bin-index=ndbcluster-bin.index
binlog_format=ROW

user=mysql

#NDB
ndbcluster
ndb-nodeid=101
default-storage-engine=NDB
ndb-connectstring=192.168.56.101,192.168.56.102

[mysql_cluster]

ndb-connectstring=192.168.56.101,192.168.56.102

[ndb_mgmd]
config-dir=/usr/mysql-cluster
config-file=/etc/config.ini



#Version Information;

version | 8.0.20-cluster |
| version_comment | MySQL Cluster Community Server - GPL |


#binlog variables as shown

mysql> show variables like '%bin%';
+------------------------------------------------+-------------------------------------+
| Variable_name | Value |
+------------------------------------------------+-------------------------------------+
| bind_address | * |
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_encryption | OFF |
| binlog_error_action | ABORT_SERVER |
| binlog_expire_logs_seconds | 2592000 |
| binlog_format | ROW |
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| binlog_gtid_simple_recovery | ON |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_rotate_encryption_master_key_at_startup | OFF |
| binlog_row_event_max_size | 8192 |
| binlog_row_image | FULL |
| binlog_row_metadata | MINIMAL |
| binlog_row_value_options | |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| binlog_transaction_compression | OFF |
| binlog_transaction_compression_level_zstd | 3 |
| binlog_transaction_dependency_history_size | 25000 |
| binlog_transaction_dependency_tracking | COMMIT_ORDER |
| innodb_api_enable_binlog | OFF |
| log_bin | ON |
| log_bin_basename | /home/binlog/ndbcluster-bin |
| log_bin_index | /var/lib/mysql/ndbcluster-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_statements_unsafe_for_binlog | ON |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| mysqlx_bind_address | * |
| ndb_log_bin | OFF |
| ndb_log_binlog_index | ON |
| ndb_report_thresh_binlog_epoch_slip | 10 |
| ndb_report_thresh_binlog_mem_usage | 10 |
| ndbinfo_database | ndbinfo |
| ndbinfo_max_bytes | 0 |
| ndbinfo_max_rows | 10 |
| ndbinfo_offline | OFF |
| ndbinfo_show_hidden | OFF |
| ndbinfo_table_prefix | ndb$ |
| ndbinfo_version | 524308 |
| sql_log_bin | ON |
| sync_binlog | 1 |
+------------------------------------------------+-------------------------------------+
48 rows in set (0.01 sec)



#Below a test for showing no entries being made in binlog.


mysql> show binlog events in 'ndbcluster-bin.000003';
+-----------------------+-----+----------------+-----------+-------------+-------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------------------+-----+----------------+-----------+-------------+-------------------------------------------+
| ndbcluster-bin.000003 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.20-cluster, Binlog ver: 4 |
| ndbcluster-bin.000003 | 125 | Previous_gtids | 1 | 156 | |
+-----------------------+-----+----------------+-----------+-------------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql>
mysql> create database test;
Query OK, 1 row affected (0.08 sec)


mysql> use test
Database changed
mysql>
mysql> create table employees (staff_no int(10));
Query OK, 0 rows affected, 1 warning (0.29 sec)

mysql>
mysql> show binlog events in 'ndbcluster-bin.000003';
+-----------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| ndbcluster-bin.000003 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.20-cluster, Binlog ver: 4 |
| ndbcluster-bin.000003 | 125 | Previous_gtids | 1 | 156 | |
| ndbcluster-bin.000003 | 156 | Anonymous_Gtid | 1 | 233 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| ndbcluster-bin.000003 | 233 | Query | 1 | 341 | create database test /* xid=84 */ |
| ndbcluster-bin.000003 | 341 | Anonymous_Gtid | 1 | 418 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| ndbcluster-bin.000003 | 418 | Query | 1 | 547 | use `test`; create table employees (staff_no int(10)) /* xid=92 */ |
+-----------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
6 rows in set (0.00 sec)

mysql>
mysql> insert into employees values (1234), (3242);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> select * from employees;
+----------+
| staff_no |
+----------+
| 3242 |
| 1234 |
+----------+
2 rows in set (0.00 sec)


mysql> show binlog events in 'ndbcluster-bin.000003';
+-----------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| ndbcluster-bin.000003 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.20-cluster, Binlog ver: 4 |
| ndbcluster-bin.000003 | 125 | Previous_gtids | 1 | 156 | |
| ndbcluster-bin.000003 | 156 | Anonymous_Gtid | 1 | 233 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| ndbcluster-bin.000003 | 233 | Query | 1 | 341 | create database test /* xid=84 */ |
| ndbcluster-bin.000003 | 341 | Anonymous_Gtid | 1 | 418 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| ndbcluster-bin.000003 | 418 | Query | 1 | 547 | use `test`; create table employees (staff_no int(10)) /* xid=92 */ |
+-----------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
6 rows in set (0.00 sec)


mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>
mysql>
mysql> select * from ndb_binlog_index;
Empty set (0.00 sec)


I have also checked binlog with
mysqlbinlog -v --base64-output=DECODE-ROWS ndbcluster-bin.000003

but it also doesnt show any insert entries.

Please help i am stuck on this since last 2 days.

Thanks.



Edited 1 time(s). Last edit at 05/28/2020 06:56AM by Nooruddin Dalvi.

Options: ReplyQuote


Subject
Views
Written By
Posted
8.0.20-cluster binlog not recording DML Statement
46
May 28, 2020 03:03AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.