MySQL5.6升级到5.7步骤

╰+哭是因爲堅強的太久メ 2022-02-15 12:26 752阅读 0赞

mysql5.6 升级至 mysql5.7

环境介绍：  
旧库：  
basedir=/usr/local/mysql-5.6  
datadir=/home/mysql   
config=/home/mysql/my.cnf  
sock=/home/mysql/mysql.sock   
sql\_mode=NO\_ENGINE\_SUBSTITUTION,STRICT\_TRANS\_TABLES  
character\_set\_server = utf8  
lower\_case\_table\_names=1  
skip-host-cache  
skip-name-resolve  
federated  
log-error=/home/mysql/log/mysqld.log  
pid-file=/home/mysql/master.example.com.pid

\[mysql\]  
default-character-set = utf8  
socket=/home/mysql/mysql.sock

升级步骤：

1、对mysql5.6进行全库备份（\*\*\*\*\*\*）用于失败倒回  
2、关闭mysql5.6版本数据库  
3、将mysql5.7软件部署  
4、修改配置信息  
5、修改启动脚本  
6、启动mysql5.7数据库  
7、mysql\_upgrade升级数据库相关参数  
8、重启数据库，测试升级结果

1、全库备份  
mysqldump -uroot -p123456 --all-databases > /root/mysql\_all.sql

2、 关闭数据库

/usr/local/mysql-5.6/bin/mysqladmin -uroot -p123456 shutdown

验证  
netstat -tnpl|grep :3306

3、 部署mysql5.7  
tar xf mysql-5.7.22-el7-x86\_64.tar.gz -C /usr/local

cd /usr/local   
mv mysql-5.7.22 mysql5.7

4、修改配置信息

vim /home/mysql/my.cnf   
basedir=/usr/local/mysql5.7            ---修改

5、修改启动脚本  
cp /usr/local/mysql5.7/support-files/mysql.server /etc/init.d/mysql5.7

vim /etc/init.d/mysql5.7  
basedir=/usr/local/mysql5.7  
datadir=/home/mysql

6、启动mysql5.7  
service mysql5.7 start

\[root@master ~\]\# netstat -tnpl |grep :3306  
tcp6       0      0 :::3306                 :::\*                    LISTEN      8820/mysqld

\[root@master mysql\]\# mysql -uroot -p123456  
mysql: \[Warning\] Using a password on the command line interface can be insecure.  
Welcome to the MySQL monitor.  Commands end with ; or \\g.  
Your MySQL connection id is 2  
Server version: 5.7.22 MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its  
affiliates. Other names may be trademarks of their respective  
owners.

Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.  
7、yum源安装的mysql启动路径是/usr/bin/mysql

首先 unlink /usr/bin/mysql,再ln -s /usr/local/mysql5.7 /usr/bin/mysql

8、出现Warning: MySQL Connection Failed: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111) in /home/httpd/html/show.php on line 9   
  
將 /tmp/mysql.sock link 到 /var/lib/mysql 目錄裡即可：   
  
linux\# ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock

9、升级库

* 利用MySQL 5.7包中的mysql_upgrade 升级MySQL数据中的系统表　-p指定密码

10、查看mysql版本的2种方式：

1、mysql -V

2、![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x2dHVsYQ_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x2dHVsYQ_size_16_color_FFFFFF_t_70]: /images/20220215/b3c5d39c3ae3475a86f28c215aff4da2.png