转载

MySQL 5.6.19升级到 5.7.9 步骤介绍

链接：http://blog.itpub.net/28602568/viewspace-2121318/

标题：MySQL 5.6.19升级到 5.7.9 步骤介绍

升级前版本检查+测试表创建

[root@localhost ~]# mysql -uroot -p123456 --socket=/var/lib/mysql/mysql3307.sock --port=3307 -e"create database lottery; use lottery;create table lottery.mhq as select 'test';select version();"
+-----------+
| version() |
+-----------+
| 5.6.19-log |
+-----------+
[root@localhost ~]#

软件包：
wget http://downloads.mysql.com/archives/get/file/mysql-community-client-5.7.9-1.el6.x86_64.rpm

wget http://downloads.mysql.com/archives/get/file/mysql-community-server-5.7.9-1.el6.x86_64.rpm

wget http://downloads.mysql.com/archives/get/file/mysql-community-common-5.7.9-1.el6.x86_64.rpm
wget http://downloads.mysql.com/archives/get/file/mysql-community-libs-5.7.9-1.el6.x86_64.rpm

yum安装命令：

yum install numactl*

yum localinstall --skip-broken mysql-community*

升级后验证版本和表数据

[root@localhost ~]# mysql -uroot -p123456 --socket=/var/lib/mysql/mysql3307.sock --port=3307 -EN -e" select version(); select * from lottery.mhq;"

*************************** 1. row ***************************
5.7.9-log
*************************** 1. row ***************************
test
[root@localhost ~]#

注释：

基本查询db表没问题，不过一些mysqldump用到设计到系统表时需要mysql_upgrade做下系统表升级；

升级前使用mysqldump

[root@localhost mysql]# mysqldump -uroot -p123456 -B retail_pms > /root/retail_pms.sql
mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'gtid/_mode'': Native table 'performance_schema'.'session_variables' has the wrong structure (1682)
[root@localhost mysql]#

升级系统表

[root@localhost mysql]# mysql_upgrade -u root -p123456 --socket=/var/lib/mysql/mysql3307.sock --port=3307 --force
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
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
Upgrading the sys schema.
Checking databases.
lottery.mhq OK
sys.sys_config OK
Upgrade process completed successfully.
Checking if update is needed.

[root@localhost mysql]#

简单查询系统表是否存在：

[root@localhost mysql]# find / -name session_variables*
/var/lib/mysql/performance_schema/session_variables.frm
/data/wonhigh/mysql3307/performance_schema/session_variables.frm

[root@localhost mysql]#

重启DB

[root@localhost ~]# date;mysqldump -uroot -pW@h1Gh#2ol5 -A --single-transaction -q -R --set-gtid-purged=OFF > /data/all.sql;date
mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'ndbinfo/_version'': Native table 'performance_schema'.'session_variables' has the wrong structure (1682) ---->这种需要重启DB才能解决哦~
[root@localhost ~]#

[root@localhost mysql]# /etc/init.d/mysqld restart
停止 mysqld： [确定]

正在启动 mysqld： [确定]

[root@localhost mysql]#

再尝试mysqldump是否成功

[root@localhost mysql]# mysqldump -uroot -p123456 retail_pms BRAND COLOR_INFO SHOP_BRAND ITEM > /root/retail_pms.sql

[root@localhost mysql]#

【源于本人笔记】若有书写错误，表达错误，请指正...

此条目发表在 MySQL分类目录。将固定连接加入收藏夹。