mysql5.6升级

mysql5.6的升级可以分为以下几个步骤：

• 安全关闭正在运行的MySQL实例
  
• 把/usr/local/mysql 的连接由MySQL5.6更改为MySQL5.7
• 启动MySQL实例，查看是否是MySQL5.7版本
• 使用mysql_upgrade命令升级系统表

首先：停止当前运行的MySQL实例，然后做如下操作

更改之后启动MySQL实例：

[root@test3?local]#?service?mysqld?start
Starting?MySQL..?SUCCESS![root@test3?local]#?netstat?-lntp
Active?Internet?connections?(only?servers)
Proto?Recv-Q?Send-Q?Local?Address???????????????Foreign?Address?????????????State???????PID/Program?name???
tcp????????0??????0?0.0.0.0:22??????????????????0.0.0.0:*???????????????????LISTEN??????31179/sshd??????????
tcp????????0??????0?:::22???????????????????????:::*????????????????????????LISTEN??????31179/sshd??????????
tcp????????0??????0?:::3000?????????????????????:::*????????????????????????LISTEN??????25168/grafana-serve?
tcp????????0??????0?:::3306

然后连接MySQL，可以看到MySQL已经有原来的5.6版本升级为5.7版本：

[root@test3?~]#?mysql?-S?/tmp/mysql.sock?-p
Enter?password:?
Welcome?to?the?MariaDB?monitor.??Commands?end?with?;?or?\g.
Your?MySQL?connection?id?is?4Server?version:?5.7.22-log?MySQL?Community?Server?(GPL)

Copyright?(c)?2000,?2014,?Oracle,?SkySQL?Ab?and?others.

Type?‘help;‘?or?‘\h‘?for?help.?Type?‘\c‘?to?clear?the?current?input?statement.

MySQL?[(none)]>?exit
Bye
[root@test3?~]#

这时候利用mysql_upgrade命令升级用户表

[root@test3?~]#?mysql_upgrade?-s?-p
Enter?password:?
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.
Upgrading?the?sys?schema.
Upgrade?process?completed?successfully.
Checking?if?update?is?needed.

#需要注意的是这个命令在执行过程中会重构数据库中所有的表，但是升级的时候，用户的数据表，是不需要重构的，因此我们使用看-s参数，表示只是重构系统表。

如果这一步的操作没有报错，那就说明MySQL实例升级完毕！

mysql的无密码登录

第一种：我们可以把用户名和密码写进配置文件中，来实现无密码登录。

查看一下mysql默认读取配置文件的路径：

[root@test3?mysql]#?mysql?--help?|grep?my.cnf
??????????????????????order?of?preference,?my.cnf,?$MYSQL_TCP_PORT,/etc/my.cnf?/etc/mysql/my.cnf?/usr/local/mysql/etc/my.cnf?~/.my.cnf?
[root@test3?mysql]#

我们可以在当前用户的家目录下面创建用户名和密码，如下：

[root@test2?~]#?cat?.my.cnf?
[client]
user=root
password=123456[root@test2?~]#?mysql
Welcome?to?the?MySQL?monitor.??Commands?end?with?;?or?\g.
Your?MySQL?connection?id?is?10Server?version:?5.6.28-log?Source?distribution

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>

但是这种方式是明码保存的很不安全，接下来，我们使用mysql自带的mysql_condif_editory命令的设置无密码登录。

[root@test2?~]#?mysql_config_editor?--help
mysql_config_editor?Ver?1.0?Distrib?5.6.28,?for?Linux?on?x86_64
Copyright?(c)?2012,?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.

MySQL?Configuration?Utility.
Usage:?mysql_config_editor?[program?options]?[command?[command?options]]??-?,?--help??????????Display?this?help?and?exit.??-v,?--verbose???????Write?more?information.??-V,?--version???????Output?version?information?and?exit.

Variables?(--variable-name=value)
and?boolean?options?{FALSE|TRUE}??Value?(after?reading?options)---------------------------------?----------------------------------------verbose???????????????????????????FALSE

Where?command?can?be?any?one?of?the?following?:
???????set?[command?options]?????Sets?user?name/password/host?name/socket/port?????????????????????????????????for?a?given?login?path?(section).
???????remove?[command?options]??Remove?a?login?path?from?the?login?file.
???????print?[command?options]???Print?all?the?options?for?a?specified?????????????????????????????????login?path.
???????reset?[command?options]???Deletes?the?contents?of?the?login?file.
???????help??????????????????????Display?this?usage/help?information.

mysql_config_editor命令

实例：

[root@test2?~]#?mysql_config_editor?set?--user=root?--host=localhost?--password?
Enter?password:?
[root@test2?~]#?ls?.mylogin.cnf
.mylogin.cnf
[root@test2?~]#

#这里的密码必须在交互输入，然后就可以直接无密码登录
[root@test2?~]#?mysql
Welcome?to?the?MySQL?monitor.??Commands?end?with?;?or?\g.
Your?MySQL?connection?id?is?11Server?version:?5.6.28-log?Source?distribution

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>

生成的文件是一个二进制文件，可以使用print参数查看

[root@test2?~]#?mysql_config_editor?print?--all
[client]
user?=?root
password?=?*****host?=?localhost
[root@test2?~]#

此命令还可以使用使用--login-path命令，如下：

[root@test2?~]#?mysql_config_editor?set?--login-path=test?--user=root?--host=localhost?--password
Enter?password:?
[root@test2?~]#?mysql_config_editor?print?--login-path=test
[test]
user?=?root
password?=?*****host?=?localhost
[root@test2?~]#?mysql?--login-path=test
Welcome?to?the?MySQL?monitor.??Commands?end?with?;?or?\g.
Your?MySQL?connection?id?is?17Server?version:?5.6.28-log?Source?distribution

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>

--login-path也是在当前家目录下面生成.mylogin.cnf文件，这个文件以 [ ] 进行标识！

?