Ubuntu18.04(linux)安装MySQL的方法步骤

安装

mysql

  sudo apt-get --purge remove mysql-server mysql-common mysql-client  sudo apt-get install mysql-server mysql-common mysql-client    mysqladmin -u root password your-new-password  sudo /etc/init.d/mysql restart  

mariadb

  apt-get install mariadb-server

字符集修改utf8

如果装的mariadb, 默认字符集已经是utf8了。mysql则不是

  mysql> show variables like 'char%';  +--------------------------+----------------------------+  | Variable_name      | Value           |  +--------------------------+----------------------------+  | character_set_client   | utf8            |  | character_set_connection | utf8            |  | character_set_database  | latin1           |  | character_set_filesystem | binary           |  | character_set_results  | utf8            |  | character_set_server   | latin1           |  | character_set_system   | utf8            |  | character_sets_dir    | /usr/share/mysql/charsets/ |  +--------------------------+----------------------------+  

  mysql> show variables like 'collation%';  +----------------------+-------------------+  | Variable_name    | Value       |  +----------------------+-------------------+  | collation_connection | utf8_general_ci  |  | collation_database  | latin1_swedish_ci |  | collation_server   | latin1_swedish_ci |  +----------------------+-------------------+    

修改字符集:

  sudo vim /etc/mysql/my.cnf

添加以下内容

  [mysqld]  collation-server = utf8_unicode_ci  init-connect='SET NAMES utf8'  character-set-server = utf8

重启:

  service mysql restart

登录权限问题

Ubuntu18.04 安装mysql或者mariadb之后,发现普通用户和远程都没有权限连接。

ERROR 1045: Access denied for user: (Using
password: YES)

修改了密码也不对。然后sudo mysql -u root即可登录。这显然不是我们想要的。

解决方案

删除root,重新创建用户。

首先,登录

  sudo mysql -u root

然后查看当前用户

  SELECT User,Host FROM mysql.user;  +------------------+-----------+  | User       | Host   |  +------------------+-----------+  | admin      | localhost |  | debian-sys-maint | localhost |  | magento_user   | localhost |  | mysql.sys    | localhost |  | root       | localhost |    

删除root账号

  mysql> DROP USER 'root'@'localhost';  Query OK, 0 rows affected (0,00 sec)  

重新创建root:

  mysql> CREATE USER 'root'@'%' IDENTIFIED BY '123456';  Query OK, 0 rows affected (0,00 sec)  

授权

  mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;  Query OK, 0 rows affected (0,00 sec)    mysql> FLUSH PRIVILEGES;  Query OK, 0 rows affected (0,01 sec)    

关于重置密码

host为%时允许远程登录

  SET PASSWORD FOR root@'localhost' = PASSWORD('password');

or

  UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root';

or

  USE mysql  UPDATE user SET Password = PASSWORD('newpwd')  WHERE Host = 'localhost' AND User = 'root';

允许任何地方登录

  USE mysql  UPDATE user SET Password = PASSWORD('newpwd')  WHERE Host = '%' AND User = 'root';

参考

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持脚本之家。

参与评论