mariadb

MariaDB資料庫管理系統是MySQL的一個分支

1:資料庫的安裝啟動

yum search mariadb
yum install -y mariadb-server.x86_64
systemctl start mariadb  #開啟
systemctl enable mariadb      

2：資料庫的初始化

3：資料庫的使用

《1》查詢

MariaDB [(none)]> show databases;       ##檢視資料庫中有的庫
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linux              |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)      

MariaDB [(none)]> use linux;             ##切換到linux庫中
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 [linux]> show tables;            ##檢視linux庫中含有的表格
+-----------------+
| Tables_in_linux |
+-----------------+
| information     |
+-----------------+
1 row in set (0.00 sec)      

MariaDB [linux]> select * from information; ##檢視information表格的所有資訊
+----------+----------+------+
| username | password | age  |
+----------+----------+------+
| zpy      | 123      | 19   |
+----------+----------+------+
1 row in set (0.00 sec)      

MariaDB [linux]> desc message;           ##檢視表格結構
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(50) | NO   |     | NULL    |       |
| sex      | varchar(10) | YES  |     | NULL    |       |
| class    | varchar(50) | YES  |     | NULL    |       |
| age      | varchar(4)  | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)      

《2》建立

MariaDB [(none)]> create database haha;     ##建立haha庫，從show databases;可以看到
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| haha               |
| linux              |
| mysql              |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)      

MariaDB [(none)]> use haha;
Database changed
MariaDB [haha]> create table xihao (      ##建立表格名為xihao
    -> username varchar(50),              ##建立的表格表頭包含的資訊有username 并且位元組數在50個範圍内
    -> favoritecolor varchar(50),
    -> favoritething varchar(30) );
Query OK, 0 rows affected (0.67 sec)      

《3》 添加表格資訊

MariaDB [haha]> insert into xihao values ('qq','yellow','book');##給xihao表中添加資訊
Query OK, 1 row affected (0.28 sec)
MariaDB [haha]> select * from xihao;                  檢視添加的資訊
+----------+---------------+---------------+
| username | favoritecolor | favoritething |
+----------+---------------+---------------+
| qq       | yellow        | book          |
+----------+---------------+---------------+
1 row in set (0.00 sec)      

《4》 修改

MariaDB [haha]> alter table xihao rename message;    ##修改表格名稱
Query OK, 0 rows affected (0.30 sec)
MariaDB [haha]> alter table message add favoritefood varchar(50);      ##給message表格中添加favoratefood類
Query OK, 1 row affected (1.63 sec)                
Records: 1  Duplicates: 0  Warnings: 0
MariaDB [haha]> select * from message;
+----------+---------------+---------------+--------------+
| username | favoritecolor | favoritething | favoritefood |
+----------+---------------+---------------+--------------+
| qq       | yellow        | book          | NULL         |
+----------+---------------+---------------+--------------+
1 row in set (0.00 sec)      

MariaDB [haha]> alter table message add sex varchar(10) after username;        ##指定位置添加
Query OK, 1 row affected (0.39 sec)                
Records: 1  Duplicates: 0  Warnings: 0
MariaDB [haha]> select * from message;
+----------+------+---------------+---------------+--------------+
| username | sex  | favoritecolor | favoritething | favoritefood |
+----------+------+---------------+---------------+--------------+
| qq       | NULL | yellow        | book          | NULL         |
+----------+------+---------------+---------------+--------------+
1 row in set (0.00 sec)      

MariaDB [haha]> alter table message drop favoritefood;           ##删除表格中favoritefood這一行
Query OK, 1 row affected (0.20 sec)                
Records: 1  Duplicates: 0  Warnings: 0
MariaDB [haha]> select * from message;
+----------+------+---------------+---------------+
| username | sex  | favoritecolor | favoritething |
+----------+------+---------------+---------------+
| qq       | NULL | yellow        | book          |
+----------+------+---------------+---------------+
1 row in set (0.00 sec)      

MariaDB [haha]> update message set sex='man';             ##更新表格中所有sex類資訊為man
Query OK, 1 row affected (1.29 sec)
Rows matched: 2  Changed: 1  Warnings: 0
MariaDB [haha]> select * from message;
+----------+------+---------------+---------------+
| username | sex  | favoritecolor | favoritething |
+----------+------+---------------+---------------+
| qq       | man  | yellow        | book          |
| aaa      | man  | ss            | dd            |
+----------+------+---------------+---------------+
2 rows in set (0.00 sec)      

MariaDB [haha]> update message set sex='woman' where username='qq';  ##指定使用者的資訊更改
Query OK, 1 row affected (0.28 sec)
Rows matched: 1  Changed: 1  Warnings: 0
MariaDB [haha]> select * from message;
+----------+-------+---------------+---------------+
| username | sex   | favoritecolor | favoritething |
+----------+-------+---------------+---------------+
| qq       | woman | yellow        | book          |
| aaa      | man   | ss            | dd            |
+----------+-------+---------------+---------------+
2 rows in set (0.00 sec)      

《5》删除

drop database haha

drop table message

delete table message where username='qq' and sex='woman'

《6》資料庫備份

mysqldump -uroot -phahaha linux > /mnt/linux.aql  ##将linux庫備份到/mnt/linux.aql中
MariaDB [(none)]> drop database linux;    ##删掉linuxk庫
Query OK, 1 row affected (0.29 sec)
mysql -uroot -phahaha -e 'create database linux;'   ##建立新的linux庫
mysql -uroot -phahaha linux < /mnt/linux.aql  ##将備份檔案倒入建立的linux庫      

systemctl stop mariadb  ##停掉mariadb
mysqld_safe --skip-grant-tables &  ##mysql進入安全模式
[root@localhost ~]# mysql           ##進入mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
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)]> use mysql          #切換到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]> select * from user; ##檢視user表
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| Host      | User | Password                                  | Select_priv | Insert_priv |###注：此處的 | Host      | User | Password 都是表頭Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string |
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Y           | Y           | 注：此處的  | localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9都是上面表頭對應的資訊  | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       |
| 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       |
| ::1       | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       |
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
3 rows in set (0.00 sec)
MariaDB [mysql]> update user set Password=password('hahaha') where User='root';  ##更新密碼，使得密碼為hahaha并且是加密字元
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0
MariaDB [mysql]> Bye
[root@localhost ~]# ps ax |grep mysql            ##過濾mysql的程序
 6156 pts/2    S      0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables
 6299 pts/2    Sl     0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock
 6340 pts/2    R+     0:00 grep --color=auto mysql
[root@localhost ~]# kill -9 6156 6299        ##殺掉程序
[root@localhost ~]# ps ax |grep mysql   
 6342 pts/2    R+     0:00 grep --color=auto mysql
[1]+  Killed                  mysqld_safe --skip-grant-tables
[root@localhost ~]# systemctl start mariadb  ##啟動正常登陸      

MariaDB [(none)]> create user zpy@localhost identified by '123'; ##建立使用者
MariaDB [(none)]> show grants for zpy@localhost;  ##檢視使用者資訊；
MariaDB [(none)]> grant select on linux.* to zpy@localhost; ##給zpy使用者可以檢視linux庫的權限。
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> grant delete on linux.* to zpy@localhost;  #給zpy使用者删除表格内容權限
MariaDB [(none)]> grant drop on linux.* to zpy@localhost;     ##給使用者zpydrop權限
Query OK, 0 rows affected (0.00 sec)               
MariaDB [(none)]> flush privileges;                         ##重載授權表
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> revoke delete on linux.* from zpy@localhost  ##撤銷使用者權限
MariaDB [(none)]> drop user zpy@localhost;               ##删除使用者zpy
Query OK, 0 rows affected (0.00 sec)