近期由于工作需要需要掌握一些sql,遂在我的阿里云服务器上安装了mysql,用于学习，安装的过程中遇到了一些坑，在此记录一下我的安装过程，在阿里云和腾讯云亲测有效，我的环境是Ubuntu 20.04
配置服务器防火墙端口
首先我们需要配置好服务器的防火墙，打开3306端口，否则一切徒劳
MYSQL TCP 3306
安装mysql 8
更新apt包地址列表
sudo apt-get update
安装mysql (mysql-server)
sudo apt-get install mysql-server # 中途需要选择一下y
初始化mysql安装配置
sudo mysql_secure_installation
在控制台会出现一系列选项，这里需要依次配置，我的选项也一次是N、设密码、N、N、N、Y。
Securing the MySQL server deployment.
Connecting to MySQL using a blank password.
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 # 我的选项
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. 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.
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，允许远程登录
... skipping.
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.
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，刷新配置
Success.
All done!
查看mysql运行状态
systemctl status mysql.service
看到如下界面应该是运行成功了
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset:>
Active: active (running) since Sat 2021-06-05 17:05:57 CST; 1min 8s ago
Main PID: 4388 (mysqld)
Status: "Server is operational"
Tasks: 38 (limit: 1122)
Memory: 324.7M
CGroup: /system.slice/mysql.service
└─4388 /usr/sbin/mysqld
配置远程访问权限
这里分两个步骤
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'@'%';
mysql> flush privileges;
mysql> qiut;
修改mysqld.cnf配置文件
vim /etc/mysql/mysql.conf.d/mysqld.cnf
用vim把bind-address改为0.0.0.0
# 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
sudo /etc/init.d/mysql restart
查看状态
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即可，去链接试试看吧！
网友评论