MySQL and MariaDB: Accounts, Backups, and Recovery Validation

When building production websites or business systems on Linux cloud servers (VPS), the database is the central store of system state. Unlike static files, databases involve highly concurrent writes, in-memory buffer pools, internal locks, and transactions spanning tables. Many operational incidents occur not because backups were never taken, but because exports used incorrect parameters, plaintext passwords were hardcoded and exposed, backups were stored on a single partition vulnerable to host failure, or recovery revealed malformed SQL, garbled character encoding, or incomplete structures.

For single-server production environments running MySQL 8.x or MariaDB 10.x+, this article explains least-privilege accounts, consistent logical export pipelines, backup validation, isolated restore drills, and disaster recovery designs that account for VPS infrastructure characteristics.

1. Account Permissions and Secure Credential Design#

Backups require sufficient database read permissions, but being able to read does not mean you should misuse the superadministrator (root) privileges. Routine operations should follow Separation of Duties by creating a dedicated backup account and never exposing plaintext passwords in terminal commands or process listings.

1.1 Distinguish MySQL and MariaDB Local Privilege Mechanisms#

MySQL and MariaDB on modern Linux distributions differ significantly in their default authentication methods:

• MariaDB: by default, locally through unix_socket the plugin, for root@localhost for authentication. This means that only the host operating system's root user (or with sudo privileges) enters in the local terminal mariadb can log in without a password, while ordinary system users cannot invoke it directly.
• MySQL 8.0+: Uses the following by default: caching_sha2_password authentication plugin. Local administration usually requires a password, and newer versions impose stricter requirements for password complexity, SSL connections, and Dynamic Privileges.

Regardless of the underlying database branch, do not use application accounts, such as those for WordPress or your own service, directly for backups. Never let backup scripts use unrestricted root 账户。

1.2 Create a Least-Privilege Backup Account#

备份工具需要读取所有目标表结构、表数据、视图、触发器及 Storage 过程，并需要在导出瞬间锁定权限以建立事务快照。可使用管理账户登录 Databases ，为本地备份单独创建用户：

-- 以 MySQL 8.0 / MariaDB 统一规范为例，创建本地专用备份用户
CREATE USER 'db_dumper'@'127.0.0.1' IDENTIFIED BY 'Set_A_Complex_Random_Pass_32Chars!';

-- 授予导出业务数据必需的只读与锁定权限
-- 涵盖表数据读取、事务快照一致性保证、视图解析及触发器导出
GRANT SELECT, RELOAD, PROCESS, LOCK TABLES, SHOW VIEW, TRIGGER ON *.* TO 'db_dumper'@'127.0.0.1';

-- 若需要导出存储过程、自定义函数及事件调度器（按需增加）：
GRANT EVENT, SHOW DATABASES ON *.* TO 'db_dumper'@'127.0.0.1';

-- 刷新权限表确保立即生效
FLUSH PRIVILEGES;

Note: The associated Host must be strictly restricted to 127.0.0.1 or localhost. Never set the backup account's Host to a wildcard %, preventing accidentally permissive firewall rules from exposing the database port to privileged-account brute-force attacks on the public internet.

1.3 Credential Storage: Eliminate ps Plaintext in Processes and Shell History#

Include Directly When Running the Command -p'MyPassword' is an extremely dangerous operation. Any user with low-level server privileges can use ps aux、top or /proc process interfaces to capture command-line arguments; Bash history files (~/.bash_history) also retains the password in plaintext.

规范的做法是使用权限严密的配置文件来传递凭据。

方案 A：针对所有客户端的 Standard 配置（~/.my.cnf）#

在运行备份脚本的系统用户家目录下建立专有配置文件：

# 文件路径：/home/backup-user/.my.cnf 或 /root/.my.cnf
[client]
user = db_dumper
password = Set_A_Complex_Random_Pass_32Chars!
host = 127.0.0.1
port = 3306

[mysqldump]
quick
max_allowed_packet = 64M

After creating it, tighten permissions so that only the owner can read and write it:

chmod 600 ~/.my.cnf

配置生效后，执行 mysql or export tools without supplying username and password parameters; the client reads this file automatically.

方案 B：MySQL 专有的加密登录路径（mysql_config_editor）#

If you are running official MySQL, use its built-in credential-obfuscation tool:

mysql_config_editor set --login-path=backup_auth --host=127.0.0.1 --user=db_dumper --password
# 输入密码后，凭据将被加密存储于 ~/.mylogin.cnf

In subsequent export commands, simply add --login-path=backup_auth to log in securely.

2. Standardized Export:mysqldump and mariadb-dump Building Consistency#

For small and medium websites and typical production databases, a Logical Dump is readable and flexible for cross-architecture and cross-version migration. However, without understanding the storage engine, exports can cause global table locks that disrupt live services, or trigger OOM (Out Of Memory) on memory-constrained VPS instances.

2.1 工具匹配与语法差异#

在命令调用时，请确认当前 Databases 环境：

• MariaDB 10.5+: It is recommended to invoke the native binary directly mariadb-dump, this tool has gradually replaced the symbolic link mysqldump, more accurately recognizing MariaDB-specific system dictionaries and virtual-column definitions.
• MySQL 8.0+: Use the official mysqldump. Note that MySQL 8.0 enables by default --tablespaces，如果备份账户未被授予 PROCESS additional global tablespace privileges, so running it directly may fail; explicitly specify --no-tablespaces。

2.2 Build a Highly Resilient, Consistent Export Pipeline#

For production databases built primarily on InnoDB, use MVCC (Multiversion Concurrency Control) for non-blocking backups and streaming compression to reduce disk I/O pressure.

The following is a standard single-database export pipeline validated in production environments:

#!/usr/bin/env bash
# 开启严格模式：任何命令报错或管道失败立即退出
set -eo pipefail

DB_NAME="production_app"
BACKUP_DIR="/var/backups/database"
DATE_STAMP="$(date +%Y%m%d_%H%M%S)"
TARGET_FILE="${BACKUP_DIR}/${DB_NAME}_${DATE_STAMP}.sql.gz"

# 严格限定新生成文件的权限掩码（仅允许所有者读写）
umask 077
mkdir -p "${BACKUP_DIR}"

# 执行一致性导出并通过管道直接压缩
# 如果是 MariaDB，可将 mysqldump 替换为 mariadb-dump
mysqldump \
  --single-transaction \
  --quick \
  --routines \
  --triggers \
  --default-character-set=utf8mb4 \
  --no-tablespaces \
  "${DB_NAME}" | gzip -c > "${TARGET_FILE}"

echo "Database export completed successfully: ${TARGET_FILE}"

关键参数深度解析：#

• --single-transaction：Must Enable. Before the export begins, start a transaction at an isolation level permitting non-repeatable reads (REPEATABLE READ), ensuring that data from all InnoDB tables belongs to the same logical point in time. The entire process avoids placing a global shared read lock on the tables, so the live service's INSERT、UPDATE、DELETE running normally.

  Warning: This parameter does not work for nontransactional engines such as MyISAM; if, during the export, you execute ALTER TABLE、DROP TABLE and other DDL operations can still invalidate the consistent snapshot or cause metadata lock (MDL) blocking.

• --quick(or -q): forces the client to retrieve data from the server row by row and stream it to the output instead of first fetching the entire table and buffering it in the client's system memory. On cloud server instances with 1GB or 2GB of RAM, exporting very large tables without this parameter can easily trigger the Linux kernel's OOM Killer, forcibly terminating the database or backup process.
• --routines and --triggers: Export associated stored procedures, custom functions, and triggers together to prevent hidden breaks in application logic after restoration.
• --default-character-set=utf8mb4: Fix the character set to prevent server operating system environment variables, such as LC_ALL、LANG) from causing encoding truncation or garbled text at non-ASCII characters such as Chinese text or Emoji in the exported file.
• set -eo pipefail: Essential for Shell pipelines. Without this directive, when mysqldump crashes midway because of insufficient permissions or space, as long as the downstream gzip exits normally, the Shell return value will still be 0, creating a false backup file whose name looks complete but whose contents are missing.

3. Validate Backups: Reject “False Success”#

On many long-running systems, administrators assume scheduled backups are still working, only to discover after a server failure that the backup directory contains nothing but 0-byte files or incomplete SQL dumps caused by a full disk. Defensive validation is an essential part of automated backups.

3.1 Separate Exit Status Codes and Error Output#

When writing automation scripts, never use 2>&1 blindly redirect standard error into the backup archive. Standard error logs must be directed to a dedicated log file:

ERR_LOG="/var/log/db_backup_error.log"

mysqldump --single-transaction --quick "${DB_NAME}" 2> "${ERR_LOG}" | gzip -c > "${TARGET_FILE}"
DUMP_STATUS="${PIPESTATUS[0]}"

if [ "${DUMP_STATUS}" -ne 0 ]; then
    echo "ERROR: mysqldump execution failed with code ${DUMP_STATUS}. Details:" >&2
    cat "${ERR_LOG}" >&2
    # 清理损坏的半成品文件，防止误判
    rm -f "${TARGET_FILE}"
    exit 1
fi

3.2 Probe Structural Metadata and End Markers#

A valid logical dump always ends with a specific comment marker when it completes normally. If the process is forcibly interrupted, the file usually ends with an unfinished SQL fragment. Quickly inspect it with the following commands:

# 检查压缩包完整性
gzip -t "${TARGET_FILE}"

# 提取解压流的最后 10 行，确认是否包含完成标志
zcat "${TARGET_FILE}" | tail -n 10 | grep -E "Dump completed on|MariaDB dump"

If you cannot find a marker such as -- Dump completed on 2026-09-26 ... marker indicates an interrupted export, for example from a database timeout, exhausted host memory, or exceeded filesystem quota. Mark the file as corrupted immediately.

3.3 Generate a Hash Verification Record#

To verify that a file has not suffered bit flips or silent corruption during transfer, archiving, or off-site storage, calculate and record its hash immediately after creation:

sha256sum "${TARGET_FILE}" > "${TARGET_FILE}.sha256"

在后续任何阶段（如下载到本地、跨节点传输后），只需执行 sha256sum -c "${TARGET_FILE}.sha256" 即可验证一致性。

4. Isolated Restore Drills: End-to-End Validation#

A backup that has not passed a restore test cannot be considered usable. Before software upgrades, architecture changes, or disasters occur, regularly perform a full replay in a temporary environment isolated from production or in a local test database to verify that the service works.

4.1 Beware of Dangerous Context in Export Files#

Before importing SQL into any environment, check whether the export contains implicit target-database declarations. SQL exported by some panels or tools may include the following statements:

CREATE DATABASE IF NOT EXISTS `production_app`;
USE `production_app`;

Importing it directly into a test instance could unknowingly overwrite an existing production database. Before restoring, inspect a sample:

zcat "${TARGET_FILE}" | head -n 40 | grep -Ei "CREATE DATABASE|USE "

If database names are hardcoded, remove those declarations during import or replay the dump only in a clean, separate test instance.

4.2 Perform an Isolated Restore Test#

Create a sandbox database with a distinct name (such as db_restore_staging), assign a separate account and replay the data stream:

# 1. 登录管理端创建隔离测试库
mysql -u root -p -e "
  DROP DATABASE IF EXISTS db_restore_staging;
  CREATE DATABASE db_restore_staging CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  CREATE USER IF NOT EXISTS 'restore_checker'@'127.0.0.1' IDENTIFIED BY 'Temp_Test_Secret_9988!';
  GRANT ALL PRIVILEGES ON db_restore_staging.* TO 'restore_checker'@'127.0.0.1';
  FLUSH PRIVILEGES;
"

# 2. 将压缩流直接重放至测试库
zcat "${TARGET_FILE}" | mysql -h 127.0.0.1 -u restore_checker -p'Temp_Test_Secret_9988!' db_restore_staging

4.3 Post-Restoration Consistency Spot-Check Checklist#

After importing, the absence of terminal errors alone is not enough. You must also verify data consistency in the following three dimensions:

1. Compare Object Counts: Compare the total numbers of tables, views, and stored procedures in the production and restored databases.
   sqlCopy Code

   SELECT table_type, COUNT(*) 
   FROM information_schema.tables 
   WHERE table_schema = 'db_restore_staging' 
   GROUP BY table_type;

2. Sample Row Counts in Core Application Tables: For large time-ordered tables such as orders, logs, or articles, compare the highest auto-increment ID and total record count:
   sqlCopy Code

   SELECT COUNT(*), MAX(id) FROM db_restore_staging.critical_orders;

3. Troubleshoot Cross-Version Compatibility：
   • When importing from MySQL 5.7 into MySQL 8.0, watch for reserved-word conflicts (such as GROUPS、SYSTEM) and deprecated SQL Modes (such as NO_AUTO_CREATE_USER）。
   • When restoring across MySQL and MariaDB, pay particular attention to those containing DEFINER views or triggers, to avoid issues arising from privilege architecture and security policies (such as MariaDB's SQL SECURITY and differences from MySQL's privilege hierarchy) that could cause triggers to fail.

5. Cloud servers 底座与灾备韧性：结合 Providers 特性的运维实践#

Database reliability depends not only on SQL scripts but is also closely tied to the host cloud platform's infrastructure, billing model, and emergency administration capabilities. VPS providers differ significantly in console architecture, login verification policies, and lifecycle management. Backup and disaster recovery plans must account for the specific provider's underlying rules.

5.1 Billing Cycles and the Survival Window Before Permanent Data Deletion#

One of the greatest security risks in production is storing all backups on a single VPS disk while overlooking the provider's billing lifecycle policies:

• BandwagonHost（ BandwagonHost ）: Its official renewal policy explicitly states不会自动从绑定的信用卡或 PayPal 发, from静默代扣. When renewal is enabled, the system generates an invoice 7 days before the service expires. It automatically deducts payment only if the user has prepaid a sufficient account balance; otherwise, an administrator must pay the invoice manually. Once an overdue invoice leads to cancellation, or a refund is initiated (available within 30 days of purchase with traffic usage below 10%),关联的 VPS instance 及其底层 Storage 卷会被彻底销毁，磁盘数据不可逆清空。
• DMIT: Monthly renewal invoices are also generated 7 days before expiration. Under DMIT's official instance policy, an instance with an overdue invoice first enters Suspended status after expiration. However, be sure to note:Suspended Instances Are Generally Retained for Only About 3 Days. Once the 3-day grace period is exhausted, the system automatically releases the resources and permanently destroys the instance and all disk data.

Architectural Lessons: Because both providers have fast, irreversible data-destruction mechanisms after payment becomes overdue,“Local Backups” Offer No Protection Against Instance Shutdowns or Account-Level Disputes。生产 Databases 的备份流程中，必须强制包含向第三方异地对象 Storage （如 Cloudflare R2、Backblaze B2、AWS S3 或异地独立灾备节点）的自动同步环节，决不能在生产机硬盘上“单点单副本”运行。

5.2 Emergency Access Channels and Network Troubleshooting#

When a database unexpectedly consumes all CPU capacity, misconfigured iptables rules cut off SSH, or permission changes lock up a service, you must use the provider's underlying Out-of-band Console for recovery. The two providers' management systems differ clearly:

DMIT Access Security and Recovery Paths#

1. Strict Default Credential Policy: During initial installation of a DMIT instance, the system image disables remote SSH root password login by default and enforces SSH key-pair (Public Key) authentication.
2. Key Repository and Instance Synchronization: in the “Access” tab of the DMIT client area's control panel, users can push new SSH keys or reset administrator credentials at any time. However, remember the official operating guideline:After Changing or Associating an SSH Key in the Web Panel, Restart the Instance Using the Panel's Shutdown/Restart Controls So the Cloud Initialization Agent Can Write and Activate the New Key。
3. Emergency Access Through the Out-of-Band Console: If a misconfigured local firewall makes all SSH ports inaccessible, log in directly to the DMIT control panel and open “Console” (VNC/streaming terminal). This terminal bypasses all external network interfaces and connects directly to the host's virtual display, allowing you to troubleshoot mysqld severe failures such as hangs or a read-only filesystem.

BandwagonHost's Permission Layers and Recovery Features#

1. A Password System with Three Separate Authority Levels: BandwagonHost's architecture has three completely independent sets of credentials. Do not confuse them during routine troubleshooting:
   • Client Area Password: The client area login password, used for billing, renewals, support tickets, and viewing all products.
   • KiwiVM Management Password: A management password exclusive to the KiwiVM control panel, used to issue underlying virtualization commands such as OS reinstallation, hardware start/stop, and data center migration.
   • OS root Password： Operating system 内的 Linux root 账户密码，仅用于 SSH 与系统内部控制。
2. KiwiVM Interactive Console：当 VPS 发生内部 Networking 失联、误删 SSH 关键组件或配置错误时， Passed KiwiVM 提供的 Interactive Console 可直接接入虚拟机的 TTY 终端。管理员可以在该终端内直接执行单用户模式维护、修复 /etc/fstab, correct database connection permissions, or copy data in an emergency.
3. Data Center Migration Restrictions: Although BandwagonHost supports seamless migration between data centers and network routes, the official rules clearly emphasize:If the instance's IP address is currently blacklisted or blocked, data center migration will be restricted or disabled entirely. During database disasters or network outages, switching data centers cannot be relied on as a fallback for blocked connectivity; you still need to extract local offline data.

6. Fully Automated Backups and Encrypted Off-Site Transfers in Practice#

Combine the security design, export standards, and disaster recovery principles above into an automated operations script requiring no manual intervention.

6.1 Write a Production-Grade Automated Backup Script#

Create /usr/local/bin/db-autobackup.sh：

#!/usr/bin/env bash
# =================================================================
# 生产环境数据库定时导出与生命周期管理脚本
# =================================================================
set -eo pipefail

# 1. 基础配置定义
DB_NAME="production_app"
BACKUP_ROOT="/var/backups/db_archives"
LOG_FILE="/var/log/db_backup.log"
RETENTION_DAYS=7
NOW="$(date +%Y%m%d_%H%M%S)"
DUMP_FILE="${BACKUP_ROOT}/${DB_NAME}_${NOW}.sql.gz"
ENC_FILE="${DUMP_FILE}.enc"

# 加密密钥文件（需预先生成并妥善保管在隔离路径，权限设为 400）
SYMMETRIC_KEY="/etc/security/db_backup.key"

# 2. 环境初始化
umask 077
mkdir -p "${BACKUP_ROOT}"

log_msg() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "${LOG_FILE}"
}

log_msg "Starting backup pipeline for database: ${DB_NAME}"

# 3. 检查专用凭据文件是否存在
if [ ! -f "${HOME}/.my.cnf" ]; then
    log_msg "FATAL: Credentials configuration file ~/.my.cnf not found!"
    exit 1
fi

# 4. 执行一致性导出（从 ~/.my.cnf 自动拉取账户配置）
# 优先选择 mariadb-dump，若不存在则回退至 mysqldump
DUMP_BIN="$(command -v mariadb-dump || command -v mysqldump)"

${DUMP_BIN} \
    --single-transaction \
    --quick \
    --routines \
    --triggers \
    --default-character-set=utf8mb4 \
    --no-tablespaces \
    "${DB_NAME}" | gzip -c > "${DUMP_FILE}"

log_msg "Export finished. Verifying gzip integrity..."
gzip -t "${DUMP_FILE}"

# 5. 校验文件完整性与特征尾标
if ! zcat "${DUMP_FILE}" | tail -n 10 | grep -E -q "Dump completed on|MariaDB dump"; then
    log_msg "FATAL: Dump verification failed! No valid trailing marker."
    rm -f "${DUMP_FILE}"
    exit 2
fi

# 6. 生成原包 SHA256 校验和
sha256sum "${DUMP_FILE}" > "${DUMP_FILE}.sha256"

# 7. 本地对称加密（防止在离线传输过程中泄漏用户敏感数据）
if [ -f "${SYMMETRIC_KEY}" ]; then
    openssl enc -aes-256-cbc -salt -pbkdf2 \
        -in "${DUMP_FILE}" \
        -out "${ENC_FILE}" \
        -pass "file:${SYMMETRIC_KEY}"
    # 加密完成后移除未经加密的本地裸文件
    rm -f "${DUMP_FILE}"
    TARGET_SYNC_FILE="${ENC_FILE}"
    log_msg "Archive encrypted successfully."
else
    log_msg "WARNING: Symmetric key not found. Skipping encryption!"
    TARGET_SYNC_FILE="${DUMP_FILE}"
fi

# 8. 执行异地分发（以安全的 rsync / SSH 传输至异地灾备服务器为例）
REMOTE_BACKUP_HOST="backup-node.example.com"
REMOTE_DEST_DIR="/data/remote_db_backups/"

log_msg "Syncing payload to offsite storage: ${REMOTE_BACKUP_HOST}..."
rsync -azq -e "ssh -o BatchMode=yes -o StrictHostKeyChecking=accept-new" \
    "${TARGET_SYNC_FILE}" "${TARGET_SYNC_FILE}.sha256" \
    "backup_user@${REMOTE_BACKUP_HOST}:${REMOTE_DEST_DIR}"

log_msg "Offsite sync completed."

# 9. 本地旧文件轮换与空间清理（防止打满生产机磁盘）
find "${BACKUP_ROOT}" -name "${DB_NAME}_*.sql.gz*" -mtime +"${RETENTION_DAYS}" -delete
log_msg "Local rotation completed. Removed archives older than ${RETENTION_DAYS} days."

6.2 Set Permissions and Add the System Scheduled Task#

Protect the script strictly and register it with the system's cron within the service:

# 仅允许系统管理员执行与查看
chmod 700 /usr/local/bin/db-autobackup.sh
chown root:root /usr/local/bin/db-autobackup.sh

# 生成备份专用的高强度对称密钥
openssl rand -base64 32 > /etc/security/db_backup.key
chmod 400 /etc/security/db_backup.key

Edit the Crontab Configuration:

crontab -e

Run backups during low-traffic periods, such as 02:30:

# 每天凌晨 02:30 触发自动化备份并记录日志
30 2 * * * /usr/local/bin/db-autobackup.sh >/dev/null 2>&1

7. Common Troubleshooting Quick Reference#

When troubleshooting database connections and backup or restore failures, use the following symptoms to narrow down the problem quickly:

Passed 合理的账户权限隔离、基于底层 Storage 引擎特性的无锁导出参数、严谨的结构性校验，并结合具有底线思维的异地容灾架构，才能真正确保 MySQL 与 MariaDB Databases 在遭遇硬件损毁、账单中断或系统故障时，做到数据完整无损、系统随时可复原。