MySQL升级方式有两种:一种是In-place Upgrade,另一种是Logical Upgrade(逻辑升级方式)。
Logical Upgrade:利用mysqldump直接导出sql文件,然后导入到新库中,适用于跨度大的版本的升级方案,做法相对安全,并能整理表中碎片。但如果有数据量较大的库需要mysqldump导出,时间就会很长。
In-place Upgrade:它的工作方法简单快速,就是直接替换掉原来版本的MySQL的安装目录和my.cnf配置文件,利用mysql_upgrade脚本来完成系统表的升级。跨小版本升级可以使用In-place这种方法。
1、In-place upgrade
1.1.环境介绍
IP: 172.16.10.22
数据目录:/data/mysql3306/data
安装目录:/usr/local/mysql5.6
配置文件:/etc/my3306.cnf
当前版本:5.6.38
1.2. 升级(5.6.38升级到5.7.20)
a) 设置INNODB_FAST_SHUTDOWN参数设置为0
set global innodb_fast_shutdown=0;
innodb_fast_shutdown有0、1、2三个值。参数0代表MySQL关闭,InnoDB需要完成所有full purge和merge insert buffer操作,这个过程需要一定的时间,有事可能会花上几个小时。参数值为1是该参数的默认值,表示关闭mysql时不完成full purge和merge insert buffer操作,但是缓冲池中的脏页还是会写到磁盘中。参数值为2时,表示既不完成full purge和merge insert buffer 操作,也不将缓冲池中的脏页刷新到磁盘,而是将日志写入到日志文件中。
b) 关闭MySQL服务
mysqladmin -uroot -pmysql -S /tmp/mysql3307.sock shutdown
c) 替换mysql的安装文件和参数以及路径
cp mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz /usr/local/
tar -zxvf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
unlink mysql5.6
ln -s mysql-5.7.20-linux-glibc2.12-x86_64 mysql5.7
mv /etc/my3306.cnf /etc/my3306.cnf.bak
vim /root/.bash_profile
export PATH=$PATH:/usr/local/mysql5.7/bin
source /root/.bash_profile
cd /data
mv mysql3306 mysql3307
由于数据文件路径是:/data/mysql3306/data
现在需要修改成:
/data/mysql3307/data
设置新的my.cnf文件(具体情况具体修改):
[client]
port = 3307
socket = /tmp/mysql.sock
[mysql]
prompt="\u@db \R:\m:\s [\d]> "
no-auto-rehash
[mysqld]
user = mysql
port = 3307
basedir = /usr/local/mysql5.7
datadir = /data/mysql3307/data
log_bin_trust_function_creators=1
socket = /tmp/mysql.sock
lower_case_table_names=1
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
skip_name_resolve = 1
innodb_undo_log_truncate=1
open_files_limit = 65535
back_log = 1024
max_connections = 500
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
table_open_cache_instances = 64
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 768
query_cache_size = 0
query_cache_type = 0
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M
slow_query_log = 1
slow_query_log_file = /data/mysql3307/logs/slow.log
log-error = /data/mysql3307/logs/error.log
long_query_time = 0.1
server-id = 3307101
log-bin = /data/mysql3307/logs/mysql-binlog
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 1G
max_binlog_size = 1G
expire_logs_days = 7
master_info_repository = TABLE
relay_log_info_repository = TABLE
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
binlog_format = row
relay_log_recovery = 1
relay-log-purge = 1
key_buffer_size = 32M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
lock_wait_timeout = 3600
explicit_defaults_for_timestamp = 1
innodb_thread_concurrency = 0
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 30
transaction_isolation = REPEATABLE-READ
innodb_buffer_pool_size = 1024M
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_max_undo_log_size = 4G
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_flush_neighbors = 0
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 50
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_online_alter_log_max_size = 4G
internal_tmp_disk_storage_engine = InnoDB
innodb_stats_on_metadata = 0
innodb_status_file = 1
innodb_status_output = 0
innodb_status_output_locks = 0
performance_schema = 1
performance_schema_instrument = '%=on'
#innodb monitor
innodb_monitor_enable="module_innodb"
innodb_monitor_enable="module_server"
innodb_monitor_enable="module_dml"
innodb_monitor_enable="module_ddl"
innodb_monitor_enable="module_trx"
innodb_monitor_enable="module_os"
innodb_monitor_enable="module_purge"
innodb_monitor_enable="module_log"
innodb_monitor_enable="module_lock"
innodb_monitor_enable="module_buffer"
innodb_monitor_enable="module_index"
innodb_monitor_enable="module_ibuf_system"
innodb_monitor_enable="module_buffer_page"
innodb_monitor_enable="module_adaptive_hash"
[mysqldump]
quick
max_allowed_packet = 32M
d) 启动MySQL服务
启动过程需要加上--skip-grant-tables和--skip-networking参数,来保证没有任何的应用连接,让升级过程更加安全。
/usr/local/mysql5.7/bin/mysqld_safe --defaults-file=/etc/my3307.cnf --skip-grant-tables --skip-networking &
错误日志很多报错:
证明系统没有升级系统表信息。
e) 升级系统数据字典信息
/usr/local/mysql5.7/bin/mysql_upgrade -S /tmp/mysql3307.sock
没有报错表示系统表升级成功。
f) 正常启动MySQL不使用--skip-grant-tables和--skip-networking参数
/usr/local/mysql5.7/bin/mysqladmin -uroot -pmysql -S /tmp/mysql3307.sock shutdown
/usr/local/mysql5.7/bin/mysqld_safe --defaults-file=/etc/my3307.cnf &
/usr/local/mysql5.7/bin/mysql -uroot -pmysql -S /tmp/mysql3307.sock
查看sys库也存在:
2、Logical Upgrade
第二种办法多用于跨度大的版本升级,一般是用于一台服务器迁移到另一台服务器:
例如,从5.1.72升级到5.7.20
首先利用MySQLDUMP 出数据库文件,由于版本差距太大,我把导出的SQL文件引擎全部换成INNODB。
在5.1.72库上执行:
mysql -uroot -p -S /tmp/mysql3307.sock smsdbtest > /backup/smsdbtest.sql
把导出的文件传到安装的5.7.20库上进行导入:
导入:
mysql -uroot -p -S /tmp/mysql3307.sock smsdbtest < /soft/smsdbtest.sql
导入成功,并进行检查。
https://www.cnblogs.com/hmwh/p/9661731.html