Linux之MariaDB基础详解

作者: 魏镇坪 | 来源:发表于2016-03-07 14:18 被阅读3236次

Lamp之MariaDB

数据库结构模型分类

1、层次模型

2、网状模型

3、关系模型

关系模型的组成部分

二维关系
- 表
  - row
  - column
- 索引
  - index
- 视图
  - view (只包含固定字段，不包含其他字段)

关系型数据库的常见组件:

数据库 : Database
表 : table
- 行 : row
- 列 : column
索引 : index
视图 : view
用户 : user
权限 : privilege
存储过程 : procedure
存储函数 : function
触发器 : trigger
事件调度器 : event schedule

DBA的组成部分

SQL引擎
- 分析器
- 计划执行器
- 优化器
- 操作求解器
存储引擎
- 文件和存储接口
- 缓冲管理器
- 磁盘空间管理器
- 恢复管理器
- 事务管理器
- 锁管理器

SQL接口（structured query Language）

类似于OS的shell接口，操作数据库的数据接口，也提供了编程功能

SQL接口语言的标准（其由ANSL组织定义）
- SQL86
- SQL89
- SQL92
- SQL99
- SQL03
SQL接口的语言分类
- DDL:(Data Defined Language)
  - create
  - alter
  - drop
- DML:(Data Manapulating Language)
  - insert
  - delete
  - update
  - select
- DCL: (Data Control Language)
  - grant
  - revoke

DB是否支持事务的测试标准

ACID
- A : 原子性 --> 不可分割的整体
- C : 一致性 --> 数据的变化是一致性的
- I : 隔离性 --> 事务彼此之间是隔离的
- D : 持久性 --> 只要一个事务完成，它都是持久完成的。
事务是将组织多个操作为一个整体，要么全部执行，要全部不执行。其实现机制为：
- 回滚机制
- 事务机制

RDBMS设计范式基础概念

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴德斯科范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般说来，数据库只需满足第三范式(3NF）就行了。

1）第一范式（1NF）
- 所谓第一范式（1NF）是指在关系模型中，对域添加的一个规范要求，所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。即实体中的某个属性有多个值时，必须拆分为不同的属性。在符合第一范式（1NF）表中的每个域值只能是实体的一个属性或一个属性的一部分。简而言之，第一范式就是无重复的域，并不可分析
2）第二范式（2NF）
- 第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或记录必须可以被唯一地区分，即不能有两个行是一样的。选取一个能区分每个实体的属性或属性组，作为实体的唯一标识。
3）第三范式（3NF）
- 第三范式（3NF）是第二范式（2NF）的一个子集，即满足第三范式（3NF）必须满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个关系中不包含已在其它关系已包含的非主关键字信息。简而言之，不依赖于非主属性，表与子表中不能有相同的字段。

数据库的运行模型：

数据库为C/S架构，如下：

S : server ,监听于套接字止，接收并处理客户端的应用请求
C : Client
- 程序接口
  - CLI
  - GUI
- 应用编程接口
  - ODBC : Open Database Connection(开放式数据互联)

MYSQL的工作模型

单进程多线程
用户连接通过线程实现
一个线程池可以定义mysql的并发连接
处理用户连接的叫连接线程
数据字典:数据一切的元数据信息,依赖mysql库来存储

数据库的基础概念

1）约束 : Constraint
- 主键 : 一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行，且必须提供数据，即NOT NULL
- 唯一键 : 一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行，允许为null
- 外键 : 一个表中的某字段可填入数据取决于另一个表中的主键已有的数据。
- 检查性约束 : 表达式约束,取决于表达式的要求
2） 索引 : 将表中的一个或多个字段中的数据复制一份另存，并且这些字段需要按特定的次序排序存储,常见的索引类型:
- 树形索引(MariaDB默认为Btree索引)
- bash索引

索引有利于读请求,但不得写请求

3） 关系运算
- 选择 : 挑选出符合条件的行（部分）
- 投影 : 挑选出需要的字段
- 连接 : 将多张表关联起来
  - 内连接
  - 外连接
    - 左外连接
    - 右外连接
  - 自连接
4）数据抽象 :
- 物理层 : 决定数据的存储格式,即RDBMS 在磁盘上如何组织文件
- 逻辑层 : 描述DB存储什么数据,以及数据间存在什么样的关系
- 视图层 : 描述DB中的部分数据
1. 关系模型的分类
- 关系模型
- 实体关系模型
- 基于对象的关系模型
- 半结构化的关系模型
  - XML格式就是一种半结构化数据

常见的RDBMS数据库

MySQL
MariaDB
PostgreSQL(pgsql)
Oracle
MSSQL

=============

MariaDB的特性

插件式存储引擎(注:存储引擎也称之为"表类型")
- MYISAM --> Aria
  - 不支持事务
- INNODB --> XtraDB
  - 支持事务
诸多扩展和新特性
提供了更多的测试组件
truly open source

Mariadb的安装

通用二进制格式安装过程
a. 准备数据目录
    以/mydata/data为例；
b. 配置mariadb
                    # groupadd -r -g 306 mysql
                    # useradd -r -g 306 -u 306 mysql
                    # tar xf mariadb-VERSION.tar.xz -C /usr/local
                    # ln -sv mariadb-version mysql
                    # cd /usr/local/mysql
                    # chown -R root:mysql ./*
                    # scripts/mysql_install_db --datadir=/mydata/data --user=mysql
                    # cp supper-files/mysql.server /etc/rc.d/init.d/mysqld
                    # chkconfig --add mysqld
c. 准备配置文件
    配置格式：类ini格式，为各程序均通过单个配置文件提供配置信息；
    [prog_name]
    
能用二进制格式安装,配置文件查找次序：
/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf

OS提供的mariadb rpm包安装的配置文件查找次序:
/etc/mysql/my.cnf --> /etc/my.cnf --> --default-extra=/PATH/TO/conf_file --> ~/my.cnf

以上两者越靠后就是最后生效的.

                    # mkdir /etc/mysql
                    # cp support-files/my-large.cnf /etc/mysql/my.cnf

                    添加三个选项：
                        [mysqld]
                        datadir = /mydata/data
                        innodb_file_per_table = on
                        skip_name_resolve = on

MariDB程序的组成

Client
- mysql : CLI交互式客户端程序
- mysqldump : 备份工具
- mysqladmin: 管理工具
- mysqlbinlog : 查看二进制日志工具
Server
- mysqld: 服务端进程
- mysqld_safe : 服务端进程,默认也是运行的此进程
- mysqld_multi : 服务端进程, 多实例
- mysql_upgrade : 升级工具

服务端监听的两种socket地址

ip socket
- 监听在3306/tcp,支持远程通信
unix socket
- 监听在sock文件上(/tmp/mysql.sock, /var/lib/mysql/mysql.sock),仅支持本地通信,通信主机为localhost,127.0.0.1都基于unix socket文件通信

命令行交互式客户端程序---mysql工具

options
- -uUSERNAME : 用户名,默认为root
- -hHOST : 服务器主机,默认为localhost
- -pPASSWD : 用户的密码
- dDB_NAME: 连接到服务端之后,指明默认数据库
- -e 'SCRIPT' : 连接至MYSQL运行某命令后,直接退出,并返回结果
```
mysql -uroot -h127.0.0.1 -pmagedu -e 'show databases;'
```

注意: mysql的用户帐号由两部分组成,'username'@'hostname',其中host用于限制此用户可通过哪些主机连接当前的MSYQL服务器

支持通配符:
- % : 匹配任意长度的任意字符
```
172.16.%.%
```
- _ : 匹配任意单个字符
内置命令
- \u DB_NAME : 设定哪个库为默认数据库
- \q : 退出
- \d CHAR : 设定新的语句结束符
- \g : 语句通用结束标记
- \G : 语句结束标记,但以竖排方式显示
- \s : 返回客户端与服务端的连接状态
- \c : 取消命令运行

通过mysql协议发往服务器执行并取回结果,每个命令都必须有结束符,默认为";",示例如下:

for i in {1..100};do AGE=$[$RANDOM%100];mysql -uroot -pM8T9cw -e "insert mydb.student(id,name,age) value ($i,\"stu$i\",$AGE);"; done

获取命令帮助
- help

SQL中的数据类型

字符型
- 定长字符型 : (最多255个字符)
  - CHAR(#) : 不区分字符大小写
  - BINARY(#) : 区分字符大小写
- 可变长字符型 :
  - VARCHAR(#) : 不区分字符大小写,需要在字符长度加1,最多65536个字符
  - VARBINARY(#) : 区分字符大小写,需要在字符长度加1,最多65536个字符
  - TEXT : 可存文本(2^32)个字符,只能存纯文本,不区分大小写
    - TINYTEXT
    - TEXT
    - MEDIUMTEXT
    - LONGTEXT
  - BLOB : 文本(2^32)个字符,可以存储图片
内置类型
- SET : 集合
- ENUM : 枚举
数值型
- 精确数值型
  - INT
    - TINYINT : 一个字节
    - SMALLINT : 二个字节
    - MEDINUMINT : 三个字节
    - INT : 四个字节
    - BIGINT : 八个字节
- 近似数据型
  - FLOAT : 单精度
  - DOBULE : 双精度
日期时间型
- DATE : 日期型
- TIME : 时间型
- DATETIME : 日期时间型
- TIMESTAMP : 时间戳(从过去到现在经过的秒数)
- YEAR(2) : 2位年数
- YEAR(4) : 4位年数
数据类型修饰符
- 所有类型都适用:
  - NOT NULL : 非空
  - DEFAULT value : 默认值
- 数值型适用
  - AUTO_INCREMENT : 自增长
  - UNSIGNED : 无符号,通常用于Int后面,进行修饰为正整数
- 字段修饰符
  - PRIMARY KEY : 主键定义
  - UNIQUE KEY : 唯一键定义

==========

数据库操作语言

数据库操作

使用格式

create database | schema [if not exists]'DB_NAME';
drop database | schema 'DB_NAME';

查看数据库
- show databases;
创建数据库
- create database mydb;
删除数据库
- drop database mydb;
查看支持的所有字符集
- show character set;
查看支持的所有排序规则
- show collation;
修改数据库的默认字符集
- alter database testdb character set utf32;
修改数据库的默认字符排序规则
- alter database testdb collate utf32_sinhala_ci;

表操作

使用格式

create table [if not exists] tb_name (col1 datatype 修饰符, col2 datatype 修饰符) engine=' '

查看所有的引擎(被支持的)
- show engines
查看所有数据库中的表
- show tables;
- show tables from mysql;
查看表结构
- desc students;
创建表
- create table students(id int unsigned not null primary key, name varchar(30) not null, age tinyint unsigned not null,gender enum('F','M'))
向表中添加字段
- alter table students add second_name char(30);
向表中删除字段
- alter table students drop second_name;
对表添加主键
- alter table students2 add primary key (id);
对表删除主键
- alter table students2 drop primary key;
对表添加索引
- alter table students2 add index name (name);
对表删除索引
- alter table students2 drop index name;
- drop index name on students2;
对表添加唯一键
- alter table students drop second_name;
对表删除唯一键
- alter table students drop index name;
修改字段字义属性
- alter table students modify name char(20);
- alter table students2 change name new_name char(20) after id;
- alter table students2 change name new_name char(20);
```
modify : 只可以修改字段定义的属性
change : 可以修改字段名称和定义的字段属性
```
查看表状态
- show table status like 'students2'\G
修改表引擎
- alter table students2 engine[=]myisam;
查看表的字段描述
- desc students;
查看库中的所有表
- show tables
- show tables from mysql;
删除表
- drop table students2;

向表中插入数据

insert weizi value(1,'zhen',30,'F');
insert weizi values(3,'wei',30,'F'),(4,'ping',31,'F');
insert weizi (id,name) value (5,"weizi");

删除表中的数据

delete from weizi where name="zhen";
delete from weizi where age is null;
delete from weizi where age > 30;
delete from weizi where id >=50 and age <=20;
delete from weizi order by age asc limit 5;

修改表中的字段值

update weizi set age=35 where id=3 and age=30;
update weizi set age=age-5 where age=35;
update weizi age=age-age;
update weizi set age=age-5 where order by id desc limit 10;
update weizi set age=age-15 where name not like 'stu%';

Select操作语句

使用格式

select col1,col2,....from tb1_name [where clause] [order y 'col_name'] [limit [m,]n]

字段表示法
- * : 表示所有字段
- as : 字段别名, col1 as alias1
where clause
- 操作符:
  - ==
  - <
  - >
  - <=
  - >=
  - !=
  - between....and...
- 条件逻辑操作
  - and
  - or
  - not
- 模糊匹配
  - like
    - %
    - _
  - rlike 'pattern' : 基于正则表达式匹配
  - is null
  - is no null
排序
- desc : 降序
- asc : 升序

Select示例

select name,age from students where age >30 and age<80;
select name,age from students where age between 30 and 80;
select name from students where name like '%ang%';
select name from students where name rlike '^.*ang.*$;
select name,age from students where age is null;
select name,age form students where age is not null;
select id,name from students order by name;
select id,name from students order by name desc;

权限及授权管理

用户表示方法:

username@host

管理权限的分类
- 管理权限
- 数据库
- 表
- 字段
- 存储例程

授权:

授权语法格式
- grant pri_type,...on [object_type] db_name.tbl_name to 'user'@'host' [identified by 'PASSWD']
  - pri_type
    - all privileges : 表示全部权限
  - db_name.tbl_name的表示方法:
    - *.* : 所有库的所有表
    - db_name.* : 指定库的所有表
    - db_name.tbl_name : 指定库的特定表
    - db_name.routine_name : 指定库上的存储过程或存储函数
  - object_type
    - table
    - function
    - procedure
授权示例:
- grant all privileges on mydb.* to 'zhenping'@'172.16.%.%' identified by 'MT8ddd';

取消权限

取消授权语法格式
- revoke pri_type,.... on db_name.tb_name from 'user'@'host';
取消授权示例:
- revoke all privileges on mydb.* from 'zhenping'@'172.16.%.%';

查看用户权限

show grants for 'user'@'host';

让新授权的权限立即生效

flush privileges;

1 Mariadb服务进程启动时会读取mysql库中的所有授权表至内存中;
2 grant和revoke等执行权限操作时会保存于表中,mariadb的服务进程会自动重读授权表
3 对于不能够或不能及时重读授权表,可手动让服务进程重启授权表,使用flush privileges

番外往篇

在CentOS 6 上手动编译安装mysql

    首先关闭之前启动的mysql服务，并且将其卸载

    [root@localhost php.d]# service mysqld stop

    [root@localhost ~]# rpm -e mysql-server
    warning: /var/log/mysqld.log saved as /var/log/mysqld.log.rpmsave

    为了彻底删除，我们也要将它产生的日志文件也给删了

    [root@localhost ~]# rm -f /var/log/mysqld.log.rpmsave

    下载mariadb的源码包（ mariadb-5.5.43-linux-x86_64.tar.gz），然后创建一个系统用户

    [root@localhost ~]# groupadd -r mysql

    [root@localhost ~]# id mysql
    uid=27(mysql) gid=27(mysql) groups=27(mysql)

    将源码包解压缩至指定的目录

    [root@localhost ~]# tar xf mariadb-5.5.43-linux-x86_64.tar.gz -C /usr/local

    为解压缩出来的目录做一个链接，因为我们用到相关文件时，引用的名称为mysql

    [root@localhost ~]# cd /usr/local

    [root@localhost local]# ln -sv mariadb-5.5.43-linux-x86_64 mysql

    将mysql目录下的文件修改其权限

    [root@localhost mysql]# chown -R root:mysql ./*

    接下来我们就要创建mysql的专用数据存储目录了。这个很明显，存放数据的目录当然要单独分区，在此处，我们将它放在LVM2上

    我们重新挂载上一个硬盘/dev/sdb，将其分成两个区，并调整为8e，即LVM的格式。

    [root@localhost ~]# fdisk /dev/sdb

    创建完成后，重读一下分区表

    [root@localhost ~]# partx -a /dev/sdb
    BLKPG: Device or resource busy
    error adding partition 1
    BLKPG: Device or resource busy
    error adding partition 2

    如此便创建成功了，接着我们将这两个分区只作为LVM2

    [root@localhost ~]# pvcreate /dev/sdb1
      Physical volume "/dev/sdb1" successfully created
    [root@localhost ~]# pvcreate /dev/sdb2
      Physical volume "/dev/sdb2" successfully created

    创建vg

    [root@localhost ~]# vgcreate myvg /dev/sdb1 /dev/sdb2
    Volume group "myvg" successfully created

    创建lv

    [root@localhost ~]# lvcreate -L10G -n mydata myvg
    Logical volume "mydata" created

    我们想要在这个lvm上安装xfs文件系统，所以先安装这个文件系统所需要的相关文件

    [root@localhost ~]# yum install xfsprogs -y

    xfs文件系统比ext4有更好的扩展性

    [root@localhost ~]# modprobe xfs
    [root@localhost ~]# modinfo xfs
    filename:       /lib/modules/2.6.32-504.el6.x86_64/kernel/fs/xfs/xfs.ko
    license:        GPL
    description:    SGI XFS with ACLs, security attributes, large block/inode numbers, no debug enabled
    author:         Silicon Graphics, Inc.
    srcversion:     4392D4D583B9D2781E4F61E
    depends:        exportfs
    vermagic:       2.6.32-504.el6.x86_64 SMP mod_unload modversions 

    将lvm格式化为xfs

    [root@localhost ~]# mkfs.xfs /dev/myvg/mydata
    meta-data=/dev/myvg/mydata       isize=256    agcount=4, agsize=655360 blks
             =                       sectsz=512   attr=2, projid32bit=0
    data     =                       bsize=4096   blocks=2621440, imaxpct=25
             =                       sunit=0      swidth=0 blks
    naming   =version 2              bsize=4096   ascii-ci=0
    log      =internal log           bsize=4096   blocks=2560, version=2
             =                       sectsz=512   sunit=0 blks, lazy-count=1
    realtime =none                   extsz=4096   blocks=0, rtextents=0

    创建文件系统挂载目录

    [root@localhost ~]# mkdir /mydata

    将挂载信息添加到配置文件中，使开机时自动挂载

    [root@localhost ~]# vim /etc/fstab

    /dev/myvg/mydata        /mydata                 xfs     defaults        0 0

    重读一下配置文件，使其挂载上

    [root@localhost ~]# mount -a

    查看挂载信息

    [root@localhost ~]# mount
    /dev/mapper/myvg-mydata on /mydata type xfs (rw)

    在这个目录下创建一个数据库目录

    [root@localhost mydata]# mkdir data

    修改其属主属组

    [root@localhost mydata]# chown mysql:mysql data
    [root@localhost mydata]# ll
    total 0
    drwxr-xr-x. 2 mysql mysql 6 Oct 11 03:37 data

    回到mysql的目录

    [root@localhost local]# cd /usr/local/mysql/

    [root@localhost mysql]# ls scripts/
    mysql_install_db

    注意这个目录下的此脚本，它是专门用于生成mysql初始化的

    查看这个初始化脚本的相关配置信息

    [root@localhost mysql]# scripts/mysql_install_db --help

    [root@localhost mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data/

    此时查看初始化生成的相关目录，其实就是库文件

    [root@localhost mysql]# ls /mydata/data
    aria_log.00000001  aria_log_control  mysql  performance_schema  test

    接着准备mysql的服务脚本

    [root@localhost mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mysqld

    添加上这个脚本

    [root@localhost mysql]# chkconfig --add mysqld
    [root@localhost mysql]# chkconfig --list mysqld
    mysqld          0:off   1:off   2:on    3:on    4:on    5:on    6:off

    接下来就要准备配置文件了，复制一个模板，稍作修改便可成为配置文件

    [root@localhost mysql]# mkdir /etc/mysql
    [root@localhost mysql]# cp support-files/my-large.cnf /etc/mysql/my.cnf

    对这个配置文件，做一下针对性的修改

    [root@localhost mysql]# vim /etc/mysql/my.cnf

    其中的配置文件分为n段

    [client]  mysqld客户端要读取的配置
    [mysqld]  mysqld服务器端要读取的配置

    我们仅需要在[mysqld]中，稍作修改即可

    thread_concurrency = 8
    datadir=/mydata/data
    innodb_file_per_table=on
    skip_name_resolve=on（跳过名称解析）

    说明：mysql每次通过客户端进行连接时，它都会把ip地址反解成主机名，并在本地做权限检查，这相当麻烦。所以干脆跳过，以后主机来访时，我们仅根据其客户端来源ip做检查，授权时也根据ip做授权。

    启动mysql服务

    [root@localhost mysql]# service mysqld start

    [root@localhost mysql]# ss -tnl
    State      Recv-Q Send-Q                                   Local Address:Port                                     Peer Address:Port 
    LISTEN     0      128                                                 :::22                                                 :::*     
    LISTEN     0      128                                                  *:22                                                  *:*     
    LISTEN     0      128                                          127.0.0.1:631                                                 *:*     
    LISTEN     0      128                                                ::1:631                                                :::*     
    LISTEN     0      100                                                ::1:25                                                 :::*     
    LISTEN     0      100                                          127.0.0.1:25                                                  *:*     
    LISTEN     0      128                                          127.0.0.1:6010                                                *:*     
    LISTEN     0      128                                                ::1:6010                                               :::*     
    LISTEN     0      50                                                   *:3306                                                *:*     
    LISTEN     0      128                                                 :::80                                                 :::*  

    在/usr/local/mysql/bin中有一些二进制程序

    [root@localhost mysql]# ls bin
    aria_chk       myisam_ftdump      mysqlbug                    mysqld_safe           mysql_plugin               mysql_upgrade
    aria_dump_log  myisamlog          mysqlcheck                  mysqldump             mysql_secure_installation  mysql_waitpid
    aria_ftdump    myisampack         mysql_client_test           mysqldumpslow         mysql_setpermission        mysql_zap
    aria_pack      my_print_defaults  mysql_client_test_embedded  mysql_embedded        mysqlshow                  mytop
    aria_read_log  mysql              mysql_config                mysql_find_rows       mysqlslap                  perror
    innochecksum   mysqlaccess        mysql_convert_table_format  mysql_fix_extensions  mysqltest                  replace
    msql2mysql     mysqladmin         mysqld                      mysqlhotcopy          mysqltest_embedded         resolveip
    myisamchk      mysqlbinlog        mysqld_multi                mysqlimport           mysql_tzinfo_to_sql        resolve_stack_dump

    查看相关的进程

    [root@localhost mysql]# ps aux | grep mysql
    root       2734  0.0  0.1  11472  1376 pts/0    S    04:02   0:00 
                /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/mydata/data --pid-file=/mydata/data/localhost.localdomain.pid
    mysql      3135  0.3 13.2 842784 132716 pts/0   Sl   04:02   0:00 
                /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/mydata/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/mydata/data/localhost.localdomain.pid --socket=/tmp/mysql.sock --port=3306
    root       3176  0.0  0.0 103252   836 pts/0    S+   04:07   0:00 grep mysql

    注意：其中含有mysqld-safe程序

    讲述下/usr/local/bin/mysql,这个命令行使用的相关方法

    [root@localhost mysql]# mysql

    mysql> use mysql;
    mysql> SELECT User,Host,Password FROM user;
    +------+-----------------------+----------+
    | User | Host                  | Password |
    +------+-----------------------+----------+
    | root | localhost             |          |
    | root | localhost.localdomain |          |
    | root | 127.0.0.1             |          |
    | root | ::1                   |          |
    |      | localhost             |          |
    |      | localhost.localdomain |          |
    +------+-----------------------+----------+
    6 rows in set (0.00 sec)

    设置安全初始化程序，禁止管理员禁止登陆，清空匿名用户，设置管理员的密码

    [root@localhost mysql]# /usr/local/mysql/bin/mysql_secure_installation

    Enter current password for root (enter for none): 
    OK, successfully used password, moving on...

    Setting the root password ensures that nobody can log into the MariaDB
    root user without the proper authorisation.

    Set root password? [Y/n] Y
    New password: 
    Re-enter new password: 
    Password updated successfully!
    Reloading privilege tables..
     ... Success!


    By default, a MariaDB installation has an anonymous user, allowing anyone
    to log into MariaDB without having to have a user account created for
    them.  This is intended only for testing, and to make the installation
    go a bit smoother.  You should remove them before moving into a
    production environment.

    Remove anonymous users? [Y/n] Y
     ... Success!

    Normally, root should only be allowed to connect from 'localhost'.  This
    ensures that someone cannot guess at the root password from the network.

    Disallow root login remotely? [Y/n] Y
     ... Success!

    By default, MariaDB comes with a database named 'test' that anyone can
    access.  This is also intended only for testing, and should be removed
    before moving into a production environment.

    Remove test database and access to it? [Y/n] Y
     - Dropping test database...
     ... Success!
     - Removing privileges on test database...
     ... Success!

    Reloading the privilege tables will ensure that all changes made so far
    will take effect immediately.

    Reload privilege tables now? [Y/n] Y
     ... Success!

    Cleaning up...

    All done!  If you've completed all of the above steps, your MariaDB
    installation should now be secure.

    Thanks for using MariaDB!

    此时你用root登录，会发现已经不能直接登录了

    [root@localhost mysql]# mysql
    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

    可用这种方式登录

    [root@localhost mysql]# mysql -uroot -p
    mysql> use mysql;
    mysql> SELECT User,Host,Password FROM user;
    +------+-----------+-------------------------------------------+
    | User | Host      | Password                                  |
    +------+-----------+-------------------------------------------+
    | root | localhost | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
    | root | 127.0.0.1 | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
    | root | ::1       | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
    +------+-----------+-------------------------------------------+
    3 rows in set (0.00 sec)

    mysql> status
    --------------
    mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

    Connection id:      18
    Current database:   mysql
    Current user:       root@localhost
    SSL:            Not in use
    Current pager:      stdout
    Using outfile:      ''
    Using delimiter:    ;
    Server version:     5.5.43-MariaDB-log MariaDB Server
    Protocol version:   10
    Connection:     Localhost via UNIX socket
    Server characterset:    latin1
    Db     characterset:    latin1
    Client characterset:    latin1
    Conn.  characterset:    latin1
    UNIX socket:        /tmp/mysql.sock
    Uptime:         20 min 35 sec

    Threads: 1  Questions: 38  Slow queries: 0  Opens: 1  Flush tables: 2  Open tables: 27  Queries per second avg: 0.030
    --------------

    查看命令的相关帮助文档

    mysql> HELP CREATE DATABASE;

    mysql> SHOW ENGINES;

    mysql> CREATE DATABASE IF NOT EXISTS testdb;

    mysql> use testdb;

    mysql> CREATE TABLE tb1(id int NOT NULL,name VARCHAR(100) NOT NULL,age tinyint);

    mysql> SHOW TABLES;
    +------------------+
    | Tables_in_testdb |
    +------------------+
    | tb1              |
    +------------------+
    1 row in set (0.00 sec)

    mysql> SHOW DATABASES;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | testdb             |
    +--------------------+
    4 rows in set (0.00 sec)

    mysql> DESC tb1;
    +-------+--------------+------+-----+---------+-------+
    | Field | Type         | Null | Key | Default | Extra |
    +-------+--------------+------+-----+---------+-------+
    | id    | int(11)      | NO   |     | NULL    |       |
    | name  | varchar(100) | NO   |     | NULL    |       |
    | age   | tinyint(4)   | YES  |     | NULL    |       |
    +-------+--------------+------+-----+---------+-------+
    3 rows in set (0.02 sec)

9.mysql的基础命令

    登录进mysql

    [root@localhost mysql]# mysql -uroot -predhat

    mysql> CREATE DATABASE testdb;

    mysql> CREATE TABLE students (id int UNSIGNED NOT NULL PRIMARY KEY,name VARCHAR(20) NOT NULL,age tinyint UNSIGNED);

    mysql> DESC students;
    +-------+---------------------+------+-----+---------+-------+
    | Field | Type                | Null | Key | Default | Extra |
    +-------+---------------------+------+-----+---------+-------+
    | id    | int(10) unsigned    | NO   | PRI | NULL    |       |
    | name  | varchar(20)         | NO   |     | NULL    |       |
    | age   | tinyint(3) unsigned | YES  |     | NULL    |       |
    +-------+---------------------+------+-----+---------+-------+
    3 rows in set (0.00 sec)

    或者是单独定义修饰符表示的内容，即定义联合式的主键

    mysql> CREATE TABLE tbl2 (id int UNSIGNED NOT NULL,name VARCHAR(20) NOT NULL,age tinyint UNSIGNED,PRIMARY KEY(id,name));

    这里只要是id，name这两个键的联合内容不相同即可。

    mysql> SHOW TABLE STATUS LIKE 'students'\G;
    *************************** 1. row ***************************
               Name: students
             Engine: InnoDB
            Version: 10
         Row_format: Compact
               Rows: 0
     Avg_row_length: 0
        Data_length: 16384
    Max_data_length: 0
       Index_length: 0
          Data_free: 0
     Auto_increment: NULL
        Create_time: 2015-10-11 04:33:41
        Update_time: NULL
         Check_time: NULL
          Collation: latin1_swedish_ci
           Checksum: NULL
     Create_options: 
            Comment: 
    1 row in set (0.00 sec)

    ERROR: 
    No query specified

    \G的含义是竖排显示

    在帮助手册中查看ALTER, CHANGE, MODIFY这三个命令的不同

    添加字段

    mysql> ALTER TABLE students ADD gender ENUM('m','f');

    当修改字段名称时，对剩下的要有定义

    mysql> ALTER TABLE students CHANGE id sid int UNSIGNED NOT NULL;

    mysql> DESC students;
    +--------+---------------------+------+-----+---------+-------+
    | Field  | Type                | Null | Key | Default | Extra |
    +--------+---------------------+------+-----+---------+-------+
    | sid    | int(10) unsigned    | NO   | PRI | NULL    |       |
    | name   | varchar(20)         | NO   |     | NULL    |       |
    | age    | tinyint(3) unsigned | YES  |     | NULL    |       |
    | gender | enum('m','f')       | YES  |     | NULL    |       |
    +--------+---------------------+------+-----+---------+-------+
    4 rows in set (0.01 sec)

    修改惟一键

    mysql> ALTER TABLE students ADD UNIQUE KEY(name);

    mysql> DESC students;
    +--------+---------------------+------+-----+---------+-------+
    | Field  | Type                | Null | Key | Default | Extra |
    +--------+---------------------+------+-----+---------+-------+
    | sid    | int(10) unsigned    | NO   | PRI | NULL    |       |
    | name   | varchar(20)         | NO   | UNI | NULL    |       |
    | age    | tinyint(3) unsigned | YES  |     | NULL    |       |
    | gender | enum('m','f')       | YES  |     | NULL    |       |
    +--------+---------------------+------+-----+---------+-------+
    4 rows in set (0.01 sec)

    添加索引

    mysql> SHOW INDEXES FROM students\G;
    *************************** 1. row ***************************
            Table: students
       Non_unique: 0
         Key_name: PRIMARY
     Seq_in_index: 1
      Column_name: sid
        Collation: A
      Cardinality: 0
         Sub_part: NULL
           Packed: NULL
             Null: 
       Index_type: BTREE
          Comment: 
    Index_comment: 
    *************************** 2. row ***************************
            Table: students
       Non_unique: 0
         Key_name: name
     Seq_in_index: 1
      Column_name: name
        Collation: A
      Cardinality: 0
         Sub_part: NULL
           Packed: NULL
             Null: 
       Index_type: BTREE
          Comment: 
    Index_comment: 
    2 rows in set (0.02 sec)

    删除索引

    mysql> ALTER TABLE students DROP age;

    mysql> SHOW INDEXES FROM students\G;

    mysql> DROP INDEX name ON students;

DML:
    
    mysql> INSERT INTO students VALUES (1,'YangGuo','m');

    mysql> SELECT * FROM students;
    +-----+---------+--------+
    | sid | name    | gender |
    +-----+---------+--------+
    |   1 | YangGuo | m      |
    +-----+---------+--------+
    1 row in set (0.00 sec)

    mysql> INSERT INTO students (sid,name) VALUES (3,'ZhangWuji'),(4,'ZhaoMin');

    mysql> SELECT * FROM students;
    +-----+-----------+--------+
    | sid | name      | gender |
    +-----+-----------+--------+
    |   1 | YangGuo   | m      |
    |   3 | ZhangWuji | NULL   |
    |   4 | ZhaoMin   | NULL   |
    +-----+-----------+--------+
    3 rows in set (0.00 sec)

    查看创建命令的帮助信息

    mysql> HELP DELETE;
    Name: 'DELETE'
    Description:
    Syntax:
    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]

    Or:

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

    mysql> HELP SELECT;
    Name: 'SELECT'
    Description:
    Syntax:
    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]]

    mysql> SELECT * FROM students WHERE gender='m';
    +-----+---------+--------+
    | sid | name    | gender |
    +-----+---------+--------+
    |   1 | YangGuo | m      |
    +-----+---------+--------+
    1 row in set (0.00 sec)

    mysql> SELECT * FROM students WHERE gender IS NULL;
    +-----+-----------+--------+
    | sid | name      | gender |
    +-----+-----------+--------+
    |   3 | ZhangWuji | NULL   |
    |   4 | ZhaoMin   | NULL   |
    +-----+-----------+--------+
    2 rows in set (0.00 sec)

    mysql> SELECT * FROM students ORDER BY name DESC LIMIT 1,2;
    +-----+-----------+--------+
    | sid | name      | gender |
    +-----+-----------+--------+
    |   3 | ZhangWuji | NULL   |
    |   1 | YangGuo   | m      |
    +-----+-----------+--------+
    2 rows in set (0.01 sec)

    mysql> SELECT * FROM students WHERE sid>=2 AND sid<=4;
    +-----+-----------+--------+
    | sid | name      | gender |
    +-----+-----------+--------+
    |   3 | ZhangWuji | NULL   |
    |   4 | ZhaoMin   | NULL   |
    +-----+-----------+--------+
    2 rows in set (0.00 sec)

    mysql> SELECT * FROM students WHERE sid BETWEEN 2 AND 4;
    +-----+-----------+--------+
    | sid | name      | gender |
    +-----+-----------+--------+
    |   3 | ZhangWuji | NULL   |
    |   4 | ZhaoMin   | NULL   |
    +-----+-----------+--------+
    2 rows in set (0.00 sec)

    mysql> SELECT * FROM students WHERE name LIKE 'z%';
    +-----+-----------+--------+
    | sid | name      | gender |
    +-----+-----------+--------+
    |   3 | ZhangWuji | NULL   |
    |   4 | ZhaoMin   | NULL   |
    +-----+-----------+--------+
    2 rows in set (0.00 sec)

    mysql> SELECT * FROM students WHERE name RLIKE '.*[A-G]u.*';
    +-----+---------+--------+
    | sid | name    | gender |
    +-----+---------+--------+
    |   1 | YangGuo | m      |
    +-----+---------+--------+
    1 row in set (0.00 sec)

    mysql> SELECT sid as stuid,name as stuname FROM students;
    +-------+-----------+
    | stuid | stuname   |
    +-------+-----------+
    |     1 | YangGuo   |
    |     3 | ZhangWuji |
    |     4 | ZhaoMin   |
    +-------+-----------+
    3 rows in set (0.00 sec）

    mysql> HELP UPDATE
    Name: 'UPDATE'
    Description:
    Syntax:
    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]

创建用户
    mysql> CREATE USER 'wpuser'@'%' IDENTIFIED BY 'wppasswd';

    mysql> use mysql;

    查看存储用户的信息表

    mysql> SELECT User,Host,Password FROM user;
    +--------+-----------+-------------------------------------------+
    | User   | Host      | Password                                  |
    +--------+-----------+-------------------------------------------+
    | root   | localhost | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
    | root   | 127.0.0.1 | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
    | root   | ::1       | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
    | wpuser | %         | *7CD61EFBFDDDBD978EA9017F2A26A59DE4589025 |
    +--------+-----------+-------------------------------------------+
    4 rows in set (0.01 sec)

    这样任意主机，就可以通过wpuser进入mysql

    我们通过192.168.1.110连接本机的mysql

    [root@localhost ~]# mysql -uwpuser -h192.168.1.109 -p

    注意：如果连接不上，注意查看一下防火墙

    MariaDB [(none)]> SHOW DATABASES;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    +--------------------+
    1 row in set (0.04 sec)

    MariaDB [(none)]> HELP GRANT;
    Name: 'GRANT'
    Description:
    Syntax:
    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]

    object_type:
        TABLE
      | FUNCTION
      | PROCEDURE

    priv_level:
        *
      | *.*
      | db_name.*
      | db_name.tbl_name
      | tbl_name
      | db_name.routine_name

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

    ssl_option:
        SSL
      | X509
      | CIPHER 'cipher'
      | ISSUER 'issuer'
      | SUBJECT 'subject'

    with_option:
        GRANT OPTION
      | MAX_QUERIES_PER_HOUR count
      | MAX_UPDATES_PER_HOUR count
      | MAX_CONNECTIONS_PER_HOUR count
      | MAX_USER_CONNECTIONS count

      MariaDB [(none)]> HELP SHOW GRANTS;
    Name: 'SHOW GRANTS'
    Description:
    Syntax:
    SHOW GRANTS [FOR user]

    MariaDB [(none)]> SHOW GRANTS FOR 'wpuser'@'%';
    +-------------------------------------------------------------------------------------------------------+
    | Grants for wpuser@%                                                                                   |
    +-------------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'wpuser'@'%' IDENTIFIED BY PASSWORD '*7CD61EFBFDDDBD978EA9017F2A26A59DE4589025' |
    +-------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

    mysql> SHOW GRANTS FOR 'root'@'localhost';

    mysql> SHOW GRANTS FOR CURRENT_USER;

    mysql> GRANT SELECT,DELETE ON testdb.* TO 'testdb'@'%' IDENTIFIED BY 'testpass';

    以192.168.1.110访问数据库

    [root@localhost ~]# mysql -utestdb -h192.168.1.109 -p

    MariaDB [(none)]> SHOW DATABASES;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | testdb             |
    +--------------------+
    2 rows in set (0.00 sec)

    MariaDB [(none)]> use testdb;
    
    MariaDB [testdb]> SHOW GRANTS FOR CURRENT_USER;
    +-------------------------------------------------------------------------------------------------------+
    | Grants for testdb@%                                                                                   |
    +-------------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'testdb'@'%' IDENTIFIED BY PASSWORD '*00E247AC5F9AF26AE0194B41E1E769DEE1429A29' |
    | GRANT SELECT, DELETE ON `testdb`.* TO 'testdb'@'%'                                                    |
    +-------------------------------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)

    MariaDB [testdb]> CREATE TABLE tbl2(id int);
    ERROR 1142 (42000): CREATE command denied to user 'testdb'@'192.168.1.110' for table 'tbl2'

    此时会发现，因为我们之前设定的原因，当前用户没有权限创建表

    MariaDB [testdb]> DELETE FROM students WHERE sid=4;
    Query OK, 1 row affected (0.01 sec)

    回收权限

    MariaDB [testdb]> REVOKE DELETE ON testdb.* FROM 'testdb'@'%';
    ERROR 1044 (42000): Access denied for user 'testdb'@'%' to database 'testdb'

    发现自己没有权限回收权限，只有以管理员的权限才可以