binlog activated after upgrade to xtradb8.0

gmllt commented 6 months ago

Currently:

We are having issues after migration to xtradb8.0. For some reasons, binlog is activated despite the property engine_config.binlog.enabled is set to false. Here the properties for the pxc-mysql job :

admin_password: ((cf_mysql_mysql_admin_password))
engine_config:
  binlog:
    enabled: false
  galera:
    enabled: true
mysql_version: "8.0"
port: 13306
seeded_databases:
- name: cloud_controller
  password: ((cc_database_password))
  username: cloud_controller
- name: diego
  password: ((diego_database_password))
  username: diego
- name: network_connectivity
  password: ((network_connectivity_database_password))
  username: network_connectivity
- name: network_policy
  password: ((network_policy_database_password))
  username: network_policy
- name: routing-api
  password: ((routing_api_database_password))
  username: routing-api
- name: uaa
  password: ((uaa_database_password))
  username: uaa
- name: locket
  password: ((locket_database_password))
  username: locket
- name: credhub
  password: ((credhub_database_password))
  username: credhub
seeded_users:
  registrar:
    host: any
    password: ((mysql_users_registrar_secret))
    role: admin
tls:
  galera: ((galera_server_certificate))
  server: ((mysql_server_certificate))

In the my.cnf configuration file generated, i don't see any log_bin variable (neither in the auto-tune.cnf) :

[client]                             
port                            = 13306 
socket                          = /var/vcap/sys/run/pxc-mysql/mysqld.sock

!include                        /var/vcap/jobs/pxc-mysql/config/auto-tune.cnf

[mysqld-8.0]                           
authentication-policy           = mysql_native_password
mysqlx_socket                   = /var/vcap/sys/run/pxc-mysql/mysqlx.sock
log_replica_updates             = ON    
binlog_expire_logs_seconds      = 604800            
source_verify_checksum          = ON
wsrep_provider                  = /var/vcap/packages/percona-xtradb-cluster-8.0/lib/libgalera_smm.so

[mysqld-5.7]
wsrep_sst_auth                  = [PRIVATE-DATA]
wsrep_provider                  = /var/vcap/packages/percona-xtradb-cluster-5.7/lib/libgalera_smm.so
log_slave_updates               = ON
expire_logs_days                = 7  
master_verify_checksum          = ON

symbolic-links                  = OFF

[mysqld] 
character_set_server            = utf8mb4                 
server-id                       = 0                           
ssl-cert=/var/vcap/jobs/pxc-mysql/certificates/server-cert.pem
ssl-key=/var/vcap/jobs/pxc-mysql/certificates/server-key.pem

# GALERA options:
pxc-strict-mode                 = MASTER
pxc_maint_transition_period     = 0   
wsrep_cluster_address           = gcomm://9ec0805a-a6c2-4731-a139-9eb275441a8e.database.default.cf.bosh,c04232d0-576f-42d3-9fa8-a9adbc07353b.database.default.cf.bosh,ff5de02c-0581-4322-887e-11ef351ec6b0.database.default.cf.bosh
wsrep_cluster_name              = galera-cluster
wsrep_load_data_splitting       = ON  
wsrep_log_conflicts             = ON
wsrep_max_ws_rows               = 0
wsrep_max_ws_size               = 1073741824
wsrep_node_address              = 10.0.4.13:4567
wsrep_node_name                 = database/0
wsrep_on                        = ON
wsrep_provider_options          = "gcache.size=512M;pc.recovery=FALSE;pc.checksum=TRUE;socket.ssl=yes;socket.ssl_ca=/var/vcap/jobs/pxc-mysql/certificates/galera-ca.pem;socket.ssl_cert=/var/vcap/jobs/pxc-mysql/certificates/galera-cert.pem;socket.ssl_key=/var/vcap/jobs/pxc-mysql/certificates/galera-key.pem;socket.ssl_cipher=ECDHE-RSA-AES256-GCM-SHA384"
wsrep_replicate_myisam          = OFF
wsrep_sst_method                = xtrabackup-v2
pxc-encrypt-cluster-traffic     = OFF

# Regular MYSQL options:
lower_case_table_names          = 0
user                            = vcap
socket                          = /var/vcap/sys/run/pxc-mysql/mysqld.sock
port                            = 13306
datadir                         = /var/vcap/store/pxc-mysql
tmpdir                          = /var/vcap/data/pxc-mysql/tmp
pid-file                        = /var/vcap/store/pxc-mysql/mysql.pid
log_error                       = /var/vcap/sys/log/pxc-mysql/mysql.err.log
init_file                       = /var/vcap/jobs/pxc-mysql/config/db_init
skip_external_locking           = TRUE
secure_file_priv                = /var/vcap/data/pxc-mysql/files
table_definition_cache          = 8192
table_open_cache                = 2000
max_allowed_packet              = 256M
skip_name_resolve               = ON
local_infile                    = ON

relay_log                       = mysql-relay
relay_log_recovery              = OFF

# Required for user to create triggers when binlog is enabled
log_bin_trust_function_creators = 1

# Slow query logging:
slow_query_log                  = 1
slow_query_log_file             = /var/vcap/sys/log/pxc-mysql/mysql_slow_query.log
long_query_time                 = 10
log_queries_not_using_indexes   = OFF

# User statistics
userstat                        = OFF

max_heap_table_size             = 16777216
tmp_table_size                  = 33554432

# These are mandatory MySQL settings for Galera to work
binlog_format                   = ROW
default_storage_engine          = InnoDB
innodb_autoinc_lock_mode        = 2
innodb_doublewrite              = ON
innodb_file_per_table           = ON
innodb_flush_log_at_trx_commit  = 1
innodb_flush_method             = fsync
innodb_lock_wait_timeout        = 50
innodb_log_buffer_size          = 32M
innodb_log_file_size            = 1024MB
# These are required to make the quota enforcer work
innodb_stats_on_metadata        = ON
innodb_stats_persistent         = OFF
innodb_strict_mode              = OFF

max_connections                 = 5000

# Event Scheduler
event_scheduler                 = OFF

[mysqld_plugin]

[sst]
encrypt=4
ssl-ca=/var/vcap/jobs/pxc-mysql/certificates/galera-ca.pem
ssl-cert=/var/vcap/jobs/pxc-mysql/certificates/galera-cert.pem
ssl-key=/var/vcap/jobs/pxc-mysql/certificates/galera-key.pem
sockopt="cipher=ECDHE-RSA-AES256-GCM-SHA384"

[mysqldump]
quick
max_allowed_packet              = 256M

[mysql]
max_allowed_packet              = 256M

But when i look for this variable via sql, I see it set up to ON:

SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

Expected:

binlog is not activated.

Steps to Reproduce:

Deploy pxc-release in galera mode with property engine_config.binlog.enabled = false, with mysql_version "8.0".
Connect to mysql and execute SHOW VARIABLES LIKE 'log_bin' or SHOW BINARY LOGS.

Deployment Context:

We see the issue on the v10.0.21 of the pxc-release, but it may affect v10.0.19 as well. We have a galera cluster of 3 nodes.

gmllt commented 6 months ago

Found it !

From MySQL 8.0, binary logging is enabled by default.

abg commented 5 months ago

Thank you for bringing this to our attention and providing a PR to address it.

We just merged your PR and we will close this issue out once we've cut a release.

abg commented 5 months ago

This issue is resolved by pxc/1.0.26

cloudfoundry / pxc-release