RHEL7: MariaDB的简单安装与使用

安装

[root@s1 ~]# yum group install mariadb mariadb-client -y

[root@s1 ~]# systemctl enable mariadb.service

[root@s1 ~]# systemctl start mariadb.service

[root@s1 ~]# mysql_secure_installation
Set root password? [Y/n] Y
New password: 
Re-enter new password: 
(按默认配置一路回车下去就行)

[root@s1 ~]# firewall-cmd --permanent --add-service=mysql
[root@s1 ~]# firewall-cmd --reload 

[root@s1 ~]# ss -tulnp | grep mysql
tcp    LISTEN     0      50                     *:3306                  *:*      users:(("mysqld",10844,13))

在/etc/my.cnf [mysqld]下添加
skip-networking=1
可以使mariadb仅在本机使用；

---

登入数据库

[root@s1 ~]# mysql --user=root --password=redhat
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

---

创建数据库

MariaDB [(none)]> CREATE DATABASE gis;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> USE gis;
Database changed
MariaDB [gis]> SHOW TABLES;
Empty set (0.00 sec)

---

创建数据表

MariaDB [gis]> CREATE TABLE River(
    -> NAME varchar(30),
    -> Origin varchar(30),
    -> Length int(30),
    -> Shape LineString );
Query OK, 0 rows affected (0.09 sec)

MariaDB [gis]> DESCRIBE River;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| NAME   | varchar(30) | YES  |     | NULL    |       |
| Origin | varchar(30) | YES  |     | NULL    |       |
| Length | int(30)     | YES  |     | NULL    |       |
| Shape  | linestring  | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)


MariaDB [gis]> INSERT INTO River(NAME,Origin,Length) VALUES('River1','Place A',20);
Query OK, 1 row affected (0.02 sec)

MariaDB [gis]> INSERT INTO River(NAME,Origin,Length) VALUES('River2','Place B',25);
Query OK, 1 row affected (0.03 sec)

MariaDB [gis]> INSERT INTO River(NAME,Origin,Length) VALUES('River3','Place C',28);
Query OK, 1 row affected (0.01 sec)

---

筛选条目

MariaDB [gis]> SELECT Origin FROM River WHERE Length>20;
+---------+
| Origin  |
+---------+
| Place B |
| Place C |
+---------+
2 rows in set (0.00 sec)

MariaDB [gis]> CREATE TABLE City( NAME varchar(30), river varchar(30), country varchar(30) );
Query OK, 0 rows affected (0.09 sec)

---

从两个表中查询

MariaDB [gis]> SELECT Ri.Name, Ri.Origin, Ci.NAME
    -> FROM City Ci, River Ri
    -> WHERE Ci.river = Ri.NAME
    -> AND Ri.length > 20 ;
+--------+---------+-----------+
| Name   | Origin  | NAME      |
+--------+---------+-----------+
| River2 | Place B | Shanghai  |
| River3 | Place C | Guangzhou |
+--------+---------+-----------+
2 rows in set (0.00 sec)

---

删除条目

MariaDB [gis]> SELECT * FROM River;
+--------+---------+--------+-------+
| NAME   | Origin  | Length | Shape |
+--------+---------+--------+-------+
| River1 | Place A |     20 | NULL  |
| River2 | Place B |     25 | NULL  |
| River3 | Place C |     28 | NULL  |
+--------+---------+--------+-------+
3 rows in set (0.00 sec)

MariaDB [gis]> DELETE FROM River WHERE NAME = 'River1' ;
Query OK, 1 row affected (0.02 sec)

MariaDB [gis]> SELECT * FROM River;
+--------+---------+--------+-------+
| NAME   | Origin  | Length | Shape |
+--------+---------+--------+-------+
| River2 | Place B |     25 | NULL  |
| River3 | Place C |     28 | NULL  |
+--------+---------+--------+-------+
2 rows in set (0.00 sec)

---

修改条目

MariaDB [gis]> UPDATE River SET Length=21 WHERE NAME='River1';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [gis]> SELECT * FROM River;
+--------+---------+--------+-------+
| NAME   | Origin  | Length | Shape |
+--------+---------+--------+-------+
| River2 | Place B |     25 | NULL  |
| River3 | Place C |     28 | NULL  |
| River1 | Place A |     21 | NULL  |
+--------+---------+--------+-------+
3 rows in set (0.00 sec)

---

赋予用户权限

MariaDB [gis]> CREATE USER feichashao@localhost IDENTIFIED BY 'redhat';
Query OK, 0 rows affected (0.00 sec)

MariaDB [gis]> GRANT SELECT, UPDATE, DELETE, INSERT on gis.* TO feichashao@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [gis]> SHOW GRANTS FOR feichashao@localhost;
+-------------------------------------------------------------------------------------------------------------------+
| Grants for feichashao@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'feichashao'@'localhost' IDENTIFIED BY PASSWORD '*84BB5DF4823DA319BBF86C99624479A198E6EEE9' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `gis`.* TO 'feichashao'@'localhost'                                       |
+-------------------------------------------------------------------------------------------------------------------+

[root@s1 ~]# mysql --user=feichashao --password=redhat
MariaDB [(none)]> use gis;
MariaDB [gis]> SHOW TABLES;
+---------------+
| Tables_in_gis |
+---------------+
| City          |
| River         |
+---------------+
2 rows in set (0.00 sec)

MariaDB [gis]> SELECT * FROM City;
+-----------+--------+---------+
| NAME      | river  | country |
+-----------+--------+---------+
| Beijng    | River1 | China   |
| Shanghai  | River2 | China   |
| Guangzhou | River3 | China   |
+-----------+--------+---------+
3 rows in set (0.00 sec)

---

备份数据库

[root@s1 ~]# mysqldump --user=root --password=redhat gis > /tmp/dbbackup.dump

MariaDB [(none)]> DROP DATABASE gis;

---

恢复数据库

先建立名为gis的数据库，然后执行：

[root@s1 ~]# mysql --user=root --password=redhat gis < /tmp/dbbackup.dump

MariaDB [gis]> SHOW TABLES;
+---------------+
| Tables_in_gis |
+---------------+
| City          |
| River         |
+---------------+
2 rows in set (0.00 sec)

MariaDB [gis]> SELECT * FROM City;
+-----------+--------+---------+
| NAME      | river  | country |
+-----------+--------+---------+
| Beijng    | River1 | China   |
| Shanghai  | River2 | China   |
| Guangzhou | River3 | China   |
+-----------+--------+---------+
3 rows in set (0.00 sec)