1 mariadb

1.1 简介

1、介绍

MariaDB是MySQL关系数据库管理系统的一个分支，由社区开发，有商业支持，旨在继续保持在GNU GPL下开源。MariaDB的开发是由MySQL的一些原始开发者领导的，他们担心甲骨文公司收购MySQL后会有一些隐患。
MariaDB打算保持与MySQL的高度兼容性，与MySQL API和命令精确匹配。MariaDB自带了一个新的存储引擎Aria，它可以替代MyISAM，成为默认的事务和非事务引擎。它最初使用XtraDB作为默认存储引擎， 并从10.2版本切换回InnoDB。
MariaDB 是一个快速、可扩展、性能一流的数据库，支持更多存储引擎。它还维护许多插件、工具和实用程序，使其成为最好的数据库之一。 MariaDB 主要用于维护库存、管理交易和客户信息。

2、版本说明

MariaDB直到5.5版本，均依照MySQL的版本。因此，使用MariaDB5.5的人会从MySQL 5.5中了解到MariaDB的所有功能。
从2012年11月12日起发布的10.0.0版开始，不再依照MySQL的版号。10.0.x版以5.5版为基础，加上移植自MySQL 5.6版的功能和自行开发的新功能。

3、支持mariadb的客户端

MariaDB的API和协议兼容MySQL，另外又添加了一些功能，这意味着，所有使用MySQL的连接器、程序库和应用程序也将可以在MariaDB下工作。在此基础上，由于担心甲骨文MySQL不再开源，Fedora等Linux发行版已经在最新版本中以MariaDB取代MySQL维基媒体基金会的服务器同样也使用MariaDB取代了MySQL。
以下是支持mariadb的客户端：

• DBEdit：一个免费的MariaDB数据库和其他数据库管理应用程序。
• Navicat：一系列Windows、Mac OS X、Linux下专有数据库管理应用程序。
• HeidiSQL：一个Windows上自由和开放源码的MySQL客户端。它支持MariaDB的5.2.7版本和以后的版本。
• phpMyAdmin：一个基于网络的MySQL数据库管理应用程序

1.2 在Ubuntu 22.04安装配置mariadb – 使用apt

1、安装

# 更新系统
sudo apt update && sudo apt upgrade

# 安装mariadb的依赖包
sudo apt-get install wget software-properties-common dirmngr ca-certificates apt-transport-https -y

# 安装mariadb
sudo apt install mariadb-server mariadb-client -y

# 查看是否安装成功
# 查看数据库的版本，两个命令都可以
mariadb -V
mariadb --version

# 检查MariaDB的状态
systemctl status mariadb.service

2、保护数据库 - 做一些基本设置

# 保护数据库
# 在 MariaDB 中执行一些基本配置
root@sftp:~# mysql_secure_installation 

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.
# 输入当前用户的密码：回车键即可
Enter current password for root (enter for none): 
OK, successfully used password, moving on...

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

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n] y
Enabled successfully!
Reloading privilege tables..
 ... Success!


You already have your root account protected, so you can safely answer 'n'.
# 修改root用户密码
Change the 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.
# 是否允许root用户远程登录
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.
# 是否删除test数据库
Remove test database and access to it? [Y/n] n
 ... skipping.

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@sftp:~# 

# 再次查看是否正确启动
systemctl status mariadb

3、在Linux上使用

mariadb
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 40
Server version: 10.6.16-MariaDB-0ubuntu0.22.04.1 Ubuntu 22.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
# 创建admin 用户，该帐户将具有与 root 帐户相同的权限，但主要是用它进行基于密码的身份验证。
MariaDB [(none)]> GRANT ALL ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'admin123' WITH GRANT OPTION;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.000 sec)

# 查看用户
MariaDB [(none)]> select Host,User,Password from mysql.user;
+-----------+-------------+-------------------------------------------+
| Host      | User        | Password                                  |
+-----------+-------------+-------------------------------------------+
| localhost | mariadb.sys |                                           |
| localhost | root        | *73DD6548518E0C6AE598554B04A458A70F7FD616 |
| localhost | mysql       | invalid                                   |
| localhost | admin       | *73DD6548518E0C6AE598554B04A458A70F7FD616 |
+-----------+-------------+-------------------------------------------+
4 rows in set (0.002 sec)

# 创建数据库
MariaDB [(none)]> create database testDb;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testDb             |
+--------------------+
5 rows in set (0.000 sec)

1.3 卸载mariadb

systemctl stop mysql
apt remove --purge mariadb\*
apt autoremove
apt autoclean