ubuntu18.04 安装mysql server

时间:2024-04-15 15:07:21

mysql 5.7支持的最高版本是Ubuntu17 ,即使安装成功后,也会出现各种妖蛾子,本人就被这种问题困扰了好一会。在Ubuntu 18.04下安装mysql,建议安装8.0以上版本!

1. 配置apt source

下载mysql-apt-config_0.8.11-1_all.deb

sudo dpkg -i mysql-apt-config_0.8.11-1_all.deb

ubuntu18.04 安装mysql server

ubuntu18.04 安装mysql server

ubuntu18.04 安装mysql server

2. 更新apt source

sudo apt-get update

3. 安装mysql 8

sudo apt-get install mysql-server

注意在安装过程中,加密方式一块选择传统加密方式

ubuntu18.04 安装mysql server

4. 配置远程访问权限

mysql -u root -proot mysql #登陆mysql,默认选中mysql数据库

查看root用户的权限:

mysql> select host, user, authentication_string, plugin from user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host | user | authentication_string | plugin |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| localhost | mysql.infoschema | $A$$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session | $A$$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys | $A$$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | mysql_native_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
rows in set (0.00 sec) mysql> show variables like '%skip_networking%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| skip_networking | OFF |
+-----------------+-------+
row in set (0.01 sec)

可以看到 root 用户的 host,默认应该显示的 localhost,只支持本地访问,不允许远程访问。

授权root用户远程访问权限:

mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'root';
Query OK, rows affected (0.09 sec) mysql> select host, user, authentication_string, plugin from user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host | user | authentication_string | plugin |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| % | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | mysql_native_password |
| localhost | mysql.infoschema | $A$$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session | $A$$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys | $A$$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | mysql_native_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
rows in set (0.00 sec) mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
Query OK, rows affected (0.06 sec) mysql> select host, user, authentication_string, plugin from user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host | user | authentication_string | plugin |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| % | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | mysql_native_password |
| localhost | mysql.infoschema | $A$$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session | $A$$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys | $A$$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | mysql_native_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
rows in set (0.00 sec) mysql> flush privileges;
Query OK, rows affected (0.01 sec)

从别的机器使用mysql客户端连接,应该是成功了。

ubuntu18.04 安装mysql server

5. 其他设置(可选)

ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;   #修改加密规则,password为当前密码
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password'; #更新 root 用户密码,password为当前密码
FLUSH PRIVILEGES; #刷新权限

6. 如果遇到SQLException : SQL state: 42000 com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Access denied for user 'root'@'%' to database 'ranger' ErrorCode: 1044 这种错误

应该是看到授权的权限没有打开: Grant_priv is set to N for root@%. 修复下

UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root'; 
FLUSH PRIVILEGES;