安装mysql+初始化+配置root远程登录

Dean

November 6, 2022

360 views

No comments

4926 words

mysql

[TOC]

1、查看有没有安装MySQL

dpkg -l | grep mysql

2、安装MySQL

sudo apt install mysql-server

如果Debian找不到mysql-server的话，可以尝试sudo apt install default-mysql-server

3、检查是否安装成功

netstat -tap | grep mysql

通过上述命令检查之后，如果看到有 mysql 的socket处于 LISTEN 状态则表示安装成功。

4、初始化mysql配置，配置完成后重启mysql服务

sudo mysql_secure_installation

...
# 1.询问是否安装密码插件，我选择 No
VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?
Press y|Y for Yes, any other key for No: n

# 2.为root用户设置密码
Please set the password for root here.

New password: 

Re-enter new password: 
# 出现下面报错的 解决方法详见第4步，解决bug
 ... Failed! Error: SET PASSWORD has no significance for user 'root'@'localhost' as the authentication method used doesn't store authentication data in the MySQL server. Please consider using ALTER USER instead if you want to change authentication parameters.

#  解决bug后，重新设置密码，也可以不设置，刚刚sudo mysql设置的就是root的密码
New password: 

Re-enter new password: 

#3.删除匿名用户，我选No
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. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : n

 ... skipping.

#4.禁止root管理员从远程登录，这里我选 No
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

 ... skipping.
 
 #5.删除test数据库并取消对它的访问权限， 我选 No
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : n

 ... skipping.
 
 #6.刷新授权表，让初始化后的设定立即生效, 选 Yes
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
Success.

All done!

5、解决bug

sudo mysql

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';

最后按exit退出，继续执行第4步的初始化配置

6、登陆mysql，成功登陆则说明配置成功

mysql -u root -p

7、配置远程访问权限

这里分两个步骤 1、把user表中root的访问host从localhost改为%，也就是任意不限制本地访问 2、把mysqld.cnf中bind-address由127.0.0.1改为0.0.0.0，不限制访问IP

修改user表配置

登录mysql

sudo mysql -uroot -p # 需要密码，就是你之前配置的

mysql> use mysql # 使用mysql库

mysql> select User, Host from mysql.user;

+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost | # 就是要把这里的localhost变成%
+------------------+-----------+

mysql> create user 'root'@'%' identified by "你的密码";

mysql> grant all privileges on *.* to 'root'@'%';

如果这里出现问题就flush privileges;再试试。如果还是不行报错ERROR 1396 (HY000): Operation CREATE USER failed for ‘test’@’%’ ，就drop user ‘test’@’%’; +flush privileges;,再返回上上步，重新create:create user 'root'@'%' identified by "你的密码";

mysql> flush privileges;
mysql> qiut;

修改mysqld.cnf配置文件

vim /etc/mysql/mysql.conf.d/mysqld.cnf

用vim把bind-address改为0.0.0.0（31行左右）
直接注释掉也行

# localhost which is more compatible and is not less secure.
bind-address            = 0.0.0.0 # 把这里改成0.0.0.0
mysqlx-bind-address     = 127.0.0.1

重启mysql

systemctl restart mysql

查看状态

netstat -lntp

root@VM-4-16-ubuntu:~# netstat -lntp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 127.0.0.1:33060         0.0.0.0:*               LISTEN      14043/mysqld
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      14043/mysqld
tcp        0      0 0.0.0.0:111             0.0.0.0:*               LISTEN      1/init

看见0.0.0.0:3306即可通过任意ip远程root登录