centos7下mysql-5.7.18升级到mysql-5.7.35(两种升级方法)

后续mysql更新到了``````````

 

开始升级mysql (mysql-5.7.18升到mysql-5.7.35)
登陆mysql网站 www.mysql.com
mysql安装包下载地址：
https://downloads.mysql.com/archives/community/

*************************第一种升级方法*******************************
mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz

#查看mysql版本号

root@test bin]# ./mysql -V
./mysql Ver 14.14 Distrib 5.7.18, for linux-glibc2.5 (x86_64) using EditLine wrapper

#开始备份数据库(备份脚本)

[root@test ~]# cat >> back.sh << EOF
#!/bin/bash


mysqldatabase="sys mysql performance_schema information_schema sltas_ods"

date=`date +%Y%m%d `
passwd='123456'
for i in ${mysqldatabase}

do

mysqldump -uroot -p${passwd} --databases ${i} > /home/sltas_ods_bak/sltas_ods_${date}.sql

done
EOF

备份完成之后，将mysql停掉

[root@test bin]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS!

#解压新的mysql包

[root@test bin]# tar xf mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz

#将旧的mysql的bin目录备份打包

[root@test bin]# cd /usr/bin
[root@test bin]# tar zcf mysql.tar.gz mysql
[root@test bin]# tar zcf mysqldump.tar.gz mysqldump
[root@test bin]# rm -rf mysql
[root@test bin]# rm -rf mysqldump

[root@test mysql]# cd /usr/local/mysql
[root@test mysql]# tar zcf bin_5.7.18.tar.gz bin
[root@test mysql]# rm -rf bin

[root@test init.d]# cd /etc/init.d/
[root@test init.d]# tar zcf mysqld_5.7.18.tar.gz mysqld
[root@test init.d]# rm -rf mysqld

#将新解压的mysql-5.7.35的bin目录复制到mysql-5.7.18目录下

[root@test]# cd /home/mysql-5.7.35-linux-glibc2.12-x86_64
[root@test]# /bin/cp bin /usr/local/mysql/
[root@test]# cd /usr/local/mysql/bin/
[root@test]# cp mysql /usr/bin
[root@test]# cp mysqldump /usr/bin

#将新的mysqld版本copy到/etc/init.d/ 下

[root@test]# cp /home/mysql-5.7.35-linux-glibc2.12-x86_64/support-files/mysql.server /etc/init.d/mysqld

#修改参数

[root@test]# vim /etc/init.d/mysqld
basedir=/usr/local/mysql
datadir=/home/data/sltas_ods

#启动mysql

[root@test bin]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!

#查看版本号

[root@test bin]# ./mysql -V
./mysql Ver 14.14 Distrib 5.7.35, for linux-glibc2.12 (x86_64) using EditLine wrapper

#执行mysql_upgrade进行检查

[root@test bin]# ./mysql_upgrade -uroot -p --force --skip-version-check -S /tmp/mysql.sock

#查看数据是否正确，检查账号密码是否可用。

 

*************************第二种二进制数据库升级方法*********************************
开始升级mysql (mysql-5.7.18升到mysql-5.7.35)
登陆mysql网站 www.mysql.com
mysql安装包下载地址：
https://downloads.mysql.com/archives/community/

#重新初始化mysql,版本为mysql5.7.35

#上传mysql新版本的部署包

[root@test]# rz
mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz
[root@test]# tar xf mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz
[root@test]# mv mysql-5.7.35-linux-glibc2.12-x86_64 /home/mysql3307

#创建mysql数据目录

[root@test]# mkdir /home/mysql3307/data -p
[root@test]# chown mysql.mysql /home/mysql3307/* -R

#创建数据库配置文件

[root@test]# vim /etc/my3307.cnf
[root@test]# cat /etc/my3307.cnf | egrep -v '^#|^$'
[client]
port = 3307
socket = /tmp/mysql3307.sock
[mysqld]
port = 3307
socket = /tmp/mysql3307.sock
datadir = /home/mysql3307/data
skip-external-locking
max_connections = 100000
max_connect_errors = 6000
open_files_limit = 65535
table_open_cache = 128
key_buffer_size = 16M
max_allowed_packet = 1024M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log_error = /home/mysql3307/data/mysql-error.log
slow_query_log = 1
lower_case_table_names=1
long_query_time = 1
slow_query_log_file = /home/mysql3307/data/mysql-slow.log
expire_logs_days = 7
character-set-server=utf8
collation-server=utf8_general_ci

performance_schema = 0
explicit_defaults_for_timestamp
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
log-bin=mysql-bin
binlog_format=row
server-id = 3
binlog-rows-query-log-events=true
binlog_row_image=full
log-bin-use-v1-row-events=1
binlog_cache_size=2M
max_binlog_size=512M
sync_binlog=0
collation-server = utf8_unicode_ci
innodb_data_home_dir = /home/mysql3307/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /home/mysql3307/data
innodb_buffer_pool_size = 64M
innodb_file_per_table = 1
innodb_open_files = 500
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

interactive_timeout = 28800
wait_timeout = 28800
plugin-load = validate_password.so
validate_password_length = 10
validate_password_policy = 1
validate-password = FORCE_PLUS_PERMANENT
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
expire_logs_days = 10

#############################################################################
#初始化数据库

第一次初始化报错
[root@test bin]# ./mysqld_safe --defaults-file=/etc/my3307.cnf --user=mysql &
[1] 76836
[root@test bin]# Logging to '/home/mysql3307/data/mysql-error.log'.
2021-12-02T03:58:51.029328Z mysqld_safe Starting mysqld daemon with databases from /home/mysql3307/data
2021-12-02T03:58:53.205213Z mysqld_safe mysqld from pid file /home/mysql3307/data/test.pid ended

[1]+ 完成 ./mysqld_safe --defaults-file=/etc/my3307.cnf --user=mysql
[root@test bin]#
[root@test bin]#
[root@test bin]# ps -ef | grep mysql
root 67800 65346 0 10:29 pts/1 00:00:00 mysql -uroot -p
root 77755 69643 0 11:59 pts/4 00:00:00 grep --color=auto mysql

查看日志根据日志排查错误

vim /home/mysql3307/data/mysql-error.log

#########################################################################################################

#再次初始化没有报错

[root@test]# cd /home/mysql3307/bin/

[root@test]# ./mysql_install_db --datadir=/home/mysql3307/data --basedir=/home/mysql3307 --user=mysql
2021-12-02 11:57:20 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
2021-12-02 11:57:24 [WARNING] The bootstrap log isn't empty:
2021-12-02 11:57:24 [WARNING] 2021-12-02T03:57:20.836189Z 0 [Warning] --bootstrap is deprecated. Please consider using --initialize instead
2021-12-02T03:57:20.836793Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2021-12-02T03:57:20.836810Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)

#初始化数据库成功，开启数据库

[root@test]# cd /home/mysql3307/bin/
[root@test]# ./mysqld_safe --defaults-file=/etc/my3307.cnf --user=mysql &
[1] 79471
[root@test bin]# 2021-12-02T04:32:04.065550Z mysqld_safe Logging to '/home/mysql3307/data/mysql-error.log'.
2021-12-02T04:32:04.117386Z mysqld_safe Starting mysqld daemon with databases from /home/mysql3307/data

#查看进程数据库启动成功

[root@test bin]# ps -ef | grep mysql
root 67800 65346 0 10:29 pts/1 00:00:00 mysql -uroot -p
root 79465 79431 0 12:31 pts/0 00:00:00 tailf /home/mysql3307/data/mysql-error.log
root 79471 69643 0 12:32 pts/4 00:00:00 /bin/sh ./mysqld_safe --defaults-file=/etc/my3307.cnf --user=mysql
mysql 80287 79471 3 12:32 pts/4 00:00:00 /home/mysql3307/bin/mysqld --defaults-file=/etc/my3307.cnf --basedir=/home/mysql3307 --datadir=/home/mysql3307/data --plugin-dir=/home/mysql3307/lib/plugin --user=mysql --log-error=/home/mysql3307/data/mysql-error.log --open-files-limit=65535 --pid-file=test.pid --socket=/tmp/mysql3307.sock --port=3307

#数据库密码进行修改

[root@test]# vi /etc/my.cnf
在[mysqld]下面增加一行
skip-grant-tables

#重启mysql

[root@test]# kill -9 mysql pid

#开启mysql

[root@test]# cd /home/mysql3307/bin/
[root@test bin]# ./mysqld_safe --defaults-file=/etc/my3307.cnf --user=mysql &
[1] 86335
[root@test bin]# 2021-12-02T06:19:40.972378Z mysqld_safe Logging to '/home/mysql3307/data/mysql-error.log'.
2021-12-02T06:19:41.012897Z mysqld_safe Starting mysqld daemon with databases from /home/mysql3307/data

#查看进程

[root@test bin]# ps -ef | grep mysql
root 86335 69643 0 14:19 pts/4 00:00:00 /bin/sh ./mysqld_safe --defaults-file=/etc/my3307.cnf --user=mysql
mysql 87167 86335 4 14:19 pts/4 00:00:00 /home/mysql3307/bin/mysqld --defaults-file=/etc/my3307.cnf --basedir=/home/mysql3307 --datadir=/home/mysql3307/data --plugin-dir=/home/mysql3307/lib/plugin --user=mysql --log-error=/home/mysql3307/data/mysql-error.log --open-files-limit=65535 --pid-file=test.pid --socket=/tmp/mysql3307.sock --port=3307
root 87198 69643 0 14:19 pts/4 00:00:00 grep --color=auto mysql

#登陆数据库修改root密码

[root@test bin]# ./mysql -uroot -p -S /tmp/mysql3307.sock ---直接回车不需要输入密码
set password = password('NTJiM2MzNTIyMGZm'); ------修改root密码

#数据库升级完成。

 

 

 

 

 

欢迎进群讨论：QQ群294668383（有意向可以添加）