前言
身为一个三年的运维工程师，从开发转测开再转运维，都是不断学习的过程，未必开发才是最优秀的，找到适合自己的职业不断深耕，你也会在自己的行业大放光彩，本系列依照《Linux就该这么学》系列随书学习练习操作，将一些课本上不顺畅的地方，全部以最简方式免费开源展示给大家，资源大家可以自行百度，也希望大家多关注刘遄老师的第二版关于centos8的丛书，学习最前沿的Linux相关技术。
常用命令汇总
数据库管理系统
数据库是指按照某些特定结构来存储数据资料的数据仓库
初始化 MariaDB 服务
[root@mail ~]# yum install mariadb mariadb-server -y
Loaded plugins: fastestmirror, langpacks
Existing lock /var/run/yum.pid: another copy is running as pid 9523.
Another app is currently holding the yum lock; waiting for it to exit...
The other application is: PackageKit
Memory : 37 M RSS (394 MB VSZ)
Started: Mon Jul 31 18:43:07 2023 - 00:01 ago
State : Running, pid: 9523
报这个错说明yum被别的占用了，所以把yum相关运行的文件删一下就行
[root@mail ~]# rm -f /var/run/yum.pid
[root@mail ~]# yum install mariadb mariadb-server -y
初始化操作涉及下面 5 个步骤。
1.设置 root 管理员在数据库中的密码值（注意，该密码并非 root 管理员在系统中的密码， 这里的密码值默认应该为空，可直接按回车键）。
2.设置 root 管理员在数据库中的专有密码。
3.随后删除匿名账户，并使用 root 管理员从远程登录数据库，以确保数据库上运行的业 务的安全性。
4.删除默认的测试数据库，取消测试数据库的一系列访问权限。
5.刷新授权列表，让初始化的设定立即生效
启动
[root@mail ~]# systemctl start mariadb
配置
[root@mail ~]# mysql_secure_installation
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: 输入要为 root 管理员设置的数据库密码
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（禁止 root 管理员从远程登录）
... 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（删除 test 数据库并取消对它的访问权限）
- 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（刷新授权表，让初始化后的设定立即生效）
生产环境中都需要使用站库分离的技术，如果需要让 root 管理员远程访问数据库，可在上面的初始化操作中设置策略，以允许 root 管 理员从远程访问。还需要设置防火墙，使其放行对数据库服务程序的访问请求，数据库 服务程序默认会占用 3306 端口，在防火墙策略中服务名称统一叫作 mysql：
[root@mail ~]# firewall-cmd --permanent --add-service=mysql
success
[root@mail ~]# firewall-cmd --reload
success
登录
[root@mail ~]# mysql -u root -p
Enter password: 此处输入 root 管理员在数据库中的密码
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.68-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('linux');（修改密码）
Query OK, 0 rows affected (0.00 sec)
管理账户以及授权
创建数据库管理账户
数据库大小写不敏感，且以分号结尾
MariaDB [(none)]> create user weihongbin@localhost identified by 'linux';
Query OK, 0 rows affected (0.01 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='weihongbin';
+-----------+------------+-------------------------------------------+
| host | user | password |
+-----------+------------+-------------------------------------------+
| localhost | weihongbin | *6F3CAE7C3BBB2A5B5D933738682953BC21AEBEE7 |
+-----------+------------+-------------------------------------------+
1 row in set (0.00 sec)
使用grant命令授权并查看权限
MariaDB [mysql]> grant select,update,delete,insert on mysql.user to weihongbin@localhost;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> show grants for weihongbin@localhost;
+-------------------------------------------------------------------------------------------------------------------+
| Grants for weihongbin@localhost |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'weihongbin'@'localhost' IDENTIFIED BY PASSWORD '*6F3CAE7C3BBB2A5B5D933738682953BC21AEBEE7' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `mysql`.`user` TO 'weihongbin'@'localhost' |
+-------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
移除授权的命令（revoke）与授权命令（grant）不同之外，其余部分都 是一致的
MariaDB [(none)]> revoke select,update,delete,insert on mysql.user from weihongbin@localhost;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show grants for weihongbin@localhost;
+-------------------------------------------------------------------------------------------------------------------+
| Grants for weihongbin@localhost |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'weihongbin'@'localhost' IDENTIFIED BY PASSWORD '*6F3CAE7C3BBB2A5B5D933738682953BC21AEBEE7' |
+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
创建数据库与表单
一个数据库可以存放多个数据表，数据表单是数据库中 最重要最核心的内容
创建库
MariaDB [(none)]> create database linux;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| linux |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
创建表（定义存储数据内容的结构：名字、价格、页）
MariaDB [(none)]> use linux;
Database changed
MariaDB [linux]> create table test (name char(15),price int,pages int);
Query OK, 0 rows affected (0.00 sec)
MariaDB [linux]> describe test;
+-------+----------+------+-----+---------+-------+
| 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.02 sec)
管理表单及数据
插入数据
MariaDB [(none)]> use linux
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 [linux]> insert into test(name,price,pages) values('weihongbin','100','1000');
Query OK, 1 row affected (0.01 sec)
MariaDB [linux]> select * from test;
+------------+-------+-------+
| name | price | pages |
+------------+-------+-------+
| weihongbin | 100 | 1000 |
+------------+-------+-------+
1 row in set (0.00 sec)
修改
MariaDB [linux]> update test set price=55;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [linux]> select price from test;
+-------+
| price |
+-------+
| 55 |
+-------+
1 row in set (0.00 sec)
删除
MariaDB [linux]> delete from test;
Query OK, 1 row affected (0.00 sec)
MariaDB [linux]> select * from test;
Empty set (0.00 sec)
条件查询
1.做数据
MariaDB [linux]> insert into test(name,price,pages) values('weihongbin1','100','100');
Query OK, 1 row affected (0.00 sec)
MariaDB [linux]> insert into test(name,price,pages) values('weihongbin2','200','200');
Query OK, 1 row affected (0.00 sec)
MariaDB [linux]> insert into test(name,price,pages) values('weihongbin3','300','300');
Query OK, 1 row affected (0.00 sec)
MariaDB [linux]> insert into test(name,price,pages) values('weihongbin4','400','400');
Query OK, 1 row affected (0.00 sec)
where命令
MariaDB [linux]> select * from test where price>99;
+-------------+-------+-------+
| name | price | pages |
+-------------+-------+-------+
| weihongbin1 | 100 | 100 |
| weihongbin2 | 200 | 200 |
| weihongbin3 | 300 | 300 |
| weihongbin4 | 400 | 400 |
+-------------+-------+-------+
4 rows in set (0.00 sec)
数据库的备份及恢复
mysqldump 命令用于备份数据库数据，格式为“mysqldump [参数] [数据库名称]”
[root@mail ~]# mysqldump -u root -p linux > /root/linux.dump
Enter password:
[root@mail ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.68-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 linux;
Query OK, 1 row affected (0.03 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
MariaDB [(none)]> create database linux;
Query OK, 1 row affected (0.00 sec)
必须先把库建起来
接下来是数据导入
[root@mail ~]# mysql -u root -p linux < /root/linux.dump
Enter password:
[root@mail ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.5.68-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 linux
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 [linux]> show tables;
+-----------------+
| Tables_in_linux |
+-----------------+
| test |
+-----------------+
1 row in set (0.00 sec)
MariaDB [linux]> describe test;
+-------+----------+------+-----+---------+-------+
| 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.00 sec)
结语
简问简答
初始化 MariaDB 或 MySQL 数据库管理系统的命令是什么？
答：是 mysql_secure_installation 命令，建议每次安装 MariaDB 或 MySQL 数据库管理系统 后都执行这条命令
用来查看已有数据库或数据表单的命令是什么？
答：要查看当前已有的数据库列表，需执行 SHOW databases;命令；要查看已有的数据表单 列表，则需执行 SHOW tables;命令。
切换至某个指定数据库的命令是什么？
答：执行“use 数据库名称”命令即可切换成功。
若想针对某个账户进行授权或取消授权操作，应该执行什么命令？
答：针对账户进行授权，需执行 GRANT 命令；取消授权则需执行 REVOKE 命令。
若只想查看 mybook 表单中的 name 字段，应该执行什么命令？
答：应执行 SELECT name FROM mybook 命令。
要想把 linuxprobe 数据库中的内容导出为一个文件（保存到 root 管理员的家目录中），应该执 行什么命令？
答：应执行 mysqldump -u root -p linuxprobe > /root/linuxprobeDB.dump 命令
