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> select * from ndb_binlog_index;
Empty set (0.00 sec)
I have also checked binlog with mysqlbinlog but it also doesnt show any insert entries.
{
[root@ndb-cluster-mysqld1 binlog]# mysqlbinlog -v --base64-output=DECODE-ROWS ndbcluster-bin.000003
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200528 14:20:15 server id 1 end_log_pos 125 CRC32 0x3ad8779c Start: binlog v 4, server v 8.0.20-cluster created 200528 14:20:15
# Warning: this binlog is either in use or was not closed properly.
# at 125
#200528 14:20:15 server id 1 end_log_pos 156 CRC32 0x71313f0a Previous-GTIDs
# [empty]
# at 156
#200528 14:21:12 server id 1 end_log_pos 233 CRC32 0x0f5b463e Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no original_committed_timestamp=1590655872244131 immediate_commit_timestamp=1590655872244131 transaction_length=185
# original_commit_timestamp=1590655872244131 (2020-05-28 14:21:12.244131 IST)
# immediate_commit_timestamp=1590655872244131 (2020-05-28 14:21:12.244131 IST)
/*!80001 SET @@session.original_commit_timestamp=1590655872244131*//*!*/;
/*!80014 SET @@session.original_server_version=80020*//*!*/;
/*!80014 SET @@session.immediate_server_version=80020*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 233
#200528 14:21:12 server id 1 end_log_pos 341 CRC32 0xb8313dcf Query thread_id=16 exec_time=0 error_code=0 Xid = 84
SET TIMESTAMP=1590655872/*!*/;
SET @@session.pseudo_thread_id=16/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
/*!80016 SET @@session.default_table_encryption=0*//*!*/;
create database test
/*!*/;
# at 341
#200528 14:22:35 server id 1 end_log_pos 418 CRC32 0x562df9ef Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no original_committed_timestamp=1590655955983593 immediate_commit_timestamp=1590655955983593 transaction_length=206
# original_commit_timestamp=1590655955983593 (2020-05-28 14:22:35.983593 IST)
# immediate_commit_timestamp=1590655955983593 (2020-05-28 14:22:35.983593 IST)
/*!80001 SET @@session.original_commit_timestamp=1590655955983593*//*!*/;
/*!80014 SET @@session.original_server_version=80020*//*!*/;
/*!80014 SET @@session.immediate_server_version=80020*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 418
#200528 14:22:35 server id 1 end_log_pos 547 CRC32 0x8618cc05 Query thread_id=16 exec_time=0 error_code=0 Xid = 92
use `test`/*!*/;
SET TIMESTAMP=1590655955/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
create table employees (staff_no int(10))
/*!*/;
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*/;
}
Please help i am stuck on this since last 2 days.
Thanks.
