0

I am using mysql 5.6.19-log MySQL Community Server (GPL). I have done the necessary changes in my.cnf to start Master<->Slave Replication. But binary files are created and but they are empty.

log-bin                        = /data/mysqllog/mysql_logs/binary_logs/mysql-bin
expire-logs-days               = 7
sync-binlog                    = 1
binlog_format                  = ROW
binlog_row_image               = full
binlog_checksum                = CRC32
# REPLICATION #
read_only                      = 0
skip-slave-start               = 1
relay-log                      = /data/mysqllog/mysql_logs/mysql_relay_logs/relay-bin
slave-net-timeout              = 60
relay_log_recovery             = ON
master_info_repository         = TABLE
relay_log_info_repository      = TABLE
master_verify_checksum         = ON
#log_slave_updates             = ON
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000024 |       167 |
| mysql-bin.000025 |       167 |
| mysql-bin.000026 |       167 |
| mysql-bin.000027 |       167 |
| mysql-bin.000028 |       167 |
| mysql-bin.000029 |       143 |
| mysql-bin.000030 |       167 |
| mysql-bin.000031 |       167 |
| mysql-bin.000032 |       143 |
| mysql-bin.000033 |       459 |
| mysql-bin.000034 |       143 |
| mysql-bin.000035 |       120 |
+------------------+-----------+

Have verified general_log for the events coming to this master.

 binary_logs]# mysqlbinlog mysql-bin.000035
/*!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
#201223  7:23:32 server id 10708277  end_log_pos 120 CRC32 0x5f62c0b1   Start: binlog v 4, server v 5.6.19-log created 201223  7:23:32 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
HKPiXw81ZaMAdAAAAHgAAAABAAQANS42LjE5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAco+JfEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAbHA
Yl8=
'/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

Any help will be appreciated.Please comment if I need to add some more details. Thank you

=EDIT=

mysql> show slave status;
Empty set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000035 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

=EDIT2=

mysql> SHOW VARIABLES LIKE '%log%';
+-----------------------------------------+------------------------------------------------------------+
| Variable_name                           | Value                                                      |
+-----------------------------------------+------------------------------------------------------------+
| audit_json_log_file                     | /data/mysqllog/mysql_logs/mysql-audit.log                  |
| back_log                                | 550                                                        |
| binlog_cache_size                       | 32768                                                      |
| binlog_checksum                         | CRC32                                                      |
| binlog_direct_non_transactional_updates | OFF                                                        |
| binlog_format                           | ROW                                                        |
| binlog_max_flush_queue_time             | 0                                                          |
| binlog_order_commits                    | ON                                                         |
| binlog_row_image                        | FULL                                                       |
| binlog_rows_query_log_events            | OFF                                                        |
| binlog_stmt_cache_size                  | 32768                                                      |
| expire_logs_days                        | 7                                                          |
| general_log                             | OFF                                                        |
| general_log_file                        | /data/mysqldata/mysql/shipping-mysqlshipping-4017.log      |
| innodb_api_enable_binlog                | OFF                                                        |
| innodb_flush_log_at_timeout             | 1                                                          |
| innodb_flush_log_at_trx_commit          | 1                                                          |
| innodb_locks_unsafe_for_binlog          | OFF                                                        |
| innodb_log_buffer_size                  | 8388608                                                    |
| innodb_log_compressed_pages             | ON                                                         |
| innodb_log_file_size                    | 1073741824                                                 |
| innodb_log_files_in_group               | 2                                                          |
| innodb_log_group_home_dir               | ./                                                         |
| innodb_mirrored_log_groups              | 1                                                          |
| innodb_online_alter_log_max_size        | 5368709120                                                 |
| innodb_undo_logs                        | 128                                                        |
| log_bin                                 | ON                                                         |
| log_bin_basename                        | /data/mysqllog/mysql_logs/binary_logs/mysql-bin            |
| log_bin_index                           | /data/mysqllog/mysql_logs/binary_logs/mysql-bin.index      |
| log_bin_trust_function_creators         | OFF                                                        |
| log_bin_use_v1_row_events               | OFF                                                        |
| log_error                               | /data/mysqllog/mysql_logs/mysql-error.log                  |
| log_output                              | FILE                                                       |
| log_queries_not_using_indexes           | OFF                                                        |
| log_slave_updates                       | OFF                                                        |
| log_slow_admin_statements               | OFF                                                        |
| log_slow_slave_statements               | OFF                                                        |
| log_throttle_queries_not_using_indexes  | 0                                                          |
| log_warnings                            | 1                                                          |
| max_binlog_cache_size                   | 18446744073709547520                                       |
| max_binlog_size                         | 1073741824                                                 |
| max_binlog_stmt_cache_size              | 18446744073709547520                                       |
| max_relay_log_size                      | 0                                                          |
| relay_log                               | /data/mysqllog/mysql_logs/mysql_relay_logs/relay-bin       |
| relay_log_basename                      | /data/mysqllog/mysql_logs/mysql_relay_logs/relay-bin       |
| relay_log_index                         | /data/mysqllog/mysql_logs/mysql_relay_logs/relay-bin.index |
| relay_log_info_file                     | relay-log.info                                             |
| relay_log_info_repository               | TABLE                                                      |
| relay_log_purge                         | ON                                                         |
| relay_log_recovery                      | ON                                                         |
| relay_log_space_limit                   | 0                                                          |
| slow_query_log                          | ON                                                         |
| slow_query_log_file                     | /data/mysqllog/mysql_logs/mysql-slow.log                   |
| sql_log_bin                             | ON                                                         |
| sql_log_off                             | OFF                                                        |
| sync_binlog                             | 1                                                          |
| sync_relay_log                          | 10000                                                      |
| sync_relay_log_info                     | 10000                                                      |
+-----------------------------------------+------------------------------------------------------------+
58 rows in set (0.01 sec)
7
  • log_slave_updates
    – Rick James
    Dec 23 '20 at 5:49
  • Do i need to have enable log_slave_updates on the master?@RickJames
    – dragon
    Dec 23 '20 at 5:52
  • Let's see SHOW MASTER STATUS; and SHOW SLAVE STATUS;
    – Rick James
    Dec 23 '20 at 6:04
  • @RickJames, have added that show master status and SHOW SLAVE STATUS
    – dragon
    Dec 23 '20 at 6:10
  • SHOW SLAVE on the replica. On Primary, do SHOW VARIABLES LIKE '%log%';
    – Rick James
    Dec 23 '20 at 6:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.