MY SQL 数据库升级

MY SQL 数据库升级

使用mysql_upprade直接升级数据字典库

这种方式的升级不可跨越大版本。
1.停掉旧版本
查看sql_mode

mysql> show variables like '%sql_mode%';
Variable_name: sql_mode
        Value:
1 row in set（0.00 sec）

动态修改innodb_fast_shutdown=0，以执行full purge（当innodb_fast_shutdown=0时，MySQL在执行关闭mysqld进程时，会对不再需要的undo log page进行清理，该清理动作非人为触发）和插入缓冲合并等操作，以干净的方式关闭MySQL。

[root@localhost mysql]# mysql -ugangshen -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.54-log MySQL Community Server（GPL）
Copyright（c）2000, 2013, Oracle and/or its affiliates. All rights reserved.
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.
mysql> set global innodb_fast_shutdown=0;
Query OK, 0 rows affected（0.00 sec）
[root@localhost mysql]# service mysqld stop
Shutting down MySQL..                                    [  OK  ]

确认MySQL已经停止

[root@localhost mysql]# ps aux |grep mysqld_safe |grep -v grep
[root@localhost mysql]# netstat -ntupl |grep mysqld
tcp      0     0 :::9104             :::＊              LISTEN      1968/mysqld_exporte

2．在my.cnf中添加skip_grant_tables参数
在my.cnf中添加skip_grant_tables参数，确保在执行升级前以不加载系统字典库的方式启动MySQL。

[root@localhost mysql]# cat /etc/my.cnf
[client]
socket=/home/mysql/data/mysqldata1/sock/mysql.sock # sock文件所在路径
[mysqld]
user=mysql
basedir = /usr/local/mysql
socket=/home/mysql/data/mysqldata1/sock/mysql.sock # sock文件所在路径
pid-file=/home/mysql/data/mysqldata1/sock/mysql.pid # pid文件所在路径
datadir=/home/mysql/data/mysqldata1/mydata # 数据文件路径
tmpdir=/home/mysql/data/mysqldata1/tmpdir # 存放临时文件的路径
log-error=/home/mysql/data/mysqldata1/log/error.log
slow_query_log


slow_query_log_file=/home/mysql/data/mysqldata1/slowlog/slow-query.log
log-bin=/home/mysql/data/mysqldata1/binlog/mysql-bin
relay-log=/home/mysql/data/mysqldata1/relaylog/mysql-relay-bin
innodb_data_home_dir = /home/mysql/data/mysqldata1/innodb_ts
innodb_log_group_home_dir = /home/mysql/data/mysqldata1/innodb_log
skip_grant_tables

3．替换basedir
解压缩MySQL 5.6.35二进制安装文件，并把MySQL 5.5.54的basedir替换为MySQL 5.6.35的basedir。

[root@localhost mysql]# cd /usr/local/
[root@localhost local]# ll


total 44
drwxr-xr-x. 2 root root 4096 Oct 27 17:54 bin
drwxr-xr-x. 2 root root 4096 Jun 28  2011 etc
drwxr-xr-x. 2 root root 4096 Jun 28  2011 games
drwxr-xr-x. 2 root root 4096 Jun 28  2011 include
drwxr-xr-x. 2 root root 4096 Jun 28  2011 lib
drwxr-xr-x. 2 root root 4096 Jun 28  2011 lib64
drwxr-xr-x. 2 root root 4096 Jun 28  2011 libexec
lrwxrwxrwx  1 root root   49 Feb 13 16:10 mysql -> /home/mysql/program/mysql-5.5.54-linux2.6-x86_64/
drwxr-xr-x  3 root root 4096 Jan 16 14:16 qflame
drwxr-xr-x. 2 root root 4096 Jun 28  2011 sbin
drwxr-xr-x. 5 root root 4096 Jan 29  2016 share
drwxr-xr-x. 2 root root 4096 Jun 28  2011 src
[root@localhost local]# unlink mysql
[root@localhost local]# ll
total 44
drwxr-xr-x. 2 root root 4096 Oct 27 17:54 bin


drwxr-xr-x. 2 root root 4096 Jun 28  2011 etc
drwxr-xr-x. 2 root root 4096 Jun 28  2011 games
drwxr-xr-x. 2 root root 4096 Jun 28  2011 include
drwxr-xr-x. 2 root root 4096 Jun 28  2011 lib
drwxr-xr-x. 2 root root 4096 Jun 28  2011 lib64
drwxr-xr-x. 2 root root 4096 Jun 28  2011 libexec
drwxr-xr-x  3 root root 4096 Jan 16 14:16 qflame
drwxr-xr-x. 2 root root 4096 Jun 28  2011 sbin
drwxr-xr-x. 5 root root 4096 Jan 29  2016 share
drwxr-xr-x. 2 root root 4096 Jun 28  2011 src
[root@localhost local]# ln -s /home/mysql/ program/mysql-5.6.35-linux-glibc2.5-x86_64/\/usr/local/mysql
[root@localhost local]# ll /usr/local/
total 44
drwxr-xr-x. 2 root root 4096 Oct 27 17:54 bin
drwxr-xr-x. 2 root root 4096 Jun 28  2011 etc
drwxr-xr-x. 2 root root 4096 Jun 28  2011 games
drwxr-xr-x. 2 root root 4096 Jun 28  2011 include


drwxr-xr-x. 2 root root 4096 Jun 28  2011 lib
drwxr-xr-x. 2 root root 4096 Jun 28  2011 lib64
drwxr-xr-x. 2 root root 4096 Jun 28  2011 libexec
lrwxrwxrwx  1 root root   55 Feb 13 17:20 mysql -> /home/mysql/program/ mysql-5.6.35-linux-glibc2.5-x86_64/
drwxr-xr-x  3 root root 4096 Jan 16 14:16 qflame
drwxr-xr-x. 2 root root 4096 Jun 28  2011 sbin
drwxr-xr-x. 5 root root 4096 Jan 29