Database Server Ops: Hardware, Tuning, Backup & Security Best Practices

Database server operations best practices cover hardware selection, system configuration, performance optimization, security management, backup and recovery, high availability, and disaster recovery, with executable code examples (adjust as needed for the environment).

1. Hardware Selection

Processor (CPU) : Choose multi‑core, high‑frequency CPUs such as Intel Xeon or AMD EPYC to handle high concurrency and complex queries.

Memory (RAM) : Provision sufficient RAM based on database size and concurrency to reduce disk I/O.

Storage : Use SSDs instead of HDDs and consider RAID 10 for better performance and reliability.

Network Interface Card (NIC) : Opt for high‑bandwidth, low‑latency NICs (e.g., 10 Gbps or higher).

2. System Configuration & Optimization

2.1 Operating System Choice

Linux is the preferred OS for most database servers (CentOS, RHEL, Ubuntu, etc.).

2.2 Kernel Parameter Tuning

# Adjust memory management parameters
sysctl -w vm.swappiness=10

# Adjust I/O scheduler
echo deadline > /sys/block/sda/queue/scheduler

2.3 Filesystem Selection

Use high‑performance filesystems such as ext4, XFS, or ZFS, and enable the noatime and nodiratime mount options.

# Enable noatime and nodiratime when mounting
mount -o remount,noatime,nodiratime /

3. Database Configuration & Optimization

3.1 MySQL Parameter Adjustment

# Show current InnoDB buffer pool size
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

# Set InnoDB buffer pool size to 512M
mysql -u root -p -e "SET GLOBAL innodb_buffer_pool_size = 512M;"

# Restart MySQL service for changes to take effect

3.2 Index Optimization

Regularly analyze query logs, optimize SQL statements, and ensure frequently queried columns have appropriate indexes.

# Example: add an index to a column
ALTER TABLE my_table ADD INDEX idx_column_name (column_name);

4. Performance Monitoring

Use tools such as Prometheus and Grafana to monitor CPU usage, memory consumption, disk I/O, and network traffic of the database.

5. Backup & Recovery

5.1 Backup Strategy

Define full and incremental backup strategies to guarantee data recoverability.

# Logical backup with mysqldump
mysqldump -u username -p database_name > backup.sql

# Incremental backup example (requires binary logs)
# Note: incremental backup implementation is complex; this is a conceptual illustration

5.2 Backup Verification

Periodically verify backup files to ensure they can be restored when needed.

6. Security Management

6.1 Permission Management

Follow the principle of least privilege to restrict database access rights.

# Grant SELECT and INSERT on a specific table to a user
GRANT SELECT, INSERT ON mydb.mytable TO 'user'@'localhost';

6.2 Encryption

Encrypt sensitive data at rest and enable SSL/TLS for data in transit.

# Enable SSL in MySQL configuration
[mysqld]
require_secure_transport=ON

6.3 Audit Logging

Enable the database audit log to record critical operations.

# Enable general query log in MySQL
[mysqld]
general_log=ON
general_log_file=/var/log/mysql/general.log

7. High Availability & Disaster Recovery

7.1 Master‑Slave Replication

Set up replication to increase data redundancy and mitigate single‑point failures.

7.2 Cluster Deployment

Use database clustering technologies (e.g., MySQL Cluster, Oracle RAC) to improve availability.

8. Automation & AI‑Driven Ops

Leverage automation tools and AI techniques for predictive maintenance and fault diagnosis, reducing human error.

9. Operational Process & Documentation

9.1 Assess Current State

Understand the current condition of the database system and identify existing issues.

9.2 Planning

Based on the assessment, create an improvement plan.

9.3 Incremental Implementation

Execute improvement measures step by step according to the plan.

Link: https://www.cnblogs.com/TS86/p/18381132