Canonical: https://vpsmap.net/en/guides/database-backup/
Language: en
Updated: 2026-09-26T00:00:00+08:00

Website Setup

# MySQL and MariaDB: Accounts, Backups, and Recovery Validation

VPSMap Editorial · Updated 2026-09-26 · 33 min read · [Plain-text version](https://vpsmap.net/guides/database-backup/index.md)

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[#](https://vpsmap.net/en/guides/database-backup/#section-1)

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[#](https://vpsmap.net/en/guides/database-backup/#section-2)

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` account.

### 1.2 Create a Least-Privilege Backup Account[#](https://vpsmap.net/en/guides/database-backup/#section-3)

Backup tools must read all target table schemas, table data, views, triggers, and stored procedures, and require locking privileges at the moment of export to establish a transaction snapshot. Log into the database with an administrative account and create a separate user for local backups:

sql

```
-- 以 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[#](https://vpsmap.net/en/guides/database-backup/#section-4)

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.

The proper approach is to pass credentials through configuration files with tightly restricted permissions.

#### Option A: Standard Configuration for All Clients (`~/.my.cnf`）[#](https://vpsmap.net/en/guides/database-backup/#section-5)

Create a Dedicated Configuration File in the Home Directory of the System User Running the Backup Script:

ini

```
# 文件路径：/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:

bash

```
chmod 600 ~/.my.cnf
```

After the Configuration Takes Effect, Run `mysql` or export tools without supplying username and password parameters; the client reads this file automatically.

#### Option B: MySQL's Proprietary Encrypted Login Path (`mysql_config_editor`）[#](https://vpsmap.net/en/guides/database-backup/#section-6)

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

bash

```
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[#](https://vpsmap.net/en/guides/database-backup/#section-7)

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 Matching Tools and Syntax Differences[#](https://vpsmap.net/en/guides/database-backup/#section-8)

When Running the Command, Confirm the Current Database Environment:

- **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`, if the backup account has not been granted `PROCESS` additional global tablespace privileges, so running it directly may fail; explicitly specify `--no-tablespaces`.

### 2.2 Build a Highly Resilient, Consistent Export Pipeline[#](https://vpsmap.net/en/guides/database-backup/#section-9)

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:

bash

```
#!/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}"
```

#### In-Depth Explanation of Key Parameters:[#](https://vpsmap.net/en/guides/database-backup/#section-10)

- `--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”[#](https://vpsmap.net/en/guides/database-backup/#section-11)

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[#](https://vpsmap.net/en/guides/database-backup/#section-12)

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:

bash

```
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[#](https://vpsmap.net/en/guides/database-backup/#section-13)

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:

bash

```
# 检查压缩包完整性
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[#](https://vpsmap.net/en/guides/database-backup/#section-14)

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:

bash

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

At any later stage, such as after downloading locally or transferring between nodes, simply run `sha256sum -c "${TARGET_FILE}.sha256"` to verify consistency.

## 4. Isolated Restore Drills: End-to-End Validation[#](https://vpsmap.net/en/guides/database-backup/#section-15)

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[#](https://vpsmap.net/en/guides/database-backup/#section-16)

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:

sql

```
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:

bash

```
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[#](https://vpsmap.net/en/guides/database-backup/#section-17)

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

bash

```
# 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[#](https://vpsmap.net/en/guides/database-backup/#section-18)

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.

sql

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

1. **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:

sql

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

1. **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 Server Infrastructure and Disaster Recovery Resilience: Provider-Specific Operational Practices[#](https://vpsmap.net/en/guides/database-backup/#section-19)

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[#](https://vpsmap.net/en/guides/database-backup/#section-20)

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**Does Not Silently Initiate Automatic Charges Against a Linked Credit Card or PayPal Account**. 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%),**The Associated VPS Instance and Its Underlying Storage Volumes Are Completely Destroyed, with Disk Data Irreversibly Erased**.

- **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**. Production database backup workflows must include automatic synchronization to third-party off-site object storage, such as Cloudflare R2, Backblaze B2, or AWS S3, or to an independent remote disaster recovery node. Never rely on a single copy at a single point on the production server's disk.

### 5.2 Emergency Access Channels and Network Troubleshooting[#](https://vpsmap.net/en/guides/database-backup/#section-21)

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[#](https://vpsmap.net/en/guides/database-backup/#section-22)

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.

1. **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**.

1. **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[#](https://vpsmap.net/en/guides/database-backup/#section-23)

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**the Linux root account password inside the operating system, used only for SSH and internal system control.

1. **KiwiVM Interactive Console**: if internal networking fails, critical SSH components are accidentally deleted, or configuration errors occur, KiwiVM's Interactive Console provides direct access to the VM's TTY terminal. Administrators can perform single-user maintenance there and repair `/etc/fstab`, correct database connection permissions, or copy data in an emergency.

1. **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[#](https://vpsmap.net/en/guides/database-backup/#section-24)

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[#](https://vpsmap.net/en/guides/database-backup/#section-25)

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

bash

```
#!/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[#](https://vpsmap.net/en/guides/database-backup/#section-26)

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

bash

```
# 仅允许系统管理员执行与查看
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:

bash

```
crontab -e
```

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

cron

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

## 7. Common Troubleshooting Quick Reference[#](https://vpsmap.net/en/guides/database-backup/#section-27)

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

| Failure Symptoms | Identify the Root Cause | Targeted Solutions |
| --- | --- | --- |
| **`Access denied for user 'db_dumper'@'...'`** | 1. Incorrect Password 2. Authorized Host Does Not Match (For Example, Permission Was Granted To `localhost` but connects over TCP to `127.0.0.1`） 3. Authentication Plugin and Client Incompatibility | Verify `SELECT user, host, plugin FROM mysql.user;`; ensure the command line `--host` matches exactly the Host type defined in the access grant. |
| **`Can't connect to local MySQL server through socket`** | 1. The Main Database Process Has Crashed or Has Not Started 2. Conflicting Socket File Paths (Such As `/tmp/mysql.sock` and `/run/mysqld/mysqld.sock`） 3. The Host Disk Is 100% Full | Check `systemctl status mariadb` or `mysql`; run `df -h` Check the root partition and `/var` the partition's free space; through system logs (`journalctl -xe`) to investigate the cause of the crash. |
| **`mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet'`** | the table contains extremely large individual rows, such as long text or large objects `LONGTEXT`、`MEDIUMBLOB`) exceeds the client's or server's transfer limit | In `~/.my.cnf` of `[mysqldump]` and the server's `[mysqld]` section, increase both `max_allowed_packet = 128M`(or higher). |
| **`Got error: 1227: Access denied; you need SUPER or SYSTEM_VARIABLES_ADMIN privilege`** | The exported SQL file contains `SET @@GLOBAL.GTID_PURGED` or with production-specific user definitions `DEFINER` Syntax | When restoring across databases or environments, append during export `--no-set-gtid-purged`, or use regular expressions before importing to remove from the SQL `DEFINER=\`user`@`host`` constraints. |
| **SSH Login Completely Rejected / Password Invalid** | 1. The Provider's System Disables Password Login by Default (Such as DMIT) 2. Firewall Misconfiguration Blocks Port 22 | Log in to the provider's panel and launch **DMIT Console** or **BandwagonHost KiwiVM Interactive Console**. For DMIT, after pushing an SSH key through Access, you must reboot the host through the panel for it to take effect. |

Only appropriate account privilege separation, non-locking export parameters tailored to the storage engine, rigorous structural verification, and an off-site disaster recovery architecture designed for worst-case scenarios can ensure that MySQL and MariaDB data remains intact and systems can be restored after hardware damage, billing-related interruptions, or system failures.
