# Mysql环境配置实战

# 目录

目录
核心环节配置
其他扩充视野
- 另一个可能导致采用任何密码登录都能通过「但是没遇到过」
- 附录-搭建mysql学习环境

[TOC]

# 核心环节配置

在unbuntu上apt安装mysql+解决任何密码登陆+开放云服务器安全组

# 1.核心安装流程

链接 (opens new window)

apt update
apt install mysql-server
sudo mysql_secure_installation

注意！我这里选择了强密码，大小写+数字+特殊字符，并且长度大于等于8

1
2
3
4
5
6

# 其他后续常用操作

systemctl status mysql.service #检查mysql服务状态
sudo /etc/init.d/mysql restart #重启mysql命令!!!

1
2

# 2.解决任何密码登陆

解决放啊：linux解决mysql用任何密码都能登录 (opens new window)
修改密码强度要够 (opens new window)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set authentication_string=PASSWORD("kjhlkjhl") where user='root';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> update user set authentication_string=PASSWORD("xycmd2018XYCMD@") where user='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> update user set password=password('xycmd2018XYCMD@') where user='root';
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
mysql>  update user set plugin="mysql_native_password";
Query OK, 1 row affected (0.00 sec)
Rows matched: 4  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

重庆

sudo /etc/init.d/mysql restart

root@hecs-358761:/usr/bin# mysql -hlocalhost  -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
root@hecs-358761:/usr/bin# mysql -hlocalhost  -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
root@hecs-358761:/usr/bin# mysql -hlocalhost  -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.

1
2
3
4
5
6
7
8
9

# 3.如何开放安全组了？

# 3.1.curl的使用

下面的curl成功了，就是开放了安全组了！
- 但是为什么不能连接呢？
- 解决方案如下方案：https://blog.csdn.net/eric_sunah/article/details/18567091
  - 我最终采用的改表法

➜  ~ curl -v 139.9.47.180:3306
*   Trying 139.9.47.180:3306...
* Connected to 139.9.47.180 (139.9.47.180) port 3306 (#0)
> GET / HTTP/1.1
> Host: 139.9.47.180:3306
> User-Agent: curl/7.79.1
> Accept: */*
>
* Received HTTP/0.9 when not allowed
* Closing connection 0
curl: (1) Received HTTP/0.9 when not allowed

➜  remote-ssh git:(master) ✗ mysql -h 139.9.47.180 -uroot -p
Enter password:
ERROR 1130 (HY000): Host '222.244.139.174' is not allowed to connect to this MySQL server

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 3.2.改表法

可能是你的帐号不允许从远程登陆，只能在localhost。这个时候只要在localhost的那台电脑，登入mysql后，更改"mysql" 数据库里的 "user" 表里的 "host"项，从"localhost"改称"%"

然后重启：

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set host = '%' where user = 'root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select host, user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| localhost | debian-sys-maint |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
4 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

# 4.使用navicat premium连接远程mysql

# 其他扩充视野

MySQL修改密码时，报错ERROR 1064 (42000)【新版MySQL修改密码命令有所变更】

参考资料 (opens new window)

# 另一个可能导致采用任何密码登录都能通过「但是没遇到过」

原因 (opens new window)

若上述配置文件中有skip-grant-tables，则无论输入什么密码，都会通过

root@iZf8z28a8ucyohw2pxd11dZ:/usr/bin# grep -r skip ./mysql*
grep: ./mysql: binary file matches
grep: ./mysqladmin: binary file matches
grep: ./mysqlanalyze: binary file matches
grep: ./mysqlbinlog: binary file matches
grep: ./mysqlcheck: binary file matches
grep: ./mysql_config_editor: binary file matches
./mysqld_safe:  --skip-kill-mysqld         Don't try to kill stray mysqld processes
./mysqld_safe:  --skip-syslog              Log messages to error log (default)
./mysqld_safe:      --skip-kill-mysqld*) KILL_MYSQLD=0 ;;
./mysqld_safe:      --skip-syslog) want_syslog=0 ;;
grep: ./mysqldump: binary file matches
grep: ./mysqlimport: binary file matches
grep: ./mysql_migrate_keyring: binary file matches
grep: ./mysqloptimize: binary file matches
grep: ./mysqlpump: binary file matches
grep: ./mysqlrepair: binary file matches
./mysqlreport:      next if /^\+/;  # skip divider lines
./mysqlreport:      next if /^$/;   # skip blank lines
grep: ./mysql_secure_installation: binary file matches
grep: ./mysqlshow: binary file matches
grep: ./mysqlslap: binary file matches
grep: ./mysql_ssl_rsa_setup: binary file matches
grep: ./mysql_upgrade: binary file matches
root@iZf8z28a8ucyohw2pxd11dZ:/usr/bin#

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

# 附录-搭建mysql学习环境

mysql> select @@basedir;  //安装目录
+-----------+
| @@basedir |
+-----------+
| /usr/     |
+-----------+
1 row in set (0.00 sec)

mysql> select @@datadir;  //数据目录
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15