每日博客

服务器安装mysql以及navicat远程连接数据库

1.安装

#更新源
sudo apt-get update
#安装mysql服务，Ubuntu20默认安装版本8以上
sudo apt-get install mysql-server

2.配置

#命令
sudo mysql_secure_installation

#显示
VALIDATE PASSWORD PLUGIN can be used to test passwords...
Press y|Y for Yes, any other key for No: 
#输入
N (选择N ,不会进行密码的强校验)
 
#显示
Please set the password for root here...
New password: （输入密码，密码不显示）
Re-enter new password: (重复输入)
 
#显示
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them...
Remove anonymous users? (Press y|Y for Yes, any other key for No) : 
#输入
N (选择N，不删除匿名用户)
 
#显示
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network...
Disallow root login remotely? (Press y|Y for Yes, any other key for No) :
#输入
 N (选择N，允许root远程连接)
 
#显示
By default, MySQL comes with a database named 'test' that
anyone can access...
Remove test database and access to it? (Press y|Y for Yes, any other key for No) :
#输入
N (选择N，不删除test数据库)
 
#显示
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : 
#输入
Y (选择Y，修改权限立即生效)

修改加密规则（must,否则navicat因mysql版本问题无法连接）

sudo mysql -uroot -p

mysql>use mysql;


mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '密码'; 
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '密码' PASSWORD EXPIRE NEVER;
mysql>flush privileges;

3.检查命令

#检查服务状态
systemctl status mysql.service
#或
sudo service mysql status
 
sudo service mysql stop  #停止
sudo service mysql start  #启动

显示：

则安装完毕。

4.远程访问

sudo mysql -uroot -p

mysql>use mysql;

#若报错检查大小写
mysql>select User,authentication_string,Host from user;

select host,user,plugin from user;

mysql> UPDATE user SET host = '%' WHERE user = 'root';

mysql>flush privileges;
mysql>quit;