[root@proxy nginx-1.12.2]# yum -y install mariadb mariadb-server mariadb-devel
[root@proxy nginx-1.12.2]# systemctl start mariadb
[root@proxy nginx-1.12.2]# systemctl status mariadb
初始化安全脚本
安装完MariaDB或MySQL后,默认root没有密码,并且提供了一个任何都可以操作的test测试数据库。有一个名称为mysql_secure_installation的脚本,该脚本可以帮助我们
为 root设置密码,并禁止root从远程其他主机登陆数据库,并删除测试性数据库test.
[root@proxy ~]# mysql_secure_installation
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n]
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
[root@proxy ~]# mysql -uroot -pmysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> \q
[root@proxy ~]# mysql -uroot -pmysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000, 2017, 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 |
+--------------------+
3 rows in set (0.00 sec)
MariaDB [(none)]>
密码安全
手动修改MariaDB或MySQL数据库密码的方法:
[root@proxy ~]# mysqladmin -uroot -pmysql password 'redhat'
[root@proxy ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> \q
Bye
修改密码成功,而且密码在数据库是加密的,有什么问题吗?问题是你的密码被明文记录了。
[root@proxy ~]# cat .mysql_history
show databases;
\q
另外数据库还有一个binlog日志里也有密码(5.6版本后修复了)。
管理好自己的历史,不使用用明文登录,选择合适的版本5.6以后的版本,
日志,行为审计,使用防火墙从TCP层设置ACL(禁止外网接触数据库)。
数据备份与还原
首先,备份数据库(注意用户名为root,密码为redhat)
//备份数据库中的某个数据表
[root@proxy ~]# mysqldump -uroot -predhat school t_student > ./t_student.sql
[root@proxy ~]# ls *.sql
t_student.sql
//备份所有的数据表
[root@proxy ~]# mysqldump -uroot -predhat school > school.sql
[root@proxy ~]# ls *.sql
school.sql t_student.sql
MariaDB [(none)]> create database school;
[root@proxy ~]# mysql -uroot -predhat school < school.sql
//数据安全
在服务器上192.168.4.1,创建一个数据账户
MariaDB [(none)]> grant all on *.* to tom@'%' identified by '123';
//使用tcpdump抓包抓取源或目标端口是3306数据包,保存到log文件中
[root@proxy ~]# yum provides tcpdump
[root@proxy ~]# yum -y install tcpdump-*
[root@proxy ~]# tcpdump -w log -i any src or dst port 3306
tcpdump: listening on any, link-type LINUX_SLL (Linux cooked), capture size 262144 bytes
^C0 packets captured
0 packets received by filter
0 packets dropped by kernel
[root@proxy ~]#
[root@proxy ~]#
[root@proxy ~]# ls
anaconda-ks.cfg nginx-1.12.2 t_student.sql 视频 下载
initial-setup-ks.cfg nginx-1.12.2.tar.gz 公共 图片 音乐
log school.sql 模板 文档 桌面
[root@proxy ~]# cat log
[root@proxy ~]# mysql
mysql mysql_fix_extensions
mysqlaccess mysqlhotcopy
mysqladmin mysqlimport
mysqlbinlog mysql_install_db
mysqlbug mysql_plugin
mysqlcheck mysql_secure_installation
mysql_config mysql_setpermission
mysql_convert_table_format mysqlshow
mysqld_multi mysqlslap
mysqld_safe mysqltest
mysqld_safe_helper mysql_tzinfo_to_sql
mysqldump mysql_upgrade
mysqldumpslow mysql_waitpid
mysql_find_rows mysql_zap
[root@proxy ~]# mysqlbinlog log
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
ERROR: File is not a binary log file.
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
客户端远程登录数据库服务器192.168.4.1
[root@rootroom9pc01 ~]# mysql -utom -p123 -h 192.168.4.1 -P 3306
MariaDB [(none)]> select * from school.t_student ;
[root@proxy ~]# tcpdump -A -r log
//使用tcpdump查看之前的抓取的数据包,很多数据库的数据都是明文显示出来
可以使用SSH远程连接服务器后,再从本地登录数据库(避免在网络传输数据,因为网络环境中不知道有没有抓包者)。
或者也可以使用SSL对MySQL服务器进行加密,类似于HTTP+SSL一样,MySQL也支持SSL加密(确保网络中传输的数据是被加密的)。