linux-运维进阶-23 MariaDB数据库管理系统

linux-运维进阶-23 MariaDB数据库管理系统

概念解析

MariaDB数据库管理系统

初始化MariaDB数据库

安装和初始化mariadb数据库服务

[root@localhost ~]# yum -y install mariadb mariadb-server
[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# systemctl enable mariadb
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
[root@localhost ~]# firewall-cmd --add-port=3306/tcp --permanent 
success
[root@localhost ~]# firewall-cmd --reload
success

初始化数据库

• 设置root管理员在数据库中的密码值（注意，该密码并非root管理员在系统中的密码，这里的密码值默认应该为空，可直接按回车键）。
• 设置root管理员在数据库中的专有密码。
• 随后删除匿名账户，并使用root管理员从远程登录数据库，以确保数据库上运行的业务的安全性。
• 删除默认的测试数据库，取消测试数据库的一系列访问权限。
• 刷新授权列表，让初始化的设定立即生效。

[root@localhost ~]# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB 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? [Y/n] 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? [Y/n] y
 ... Success!

By default, MariaDB 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? [Y/n] 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? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!
[root@localhost ~]# 

登录数据库

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (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)]> 

查看数据库

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

修改当前用户的密码

MariaDB [(none)]> set password = password('123456');
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> 

管理账户及授权

创建一个用户并设置密码

MariaDB [(none)]> create user aaa@localhost identified by '123456'
    -> ;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> 

注意，这里我是忘了敲分号，不过第二行补敲也是可以的。

查看这个用户的指定属性

MariaDB [(none)]> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> select Host,User,Password from user where User= 'aaa';
+-----------+------+-------------------------------------------+
| Host      | User | Password                                  |
+-----------+------+-------------------------------------------+
| localhost | aaa  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+------+-------------------------------------------+
1 row in set (0.00 sec)

MariaDB [mysql]> 

为用户赋予权限

grant命令用于为账户进行授权

命令	作用
GRANT 权限 ON 数据库.表单名称 TO 账户名@主机名	对某个特定数据库中的特定表单给予授权
GRANT权限ON数据库.*TO账户名@主机名	对某个特定数据库中的所有表单给予授权
GRANT权限ON*.*TO账户名@主机名	对所有数据库及所有表单给予授权
GRANT权限1,权限2 ON 数据库.*TO账户名@主机名	对某个数据库中的所有表单给予多个授权
GRANT ALL PRIVILEGES ON *.*TO账户名@主机名	对所有数据库及所有表单给予全部授权（需谨慎操作）

为用户aaa授予增、删、改、查的权限

MariaDB [mysql]> grant select,update,insert on mysql.user to aaa@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> show grants for aaa@localhost;
+------------------------------------------------------------------------------------------------------------+
| Grants for aaa@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'aaa'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT SELECT, INSERT, UPDATE ON `mysql`.`user` TO 'aaa'@'localhost'                                        |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [mysql]> 

移除aaa用户增、删、改、查的权限

MariaDB [mysql]> revoke select,update,insert on mysql.user from aaa@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> show grants for aaa@localhost;
+------------------------------------------------------------------------------------------------------------+
| Grants for aaa@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'aaa'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

创建数据库与表单

命令	作用
CREATE DATABASE 数据库名称	创建新的数据库
DESCRIBE 表单名称	描述表单
UPDATE表单名称SET attribute=新值WHERE attribute>原始值	更新表单中的数据
USE数据库名称	指定使用的数据库
SHOW databases	显示当前已有的数据库
SHOW tables	显示当前数据库中的表单
SELECT * FROM表单名称	从表单中选中某个记录值
DELETE FROM表单名WHERE attribute=值	从表单中删除某个记录值

创建数据库

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

MariaDB [mysql]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| DBaaa              |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.01 sec)

MariaDB [mysql]> 

使用这个库以及在新建的库里新建数据表，并且查询新建表的结构

MariaDB [mysql]> use DBaaa;
Database changed
MariaDB [DBaaa]> create table student(
    -> name char(15),
    -> school char(15),
    -> age int
    -> );
Query OK, 0 rows affected (0.00 sec)

MariaDB [DBaaa]> desc student;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| name   | char(15) | YES  |     | NULL    |       |
| school | char(15) | YES  |     | NULL    |       |
| age    | int(11)  | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

MariaDB [DBaaa]> 

管理表单及数据——增删改查

插入一条学生信息（增）

MariaDB [DBaaa]> select * from student;
Empty set (0.00 sec)

MariaDB [DBaaa]> insert into student(name,school,age) values('zhangsan','beida',18);
Query OK, 1 row affected (0.00 sec)

MariaDB [DBaaa]> select * from student;
+----------+--------+------+
| name     | school | age  |
+----------+--------+------+
| zhangsan | beida  |   18 |
+----------+--------+------+
1 row in set (0.00 sec)

MariaDB [DBaaa]> 

修改这条信息（改）

如果数据库里只有这一个人，我们可以这样修改他的信息，即修改所有成员的年龄为20

MariaDB [DBaaa]> update student set age=20;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [DBaaa]> select * from student;
+----------+--------+------+
| name     | school | age  |
+----------+--------+------+
| zhangsan | beida  |   20 |
+----------+--------+------+
1 row in set (0.00 sec)

MariaDB [DBaaa]> 

如果数据库里不只这一个人，如下，我们再加几个，然后我们可以这样修改他的信息，即针对某个人修改其年龄。

MariaDB [DBaaa]> insert into student(name,school,age) values('lisi','qinghua',16);
Query OK, 1 row affected (0.00 sec)

MariaDB [DBaaa]> insert into student(name,school,age) values('wangwu','fudan',17);
Query OK, 1 row affected (0.00 sec)

MariaDB [DBaaa]> insert into student(name,school,age) values('zhaoliu','zheda',19);
Query OK, 1 row affected (0.00 sec)

MariaDB [DBaaa]> select * from student;
+----------+---------+------+
| name     | school  | age  |
+----------+---------+------+
| zhangsan | beida   |   20 |
| lisi     | qinghua |   16 |
| wangwu   | fudan   |   17 |
| zhaoliu  | zheda   |   19 |
+----------+---------+------+
4 rows in set (0.00 sec)

MariaDB [DBaaa]> update student set age=50 where name='zhangsan';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [DBaaa]> select * from student;
+----------+---------+------+
| name     | school  | age  |
+----------+---------+------+
| zhangsan | beida   |   50 |
| lisi     | qinghua |   16 |
| wangwu   | fudan   |   17 |
| zhaoliu  | zheda   |   19 |
+----------+---------+------+
4 rows in set (0.00 sec)

MariaDB [DBaaa]> 

删除数据库的信息（删）

如果数据库里不只有一个人，我们可以这样删除他的信息，即针对某个人删除其信息

MariaDB [DBaaa]> select * from student;
+----------+---------+------+
| name     | school  | age  |
+----------+---------+------+
| zhangsan | beida   |   50 |
| lisi     | qinghua |   16 |
| wangwu   | fudan   |   17 |
| zhaoliu  | zheda   |   19 |
+----------+---------+------+
4 rows in set (0.00 sec)

MariaDB [DBaaa]> delete from student where name='zhangsan';
Query OK, 1 row affected (0.00 sec)

MariaDB [DBaaa]> select * from student;
+---------+---------+------+
| name    | school  | age  |
+---------+---------+------+
| lisi    | qinghua |   16 |
| wangwu  | fudan   |   17 |
| zhaoliu | zheda   |   19 |
+---------+---------+------+
3 rows in set (0.00 sec)

MariaDB [DBaaa]> 

一次性删除所有表项：

MariaDB [DBaaa]> select * from student;
+---------+---------+------+
| name    | school  | age  |
+---------+---------+------+
| lisi    | qinghua |   16 |
| wangwu  | fudan   |   17 |
| zhaoliu | zheda   |   19 |
+---------+---------+------+
3 rows in set (0.00 sec)

MariaDB [DBaaa]> delete from student;
Query OK, 3 rows affected (0.00 sec)

MariaDB [DBaaa]> select * from student;
Empty set (0.00 sec)

MariaDB [DBaaa]> 

数据表中插入多条信息

MariaDB [DBaaa]> select * from student;
Empty set (0.00 sec)

MariaDB [DBaaa]> insert into student(name,school,age) values('lisi','qinghua',16);
Query OK, 1 row affected (0.00 sec)

MariaDB [DBaaa]> insert into student(name,school,age) values('wangwu','fudan',17);
Query OK, 1 row affected (0.00 sec)

MariaDB [DBaaa]> insert into student(name,school,age) values('zhaoliu','zheda',19);
Query OK, 1 row affected (0.00 sec)

MariaDB [DBaaa]> select * from student;
+---------+---------+------+
| name    | school  | age  |
+---------+---------+------+
| lisi    | qinghua |   16 |
| wangwu  | fudan   |   17 |
| zhaoliu | zheda   |   19 |
+---------+---------+------+
3 rows in set (0.00 sec)

MariaDB [DBaaa]> 

查询数据库中的数据——查

​ 查找某条信息，要想让查询结果更加精准，就需要结合使用select与where命令了。其中，where命令是在数据库中进行匹配查询的条件命令。通过设置查询条件，就可以仅查找出符合该条件的数据

参数	作用
=	相等
<>或!=	不相等
>	大于
<	小于
>=	大于或等于
<=	小于或等于
BETWEEN	在某个范围内
LIKE	搜索一个例子
IN	在列中搜索多个值

MariaDB [DBaaa]> select * from student;
+---------+---------+------+
| name    | school  | age  |
+---------+---------+------+
| lisi    | qinghua |   16 |
| wangwu  | fudan   |   17 |
| zhaoliu | zheda   |   19 |
+---------+---------+------+
3 rows in set (0.00 sec)

MariaDB [DBaaa]> select * from student where age<18;
+--------+---------+------+
| name   | school  | age  |
+--------+---------+------+
| lisi   | qinghua |   16 |
| wangwu | fudan   |   17 |
+--------+---------+------+
2 rows in set (0.00 sec)

MariaDB [DBaaa]> select * from student where age>18;
+---------+--------+------+
| name    | school | age  |
+---------+--------+------+
| zhaoliu | zheda  |   19 |
+---------+--------+------+
1 row in set (0.00 sec)

MariaDB [DBaaa]> exit
Bye
[root@localhost ~]# 

数据库的备份和恢复

mysqldump命令用于备份数据库数据，格式为“mysqldump [参数] [数据库名称]”。

其中参数与mysql命令大致相同，-u参数用于定义登录数据库的账户名称，-p参数代表密码提示符

[root@localhost ~]# mysqldump -u root -p DBaaa > /root/DBaaa.dump
Enter password: 
[root@localhost ~]# 

尝试删除eagleslab数据库，然后再恢复

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (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)]> drop database DBaaa;
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

删完库，我们给它加回来，但是事实上加回来的只是一个空壳
MariaDB [(none)]> create database DBaaa;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| DBaaa              |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> use DBaaa;
Database changed
MariaDB [DBaaa]> select * from student;
ERROR 1146 (42S02): Table 'DBaaa.student' doesn't exist
MariaDB [DBaaa]> exit
Bye
[root@localhost ~]# 


可以看到这个空壳数据库里，并没有student表。

将刚刚备份的文件导入数据库

[root@localhost ~]# mysql -u root -p DBaaa < /root/DBaaa.dump 
Enter password: 
[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 18
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (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)]> use DBaaa;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [DBaaa]> show tables;
+-----------------+
| Tables_in_DBaaa |
+-----------------+
| student         |
+-----------------+
1 row in set (0.00 sec)

MariaDB [DBaaa]> select * from student;
+---------+---------+------+
| name    | school  | age  |
+---------+---------+------+
| lisi    | qinghua |   16 |
| wangwu  | fudan   |   17 |
| zhaoliu | zheda   |   19 |
+---------+---------+------+
3 rows in set (0.00 sec)

MariaDB [DBaaa]> exit
Bye
[root@localhost ~]# 

可以看到我们的数据恢复成功了。

数据库密码泄露

不安全的数据库登录方式：

mysql -uroot -p123456

​ 这种登录方式可以用history 查看到数据库账户密码，是极不安全的

[root@localhost ~]# mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (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)]> exit
Bye
[root@localhost ~]# history 1
   87  history 1
[root@localhost ~]# history 3
   86  mysql -uroot -p123456
   87  history 1
   88  history 3
[root@localhost ~]# 

注意，history后面的参数是显示历史命令的条数。

建议还是用正常的mysql -u root -p

再输入看不见的密码比较好

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 23
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (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)]>  create user bbb@localhost identified by '123456'
    -> ;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]>  select Host,User,Password from user where User= 'bbb';
+-----------+------+-------------------------------------------+
| Host      | User | Password                                  |
+-----------+------+-------------------------------------------+
| localhost | bbb  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+------+-------------------------------------------+
1 row in set (0.00 sec)

MariaDB [mysql]> 


MariaDB [mysql]> exit
Bye
[root@localhost ~]# history 2
   89  mysql -u root -p
   90  history 2
[root@localhost ~]# 

如上，使用history看不到密码，进到mysql里查看bbb的密码也只是乱码。

本篇到此结束