MYSQL本机上部署不同版本实例&升级mysql到新版本

1. mysql官方下载最新5.7.38安装包，上传至服务器MySQL :: Begin Your Download

放路径：/software/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz

解压路径：/data.mysql

1. 新建目录，复制my.cnf

mkdir /data
mkdir /data/mysql

tar -zxvf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
cp mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz/ /data/mysql/


cd /data/mysql
vi my.cnf 
 

3.my.cnf

[client]
port = 3307
socket = /data/mysql/mysql.sock
default-character-set=utf8


[mysqld]
explicit_defaults_for_timestamp=true

basedir =/data/mysql
datadir = /data/mysql/data
socket=/data/mysql/mysql.sock
user=mysql
port=3307
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#character-set-server=utf8:
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

server_id=20
 
log_bin = /data/mysql/mysql-bin
 
binlog_format = ROW
 
expire_logs_days = 30
#skip-grant-tables=1

log-error=/data/mysql/logs/error.log
general_log_file = /data/mysql/logs/mysql.log

4.赋权，初始化

 chown -R mysql:mysql /data/mysql/

bin/mysqld --defaults-file=/data/mysql/my.cnf --initialize --user=mysql --basedir=/data/mysql/ --datadir=/data/mysql/data
//生成临时密码
 root@localhost: xxxx

5.启动mysql

 //查看到3307端口号代表mysql已启动

./bin/mysqld --defaults-file=/data/mysql/my.cnf &

netstat -tlunp
//进入mysql，修改密码
./bin/mysql -uroot -p -P3307 -h 127.0.0.1

SET PASSWORD = PASSWORD('123');
ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
grant all on *.* to root@'%' identified by '123';
FLUSH PRIVILEGES;

6.由于升级版本跨度小，尝试备份直接启用data目录

7.由于内存不足，先进行扩容，具体为挂载无用盘到新的数据目录下

//关停数据库
/data/mysql/bin/mysqladmin -uroot -p -h127.0.0.1 -P3307 --socket=/data/mysql/mysql.sock shutdown

8.设置开机自动挂载

9.在挂载后的数据目录启动新mysql实例

/data/mysql/bin/mysqld --defaults-file=/data/mysql/my.cnf &
//挂载成功，停止当前实例
/data/mysql/bin/mysqladmin -uroot -p -h127.0.0.1 -P3307 --socket=/data/mysql/mysql.sock shutdown

10.停机,检查数据库状态,拷贝旧数据目录到新数据目录下

[root@localhost data]# /etc/init.d/mysqld status
 SUCCESS! MySQL running (4358)
[root@localhost data]# /etc/init.d/mysqld stop
Shutting down MySQL.... SUCCESS! 
[root@localhost data]# /etc/init.d/mysqld status
 ERROR! MySQL is not running
[root@localhost data]# ps -aux | grep mysql
root     16539  0.0  0.0 112724   988 pts/2    S+   17:21   0:00 grep --color=auto mysql
[root@localhost data]# netstat -tnlp | grep 33
 
 //拷贝
[root@localhost data]# cp -ar /usr/local/mysql/data/

11.启动验证是否成功

/data/mysql/bin/mysqld --defaults-file=/data/mysql/my.cnf &


netstat -tlunp
//注意需要输入旧数据库密码
./bin/mysql -uroot -p -P3307 -h 127.0.0.1

//查看版本和数据
SELECT VERSION();

use xxxx;

select count(*) from xxxx;

12.验证成功，停机更改my.cnf端口配置，更改自启动脚本

/data/mysql/bin/mysqladmin -uroot -p -h127.0.0.1 -P3307 --socket=/data/mysql/mysql.sock shutdown

//备份
cp my.cnf my.cnf_back
//更改port=3306
vi my.cnf

cp /etc/init.d/mysqld /etc/init.d/mysqld_bak

//把所有 /usr/local/ 修改为 /data/
vi /etc/init.d/mysqld

13.启动验证

/etc/init.d/mysqld start

完成更新