Mysql5.6升级5.7

1、假设现在已经安装好了5.6并且能正常使用,且在/usr/local/下，mysql是通过软连接指向Mysql5.6的，软连接作用就是方便版本控制。

首先关闭Mysql服务

service mysqld stop  这条命令应该需要root权限才可以执行，生产中root权限不好拿到
kill -9 pid，简单粗暴

2、再检查一下/etc/my.cnf中的配置信息datadir，修改为mysql-5.6-------/data.（这一步现在看好像是没什么必要了）

然后，执行以下命令，解除软连接

cd /usr/local/
unlink mysql 
ln -s mysql-5.7******** mysql
mysql -uroot -p

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.9 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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> select version();
+-----------+
| version() |
+-----------+
| 5.7.9     |
+-----------+
1 row in set (0.00 sec)

mysql> 

升级完成，其实只要做个软连接，指定下数据目录就可以了，跨大版本升级的话，在Mysql官网会有相应的操作文档。

3、在此之后 ，又进行5.6升级5.7，在登录的时候报错，过程如下

3.1、守护进程启动mysql，查看进程是否正常启动

[root@localhost mysql]# 200218 06:36:51 mysqld_safe Logging to '/usr/local/mysql/data/error.log'.
200218 06:36:51 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

[root@localhost mysql]# 
[root@localhost mysql]# ps -ef | grep mysql
root       9261   8955  0 06:36 pts/0    00:00:00 /bin/sh bin/mysqld_safe --user=mysql
mysql      9707   9261  0 06:36 pts/0    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/data/error.log --pid-file=/usr/local/mysql/data/localhost.localdomain.pid --socket=/var/lib/mysql/mysql.sock --port=3306
root       9743   8955  0 06:38 pts/0    00:00:00 grep --color=auto mysql

3.2、登录数据库

[root@localhost mysql]# mysql -u root -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111)

解决办法：

ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock

3.3、再次登录数据库

[root@localhost mysql]# mysql -uroot -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

解决办法：（原因是/etc/my.cnf配置文件中已经有了密码但与初始化的root密码不符）

1、先停止Mysql进程服务
/etc/init.d/mysqld stop
2、再次启动Mysql进程，并禁止远程连接
 ./bin/mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
3、登录mysql数据库
mysql -u root mysql
4、更改root密码
update mysql.user set authentication_string=password('111111') where user='root' and Host = 'localhost';
5、重新登录Mysql数据库
[root@localhost mysql]# mysql -uroot -p111111
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.9-log

Copyright (c) 2000, 2015, 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> 

 4、执行升级命令

[root@localhost mysql]# ./bin/mysql_upgrade -s
The --upgrade-system-tables option was used, databases won't be touched.
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
The sys schema is already up to date (version 1.5.0).
Upgrade process completed successfully.
Checking if update is needed.

 5、备注：在5。6升级的时候应该把数据路径下的mysql库做备份

cp -rf /usr/local/mysql/data/mysql /usr/local/mysql/data/mysql-5.6
此时是root帐户的权限，如果在此时进行回滚的操作，会因mysql-5.6没有不是mysql的权限，报错。
chown -R  mysql mysql-5.6
chgrp -R mysql mysql-5.6

 6、升级5.7后，在重新初始化后，用初始化过程中生成的随机密码来登录数据库时，报错

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
[root@bogon data]# ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock

还有一个本地socket连接问题，用上边的步骤做一遍就好了。

[root@bogon data]# mysql -uroot -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

 解决办法如下：

1、先把mysql进程停止
[root@bogon data]# ps -ef | grep mysql
root       8032      1  0 09:32 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql-5.6.27-linux-glibc2.5-x86_64/data --pid-file=/usr/local/mysql-5.6.27-linux-glibc2.5-x86_64/data/bogon.pid
mysql      8323   8032  0 09:32 ?        00:00:02 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql-5.6.27-linux-glibc2.5-x86_64/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql-5.6.27-linux-glibc2.5-x86_64/data/bogon.err --pid-file=/usr/local/mysql-5.6.27-linux-glibc2.5-x86_64/data/bogon.pid --socket=/var/lib/mysql/mysql.sock
root       9171   8767  0 10:05 pts/0    00:00:00 grep --color=auto mysql
[root@bogon data]# kill -9 8032 8323
[root@bogon data]# ps -ef | grep mysql
root       9175   8767  0 10:06 pts/0    00:00:00 grep --color=auto mysql
2、再输入命令，mysqld_safe不用验证密码登录
[root@bogon data]# mysqld_safe --user=root --skip-grant-tables --skip-networking
这也同时禁止了远程登录
3、登录数据库、并修改密码
[root@bogon data]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.9-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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> update mysql.user set authentication_string=password('111111') where user='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> quit
4、重启mysql服务，用新密码重新正常登录即可。