mysql5.730_数据库mysql升级（5.7.17到5.7.30）

数据库mysql升级(5.7.17到5.7.30)

将数据库从5.7.17升级到5.7.30

环境配置：

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/ mysql5730

停止数据库

[root@localhost ~]# service mysqld stop

Shutting down MySQL. SUCCESS!

将数据库和所有数据备份

安装目录备份

[root@localhost ~]# cp -rp /usr/local/mysql /usr/local/mysql57

数据备份

[root@localhost ~]# cd /

[root@localhost /]# cp -rp data data57

启动文件备份

[root@localhost /]# cd /etc/init.d/

[root@localhost init.d]# cp mysqld mysql57

配置文件备份

[root@localhost init.d]# cp /etc/my.cnf /etc/my.cnf_57

2.数据库升级

原数据库信息5.7.17

①删除原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 mysql5730 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/mysqld

[root@localhost local]# vim /etc/init.d/mysqld

编写新启动脚本 将basedir和datadir填写进去

粗体

basedir=/usr/local/mysql

datadir=/usr/local/mysql/data

③启动mysql

[root@localhost local]# service mysql start

Starting MySQL… SUCCESS!

查看进程和端口确认启动

④升级数据字典

[root@mysql1 local]# /usr/local/mysql/bin/mysql_upgrade -uroot -p

mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.

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

Found outdated sys schema version 1.5.1.

Upgrading the sys schema.

Checking databases.

sys.sys_config OK

zb.tb_test OK

Upgrade process completed successfully.

⑤重启启动mysql并验证

[root@localhost local]# service mysql restart

Shutting down MySQL… SUCCESS!

Starting MySQL. SUCCESS!

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