数据管理

数据库

数据库的类型
db2  oracle   mysql（mariadb）   sqlserver
数据库相当于高级的excel表格，其中的字段相当于列

一、数据库的设置

 1.重置虚拟机，修改网络配置、主机名和yum源
 vim /etc/sysconfig/network-scripts/ifcfg-eth0
 hostnamectl set-hostname testdb.westos.com
 vim /etc/yum.repos.d/rhel_dvd.repo

 2.安装数据库（mariadb）服务
  若不知道mariadb的安装包：
 [[email protected] ~]# yum search mariadb
  安装数据库：

 yum install mariadb-server.x86_64 -y

 3.数据库相关设置
  打开并设置开启自启动
 [[email protected] ~]# systemctl start mariadb

 [[email protected] ~]# systemctl enable mariadb

  查看数据库接口mysql    ##3306接口打开不安全
 [[email protected] ~]# netstat -antuple | grep mysql

 tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      27         68511      3272/mysqld

  关闭数据库接口（修改配置文件/etc/my.cnf）

 skip-networking=1

 [mysqld_safe]
 log-error=/var/log/mariadb/mariadb.log
 pid-file=/var/run/mariadb/mariadb.pid

  重启后再次查看数据库接口

 [[email protected] ~]# netstat -antuple | grep mysql

 [[email protected] ~]# mysql_secure_installation

 修改数据库密码 mysql_secure_installation

 

 Enter current password for root (enter for none):
 Set root password? [Y/n] Y
 Remove anonymous users? [Y/n]
 Disallow root login remotely? [Y/n]
 Remove test database and access to it? [Y/n]
 Reload privilege tables now? [Y/n]
 
 登陆数据库   ##注意：密码不要写在-p后（quit退出）

 [[email protected] ~]# mysql -uroot -p

 Enter password:
 
二、数据库编辑

 1.显示数据库（相当于ls）

MariaDB [(none)]> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

 2.进入数据库（相当于cd）

MariaDB [(none)]> use mysql;      ##切换到mysql数据库中

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

 3.显示数据库中的表格（相当于ls）

MariaDB [mysql]> show tables;

+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |

 4.查询user表中的Host,User,Password信息

MariaDB [mysql]> select Host,User,Password from user;
+-----------+------+-------------------------------------------+
| Host      | User | Password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| 127.0.0.1 | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| ::1       | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
+-----------+------+-------------------------------------------+

 5.查询user表的数据结构  desc user;

MariaDB [mysql]> desc user;

+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |     

 6.新建数据库 create database westos;

MariaDB [(none)]> create database westos;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| westos             |
+--------------------+

 7.新建数据库westos中的表格  create table linux

MariaDB [(none)]> use westos;
Database changed
MariaDB [westos]> show tables;
Empty set (0.00 sec)

MariaDB [westos]> create table linux (
    -> username varchar(50) not null,    ##字符类型为char，不能为空
    -> password varchar(50) not null     ##字符类型为char，不能为空
    -> );

Query OK, 0 rows affected (0.01 sec)

MariaDB [westos]> show tables;
+------------------+
| Tables_in_westos |
+------------------+
| linux            |
+------------------+

 8.在linux表格中插入信息  insert into linux values('hi','1234');

MariaDB [westos]> insert into linux values('hi','1234');
Query OK, 1 row affected (0.01 sec)

MariaDB [westos]> select * from linux;   ##查看linux表格中的所有信息
+----------+----------+
| username | password |
+----------+----------+
| hello    | 123      |
| hi       | 1234     |
+----------+----------+

 9.修改linux表格中的信息（例：password）

MariaDB [westos]> update linux set password='01230' where username='hi';

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [westos]> select * from linux;

+----------+----------+
| username | password |
+----------+----------+
| hello    | 123      |
| hi       | 01230    |
+----------+----------+

 10.在linux表格中添加age字段
MariaDB [westos]> alter table linux add age varchar(4) after username;
Query OK, 2 rows affected (0.05 sec)               
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [westos]> select * from linux;
+----------+------+----------+
| username | age  | password |
+----------+------+----------+
| hello    | NULL | 123      |
| hi       | NULL | 01230    |
+----------+------+----------+

 11.在linux表格中移除age字段

MariaDB [westos]> alter table linux drop age;

Query OK, 2 rows affected (0.03 sec)               
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [westos]> select * from linux;
+----------+----------+
| username | password |
+----------+----------+
| hello    | 123      |
| hi       | 01230    |
+----------+----------+
 
 12.修改表格名字

MariaDB [westos]> alter table linux rename mydata;

Query OK, 0 rows affected (0.01 sec)

MariaDB [westos]> show tables;
+------------------+
| Tables_in_westos |
+------------------+
| mydata           |
+------------------+

 13.删除表格中的某一行

MariaDB [westos]> delete from mydata where username='hi';

Query OK, 1 row affected (0.01 sec)

MariaDB [westos]> select * from mydata;
+----------+----------+
| username | password |
+----------+----------+
| hello    | 123      |
+----------+----------+

 14.删除表格

MariaDB [westos]> drop table mydata;

Query OK, 0 rows affected (0.01 sec)

MariaDB [westos]> show tables;
Empty set (0.00 sec)

 15.删除数据库

MariaDB [westos]> drop database westos;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

三、数据库用户管理

 1.用户创建  

MariaDB [(none)]> create user [email protected] identified by 'redhat';

Query OK, 0 rows affected (0.00 sec)
  ##[email protected] 本地用户    ##[email protected]‘%’ 远程用户

 2.用户授权

MariaDB [(none)]> grant INSERT,SELECT on westos.* to [email protected];

Query OK, 0 rows affected (0.01 sec)
  查看用户权限

MariaDB [(none)]> show grants for [email protected];

+-------------------------------------------------------------------------------------------------------------+
| Grants for [email protected]                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'hate'@'localhost' IDENTIFIED BY PASSWORD '*84BB5DF4823DA319BBF86C99624479A198E6EEE9' |
| GRANT SELECT, INSERT ON `westos`.* TO 'hate'@'localhost'                                                    |
+-------------------------------------------------------------------------------------------------------------+
 
 3.此时用hate用户登陆实验
  具备select权限，可以查看westos数据库：

MariaDB [(none)]> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| westos             |
+--------------------+

  具备insert权限，可以在westos.linux表格中插入：

MariaDB [(none)]> insert into westos.linux values('hi','123');
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> select * from westos.linux;
+----------+----------+
| username | password |
+----------+----------+
| hello    | 123      |
| hi       | 123      |
+----------+----------+
  不具备delete权限，不能删除：

MariaDB [(none)]> delete from westos.linux where username='hi';

ERROR 1142 (42000): DELETE command denied to user 'hate'@'localhost' for table 'linux'

 4.撤销用户权限（切换root用户）

MariaDB [(none)]> revoke INSERT on westos.* from [email protected];

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show grants for [email protected];

+-------------------------------------------------------------------------------------------------------------+
| Grants for [email protected]                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'hate'@'localhost' IDENTIFIED BY PASSWORD '*84BB5DF4823DA319BBF86C99624479A198E6EEE9' |
| GRANT SELECT ON `westos`.* TO 'hate'@'localhost'  
+-------------------------------------------------------------------------------------------------------------+
 

 5.重载授权表  flush privileges

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

 6.删除用户

MariaDB [(none)]> drop user [email protected];

Query OK, 0 rows affected (0.00 sec)

四、数据库资料备份

 1.数据库资料备份方式

mysqldump -uroot -predhat --all-database > /mnt/westos.all   ##所有数据库资料备份

mysqldump -uroot -predhat --all-database --no-data > /mnt/westos.err  ##只备份数据结构，不备份数据
mysqldump -uroot -predhat westos > /mnt/westos.sql  ##指定westos数据库的资料备份

 2.数据恢复
  注意：当备份的数据库不存在时，会报错（例：删除westos数据库）

[[email protected] ~]# mysql -uroot -predhat < /mnt/westos.sql

ERROR 1046 (3D000) at line 22: No database selected

  恢复方式1：修改备份文件  /mnt/westos.sql

CREATE DATABASE westos;
USE westos;

DROP TABLE IF EXISTS `linux`;

[[email protected] ~]# mysql -uroot -predhat < /mnt/westos.sql

  恢复后，登陆数据库查看
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| westos             |
+--------------------+

  恢复方式2：建立westos数据库

[[email protected] ~]# mysql -uroot -predhat -e "create database westos;"

[[email protected] ~]# mysql -uroot -predhat westos < /mnt/westos.sql

  登陆数据库查看
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| westos             |
+--------------------+

五、用户密码更改

 1.若用户密码忘记，关闭数据库服务，跳过认证列表

[[email protected] ~]# systemctl stop mariadb.service

[[email protected] ~]# mysqld_safe --skip-grant-tables &

[1] 2678
[[email protected] ~]# 180428 01:33:00 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
180428 01:33:00 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

 2.进入数据库修改（mysql回车即可）

MariaDB [(none)]> update mysql.user set Password='redhat' where User='root';

Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

 查看密码 select * from mysql.user  ##以上修改密码为明文
  | localhost | root | redhat     | Y

 以加密方式修改密码：

MariaDB [mysql]> update mysql.user set Password=password('123') where User='root';

Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

 再次查看密码（mysql.user）：
| localhost | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | Y

 3.查看mysql相关进程，全部关闭

[[email protected] ~]# ps aux | grep mysql

root      3228  0.0  0.1 113252  1560 pts/1    S    01:44   0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables
mysql     3383  0.1  9.2 859064 89276 pts/1    Sl   01:44   0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock
root      3416  0.0  0.0 112640   936 pts/1    R+   01:45   0:00 grep --color=auto mysql
  关闭后重启mysql服务，新密码登陆即可

[[email protected] ~]# systemctl restart mariadb.service

[[email protected] ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.35-MariaDB MariaDB Server