Linux系统之Mysql数据库用户基本管理

2022-11-05 108

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

RDS MySQL Serverless 基础系列，0.5-2RCU 50GB

RDS MySQL Serverless 高可用系列，价值2615元额度，1个月

简介： Linux系统之Mysql数据库用户基本管理

一、检查本地系统版本

[root@node ~]# cat /etc/os-release 
NAME="CentOS Linux"
VERSION="7 (Core)"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="7"
PRETTY_NAME="CentOS Linux 7 (Core)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:7"
HOME_URL="https://www.centos.org/"
BUG_REPORT_URL="https://bugs.centos.org/"

CENTOS_MANTISBT_PROJECT="CentOS-7"
CENTOS_MANTISBT_PROJECT_VERSION="7"
REDHAT_SUPPORT_PRODUCT="centos"
REDHAT_SUPPORT_PRODUCT_VERSION="7"

二、检查mysql服务状态

[root@node ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Sat 2022-11-05 15:55:26 CST; 3h 17min ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 57435 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 57412 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 57437 (mysqld)
    Tasks: 28
   Memory: 217.3M
   CGroup: /system.slice/mysqld.service
           └─57437 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Nov 05 15:55:23 node systemd[1]: Starting MySQL Server...
Nov 05 15:55:26 node systemd[1]: Started MySQL Server.

三、测试本地连接mysql

[root@node ~]# mysql
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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)

mysql>

四、新增mysql数据库用户

1.用户组成介绍

admin@'%'      #   %代表0.0.0.0任意机器使用admin账号登录
admin@'192.168.100.100'    # 代表此用户只能是从192.168.200.1这台机器登录

2.新建用户

mysql> create user "admin"@"%" identified by "123456";
Query OK, 0 rows affected (0.00 sec)

五、给用户授权

mysql>  grant all on *.* to 'admin'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

六、mysql用户的基本操作

1.修改用户密码

set password for admin=password('aayya666');

2.查看用户列表

mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| admin         | %         |
| root          | 127.0.0.1 |
| root          | ::1       |
|               | localhost |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
|               | node      |
| root          | node      |
+---------------+-----------+
9 rows in set (0.00 sec)

3.查看用户授权

mysql>  show grants for admin;
+--------------------------------------------------------------+
| Grants for admin@%                                           |
+--------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

4.删除用户

drop user 'admin'@'%';

七、测试远程登录mysql

[root@node ~]# mysql -h 192.168.3.166 -P3306 -uadmin -p123456
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 6
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> \s
--------------
mysql  Ver 14.14 Distrib 5.7.40, for Linux (x86_64) using  EditLine wrapper

Connection id:        6
Current database:    
Current user:        admin@web2.huawei.com
SSL:            Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.7.40 MySQL Community Server (GPL)
Protocol version:    10
Connection:        192.168.3.166 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:        3306
Uptime:            3 hours 34 min 15 sec

Threads: 1  Questions: 3268  Slow queries: 0  Opens: 299  Flush tables: 1  Open tables: 87  Queries per second avg: 0.254
--------------

mysql>