数据库mysql升级（5.6到5.7）

mysql5.6升级5.7

将数据库从5.6.35升级到5.7.22
mysql安装目录 /usr/local/mysql/
数据目录/data

1.准备工作

下载5.7.22版本mysql并解压
[root@localhost ~]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.22-el7-x86_64.tar.gz
[root@localhost ~]#tar -zxvf mysql-5.7.22-el7-x86_64.tar.gz -C /usr/local/
[root@localhost ~]#cd /usr/local/
[root@localhost ~]#mv mysql-5.7.22-el7-x86_64/ mysql57

停止数据库
[root@localhost ~]# service mysql stop
Shutting down MySQL. SUCCESS!

将数据库和所有数据备份
安装目录备份
[root@localhost ~]# cp -rp /usr/local/mysql /usr/local/mysql56
数据备份
[root@localhost ~]# cd /
[root@localhost /]# cp -rp data data56
启动文件备份
[root@localhost /]# cd /etc/init.d/
[root@localhost init.d]# cp mysql mysql56
配置文件备份
[root@localhost init.d]# cp /etc/my.cnf /etc/my.cnf_56

2.数据库升级

原数据库信息5.6.35

①删除原basedir并替换

[root@localhost ~]# cd /usr/local/
[root@localhost local]# ls
bin games lib libexec mysql56 sbin src
etc include lib64 mysql mysql57 share
[root@localhost local]# rm -rf mysql
[root@localhost local]# mv mysql57 mysql
记得修改新mysql权限
[root@localhost local]# chown -R mysql.mysql mysql

②替换新版mysql启动脚本

[root@localhost local]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqlcp: overwrite ‘/etc/init.d/mysql’ y
[root@localhost local]# vim /etc/init.d/mysql
编写新启动脚本 将basedir和datadir填写进去

basedir=/usr/local/mysql
datadir=/data

③启动mysql

[root@localhost local]# service mysql start
Starting MySQL… SUCCESS!
查看进程和端口确认启动

④升级数据字典

[root@localhost local]# /usr/local/mysql/bin/mysql_upgrade -uroot -p
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
Checking databases.
sys.sys_config OK
Upgrade process completed successfully.
Checking if update is needed.

⑤重启启动mysql并验证

[root@localhost local]# service mysql restart
Shutting down MySQL… SUCCESS!
Starting MySQL. SUCCESS!

可以看到数据库已经升级到5.7.22了