转自 叶泯希博客:https://yeminxi.github.io/posts/660d7bf6.html
前言
虚拟机需求:1台服务器(Server)192.168.1.10、1台客户端(Client1)192.168.1.100
部署数据库
任务情境描述
任务一
某公司准备开发一套员工管理系统,其中数据库使用到了MariaDB数据库,现需要系统管理员完成以下部署工作:在公司一台Linux服务器(192.168.1.10)上安装数据库服务器和客户端,方便开发人员开发测试使用,在另一台Linux主机(192.168.1.100)上安装客户端;
对数据库进行初始化设置。
任务二
该公司准备开发一套新员工管理系统,现需要系统管理员完成以下安全管理工作:
对数据库服务器进行必要的网络安全设置,只允许从公司内部网络(192.168.1.0/24)访问数据库服务器;
从安全的角度对数据库的配置文件进行必要的安全设置,修改数据库端口号为“8800”,并设置日志文件为/var/log/mariaDB/access.log
登录数据库,并新建一个名为newstaff的数据库;在数据库中创建一个数据表为stafftable;字段类型为staff int,username varchar(20),sex char(6),age
int;并将下表中的总公司部门员工信息插入到stafftable表中:
对数据库账户和权限进行必要的管理,为开发人员创建一个名为“adminDB”的账户,但只允许他对newstaff数据库进行必要的操作;
为有效保护员工数据,编写一个定时任务,让系统每天凌晨0:00点对newstaff数据库自动进行数据备份。
一、 服务器(Server)和客户端(Client1)安装数据库服务
1. 服务器(Server)
[root@server www]# yum -y install mariadb-server mysql //安装
[root@server www]# systemctl start mariadb.service //开启
[root@server www]# systemctl enable mariadb.service //自启
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
2. 客户端(Client1)
[root@webserver webserver]# yum -y install mysql //安装
二、在服务器(Server)创建数据库和表
[root@server www]# mysql -uroot -p //登录数据库 默认密码应该是root或空
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)]> create database newstaff; //创建数据库newstaff
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use newstaff; //进入数据库newstaff
Database changed
MariaDB [newstaff]> create table stafftable ( //按任务需求创建表
-> staffID int auto_increment, //自增
-> username varchar(20),
-> sex char(6),
-> age int,
-> primary key(staffID) //主键
-> )engine=innodb default charset=utf8; //表的引擎字符类型
Query OK, 0 rows affected (0.02 sec)
MariaDB [newstaff]> insert into stafftable(username,sex,age) values('Tom','male',25),('Mary','female',30);
Query OK, 2 rows affected (0.05 sec) //按要求插入数据
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [newstaff]> desc stafftable; //查看表结构
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| staffID | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(20) | YES | | NULL | |
| sex | char(6) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
MariaDB [newstaff]> select * from stafftable; //查询表内所有数据
+---------+----------+--------+------+
| staffID | username | sex | age |
+---------+----------+--------+------+
| 1 | Tom | male | 25 |
| 2 | Mary | female | 30 |
+---------+----------+--------+------+
2 rows in set (0.01 sec)
MariaDB [newstaff]> quit //退出
Bye
[root@server www]#
三、 服务器(Server)只允许192.168.1.0/24访问数据库
[root@server www]# iptables -A INPUT -p tcp -s 192.168.1.0/24 --dport 8800 -j ACCEPT
四、 修改服务器(Server)默认端口
vim /etc/my.cnf //插入以下
port=8800 //修改默认端口
max_user_connections=2 //最大用户数
log=/var/log/mariadb/access.log //修改日志默认路径
[mysqld_safe]
防火墙设置
允许mysql[root@server www]# firewall-cmd --permanent --zone=public --add-service=mysql
success
允许端口[root@server www]# firewall-cmd --permanent --zone=public --add-port=8800/tcp
success
允许端口[root@server www]# firewall-cmd --permanent --zone=public --add-port=8800/udp
success
重启防火墙[root@server www]# semanage port -l | grep mysql
mysqld_port_t tcp 8800, 1186, 3306, 63132-63164
mysqlmanagerd_port_t tcp 2273
[root@server www]# semanage port -a -t mysqld_port_t -p tcp 8800
[root@server www]# firewall-cmd --reload
success
[root@server www]# systemctl restart mariadb.service
五、在服务器(Server)上创建账户只允许他对newstaff数据库进行操作
创建用户
[root@server www]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 7
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 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]> create user 'adminDB'@'%' identified by 'root'; //设置用户名和密码root
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> quit
Bye
[root@server www]#
只允许对newstaff数据库进行操作
[root@server www]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
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)]> show grants for 'adminDB'@'%';
+----------------------------------------------------------------------------------------------------------------+
| Grants for adminDB@% |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'adminDB'@'%' IDENTIFIED BY PASSWORD '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' |
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> grant select,insert,update,delete,create,drop on newstaff.* to 'adminDB'@'%'; //只允许adminDB操作数据库newstaff
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> quit
Bye
[root@server www]#
六、编写备份程序(可略,老师好像不看)
[root@server www]# mysqldump -uroot -p newstaff > newstaff_backup.sql //备份newstaff数据库
Enter password:
[root@server www]# cd ..
[root@server var]# cd ..
[root@server /]# vim backup.sh //编写程序
#!/bin/bash
time=`date+"("%F")"%R`
$/usr/local/mysql/bin/mysqldump -uroot -proot newstaff|gzip > /root/staffbackup-$time.gz
[root@server /]# crontab -e //添加定时任务
00 00 * * * /backup.sh //0点0分 每天 每月 每时 脚本路径
crontab: installing new crontab
[root@server /]# systemcrl restart mariadb
七、 测试
老师一般说这几条命令,她叫你打什么就打什么吧
1. 客户端(Client1)
[root@webserver webserver]# mysql -uadminDB -p -P8800 -h 192.168.1.10 //带端口登录
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
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 newstaff;
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 [newstaff]> desc stafftable; //查看表结构
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| staffID | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(20) | YES | | NULL | |
| sex | char(6) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
MariaDB [newstaff]> select * from stafftable; //查看表内容
+---------+----------+--------+------+
| staffID | username | sex | age |
+---------+----------+--------+------+
| 1 | Tom | male | 25 |
| 2 | Mary | female | 30 |
+---------+----------+--------+------+
2 rows in set (0.00 sec)
MariaDB [newstaff]> quit
Bye
[root@webserver webserver]#
2. 服务器(Server)
[root@server /]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
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)]> show grants for 'adminDB'@'%';
+--------------------------------------------------------------------------------------------------------------------+
| Grants for adminDB@% |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'adminDB'@'%' IDENTIFIED BY PASSWORD '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `newstaff`.* TO 'adminDB'@'%' |
+--------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [(none)]> quit
Bye
[root@server /]#