安装MariaDB（适用于Ubuntu 18.04 / 20.04 / 22.04 LTS和树莓派操作系统）

2 年 ago
文, 翔
9 minutes

为了建立这样的服务器，安装MariaDB。

    • OS: Ubuntu 18.04 LTS / Ubuntu 20.04 LTS / Ubuntu 22.04 LTS

および Raspberry Pi OS Lite Release date: April 4th 2022.04

同じマシンに DB サーバとWebアプリサーバをインストール、
Webアプリからは DB へ localhost で接続
DB には日本語テーブル名、日本語カラム名を用いる。

简单的安装步骤。
（2020/06/10：确认在Ubuntu20.04LTS上同样有效，并更改标题和开头）
（2020/12/18：添加关于重新安装的内容）
（2021/03/25：针对在Ubuntu20.04LTS上使用CREATE TABLE时出现错误进行了修改和补充）
（2022/06/06：确认在Ubuntu22.04LTS上同样有效，并更改标题和开头）
（2022/08/03：确认在Raspberry Pi OS Lite发布日期为2022年4月4日的情况下同样有效，并更改标题等）
（2022/08/10：添加关于时区确认的内容）

安装

直接使用Ubuntu / Raspberry Pi OS官方软件仓库中的内容。


$ sudo apt-get install mariadb-server mariadb-client

版本确认


$ sudo mysql -v
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 42
Server version: 10.1.44-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

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

Reading history-file /home/nanbuwks/.mysql_history
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> exit;
Writing history-file /home/nanbuwks/.mysql_history
Bye

顺便提一下，各自在不同的日子去验证版本的环境如下所示。

    • Ubuntu20.04 (2020/06/10)

MariaDB Sever version は 10.3.22-MariaDB-1ubuntu1 Ubuntu 20.04

Ubuntu22.04 (2022/06/06)

MariaDB Sever version は 10.6.7-MariaDB-2ubuntu1 Ubuntu 22.04

Raspberry Pi OS Lite Release date: April 4th 2022.04 (2022/08/02)

10.5.15-MariaDB-0+deb11u1 Raspbian 11

几乎是第一次使用Mariadb，但是我没想到可以通过mysql在CLI中进行调用。

文字编码设置

将/etc/mysql/mariadb.conf.d/50-server.cnf 更改如下：


#
# * Character sets
#
# MySQL/MariaDB default is Latin1, but in Debian we rather default to the full
# utf8 4-byte character set. See also client.cnf
#
character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci

以下是用中文本地化的句子转述，仅提供一种选项：
向下


#
# * Character sets
#
# MySQL/MariaDB default is Latin1, but in Debian we rather default to the full
# utf8 4-byte character set. See also client.cnf
#
character-set-server  = utf8mb4
collation-server      = utf8mb4_bin

不区分大小写，设置为包含4个字节的UTF8编码。

在包含4个字节的UTF8代码中进行设置和比较，区分大小写。

根据 @lowln 于 2022年1月8日的指正，进行修正。


 sudo systemctl restart mariadb

在中文中以一种方式进行重述：

在中文中进行反映。

设定

对于Ubuntu而言，如果你想要首先使用普通用户进行设置的话…


$ 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
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
Aborting!

Cleaning up...

在树莓派操作系统中，尽管没有终止，但出现了访问被拒绝的情况。


$ sudo mysql_secure_installation

我使用sudo运行成功。
下面是Ubuntu的情况。



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
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

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

Set root password? [Y/n] Y
New password: 
Re-enter new password: 
Sorry, passwords do not match.

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!

禁止远程根登录？[是/否] 是

如果是树莓派操作系统的情况下：



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!

创建用于Web应用程序的用户和测试数据库。


$ sudo mysql -u root -p

我尝试创建一个测试数据库。为了试试，在数据库名上也用了日语。

MariaDB [(none)]> create database テストデータベース;
Query OK, 1 row affected (0.00 sec)

创建用于从Web应用程序进行连接的用户

MariaDB [(none)]>  CREATE USER 'webdb'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

（”密码” 应适时更改）

設定權限

MariaDB [(none)]> GRANT ALL ON テストデータベース.* TO 'webdb'@'localhost';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> exit;
Bye

尝试连接


$ mysql -u webdb -h localhost -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 44
Server version: 10.1.44-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.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.

MariaDB [(none)]> use テストデータベース
Database changed
MariaDB [テストデータベース]> show tables;
Empty set (0.00 sec)

顺利进行

如果要从任何网络访问(root权限下)创建另一个webdb用户。


MariaDB [(none)]> CREATE USER 'webdb'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT ALL ON テストデータベース.* TO webdb@'%';
Query OK, 0 rows affected (0.00 sec)

请确认用户设置

用户可以设置主机吗？


MariaDB [(none)]> select user, host from mysql.user;
+----------+-----------+
| user     | host      |
+----------+-----------+
| webdb    | %         |
| root     | localhost |
| testUser | localhost |
| webdb    | localhost |
+----------+-----------+
4 rows in set (0.00 sec)

数据库权限是否已经分配了？

MariaDB [(none)]> show grants for 'webdb'@'localhost'
+--------------------------------------------------------------------------------------------------------------+
| Grants for webdb@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'webdb'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
| GRANT ALL PRIVILEGES ON `テストデータベース`.* TO 'webdb'@'localhost'                                        |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> show grants for `webdb`@'%';
+------------------------------------------------------------------------------------------------------+
| Grants for webdb@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'webdb'@'%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
| GRANT ALL PRIVILEGES ON `テストデータベース`.* TO 'webdb'@'%'                                        |
+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

允许外部连接的设置


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

作为


・・・ 
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1
・・・

↓改变


・・・ 
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1
bind-address            = 0.0.0.0
・・・

重新启动MariaDB


$ sudo service mariadb restart

没问题。

不需要使用sudo，可以以root用户访问。

如果不得不进行此设置，可以按照以下方式进行。

首先，对当前设置进行确认。

> SHOW GRANTS FOR root@localhost;
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                                        |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA unix_socket USING '*45BD13A4098870D220D0D524550A3E6F3E698C5A' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                                                    |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

修改设定

> grant all privileges on *.* to root@localhost identified by 'password' with grant option;
Query OK, 0 rows affected (0.022 sec)
> flush privileges;
Query OK, 0 rows affected (0.093 sec)

修改后的设置是这样的。

> SHOW GRANTS FOR root@localhost;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

创建一个测试表并进行操作确认。

创建名为「sensor」的表。
为了测试，我也创建了一个名为「センサ」的表，看看是否支持日语。

create table if not exists sensor (
id SERIAL PRIMARY KEY AUTO_INCREMENT NOT NULL,
sensor integer,
 temp real,
 hemi real,
 memo text
);

create table if not exists センサ (
id SERIAL PRIMARY KEY AUTO_INCREMENT NOT NULL,
センサ integer,
 温度 real,
 湿度 real,
 メモ text
);

（2021年03月25日記、2022年08月03日修正：）
如果您使用Ubuntu 20.04（MariaDB版本为10.3.25-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04）或更高版本，以上错误将会发生。


MariaDB [テストデータベース]> create table if not exists センサ ( id SERIAL PRIMARY KEY AUTO_INCREMENT NOT NULL, センサ integer,  温度 real,  湿度 real,  メモ text );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AUTO_INCREMENT NOT NULL, センサ integer,  温度 real,  湿度 real,  メ...' at line 1
MariaDB [テストデータベース]> 



id SERIAL PRIMARY KEY AUTO_INCREMENT NOT NULL,

听说如果同时指定 SERIAL 类型和 AUTO_INCREMENT，会导致错误。

「MySQL :: MySQL 8.0 参考手册 :: 11.1.1 数值数据类型语法」
https://dev.mysql.com/doc/refman/8.0/zh-CN/numeric-type-syntax.html

根据报道，

SERIAL 是 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE 的别名。
在整数列的定义中，SERIAL DEFAULT VALUE 是 NOT NULL AUTO_INCREMENT UNIQUE 的别名。

就是这样。

所以，如果改成以下的方式，就可以成功了。

 
MariaDB [テストデータベース]> create table if not exists センサ ( id BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL, センサ integer,  温度 real,  湿度 real,  メモ text );
Query OK, 0 rows affected (0.017 sec)

（：追加记之结束）

我试试放入测试数据。


MariaDB [test]> INSERT INTO sensor ( sensor,temp,hemi,memo ) VALUES (1,22.0,85.0,'ストーブON');
Query OK, 1 row affected (0.00 sec)

MariaDB [test]>  INSERT INTO センサ ( センサ,温度,湿度,メモ ) VALUES (1,22.0,85.0,'ストーブON');
Query OK, 1 row affected (0.00 sec)

确认


MariaDB [test]> select * from sensor;
+----+--------+------+------+----------------+
| id | sensor | temp | hemi | memo           |
+----+--------+------+------+----------------+
|  1 |      1 |   22 |   85 | ストーブON     |
+----+--------+------+------+----------------+
1 row in set (0.00 sec)

MariaDB [test]> select * from センサ;
+----+-----------+--------+--------+----------------+
| id | センサ    | 温度   | 湿度   | メモ           |
+----+-----------+--------+--------+----------------+
|  1 |         1 |     22 |     85 | ストーブON     |
+----+-----------+--------+--------+----------------+
1 row in set (0.01 sec)

时区设置

select now();

查看时间。

请参考以下网址了解有关MySQL 8.0版的时区支持。

系统时区。服务器在启动时会自动识别主机机器的时区，并使用它来设置system_time_zone系统变量。之后，这个值将不会改变。

如果主机的时区未设置，则设置并重新启动。

重新安装

apt包的清除等设置是否正确呢？
在Ubuntu 18.04中，


$ sudo apt purge mariadb-server

似乎无论怎么做都不顺利。

如果遇到无法成功重新安装MariaDB的问题，请参考这篇名为「UbuntuでMariaDBをうまく再インストールできなくなったときの対処方法」的文章。链接在这里：https://qiita.com/SUZUKI_Masaya/items/c587512643b7d6337538

✖
广告
将在 10 秒后关闭
bannerAds