Linux基础学习-MariaDB数据库管理系统

数据库管理系统

数据库是指按照某些特定结构来存储数据资料的数据仓库,数据库管理系统是一种能够对数据库中存放的数据进行建立、修改、删除、查找、维护等操作的软件程序.

初始化MariaDB服务

[root@mail ~]# yum install mariadb mariadb-server -y

[root@mail mysql]# systemctl restart mariadb
[root@mail mysql]# systemctl enable mariadb
ln -s '/usr/lib/systemd/system/mariadb.service' '/etc/systemd/system/multi-user.target.wants/mariadb.service'


[root@mail ~]# mysql
mysql                       mysqldump                   mysql_setpermission
mysqlaccess                 mysqldumpslow               mysqlshow
mysqladmin                  mysql_find_rows             mysqlslap
mysqlbinlog                 mysql_fix_extensions        mysqltest
mysqlbug                    mysqlhotcopy                mysql_tzinfo_to_sql
mysqlcheck                  mysqlimport                 mysql_upgrade
mysql_convert_table_format  mysql_install_db            mysql_waitpid
mysqld_multi                mysql_plugin                mysql_zap
mysqld_safe                 mysql_secure_installation   
[root@mail mysql]# mysql_secure_installation 
/usr/bin/mysql_secure_installation:行379: find_mysql_client: 未找到命令

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(设置root管理员密码)
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(删除匿名用户)

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管理员从远程登录)

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@mail mysql]# firewall-cmd --permanent --add-service=mysql
[root@mail mysql]# firewall-cmd --reload

登录MariaDB数据库,其中-u参数用来指定以root管理员的身份登录,-p参数用来验证该用户在数据库中的密码值.

[root@mail mysql]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
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 |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.02 sec)

//重置root管理员密码
MariaDB [(none)]> SET password = PASSWORD('redhat');
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> exit
Bye

管理账户以及授权

命令	作用
GRANT 权限 ON 数据库.表单名称 TO 账户名@主机名	对某个特定数据库中的特定表单给予授权
GRANT 权限 ON 数据库.* TO 账户名@主机名	对某个特定数据库中的所有表单给予授权
GRANT 权限 ON . TO 账户名@主机名	对所有数据库及所有表单给予授权
GRANT 权限1,权限2 ON 数据库.* TO 账户名@主机名	对某个数据库中的所有表单给予多个授权
GRANT ALL PRIVILEGES ON . TO 账户名@主机名	对所有数据库及所有表单给予全部授权

MariaDB [(none)]> create user hcie@localhost identified by 'hcie';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant select,update,delete,insert on mysql.* to hcie;
Query OK, 0 rows affected (0.03 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]> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.00 sec)

MariaDB [(none)]> show grants for hcie@localhost;
+-------------------------------------------------------------------------------------------------------------+
| Grants for hcie@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'hcie'@'localhost' IDENTIFIED BY PASSWORD '*D6CDD5D8461B882D121CAC3EE6A028367E198649' |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> show grants for hcie;
+-----------------------------------------------------------------+
| Grants for hcie@%                                               |
+-----------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'hcie'@'%'                                |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `mysql`.* TO 'hcie'@'%' |
+-----------------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> revoke select,update,delete,insert on mysql.* from hcie;
Query OK, 0 rows affected (0.00 sec)

命令	作用
CREATE DATABASE 数据库名称	创建新的数据库
DESCRIBE 表单名称	描述表单
UPDATE 表单名称 SET attribute=新值 WHERE attribute>原始值	更新表单中的数据
USE 数据库名称	指定使用的数据库
SHOW databases	显示当前已有的数据库
SHOW tables	显示当前数据库中的表单
SELECT * FROM 表单名称	从表单中选中某个记录值
DELETE FROM 表单名 WHERE attribute=值	从表单中删除某个记录值

创建数据库与表单

MariaDB [(none)]> create database typecho;
Query OK, 1 row affected (0.02 sec)

MariaDB [(none)]> use typecho;
Database changed
MariaDB [typecho]> create table mybook (name char(15),price int,pages int);
Query OK, 0 rows affected (0.14 sec)

MariaDB [typecho]> show tables;
+-------------------+
| Tables_in_typecho |
+-------------------+
| mybook            |
+-------------------+
1 row in set (0.00 sec)

MariaDB [typecho]> 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.05 sec)

管理表单及数据

参数	作用
=	相等
<>或!=	不相等

| 大于
< | 小于
= | 大于或等于
<= | 小于或等于
BETWEEN | 在某个范围
LIKE | 搜索一个例子
IN | 在列中搜索多个值

MariaDB [typecho]> insert into mybook(name,price,pages) values('linux','60','618');
Query OK, 1 row affected (0.04 sec)

MariaDB [typecho]> select * from mybook;
+-------+-------+-------+
| name  | price | pages |
+-------+-------+-------+
| linux |    60 |   618 |
+-------+-------+-------+
1 row in set (0.00 sec)

MariaDB [typecho]> update mybook set price=65;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [typecho]> select name,price from mybook;
+-------+-------+
| name  | price |
+-------+-------+
| linux |    65 |
+-------+-------+
1 row in set (0.00 sec)

MariaDB [typecho]> delete from mybook;
Query OK, 1 row affected (0.00 sec)

MariaDB [typecho]> select * from mybook;
Empty set (0.00 sec)

MariaDB [typecho]> insert into mybook(name,price,pages) values('linux1','30','518');
Query OK, 1 row affected (0.01 sec)

MariaDB [typecho]> insert into mybook(name,price,pages) values('linux2','50','518');
Query OK, 1 row affected (0.01 sec)

MariaDB [typecho]> insert into mybook(name,price,pages) values('linux3','80','518');
Query OK, 1 row affected (0.01 sec)

MariaDB [typecho]> insert into mybook(name,price,pages) values('linux4','100','518');
Query OK, 1 row affected (0.00 sec)

MariaDB [typecho]> select * from mybook where price > 75;
+--------+-------+-------+
| name   | price | pages |
+--------+-------+-------+
| linux3 |    80 |   518 |
| linux4 |   100 |   518 |
+--------+-------+-------+
2 rows in set (0.00 sec)

MariaDB [typecho]> select * from mybook where price != 80;
+--------+-------+-------+
| name   | price | pages |
+--------+-------+-------+
| linux1 |    30 |   518 |
| linux2 |    50 |   518 |
| linux4 |   100 |   518 |
+--------+-------+-------+
3 rows in set (0.00 sec)

数据库的备份及恢复

[root@mail mysql]# mysqldump -u root -p typecho > /root/typechoDB.dump
Enter password: 

MariaDB [(none)]> drop database typecho;
Query OK, 1 row affected (0.02 sec)

MariaDB [(none)]> create database typecho;
Query OK, 1 row affected (0.00 sec)

[root@mail mysql]# mysql -u root -p typecho < /root/typechoDB.dump 
Enter password: 
[root@mail mysql]# mysql -u root -p

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| typecho            |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> use typecho;
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 [typecho]> select name,pages from mybook;
+--------+-------+
| name   | pages |
+--------+-------+
| linux1 |   518 |
| linux2 |   518 |
| linux3 |   518 |
| linux4 |   518 |
+--------+-------+
4 rows in set (0.00 sec)

MariaDB [typecho]> show tables;
+-------------------+
| Tables_in_typecho |
+-------------------+
| mybook            |
+-------------------+
1 row in set (0.00 sec)

MariaDB [typecho]> 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.00 sec)