数据库的安装、授权以及远程连接

枕枕枕竹

已于 2022-12-28 09:48:12 修改

阅读量153

点赞数

文章标签：数据库 mysql linux

于 2022-12-27 20:16:09 首次发布

本文链接：https://blog.csdn.net/zhenzhu_z/article/details/128458904

版权

安装数据库

找到我们需要的mysql的包[在此查找]

所需的包：

mysql-community-client [client快捷]

mysql-community-common [common快捷]

mysql-community-devel [devel快捷]

mysql-community-libs [libs快捷]

mysql-community-server [server快捷]

使用wget下载

[root@zhenzhu ~]# cd /usr/src
[root@zhenzhu src]# ls
debug  kernels
[root@zhenzhu src]# wget http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-client-5.7.40-1.el7.x86_64.rpm http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-common-5.7.40-1.el7.x86_64.rpm http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-devel-5.7.40-1.el7.x86_64.rpm http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-libs-5.7.40-1.el7.x86_64.rpm http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-server-5.7.40-1.el7.x86_64.rpm 
安装过程略
[root@zhenzhu src]# ls
debug
kernels
mysql-community-client-5.7.40-1.el7.x86_64.rpm
mysql-community-common-5.7.40-1.el7.x86_64.rpm
mysql-community-devel-5.7.40-1.el7.x86_64.rpm
mysql-community-libs-5.7.40-1.el7.x86_64.rpm
mysql-community-server-5.7.40-1.el7.x86_64.rpm
[root@zhenzhu src]#

安装

[root@zhenzhu src]# yum -y localinstall *.rpm
安装过程略
[root@zhenzhu src]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled;>
   Active: inactive (dead)
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html

[root@zhenzhu src]#

启动

[root@zhenzhu src]# systemctl disable --now firewalld  //关闭防火墙
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@zhenzhu src]# vim /etc/selinux/config     //关闭selinux
[root@zhenzhu src]# setenforce 0    //设置临时生效
[root@zhenzhu src]# systemctl start mysqld    //mysql服务开启临时生效
[root@zhenzhu src]# systemctl enable mysqld    //mysql服务开机自启
[root@zhenzhu src]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled;>
   Active: active (running) since Tue 2022-12-27 17:54:05 CST; 2min>
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 13021 (mysqld)
    Tasks: 27 (limit: 12241)
   Memory: 293.1M
   CGroup: /system.slice/mysqld.service
           └─13021 /usr/sbin/mysqld --daemonize --pid-file=/var/run>

Dec 27 17:54:02 zhenzhu.example.com systemd[1]: Starting MySQL Serv>
Dec 27 17:54:05 zhenzhu.example.com systemd[1]: Started MySQL Serve>

[root@zhenzhu src]# ss -antl    //mysql服务端口号为3306
State  Recv-Q Send-Q Local Address:Port   Peer Address:Port Process 
LISTEN 0      128          0.0.0.0:22          0.0.0.0:*            
LISTEN 0      128             [::]:22             [::]:*            
LISTEN 0      80                 *:3306              *:*            
[root@zhenzhu src]#

登陆需要密码，但是没有设置密码，需要查看临时密码使用（单次使用，使用完失效）

[root@zhenzhu src]# grep password /var/log/mysqld.log 
2022-12-27T09:54:03.476981Z 1 [Note] A temporary password is generated for root@localhost: daV</9;.k-se    //密码为daV</9;.k-se
[root@zhenzhu src]#

链接mysql

[root@zhenzhu src]# mysql -uroot -p'daV</9;.k-se'    //密码有特殊字符用单引号引起
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.40

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

设置mysql的root用户密码（此root身份非主机root用户身份，这里指mysql的root）

mysql> set password = password("ZhenZhu123!");    //需符合密码策略，命令需要以；结尾
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

重新登陆测试

mysql> quit
Bye
[root@zhenzhu src]# mysql -uroot -p'ZhenZhu123!'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.40 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

服务初始化

[root@zhenzhu ~]# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root: 
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.

Estimated strength of the password: 100 
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n    //是否修改密码

 ... skipping.
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) : n    //是否拒绝root远程连接

 ... 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) : 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! 
[root@zhenzhu ~]#

授权

授权root用户在本机（登陆点ip）上登录访问所有数据库

mysql> grant all on *.* to 'root'@'192.168.204.1' identified by 'Zhen
nZhu123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

安装navicat，连接数据库

连接配置

连接成功

安装workbench，连接数据库

连接配置

连接成功

测试

创建数据库zhenzhu

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> CREATE DATABASE zhenzhu;
Query OK, 1 row affected (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zhenzhu            |
+--------------------+
5 rows in set (0.00 sec)

mysql>

navicat和workbench可读取