阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

CentOS7下MariaDB常用管理命令详解

220次阅读
没有评论

共计 7957 个字符,预计需要花费 20 分钟才能阅读完成。

在 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   (\r) 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] ...

更多 MariaDB 相关教程见以下内容

Ubuntu 16.04 LTS 上安装 Nginx、MariaDB 和 HHVM 运行 WordPress  http://www.linuxidc.com/Linux/2016-10/136435.htm

Ubuntu 16.04 Dockerfile 安装 MariaDB  http://www.linuxidc.com/Linux/2016-09/135260.htm

Linux 系统教程:如何检查 MariaDB 服务端版本  http://www.linuxidc.com/Linux/2015-08/122382.htm

Ubuntu 16.04 下如何安装 MariaDB  http://www.linuxidc.com/Linux/2017-04/142915.htm

CentOS 7.3 二进制安装 MariaDB10.2.8 步骤  http://www.linuxidc.com/Linux/2017-10/147904.htm

CentOS 7 编译安装 MariaDB-10.1.22  http://www.linuxidc.com/Linux/2017-05/143291.htm

Ubuntu 上如何将 MySQL 5.5 数据库迁移到 MariaDB 10  http://www.linuxidc.com/Linux/2014-11/109471.htm

[翻译]Ubuntu 14.04 (Trusty) Server 安装 MariaDB  http://www.linuxidc.com/Linux/2014-12/110048htm

Ubuntu 14.04(Trusty)安装 MariaDB 10 数据库  http://www.linuxidc.com/Linux/2016-11/136833.htm

MariaDB 的详细介绍:请点这里
MariaDB 的下载地址:请点这里

本文永久更新链接地址:http://www.linuxidc.com/Linux/2017-10/148099.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计7957字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中