一、安装数据库服务器(以MySQL和PostgreSQL为例)
sudo apt update
,确保系统获取最新软件包信息。sudo apt install mysql-server -y
,自动安装MySQL主程序及依赖。sudo mysql_secure_installation
,按提示设置root密码、删除匿名用户、禁止root远程登录、删除测试数据库。sudo systemctl start mysql
启动服务,sudo systemctl enable mysql
设置开机自动启动。sudo apt update
,同步软件包索引。sudo apt install postgresql postgresql-contrib -y
,安装PostgreSQL服务器及常用扩展(如pg_stat_statements
)。sudo systemctl start postgresql
启动服务,sudo systemctl enable postgresql
设置开机自启。sudo -u postgres psql
),输入\q
退出,确认命令行工具可用。二、基础配置(安全与权限管理)
sudo mysql -u root -p
),执行ALTER USER 'root'@'localhost' IDENTIFIED BY 'StrongPassword123!'
(替换为复杂密码)。sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
,找到bind-address = 127.0.0.1
(默认仅本地访问),若需远程访问可改为0.0.0.0
(需谨慎),保存后重启服务sudo systemctl restart mysql
。CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'AppPassword456';
创建用户,GRANT ALL PRIVILEGES ON my_database.* TO 'app_user'@'localhost';
授权指定数据库权限,FLUSH PRIVILEGES;
刷新权限表。sudo -u postgres psql
),执行ALTER USER postgres WITH PASSWORD 'SecurePostgresPass';
设置强密码。CREATE DATABASE mydb;
创建数据库,CREATE USER myuser WITH ENCRYPTED PASSWORD 'UserPass123';
创建用户,GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
授权,\q
退出。sudo nano /etc/postgresql/<version>/main/postgresql.conf
(如postgresql.conf
),修改max_connections = 100
(根据服务器资源调整最大连接数);编辑sudo nano /etc/postgresql/<version>/main/pg_hba.conf
,添加host all all 0.0.0.0/0 md5
(允许所有IP远程连接,需配合防火墙设置),保存后重启服务sudo systemctl restart postgresql
。三、服务管理
sudo systemctl start mysql
(启动)、sudo systemctl stop mysql
(停止)、sudo systemctl restart mysql
(重启)。sudo systemctl start postgresql
(启动)、sudo systemctl stop postgresql
(停止)、sudo systemctl restart postgresql
(重启)。sudo systemctl status mysql
(MySQL状态)、sudo systemctl status postgresql
(PostgreSQL状态),确认服务运行正常(显示“active (running)”)。四、常用操作命令
mysql -u root -p
(登录root账户)、mysql -u app_user -p
(登录普通用户)。psql -U postgres
(登录postgres用户)、psql -U myuser -d mydb -h localhost
(登录指定数据库)。mysqldump -u root -p --all-databases > full_backup.sql
(全量备份)、mysql -u root -p < backup.sql
(恢复)。pg_dump -U postgres --all-databases > full_backup.sql
(全量备份)、psql -U postgres < backup.sql
(恢复)。注意事项
sudo apt update && sudo apt upgrade
),修复安全漏洞。ufw
)放行数据库端口(MySQL:3306、PostgreSQL:5432),仅允许可信IP访问。