最近使用linux需要用到数据库,所以准备安装mysql,但是其中遇到了不少坑,特此记录下。
1.安装mysql
以为安装mysql挺复杂的,看网上教程步骤都挺多的,但是跟着网上操作了好久,后来才知道原来安装起来并不难,我们可以直接安装,几条指令即可
#命令1
sudo apt-get update
#命令2
sudo apt-get install mysql-server
这样就完成了基本安装了
2.配置MySQL
2.1 初始化配置
sudo mysql_secure_installation
配置项较多
#1
VALIDATE PASSWORD PLUGIN can be used to test passwords...
Press y|Y for Yes, any other key for No: N (我的选项)
#2
Please set the password for root here...
New password: (输入密码)
Re-enter new password: (重复输入)
#3
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 (我的选项)
#4
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 (我的选项)
#5
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 (我的选项)
#6
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 (我的选项)
基本配置已完成
2.2 检查mysql服务状态
systemctl status mysql.service
3.配置远程访问
这里我准备使用navicat连接远程数据库,再次之前需要进行配置
3.1 首先用根用户进入
sudo mysql -uroot -p
然后
grant all privileges on *.* to 'root'@'%' with grant option;
这一步一般可能会出错,说权限不够,
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
解决办法:
1、查看 mysql 初始的密码策略,
输入语句 “ SHOW VARIABLES LIKE ‘validate_password%’; ” 进行查看,
2、首先需要设置密码的验证强度等级,设置 validate_password_policy 的全局参数为 LOW 即可,
输入设值语句
set global validate_password_policy=LOW;
3、当前密码长度为 8 ,如果不介意的话就不用修改了,按照通用的来讲,设置为 6 位的密码,设置 validate_password_length 的全局参数为 6 即可,
输入设值语句 “
set global validate_password_length=6;
进行设值,
4.现在可以为 mysql 设置简单密码了,只要满足六位的长度即可,
输入修改语句
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
可以看到修改成功,表示密码策略修改成功了!!!
操作完后切记执行以下命令刷新权限
然后再执行语句
grant all privileges on *.* to 'root'@'%' with grant option;
再刷新一下
FLUSH PRIVILEGES;
查看用户
select Host,User from user;
如果root用户还没有%
那就自己创建一个新用户
create user 'root'@'%' identified by '123456';
再查看一下
select host,user from user;
然后进行授权
flush privileges;
grant all privileges on *.* to 'root'@'%';
或者
grant all privileges on *.* to 'root'@'%' with grant option;
有了%号之后重启下mysql
sudo service mysql restart
最重要的一步,修改配置文件
修改之前先停掉mysql服务,使用以下命令关闭MySQL服务:
service mysql stop
然后修改配置文件!!!
允许远程主机访问还要在mysql的配置文件(/etc/mysql/mysql.conf.d/mysqld.cnf)找到bind-address的值修改为0.0.0.0
vi /etc/mysql/mysql.conf.d/mysqld.cnf
修改完之后启动mysql
service mysql start
然后就可以用Navicat连接mysql