ubuntu20配置mysql注意事项

目录

一、mysql安装

二、初始化配置密码

 三、配置文件的位置

四、常用的mysql命令

五、踩坑以及解决方法

一、mysql安装

1.更新apt源

sudo apt update

 2.安装mysql服务

sudo apt-get install mysql-server

3.初始化配置

sudo mysql_secure_installation

4.配置项

•  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，修改权限立即生效)

5.检查mysql服务状态

systemctl status mysql.service

二、初始化配置密码

1.访问mysql数据库

sudo mysql

2.切换数据库

use mysql;

3.查询用户表命令

select User,authentication_string,Host from user;

4.查看状态

select host,user,plugin from user;

 5.设置权限与密码

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '密码'; #使用mysql_native_password修改加密规则
ALTER USER 'root'@'localhost' IDENTIFIED BY '密码' PASSWORD EXPIRE NEVER; #更新一下用户的密码
UPDATE user SET host = '%' WHERE user = 'root'; #允许远程访问

6. 刷新cache中配置 刷新权限

flush privileges; 
quit;

 三、配置文件的位置

/etc/mysql/my.cnf

配置文件修改如下：

[mysqld]
port=3306
socket=/var/lib/mysql/mysql.socket
#socket=/tmp/mysql.socket
skip-external-locking
skip-grant-tables

四、常用的mysql命令

1.重启mysql

sudo systemctl restart mysql

2.查看mysql服务状态

sudo systemctl status mysql

3.启动mysql

sudo systemctl start mysql

五、踩坑以及解决方法

遇到问题：Can 't connect to local MySQL server through socket '/tmp/mysql.sock '(2) ";

解决方法：

1. 按照三中的方法对配置文件进行修改修改后重启mysql服务

2. 查看在/var/lib/mysql下是否存在mysql.socket

3.尝试如下命令：

mysql -u root -p -S /var/lib/mysql/mysql.socket

输入密码后可进入mysql

4.上述命令成功后，进行软连接

ln -s /var/lib/mysql/mysql.socket /tmp/mysql.sock

5.之后可通过mysql -u root -p的方式访问mysql

参考链接1

参考链接2