mysql5.6升级及mysql无密码登录
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 4 Server 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 10 Server 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.
实例：
[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 11 Server 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 17 Server 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文件，这个文件以 [ ] 进行标识！
