MariaDB資料庫管理系統是MYSQL的一個分支，主要由開源社群在維護，采用GPL授權許可，mariad的目的是完全相容MYSQL,包括API和指令行，使之成為MYSQL的替代品。在存儲引擎方面使用XtraDB來代替MYSQL的InnoDB。
連接配接池：認證、線程重用、連接配接數限制、記憶體檢查、緩存
SQL接口：DDL, DML, 關系型資料庫的基本抽象
parser: 查詢轉換、對象權限檢查
優化器：通路路徑，性能相關的統計資料
caches和buffers：與存儲引擎自身相關的I/O性能提升工具
存儲引擎：MyISAM、InnoDB(變種：XtraDB)、Memory、Merge、Federated、
CSV、Archive、Blackholl、Aria、SphinxSE、TokuDB
MariaDB基礎
1.資料庫操作
建立資料庫
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
删除資料庫
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
修改資料庫
ALTER {DATABASE | SCHEMA} [IF EXISTS] db_name
檢視資料庫
2.表操作
建立表
CREATE TABLE [IF NOT EXISTS] tb_name
(col1_def,col2_def,PRIMARY KEY(col_name,...),UNIQUE (col1,...),INDEX (col1,...))
[table_option]
table_option:
ENGINE [=] engine_name
COMMENT [=]
'string'
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
删除表
DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...
修改表
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
[alter_specification [, alter_specification] ...]
alter_specification:
#插入新字段
ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ]
#删除字段
DROP [COLUMN] col_name
#修改字段屬性
ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
#修改字段名
CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
#修改字段類型及屬性等
MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
修改表名
ALTER TABLE tb_name RENAME TO new_tb_name;
RENAME TABLE old_name TO new_name;
#指定排序字段
ORDER BY col_name [, col_name] ...
#轉換字元集及排序規則
CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
檢視表的索引
SHOW {INDEX | INDEXES | KEYS}
{FROM | IN} tbl_name
[{FROM | IN} db_name]
[WHERE expr]
使用者及權限管理：
使用者賬号：'username'@'host'
host可使用IP、網絡位址、使用通配符(_和%)；來源主機
MariaDB [(none)]> CREATE USER 'lys'@'192.168.0.0' IDENTIFIED BY '123';
建立使用者賬号，并賦予密碼；
CREATE USER 'username'@'host' IDENTIFIED BY [PASSWORD] 'password'；
删除使用者：
DROP USER 'username'@'host'；
授權：
GRANT priv1,... ON db_name.tbl_name TO 'username'@'host' [IDENTIFIED BY [PASSWORD] 'password'];
MariaDB [(none)]> GRANT ALL ON mysql.* TO 'lys'@'192.168.0.0';
如果使用者事先不存在，則建立此賬号并授權；
ALL [PRIVILEGES]：所有權限
檢視使用者已經獲得的授權：
SHOW GRANTS [FOR user]
收回授權：REVOKE
REVOKE priv1,... ON db_name.tbl_name FROM 'username'@'host';
mariadb的安裝
mariadb的安裝主要有3種方式：源代碼，通用二進制格式，包管理器格式。
第一步，下載下傳mariadb安裝包，并将安裝包解壓到/usr/local目錄下。同時做一個連接配接，連接配接到mysql。
第二步，進入到mysql中
第三步，複制support-files中的my-large.cnf到/etc/mysql/my.cnf。
以上隻顯示部配置設定置檔案。在啟動mariadb前，必須先初始化資料庫。
[root@lys mysql]# scripts/mysql_install_db 資料字典
[root@lys mysql]# scripts/mysql_install_db --help 檢視安裝參數
Usage: scripts/mysql_install_db [OPTIONS]
--basedir=path The path to the MariaDB installation directory.
--builddir=path If using --srcdir with out-of-directory builds, you
will need to set this to the location of the build
directory where built files reside.
--cross-bootstrap For internal use. Used when building the MariaDB system
tables on a different host than the target.
--datadir=path The path to the MariaDB data directory.
--defaults-extra-file=name
Read this file after the global files are read.
--defaults-file=name Only read default options from the given file name.
--force Causes mysql_install_db to run even if DNS does not
work. In that case, grant table entries that normally
use hostnames will use IP addresses.
--help Display this help and exit.
--ldata=path The path to the MariaDB data directory. Same as
--datadir.
--no-defaults Don't read default options from any option file.
--defaults-file=path Read only this configuration file.
--rpm For internal use. This option is used by RPM files
during the MariaDB installation process.
--skip-name-resolve Use IP addresses rather than hostnames when creating
grant table entries. This option can be useful if
your DNS does not work.
--srcdir=path The path to the MariaDB source directory. This option
uses the compiled binaries and support files within the
source tree, useful for if you don't want to install
MariaDB yet and just want to create the system tables.
--user=user_name The login username to use for running mysqld. Files
and directories created by mysqld will be owned by this
user. You must be root to use this option. By default
mysqld runs using your current login name and files and
directories that it creates will be owned by you.
All other options are passed to the mysqld program
[root@lys mysql]# scripts/mysql_install_db --user=mysql --datadir=/data/mydata
以上參數--user是以什麼使用者運作，--datadir指定資料庫的工作目錄.
如果在/data/mydata出現如上内容說明資料庫初始化成功。接下來就開始編輯資料庫的配置檔案，在配置前 先進行備份工作。
cp mysql.server /etc/rc.d/init.d/mysqld 複制資料庫的啟動腳本
chmod +x /etc/rc.d/init.d/mysqld 給啟動腳本執行的權限
chkconfig --add mysqld 添加服務
chkconfig mysqld on 服務自啟動
[root@lys support-files]# service mysqld start
Starting MySQL.... [ OK ]
mysql監聽在3306端口。
[root@lys mysql]# vi /etc/profile.d/mysql.sh
[root@lys mysql]# cat /etc/profile.d/mysql.sh