MySQL5.7的安装、配置、远程连接-Ubuntu18

1. sudo apt-get update

2. sudo apt-get install mysql-server

3. mysql → show databases;(有Database则安装成功) → exit

4. sudo mysql_secure_installation

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD PLUGIN can be used to test passwords and improve security. It checks the strength of password and allows the users to set only those passwords which are secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: n
Please set the password for root here.

New password: password

Re-enter new password:repassword

By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : n

… skipping.


Normally, root should only be allowed to connect from ‘localhost’. This ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.
By default, MySQL comes with a database named ‘test’ that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : n

… skipping.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

5. systemctl status mysql.service

● mysql.service - MySQL Community Server
　　Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
　　Active: active (running) since Sun 2020-05-31 14:18:38 CST; 23min ago
　 Main PID: 29661 (mysqld)
　　　Tasks: 28 (limit: 2338)
　　 CGroup: /system.slice/mysql.service
　　　　　　 └─29661 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid

May 31 14:18:38 iZajt9ftumb6brZ systemd[1]: Starting MySQL Community Server…
May 31 14:18:38 iZajt9ftumb6brZ systemd[1]: Started MySQL Community Server．

6. 非root用户:~$ mysql -u root -p

Enter password:
ERROR 1698 (28000): Access denied for user ‘root’@‘localhost’

7. root用户:# mysql → use mysql; → show tables; → select * from user;

发现root用户的认证方式是：auth_socket (column:plugin)
它只检查用户是否使用套接字进行连接，然后比较用户名，并没有密码验证。

8. ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’;
9. 之后，root用户和非root用户都需要密码验证， mysql -u root -p
10. client，navicat，error: 2003 - Can’t connect to MySql server on ’ ip ’ (10061 “Unknown error”)
11. server，查看哪些端口被打开 netstat -anp

127.0.0.1:3306 (mysqld)
127.0.0.1:3306 – 指mysql数据库仅能在本地下访问；

12. vi /etc/mysql/mysql.conf.d/mysqld.cnf (mysql5.7的目录)

bind-address= 127.0.0.1 把它改成 bind-address= 0.0.0.0
Linux命令：a 编辑，esc结束，:wq，保存并退出

13. service mysql restart
14. client，navicat，error: 1130 - Host ’ ip ’ is not allowed to connect to this MySql server

因为没有开启权限。

15. server，mysql -u root -p
16. GRANT ALL PRIVILEGES ON . TO ‘root’@’%’ IDENTIFIED BY ‘password’ WITH GRANT OPTION;

对所有 root 用户授权

1. Ubuntu18.04 安装MySQL
2. mysql出现ERROR1698(28000):Access denied for user root@localhost错误解决方法
3. Linux 上安装 Mysql 设置root密码问题
4. Navicat for Mysql连接mysql数据库时出现 2003-Can’t connect to MySql server on ‘localhost’(10061)