データベース

# Install MySQL Server
sudo apt update
sudo apt install mysql-server
 
# Check service status
sudo systemctl status mysql
 
# Enable auto-start on boot
sudo systemctl enable mysql

# Install MariaDB Server
sudo apt update
sudo apt install mariadb-server
 
# Check service status
sudo systemctl status mariadb
 
# Enable auto-start on boot
sudo systemctl enable mariadb

# Both MySQL and MariaDB use the same command
sudo mysql_secure_installation

# Log in to MySQL/MariaDB (Ubuntu defaults to unix_socket authentication)
sudo mysql
 
# Or log in with a password
mysql -u root -p

-- List all databases
SHOW DATABASES;
 
-- Create a database
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 
-- Use a database
USE myapp;
 
-- Create a user and set password
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'StrongPassword123!';
 
-- Grant privileges (limited to a specific database)
GRANT ALL PRIVILEGES ON myapp.* TO 'appuser'@'localhost';
 
-- Flush privileges
FLUSH PRIVILEGES;
 
-- View user privileges
SHOW GRANTS FOR 'appuser'@'localhost';
 
-- Create a table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- Insert data
INSERT INTO users (username, email) VALUES ('zhang', 'zhang@example.com');
 
-- Query data
SELECT * FROM users;
 
-- View table structure
DESCRIBE users;
 
-- Drop a user
DROP USER 'appuser'@'localhost';
 
-- Drop a database
DROP DATABASE myapp;
 
-- Exit
EXIT;

# Edit MySQL configuration
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Or MariaDB configuration
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

# Allow connections from all IPs (in production, bind to a specific IP)
bind-address = 0.0.0.0

-- Create a user that can connect from any IP
CREATE USER 'remoteuser'@'%' IDENTIFIED BY 'StrongPassword123!';
GRANT ALL PRIVILEGES ON myapp.* TO 'remoteuser'@'%';
FLUSH PRIVILEGES;
 
-- Or restrict to a specific IP
CREATE USER 'remoteuser'@'192.168.1.100' IDENTIFIED BY 'StrongPassword123!';
GRANT ALL PRIVILEGES ON myapp.* TO 'remoteuser'@'192.168.1.100';
FLUSH PRIVILEGES;

# Restart the service for changes to take effect
sudo systemctl restart mysql
# Or
sudo systemctl restart mariadb
 
# If the firewall is enabled, allow the port
sudo ufw allow 3306/tcp

[mysqld]
# Character set (utf8mb4 recommended to support emoji and other characters)
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
 
# InnoDB buffer pool (recommended: 50%-70% of physical memory)
innodb_buffer_pool_size = 1G
 
# Maximum connections
max_connections = 200
 
# Slow query log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# Restart the service after changes
sudo systemctl restart mysql

# Install PostgreSQL
sudo apt update
sudo apt install postgresql postgresql-client
 
# Check service status
sudo systemctl status postgresql
 
# Check installed version
psql --version

# Switch to the postgres user
sudo -u postgres psql

-- Create a new database user
CREATE USER appuser WITH PASSWORD 'StrongPassword123!';
 
-- Create a database and assign an owner
CREATE DATABASE myapp OWNER appuser;
 
-- Grant connection privileges
GRANT ALL PRIVILEGES ON DATABASE myapp TO appuser;
 
-- List all databases
\l
 
-- List all users
\du
 
-- Switch database
\c myapp
 
-- Exit
\q

# Create a new user using the postgres account
sudo -u postgres createuser --interactive appuser
 
# Create a database
sudo -u postgres createdb -O appuser myapp
 
# Connect with the new user
psql -U appuser -d myapp -h localhost

# Connect to a database
psql -U appuser -d myapp -h localhost

-- Create a table
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    tags TEXT[],
    metadata JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
 
-- Insert data (note: PostgreSQL supports array and JSON types)
INSERT INTO articles (title, content, tags, metadata)
VALUES (
    'Ubuntu 26.04 Released',
    'The new version brings many improvements...',
    ARRAY['ubuntu', 'linux', 'release'],
    '{"author": "admin", "views": 0}'::jsonb
);
 
-- Query data
SELECT * FROM articles;
 
-- JSON query
SELECT title FROM articles WHERE metadata->>'author' = 'admin';
 
-- Array query
SELECT title FROM articles WHERE 'ubuntu' = ANY(tags);
 
-- View table structure
\d articles
 
-- List all tables
\dt
 
-- View table sizes
\dt+

# Edit the main configuration file
sudo nano /etc/postgresql/18/main/postgresql.conf

# Change the listen address
listen_addresses = '*'

# Edit the client authentication configuration
sudo nano /etc/postgresql/18/main/pg_hba.conf

# Allow a specific subnet via password authentication
host    all    all    192.168.1.0/24    scram-sha-256

# Or allow all IPs (not recommended for production)
host    all    all    0.0.0.0/0    scram-sha-256

# Restart PostgreSQL
sudo systemctl restart postgresql
 
# Allow through firewall
sudo ufw allow 5432/tcp

# Install Valkey (recommended on 26.04)
sudo apt update
sudo apt install valkey-server
 
# If you need Redis, you can install it separately (coexists with Valkey; swap the redis prefix into the commands)
# sudo apt install redis-server
 
# Check service status
sudo systemctl status valkey-server
 
# Verify it is working properly
valkey-cli ping
# Should output: PONG

# Connect to the Valkey CLI (redis-cli is also available as a compatibility alias)
valkey-cli

# String operations
SET name "Ubuntu Fan"
GET name

# Set a key with expiration (60 seconds)
SET session:abc123 "user_data" EX 60

# Check remaining TTL
TTL session:abc123

# Hash operations (similar to objects)
HSET user:1 name "John" email "john@example.com" age "30"
HGET user:1 name
HGETALL user:1

# List operations
LPUSH tasks "task1" "task2" "task3"
LRANGE tasks 0 -1
RPOP tasks

# Set operations
SADD online_users "user1" "user2" "user3"
SMEMBERS online_users
SISMEMBER online_users "user1"

# Sorted set (leaderboard scenario)
ZADD leaderboard 100 "player1" 200 "player2" 150 "player3"
ZREVRANGE leaderboard 0 -1 WITHSCORES

# List all keys
KEYS *

# Delete a key
DEL name

# Check key type
TYPE user:1

# View server info
INFO

# View memory usage
INFO memory

# Exit
QUIT

# Edit Valkey configuration (the Redis-compatible package uses /etc/redis/redis.conf)
sudo nano /etc/valkey/valkey.conf

# RDB persistence rules (enabled by default)
# Save if at least 1 change in 3600 seconds
save 3600 1
# Save if at least 100 changes in 300 seconds
save 300 100
# Save if at least 10000 changes in 60 seconds
save 60 10000
 
# RDB file location
dir /var/lib/valkey
dbfilename dump.rdb

# Enable AOF
appendonly yes
 
# AOF sync policy
# always: Sync on every write (safest, but slowest)
# everysec: Sync once per second (recommended, balances safety and performance)
# no: Let the OS decide (fastest, but may lose data)
appendfsync everysec
 
# AOF filename
appendfilename "appendonly.aof"

# Restart Valkey for changes to take effect
sudo systemctl restart valkey-server

sudo nano /etc/valkey/valkey.conf

# Set password
requirepass YourStrongRedisPassword
 
# Bind address (defaults to local access only)
bind 127.0.0.1 ::1
 
# Disable dangerous commands
rename-command FLUSHDB ""
rename-command FLUSHALL ""
rename-command CONFIG ""

# Connect with password
valkey-cli -a YourStrongRedisPassword
 
# Or authenticate after connecting
valkey-cli
AUTH YourStrongRedisPassword

# Install SQLite
sudo apt install sqlite3
 
# Create/open a database file
sqlite3 mydb.sqlite
 
# Operate within the SQLite prompt

-- Create a table
CREATE TABLE notes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    content TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
 
-- Insert data
INSERT INTO notes (title, content) VALUES ('Test Note', 'This is a test entry');
 
-- Query
SELECT * FROM notes;
 
-- List all tables
.tables
 
-- View table structure
.schema notes
 
-- Display results with column names
.headers on
.mode column
 
-- Export as SQL
.dump
 
-- Export as CSV
.mode csv
.output notes.csv
SELECT * FROM notes;
.output stdout
 
-- Exit
.quit

# Back up a single database
mysqldump -u root -p myapp > myapp_backup_$(date +%Y%m%d).sql
 
# Back up all databases
mysqldump -u root -p --all-databases > all_databases_$(date +%Y%m%d).sql
 
# Compressed backup
mysqldump -u root -p myapp | gzip > myapp_backup_$(date +%Y%m%d).sql.gz
 
# Restore a database
mysql -u root -p myapp < myapp_backup_20260324.sql
 
# Restore a compressed backup
gunzip < myapp_backup_20260324.sql.gz | mysql -u root -p myapp

# Back up a single database
sudo -u postgres pg_dump myapp > myapp_backup_$(date +%Y%m%d).sql
 
# Back up all databases
sudo -u postgres pg_dumpall > all_databases_$(date +%Y%m%d).sql
 
# Custom format backup (supports parallel restore)
sudo -u postgres pg_dump -Fc myapp > myapp_backup_$(date +%Y%m%d).dump
 
# Restore SQL format backup
sudo -u postgres psql myapp < myapp_backup_20260324.sql
 
# Restore custom format backup
sudo -u postgres pg_restore -d myapp myapp_backup_20260324.dump

# Manually trigger RDB snapshot
valkey-cli BGSAVE
 
# Copy the RDB file
sudo cp /var/lib/valkey/dump.rdb /backup/valkey_$(date +%Y%m%d).rdb
 
# Restore: stop Valkey, replace dump.rdb, restart
sudo systemctl stop valkey-server
sudo cp /backup/valkey_20260324.rdb /var/lib/valkey/dump.rdb
sudo chown valkey:valkey /var/lib/valkey/dump.rdb
sudo systemctl start valkey-server

# Create backup script
sudo nano /usr/local/bin/db-backup.sh

#!/bin/bash
BACKUP_DIR="/backup/databases"
DATE=$(date +%Y%m%d_%H%M%S)
mkdir -p "$BACKUP_DIR"
 
# MySQL/MariaDB backup
mysqldump -u root --all-databases | gzip > "$BACKUP_DIR/mysql_$DATE.sql.gz"
 
# PostgreSQL backup
sudo -u postgres pg_dumpall | gzip > "$BACKUP_DIR/postgres_$DATE.sql.gz"
 
# Valkey backup
valkey-cli BGSAVE
sleep 5
cp /var/lib/valkey/dump.rdb "$BACKUP_DIR/valkey_$DATE.rdb"
 
# Delete backups older than 30 days
find "$BACKUP_DIR" -type f -mtime +30 -delete
 
echo "Backup complete: $DATE"

# Set execute permission
sudo chmod +x /usr/local/bin/db-backup.sh
 
# Add cron job (runs daily at 3 AM)
sudo crontab -e
# Add the following line:
# 0 3 * * * /usr/local/bin/db-backup.sh >> /var/log/db-backup.log 2>&1

# --- MySQL / MariaDB ---
sudo systemctl start mysql        # Start
sudo systemctl stop mysql         # Stop
sudo systemctl restart mysql      # Restart
sudo systemctl status mysql       # Check status
sudo mysql                        # Log in
 
# --- PostgreSQL ---
sudo systemctl start postgresql   # Start
sudo systemctl stop postgresql    # Stop
sudo systemctl restart postgresql # Restart
sudo systemctl status postgresql  # Check status
sudo -u postgres psql             # Log in
 
# --- Valkey ---
sudo systemctl start valkey-server   # Start
sudo systemctl stop valkey-server    # Stop
sudo systemctl restart valkey-server # Restart
sudo systemctl status valkey-server  # Check status
valkey-cli                           # Log in
valkey-cli INFO                      # View info
valkey-cli DBSIZE                    # View key count