当前位置：Gxlcms > 数据库问题 > Centos7 使用yum安装MariaDB与MariaDB的简单配置与使用

Centos7 使用yum安装MariaDB与MariaDB的简单配置与使用

时间：2021-07-01 10:21:17 帮助过：12人阅读

.mariadb的安装

MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可。

开发这个分支的原因之一是：甲骨文公司收购了MySQL后，有将MySQL闭源的潜在风险，因此社区采用分支的方式来避开这个风险。

MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。

方法1:阿里源下yum安装mariadb版本会旧一点

Red Hat Enterprise Linux/CentOS 7.0 发行版已将默认的数据库从 MySQL 切换到MariaDB。

<span style="color: #000000;">安装命令
# </span><span style="color: #0000ff;">yum</span> -y <span style="color: #0000ff;">install</span> mariadb mariadb-<span style="color: #000000;">server
安装完成MariaDB，首先启动MariaDB，两条命令都可以
 
systemctl start mariadb
#centos6命令
service mariadb start</span>

方法2：yum安装mariadbrepo仓库配置安装

# 如果已经添加了阿里云的源又想安装最新版本的mariadb,那么少就使用以下步骤

<span style="color: #000000;"># 编辑创建mariadb.repo仓库文件
</span><span style="color: #0000ff;">vi</span> /etc/<span style="color: #0000ff;">yum</span>.repos.d/<span style="color: #000000;">MariaDB.repo
 
 
添加repo仓库配置
[mariadb]
name</span>=<span style="color: #000000;">MariaDB
baseurl</span>=http:<span style="color: #008000;">//</span><span style="color: #008000;">yum.mariadb.org/10.1/centos7-amd64</span>
<span style="color: #000000;">
gpgkey</span>=https:<span style="color: #008000;">//</span><span style="color: #008000;">yum.mariadb.org/RPM-GPG-KEY-MariaDB</span>
<span style="color: #000000;">
gpgcheck</span>=<span style="color: #800080;">1<br></span>

当 MariaDB 仓库地址添加好后，你可以通过下面的一行命令轻松安装 MariaDB。

<span style="color: #0000ff;">yum</span> <span style="color: #0000ff;">install</span> MariaDB-server MariaDB-client -y

二.MariaDB基本配置

1.开启关闭与查看mariadb命令

mariadb数据库的相关命令是：

<span style="color: #000000;">systemctl start mariadb  #启动MariaDB
systemctl stop mariadb  #停止MariaDB
systemctl restart mariadb  #重启MariaDB
systemctl enable mariadb  #设置开机启动</span>

# 查看mariadb进程

[root@node home]# netstat -ntlp |<span style="color: #0000ff;">grep</span> <span style="color: #800080;">3306</span><span style="color: #000000;">
tcp      </span><span style="color: #800080;">0</span>     <span style="color: #800080;">0</span> <span style="color: #800080;">0.0</span>.<span style="color: #800080;">0.0</span>:<span style="color: #800080;">3306</span>      <span style="color: #800080;">0.0</span>.<span style="color: #800080;">0.0</span>:*         LISTEN      <span style="color: #800080;">3931</span>/mysqld

2.初始化MariaDB 数据库

在确认 MariaDB 数据库软件程序安装完毕并成功启动后请不要立即使用。为了确保数据库的安全性和正常运转，需要先对数据库程序进行初始化操作。这个初始化操作涉及下面 5 个步骤。

设置 root 管理员在数据库中的密码值(注意，该密码并非 root 管理员在系统中的密码，这里的密码值默认应该为空，可直接按回车键)。

设置 root 管理员在数据库中的专有密码。

随后删除匿名账户，并使用 root 管理员从远程登录数据库，以确保数据库上运行的业

务的安全性。

删除默认的测试数据库，取消测试数据库的一系列访问权限。

刷新授权列表，让初始化的设定立即生效。

<span style="color: #000000;">初始化命令:
mysql_secure_installation
 
首先是设置密码，会提示先输入密码
Enter current password </span><span style="color: #0000ff;">for</span> root (enter <span style="color: #0000ff;">for</span> none):<<span style="color: #000000;">–初次运行直接回车
设置密码
Set root password</span>? [Y/n]  y<<span style="color: #000000;">– 是否设置root用户密码，输入y并回车或直接回车
New password: </span><<span style="color: #000000;">– 设置root用户的密码
Re</span>-enter new password: <<span style="color: #000000;">– 再输入一次你设置的密码
 
其他配置
Remove anonymous users</span>? [Y/n]  y<<span style="color: #000000;">– 是否删除匿名用户，回车
Disallow root </span><span style="color: #0000ff;">login</span> remotely? [Y/n] n<<span style="color: #000000;">–是否禁止root远程登录,回车,不过一般为y
Remove test database and access to it</span>? [Y/n] y<<span style="color: #000000;">– 是否删除test数据库，回车
Reload privilege tables now</span>? [Y/n] y<<span style="color: #000000;">– 是否重新加载权限表，回车
初始化MariaDB完成，接下来测试登录
 
# mysql </span>-uroot -p    进入数据库(数据库中的操作命令和mysql是一样的)

3.中文编码设置，utf8编码

文件/etc/<span style="color: #000000;">my.cnf
</span><span style="color: #0000ff;">vi</span> /etc/<span style="color: #000000;">my.cnf
 
在[mysqld]标签下添加
init_connect</span>=<span style="color: #800000;">‘</span><span style="color: #800000;">SET collation_connection = utf8_unicode_ci</span><span style="color: #800000;">‘</span><span style="color: #000000;">
init_connect</span>=<span style="color: #800000;">‘</span><span style="color: #800000;">SET NAMES utf8</span><span style="color: #800000;">‘</span><span style="color: #000000;">
character</span>-set-server=<span style="color: #000000;">utf8
collation</span>-server=<span style="color: #000000;">utf8_unicode_ci
skip</span>-character-set-client-<span style="color: #000000;">handshake
进行查看就支持utf8了</span>

三.mariadb的使用

关于mariadb的使用其实是和mysql的语句完全是一样的

1.开启关闭和设置开机启动

mariadb数据库的相关命令是：

<span style="color: #000000;">systemctl start mariadb  #启动MariaDB
systemctl stop mariadb  #停止MariaDB
systemctl restart mariadb  #重启MariaDB
systemctl enable mariadb  #设置开机启动</span>

2.设置密码建库建表

<span style="color: #000000;"># 修改mysql密码
MariaDB [(none)]</span>>  set password = PASSWORD(<span style="color: #800000;">‘</span><span style="color: #800000;">hsz123</span><span style="color: #800000;">‘</span><span style="color: #000000;">);
Query OK, </span><span style="color: #800080;">0</span> rows affected (<span style="color: #800080;">0.34</span><span style="color: #000000;"> sec)
# 创建tests数据库 如果加charset</span>=<span style="color: #000000;">utf8  表示指定utf8编码
MariaDB [(none)]</span>><span style="color: #000000;"> create database test;
Query OK, </span><span style="color: #800080;">1</span> row affected (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
# 进入test数据库
MariaDB [(none)]</span>><span style="color: #000000;"> use test;
Database changed
# 创建mytest数据表
MariaDB [test]</span>> create table mytest(<span style="color: #0000ff;">id</span> <span style="color: #0000ff;">int</span>,name <span style="color: #0000ff;">char</span>(<span style="color: #800080;">32</span><span style="color: #000000;">));
Query OK, </span><span style="color: #800080;">0</span> rows affected (<span style="color: #800080;">0.02</span><span style="color: #000000;"> sec)
# 查看数据表
MariaDB [test]</span>><span style="color: #000000;"> show tables;
</span>+----------------+
| Tables_in_test |
+----------------+
| mytest         |
+----------------+
<span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
# 查看mytest数据表的表结构
MariaDB [test]</span>><span style="color: #000000;"> desc mytest;
</span>+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| <span style="color: #0000ff;">id</span>    | <span style="color: #0000ff;">int</span>(<span style="color: #800080;">11</span>)  | YES  |     | NULL    |       |
| name  | <span style="color: #0000ff;">char</span>(<span style="color: #800080;">32</span>) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
<span style="color: #800080;">2</span> rows <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.05</span> sec)

2.简单的增删改查

<span style="color: #000000;"># 给表增加两条数据
MariaDB [test]</span>> insert into mytest(<span style="color: #0000ff;">id</span>,name) values(<span style="color: #800080;">1</span>,<span style="color: #800000;">"</span><span style="color: #800000;">zero</span><span style="color: #800000;">"</span>),(<span style="color: #800080;">2</span>,<span style="color: #800000;">"</span><span style="color: #800000;">one</span><span style="color: #800000;">"</span><span style="color: #000000;">);
Query OK, </span><span style="color: #800080;">2</span> rows affected (<span style="color: #800080;">0.35</span><span style="color: #000000;"> sec)
Records: </span><span style="color: #800080;">2</span>  Duplicates: <span style="color: #800080;">0</span>  Warnings: <span style="color: #800080;">0</span><span style="color: #000000;">
# 查看id,name 字段mytest的数据
MariaDB [test]</span>> <span style="color: #0000ff;">select</span> <span style="color: #0000ff;">id</span><span style="color: #000000;">,name from mytest;
</span>+------+------+
| <span style="color: #0000ff;">id</span>   | name |
+------+------+
|    <span style="color: #800080;">1</span> | zero |
|    <span style="color: #800080;">2</span> | one  |
+------+------+
<span style="color: #800080;">2</span> rows <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
# 删除mytest表中id</span>=<span style="color: #800080;">3</span><span style="color: #000000;"> 的数据
MariaDB [test]</span>> delete from mytest <span style="color: #0000ff;">id</span>=<span style="color: #800080;">2</span><span style="color: #000000;">;
ERROR </span><span style="color: #800080;">1064</span> (<span style="color: #800080;">42000</span>): You have an error <span style="color: #0000ff;">in</span> your SQL syntax; check the manual that corresponds to your MariaDB server version <span style="color: #0000ff;">for</span> the right syntax to use near <span style="color: #800000;">‘</span><span style="color: #800000;">id=2</span><span style="color: #800000;">‘</span> at line <span style="color: #800080;">1</span><span style="color: #000000;">
MariaDB [test]</span>> delete from mytest where <span style="color: #0000ff;">id</span>=<span style="color: #800080;">2</span><span style="color: #000000;">;
Query OK, </span><span style="color: #800080;">1</span> row affected (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
# 查看表的所有数据
MariaDB [test]</span>> <span style="color: #0000ff;">select</span> *<span style="color: #000000;"> from mytest;
</span>+------+------+
| <span style="color: #0000ff;">id</span>   | name |
+------+------+
|    <span style="color: #800080;">1</span> | zero |
+------+------+
<span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.01</span><span style="color: #000000;"> sec)
# 更新表id</span>=1表的字段name=<span style="color: #000000;">ten
MariaDB [test]</span>> update mytest set name=ten where <span style="color: #0000ff;">id</span>=<span style="color: #800080;">1</span><span style="color: #000000;">;
ERROR </span><span style="color: #800080;">1054</span> (42S22): Unknown column <span style="color: #800000;">‘</span><span style="color: #800000;">ten</span><span style="color: #800000;">‘</span> <span style="color: #0000ff;">in</span> <span style="color: #800000;">‘</span><span style="color: #800000;">field list</span><span style="color: #800000;">‘</span><span style="color: #000000;">
MariaDB [test]</span>> update mytest set name=<span style="color: #800000;">"</span><span style="color: #800000;">ten</span><span style="color: #800000;">"</span> where <span style="color: #0000ff;">id</span>=<span style="color: #800080;">1</span><span style="color: #000000;">;
Query OK, </span><span style="color: #800080;">1</span> row affected (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
Rows matched: </span><span style="color: #800080;">1</span>  Changed: <span style="color: #800080;">1</span>  Warnings: <span style="color: #800080;">0</span><span style="color: #000000;">
 
MariaDB [test]</span>> <span style="color: #0000ff;">select</span> *<span style="color: #000000;"> from mytest;
</span>+------+------+
| <span style="color: #0000ff;">id</span>   | name |
+------+------+
|    <span style="color: #800080;">1</span> | ten  |
+------+------+
<span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.01</span> sec)

3.关于用户及权限常用命令

<span style="color: #000000;"># 创建用户和密码
MariaDB [test]</span>> create user zero@<span style="color: #800000;">‘</span><span style="color: #800000;">%</span><span style="color: #800000;">‘</span> identified by <span style="color: #800000;">‘</span><span style="color: #800000;">zero</span><span style="color: #800000;">‘</span><span style="color: #000000;">;
Query OK, </span><span style="color: #800080;">0</span> rows affected (<span style="color: #800080;">0.01</span><span style="color: #000000;"> sec)
 
 
mysql使用grant命令对账户进行授权，grant命令常见格式如下
 
grant 权限 on 数据库.表名 to 账户@主机名            对特定数据库中的特定表授权
grant 权限 on 数据库.</span>*<span style="color: #000000;"> to 账户@主机名            　　对特定数据库中的所有表给与授权
grant 权限1,权限2,权限3 on </span>*.*<span style="color: #000000;"> to 账户@主机名   　　 对所有库中的所有表给与多个授权
grant all privileges on </span>*.*<span style="color: #000000;"> to 账户@主机名   　　 对所有库和所有表授权所有权限
 
#授予用户最大的权限，所有的权限
grant all privileges on </span>*.* to username@<span style="color: #800000;">‘</span><span style="color: #800000;">%</span><span style="color: #800000;">‘</span> identified by <span style="color: #800000;">‘</span><span style="color: #800000;">password</span><span style="color: #800000;">‘</span>;

<span style="color: #000000;">#授予zero用户，只有创建test数据库的权限
MariaDB [test]</span>> grant create on test.* to zero@<span style="color: #800000;">‘</span><span style="color: #800000;">%</span><span style="color: #800000;">‘</span> identified by <span style="color: #800000;">‘</span><span style="color: #800000;">zero</span><span style="color: #800000;">‘</span><span style="color: #000000;">;
Query OK, </span><span style="color: #800080;">0</span> rows affected (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
# 所以查询zero用户的数据库只有如下所示
[root@node </span>~]# mysql -uzero -<span style="color: #000000;">pzero
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection </span><span style="color: #0000ff;">id</span> is <span style="color: #800080;">6</span><span style="color: #000000;">
Server version: </span><span style="color: #800080;">5.5</span>.<span style="color: #800080;">60</span>-<span style="color: #000000;">MariaDB MariaDB Server
 
Copyright (c) </span><span style="color: #800080;">2000</span>, <span style="color: #800080;">2018</span><span style="color: #000000;">, Oracle, MariaDB Corporation Ab and others.
 
Type </span><span style="color: #800000;">‘</span><span style="color: #800000;">help;</span><span style="color: #800000;">‘</span> or <span style="color: #800000;">‘</span><span style="color: #800000;">\h</span><span style="color: #800000;">‘</span> <span style="color: #0000ff;">for</span> help. Type <span style="color: #800000;">‘</span><span style="color: #800000;">\c</span><span style="color: #800000;">‘</span> to <span style="color: #0000ff;">clear</span><span style="color: #000000;"> the current input statement.
 
MariaDB [(none)]</span>><span style="color: #000000;"> show databases;
</span>+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
<span style="color: #800080;">2</span> rows <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
 
MariaDB [(none)]</span>>

<span style="color: #000000;">#授予one创建的权限，对于所有的库表生效
MariaDB [test]</span>> grant create  on *.* to one<span style="color: #800000;">@"</span><span style="color: #800000;">%</span><span style="color: #800000;">"</span>  identified by <span style="color: #800000;">‘</span><span style="color: #800000;">one</span><span style="color: #800000;">‘</span><span style="color: #000000;">;
Query OK, </span><span style="color: #800080;">0</span> rows affected (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
# 所以查询数据库可以显示如下所示
[root@node </span>~]# mysql -uone -<span style="color: #000000;">pone
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection </span><span style="color: #0000ff;">id</span> is <span style="color: #800080;">7</span><span style="color: #000000;">
Server version: </span><span style="color: #800080;">5.5</span>.<span style="color: #800080;">60</span>-<span style="color: #000000;">MariaDB MariaDB Server
 
Copyright (c) </span><span style="color: #800080;">2000</span>, <span style="color: #800080;">2018</span><span style="color: #000000;">, Oracle, MariaDB Corporation Ab and others.
 
Type </span><span style="color: #800000;">‘</span><span style="color: #800000;">help;</span><span style="color: #800000;">‘</span> or <span style="color: #800000;">‘</span><span style="color: #800000;">\h</span><span style="color: #800000;">‘</span> <span style="color: #0000ff;">for</span> help. Type <span style="color: #800000;">‘</span><span style="color: #800000;">\c</span><span style="color: #800000;">‘</span> to <span style="color: #0000ff;">clear</span><span style="color: #000000;"> the current input statement.
 
MariaDB [(none)]</span>><span style="color: #000000;"> show databases;
</span>+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| text               |
+--------------------+
<span style="color: #800080;">5</span> rows <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
 
MariaDB [(none)]</span>><span style="color: #000000;">
# 删除one用户
MariaDB [test]</span>><span style="color: #000000;"> drop user one;
Query OK, </span><span style="color: #800080;">0</span> rows affected (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
# 刷新权限
MariaDB [test]</span>><span style="color: #000000;"> flush privileges;
Query OK, </span><span style="color: #800080;">0</span> rows affected (<span style="color: #800080;">0.00</span> sec)

4.数据库备份与恢复

<span style="color: #000000;"># mysqldump命令用于备份数据库数据
##备份所有数据库命令
[root@node </span>~]# mysqldump -u root -p --all-databases > /tmp/<span style="color: #000000;">db.dump
Enter password:
[root@node </span>~]# ll /tmp/<span style="color: #000000;">db.dump
</span>-rw-r--r--. <span style="color: #800080;">1</span> root root <span style="color: #800080;">515562</span> Sep <span style="color: #800080;">13</span> <span style="color: #800080;">23</span>:<span style="color: #800080;">00</span> /tmp/<span style="color: #000000;">db.dump
##备份单个数据库命令
[root@node </span>~]# mysqldump -u root -p text > /tmp/<span style="color: #000000;">text.sql
Enter password:
[root@node </span>~]# ll /tmp/<span style="color: #000000;">text.sql
</span>-rw-r--r--. <span style="color: #800080;">1</span> root root <span style="color: #800080;">1261</span> Sep <span style="color: #800080;">13</span> <span style="color: #800080;">23</span>:<span style="color: #800080;">01</span> /tmp/<span style="color: #000000;">text.sql
## 将备份的数据库导入
[root@node </span>~]# mysql -uroot -p  text2< /tmp/<span style="color: #000000;">text.sql
 
# 删除数据库
MariaDB [text2]</span>><span style="color: #000000;"> show databases;
</span>+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| text               |
| text2              |
+--------------------+
<span style="color: #800080;">6</span> rows <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
 
MariaDB [text2]</span>><span style="color: #000000;"> drop database text;
Query OK, </span><span style="color: #800080;">0</span> rows affected (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
 
MariaDB [text2]</span>><span style="color: #000000;"> show databases;
</span>+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| text2              |
+--------------------+
<span style="color: #800080;">5</span> rows <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span> sec)

Centos7 使用yum安装MariaDB与MariaDB的简单配置与使用

标签：netstat character 最大 word reload 开源 rest 多个备份数据库

Centos7 使用yum安装MariaDB与MariaDB的简单配置与使用

方法1:阿里源下yum安装mariadb版本会旧一点

方法2：yum安装mariadbrepo仓库配置安装

二.MariaDB基本配置

1.开启关闭与查看mariadb命令

2.初始化MariaDB 数据库

3.中文编码设置，utf8编码

三.mariadb的使用

1.开启关闭和设置开机启动

2.简单的增删改查

3.关于用户及权限常用命令

4.数据库备份与恢复

人气教程排行