os：Ubuntu 20.04.3 LTS
安装mysql-server ：sudo apt install mysql-server
然后用sudo mysql无密码登录
mysql> select user,host from mysql.user ;
+------------------+-----------+
| user | host |
+------------------+-----------+
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
创建用户root并设置密码：
#create user 'root'@'localhost' identified with mysql_native_password by '123';
#因为系统内已经存在root@localhost,所以需要alter而不是create:
mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'admin';
Query OK, 0 rows affected (0.01 sec)
授权：
mysql> grant all privileges on *.* to 'root'@'localhost' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> update user set host='%' where user='root' and host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
刷新权限：
flush privileges;
退出重新登录时需要用密码了
sudo mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
mysql -u root -p:
创建用户testusr:
mysql> create user 'testusr'@'localhost' identified with mysql_native_password by 'testpwd';
Query OK, 0 rows affected (0.01 sec)
创建数据库testdb：
mysql> create database testdb default charset utf8 collate utf8_general_ci;
Query OK, 1 row affected, 2 warnings (0.01 sec)
将testdb的所有权限赋予testusr:
mysql> grant all privileges on testdb.* to 'testusr'@'localhost' with grant option;
Query OK, 0 rows affected (0.00 sec)
刷新权限：
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
退出使用testusr登录：
mysql -u testusr -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 8.0.26-0ubuntu0.20.04.2 (Ubuntu)
查看可以操作的数据库：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| testdb |
+--------------------+
2 rows in set (0.00 sec)
mysql远程连接不上的问题，看这里
首先声明我这儿不涉及防火墙问题，就跳过了
先netstat -apn|grep 3306
tcp6 0 0 127.0.0.1:3306 :::* LISTEN 13524/mysqld
发现是绑定到了本地，所以需要修改下mysql配置文件，
我需要修改的文件是：sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
然后将bind的两行注释掉：
#bind-address = 127.0.0.1
#mysqlx-bind-address = 127.0.0.1
同时还要登录mysql，修改mysql数据库下user表的host
use mysql;
update user set host='%' where user='root' and host='localhost';
update user set host='%' where user='testusr' and host='localhost';
最后重启mysql:
sudo /etc/init.d/mysql restart
此时就可以使用root或testusr在win下使用heidiSQL客户端访问mysql服务器
用testusr登录后发现只有information_schema数据库，后来发现之前grant时，使用的语句是:mysql> grant all privileges on testdb.* to 'testusr'@'localhost' with grant option;
需要改为：
mysql> grant all privileges on testdb.* to 'testusr'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
区别就在于之前是localhost,后来远程访问时，要重新赋权，将localhost改为%，
这样在heidisql里使用testusr访问，就能看到testdb数据库了。
