第十六章 使用MariaDB数据库管理系统
一、数据库管理系统
1、数据库介绍
2、数据库管理系统介绍
二、初始化MariaDB服务
1、安装MariaDB服务程序
[root@centos ~]# yum install mariadb mariadb-server
CentOS-8.5.2111 - Base - mirr 38 kB/s | 3.9 kB 00:00
CentOS-8.5.2111 - Extras - mi 21 kB/s | 1.5 kB 00:00
CentOS-8.5.2111 - AppStream - 46 kB/s | 4.3 kB 00:00
依赖关系解决。
==============================================================
软件包 架构 版本 仓库 大小
==============================================================
安装:
mariadb x86_64 3:10.3.28-1.module_el8.3.0+757+d382997d
AppStream 6.0 M
mariadb-server x86_64 3:10.3.28-1.module_el8.3.0+757+d382997d
AppStream 16 M
安装依赖关系:
mariadb-common x86_64 3:10.3.28-1.module_el8.3.0+757+d382997d
AppStream 64 k
mariadb-connector-c x86_64 3.1.11-2.el8_3 AppStream 200 k
mariadb-connector-c-config
noarch 3.1.11-2.el8_3 AppStream 15 k
mariadb-errmsg x86_64 3:10.3.28-1.module_el8.3.0+757+d382997d
AppStream 234 k
perl-DBD-MySQL x86_64 4.046-3.module_el8.3.0+419+c2dec72b
AppStream 156 k
安装弱的依赖:
mariadb-backup x86_64 3:10.3.28-1.module_el8.3.0+757+d382997d
AppStream 6.1 M
mariadb-gssapi-server x86_64 3:10.3.28-1.module_el8.3.0+757+d382997d
AppStream 51 k
mariadb-server-utils x86_64 3:10.3.28-1.module_el8.3.0+757+d382997d
AppStream 1.1 M
启用模块流:
mariadb 10.3
perl-DBD-MySQL 4.046
事务概要
==============================================================
安装 10 软件包
总下载：30 M
安装大小：153 M
确定吗？[y/N]： y
下载软件包：
(1/10): mariadb-common-10.3.2 216 kB/s | 64 kB 00:00
(2/10): mariadb-connector-c-3 279 kB/s | 200 kB 00:00
(3/10): mariadb-connector-c-c 155 kB/s | 15 kB 00:00
(4/10): mariadb-errmsg-10.3.2 308 kB/s | 234 kB 00:00
(5/10): mariadb-gssapi-server 239 kB/s | 51 kB 00:00
(6/10): mariadb-10.3.28-1.mod 326 kB/s | 6.0 MB 00:18
(7/10): mariadb-backup-10.3.2 325 kB/s | 6.1 MB 00:19
(8/10): perl-DBD-MySQL-4.046- 281 kB/s | 156 kB 00:00
(9/10): mariadb-server-utils- 319 kB/s | 1.1 MB 00:03
(10/10): mariadb-server-10.3. 328 kB/s | 16 MB 00:50
--------------------------------------------------------------
总计 587 kB/s | 30 MB 00:52
运行事务检查
事务检查成功。
运行事务测试
事务测试成功。
运行事务
准备中 : 1/1
安装 : mariadb-connector-c-config-3.1.11-2.el8_3 1/10
安装 : mariadb-common-3:10.3.28-1.module_el8.3.0 2/10
安装 : mariadb-errmsg-3:10.3.28-1.module_el8.3.0 3/10
安装 : perl-DBD-MySQL-4.046-3.module_el8.3.0+419 4/10
安装 : mariadb-connector-c-3.1.11-2.el8_3.x86_64 5/10
安装 : mariadb-backup-3:10.3.28-1.module_el8.3.0 6/10
安装 : mariadb-gssapi-server-3:10.3.28-1.module_ 7/10
安装 : mariadb-server-utils-3:10.3.28-1.module_e 8/10
运行脚本: mariadb-server-3:10.3.28-1.module_el8.3.0 9/10
安装 : mariadb-server-3:10.3.28-1.module_el8.3.0 9/10
运行脚本: mariadb-server-3:10.3.28-1.module_el8.3.0 9/10
安装 : mariadb-3:10.3.28-1.module_el8.3.0+757+d3 10/10
运行脚本: mariadb-3:10.3.28-1.module_el8.3.0+757+d3 10/10
验证 : mariadb-3:10.3.28-1.module_el8.3.0+757+d3 1/10
验证 : mariadb-backup-3:10.3.28-1.module_el8.3.0 2/10
验证 : mariadb-common-3:10.3.28-1.module_el8.3.0 3/10
验证 : mariadb-connector-c-3.1.11-2.el8_3.x86_64 4/10
验证 : mariadb-connector-c-config-3.1.11-2.el8_3 5/10
验证 : mariadb-errmsg-3:10.3.28-1.module_el8.3.0 6/10
验证 : mariadb-gssapi-server-3:10.3.28-1.module_ 7/10
验证 : mariadb-server-3:10.3.28-1.module_el8.3.0 8/10
验证 : mariadb-server-utils-3:10.3.28-1.module_e 9/10
验证 : perl-DBD-MySQL-4.046-3.module_el8.3.0+419 10/10
已安装:
mariadb-3:10.3.28-1.module_el8.3.0+757+d382997d.x86_64
mariadb-backup-3:10.3.28-1.module_el8.3.0+757+d382997d.x86_64
mariadb-common-3:10.3.28-1.module_el8.3.0+757+d382997d.x86_64
mariadb-connector-c-3.1.11-2.el8_3.x86_64
mariadb-connector-c-config-3.1.11-2.el8_3.noarch
mariadb-errmsg-3:10.3.28-1.module_el8.3.0+757+d382997d.x86_64
mariadb-gssapi-server-3:10.3.28-1.module_el8.3.0+757+d382997d.x86_64
mariadb-server-3:10.3.28-1.module_el8.3.0+757+d382997d.x86_64
mariadb-server-utils-3:10.3.28-1.module_el8.3.0+757+d382997d.x86_64
perl-DBD-MySQL-4.046-3.module_el8.3.0+419+c2dec72b.x86_64
完毕！
2、启动服务并设置开机自启动
[root@centos ~]# systemctl start mariadb.service && systemctl enable mariadb.service
Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.
3、初始化操作
（1）、设置密码
//设置root管理员在数据库中的密码值（注意：该密码并非root管理员在系统中的密码，这里的密码值默认应该为空，可直接按回车键）
[root@centos ~]# 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...
（2）、设置专有密码
//设置root管理员在数据库中的专有密码
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!
（3）、删除匿名
//删除匿名账户，并使用root管理员从远程登陆数据库，以确保数据库上运行的业务的安全性
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] n //允许管理员从远程登陆
... Success!
（3）、删除默认数据库及权限
//删除默认的测试数据库，取消测试数据库的一系列访问权限
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!
（4）、立即生效
//刷新授权列表，让初始化的设定立即生效
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!
4、防火墙策略
[root@centos ~]# firewall-cmd --add-service=mysql --permanent
success
[root@centos ~]# firewall-cmd --reload
success
5、登录数据库
[root@centos ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 50
Server version: 10.3.28-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)]>
6、修改数据库密码值
//修改密码值
MariaDB [(none)]> set password = PASSWORD('centos');
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> exit
Bye
//登录数据库验证
[root@centos ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 51
Server version: 10.3.28-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)]>
三、管理用户以及授权
1、创建数据库管理账户
命令格式：create user 用户名@主机名 identified by '密码'
MariaDB [(none)]> create user kali@localhost identified by '123456';
Query OK, 0 rows affected (0.000 sec)
2、查询账户kali的信息
//查询主机名称、账户名称、加密的密码值
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="kali";
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| localhost | kali | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+------+-------------------------------------------+
1 row in set (0.000 sec)
3、切换用户查看数据库
//退出数据库
MariaDB [mysql]> exit
Bye
//使用kali登录数据库
[root@centos ~]# mysql -u kali -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 52
Server version: 10.3.28-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)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.000 sec)
4、GRANT命令的常见格式以及解释
5、更改账户kali的权限
（1）、授予查询、更新、删除以及插入等权限
[root@centos ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 60
Server version: 10.3.28-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)]> GRANT SELECT,UPDATE,DELETE,INSERT on mysql.user TO kali@localhost;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> show grants for kali@localhost;
+----------------------------------------------------------------------------+
| Grants for kali@localhost |
+----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `kali`@`localhost` IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `mysql`.`user` TO `kali`@`localhost`|
+----------------------------------------------------------------------------+
2 rows in set (0.000 sec)
（2）、移除授权命令
MariaDB [mysql]> REVOKE SELECT,UPDATE,DELETE,INSERT ON mysql.user FROM kali@localhost;
Query OK, 0 rows affected (0.000 sec)
（3）、删除用户
MariaDB [mysql]> DROP user kali@localhost;
Query OK, 0 rows affected (0.000 sec)
四、创建数据库与表单
1、用于创建数据库的命令以及作用
2、创建数据库
//创建数据库
MariaDB [mysql]> create database test;
Query OK, 1 row affected (0.000 sec)
//查看数据库
MariaDB [mysql]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.000 sec)
3、创建表单
//创建表单
MariaDB [mysql]> use test;
Database changed
MariaDB [test]> create table mybook (name char(15),price int,pages int);
Query OK, 0 rows affected (0.011 sec)
//查看数据表
MariaDB [test]> describe mybook;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(15) | YES | | NULL | |
| price | int(11) | YES | | NULL | |
| pages | int(11) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.001 sec)
五、管理表单及数据
1、插入数据
//插入数据
MariaDB [test]> insert into mybook(name,price,pages) values('Linux','60','500');
Query OK, 1 row affected (0.001 sec)
//查询数据表内容
MariaDB [test]> select * from mybook;
+-------+-------+-------+
| name | price | pages |
+-------+-------+-------+
| Linux | 60 | 500 |
+-------+-------+-------+
1 row in set (0.000 sec)
2、更新数据表
//更新数据表
MariaDB [test]> update mybook set price=55;
Query OK, 1 row affected (0.001 sec)
Rows matched: 1 Changed: 1 Warnings: 0
//查询数据表内容
MariaDB [test]> select * from mybook;
+-------+-------+-------+
| name | price | pages |
+-------+-------+-------+
| Linux | 55 | 500 |
+-------+-------+-------+
1 row in set (0.000 sec)
3、删除数据表
//删除数据表
MariaDB [test]> delete from mybook;
Query OK, 1 row affected (0.001 sec)
//查询数据表
MariaDB [test]> select * from mybook;
Empty set (0.000 sec)
4、where命令中使用的参数以及作用
5、实战
（1）、插入四条数据
MariaDB [test]> insert into mybook(name,price,pages) values('kali','30','518');
Query OK, 1 row affected (0.001 sec)
MariaDB [test]> insert into mybook(name,price,pages) values('oscp','50','518');
Query OK, 1 row affected (0.001 sec)
MariaDB [test]> insert into mybook(name,price,pages) values('cisp','80','518');
Query OK, 1 row affected (0.001 sec)
MariaDB [test]> insert into mybook(name,price,pages) values('redhat','100','518');
Query OK, 1 row affected (0.001 sec)
（2）、查询价格大于75元和价格不等于80元的图书
//价格大于75元的图书
MariaDB [test]> select * from mybook where price>75;
+--------+-------+-------+
| name | price | pages |
+--------+-------+-------+
| cisp | 80 | 518 |
| redhat | 100 | 518 |
+--------+-------+-------+
2 rows in set (0.000 sec)
//价格不等于80元的图书
MariaDB [test]> select * from mybook where price!=80;
+--------+-------+-------+
| name | price | pages |
+--------+-------+-------+
| kali | 30 | 518 |
| oscp | 50 | 518 |
| redhat | 100 | 518 |
+--------+-------+-------+
3 rows in set (0.000 sec)
（3）、查找价格为30元，页数为518页的图书
MariaDB [test]> select * from mybook where price=30 and pages=518;
+------+-------+-------+
| name | price | pages |
+------+-------+-------+
| kali | 30 | 518 |
+------+-------+-------+
1 row in set (0.000 sec)
六、数据库的备份及恢复
1、mysqldump命令
命令格式：mysqldump [参数] [数据库名称]
2、备份数据库
//将test数据库中的内容导出成文件并保存到root管理员的家目录中
[root@centos ~]# mysqldump -u root -p test > /root/test.dump
Enter password: //数据库root的密码
3、编辑数据库管理系统
//登录数据库
[root@centos ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 63
Server version: 10.3.28-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.
//彻底删除test数据库
MariaDB [(none)]> drop database test;
Query OK, 1 row affected (0.004 sec)
//查询数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
//创建test数据库
MariaDB [(none)]> create database test;
Query OK, 1 row affected (0.000 sec)
4、数据恢复
//将备份的数据库文件导入
[root@centos ~]# mysql -u root -p test < /root/test.dump
Enter password:
//登录数据库
[root@centos ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 65
Server version: 10.3.28-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)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.000 sec)
//使用test数据库
MariaDB [(none)]> use test;
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 [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| mybook |
+----------------+
1 row in set (0.000 sec)
//查看mybook数据表
MariaDB [test]> describe mybook;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(15) | YES | | NULL | |
| price | int(11) | YES | | NULL | |
| pages | int(11) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.001 sec)
//查看mybook数据表的内容
MariaDB [test]> select * from mybook;
+--------+-------+-------+
| name | price | pages |
+--------+-------+-------+
| kali | 30 | 518 |
| oscp | 50 | 518 |
| cisp | 80 | 518 |
| redhat | 100 | 518 |
+--------+-------+-------+
4 rows in set (0.000 sec)