如何在 Debian 12/11/10 上安装 MariaDB 10.6

原文链接如何在 Debian 12/11/10 上安装 MariaDB 10.6

MariaDB 是一种流行的开源关系数据库，由 MySQL 开发人员开发。与 MySQL 不同，MariaDB 是完全免费使用的。它的开发在性能和稳定性上做了很多考虑。 MariaDB 还因新存储引擎的健壮性和可扩展性而受到青睐。 MariaDB 10.6 还对 10.5 版本进行了许多改进和一些附加功能。 mariaDB 拥有不同的工具和插件，使其适用范围广泛。它是大多数 Linux 发行版中的默认数据库。

本指南介绍了如何从 MariaDB APT 存储库在 Debian 上逐步安装 MariaDB 10.6。

MariaDB 10.6 是 MariaDB 当前的稳定版本，具有许多改进和新功能，如下所述：

MariaDB 10.6 的新特性

忽略索引 – 这些索引可见并维护，但优化器不使用
支持系统模式——这是一个“系统”数据库，包含用于调查性能问题的视图和过程。
SKIP LOCKED – 锁定的表将被跳过更新或选择。
JSON_TABLE() – 可以创建一个 JSON 表，该表可用作 JSON 文档的子查询。
OFFSET…FETCH…[WITH TIES] –WITH TIES 是一个可选子句，可添加额外的功能。使用的示例
Oracle 兼容性 – 目前正在努力使 MariaDB 与 OracleDB 兼容，并已添加一些 Oracle 语法和功能。

MariaDB 10.6 的改进

MariaDB 10.6 相对于其前身的一些改进包括：

原子 DDL – CREATE、ALTER、DROP 和 RENAME 是原子的并且是安全的。如果 MariaDB 服务器在处理任何这些操作时崩溃，则更改将完全完成或根本不完成。
InnoDB 改进 – 首次插入空表的速度更快。还避免写入临时表。更快的隐式和显式临时表。
加莱拉的改进。能够在两个节点之间启用加密连接而无需停机。还添加了标志来指定是否应启用 galera 有争议的兼容功能。
清理以删除不支持的功能，例如 TukoDB 引擎、Cassandra 引擎、一些 InnoDB 变量和一些 innodb_checksum_algorithm。

在 Debian 12/11/10 上安装 MariaDB 10.6

按照以下步骤在最新的 Debian 服务器上安装 MariaDB 10.6

第1步：系统升级

始终建议在安装之前安装最新的系统软件包以避免依赖性问题。运行以下命令：

sudo apt update && sudo apt upgrade -y

检查更新后是否需要重新启动。

[ -f /var/run/reboot-required ] && sudo reboot -f

第2步：安装所需的软件包

接下来，安装必要的软件包，如下所示：

sudo apt install curl software-properties-common dirmngr -y

步骤 3：添加 MariaDB APT 存储库

分别运行以下命令导入MariaDB签名密钥并添加MariaDB APT存储库

curl -LsS -O https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
sudo bash mariadb_repo_setup --mariadb-server-version=10.6

如果成功，输出应类似于以下内容。

# [info] Checking for script prerequisites.
# [info] MariaDB Server version 10.6 is valid
# [info] Repository file successfully written to /etc/apt/sources.list.d/mariadb.list
# [info] Adding trusted package signing keys...
# [info] Running apt-get update...
# [info] Done adding trusted package signing keys

步骤 4：在 Debian 上安装 MariaDB

接下来，更新软件包并安装 MariaDB 服务器

sudo apt update
sudo apt install mariadb-server mariadb-client

继续安装 MariaDB 软件包及其所有依赖项：

....
The following NEW packages will be installed:
  galera-4 gawk libcgi-fast-perl libcgi-pm-perl libclone-perl libdaxctl1 libdbd-mariadb-perl libdbi-perl libencode-locale-perl libfcgi-bin libfcgi-perl
  libfcgi0ldbl libgdbm-compat4 libhtml-parser-perl libhtml-tagset-perl libhtml-template-perl libhttp-date-perl libhttp-message-perl libio-html-perl
  liblwp-mediatypes-perl libmariadb3 libmpfr6 libndctl6 libperl5.32 libpmem1 libsigsegv2 libterm-readkey-perl libtimedate-perl liburi-perl lsof mariadb-client
  mariadb-client-10.6 mariadb-client-core-10.6 mariadb-common mariadb-server mariadb-server-10.6 mariadb-server-core-10.6 mysql-common perl perl-modules-5.32
  rsync
0 upgraded, 41 newly installed, 0 to remove and 0 not upgraded.
Need to get 37.5 MB of archives.
After this operation, 265 MB of additional disk space will be used.
Do you want to continue? [Y/n] y

第 5 步：启动并启用 MariaDB

现在运行以下命令来启动 MariaDB 并使其在系统重新启动时自动启动

sudo systemctl start mariadb
sudo systemctl enable mariadb

确认 MariaDB 正在运行

$ sudo systemctl status mariadb
● mariadb.service - MariaDB 10.6.12 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor pres>
    Drop-In: /etc/systemd/system/mariadb.service.d
             └─migrated-from-my.cnf-settings.conf
     Active: active (running)  since Tue 2023-04-04 13:36:39 UTC; 21min ago
       Docs: man:mariadbd(8)
             https://mariadb.com/kb/en/library/systemd/

第 6 步：安全的 MariaDB 安装

成功安装 MariaDB 后，请按如下所示继续保护它：

$ sudo mariadb-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'.

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.

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!

第7步：检查MariaDB版本

要检查 MariaDB 版本，我们需要登录 MariaDB，如下所示。如果您设置了 root 密码，请使用 -p。

$ mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 31
Server version: 10.6.12-MariaDB-1:10.6.12+maria~deb11 mariadb.org binary distribution

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

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MariaDB [(none)]>

然后运行以下 MySQL 命令来检查 MariaDB 版本

MariaDB [(none)]> SELECT VERSION();
+---------------------------------------+
| VERSION()                             |
+---------------------------------------+
| 10.6.12-MariaDB-1:10.6.12+maria~deb11 |
+---------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> exit
Bye

第8步：MariaDB基本命令

要开始使用基本命令，我们可以检查创建数据库、用户和表。

MariaDB 创建数据库

要在 MariaDB 中创建数据库，请如上所示登录并运行以下命令。

#Create a new database
MariaDB [(none)]> CREATE DATABASE testdb;

#If the database with the same exists
MariaDB [(none)]> CREATE DATABASE testdb;
ERROR 1007 (HY000): Can't create database 'testdb'; database exists

#Create a database if already exits
MariaDB [(none)]> CREATE OR REPLACE DATABASE testdb;
Query OK, 2 rows affected (0.009 sec)

#First check if a database exists 
MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS testdb;
Query OK, 1 row affected, 1 warning (0.000 sec)

# Check Databases MariaDB
MariaDB [(none)]> SHOW DATABASES;

MariaDB添加用户并授予权限

创建用户并授予权限；

#Create user mariadb
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';

#Grant all privileges to the user
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'localhost' IDENTIFIED BY 'mypassword';

#Grant privileges to a specific database
GRANT ALL PRIVILEGES ON 'DB1'.* TO 'user1'@'localhost';

#Remember to refresh the privileges
FLUSH privileges;

#To check user grants in MariaDB
SHOW GRANTS FOR 'myuser'@'locahost';

创建表并添加数据 MariaDB

创建数据库后，您可以创建表并向其中添加数据

CREATE TABLE employees (id INT, name VARCHAR(20), email VARCHAR(20));
INSERT INTO employees (id,name,email) VALUES(01,"lorna","[email ")

MariaDB 清理

要完全删除 MariaDB，请运行以下命令。

sudo apt purge mariadb-server
sudo rm -rf /var/lib/mysql/

就是这样。您已在 Debian 12/11/10 上成功安装 MariaDB 10.6。这是一个简单直接的安装。我希望该指南对您有所帮助。