Ubuntu下的Mysql配置

Ubuntu作为一款较为简便的Linux发行版，通过apt可以直接安装mysql-server与mysql-client

• 下载安装mysql

  sudo apt-get update  //更新软件包
  sudo apt-get mysql-server
  sudo apt-get mysql-client

• 验证安装是否成功

  sudo systemctl status mysql-server  //查看mysql的运行状态

  //启动mysql服务
  sudo service mysql start
      //or
  sudo systemctl start mysql.service
  //停止mysql服务
  sudo service mysql stop
      //or
  sudo systemctl stop mysql.service
  //重启mysql服务
  sudo service mysql restart
      //or
  sudo systemctl restart mysql.service

• 初始化数据库参数

  sudo mysql_secure_installation

  • 运行后会出现一连串的配置选项，包含了对于密码强度设置，与root用户的初始密码设置

  //设置密码强度验证插件
  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: y
  -------------------------------------------
  //三种验证等级
  There are three levels of password validation policy:
  
  LOW    Length >= 8
  MEDIUM Length >= 8, numeric, mixed case, and special characters
  STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file
  
  Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2
  -------------------------------------------
  //设置root密码
  Please set the password for root here.
  
  
  New password: 
  
  Re-enter new password: 

• 进入mysql客户端

  sudo mysql -u root -p //通过sudo提权进入
      
  mysql -h127.0.0.1 -u root -p //通过本地IP连接
      
  mysql -u root -p  //正常进入方式，但是通常会出现报错（ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)）

• 用户操作

  • 创建新用户，并将验证方式设置为mysql_native_password，能够通过第三方应用访问

    GRANT ALL PRIVILEGES ON *.* TO 'administrator'@'localhost' IDENTIFIED BY 'very_strong_password';

  • 用户授权

    GRANT privileges ON databasename.tablename TO 'username'@'host';

    GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

    flush privileges;   //刷新权限

  • 更新用户密码

    ALTER USER 'username'@'host' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;  

  • 删除用户

    DROP USER 'username'@'host';

  • 查看用户权限

    select Host,User,plugin from mysql.user;

  • 修改加密规则

    ALTER USER 'username'@'host' IDENTIFIED WITH mysql_native_password BY 'password';

• 所遇见的报错

  • ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

  • 在打开客户端时，mysql无法寻找到mysql.sock文件,这种情况下，可以使用指定IP连接的方式进行客户端启动

  • ERROR 1054 (42S22): Unknown column 'password' in 'field list'

  • 在修改用户密码出现上述报错，通常是由于所设置的密码格式不符合要求，可以通过SHOW VARIABLES LIKE 'validate_password%';查看密码策略

    +-------------------------------------------------+--------+
    | Variable_name                                   | Value  |
    +-------------------------------------------------+--------+
    | validate_password.changed_characters_percentage | 0      |
    | validate_password.check_user_name               | ON     |
    | validate_password.dictionary_file               |        |
    | validate_password.length                        | 8      |
    | validate_password.mixed_case_count              | 1      |
    | validate_password.number_count                  | 1      |
    | validate_password.policy                        | MEDIUM |
    | validate_password.special_char_count            | 1      |
    +-------------------------------------------------+--------+
    8 rows in set (0.01 sec)

    输入设值语句 set global validate_password_policy=LOW; 进行密码强度修改

    输入设置语句 set global validate_password_length=4;进行密码长度修改

  修改后，再次尝试ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘123456’;