CentOS7下mariadb日常管理

在CentOS7下，官方提供的mysql的rpm包就是mariadb，可查看mariadb包信息

[root@host ~]$rpm -qi mariadb    #  需要先安装该包
Name        : mariadb            # 名称
Epoch       : 1                  
Version     : 5.5.52             # 版本
Release     : 1.el7              # 发行版
Architecture: x86_64             # 支持安装的CPU架构
Install Date: Mon 24 Apr 2017 08:44:48 AM CST    #　安装时间
Group       : Applications/Databases
Size        : 50855896
License     : GPLv2 with exceptions and LGPLv2 and BSD　　　　　　　　　　　　　　　
Signature   : RSA/SHA256, Mon 21 Nov 2016 03:23:55 AM CST, Key ID 24c6a8a7f4a80eb5
Source RPM  : mariadb-5.5.52-1.el7.src.rpm
Build Date  : Tue 15 Nov 2016 09:20:59 AM CST
Build Host  : c1bm.rdu2.centos.org
Relocations : (not relocatable)
Packager    : CentOS BuildSystem <http://bugs.centos.org>
Vendor      : CentOS
URL         : http://mariadb.org
Summary     : A community developed branch of MySQL
Description :                                               # 描述
MariaDB is a community developed branch of MySQL.
MariaDB is a multi-user, multi-threaded SQL database server.
It is a client/server implementation consisting of a server daemon (mysqld)
and many different client programs and libraries. The base package
contains the standard MariaDB/MySQL client programs and generic MySQL files.

 

根据最后一项——Description描述，MariaDB属于MySQL的社区开发分支。

同时，MariaDB是一款支持多用户、多线程的SQL数据库服务器。

MariaDB采用C/S架构，mysqld作为服务器进程，还有许多的客服端程序和库。

 

客户端命令mysql

格式

mysql [OPTIONS] [database]

常用选项

-u, --user=username：用户名，默认为root；
-h, --host=hostname：远程主机（即mysql服务器）地址，默认为localhost; 客户端连接服务端，服务器会反解客户的IP为主机名，关闭此功能(skip_name_resolve=ON)；
-p, --password[=PASSWORD]：USERNAME所表示的用户的密码； 默认为空；
                
　　注意：mysql的用户账号由两部分组成：'USERNAME'@'HOST'; 其中HOST用于限制此用户可通过哪些远程主机连接当前的mysql服务；
                    HOST的表示方式，支持使用通配符：
                        %：匹配任意长度的任意字符；
                            172.16.%.%,  172.16.0.0/16
                        _：匹配任意单个字符；
                    
-P, --port=#：mysql服务器监听的端口；默认为3306/tcp；
-S, --socket=/PATH/TO/mysql.sock：套按字文件路径；                
-D, --database=DB_name：连接到服务器端之后，设定其处指明的数据库为默认数据库；
-e, --execute='SQL STATEMENT'：连接至服务器并让其执行此命令后直接返回；

注：在上面的选项中，选项和参数之间可以不使用空格分开。

示例登录mysql

[root@host ~]$mysql -uroot -p　　　　　　　　　　　　　　　＃账号root, 选项和参数可以不分开
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 54
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MariaDB [(none)]>                                    #　表示登录成功

查看客户端常见命令

MariaDB [(none)]> help

General information about MariaDB can be found at
http://mariadb.org

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (?) Synonym for `help'.                                                 #　查看帮助
clear     (c) Clear the current input statement.                                  #  清空当前输入的语句
connect   (
) Reconnect to the server. Optional arguments are db and host.        #  重新连接服务器
delimiter (d) Set statement delimiter.                                            #  设置语句分隔符（结束符），默认为分号';'
edit      (e) Edit command with $EDITOR.　　　　　　　　　　　　　　　　　　　　　　　　 #  编辑命令
ego       (G) Send command to mysql server, display result vertically.            #　发送命令至服务器，垂直显示结果
exit      (q) Exit mysql. Same as quit.　　　　　　　　　　　　　　　　　　　　　　　　　#　退出
go        (g) Send command to mysql server.                                       # 发送命令至服务器
help      (h) Display this help.　　　　　　　　　　　　　　　　　　　　　　　　　　　　　#　查看帮助
quit      (q) Quit mysql.　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　＃　退出
source    (.) Execute an SQL script file. Takes a file name as an argument.       # 读取SQL脚本
system    (!) Execute a system shell command.                                     #  执行shell命令
tee       (T) Set outfile [to_outfile]. Append everything into given outfile.     #  设置输出文件
use       (u) Use another database. Takes database name as argument.              # 指定数据库
charset   (C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets. # 指定字符集
warnings  (W) Show warnings after every statement.                                # 显示警告信息
nowarning (w) Don't show warnings after every statement. 　　　　　　　　　　　　　　 # 不显示警告信息　

查看mysql服务端帮助信息，可使用help contents:

MariaDB [(none)]> help contents
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
   Account Management                   # 账户管理语句
   Administration                       # 管理员  
   Compound Statements               
   Data Definition                      # 数据定义语句
   Data Manipulation                    # 数据操作语句
   Data Types                           # 数据类型
   Functions                            # 函数
   Functions and Modifiers for Use with GROUP BY
   Geographic Features
   Help Metadata
   Language Structure
   Plugins
   Procedures
   Table Maintenance
   Transactions
   User-Defined Functions
   Utility

要查看某一类命令或者某单一命令使用，均可使用help KEYWORD查看，例如：

MariaDB [(none)]> help Data Definition;
You asked for help about help category: "Data Definition"
For more information, type 'help <item>', where <item> is one of the following
topics:
   ALTER DATABASE
   ALTER EVENT
   ALTER FUNCTION

   ...

   RENAME TABLE
   TRUNCATE TABLE

MariaDB [(none)]> help create table;
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

Or:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    select_statement

Or:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

...

数据类型

自动增长类型
        AUTO_INCREMENT
字符型：
        char/binary        varchar/varbinary
        text/ngtext/blob/ediumblob/longblob
        set/enum
数值型
        int/tinyint/smallint/mediumint/bigint
        decimal
        float/double
日期型
        date/time/datetime/timestamp/year(2,4)
                

常见数据库管理语句

选择操作的数据库

USE db_name

创建

CREATE  {DATABASE | SCHEMA}  [IF NOT EXISTS]  db_name;
[DEFAULT]  CHARACTER SET [=] charset_name                 # 设置字符集
[DEFAULT]  COLLATE [=] collation_name                     # 设置排序方式
                        
SHOW CHARACTER SET 　　　　　　　　　　  #  查看支持的所有的字符集
SHOW  COLLATION                       #  查看支持的所有排序方式

修改

ALTER {DATABASE | SCHEMA}  [db_name]

删除

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

查看

SHOW {DATABASES | SCHEMAS}
    [LIKE 'pattern' | WHERE expr]          

 

表管理

创建

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name    
    (create_definition,...)          #  字段定义  
    [table_options]                  #  数据表的基本设置
    [partition_options]              #  分割选项

修改

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
    [alter_specification [, alter_specification] ...]
    [partition_options]

删除

DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]

DML数据操作语言

查看

SELECT
    [ALL | DISTINCT | DISTINCTROW ]      
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

插入

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

删除

Single-table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

Multiple-table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

修改

Single-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

Multiple-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]

权限管理（账户管理）

创建用户

CREATE USER user_specification
    [, user_specification] ...

user_specification:
    user
    [
        IDENTIFIED BY [PASSWORD] 'password'
      | IDENTIFIED WITH auth_plugin [AS 'auth_string']
    ]

删除用户

DROP USER user [, user] ...

向用户授权

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_specification [, user_specification] ...
    [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
    [WITH with_option ...]

GRANT PROXY ON user_specification
    TO user_specification [, user_specification] ...
    [WITH GRANT OPTION]

取消授权或者调整权限

REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user [, user] ...

REVOKE ALL PRIVILEGES, GRANT OPTION
    FROM user [, user] ...

REVOKE PROXY ON user
    FROM user [, user] ...