Ubuntu24.04安装配置MySQL【最新保姆级教程】【远程连接DataGrip】
一.安装mysql
1.更新apt
sudo apt update
sudo apt upgrade -y2.安装mysql server
sudo apt-get install mysql-server3.安装mysql开发包
sudo apt install -y libmysqlclient-dev4.设置mysql server开机自启
sudo systemctl enable mysql5.设置安全性内容
sudo mysql_secure_installation请按照提示输入y or n
下附参考
hayes@hayes-VMware-Virtual-Platform:~/桌面$ sudo mysql_secure_installation
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: y
There are three levels of password validation policy:
LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0
Skipping password set for root as authentication with auth_socket is used by default.
If you would like to use password authentication instead, this can be done with the "ALTER_USER" command.
See https://dev.mysql.com/doc/refman/8.0/en/alter-user.html#alter-user-password-management for more information.
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) : y
Success.
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) : y
Success.
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) : y
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
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!
6.启动mysql server
sudo service mysql start若出现以下warning：
Warning: The unit file, source configuration file or drop-ins of mysql.service changed on disk. Run 'systemctl daemon-reload' to reload units.则输入：
systemctl daemon-reload7.检查mysql是否正常运行
sudo service mysql status
看到Active：active（running）则成功运行
按q退出
8.查看mysql版本
mysql --version二.新建远程连接用户
1.启动mysql
sudo mysql2.查看数据库用户身份验证方式
select user,authentication_string,plugin,host from mysql.user;
可以看到root是通过auth_socket进行验证登录，并没有设置其密码，并且只能在本地验证登录。当然密码以及是否多端登录都是可以进行修改的，但个人不建议对root进行修改，于是后续将通过新建一个远程用户进行教学。
3.查看mysql server上的所有数据库
SHOW DATABASES;
目前的四个数据库都是系统默认的数据库，后续可以按照个人需要进行创建。
4.选择mysql数据库
use mysql;看到提示Database changed即可。
5.创建用户user1,并设置只能本地登录，密码为password。用户名和密码可自定义
create user 'user1'@'localhost' identified by 'password';后续会将localhost设置为%，此处演示如何设置本地登录用户。
6.检查是否成功创建
select user,authentication_string,plugin,host from mysql.user;
可以看到最上方多了一行Hayes用户，plugin中可以看出是通过密码进行登录验证。
7.（可选）创建新数据库
create database test;
show databases; #检查是否成功创建8.设置刚创建的用户为远程登录
update user set host='%' where user='Hayes' and host='localhost';需自行更改用户名。'%'代表不限制本地登录。
9.验证是否修改成功
select user,authentication_string,plugin,host from mysql.user;
可以看到host一栏中原本的'localhost'已经变为了'%'，证明成功修改为远程登录。
10.授予新用户权限
为了保障数据库安全，建议选择限制新用户可访问的数据库或精确到数据表，但此处由于作者个人使用，则赋予等同于root的权限，请大家注意代码区分。
grant all privileges on *.* to 'user_name'@'%';
#第一个*代表全部数据库，第二个*代表数据库内的全部数据表。此处权限等同于root用户
grant all privileges on database_name.* to 'user_name'@'%';
#此行代码是授予固定某个数据库中所有表的所有权限，请自行修改database_name
grant all privileges on database_name.table_name to 'user_name'@'%';
#同理也可指定针对某个表的权限11.退出mysql server
exit12.进入目录/etc/mysql/mysql.conf.d,修改其下的文件mysqld.cnf
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf #如果没有vim也可以用vi
在bind-address行将其注释掉，即在前面添加#
针对个人开发使用的mysql，方便起见直接注释掉。
当然也可以不选择注释掉，可以将127.0.0.1修改为0.0.0.0，这将允许MySQL监听所有IP地址。
bind-address = 0.0.0.013.重启数据库，使配置生效
sudo service mysql restart14.配置防火墙
确保主机能够远程连接。
sudo ufw allow 3306/tcp
sudo ufw reload安全起见也可以限制允许访问mysql的ip地址，ip地址选择自己主机的ip地址。
sudo ufw allow from ip_address to any port 3306将ip_address修改为主机ip。
三.使用DataGrip远程连接mysql
1.首先打开datagrip
点击＋
2.选择数据源再点击MySQL
3.配置登录
- 按照个人喜好修改名称
- 将主机栏修改为ubuntu ip地址（查找ip可在ubuntu终端输入ifconfig）
- 将mysql server的用户名和密码填写上去
- 注意先点击左下角的测试连接，如果显示成功连接，则先点击应用后点击确定
4.修改可见数据库
点击小数字（数字代表可见的数据库数量）
勾选所有架构，即可查看全部数据库。
至此本教程完结。
感谢观看。
参考文章连接
https://blog.csdn.net/weixin_53459689/article/details/136199813?fromshare=blogdetail&sharetype=blogdetail&sharerId=136199813&sharerefer=PC&sharesource=weixin_66396853&sharefrom=from_link
https://blog.csdn.net/weixin_53459689/article/details/136199813?fromshare=blogdetail&sharetype=blogdetail&sharerId=136199813&sharerefer=PC&sharesource=weixin_66396853&sharefrom=from_link
