如何在Ubuntu 20.04上安装 Mysql

1. Mysql安装

# update the package index on your server if you’ve not done so recently
sudo apt update

# Then install the mysql-server package
sudo apt install mysql-server

2. Mysql配置

sudo mysql_secure_installation

按提示配置root密码，密码等级...

~$ sudo mysql_secure_installation
Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD COMPONENT 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 component?

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

New password: 

Re-enter new password: 
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) : 

 ... 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) : 

 ... 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! 

3. 创建用户并给权限

1. 以root用户登录mysql

   sudo mysql
   

2. 修改密码等级，使能设置简单6位数密码

   # 显示当前mysql 密码策略
   mysql> SHOW VARIABLES LIKE 'validate_password%';
   
   # 设置密码的强度验证等级为LOW
   mysql> set global validate_password.policy=LOW;
   
   # 设置密码长度为6
   mysql> set global validate_password.length=6;
   

3. 创建新用户

   # 创建新用户
   # 用户名: my_user
   # Host: %表示支持任意连接，localhost表示只允许本地连接
   # 密码: my_password
   mysql> CREATE USER 'my_user'@'%' IDENTIFIED BY 'my_password';
   
   # 查询用户
   mysql> SELECT user,host FROM mysql.user;
   
   # 删除用户, 注意默认删除的是'XXX'@'%'这个用户。如果要删除'XXX'@'localhost'，使用drop删除时需要加上host即drop user 'XXX'@'localhost'
   mysql> drop user my_user;
   

4. 创建数据库

   # 显示现有数据库
   mysql> show databases;
   
   # 创建数据库
   mysql> CREATE DATABASE my_database;
   

5. 将新创建的数据库与新用户关联，并赋权限

   mysql> GRANT ALL PRIVILEGES ON my_database.* to my_user@'%';
   
   # 如果需要给用户创建数据库的权限，则可以这样设置
   mysql> GRANT ALL PRIVILEGES ON *.* to my_user@'%';
   

6. 保存退出

   mysql> FLUSH PRIVILEGES;
   mysql> exit
   

7. 新用户登录

   # 登录
   mysql -u my_user -p
   
   # 使用对应数据库
   mysql> use my_database;
   
   # 显示所有表
   mysql> show tables;
   

3. 遇到的问题

1. ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)

解决：
修改配置文件：/etc/mysql/mysql.conf.d/mysqld.cnf， 注释掉 bind-address = 127.0.0.1

#bind-address = 127.0.0.1

重启mysql即可

# 重启mysql
sudo service mysql restart

# 验证修改是否生效
sudo netstat -tnlp | grep mysqld

4. 其他

免密登录设置

如果需要不用密码直接登录，则需修改/etc/mysql/mysql.conf.d/mysqld.cnf， 添加skip-grant-tables, 重启mysql

skip-grant-tables

重启mysql

# 重启mysql
sudo service mysql restart

验证

mysql -u root -p
# 不用输入密码，直接回车（出现Enter Password 也一样直接回车，即可登陆成功