一台云服务器Ubuntu篇：3、Apt安装MariaDB

在Oracle 1C1G Ubuntu 24.04上，分别安装了MySQL 8.0.43、MySQL 5.7.44、MariaDB 10.11.13，后发现还是MariaDB 10.11的内存占用最少。
查看内存占用前15的应用：$ ps -eo pid,ppid,%mem,rss,%cpu,cmd --sort=-%mem | head -n 16

安装

sudo apt install mariadb-server # Ubuntu 24.04 是10.11版本
sudo systemctl status mariadb

查看配置文件路径

mysql --help或mariadb --help命令。这些命令会在输出的第一行中显示配置文件的位置。
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf 通常，配置文件按照列出的顺序读取，如果有多个文件包含相同的设置，后面的文件中的设置会覆盖前面的文件中的设置。
grep -Ev '^$|^#' /etc/mysql/my.cnf内容：

[client-server]
# Port or socket location where to connect
# port = 3306
socket = /run/mysqld/mysqld.sock

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

这二个目录下的文件：

$ ls /etc/mysql/conf.d/
mysql.cnf  mysqldump.cnf
$ ls /etc/mysql/mariadb.conf.d/
50-client.cnf         50-mysqld_safe.cnf  60-galera.cnf       provider_lz4.cnf   provider_lzo.cnf
50-mysql-clients.cnf  50-server.cnf       provider_bzip2.cnf  provider_lzma.cnf  provider_snappy.cnf

$ ls /etc/mysql/conf.d/
mysql.cnf  mysqldump.cnf
$ ls /etc/mysql/mariadb.conf.d/
50-client.cnf         50-mysqld_safe.cnf  60-galera.cnf       provider_lz4.cnf   provider_lzo.cnf
50-mysql-clients.cnf  50-server.cnf       provider_bzip2.cnf  provider_lzma.cnf  provider_snappy.cnf

主配置文件是 grep -Ev '^$|^#' /etc/mysql/mariadb.conf.d/50-server.cnf

[mysqld]
pid-file                = /run/mysqld/mysqld.pid
basedir                 = /usr
bind-address            = 127.0.0.1
expire_logs_days        = 10
character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci

初始化

 sudo mysql_secure_installation # 总结下来，就是全按 Y 可以了。

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] n
… skipping.

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

数据库登录与验证

使用mysql -uroot -p或mysql登录数据库

$ mysql -uroot -p{PWD}
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 31
Server version: 10.11.13-MariaDB-0ubuntu0.24.04.1 Ubuntu 24.04

查看数据库目录：SHOW VARIABLES LIKE '%datadir%';
/var/lib/mysql/

远程登录连接数据库

netstat -an | grep 3306 # 如果显示结果中只有 `127.0.0.1:3306`，表示只支持本地访问。
sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf

netstat -an | grep 3306 # 如果显示结果中只有 `127.0.0.1:3306`，表示只支持本地访问。
sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf

bind-address = 0.0.0.0 # 允许所有地址访问，默认的127.0.0.1改为0.0.0.0
port = 12345 # 修改为你需要的端口号，使用默认的3306不需要增加此行

sudo systemctl restart mariadb # 重启数据库，会要求输入OS用户密码

sudo systemctl restart mariadb # 重启数据库，会要求输入OS用户密码

这时，通过ssh本地映射隧道连接，执行 mariadb.exe -hlocalhost -P3306 -uroot -p{PWD}
报错：Host ‘xxxx’ is not allowed to connect to this MariaDB server
是因为在初始化时root被禁用远程登录。

在服务上登录数据库mariadb.exe -uroot -p，且创建可远程的账号：

CREATE USER 'DBA'@'%' IDENTIFIED BY "{PWD}";
GRANT all privileges ON *.* TO 'MariaDBA'@'%';
-- update mysql.user set super_priv='Y' where user='DBA' and host='%';
flush privileges;
select host,user from mysql.user;

CREATE USER 'DBA'@'%' IDENTIFIED BY "{PWD}";
GRANT all privileges ON *.* TO 'MariaDBA'@'%';
-- update mysql.user set super_priv='Y' where user='DBA' and host='%';
flush privileges;
select host,user from mysql.user;

在终端里执行：mariadb.exe -hlocalhost -P3306 -uroot -p{PWD}
报错：ERROR 2026 (HY000): TLS/SSL error: SSL is required, but the server does not support it
表示服务器未启用SSL，可指定不使用SSL连接：mariadb.exe -hlocalhost -P3306 -uroot -p{PWD} --skip-ssl

用户验证方式：Socket与Password切换

Socket 身份验证允许使用 Unix 或 Linux 用户凭证连接到数据库，这对于安全环境非常有用。这简化了登录过程，消除了频繁用户输入密码的要求。

select host,user,plugin from mysql.user;
-- 当前连接登录的用户
select CURRENT_USER();
-- 切换到unix_socket验证
ALTER USER root@localhost IDENTIFIED VIA unix_socket;
-- 切换到db密码验证
ALTER USER root@localhost IDENTIFIED VIA mysql_native_password;
SET PASSWORD = PASSWORD('foo');
-- 刷新
FLUSH PRIVILEGES;

select host,user,plugin from mysql.user;
-- 当前连接登录的用户
select CURRENT_USER();
-- 切换到unix_socket验证
ALTER USER root@localhost IDENTIFIED VIA unix_socket;
-- 切换到db密码验证
ALTER USER root@localhost IDENTIFIED VIA mysql_native_password;
SET PASSWORD = PASSWORD('foo');
-- 刷新
FLUSH PRIVILEGES;

改为unix_socket后

l@v:/www$ mysql -uroot
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

但是 sudo mysql 可以使用root@localhost登录成功。

卸载

sudo systemctl stop mariadb
sudo apt remove --purge mariadb-server
sudo apt autoremove --purge