Linux系统配置以支持数据库编程指南

1. 系统准备

1.1 更新系统

sudo apt update && sudo apt upgrade -y  # Ubuntu/Debian
sudo yum update -y  # CentOS/RHEL

1.2 安装基本开发工具

sudo apt install build-essential git curl wget -y  # Ubuntu/Debian
sudo yum groupinstall "Development Tools" -y  # CentOS/RHEL

2. 数据库系统安装

2.1 MySQL/MariaDB

# Ubuntu/Debian
sudo apt install mariadb-server mariadb-client libmariadb-dev -y

# CentOS/RHEL
sudo yum install mariadb-server mariadb-devel -y

# 安全配置
sudo mysql_secure_installation

2.2 PostgreSQL

# Ubuntu/Debian
sudo apt install postgresql postgresql-contrib libpq-dev -y

# CentOS/RHEL
sudo yum install postgresql-server postgresql-devel -y
sudo postgresql-setup --initdb

2.3 MongoDB

# Ubuntu/Debian
sudo apt install mongodb mongodb-server libmongoc-dev -y

# CentOS/RHEL
sudo yum install mongodb-org mongodb-org-server -y

2.4 SQLite

sudo apt install sqlite3 libsqlite3-dev -y  # Ubuntu/Debian
sudo yum install sqlite sqlite-devel -y  # CentOS/RHEL

3. 编程语言环境配置

3.1 Python

sudo apt install python3 python3-pip python3-dev -y
pip3 install mysql-connector-python psycopg2-binary pymongo sqlalchemy

3.2 Node.js

curl -fsSL https://deb.nodesource.com/setup_lts.x | sudo -E bash -
sudo apt install nodejs -y
npm install mysql2 pg mongodb sqlite3 sequelize

3.3 Java

sudo apt install default-jdk maven -y
# 添加JDBC驱动到项目依赖

4. 数据库管理工具

4.1 命令行工具

sudo apt install mycli pgcli -y  # MySQL和PostgreSQL的增强CLI

4.2 图形界面工具

# DBeaver (跨平台)
sudo snap install dbeaver-ce

# MySQL Workbench
sudo apt install mysql-workbench -y

5. 性能优化配置

5.1 调整MySQL配置

sudo nano /etc/mysql/my.cnf

添加/修改以下内容：

[mysqld]
innodb_buffer_pool_size = 1G  # 根据内存调整
innodb_log_file_size = 256M
max_connections = 100

5.2 调整PostgreSQL配置

sudo nano /etc/postgresql/12/main/postgresql.conf

修改：

shared_buffers = 1GB
effective_cache_size = 3GB
work_mem = 16MB
maintenance_work_mem = 256MB

6. 安全配置

6.1 防火墙设置

sudo ufw allow 3306/tcp  # MySQL
sudo ufw allow 5432/tcp  # PostgreSQL
sudo ufw enable

6.2 数据库用户权限

-- MySQL示例
CREATE USER 'devuser'@'localhost' IDENTIFIED BY 'securepassword';
GRANT ALL PRIVILEGES ON *.* TO 'devuser'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

7. 备份与恢复

7.1 MySQL备份

mysqldump -u root -p --all-databases > full_backup.sql

7.2 PostgreSQL备份

pg_dumpall -U postgres > full_backup.sql

8. 监控工具

8.1 安装Prometheus + Grafana

# Prometheus
wget https://github.com/prometheus/prometheus/releases/download/v2.30.3/prometheus-2.30.3.linux-amd64.tar.gz
tar xvfz prometheus-*.tar.gz
cd prometheus-*

# Grafana
sudo apt install grafana -y
sudo systemctl start grafana-server

9. 容器化选项 (Docker)

9.1 使用Docker运行数据库

# MySQL
docker run --name mysql-dev -e MYSQL_ROOT_PASSWORD=my-secret-pw -p 3306:3306 -d mysql:latest

# PostgreSQL
docker run --name postgres-dev -e POSTGRES_PASSWORD=mysecretpassword -p 5432:5432 -d postgres:latest

10. 测试连接

10.1 Python测试脚本

import mysql.connector

db = mysql.connector.connect(
    host="localhost",
    user="devuser",
    password="securepassword"
)

print(db)

完成以上配置后，您的Linux系统已准备好进行各种数据库编程开发工作。根据具体项目需求，您可能需要进一步调整配置或安装额外的库和工具。