I am trying to upgrade my Percona MySQL server to 5.7. The current version on Percona MySQL is 5.6.51-91.0 and the version that I am trying to upgrade is 5.7.40-43. I am performing an in-place upgrade having the following steps:
- Backup the data and my.cnf file
- Remove the package of v5.6
- Install the package of v5.7
- Executing the mysql_upgrade checker, the upgrade the is getting failed at this point and the error log says:
2023-04-10T18:22:44.763494Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.41-44-log) starting as process 31924 …
2023-04-10T18:22:44.798744Z 0 [Note] InnoDB: PUNCH HOLE support available
2023-04-10T18:22:44.798793Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2023-04-10T18:22:44.798804Z 0 [Note] InnoDB: Uses event mutexes
2023-04-10T18:22:44.798812Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2023-04-10T18:22:44.798821Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.12
2023-04-10T18:22:44.798830Z 0 [Note] InnoDB: Using Linux native AIO
2023-04-10T18:22:44.803122Z 0 [Note] InnoDB: Number of pools: 1
2023-04-10T18:22:44.808970Z 0 [Note] InnoDB: Using CPU crc32 instructions
2023-04-10T18:22:44.819849Z 0 [Note] InnoDB: Initializing buffer pool, total size = 300G, instances = 8, chunk size = 128M
2023-04-10T18:22:52.880252Z 0 [Note] InnoDB: Completed initialization of buffer pool
2023-04-10T18:22:54.811564Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2023-04-10T18:22:54.826993Z 0 [Note] InnoDB: Crash recovery did not find the parallel doublewrite buffer at /mysqlData/mysql/data/xb_doublewrite
2023-04-10T18:22:54.829936Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2023-04-10T18:22:54.830997Z 0 [ERROR] InnoDB: Upgrade after a crash is not supported. This redo log was created before MySQL 5.7.9, and we did not find a valid checkpoint. Please follow the instructions at http://dev.mysql.com/doc/refman/5.7/en/upgrading.html
2023-04-10T18:22:54.831036Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2023-04-10T18:22:59.936051Z 0 [ERROR] Plugin ‘InnoDB’ init function returned error.
2023-04-10T18:22:59.936100Z 0 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
2023-04-10T18:22:59.936173Z 0 [ERROR] Failed to initialize builtin plugins.
2023-04-10T18:22:59.936184Z 0 [ERROR] Aborting
I also found that the parameter innodb_log_block_size is replaced now with innodb_log_write_ahead_size in v5.7 so I made those changes as well in the my.cnf file but it’s not working and making the database crash. Below is the my.cnf :
[client]
socket=/mysqlData/mysql/mysql.sock
[mysqld]
#require_secure_transport=ON
#Settings related to Teleport configuration
ssl-cert=/etc/teleport/va-db-replica-015.crt
ssl-key=/etc/teleport/va-db-replica-015.key
ssl-ca=/etc/teleport/va-db-replica-015.cas
#default-time-zone=EST5EDT
datadir=/mysqlData/mysql/data
socket=/mysqlData/mysql/mysql.sock
max_connections=4000
Key buffer size is used for MyISAM table, hence disabling the same
#key_buffer_size=32M
sort_buffer_size=64M
read_buffer_size=64M
join_buffer_size=64M
table_open_cache=4096
thread_cache_size=500
connect_timeout=60
wait_timeout=600
interactive_timeout=120
open_files_limit=40000
innodb_open_files=800
event_scheduler=OFF
max_allowed_packet=128M
tmp_table_size=1G
max_heap_table_size=1G
#######################
SLAVE SETTINGS
#######################
#log-slave-updates
skip-slave-start
#slave-skip-errors=1062, 1032
#skip-slave-start
read-only
server-id=577
report-host=va-db-replica-015
log-bin=/mysqlData/mysqlbinlog/va-db-replica-015-binlog
relay-log=/mysqlData/mysqlbinlog/va-db-replica-015-relay-bin
#slave_compressed_protocol=1
binlog_format=ROW
expire_logs_days=1
#auto-increment-increment=2
#auto-increment-offset=1
#######################
QUERY CACHE is deprecated from MySQL version 5.6
#######################
#query_cache_size=0
#query_cache_type=1
#query_cache_strip_comments=1
skip-name-resolve
#######################
LOGS
#######################
log_error=/mysqlData/log/mysqld.log
#slow_query_log_file=/mysqlData/log/slow.log
#slow_query_log=OFF
#slow_launch_time=1
#log_queries_not_using_indexes=1
#long_query_time=10
#log_slow_verbosity=fu
#query_response_time_stats=ON
#######################
Innodb Performance
#######################
#innodb_force_recovery=0
innodb_file_per_table
innodb_buffer_pool_size=300G
#innodb_additional_mem_pool_size=20M
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_flush_log_at_trx_commit=2
Dynamic
innodb_io_capacity=500
Dynamic
innodb_concurrency_tickets=4000
innodb_read_io_threads=40
innodb_write_io_threads=30
#Controls the number of background threads used by the InnoDB storage engine to perform purging and garbage collection of old data from the database.
innodb_purge_threads=4
#innodb_flush_neighbor_pages=none
#innodb_adaptive_flushing_method=keep_average
#Transaction related parameters that are dynamic
#innodb_log_block_size=8192
#innodb_log_block_size has been replaced by innodb_log_write_ahead_size variable in v5.7.
innodb_log_write_ahead_size=8192
innodb_log_buffer_size=1G
explicit_defaults_for_timestamp=1
Let InnoDB uses an adaptive algorithm to determine the optimal number of threads based on the available system resources and workload - Dynamic
#innodb_thread_concurrency=0
wait longer for client to read - Dynamic
innodb_sync_spin_loops=30
Disabling the below parameter as the working set of data may go beyond 300G so we will let InnoDB storage engine dynamically load data into the buffer pool as needed
innodb_buffer_pool_populate=0
[mysqld_safe]
err-log=/mysqlData/log/mysqld.log
#######################
NUMA READY SYSTEM / PERCONA SERVER 5.5+
#######################
flush_caches=1
numa_interleave=1
Can someone help me here please?
Note: I did the upgrade with database schemas without data and keeping the default my.cnf and it worked without any issues so I believe the issue is something related to the parameters changes.
, I used the flag : --upgrade-system-tables with mysql_upgrade and it worked well. The --upgrade-system-tables option tells mysql_upgrade to upgrade only the system tables and skip the application tables.