Not the answer you need?
Register and ask your own question!

my.cnf configuration from percona xtraDB cluster 5.7 to percona xtraDB cluster 8.0.19

DBA100DBA100 Current User Role Patron
Hi,
We are going to migration from percona xtraDB cluster 5.7 to percona xtraDB cluster 8.0.19, they both on different box (as we change OS, so must be on other host) so we are not going to do do in place upgrade.

in the existing my.cnf of percona xtraDB cluster 5.7 (see below) we have this global variable, not sure which one is not needed anymore and what should be the optimized global variable for us on percona 8.0.19:

[mysqld]
#general_log = on 
#general_log_file=/var/log/mysql/query
#datadir=/mysql/data
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql  (is it still needed?)

# Path to Galera library
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so

# Cluster connection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://<node#1 IP>,<node#2 IP>

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# This InnoDB autoincrement locking mode is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node 1 address
wsrep_node_address=<node#1 IP>

# SST method
wsrep_sst_method=xtrabackup-v2

# Cluster name
wsrep_cluster_name=<cluster name>

#Authentication for SST method
wsrep_sst_auth="user:password"

# MySQL VARIABLES
max_connections = 10000
key_buffer_size =256000000
myisam_sort_buffer_size=67108864
read_buffer_size =1048576
read_rnd_buffer_size=4194304
sort_buffer_size=1048576
query_cache_size=16777216

lock_wait_timeout=300
wait_timeout=28800
interactive_timeout=28800
connect_timeout=30

sort_buffer_size=256000000
max_allowed_packet = 1073741824

lower_case_table_names=1
autocommit=0
open_files_limit=10000

log_timestamps = SYSTEM


transaction_isolation="READ-COMMITTED"
sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"



Answers

  • matthewbmatthewb Senior [email protected] Percona Staff Role
    I would suggest you read up on the configuration differences between 5.7 and 8.0. The MySQL 8.0 manual has much detail here. It is not safe to simply copy 5.7 my.cnf over to 8.0. Many variables were removed, and some 5.7 defaults are no longer valid in 8.0.
  • DBA100DBA100 Current User Role Patron
    " It is not safe to simply copy 5.7 my.cnf over to 8.0. Many variables were removed, and some 5.7 defaults are no longer valid in 8.0."
    this is what I am wondering, I tried to put old setting to new cluster, then I must bootstrap it again, right?

  • matthewbmatthewb Senior [email protected] Percona Staff Role
    You only have to bootstrap the first node of the cluster. If your cluster is currently down, yes, edit the config, remove any old 5.7 variables, then bootstrap the node.
  • DBA100DBA100 Current User Role Patron
    so after adding old 5.7 variables, bootstrap the first node and test.  
    if doesn't work, remove the old 5.7 variables, then bootstrap the first node again and store back to normal ?

    "he configuration differences between 5.7 and 8.0. "
    any resource for me to read ?
  • matthewbmatthewb Senior [email protected] Percona Staff Role
    Check the MySQL manual for upgrading from 5.7 -> 8.0. The manual lists every parameter that was removed.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.