简单了解数据库--笔记01

一、安装数据库

1.安装mariadb

[root@c7-100 ~]# yum -y install mariadb-server mariadb

2.启动数据库

启动数据库
[root@c7-100 ~]# systemctl start mariadb.service设置开机自启动
[root@c7-100 ~]# systemctl enable mariadb.service 查看数据库版本
[root@c7-100 ~]# rpm -qa | grep mariadb-server
mariadb-server-5.5.68-1.el7.x86_64数据库停止
[root@c7-100 ~]# systemctl stop mariadb.service禁止开机自启动
[root@c7-100 ~]# systemctl disable mariadb.service查看数据库状态
[root@c7-100 ~]# systemctl status mariadb.service

3.数据库重要的目录文件

[root@c7-100 ~]# rpm -ql mariadb-server
/etc/logrotate.d/mariadb   //日志切割文件
[root@c7-100 ~]# cat /etc/logrotate.d/mariadb
#/var/log/mariadb/mariadb.log {属主6，属组4，普通用户0
#        create 640 mysql mysql
#        notifempty每天切割一次：week，mouth，hour
#        daily保留最近3份日志
#        rotate 3
#        missingok压缩处理
#        compress
#    postrotate
#	# just if mysqld is really running
#	if test -x /usr/bin/mysqladmin && \
#	   /usr/bin/mysqladmin ping &>/dev/null
#	then
#	   /usr/bin/mysqladmin flush-logs
#	fi
#    endscript
#}--------------------------------------------------
/etc/my.cnf.d/server.cnf   //***数据库配置文件***
[root@c7-100 ~]# grep -Ev "^$|^#" /etc/my.cnf.d/server.cnf
服务端配置信息
[server]
[mysqld]
[embedded]
[mysqld-5.5]
客户端配置
[mariadb]
[mariadb-5.5]--------------------------------------------------
/var/lib/mysql  //数据库的数据存放目录
[root@c7-100 ~]# ll /var/lib/mysql
总用量 28700
-rw-rw---- 1 mysql mysql    16384 8月   9 10:16 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 8月   9 10:16 aria_log_control
-rw-rw---- 1 mysql mysql 18874368 8月   9 10:16 ibdata1
-rw-rw---- 1 mysql mysql  5242880 8月   9 10:16 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 8月   9 10:16 ib_logfile1
drwx------ 2 mysql mysql     4096 8月   9 10:16 mysql
srwxrwxrwx 1 mysql mysql        0 8月   9 10:16 mysql.sock
drwx------ 2 mysql mysql     4096 8月   9 10:16 performance_schema
drwx------ 2 mysql mysql        6 8月   9 10:16 test--------------------------------------------------
/var/log/mariadb/mariadb.log   //错误日志
/var/run/mariadb   //进程文件pid号

二、登录数据库

[root@c7-100 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.68-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)退出数据库
MariaDB [(none)]> quit
Bye

三、给管理员设置密码

[root@c7-100 ~]# mysqladmin password '1'[root@c7-100 ~]# mysql -u root -p1  //p后面不能有空格
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.5.68-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]>

四、修改密码

1.知道原密码修改

[root@c7-100 ~]# mysqladmin -uroot -p1 password 'a1'
[root@c7-100 ~]# mysql -u root -p1
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@c7-100 ~]# mysql -u root -pa1
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 5.5.68-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]>

2.忘记原密码修改密码

1.关闭数据库

[root@c7-100 ~]# systemctl stop mariadb.service
如果关闭数据库，查看进程仍然存在
pkill mysqld

2.关闭授权表与网络启动数据库

--skip-grant-table 跳过验证权限表

--skip-network 跳过网络

[root@c7-100 ~]# mysqld_safe --skip-grant-table --skip-network &查看是否登录
[root@c7-100 ~]# ps -ef | grep mysqld

3.登录

[root@c7-100 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.68-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]>

4.修改用户密码

查询用户表
MariaDB [(none)]> select user,host,password from mysql.user;
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *7495041D24E489A0096DCFA036B166446FDDD992 |
| root | c7-100    |                                           |
| root | 127.0.0.1 |                                           |
| root | ::1       |                                           |
|      | localhost |                                           |
|      | c7-100    |                                           |
+------+-----------+-------------------------------------------+
6 rows in set (0.00 sec)修改用户表中的密码
MariaDB [(none)]> update mysql.user set password=PASSWORD("1") where user='root' and host='localhost';
Query OK, 1 row affected (0.00 sec)             #PASSWORD()这个是mysql自带的函数用于对密码加密
Rows matched: 1  Changed: 1  Warnings: 0刷新落盘（把内存的东西刷新到磁盘）
MariaDB [(none)]> flush privileges;

5.退出杀死mysqld进程(查看进程确保完全杀死)

[root@c7-100 ~]# pkill mysqld

6.正常启动

[root@c7-100 ~]# systemctl start mariadb.service 
[root@c7-100 ~]# netstat -tnulp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1229/sshd           
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      1359/master         
tcp        0      0 127.0.0.1:6010          0.0.0.0:*               LISTEN      1477/sshd: root@pts 
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      3423/mysqld         
tcp6       0      0 :::22                   :::*                    LISTEN      1229/sshd           
tcp6       0      0 ::1:25                  :::*                    LISTEN      1359/master         
tcp6       0      0 ::1:6010                :::*                    LISTEN      1477/sshd: root@pts 
udp        0      0 127.0.0.1:323           0.0.0.0:*                           888/chronyd         
udp6       0      0 ::1:323                 :::*                                888/chronyd

7.登录测试

[root@c7-100 ~]# mysql -u root -p1
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.68-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]>

五、用户管理

1.查看数据库中用户

MariaDB [(none)]> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 | //本机
| root | ::1       | //ipv6本机
|      | c7-100    |
| root | c7-100    |
|      | localhost |
| root | localhost |
+------+-----------+
6 rows in set (0.00 sec)

数据库用户的组成部分

用户名@'主机域'
代表这个用户必须只能在你的数据库本机登录
root@'localhost'
root@'127.0.0.1'代表只有主机ip是10.0.0.0/24网段的主机，可以登录使用
root@'10.0.0.%'
root@'10.0.0.0/24'
root@'10.0.0.0 255.255.255.0'hosts解析的主机域
root@'c7-100'----------------------------------------------
root@'%'  任意ip地址都能来登录，严禁这样设置

2.删除数据库用户

语法【drop user '用户名'@'主机域';】

MariaDB [(none)]> drop user 'root'@'c7-100';
Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | localhost |
+------+-----------+
2 rows in set (0.00 sec)

3.创建用户

语法【create user '用户名'@'主机域' identified by '密码'】

MariaDB [(none)]> create user a1@'10.0.0.%' identified by '1';
Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> select user,host from mysql.user;
+--------+-----------+
| user   | host      |
+--------+-----------+
| a1     | 10.0.0.%  |
| root   | 127.0.0.1 |
| root   | localhost |
+--------+-----------+
3 rows in set (0.00 sec)-------------------------------------------------------------
由于主机域的原因，你若还想本机登录这个a1，需要tcp登录，指定主机ip，否则无法登录成功
[root@c7-100 ~]# mysql -u a1 -p1 -h 10.0.0.100
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.68-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]>

4.修改用户

语法【update mysql.user set password=PASSWORD('新密码') where user='用户名' and host='主机域'】

无法修改名称，只能修改密码

MariaDB [(none)]> update mysql.user set password=PASSWORD('a1') where user='a1' and host='10.0.0.%';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
修改完必须落盘
MariaDB [(none)]> flush privileges;另一种mysql修改密码的方式
alter user 用户名@'主机域' identified by '新密码'

5.用户权限

1.查看用户权限

语法【show grants for 用户名@'主机域';】

MariaDB [(none)]> show grants for a1@'10.0.0.%';

2.查看当前登录用户

MariaDB [(none)]> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

3.给用户授权

语法【grant 权限 on 库.表 to '用户名'@'主机域';】

MariaDB [(none)]> grant all on *.* to a1@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
刷新落盘
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)all  //所有的权限
*.*  //所有的库所有的表