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