MySQL 5.7.18升级到5.7.22教程--离线升级MySQL数据库

本文以MySQL 5.7.18升级到5.7.22为例。

环境：Linux、MySQL

主要步骤：

1、把数据库权限导出
2、导出整个库
3、备份原数据库
4、安装新数据库版本
5、恢复权限及整个库

1、把数据库权限导出

将pt-show-grants文件放到/data/mysql_data目录下，进到/data/mysql_data目录，运行命令

$ perl pt- show -grants -- user =root --ask-pass --flush > /data/mysql_data/grants. sql

即可生成权限SQL文件

此处有可能报错

Enter password: Cannot read response; is Term::ReadKey installed? Can't locate Term/ReadKey.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at pt-show-grants line 904.

原因是缺少 Term/ReadKey.pm模块，安装一下

[root@localhost mysql_update]# rpm -ivh perl-TermReadKey-2.30-13.el6.x86_64.rpm

warning: perl-TermReadKey-2.30-13.el6.x86_64.rpm: Header V3 RSA/SHA1 Signature, key ID c105b9de: NOKEY

Preparing... ########################################### [100%]

1:perl-TermReadKey ########################################### [100%]

2、导出整个库

以下命令是生成要备份的库列表，除mysql、performance_schema、information_schema这几个库MySQL安装时会自带的外

$ mysql -uroot -p123456 -BNe "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME NOT IN ('mysql', 'performance_schema', 'information_schema')" | tr 'n' ' ' > /data/mysql_data/dbs-to-dump . sql

然后对整个库进行备份

$ mysqldump -uroot -p123456 -- routines -- events -- single-transaction -- databases $(cat /data/mysql_data/dbs-to-dump . sql) > /data/mysql_data/ full -data-dump . sql

此处有可能报错

mysqldump: [ERROR] unknown option '--no-beep'

vim /etc/my.cnf

注释掉 [client]下的  no-beep  参数;

重启一下mysql

service mysqld restart

3、备份原数据库

关闭数据库

service mysqld stop

迁移数据库到新目录

mv /data /mysql/ /data /mysql- 5.7.18

4、安装新数据库版本

解压mysql-5.7.22

tar -xzvf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

mysql-5.7.22下载链接

http://mirrors.sohu.com/mysql/MySQL-5.7/

重命名

mv mysql-5.7.22-linux-glibc2.12-x86_64/ /data/mqsql/

#安装

cd mysql/

mkdir data

更改所有者

cd ../

chown -R mysql:mysql /data/mysql

#授权755

chmod -R 755 /data/mysql

cd mysql

./bin/mysqld --user=mysql --basedir=/data/mysql --datadir=/data/mysql/data --initialize

#注意最下面的密码，待会启动MySQL的时候要用到

2018-06-21T08:10:59.876340Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2018-06-21T08:10:59.876393Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
 100
 100
2018-06-21T08:11:01.111546Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-06-21T08:11:01.336463Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-06-21T08:11:01.412039Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: a2bc3741-752a-11e8-9371-886639ceda0a.
2018-06-21T08:11:01.426528Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-06-21T08:11:01.427977Z 1 [Note] A temporary password is generated for root@localhost: # s2uvXcyVqmy
2018-06-21T08:11:08.592289Z 1 [Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
2018-06-21T08:11:08.598055Z 1 [Warning] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.
2018-06-21T08:11:08.598067Z 1 [Warning] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.
2018-06-21T08:11:08.598084Z 1 [Warning] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.
2018-06-21T08:11:08.598090Z 1 [Warning] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.
2018-06-21T08:11:08.598100Z 1 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
2018-06-21T08:11:08.598134Z 1 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
2018-06-21T08:11:08.598143Z 1 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.

#先简单测试一下

执行 ./support-files/mysql.server start

如果提示下面错误

./support-files/mysql.server: line 239: my_print_defaults: command not found

./support-files/mysql.server: line 259: cd: /usr/local/mysql: No such file or directory

Starting MySQL ERROR! Couldn't find MySQL server (/usr/local/mysql/bin/mysqld_safe)

那就正常了，哈哈

1.cp support-files/mysql.server  /etc/init.d/mysqld

2.chmod 755 /etc/init.d/mysqld

3.vi /etc/init.d/mysqld

将66-73行/usr/local/修改为/data/ 

修改前

then

  basedir=/usr/local/mysql

  bindir=/usr/local/mysql/bin

  if test -z "$datadir"

  then

    datadir=/usr/local/mysql/data

  fi

  sbindir=/usr/local/mysql/bin

  libexecdir=/usr/local/mysql/bin

else

修改后，根据你的实际目录修改

then

  basedir=/data/mysql

  bindir=/data/mysql/bin

  if test -z "$datadir"

  then

    datadir=/data/mysql/data

  fi

  sbindir=/data/mysql/bin

  libexecdir=/data/mysql/bin

else

service mysqld start

如果提示Starting MySQL.Logging to '/data/mysql/data/MyServer.err'.

不管它就行，以后重启就不会提示了

接下来来配置my.cnf

如果按照下面配置好配置文件还不行，

记得kill进程

ps -ef|grep mysql

kill -9 pid

先清理一下内存

sync

echo 3 > /proc/sys/vm/drop_caches

启动MySQL

/etc/init.d/mysqld start

使用刚刚安装的初始密码进行登录

mysql -uroot -p' s2uvXcyVqmy'

更改密码

MySQL [mysql]> update user set password_expired='N' where user='root';

MySQL [mysql]> SET PASSWORD = PASSWORD('123456');

MySQL [mysql]> flush privileges;

MySQL [mysql]> quit

启动MySQL有可能报错

报错：The server quit without updating PID file (/[FAILED]ql/mysqld.pid).

原因没权限启动进程，有些目录属root

chown -R mysql:mysql /data/mysql

5、恢复权限及整个库

1）用户信息导入到新数据库中.

mysql -uroot < /data/mysql_data/grants .sql

2）导出的数据导入到新数据库.

$ mysql -e "SET GLOBAL max_allowed_packet=1024*1024*1024" ;

$ mysql -uroot -p -- max -allowed-packet = 1 G < /data/mysql_data/ full -data-dump . sql

如果中途遇到什么错就百度一下处理吧。

告诉你一个捷径：

更暴力的升级方式是将/data/mysql-5.7.22/bin目录下所有文件替换原/data/mysql-5.7.18/bin目录下所有文件，可以尝试一下。

本人试过/data/mysql-5.7.18/data可以做迁移到/data/mysql-5.7.22。

但是还是建议按本文步骤来做稳妥一些。

相关文件下载

https://download.csdn.net/download/qq_41018670/10491169

参考：

https://blog.csdn.net/sunofsummer/article/details/51777920

https://blog.csdn.net/guoxingege/article/details/70574043