热门标签
热门文章
- 1消息中间件RabbitMQ需要知道的6个端口的作用_4369端口是什么服务
- 2从入门到入狱之kali Linux 命令指令_进入kali linux的代码
- 3Datawhale X 魔搭 AI 夏令营第四期的学习活动 Task1笔记
- 4vs2019 git拉取代码提示密码错误_the provided password or token is incorrect or you
- 5【Redis】网络模型_redis网络模型
- 6Vitis AI 综合实践（迁移学习+模型量化+DPU部署）_vitis ai dpu
- 7IDEA中Git及可视化工具TortoiseGit简单使用_git可视化工具
- 8【Flutter】编写第一个 Flutter 应用（官方示例完整代码版）_flutter编写
- 9解决git@gitee.com: Permission denied (publickey). Could not read from remote repository._gitee权限被拒绝
- 10手把手教你搭建QQ机器人_chatgptqq机器人
当前位置: article > 正文
MySQL版本升级_mysql升级
作者：正经夜光杯 | 2024-08-12 01:54:41
赞
踩
mysql升级
MySQL版本升级
升级说明
MySQL升级的实质
对数据字典的升级
数据字典有：mysql、information_schema、performance_schema、sys schema。
MySQL升级的两种方式
大版本升级（需要考虑业务的可用性）
- 5.6 to 5.7
- 5.6 to 8.0
- 5.7 to 8.0
小版本升级（业务不是必须的）
- 5.7.11 to 5.7.22
- 8.0.11 to 8.0.12
升级技术
1） 备份恢复：mysqldump，mysqlpump，mydumper
2） mysql_upgrade
升级的注意事项
升级之前要做好备份
确认新版本是否有重大变更
注意 SQL mode 的变化
- 比如：在MySQL5.7中发生了SQL mode的变化，对不再支持的SQL mode，部分SQL会跑不通，此时可以清空SQL mode，跑完之后在设置SQL mode。
升级成功后，确认业务SQL是否可以跑通
程序层是否都正常
- 有时原使用的程序语言部分内容不被支持新版本数据库。比如，有一次在5.1时用的是PHP4.0，但升级到5.6，PHP的某些函数不被支持。
在升级完成之后，一定要在测试时使用和线上版本相同的程序，测试是否存在问题。
存储引擎的变化
- 比如：在8.0版本，不再支持myisam 引擎。
注意字符集的乱码问题
大版本升级
5.7 to 8.0
1、在原版本创建测试数据库和测试数据
[root@itpuxdb app]# pwd /mysql/app [root@itpuxdb app]# [root@itpuxdb app]# ls -lsa *.sql 47380 -rw-r--r-- 1 mysql mysql 48513198 Mar 10 2018 itpuxdb.sql [root@itpuxdb app]# [root@itpuxdb app]# mysql -uroot -prootroot -e "create database itpuxdb;" [root@itpuxdb app]# mysql -uroot -prootroot itpuxdb < itpuxdb.sql [root@itpuxdb app]# mysql -uroot -prootroot itpuxdb -e "show tables" mysql: [Warning] Using a password on the command line interface can be insecure. +-------------------+ | Tables_in_itpuxdb | +-------------------+ | bm | | dd | | dq | | gj | | gw | | itpux11 | | itpux12 | | itpux_m1 | | itpux_m5 | | itpux_obj | | itpux_sales | | itpux_yg | | jl | | yg | +-------------------+ [root@itpuxdb app]#
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
2、下载解压新版安装包
#当前的版本 [root@itpuxdb ~]# mysql --version mysql Ver 14.14 Distrib 5.7.20, for linux-glibc2.12 (x86_64) using EditLine wrapper [root@itpuxdb ~]# [root@itpuxdb ~]# mysql -uroot -prootroot -e "select @@version" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | @@version | +------------+ | 5.7.20-log | +------------+ [root@itpuxdb ~]# mysql -uroot -prootroot -e "select version()" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | version() | +------------+ | 5.7.20-log | +------------+ [root@itpuxdb ~]# #老版本的目录 [root@itpuxdb app]# pwd /mysql/app [root@itpuxdb app]# ll total 603148 drwx------ 2 mysql mysql 16384 Jan 31 2021 lost+found lrwxrwxrwx 1 mysql mysql 35 Jan 31 2021 mysql -> mysql-5.7.20-linux-glibc2.12-x86_64 drwxr-xr-x 9 mysql mysql 4096 Jan 31 2021 mysql-5.7.20-linux-glibc2.12-x86_64 #上传解压高版本的代码包 [root@itpuxdb app]# ll mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz -rw-r--r-- 1 root root 617598500 Aug 12 18:02 mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz [root@itpuxdb app]# tar xvJf mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz [root@itpuxdb app]# ll lrwxrwxrwx 1 mysql mysql 35 Jan 31 2021 mysql -> mysql-5.7.20-linux-glibc2.12-x86_64 drwxr-xr-x 9 mysql mysql 4096 Jan 31 2021 mysql-5.7.20-linux-glibc2.12-x86_64 drwxr-xr-x 9 root root 4096 Aug 12 18:23 mysql-8.0.34-linux-glibc2.12-x86_64 -rw-r--r-- 1 root root 617598500 Aug 12 18:02 mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz [root@itpuxdb app]# #删除原链接，做新链接 [root@itpuxdb app]# ln -sf mysql-8.0.34-linux-glibc2.12-x86_64 mysql [root@itpuxdb app]# ll total 650552 lrwxrwxrwx 1 root root 35 Aug 12 18:24 mysql -> mysql-8.0.34-linux-glibc2.12-x86_64 drwxr-xr-x 9 mysql mysql 4096 Jan 31 2021 mysql-5.7.20-linux-glibc2.12-x86_64 drwxr-xr-x 9 root root 4096 Aug 12 18:23 mysql-8.0.34-linux-glibc2.12-x86_64 -rw-r--r-- 1 root root 617598500 Aug 12 18:02 mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz [root@itpuxdb app]#
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
3、停止MySQL服务
#停止mysql数据库服务
service mysql stop
- 1
- 2
4、准备适合8.0版本的参数文件和启动脚本
#注意5.7和8.0的一些参数还不一样，所以需要准备适应与8.0的参数文件和mysql服务文件： [root@itpuxdb app]# ll drwx------ 2 mysql mysql 16384 Jan 31 2021 lost+found -rw-r--r-- 1 root root 3911 Feb 25 2021 my.cnf lrwxrwxrwx 1 mysql mysql 35 Jan 31 2021 mysql -> mysql-5.7.20-linux-glibc2.12-x86_64 drwxr-xr-x 9 mysql mysql 4096 Jan 31 2021 mysql-5.7.20-linux-glibc2.12-x86_64 -rw-r--r-- 1 root root 617598500 Aug 12 18:02 mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz -rw-r--r-- 1 root root 10735 Aug 1 2018 mysql.server [root@itpuxdb app]# #进去新的代码路径里备份mysql.server文件 [root@itpuxdb support-files]# pwd /mysql/app/mysql/support-files [root@itpuxdb support-files]# ll total 20 -rwxr-xr-x 1 7161 31415 1061 Jun 22 19:07 mysqld_multi.server -rw-r--r-- 1 7161 31415 2027 Jun 22 20:56 mysql-log-rotate -rwxr-xr-x 1 7161 31415 10576 Jun 22 20:56 mysql.server [root@itpuxdb support-files]# mv mysql.server mysql.server.bak [root@itpuxdb support-files]# ll total 20 -rwxr-xr-x 1 7161 31415 1061 Jun 22 19:07 mysqld_multi.server -rw-r--r-- 1 7161 31415 2027 Jun 22 20:56 mysql-log-rotate -rwxr-xr-x 1 7161 31415 10576 Jun 22 20:56 mysql.server.bak [root@itpuxdb support-files]# #然后修改一份该文件，或者直接使用我们之前准备好的，放到该路径下 [root@itpuxdb support-files]# cp /mysql/app/mysql.server . [root@itpuxdb support-files]# ll total 32 -rwxr-xr-x 1 7161 31415 1061 Jun 22 19:07 mysqld_multi.server -rw-r--r-- 1 7161 31415 2027 Jun 22 20:56 mysql-log-rotate -rw-r--r-- 1 root root 10735 Aug 12 18:27 mysql.server -rwxr-xr-x 1 7161 31415 10576 Jun 22 20:56 mysql.server.bak #修改权限 [root@itpuxdb support-files]# chmod 775 mysql.server [root@itpuxdb support-files]# ll total 32 -rwxr-xr-x 1 7161 31415 1061 Jun 22 19:07 mysqld_multi.server -rw-r--r-- 1 7161 31415 2027 Jun 22 20:56 mysql-log-rotate -rwxrwxr-x 1 root root 10735 Aug 12 18:27 mysql.server -rwxr-xr-x 1 7161 31415 10576 Jun 22 20:56 mysql.server.bak [root@itpuxdb support-files]# #替换参数文件 [root@itpuxdb 3306]# ll total 12 drwxr-xr-x 6 mysql mysql 4096 Aug 12 18:29 data -rw-r--r-- 1 root root 3678 Feb 1 2021 my.cnf -rwxrwxrwx 1 root root 88 Jan 31 2021 mysql.start [root@itpuxdb 3306]# pwd /mysql/data/3306 [root@itpuxdb 3306]# mv my.cnf my.cnf.bak [root@itpuxdb 3306]# cp /mysql/app/my.cnf /mysql/data/3306/my.cnf [root@itpuxdb 3306]# ll total 16 drwxr-xr-x 6 mysql mysql 4096 Aug 12 18:29 data -rw-r--r-- 1 root root 3911 Aug 12 18:31 my.cnf -rw-r--r-- 1 root root 3678 Feb 1 2021 my.cnf.bak -rwxrwxrwx 1 root root 88 Jan 31 2021 mysql.start [root@itpuxdb 3306]#
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
适合MySQL 8.0的参数
[client] port=3306 socket = /mysql/data/3306/mysql.sock [mysql] no-beep prompt="\u@mysqldb \R:\m:\s [\d]> " #no-auto-rehash auto-rehash default-character-set=utf8mb4 [mysqld] ########basic settings######## server-id=3306 port=3306 user = mysql bind_address= 192.168.1.51 basedir=/mysql/app/mysql datadir=/mysql/data/3306/data socket = /mysql/data/3306/mysql.sock pid-file = /mysql/data/3306/mysql.pid character-set-server=utf8mb4 autocommit = 0 #skip_name_resolve = 1 max_connections = 800 max_connect_errors = 1000 default-storage-engine=INNODB transaction_isolation = READ-COMMITTED explicit_defaults_for_timestamp = 1 sort_buffer_size = 32M join_buffer_size = 128M tmp_table_size = 72M max_allowed_packet = 16M #sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER" interactive_timeout = 1800 wait_timeout = 1800 read_buffer_size = 16M read_rnd_buffer_size = 32M #query_cache_type = 1 #query_cache_size=1M table_open_cache=2000 thread_cache_size=768 myisam_max_sort_file_size=10G myisam_sort_buffer_size=135M key_buffer_size=32M read_buffer_size=8M read_rnd_buffer_size=4M back_log=1024 #flush_time=0 open_files_limit=65536 table_definition_cache=1400 #binlog_row_event_max_size=8K #sync_master_info=10000 #sync_relay_log=10000 #sync_relay_log_info=10000 ########log settings######## log-output=FILE general_log = 0 general_log_file=/mysql/log/3306/itpuxdb-general.err slow_query_log = ON slow_query_log_file=/mysql/log/3306/itpuxdb-query.err long_query_time=10 log-error=/mysql/log/3306/itpuxdb-error.err log_queries_not_using_indexes = 1 log_slow_admin_statements = 1 log_slow_slave_statements = 1 log_throttle_queries_not_using_indexes = 10 #expire_logs_days = 90 #binlog_expire_logs_seconds=2592000 binlog_expire_logs_seconds=604800 min_examined_row_limit = 100 log_bin=/mysql/log/3306/binlog/itpuxdb-binlog log_bin_index=/mysql/log/3306/binlog/itpuxdb-binlog.index binlog_format='ROW' binlog_rows_query_log_events=on ########replication settings######## #master_info_repository = TABLE #relay_log_info_repository = TABLE #log_bin = bin.log #sync_binlog = 1 #gtid_mode = on #enforce_gtid_consistency = 1 #log_slave_updates #binlog_format = row #relay_log = relay.log #relay_log_recovery = 1 #binlog_gtid_simple_recovery = 1 #slave_skip_errors = ddl_exist_errors ########innodb settings######## #根据您的服务器IOPS能力适当调整 #一般配置普通SSD盘的话，可以调整到10000 - 20000 #配置高端的PCIe SSD卡的话,可以调整到50000 - 80000 innodb_io_capacity = 4000 innodb_io_capacity_max = 8000 innodb_buffer_pool_size = 500M innodb_buffer_pool_instances = 8 innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_dump_at_shutdown = 1 innodb_lru_scan_depth = 2000 innodb_lock_wait_timeout = 5 #innodb_flush_method = O_DIRECT innodb_log_file_size = 200M innodb_log_files_in_group = 2 innodb_log_buffer_size = 16M #innodb_undo_logs = 128 innodb_undo_tablespaces = 3 innodb_undo_log_truncate = 1 innodb_max_undo_log_size = 2G innodb_flush_neighbors = 1 innodb_purge_threads = 4 #innodb_large_prefix = 1 innodb_thread_concurrency = 64 innodb_print_all_deadlocks = 1 innodb_strict_mode = 1 innodb_sort_buffer_size = 64M innodb_flush_log_at_trx_commit=1 innodb_autoextend_increment=64 innodb_concurrency_tickets=5000 innodb_old_blocks_time=1000 innodb_open_files=65536 innodb_stats_on_metadata=0 innodb_file_per_table=1 innodb_checksum_algorithm=0 innodb_data_file_path=ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:5G innodb_temp_data_file_path = ibtmp1:200M:autoextend:max:20G innodb_buffer_pool_dump_pct = 40 innodb_page_cleaners = 4 innodb_purge_rseg_truncate_frequency = 128 binlog_gtid_simple_recovery=1 log_timestamps=system #transaction_write_set_extraction=MURMUR32 default_authentication_plugin=mysql_native_password #default_authentication_plugin=caching_sha2_password
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
mysql.server修改
[root@itpuxdb support-files]# vim mysql.server #修改 46 basedir= 47 datadir= #改为 46 basedir=/mysql/app/mysql 47 datadir=/mysql/data/3306/data #修改 63 mysqld_pid_file_path= #改为 63 mysqld_pid_file_path=/mysql/data/3306/mysql.pid #修改 64 if test -z "$basedir" 65 then 66 basedir=/usr/local/mysql 67 bindir=/usr/local/mysql/bin 68 if test -z "$datadir" 69 then 70 datadir=/usr/local/mysql/data 71 fi 72 sbindir=/usr/local/mysql/bin 73 libexecdir=/usr/local/mysql/bin 74 else 75 bindir="$basedir/bin" 76 if test -z "$datadir" 77 then 78 datadir="$basedir/data" 79 fi 80 sbindir="$basedir/sbin" 81 libexecdir="$basedir/libexec" 82 fi #将如上代码块中涉及到的/usr/local相关目录根据我们的规划进行修改 64 if test -z "$basedir" 65 then 66 basedir=/mysql/app/mysql 67 bindir=/mysql/app/mysql/bin 68 if test -z "$datadir" 69 then 70 datadir=/mysql/data/3306/data 71 fi 72 sbindir=/mysql/app/mysql/bin 73 libexecdir=/mysql/app/mysql/bin 74 else 75 bindir="$basedir/bin" 76 if test -z "$datadir" 77 then 78 datadir="$basedir/data" 79 fi 80 sbindir="$basedir/sbin" 81 libexecdir="$basedir/libexec" 82 fi 因为默认是去读的/etc/my.cnf，启动这个复制出来的mysql文件还是不能启动mysql服务，规范的情况下我们的配置文件不往系统里放（不放在/etc目录下，因为一般OS安装在一块单独的磁盘），不方便管理不方便备份，系统一旦坏了，文件就没了。 #修改 266 $bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null & 加个参数：指向我们建立的my.cnf文件 改为： 266 $bindir/mysqld_safe --defaults-file=/mysql/data/3306/my.cnf --datadir="$datadir" --pid-file="$mysql d_pid_file_path" $other_args >/dev/null &
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
5、修改权限启动MySQL
#修改权限 chown -R mysql:mysql /mysql #尝试启动MySQL #启动数据库服务： [root@itpuxdb 3306]# service mysql start Starting MySQL.The server quit without updating PID file (/[失败]data/3306/mysql.pid). #查看报错信息 [root@itpuxdb 3306]# tail -100f /mysql/log/3306/itpuxdb-error.err 2023-08-12T18:33:40.069204+08:00 0 [Warning] [MY-011068] [Server] The syntax 'log_slow_slave_statements' is deprecated and will be removed in a future release. Please use log_slow_replica_statements instead. 2023-08-12T18:33:40.069230+08:00 0 [Warning] [MY-011070] [Server] 'binlog_format' is deprecated and will be removed in a future release. 2023-08-12T18:33:40.069361+08:00 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead. 2023-08-12T18:33:40.069389+08:00 0 [System] [MY-010116] [Server] /mysql/app/mysql/bin/mysqld (mysqld 8.0.34) starting as process 8142 mysqld: File '/mysql/log/3306/binlog/itpuxdb-binlog.index' not found (OS errno 2 - No such file or directory) 2023-08-12T18:33:40.072133+08:00 0 [ERROR] [MY-010119] [Server] Aborting 2023-08-12T18:33:40.072281+08:00 0 [System] [MY-010910] [Server] /mysql/app/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.34) MySQL Community Server - GPL. #如上提示 File '/mysql/log/3306/binlog/itpuxdb-binlog.index' not found (OS errno 2 - No such file or directory) #创建对应目录 mkdir /mysql/log/3306/binlog chown mysql:mysql /mysql/log/3306/binlog #重新启动 service mysql start #查看后台信息 [root@itpuxdb 3306]# tail -100f /mysql/log/3306/itpuxdb-error.err 2023-08-12T18:39:45.721519+08:00 0 [Warning] [MY-011068] [Server] The syntax 'log_slow_slave_statements' is deprecated and will be removed in a future release. Please use log_slow_replica_statements instead. 2023-08-12T18:39:45.721544+08:00 0 [Warning] [MY-011070] [Server] 'binlog_format' is deprecated and will be removed in a future release. 2023-08-12T18:39:45.721766+08:00 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead. 2023-08-12T18:39:45.721798+08:00 0 [System] [MY-010116] [Server] /mysql/app/mysql/bin/mysqld (mysqld 8.0.34) starting as process 10476 2023-08-12T18:39:45.742785+08:00 0 [Warning] [MY-013267] [InnoDB] The setting INNODB_UNDO_TABLESPACES is deprecated and is no longer used. InnoDB always creates 2 undo tablespaces to start with. If you need more, please use CREATE UNDO TABLESPACE. 2023-08-12T18:39:45.742814+08:00 0 [Warning] [MY-013907] [InnoDB] Deprecated configuration parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute innodb_redo_log_capacity=419430400. Please use innodb_redo_log_capacity instead. 2023-08-12T18:39:45.745048+08:00 1 [System] [MY-011012] [Server] Starting upgrade of data directory. 2023-08-12T18:39:45.745121+08:00 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2023-08-12T18:39:49.385103+08:00 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2023-08-12T18:39:52.810972+08:00 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data. 2023-08-12T18:39:53.972450+08:00 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80034' started. 2023-08-12T18:40:01.393930+08:00 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80034' completed. 2023-08-12T18:40:02.907693+08:00 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2023-08-12T18:40:02.907945+08:00 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel. 2023-08-12T18:40:03.433234+08:00 0 [System] [MY-010931] [Server] /mysql/app/mysql/bin/mysqld: ready for connections. Version: '8.0.34' socket: '/mysql/data/3306/mysql.sock' port: 3306 MySQL Community Server - GPL. 2023-08-12T18:40:03.433359+08:00 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock #如上后台提醒版本升级成功了
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
6、升级数据字典（非必要）
#如下是当前mysql库里面的文件结构 [root@itpuxdb mysql]# pwd /mysql/data/3306/data/mysql [root@itpuxdb mysql]# ll total 32 -rw-r----- 1 mysql mysql 5975 Aug 12 18:40 general_log_394.sdi -rw-r----- 1 mysql mysql 35 Aug 12 18:40 general_log.CSM -rw-r----- 1 mysql mysql 0 Aug 12 18:39 general_log.CSV -rw-r----- 1 mysql mysql 12504 Aug 12 18:40 slow_log_396.sdi -rw-r----- 1 mysql mysql 35 Aug 12 18:40 slow_log.CSM -rw-r----- 1 mysql mysql 0 Jan 31 2021 slow_log.CSV [root@itpuxdb mysql]# #升级数据字典 [root@itpuxdb mysql]# mysql_upgrade -uroot -p -S /mysql/data/3306/mysql.sock The mysql_upgrade client is now deprecated. The actions executed by the upgrade client are now done by the server. To upgrade, please start the new MySQL binary with the older data directory. Repairing user tables is done automatically. Restart is not required after upgrade. The upgrade process automatically starts on running a new MySQL binary with an older data directory. To avoid accidental upgrades, please use the --upgrade=NONE option with the MySQL binary. The option --upgrade=FORCE is also provided to run the server upgrade sequence on demand. It may be possible that the server upgrade fails due to a number of reasons. In that case, the upgrade sequence will run again during the next MySQL server start. If the server upgrade fails repeatedly, the server can be started with the --upgrade=MINIMAL option to start the server without executing the upgrade sequence, thus allowing users to manually rectify the problem. [root@itpuxdb mysql]# #如上提示从第5步骤的后台日志可以看出升级已经完成 #此处的提示告诉我们升级是自动完成的
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
mysql_upgrade客户端现在已经被弃用。升级客户端执行的操作现在由服务器执行。 要进行升级，请使用旧数据目录启动新的MySQL二进制文件。用户表的修复将自动完成。升级后不需要重新启动。
升级过程会在使用旧数据目录运行新的MySQL二进制文件时自动启动。为了避免意外升级，请在MySQL二进制文件中使用–upgrade=NONE选项。还提供了–upgrade=FORCE选项，以按需运行服务器升级序列。
由于多种原因，服务器升级可能会失败。在这种情况下，升级序列将在下次MySQL服务器启动时再次运行。如果服务器升级反复失败，可以使用–upgrade=MINIMAL选项启动服务器，以在不执行升级序列的情况下启动服务器，从而允许用户手动解决问题。
7、检查数据和版本信息
#升级后的版本 [root@itpuxdb mysql]# mysql --version mysql Ver 8.0.34 for Linux on x86_64 (MySQL Community Server - GPL) [root@itpuxdb mysql]# mysql -uroot -prootroot -e "select @@version" mysql: [Warning] Using a password on the command line interface can be insecure. +-----------+ | @@version | +-----------+ | 8.0.34 | +-----------+ [root@itpuxdb mysql]# mysql -uroot -prootroot -e "select version()" mysql: [Warning] Using a password on the command line interface can be insecure. +-----------+ | version() | +-----------+ | 8.0.34 | +-----------+ #查看数据 [root@itpuxdb mysql]# mysql -uroot -prootroot itpuxdb -e "show tables" mysql: [Warning] Using a password on the command line interface can be insecure. +-------------------+ | Tables_in_itpuxdb | +-------------------+ | bm | | dd | | dq | | gj | | gw | | itpux11 | | itpux12 | | itpux_m1 | | itpux_m5 | | itpux_obj | | itpux_sales | | itpux_yg | | jl | | yg | +-------------------+ [root@itpuxdb mysql]#
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
升级后中文字符乱码处理
#升级后发现表中的中文字符乱码 #对比两个版本的字符集设置 #原版本 mysql> show variables like 'collation_%'; +----------------------+-----------------+ | Variable_name | Value | +----------------------+-----------------+ | collation_connection | utf8_general_ci | | collation_database | utf8_general_ci | | collation_server | utf8_general_ci | +----------------------+-----------------+ 3 rows in set (0.00 sec) mysql> show variables like '%character%'; +--------------------------+----------------------------------------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /mysql/app/mysql-5.7.43-linux-glibc2.12-x86_64/share/charsets/ | +--------------------------+----------------------------------------------------------------+ 8 rows in set (0.00 sec) #升级后的版本 mysql> show variables like 'collation_%'; +----------------------+--------------------+ | Variable_name | Value | +----------------------+--------------------+ | collation_connection | latin1_swedish_ci | | collation_database | utf8mb3_general_ci | | collation_server | utf8mb4_0900_ai_ci | +----------------------+--------------------+ 3 rows in set (0.00 sec) mysql> show variables like '%character%'; +--------------------------+----------------------------------------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------------------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | utf8mb3 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | utf8mb4 | | character_set_system | utf8mb3 | | character_sets_dir | /mysql/app/mysql-8.0.34-linux-glibc2.12-x86_64/share/charsets/ | +--------------------------+----------------------------------------------------------------+ 8 rows in set (0.00 sec) #修改参数 #会话修改（立即生效） SET character_set_client = utf8 ; SET character_set_connection = utf8 ; SET character_set_database = utf8 ; set character_set_filesystem = 'utf8'; SET character_set_results = utf8 ; SET character_set_server = utf8 ; -- set character_set_system = 'utf8'; -- set character_sets_dir = 'utf8'; SET collation_connection = utf8_general_ci ; SET collation_database = utf8_general_ci ; SET collation_server = utf8_general_ci ; #修改my.cnf（重启生效） [client] default_character_set=utf8 [mysql] default_character_set=utf8 [mysqld] init_connect='SET collation_connection = utf8_general_ci' init_connect='SET NAMES utf8' character_set_server=utf8 collation_server=utf8_general_ci skip_character_set_client_handshake #修改后的结果 mysql> show variables like 'collation_%'; +----------------------+--------------------+ | Variable_name | Value | +----------------------+--------------------+ | collation_connection | utf8mb3_general_ci | | collation_database | utf8mb3_general_ci | | collation_server | utf8mb3_general_ci | +----------------------+--------------------+ 3 rows in set (0.00 sec) mysql> show variables like '%character%'; +--------------------------+----------------------------------------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------------------------------------+ | character_set_client | utf8mb3 | | character_set_connection | utf8mb3 | | character_set_database | utf8mb3 | | character_set_filesystem | binary | | character_set_results | utf8mb3 | | character_set_server | utf8mb3 | | character_set_system | utf8mb3 | | character_sets_dir | /mysql/app/mysql-8.0.34-linux-glibc2.12-x86_64/share/charsets/ | +--------------------------+----------------------------------------------------------------+ 8 rows in set (0.01 sec) mysql>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
小版本升级
5.7.20 to 5.7.43
1、在原版本创建测试数据库和测试数据
[root@itpuxdb app]# pwd /mysql/app [root@itpuxdb app]# [root@itpuxdb app]# ls -lsa *.sql 47380 -rw-r--r-- 1 mysql mysql 48513198 Mar 10 2018 itpuxdb.sql [root@itpuxdb app]# [root@itpuxdb app]# mysql -uroot -prootroot -e "create database itpuxdb;" [root@itpuxdb app]# mysql -uroot -prootroot itpuxdb < itpuxdb.sql [root@itpuxdb app]# mysql -uroot -prootroot itpuxdb -e "show tables" mysql: [Warning] Using a password on the command line interface can be insecure. +-------------------+ | Tables_in_itpuxdb | +-------------------+ | bm | | dd | | dq | | gj | | gw | | itpux11 | | itpux12 | | itpux_m1 | | itpux_m5 | | itpux_obj | | itpux_sales | | itpux_yg | | jl | | yg | +-------------------+ [root@itpuxdb app]#
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
2、下载解压新版安装包
#当前的版本 [root@itpuxdb ~]# mysql --version mysql Ver 14.14 Distrib 5.7.20, for linux-glibc2.12 (x86_64) using EditLine wrapper [root@itpuxdb ~]# [root@itpuxdb ~]# mysql -uroot -prootroot -e "select @@version" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | @@version | +------------+ | 5.7.20-log | +------------+ [root@itpuxdb ~]# mysql -uroot -prootroot -e "select version()" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | version() | +------------+ | 5.7.20-log | +------------+ [root@itpuxdb ~]# #老版本的目录 [root@itpuxdb app]# pwd /mysql/app [root@itpuxdb app]# ll total 603148 drwx------ 2 mysql mysql 16384 Jan 31 2021 lost+found lrwxrwxrwx 1 mysql mysql 35 Jan 31 2021 mysql -> mysql-5.7.20-linux-glibc2.12-x86_64 drwxr-xr-x 9 mysql mysql 4096 Jan 31 2021 mysql-5.7.20-linux-glibc2.12-x86_64 #上传解压高版本的代码包 [root@itpuxdb app]# ll mysql-5.7.43-linux-glibc2.12-x86_64.tar.gz -rw-r--r-- 1 root root 694611572 Aug 12 12:46 mysql-5.7.43-linux-glibc2.12-x86_64.tar.gz [root@itpuxdb app]# tar -zxvf mysql-5.7.43-linux-glibc2.12-x86_64.tar.gz [root@itpuxdb app]# ll drwx------ 2 mysql mysql 16384 Jan 31 2021 lost+found lrwxrwxrwx 1 mysql mysql 35 Jan 31 2021 mysql -> mysql-5.7.20-linux-glibc2.12-x86_64 drwxr-xr-x 9 mysql mysql 4096 Jan 31 2021 mysql-5.7.20-linux-glibc2.12-x86_64 drwxr-xr-x 9 root root 4096 Aug 12 22:54 mysql-5.7.43-linux-glibc2.12-x86_64 -rw-r--r-- 1 root root 694611572 Aug 12 12:46 mysql-5.7.43-linux-glibc2.12-x86_64.tar.gz [root@itpuxdb app]# #删除原链接，做新链接 [root@itpuxdb app]# rm -rf mysql [root@itpuxdb app]# ln -sf mysql-5.7.43-linux-glibc2.12-x86_64 mysql [root@itpuxdb app]# ll drwx------ 2 mysql mysql 16384 Jan 31 2021 lost+found lrwxrwxrwx 1 root root 35 Aug 12 22:55 mysql -> mysql-5.7.43-linux-glibc2.12-x86_64 drwxr-xr-x 9 mysql mysql 4096 Jan 31 2021 mysql-5.7.20-linux-glibc2.12-x86_64 drwxr-xr-x 9 root root 4096 Aug 12 22:54 mysql-5.7.43-linux-glibc2.12-x86_64 -rw-r--r-- 1 root root 694611572 Aug 12 12:46 mysql-5.7.43-linux-glibc2.12-x86_64.tar.gz [root@itpuxdb app]#
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
3、停止MySQL服务
#停止mysql数据库服务
service mysql stop
- 1
- 2
4、准备启动脚本
#将就目录下的mysql.server文件复制到新目录下
mv /mysql/app/mysql/support-files/mysql.server /mysql/app/mysql/support-files/mysql.server.bak
cp /mysql/app/mysql-5.7.20-linux-glibc2.12-x86_64/support-files/mysql.server /mysql/app/mysql/support-files/mysql.server
- 1
- 2
- 3
mysql.server修改
[root@itpuxdb support-files]# vim mysql.server #修改 46 basedir= 47 datadir= #改为 46 basedir=/mysql/app/mysql 47 datadir=/mysql/data/3306/data #修改 63 mysqld_pid_file_path= #改为 63 mysqld_pid_file_path=/mysql/data/3306/mysql.pid #修改 64 if test -z "$basedir" 65 then 66 basedir=/usr/local/mysql 67 bindir=/usr/local/mysql/bin 68 if test -z "$datadir" 69 then 70 datadir=/usr/local/mysql/data 71 fi 72 sbindir=/usr/local/mysql/bin 73 libexecdir=/usr/local/mysql/bin 74 else 75 bindir="$basedir/bin" 76 if test -z "$datadir" 77 then 78 datadir="$basedir/data" 79 fi 80 sbindir="$basedir/sbin" 81 libexecdir="$basedir/libexec" 82 fi #将如上代码块中涉及到的/usr/local相关目录根据我们的规划进行修改 64 if test -z "$basedir" 65 then 66 basedir=/mysql/app/mysql 67 bindir=/mysql/app/mysql/bin 68 if test -z "$datadir" 69 then 70 datadir=/mysql/data/3306/data 71 fi 72 sbindir=/mysql/app/mysql/bin 73 libexecdir=/mysql/app/mysql/bin 74 else 75 bindir="$basedir/bin" 76 if test -z "$datadir" 77 then 78 datadir="$basedir/data" 79 fi 80 sbindir="$basedir/sbin" 81 libexecdir="$basedir/libexec" 82 fi 因为默认是去读的/etc/my.cnf，启动这个复制出来的mysql文件还是不能启动mysql服务，规范的情况下我们的配置文件不往系统里放（不放在/etc目录下，因为一般OS安装在一块单独的磁盘），不方便管理不方便备份，系统一旦坏了，文件就没了。 #修改 266 $bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null & 加个参数：指向我们建立的my.cnf文件 改为： 266 $bindir/mysqld_safe --defaults-file=/mysql/data/3306/my.cnf --datadir="$datadir" --pid-file="$mysql d_pid_file_path" $other_args >/dev/null &
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
5、修改权限启动MySQL
#修改权限 chown -R mysql:mysql /mysql #尝试启动MySQL [root@itpuxdb ~]# service mysql start Starting MySQL.... [确定] [root@itpuxdb ~]# #查看后台信息 2023-08-12T23:08:10.665733+08:00 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled 2023-08-12T23:08:10.665798+08:00 0 [Note] /mysql/app/mysql/bin/mysqld (mysqld 5.7.43-log) starting as process 5015 ... 2023-08-12T23:08:10.673172+08:00 0 [Note] InnoDB: PUNCH HOLE support available 2023-08-12T23:08:10.673287+08:00 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2023-08-12T23:08:10.673304+08:00 0 [Note] InnoDB: Uses event mutexes 2023-08-12T23:08:10.673320+08:00 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier 2023-08-12T23:08:10.673331+08:00 0 [Note] InnoDB: Compressed tables use zlib 1.2.13 2023-08-12T23:08:10.673343+08:00 0 [Note] InnoDB: Using Linux native AIO 2023-08-12T23:08:10.673375+08:00 0 [Note] InnoDB: Adjusting innodb_buffer_pool_instances from 8 to 1 since innodb_buffer_pool_size is less than 1024 MiB 2023-08-12T23:08:10.674107+08:00 0 [Note] InnoDB: Number of pools: 1 2023-08-12T23:08:10.674345+08:00 0 [Note] InnoDB: Using CPU crc32 instructions 2023-08-12T23:08:10.676971+08:00 0 [Note] InnoDB: Initializing buffer pool, total size = 512M, instances = 1, chunk size = 128M 2023-08-12T23:08:10.714812+08:00 0 [Note] InnoDB: Completed initialization of buffer pool 2023-08-12T23:08:10.718793+08:00 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2023-08-12T23:08:10.731445+08:00 0 [Note] InnoDB: Opened 3 undo tablespaces 2023-08-12T23:08:10.731461+08:00 0 [Note] InnoDB: 3 undo tablespaces made active 2023-08-12T23:08:10.731622+08:00 0 [Note] InnoDB: Highest supported file format is Barracuda. 2023-08-12T23:08:10.753473+08:00 0 [Note] InnoDB: Creating shared tablespace for temporary tables 2023-08-12T23:08:10.753548+08:00 0 [Note] InnoDB: Setting file './ibtmp1' size to 200 MB. Physically writing the file full; Please wait ... 2023-08-12T23:08:10.753629+08:00 0 [Note] InnoDB: Progress in MB: 100 200 2023-08-12T23:08:11.137444+08:00 0 [Note] InnoDB: File './ibtmp1' size is now 200 MB. 2023-08-12T23:08:11.138056+08:00 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active. 2023-08-12T23:08:11.138079+08:00 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active. 2023-08-12T23:08:11.138610+08:00 0 [Note] InnoDB: Waiting for purge to start 2023-08-12T23:08:11.188920+08:00 0 [Note] InnoDB: 5.7.43 started; log sequence number 6437688399 2023-08-12T23:08:11.189593+08:00 0 [Note] InnoDB: Loading buffer pool(s) from /mysql/data/3306/data/ib_buffer_pool 2023-08-12T23:08:11.189822+08:00 0 [Note] Plugin 'FEDERATED' is disabled. 2023-08-12T23:08:11.196795+08:00 0 [Warning] InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name. Please run mysql_upgrade 2023-08-12T23:08:11.196883+08:00 0 [Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade 2023-08-12T23:08:11.264568+08:00 0 [Note] InnoDB: Buffer pool(s) load completed at 230812 23:08:11 2023-08-12T23:08:12.568690+08:00 0 [Note] Auto generated SSL certificates are placed in data directory. 2023-08-12T23:08:12.568724+08:00 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher. 2023-08-12T23:08:12.568728+08:00 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher. 2023-08-12T23:08:12.571825+08:00 0 [Warning] CA certificate ca.pem is self signed. 2023-08-12T23:08:13.469679+08:00 0 [Note] Auto generated RSA key files are placed in data directory. 2023-08-12T23:08:13.471355+08:00 0 [Note] Server hostname (bind-address): '192.168.1.51'; port: 3306 2023-08-12T23:08:13.471385+08:00 0 [Note] - '192.168.1.51' resolves to '192.168.1.51'; 2023-08-12T23:08:13.471411+08:00 0 [Note] Server socket created on IP: '192.168.1.51'. 2023-08-12T23:08:13.483979+08:00 0 [Note] Failed to start slave threads for channel '' 2023-08-12T23:08:13.516934+08:00 0 [Warning] Optional native table 'performance_schema'.'processlist' has the wrong structure or is missing. 2023-08-12T23:08:13.517641+08:00 0 [Note] Event Scheduler: Loaded 0 events 2023-08-12T23:08:13.518414+08:00 0 [Note] /mysql/app/mysql/bin/mysqld: ready for connections. Version: '5.7.43-log' socket: '/mysql/data/3306/mysql.sock' port: 3306 MySQL Community Server (GPL) #如上后台没有任何升级信息，小版本升级几乎无感
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
6、升级数据字典
#如下是当前mysql库里面的文件结构 [root@itpuxdb mysql]# pwd /mysql/data/3306/data/mysql [root@itpuxdb mysql]# pwd /mysql/data/3306/data/mysql [root@itpuxdb mysql]# ll -h total 12M -rw-r----- 1 mysql mysql 8.7K Jan 31 2021 columns_priv.frm -rw-r----- 1 mysql mysql 0 Jan 31 2021 columns_priv.MYD -rw-r----- 1 mysql mysql 4.0K Jan 31 2021 columns_priv.MYI -rw-r----- 1 mysql mysql 9.4K Jan 31 2021 db.frm -rw-r----- 1 mysql mysql 2.0K Feb 1 2021 db.MYD -rw-r----- 1 mysql mysql 5.0K Feb 1 2021 db.MYI -rw-r----- 1 mysql mysql 61 Jan 31 2021 db.opt -rw-r----- 1 mysql mysql 8.6K Jan 31 2021 engine_cost.frm -rw-r----- 1 mysql mysql 96K Jan 31 2021 engine_cost.ibd -rw-r----- 1 mysql mysql 10K Jan 31 2021 event.frm -rw-r----- 1 mysql mysql 0 Jan 31 2021 event.MYD -rw-r----- 1 mysql mysql 2.0K Jan 31 2021 event.MYI -rw-r----- 1 mysql mysql 8.5K Jan 31 2021 func.frm -rw-r----- 1 mysql mysql 0 Jan 31 2021 func.MYD -rw-r----- 1 mysql mysql 1.0K Jan 31 2021 func.MYI -rw-r----- 1 mysql mysql 35 Mar 14 2021 general_log.CSM -rw-r----- 1 mysql mysql 0 Jan 31 2021 general_log.CSV -rw-r----- 1 mysql mysql 8.6K Jan 31 2021 general_log.frm -rw-r----- 1 mysql mysql 8.6K Jan 31 2021 gtid_executed.frm -rw-r----- 1 mysql mysql 96K Jan 31 2021 gtid_executed.ibd -rw-r----- 1 mysql mysql 8.5K Jan 31 2021 help_category.frm -rw-r----- 1 mysql mysql 112K Jan 31 2021 help_category.ibd -rw-r----- 1 mysql mysql 8.5K Jan 31 2021 help_keyword.frm -rw-r----- 1 mysql mysql 240K Jan 31 2021 help_keyword.ibd -rw-r----- 1 mysql mysql 8.5K Jan 31 2021 help_relation.frm -rw-r----- 1 mysql mysql 144K Jan 31 2021 help_relation.ibd -rw-r----- 1 mysql mysql 8.6K Jan 31 2021 help_topic.frm -rw-r----- 1 mysql mysql 9.0M Jan 31 2021 help_topic.ibd -rw-r----- 1 mysql mysql 13K Jan 31 2021 innodb_index_stats.frm -rw-r----- 1 mysql mysql 128K Aug 12 22:53 innodb_index_stats.ibd -rw-r----- 1 mysql mysql 8.7K Jan 31 2021 innodb_table_stats.frm -rw-r----- 1 mysql mysql 96K Aug 12 22:53 innodb_table_stats.ibd -rw-r----- 1 mysql mysql 8.8K Jan 31 2021 ndb_binlog_index.frm -rw-r----- 1 mysql mysql 0 Jan 31 2021 ndb_binlog_index.MYD -rw-r----- 1 mysql mysql 1.0K Jan 31 2021 ndb_binlog_index.MYI -rw-r----- 1 mysql mysql 8.4K Jan 31 2021 plugin.frm -rw-r----- 1 mysql mysql 96K Jan 31 2021 plugin.ibd -rw-r----- 1 mysql mysql 9.8K Jan 31 2021 proc.frm -rw-r----- 1 mysql mysql 294K Jan 31 2021 proc.MYD -rw-r----- 1 mysql mysql 4.0K Jan 31 2021 proc.MYI -rw-r----- 1 mysql mysql 8.7K Jan 31 2021 procs_priv.frm -rw-r----- 1 mysql mysql 0 Jan 31 2021 procs_priv.MYD -rw-r----- 1 mysql mysql 4.0K Jan 31 2021 procs_priv.MYI -rw-r----- 1 mysql mysql 8.6K Jan 31 2021 proxies_priv.frm -rw-r----- 1 mysql mysql 837 Jan 31 2021 proxies_priv.MYD -rw-r----- 1 mysql mysql 9.0K Jan 31 2021 proxies_priv.MYI -rw-r----- 1 mysql mysql 8.5K Jan 31 2021 server_cost.frm -rw-r----- 1 mysql mysql 96K Jan 31 2021 server_cost.ibd -rw-r----- 1 mysql mysql 8.7K Jan 31 2021 servers.frm -rw-r----- 1 mysql mysql 96K Jan 31 2021 servers.ibd -rw-r----- 1 mysql mysql 11K Jan 31 2021 slave_master_info.frm -rw-r----- 1 mysql mysql 96K Jan 31 2021 slave_master_info.ibd -rw-r----- 1 mysql mysql 9.3K Jan 31 2021 slave_relay_log_info.frm -rw-r----- 1 mysql mysql 96K Jan 31 2021 slave_relay_log_info.ibd -rw-r----- 1 mysql mysql 9.2K Jan 31 2021 slave_worker_info.frm -rw-r----- 1 mysql mysql 96K Jan 31 2021 slave_worker_info.ibd -rw-r----- 1 mysql mysql 35 Mar 14 2021 slow_log.CSM -rw-r----- 1 mysql mysql 0 Jan 31 2021 slow_log.CSV -rw-r----- 1 mysql mysql 8.9K Jan 31 2021 slow_log.frm -rw-r----- 1 mysql mysql 8.8K Jan 31 2021 tables_priv.frm -rw-r----- 1 mysql mysql 1.9K Jan 31 2021 tables_priv.MYD -rw-r----- 1 mysql mysql 9.0K Jan 31 2021 tables_priv.MYI -rw-r----- 1 mysql mysql 8.5K Jan 31 2021 time_zone.frm -rw-r----- 1 mysql mysql 96K Jan 31 2021 time_zone.ibd -rw-r----- 1 mysql mysql 8.5K Jan 31 2021 time_zone_leap_second.frm -rw-r----- 1 mysql mysql 96K Jan 31 2021 time_zone_leap_second.ibd -rw-r----- 1 mysql mysql 8.5K Jan 31 2021 time_zone_name.frm -rw-r----- 1 mysql mysql 96K Jan 31 2021 time_zone_name.ibd -rw-r----- 1 mysql mysql 8.5K Jan 31 2021 time_zone_transition.frm -rw-r----- 1 mysql mysql 96K Jan 31 2021 time_zone_transition.ibd -rw-r----- 1 mysql mysql 8.6K Jan 31 2021 time_zone_transition_type.frm -rw-r----- 1 mysql mysql 96K Jan 31 2021 time_zone_transition_type.ibd -rw-r----- 1 mysql mysql 11K Jan 31 2021 user.frm -rw-r----- 1 mysql mysql 740 Feb 1 2021 user.MYD -rw-r----- 1 mysql mysql 4.0K Feb 1 2021 user.MYI [root@itpuxdb mysql]# #升级数据字典 [root@itpuxdb mysql]# mysql_upgrade -uroot -p -S /mysql/data/3306/mysql.sock Enter password: Checking if update is needed. Checking server version. Running queries to upgrade MySQL server. Checking system database. mysql.columns_priv OK mysql.db OK mysql.engine_cost OK mysql.event OK mysql.func OK mysql.general_log OK mysql.gtid_executed OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.server_cost OK mysql.servers OK mysql.slave_master_info OK mysql.slave_relay_log_info OK mysql.slave_worker_info OK mysql.slow_log OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK Found outdated sys schema version 1.5.1. Upgrading the sys schema. Checking databases. itpuxdb.bm OK itpuxdb.dd OK itpuxdb.dq OK itpuxdb.gj OK itpuxdb.gw OK itpuxdb.itpux11 OK itpuxdb.itpux12 OK itpuxdb.itpux_m1 OK itpuxdb.itpux_m5 OK itpuxdb.itpux_obj OK itpuxdb.itpux_sales OK itpuxdb.itpux_yg OK itpuxdb.jl OK itpuxdb.yg OK sys.sys_config OK Upgrade process completed successfully. Checking if update is needed. [root@itpuxdb mysql]# #升级后的文件结构 [root@itpuxdb mysql]# ll -h total 9.7M -rw-r----- 1 mysql mysql 8.7K Aug 12 23:11 columns_priv.frm -rw-r----- 1 mysql mysql 0 Aug 12 23:11 columns_priv.MYD -rw-r----- 1 mysql mysql 4.0K Aug 12 23:11 columns_priv.MYI -rw-r----- 1 mysql mysql 9.4K Aug 12 23:11 db.frm -rw-r----- 1 mysql mysql 2.0K Aug 12 23:11 db.MYD -rw-r----- 1 mysql mysql 5.0K Aug 12 23:11 db.MYI -rw-r----- 1 mysql mysql 61 Jan 31 2021 db.opt -rw-r----- 1 mysql mysql 8.6K Jan 31 2021 engine_cost.frm -rw-r----- 1 mysql mysql 96K Jan 31 2021 engine_cost.ibd -rw-r----- 1 mysql mysql 10K Aug 12 23:11 event.frm -rw-r----- 1 mysql mysql 0 Aug 12 23:11 event.MYD -rw-r----- 1 mysql mysql 2.0K Aug 12 23:11 event.MYI -rw-r----- 1 mysql mysql 8.5K Aug 12 23:11 func.frm -rw-r----- 1 mysql mysql 0 Aug 12 23:11 func.MYD -rw-r----- 1 mysql mysql 1.0K Aug 12 23:11 func.MYI -rw-r----- 1 mysql mysql 35 Aug 12 23:11 general_log.CSM -rw-r----- 1 mysql mysql 0 Aug 12 23:11 general_log.CSV -rw-r----- 1 mysql mysql 8.6K Aug 12 23:11 general_log.frm -rw-r----- 1 mysql mysql 8.6K Aug 12 23:11 gtid_executed.frm -rw-r----- 1 mysql mysql 96K Jan 31 2021 gtid_executed.ibd -rw-r----- 1 mysql mysql 8.5K Aug 12 23:11 help_category.frm -rw-r----- 1 mysql mysql 112K Aug 12 23:11 help_category.ibd -rw-r----- 1 mysql mysql 8.5K Aug 12 23:11 help_keyword.frm -rw-r----- 1 mysql mysql 240K Aug 12 23:11 help_keyword.ibd -rw-r----- 1 mysql mysql 8.5K Aug 12 23:11 help_relation.frm -rw-r----- 1 mysql mysql 144K Aug 12 23:11 help_relation.ibd -rw-r----- 1 mysql mysql 8.6K Aug 12 23:11 help_topic.frm -rw-r----- 1 mysql mysql 7.0M Aug 12 23:11 help_topic.ibd -rw-r----- 1 mysql mysql 13K Aug 12 23:11 innodb_index_stats.frm -rw-r----- 1 mysql mysql 96K Aug 12 23:11 innodb_index_stats.ibd -rw-r----- 1 mysql mysql 8.7K Aug 12 23:11 innodb_table_stats.frm -rw-r----- 1 mysql mysql 96K Aug 12 23:11 innodb_table_stats.ibd -rw-r----- 1 mysql mysql 8.8K Aug 12 23:11 ndb_binlog_index.frm -rw-r----- 1 mysql mysql 0 Jan 31 2021 ndb_binlog_index.MYD -rw-r----- 1 mysql mysql 1.0K Jan 31 2021 ndb_binlog_index.MYI -rw-r----- 1 mysql mysql 8.4K Aug 12 23:11 plugin.frm -rw-r----- 1 mysql mysql 96K Aug 12 23:11 plugin.ibd -rw-r----- 1 mysql mysql 9.8K Aug 12 23:11 proc.frm -rw-r----- 1 mysql mysql 295K Aug 12 23:11 proc.MYD -rw-r----- 1 mysql mysql 4.0K Aug 12 23:11 proc.MYI -rw-r----- 1 mysql mysql 8.7K Aug 12 23:11 procs_priv.frm -rw-r----- 1 mysql mysql 0 Aug 12 23:11 procs_priv.MYD -rw-r----- 1 mysql mysql 4.0K Aug 12 23:11 procs_priv.MYI -rw-r----- 1 mysql mysql 8.6K Aug 12 23:11 proxies_priv.frm -rw-r----- 1 mysql mysql 837 Jan 31 2021 proxies_priv.MYD -rw-r----- 1 mysql mysql 9.0K Jan 31 2021 proxies_priv.MYI -rw-r----- 1 mysql mysql 8.5K Jan 31 2021 server_cost.frm -rw-r----- 1 mysql mysql 96K Jan 31 2021 server_cost.ibd -rw-r----- 1 mysql mysql 8.7K Aug 12 23:11 servers.frm -rw-r----- 1 mysql mysql 96K Aug 12 23:11 servers.ibd -rw-r----- 1 mysql mysql 11K Aug 12 23:11 slave_master_info.frm -rw-r----- 1 mysql mysql 96K Jan 31 2021 slave_master_info.ibd -rw-r----- 1 mysql mysql 9.3K Aug 12 23:11 slave_relay_log_info.frm -rw-r----- 1 mysql mysql 96K Jan 31 2021 slave_relay_log_info.ibd -rw-r----- 1 mysql mysql 9.2K Aug 12 23:11 slave_worker_info.frm -rw-r----- 1 mysql mysql 96K Jan 31 2021 slave_worker_info.ibd -rw-r----- 1 mysql mysql 35 Aug 12 23:11 slow_log.CSM -rw-r----- 1 mysql mysql 0 Jan 31 2021 slow_log.CSV -rw-r----- 1 mysql mysql 8.9K Aug 12 23:11 slow_log.frm -rw-r----- 1 mysql mysql 8.8K Aug 12 23:11 tables_priv.frm -rw-r----- 1 mysql mysql 1.9K Aug 12 23:11 tables_priv.MYD -rw-r----- 1 mysql mysql 9.0K Aug 12 23:11 tables_priv.MYI -rw-r----- 1 mysql mysql 8.5K Aug 12 23:11 time_zone.frm -rw-r----- 1 mysql mysql 96K Aug 12 23:11 time_zone.ibd -rw-r----- 1 mysql mysql 8.5K Aug 12 23:11 time_zone_leap_second.frm -rw-r----- 1 mysql mysql 96K Aug 12 23:11 time_zone_leap_second.ibd -rw-r----- 1 mysql mysql 8.5K Aug 12 23:11 time_zone_name.frm -rw-r----- 1 mysql mysql 96K Aug 12 23:11 time_zone_name.ibd -rw-r----- 1 mysql mysql 8.5K Aug 12 23:11 time_zone_transition.frm -rw-r----- 1 mysql mysql 96K Aug 12 23:11 time_zone_transition.ibd -rw-r----- 1 mysql mysql 8.6K Aug 12 23:11 time_zone_transition_type.frm -rw-r----- 1 mysql mysql 96K Aug 12 23:11 time_zone_transition_type.ibd -rw-r----- 1 mysql mysql 11K Aug 12 23:11 user.frm -rw-r----- 1 mysql mysql 740 Aug 12 23:11 user.MYD -rw-r----- 1 mysql mysql 4.0K Aug 12 23:11 user.MYI [root@itpuxdb mysql]#
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
7、检查数据和版本信息
#升级后的版本 [root@itpuxdb mysql]# mysql --version mysql Ver 14.14 Distrib 5.7.43, for linux-glibc2.12 (x86_64) using EditLine wrapper [root@itpuxdb mysql]# mysql -uroot -prootroot -e "select @@version" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | @@version | +------------+ | 5.7.43-log | +------------+ [root@itpuxdb mysql]# mysql -uroot -prootroot -e "select version()" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | version() | +------------+ | 5.7.43-log | +------------+ [root@itpuxdb mysql]# mysql -uroot -prootroot itpuxdb -e "show tables" mysql: [Warning] Using a password on the command line interface can be insecure. +-------------------+ | Tables_in_itpuxdb | +-------------------+ | bm | | dd | | dq | | gj | | gw | | itpux11 | | itpux12 | | itpux_m1 | | itpux_m5 | | itpux_obj | | itpux_sales | | itpux_yg | | jl | | yg | +-------------------+ [root@itpuxdb mysql]#
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
对MySQL做升级，若升级失败了怎么办
在做升级时，一般创建一个从库进行升级，若升级失败，也不会影响到主库；
若升级成功，测试也成功，便会将其他的从库也逐渐升级到新版本，最后将主库下线，提升一个从库做新主库，对旧主库进行版本升级。
声明：本文内容由网友自发贡献，转载请注明出处：【wpsshop博客】
推荐阅读
相关标签
