18.1 数据库管理系统
我们的生活中无时无刻都在接触到数据,而数据库便是通过指定的组织结构将这数据存储的仓库,并且随着互联网和信息技术的发展,数据库也已经从最初只能存储简单表格发展到了存储海量数据的大型分布式模式。
出现问题?大胆提问!
因读者们硬件不同或操作错误都可能导致实验配置出错,请耐心再仔细看看操作步骤吧,不要气馁~
Linux技术交流请加A群:560843(满),B群:340829(推荐),C群:463590(推荐),点此查看全国群。
*本群特色:通过口令验证确保每一个群员都是《Linux就该这么学》的读者,答疑更有针对性,不定期免费领取定制礼品。
18.2 初始化mariaDB服务程序
MariaDB相对于MYSQL来讲确实在功能上有很多扩展特性,比如微秒的支持、线程池、子查询优化、组提交、进度报告等。
安装mariaDB服务程序:
[root@linuxprobe ~]# yum install mariadb mariadb-server Loaded plugins: langpacks, product-id, subscription-manager ………………省略部分安装过程……………… Installing: mariadb x86_64 1:5.5.35-3.el7 rhel7 8.9 M mariadb-server x86_64 1:5.5.35-3.el7 rhel7 11 M Installing for dependencie perl-Compress-Raw-Bzip2 x86_64 2.061-3.el7 rhel7 32 k perl-Compress-Raw-Zlib x86_64 1:2.061-4.el7 rhel7 57 k perl-DBD-MySQL x86_64 4.023-5.el7 rhel7 140 k perl-DBI x86_64 1.627-4.el7 rhel7 802 k perl-Data-Dumper x86_64 2.145-3.el7 rhel7 47 k perl-IO-Compress noarch 2.061-2.el7 rhel7 260 k perl-Net-Daemon noarch 0.48-5.el7 rhel7 51 k perl-PlRPC noarch 0.2020-14.el7 rhel7 36 k ………………省略部分安装过程……………… Complete!
启动mariadb服务程序并添加到开机启动项中:
[root @linuxprobe ~ ] # systemctl enable mariadb
ln -s '/usr/lib/systemd/system/mariadb.service' '/etc/systemd/system/multi-user.target.wants/mariadb.service'
为了保证数据库的安全性,一定要进行初始化工作:
第1步:设定root用户密码。
第2步:删除匿名帐号。
第3步:禁止root用户从远程登陆。
第4步:删除test数据库并取消对其的访问权限。
第5步:刷新授权表,让初始化后的设定立即生效。
初始化数据库服务程序:
[root@linuxprobe ~]# mysql_secure_installation /usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found 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: 输入要为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(刷新授权表,让初始化后的设定立即生效) ... 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@linuxprobe ~]# firewall-cmd --permanent --add-service=mysql success [root@linuxprobe ~]# firewall-cmd --reload success
使用root用户登陆到数据库中:
[root@linuxprobe ~]# mysql -u root -p Enter password: 此处输入root用户在数据库中的密码。 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 5 Server version: 5.5.35-MariaDB MariaDB Server Copyright (c) 2000, 2013, Oracle, Monty Program 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.01 sec)
修改当前用户在数据库中的密码(示例中的密码为redhat):
MariaDB [(none)]> set password = password('redhat'); Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> exit Bye
使用旧的密码将不能再登陆到数据库:
[root@linuxprobe ~]# mysql -u root -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
18.3 管理数据库与表单数据
关系型数据库(DataBase)是由一个或多个数据表单(Table)组成的,数据表单则一般会保存着多个数据记录(Record)。
18.3.1 创建用户并授权
创建一个新的数据库用户:
创建数据库用户的命令:CREATE USER 用户名@主机名 IDENTIFIED BY '密码';
MariaDB [(none)]> create user luke@localhost IDENTIFIED BY 'linuxprobe'; Query OK, 0 rows affected (0.00 sec)
进入到mysql数据库中:
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
查看新创建的用户、主机、姓名与密码信息:
MariaDB [mysql]> select host,user,password from user where user="luke"; +-----------+------+-------------------------------------------+ | host | user | password | +-----------+------+-------------------------------------------+ | localhost | luke | *55D9962586BE75F4B7D421E6655973DB07D6869F | +-----------+------+-------------------------------------------+ 1 row in set (0.00 sec)
退出数据库后使用新用户登陆:
MariaDB [mysql]> exit Bye [root@linuxprobe ~]# mysql -u luke -p Enter password: 此处输入luke用户的数据库密码 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 6 Server version: 5.5.35-MariaDB MariaDB Server Copyright (c) 2000, 2013, Oracle, Monty Program 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.03 sec)
数据库GRANT命令的授权操作常用方案:
命令 | 作用 |
GRANT 权限 ON 数据库.表单名称 TO 用户名@主机名 | 对某个特定数据库中的特定表单给予授权。 |
GRANT 权限 ON 数据库.* TO 用户名@主机名 | 对某个特定数据库中的所有表单给予授权。 |
GRANT 权限 ON *.* TO 用户名@主机名 | 对所有数据库及所有表单给予授权。 |
GRANT 权限1,权限2 ON 数据库.* TO 用户名@主机名 | 对某个数据库中的所有表单给予多个授权。 |
GRANT ALL PRIVILEGES ON *.* TO 用户名@主机名 | 对所有数据库及所有表单给予全部授权,(谨慎操作)。 |
切换回root用户登陆数据库并进入到mysql数据库中:
[root@linuxprobe ~]# mysql -u root -p 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
给予luke用户对user表单的查询、更新、删除、插入权限:
MariaDB [mysql]> GRANT SELECT,UPDATE,DELETE,INSERT on mysql.user to luke@localhost; Query OK, 0 rows affected (0.00 sec)
查看luke用户当前的授权:
+-------------------------------------------------------------------------------------------------------------+
| Grants for luke @localhost |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *. * TO 'luke' @ 'localhost' IDENTIFIED BY PASSWORD '*55D9962586BE75F4B7D421E6655973DB07D6869F' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `mysql `. `user ` TO 'luke' @ 'localhost' |
+-------------------------------------------------------------------------------------------------------------+
2 rows in set ( 0.00 sec )
再次切换到luke用户后查看可用的数据库:
[root@linuxprobe ~]# mysql -u luke -p MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | +--------------------+ 2 rows in set (0.01 sec)
进入到mysql数据库中看到user表单了:
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]> show tables; +-----------------+ | Tables_in_mysql | +-----------------+ | user | +-----------------+ 1 row in set (0.01 sec)
切换回root用户后取消刚刚全部的授权:
MariaDB [(none)]> revoke SELECT,UPDATE,DELETE,INSERT on mysql.user from luke@localhost; Query OK, 0 rows affected (0.00 sec)
再次查看luke用户的授权:
+-------------------------------------------------------------------------------------------------------------+
| Grants for luke @localhost |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *. * TO 'luke' @ 'localhost' IDENTIFIED BY PASSWORD '*55D9962586BE75F4B7D421E6655973DB07D6869F' |
+-------------------------------------------------------------------------------------------------------------+
1 row in set ( 0.00 sec )