MySQL 服务器启停

在Windows 10子系统Ubuntu中,安装 mysql 后,启动时,程序提示 “No directory, logging in with HOME=/” 解决办法:

  • sudo service mysql stop
  • sudo usermod -d /var/lib/mysql/ mysql
  • sudo service mysql start
  • 查看MySQL服务状态:sudo service mysql status
  • 启动MySQL服务:sudo service mysql start
  • 停止MySQL服务:sudo service mysql stop
  • 重启MySQL服务:sudo service mysql restart
  • 删除MySQL:sudo apt autoremove mysql-server

配置数据库远程连接

MySQL 8+ 版本远程连接:

注意:每次修改后,涉及到权限的一定要刷新权限,flush privilege;

1、登录 MySQL 机器:# mysql -u root -p
2、切换到 MySQL 数据库:mysql> use mysql
3、查看是否运行远程访问:mysql> select host,user,plugin from user;

可以看到第一行root 用户的host为localhost,要远程访问,需要将它改成%

  1. +-----------+------------------+-----------------------+
  2. | host | user | plugin |
  3. +-----------+------------------+-----------------------+
  4. | localhost | root | auth_socket |
  5. | localhost | debian-sys-maint | mysql_native_password |
  6. | localhost | mysql.infoschema | caching_sha2_password |
  7. | localhost | mysql.session | mysql_native_password |
  8. | localhost | mysql.sys | caching_sha2_password |
  9. +-----------+------------------+-----------------------+

4、开启远程访问:

  1. 将 host 改为 %:mysql> update user set host='%' where user ='root';
  2. 刷新权限:flush privileges;
  3. 更改连接的密码校验方式:

    如果客户端不支持 caching_sha2_password 加密方式,可以进行切换

  1. mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
  1. 刷新权限:flush privileges;
  2. 退出并重启 mysql 服务:service mysql restart

创建用户

1、创建用户

  1. # MySQL 8在创建用户上面,MySQL 8之前的创建方式会报错
  2. mysql> GRANT ALL ON *.* TO `wangwei`@`127.0.0.1` IDENTIFIED BY 'passowrd' WITH GRANT OPTION;
  3. # MySQL 8正确的创建方式
  4. mysql> CREATE USER `developer`@`%` IDENTIFIED BY '123456';

2、授权

  1. mysql> GRANT ALL ON *.* TO `developer`@`%` WITH GRANT OPTION;

3、更换加密连接方式 plugin

  1. mysql> ALTER USER 'developer'@'%' IDENTIFIED WITH mysql_native_password BY '123456';