数据库 MySQL / MariaDB

— 目录 —

MySQL / MariaDB 介绍

MySQL 是最常用的关系性数据库之一，是一些 VPS 常用自建软件（如 Wordpress blog、NextCloud…）依赖的基础。MariaDB 是 MySQL 的一个分支。MySQL 在 2009 年被收购，逐渐变得商业化后，包括 MySQL 创始人在内的一些开发者，为了保持开源项目的纯洁，而创建了 MariaDB，作为独立的开源项目来维护。

MariaDB 保持了对 MySQL 的兼容性，甚至在一些功能上更加便利。对于需要使用 MySQL 的绝大多数软件（至少是本站介绍的所有软件），选择 MariaDB 还是 MySQL，没有任何区别。本站推荐使用 MariaDB。

但是，从称谓上，MySQL 更习惯被用作这种类型数据库的统称，尤其是和其它类型的数据库做对比时（如 PostgreSQL、SQLite…）。所以，虽然大家实际安装的是 MariaDB，可能有时仍然会把它叫做 MySQL。

因为在本站的教程里，数据库是安装在 docker 容器里的，所以本文的很多数据库命令，都是关于如何从 docker 容器外部来操作数据库的，比通常直接介绍数据库命令的教程更复杂一些。同时，为了避免在之后的数据库操作、以及日常的维护脚本中，直接输入数据库密码，本文采用 docker secret 的方式保存数据库的 root 用户密码。这些在下文的命令里会进行区分说明。

MariaDB 的安装

docker compose 目录结构

/DOCKERS/mariadb       # docker compose 项目目录
├── root_pw.txt        # 储存根用户的密码
└── docker-compose.yml # 配置文件

和本站其它大多数程序的 docker 配置不同，mariadb 的数据文件，并不是用本地挂载（bind mount）的方式存放在 docker compose 目录里，而是使用数据卷（docker volume）的方式。因为不同版本的 mariadb，对数据文件的权限要求可能会不同，容易和 vps 自身的文件权限发生冲突。所以建议新人用户使用数据卷（docker volume）的方式。

按照本站 docker 一文的设定，预先创建 docker 网络，让其它容器和外部网站，共享同一个数据库系统。

# 如果还没有运行过的话，先创建 docker 网络，为需要数据库的容器共享内部网络
sudo docker network create network_database

在运行 docker-compose 之前，首先，设置一个数据库的根用户（root）密码，放到数据库 docker 项目的 root_pw.txt 里面，这是为了避免在以后的数据维护命令中，把密码写在数据库的明文里。

echo 'root_password_example' > /DOCKERS/mariadb/root_pw.txt

docker-compose.yml，本站使用 MariaDB 12.3 的长期支持版本。

services:
  vps-mariadb:
    image: mariadb:12.3-ubi # 这是长期稳定支持的版本
    container_name: vps-mariadb
    restart: always
    # 如果忘记了 root 密码，取消下一行的注释，重新启动 docker-compose
    # command: --skip-grant-tables --skip-networking
    environment:
      # 设置根用户 root 的密码文件位置
      - MARIADB_ROOT_PASSWORD_FILE=/run/secrets/vps_mariadb_rootpw
      # 可选项：启动时可以直接创建一个数据库，指定它的用户和密码。
      # - MARIADB_DATABASE=db_example
      # - MARIADB_USER=user_example
      # - MARIADB_PASSWORD=user_password_example
    secrets:
      # 使用 docker secret 机制，将 root 密码传入 docker 容器
      - vps_mariadb_rootpw
    ports:
      - "127.0.0.1:3306:3306"
    volumes:
      - mariadb_data:/var/lib/mysql
    networks:
      - network_database # 加入预设的数据库共享网络
volumes:
  mariadb_data: # 存储数据的 docker volume
networks:
  network_database:
    external: true
secrets:
  vps_mariadb_rootpw:    
    file: ./root_pw.txt  # secret 对应的容器外部的密码文件

如果你暂时只是为了单个服务而创建数据库，可以在第一次启动时，在 docker-compose.yml 里直接创建新的数据库名称和用户密码，可以省略下文专门进入数据库创建的步骤；

MariaDB 数据库的使用，创建数据库，创建用户

对于每个需要使用 MySQL / MariaDB 数据库的网络服务（譬如，你新建了一个 wordpress blog），需要在 MariaDB 数据库里做的操作，大致如下：

进入数据库命令行（也有通过网页操作的方式，以后说）；
为新的服务创建一个数据库；
创建一个新的用户和密码；
让用户拥有新建的数据库的访问和管理权限；
刷新整个数据库的权限设置。

也可以让一个用户同时管理多个数据库，这样就可以省略上面的步骤 3，但是，强烈建议不要使用 root 根用户。通常每个使用 MariaDB 数据库的服务，都会把分给它的数据库用户和密码，明文保存在各自的程序里。让它们知道 root 根用户的密码，有很大的安全隐患。

进入 MariaDB 的命令行界面

在 vps 命令行，通过 docker，进入数据库的命令行模式，有三种方式可以运行：

运行下面的命令，按照本文前面的安全配置，会自动从 root_pw.txt 中获取密码，避免了把密码在命令行明文输入的风险：

sudo docker exec -it vps-mariadb sh -c 'mariadb -u root -p"$(cat $MARIADB_ROOT_PASSWORD_FILE)"'

运行下面的命令，然后根据提示，输入密码：

sudo docker exec -it vps-mariadb mariadb -u root -p

也可以把密码写在命令里（注意，-p 后面没有空格），直接进入数据库。但这样会让一些辅助记录历史命令的软件，记住你的明文密码，造成安全隐患。所以建议尽量不要这样做。

sudo docker exec -it vps-mariadb mariadb -u root -p"PASSWORD"

然后会看到这样的，和 vps 不同的提示符，表明你已经进入了数据库管理界面：

MariaDB [(none)]>

创建新的数据库和用户

在 MariaDB 管理界面下，输入下面的命令。命令里的大写字母，只是为了显示方便，实际并不区分大小写。

# 创建新的数据库 database_new，默认使用 utf8 的国际多语言支持
CREATE DATABASE database_new CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

# 创建新的用户 vps_mariadb_user 和密码 password_example
CREATE USER 'vps_mariadb_user'@'%' IDENTIFIED VIA mysql_native_password USING PASSWORD('password_example') OR unix_socket;

# 将数据库 database_new 的使用权限，赋给用户 vps_mariadb_user
GRANT ALL ON database_new.* TO 'vps_mariadb_user';

# 刷新数据库的权限。所有涉及到权限的操作，最后都要执行这条命令才生效。
FLUSH PRIVILEGES;

# 最后，退出数据库管理界面
QUIT;

然后，就可以在其它的软件设置里，填入相应的数据库信息了。通常是这些

数据库名称：database_new
数据库用户名：vps_mariadb_user
数据库密码：password_example
数据库地址：
- 如果软件在 docker 容器外部：localhost 或 127.0.0.1
- 如果是 network_database 网络里的另一个 docker 容器：vps-mariadb
数据库端口号：3306

数据库和用户之间，是多对多的关系。

可以把多个数据库的权限，都赋给一个用户，然后在所有工具的设置里，都填写同样的用户名和密码；
可以为每个数据库，创建一个新的用户，用户看不到不属于自己的数据库的内容；
也可以把一个数据库，同时赋给多个用户。

你可以随意选择适合自己的方案，更方便的或更安全的。本站在之后的工具介绍里，统一以 vps_mariadb_user 作为用户名的例子。

常用数据库操作

# 列出所有数据库
SHOW DATABASES;

# 列出所有用户
SELECT User, Host FROM mysql.user;

# 显示用户 user_name_example 的所有权限
SHOW GRANTS FOR 'user_name_example'@'%';

# 创建新的用户
CREATE USER 'user_name_example'@'%' IDENTIFIED VIA mysql_native_password USING PASSWORD('password_example') OR unix_socket;

# 修改用户的密码
ALTER USER 'user_name_example'@'%' IDENTIFIED VIA mysql_native_password USING PASSWORD('new_password') OR unix_socket;

# 修改 root 根用户的密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

# 删除用户
DROP USER user_name_example;

# 创建新的数据库，默认使用 utf8 的国际多语言支持
CREATE DATABASE database_new_1 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

# 删除数据库
DROP DATABASE database_new_1;

# 将数据库的使用权限，赋给用户
GRANT ALL ON database_new_1.* TO 'user_name_example'@'%';

# 取消用户对于数据库的使用权限
REVOKE ALL PRIVILEGES ON database_new_1.* FROM 'user_name_example'@'%';

# 刷新权限
FLUSH PRIVILEGES;

# 退出数据库
QUIT;

数据库的备份

MySQL / MariaDB 数据库的备份方式，通常分为这几种：

逻辑备份：导出通用的 .sql 文件，可以方便地把单个或多个数据库的内容，导入到其它服务器。执行速度比物理备份慢。对于本站的个人规模的数据库，更推荐使用逻辑备份；
物理备份：使用 mariadb-backup 命令，迅速复制当前数据库的全部内容文件。速度更快，但通常是对数据库整体进行备份。备份和恢复都和当前的数据库版本紧密相关。
Docker 备份：在停止数据库后，打包备份整个 docker 项目文件夹，和相应的 docker 数据卷（volume），也是另一种可能更简单的 “物理备份”。

简单起见，本文只介绍逻辑备份，其它方式参见 MariaDB 官方文档的更详细介绍。

逻辑备份 / SQL 导出：mariadb-dump / mysqldump

备份

将单个数据库 database_1 的所有内容，备份到 backup_1.sql 文件：

# 方法 1. 从本站预设的 docker secret 文件中，获取密码
sudo docker exec -it vps-mariadb sh -c 'mariadb-dump -u root -p"$(cat $MARIADB_ROOT_PASSWORD_FILE)" --databases database_1' > /path/to/backup_1.sql

# 方法 2. 在命令行使用参数 -p 或者 -p"PASSWORD"
sudo docker exec -it vps-mariadb mariadb-dump -u root -p --databases database_1 > /path/to/backup_1.sql

也可以把多个数据库，同时备份到一个 .sql 文件：

sudo docker exec -it vps-mariadb sh -c 'mariadb-dump -u root -p"$(cat $MARIADB_ROOT_PASSWORD_FILE)" --databases database_1 database_2 database_3' > /path/to/backup_123.sql

甚至把包含系统数据库的全部数据库，同时备份到一个 .sql 文件：

sudo docker exec -it vps-mariadb sh -c 'mariadb-dump -u root -p"$(cat $MARIADB_ROOT_PASSWORD_FILE)" --all-databases' > /path/to/backup_all.sql

恢复

将前面生成的数据库备份文件，恢复到数据库的命令：（注意：docker exec 后面的参数是 -i ，而不是常用的 -it）

# 方法 1. 从本站预设的 docker secret 文件中，获取密码
sudo docker exec -i vps-mariadb sh -c 'mariadb -u root -p"$(cat $MARIADB_ROOT_PASSWORD_FILE)"' < /path/to/backup_1.sql

# 方法 2. 在命令行使用参数 -p 或者 -p"PASSWORD"
sudo docker exec -i vps-mariadb mariadb -u root -p < /path/to/backup_1.sql

注意：

虽然备份文件里，包含了把每个数据表（table）删除后重新创建的命令，但是，为了稳妥起见，建议先删除原有的数据库（drop database），再进行导入；
备份命令中的 –databases 参数，会包含创建数据库 database_1 的命令，如果要将备份文件恢复到另一个数据库 database_2，需要手动将 .sql 文件开头创建数据库的命令删除。然后，在恢复命令中指定目标数据库 database_2；

sudo docker exec -i vps-mariadb sh -c 'mariadb -u root -p"$(cat $MARIADB_ROOT_PASSWORD_FILE)" database_2' < /path/to/backup_1.sql

备份文件中，不包含和数据库用户权限有关的信息。如果是在一个新建的数据库系统中导入原有的 .sql 文件，需要重新把数据库授权给相应的用户。

其它

是否需要同时安装 MySQL 和 PostgreSQL？

另一种常见的，配合自建软件的数据库，是 PostgreSQL。有些自建工具，原生支持多种数据库；有些仅支持一种；也有些能找到第三方开发者提供的，对其它数据库的支持方案，但可能不大稳定，需要对技术更有自信，才适合使用。

正常情况下，单独的 MySQL 或者 PostgreSQL，运行两三份个人软件的数据库，需要占用的服务器内存，从 200MB 到 500+MB，甚至更多。如果你的 VPS 内存充足，同时安装两种数据库，会更加便利。但如果你是只有 1~2 GB 内存的 VPS，又想跑多个服务，就需要斟酌一下，能不能只装一种数据库，就能满足自己的工具需求？

自建软件	MySQL	PostgreSQL	SQLite
Wordpress	✓	第三方	第三方
Nextcloud	✓	✓	x
Mastodon	x	✓	x
Wallabag	✓	✓	✓
Miniflux	x	✓	x

修改 root 密码

进入 MariaDB 管理界面：

sudo docker exec -it vps-mariadb sh -c 'mariadb -u root -p"$(cat $MARIADB_ROOT_PASSWORD_FILE)"'

在 MariaDB 提示符下，输入下面的命令，设置新密码：

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;
QUIT;

最后，把新的密码，写入外部储存的密码文件 root_pw.txt

echo 'new_password' > /DOCKERS/mariadb/root_pw.txt

# 或者直接编辑 root_pw.txt 文件
nano /DOCKERS/mariadb/root_pw.txt

忘记 root 密码怎么办？

停止 MariaDB 容器

cd /DOCKERS/mariadb
sudo docker-compose down

编辑 docker-compose.yml，添加下面的 command（如果是使用本文的 docker-compose.yml，只需取消这一行前面的注释，注意对齐缩进）

  command: --skip-grant-tables --skip-networking

重启 docker 容器，然后就可以无需密码进入数据库：

sudo docker-compose up -d
sudo docker exec -it vps-mariadb mariadb -u root

在 MariaDB 提示符下，输入下面的命令，设置新密码：

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
QUIT;

退出数据库，停止 docker-compose 容器，再次编辑 docker-compose.yml，把 –skip-grant-tables 那行命令删除或注释掉，再次启动 docker-compose 容器，就可以使用新的 root 密码进入 MariaDB 数据库了。

# 停止容器
sudo docker-compose down

# 把 docker-compose.yml 里这一行注释掉
# command: --skip-grant-tables --skip-networking

# 再次启动容器
sudo docker-compose up -d

最后，把新的密码，写入外部储存的密码文件 root_pw.txt

echo 'new_password' > /DOCKERS/mariadb/root_pw.txt

# 或者直接编辑 root_pw.txt 文件
nano /DOCKERS/mariadb/root_pw.txt

#Docker #Database #Backup