Re: 5.6.21 upgrade to 5.7.30 error

[xiaobo shi]

in-place 升级
1.mysql 5.6.21 my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /data/mysql/data
tmpdir = /data/mysql/tmp
port = 3306
socket = /tmp/mysql.sock
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
server-id=157
transaction-isolation = read-committed
log_slave_updates=1
character-set-server = utf8
lower_case_table_names=1
default-storage-engine = INNODB
log-output=FILE
general-log=0
general_log_file="cps.log"
slow-query-log=1
slow_query_log_file="cps-slow-99.log"
long_query_time=1
log-bin="cpsLOG-bin"
binlog_format='ROW'
log-error=/data/mysql/log/err.log
expire_logs_days=10
max_binlog_size=1073741824
max_connections=2000
query_cache_size=0
table_open_cache=4096
tmp_table_size=513M
thread_cache_size=64
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=2G
key_buffer_size=16M
read_buffer_size=16777216
read_rnd_buffer_size=33554432
sort_buffer_size=33554432
innodb_additional_mem_pool_size=32M
innodb_flush_log_at_trx_commit=2
innodb_data_file_path = ibdata1:1G:autoextend
innodb_log_buffer_size=16M
innodb_buffer_pool_size=10G
innodb_log_file_size=1280M
innodb_thread_concurrency=97
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=3000
innodb_io_capacity = 400
innodb_io_capacity_max = 8000
innodb_sort_buffer_size = 67108864
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
innodb_lock_wait_timeout=50
back_log=70
flush_time=0
innodb_flush_method = O_DIRECT
wait_timeout=300
interactive_timeout = 300
lock_wait_timeout = 1800
join_buffer_size=1M
max_allowed_packet=20M
max_connect_errors=1000
open_files_limit=4110
query_cache_type=0
sort_buffer_size=2M
table_definition_cache=4096
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_binlog=1
sync_relay_log_info=10000
performance_schema=0
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_dump_now = 1
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_load_now = 1
relay_log_recovery=1
master_info_repository=TABLE
relay_log_info_repository=TABLE
[client]
no-beep
port = 3306
socket = /tmp/mysql.sock
# pipe
# socket=mysql
#port=3306
user=root
password=rootshixiaobo

[mysql]

default-character-set=utf8

2.mysql 5.7.30 my.cnf

[mysqld]
basedir = /usr/local/mysql
datadir = /data/mysql/data
tmpdir = /data/mysql/tmp
#gtid_mode=ON
#enforce_gtid_consistency=ON
binlog_checksum=NONE
port = 3306
socket = /tmp/mysql.sock
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
server-id=171
transaction-isolation = read-committed
log_slave_updates=1
character-set-server = utf8
lower_case_table_names=1
default-storage-engine = INNODB
log-output=FILE
general-log=0
general_log_file="ns.log"
slow-query-log=1
slow_query_log_file="ns-slow-99.log"
long_query_time=2
log-bin="cpsLOG-bin"
binlog_format='ROW'
log-error=/data/mysql/log/err.log
expire_logs_days=15
max_binlog_size=536870912
max_connections=1000
query_cache_size=0
table_open_cache=4096
tmp_table_size=513M
thread_cache_size=64
key_buffer_size=16M
read_buffer_size=16777216
read_rnd_buffer_size=33554432
sort_buffer_size=33554432
innodb_flush_log_at_trx_commit=1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_log_files_in_group=3
innodb_log_buffer_size=16M
innodb_buffer_pool_size=10G
innodb_log_file_size=1280M
innodb_thread_concurrency=97
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_io_capacity = 800
innodb_io_capacity_max = 8000
innodb_sort_buffer_size = 67108864
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
innodb_lock_wait_timeout=50
back_log=70
flush_time=0
innodb_flush_method = O_DIRECT
wait_timeout=300
interactive_timeout = 300
lock_wait_timeout = 300
join_buffer_size=1M
max_allowed_packet=20M
max_connect_errors=1000
open_files_limit=4110
query_cache_type=0
sort_buffer_size=2M
table_definition_cache=4096
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_binlog=1
sync_relay_log_info=10000
collation_server=utf8_unicode_ci
init-connect='SET NAMES utf8'
performance_schema=1
relay_log_recovery=1
master_info_repository=TABLE
relay_log_info_repository=TABLE
[client]
no-beep
port = 3306
socket = /tmp/mysql.sock
# pipe
# socket=mysql
#port=3306
user=root
password=rootshixiaobo

[mysql]

default-character-set=utf8


3.mysql 5.6.21 设置 set global innodb_fast_shutdown=0;
关闭db: /usr/local/mysql/bin/mysqladmin -uroot -pshixiaobo shutdown
4.解压：mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
5.启动5.7.30 ：
/mysql-5.7.30-linux-glibc2.12-x86_64/bin/mysqld_safe --defaults-file=/etc/my.cnf --skip-grant-tables --skip-networking &