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
2. 更新apt source
sudo apt-get update
3. 安装mysql 8
sudo apt-get install 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客户端连接,应该是成功了。
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;