mariadb数据库基本使用

mariadb数据库简介

自甲骨文公司收购MySQL后，其在商业数据库与开源数据库领域市场的占有份额都跃居第一，这样的格局引起了业内很多的人士的担忧，因为商业数据库的老大有可能将MySQL闭源。为了避免Oracle将MySQL闭源，而无开源的类MySQL数据库可用，MySQL社区采用分支的方式来避开这个风险。MariaDB数据库就这样诞生了，MariaDB是一个向后兼容，可能在以后替代MySQL的数据库产品，其官方地址为：https://mariadb.org/ 。mariadb和mysql几乎是一样的。首先，mariadb就是由mysql的创始人负责维护的。而mariadb就是mysql创始人女儿的名字。使用方法和mysql相同

mariadb与mysql的区别：

1.MariaDB不仅仅是Mysql的一个替代品，MariaDB包括的一些新特性使它优于MySQL

2.MariaDB跟MySQL在绝大多数方面是兼容的，对于开发者来说，几乎感觉不到任何不同。目前MariaDB是发展最快的MySQL分支版本，新版本发布速度已经超过了Oracle官方的MySQL版本。

3.MariaDB 是一个采用Aria存储引擎的MySQL分支版本， 这个项目的更多的代码都改编于 MySQL 6.0

MariaDB虽然被视为MySQL数据库的替代品，但它在扩展功能、存储引擎以及一些新的功能改进方面都强过MySQL。而且从MySQL迁移到MariaDB也是非常简单的：

1、数据和表定义文件（.frm）是二进制兼容的
2、所有客户端API、协议和结构都是完全一致的
3、所有文件名、二进制、路径、端口等都是一致的
4、所有的MySQL连接器，比如PHP、Perl、Python、Java、.NET、MyODBC、Ruby以及MySQL C connector等在MariaDB中都保持不变
5、mysql-client包在MariaDB服务器中也能够正常运行
6、共享的客户端库与MySQL也是二进制兼容的
1
2
3
4
5
6

安装mariadb 数据库

apt install mariadb-server mariadb-client #安装mariadb 服务端程序
systemctl restart mariadb.service #启动程序
systemctl enable mariadb.service #设为开机自启动
ss -nltp |grep 3306 #mysql 服务默认的端口号是tcp 3306
1
2
3
4

MariaDB 高级应用介绍

（这里只是介绍没有做对应的实验）
Mariadb 高级应用范例如
主从复制
读写分离
数据分片
集群应用
…

数据库分片指：
通过某种特定的条件，将我们存放在一个数据库中的数据分散存放在不同的多个数据库（主机）中，这样来达到分散单台设备的负载，根据切片规则，可分为以下两种切片模式

MariaDB 基础应用

Mariadb 数据库连接方式及初始化
1、连接数据库
Mysql 命令行工具是MySQL 官方提供的连接工具，用户可以通过mysql 连接到mysqld 上进行一系列的SQL 操作。

mysql 工具有两种模式：交互模式和命令行模式。交互模式指令需要连接到mysql 服务器下达，命令行模式通过特定（-e）参数读取shell 命令行传递的指令到服务器。

命令格式：
mysql [OPTIONS] [database]

-h, --help 显示帮助信息
-e, --execute=name 执行指定命令，命令行模式
-u, --user=name 指定连接的用户
-h, --host=name 指定主机名
-p, --password 指定密码
-P, --port 指定端口
1
2
3
4
5
6

连接数据库示例：
（1）使用root 账号连接服务器，无密码登录

mysql -u root
1

（2）使用root 账号连接服务器，使用密码登录，-h 指定登陆主机的IP 或名字，注意未指定-h 选项时，默认登陆本机的数据库

mysql -u root -p'passwd' -h <ip>
1

一些常用的查询命令示例：

select database(); #查看当前进入的数据库
select user(); #查看当前登录用户
show databases; #查看数据库
use mysql #进入mysql 数据库
show tables; #查看此数据库的表，test 没表，可以看其他数据库的
desc user; #查看表结果
show variables like 'innodb%'; #查看环境变量
有时命令输错停在>界面出不来，一般是少些了'或者；补全即可退出，实在不行可以CTRL+D
在重新登录
1
2
3
4
5
6
7
8
9

2、重新初始化mysql
Mariadb 在安装完毕后，就自动初始化好了基础的数据库，目录位于：/var/lib/mysql
Mysql 的配置文件：/etc/mariadb.cnf，为兼容mysql, 原来的配置文件my.cnf 是它的一个软链接

我们也可以通过命令mysql_secure_installation，重新初始化mysql:

mysql_secure_installation
Enter current password for root (enter for none): #输入当前root 的密码，密码为空直接回车
即可
Change the root password? [Y/n] #是否修改root 的密码
Remove anonymous users? [Y/n] #是否删除匿名用户
Disallow root login remotely? [Y/n] #是否不允许root 远程登录
Remove test database and access to it? [Y/n] #是否删除测试数据库test
Reload privilege tables now? [Y/n] #是否重新加载授权信息
1
2
3
4
5
6
7
8

3、重启

systemctl restart mariadb
1

Mariadb 数据库权限管理

1、权限设置中，登陆位置的配置说明

mariadb 的用户登录限制包括用户名和登录位置两部分。早期版本%就可以代表所有的连接，后期版本localhost 表示本地登录，%表示tcp/ip 的远程登录的所有ip。%也可以写具体主机或网段，比如192.168.200.10、192.168.100.%或者192.168.200.0/255.255.255.0，其他格式不识别。

如果登录主机符合多个连接条件，默认连接第一个身份，设置权限的时候需要注意

2、建立新用户、设定密码与登陆位置示例：

在mysql 的命令行状态下：

select user(); #查看当前登录用户
use mysql
select host,user,password from user; #查看数据库所有登录范围、用户和密码，4 个root 不是同一个用户，可以单独设置密码
create user uos@'localhost' identified by '123456'; #建立可以本地登录的用户uos 并设置密码123456
create user uos@'%' identified by '123456'; #建立可以远程登录的用户uos并设置密码123456
create user test@'localhost' identified by '123456'; #建立可以本地登录的用户test并设置密码123456
create user test@'192.168.200.10' identified by '123456'; #建立可以从192.168.200.10 主机登录的用户test 并设置密码123456
grant all on *.* to uos@'localhost'; #授权本地登录的用户uos 对所有数据库的所有表有所有权限
grant select on scott.* to test@'localhost'; #授权本地登录的用户test 可以读取scott 数据库的所有表
grant all on *.* to uostest@'%' identified by '123456'; #可以建立用户、授权、设置密码一起做
flush privileges; #刷新授权表
show privileges; #查看所有权限
show grants for uos@'localhost'; #查看用户权限
show grants for test@'localhost';
revoke select on scott.* from test@'192.168.200.10'; #收回权限

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

3、修改新密码（需输入root 原始密码）

（1）使用mysqladmin 修改

mysqladmin -u root -p password '123456'
1

（2）或进入数据库，在mysql 命令行中修改

mysql -uroot -p
1

set password=password('uos'); #对当前用户设置密码，立刻生效
set password for uos@'localhost'=password('123456'); #对任意用户设置密码
use mysql #使用mysql 这个内置数据库
update user set password=password('uos') where user='root' and host='localhost'; #修改密码后需要flush privileges;刷新权限表，或重启服务
1
2
3
4

4、mariadb 忘记root 密码怎么办？

vim /etc/mysql/mariadb.conf.d/50-server.cnf

[mysqld]
skip-grant-tables #在[mysqld]下添加此字段，进入但用户模式
1
2
3
4

重启服务

systemctl restart mariadb.service
1

输入mysql 命令无需密码直接进入数据库

use mysql
update user set password=password('uos') where user='root';
1
2

从5.5.7 版本中mysql 数据库中就开始引入plugin 这项配置，用来进行用户密码验证

update user set plugin='' where user='root';
1

停止mariadb 服务，删除配置文件的skip-grant-tables 选项，重启mariadb 服务，使用新密码进入

数据库字符集修改

查看Linux 的字符集用locale 命令
注：客户端的字符集要和数据库的字符集一致，不一致有可能乱码

进入mysql 命令行，status 查看mariadb 属性

status
1

Server characterset:utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
设置mariadb server 的默认字符集：
1
2
3
4
5

设置mariadb server 的默认字符集：

vim /etc/mysql/mariadb.conf.d/50-server.cnf

character-set-server = utf8
#collation-server = utf8
1
2

重启mariadb

systemctl restart mariadb.service
1

再验证字符集
mysql -uroot -p

MariaDB [(none)]> status #重启后变更为新字符集
Server characterset:utf8
1
2

查看数据库的字符集

create database scott;
drop database scott; #删除数据库
source /scott.sql #使用脚本重建数据库
show create database scott; #在更改字符集之后建立，所以是字符集是utf8
show create table emp;
1
2
3
4
5

更改scott 数据库和表的字符集

alter database scott charset utf8;
alter table emp charset utf8;
1
2

数据库DDL，DML 和DCL 语言操作

SQL 语言可以理解成向服务端提问的语言，SQL 语言按照功能可以分为几种子语言：

• 数据定义语言（DDL）：创建和管理数据库，包括创建修改表，定义索引，管理约束条 件。DDL 操作对象为数据库内部的对象。
• 数据操纵语言（DML）：查询和更新数据库中的数据,用于添加，删除，更新，查询。 DML的操作对象是表的内部数据。而不会涉及到表的定义，结构的修改。
• 数据控制语言（DCL）：对用户的权限控制

「1」建立scott 数据库快速建立脚本
SCOTT 是数据库内部的一个示例用户，缺省口令为tiger，下面有表emp, dept 等，这些表和表间的关系演示了关系型数据库的一些基本原理
vim /scott.sql
这里省略了表格。

调用sql 脚本
1

mysql -uroot -p

system ls -l /root
source /scott.sql
1
2
3
4

「2」查数据

select * from emp;
select ename,sal from emp;
select ename,(sal+200)*3 as bonus from emp; #支持算数表达式加减乘数和括号等
select ename,sal,comm,sal+ifnull(comm,0) as income from emp; #空值参与算术运算为空值，建议用ifnull 函数转换
select distinct deptno from emp; #用distinct 去除重复结果
select ename,sal,deptno from emp where deptno=30;
select * from emp where ename like '___TT'; #_代表任意单个字符
select * from emp where ename like '%LL%'; #%代表任意字符
select ename,sal,deptno from emp where deptno=30 and sal>2000; #与
select ename,sal,deptno from emp where deptno=30 or sal>2000; #或
select ename,sal,deptno from emp where not sal>2000; #非
select * from emp order by sal; #默认升序，降序加desc
select empno,ename,sal,deptno from emp order by deptno,sal desc; #多列排序
select concat(ename,'\'s sal is ',sal) from emp; #支持多种函数，不一一举了
select count(*) from emp;
select deptno,sum(sal),min(sal),max(sal),avg(sal) from emp group by deptno;
select deptno,avg(sal) from emp group by deptno having avg(sal)<2000;
select ename,dname from emp,dept; #笛卡尔乘积
select ename,dname from emp,dept where emp.deptno=dept.deptno;
select ename,sal,grade from emp join salgrade on sal between losal and hisal;
select y.ename yuangong,j.ename jingli from emp y,emp j where y.mgr=j.empno;
select ename,dname,sal,grade from emp,dept,salgrade where emp.deptno=dept.deptno and emp.sal
between salgrade.losal and salgrade.hisal;
select ename,sal from emp where sal=(select max(sal) from emp); #子查询找到工资最高薪
select ename from emp where empno not in (select mgr from emp where mgr is not null); #in 结果集不能有空值，否则结果为空

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

「3」创建表
创建数据库

MariaDB [(none)]> create database uosdata;
1

创建表并建立字段
格式：create table 数据表名(字段名字段类型);

MariaDB [uos]> create table uostable(id int,name varchar(10),mail varchar(30));
1

int 类型表示正常大小的整数（数字数据类型）。
char 类型（字符串数据类型）表示包含指定长度的空格的右侧带有固定长度的字符串。M 表示字符的列长度，取值范围为0〜255，缺省值为1。
varchar 类型（字符串数据类型）表示一个可变长度字符串，M 范围（最大列长度）为0
到65535。
1
2
3
4

「4」修改表

create table uos1 like uostable; #没有数据，只是复制了表的结构
insert into uos1 select * from uostable; #复制数据，表不存在无法复制
create table uos2 as select * from uostable; #新建表，并复制整个表结构+数据
1
2
3

查看表结构
格式：desc 数据表名;

MariaDB [uos]> desc uos;
1

向数据表插入数据
格式：insert into 数据表名(id,name,mail) values(1,‘uos1’,‘uos1@uos.com’);

insert into uostable(id,name,mail) values(1,'uos1','uos1@uos.com');
insert into uostable values(2,'uos2','uos2@uos.com'); #加入全列数据可以不写列名
insert into uostable(id,name) values(3,'uos3'); #不是全列必须写列名
insert into uostable values(4,'uos4','uos4@uos.com'),(5,'uos5','uos5@uos.com'),(6,'uos6','uos6@uos.com');

1
2
3
4
5

更新表

update uostable set name='test' where id=2;
1

修改表结构

alter table uostable add newlist varchar(20); #增加newlist 列
alter table uostable drop newlist; #删除newlist 列
alter table uostable add firstlist varchar(20) first; #增加firstlist 列到第一列
alter table uostable add afterid varchar(30) after id; #增加afterid 列到id 列后面
1
2
3
4

「5」删除表

delete from uostable where id=4; #要加上where 约束，没有where 删除整个表
delete from uos1; #DML 操作，清除表数据，保留表结果
truncate uos2; #DDL 操作，清除表数据，保留表结果，更彻底，降低高水位线
drop table uos1; #清除表结构和数据
1
2
3
4

「6」外部表导入导出
建立测试用外部表（即以逗号分隔各列的CSV 格式）
vim /uos.txt

1,uos1,uos1@uos.com
2,uos2,uos2@uos.com
3,uos3,uos3@uos.com
4,uos4,uos4@uos.com
1
2
3
4

将外部表导入数据库

create database uosdatabase;
use uosdatabase
create table uostable(id int(4),name varchar(10),email varchar(20));
load data infile '/uos.txt' into table uostable fields terminated by ',' lines terminated by '\n';
1
2
3
4

将数据库导出为外部表

select * from uostable into outfile '/var/lib/mysql/uosdatabase/uostable.txt' fields terminated by ','
lines terminated by '\n';
1
2

通过外部表导入scott 数据库
准备数据：分别建立scott 数据库的3 个CSV 表文件
vim /scott.emp.txt

7369,'SMITH','CLERK',7902,800,200,20
7499,'ALLEN','SALES',7698,1600,300,30
7521,'WARD','SALES',7698,1250,500,30
7566,'JONES','MANAG',7839,2975,100,20
1
2
3
4

vim /scott.dept.txt

10,'ACCOUNTING','NEW YORK'
20,'RESEARCH','DALLAS'
30,'SALES','CHICAGO'
40,'OPERATIONS','BOSTON'
1
2
3
4

vim /scott.salgrade.txt

1,700,1200
2,1201,1400
3,1401,2000
1
2
3

建立scott 库和三个表，并导入数据

create database scott;
use scott
create table emp(empno int(4),ename varchar(10),job varchar(9),mgr int(4) default null,sal
int(7),comm int(7) default null,deptno int(2));
load data infile '/scott.emp.txt' into table emp fields terminated by ',' lines terminated by '\n';
create table dept(deptno int(2),dname varchar(14),loc varchar(13));
load data infile '/scott.dept.txt' into table dept fields terminated by ',' lines terminated by '\n';
create table salgrade(grade int,losal int,hisal int);
load data infile '/scott.salgrade.txt' into table salgrade fields terminated by ',' lines terminated by'\n';
1
2
3
4
5
6
7
8
9

将scott 数据库导出为外部表

use scott
select * from emp into outfile '/var/lib/mysql/scott/scott.emp.txt' fields terminated by ',' lines
terminated by '\n';
1
2
3

数据库管理程序mysqladmin 详解

mysqladmin 是一个执行mysqld 管理操作的客户端程序。它可以用来检查服务器的配置和当前状态、创建和删除数据库等。

因此使用mysqladmin 前就确保所连接的mysqld 服务端进程正常运行和连接

mysqladmin 工具的使用格式：

mysqladmin [option] command [command option] command ......
1

option 选项：

-c number 自动运行次数统计，必须和-i 一起使用
-i number 间隔多长时间重复执行
示例：每隔两秒查看一次服务器的状态，总共重复5 次。
mysqladmin -uroot -p -i 2 -c 5 status
-h, --host=name Connect to host. 连接的主机名或iP
-p, --password[=name] 登录密码，如果不写于参数后，则会提示输入
-P, --port=# Port number to use for connection. 指定数据库端口
-s, --silent Silently exit if one can't connect to server.
-S, --socket=name Socket file to use for connection. 指定socket file
-i, --sleep=# Execute commands again and again with a sleep between. 间隔一段时间
执行一次
-u, --user=name User for login if not current user.登录数据库用户名
-v, --verbose Write more information. 写更多的信息
-V, --version Output version information and exit. 显示版本
1
2
3
4
5
6
7
8
9
10
11
12
13
14

mysqladmin 的相关命令示例：

mysqladmin password uos123 #<==设置密码，前文用过的。
mysqladmin -uroot -puos123 password uos #<==修改密码，前文用过的。
mysqladmin -uroot -puos123 status #<==查看状态，相当于show status。
mysqladmin -uroot -puos123 -i 1 status #<==每秒查看一次状态。
mysqladmin -uroot -puos123 extended-status #<==等同show global status;。
mysqladmin -uroot -puos123 flush-logs #<==切割日志。
mysqladmin -uroot -puos123 processlist #<==查看执行的SQL 语句信息。
mysqladmin -uroot -puos123 processlist -i 1 #<==每秒查看一次执行的SQL 语句。
mysqladmin -uroot -p'uos' shutdown #<==关闭mysql 服务，前文用过的。
mysqladmin -uroot -p'uos' variables #<==相当于show variables。
1
2
3
4
5
6
7
8
9
10

Mariadb 数据库备份与还原

在linux 环境备份数据库

mysqldump -u root -p scott > /scott.dump #备份数据库
mysqldump -u root -p scott emp > /scott.emp.dump #备份数据库中的表
mysqldump -u root -p scott dept salgrade > /scott.dept+salgrade.dump #备份数据库中的多个表
1
2
3

在linux 环境还原数据库

>drop database scott;
#如果报错，删除/var/lib/mysql/scott/scott.emp.txt 文件
>create database uos; #不建立数据库，无法导入整库,随意命名
mysql -u root -p uos < /scott.dump #还原数据库
>drop database uos;
>create database uos; #重建数据库用于测试还原表
mysql -u root -p uos < /scott.emp.dump #还原数据库中的表
mysql -u root -p uos < /scott.dept+salgrade.dump #还原数据库中的多个表
1
2
3
4
5
6
7
8

Mariadb 数据库索引

什么是索引
索引是一种特殊的文件(InnoDB 数据表上的索引是表空间的一个组成部分)，它们包含着对数
据表里所有记录的引用指针。
更通俗的说，数据库索引好比是一本书前面的目录，在查找内容之前可以先在目录中查找索引位置，以此快速定位查询数据。
对于索引，会保存在额外的文件中。

索引的类型

1、普通索引
2、唯一性索引
3、主键索引（主索引）
4、复合索引
5、全文索引

1
2
3
4
5
6

创建普通索引
创建表时添加索引
语法：

create table 表名（
列定义
index 索引名称（字段）
index 索引名称（字段）
）
注：可
1
2
3
4
5
6

以使用key 关键字，也可以使用index 关键字。索引名称，可以加也可以不加，不加
使用字段名作为索引名。

MariaDB [book]> desc books;
1

示例：

MariaDB [book]> create table demo(id int(4),name varchar(20),pwd varchar(20),index(pwd));
Query OK, 0 rows affected (0.08 sec)
MariaDB [book]> create table demo1(id int(4),name varchar(20),pwd varchar(20),key(pwd));
Query OK, 0 rows affected (0.01 sec)
MariaDB [book]> create table demo2(id int(4),name varchar(20),pwd varchar(20),key
index_pwd(pwd)); # 为索引加上名称
Query OK, 0 rows affected (0.02 sec)
1
2
3
4
5
6
7