Installing MySQL on Alpine Linux

MySQL is one of the world’s most popular open-source relational database management systems. In Alpine Linux, we’ll use MariaDB, a drop-in replacement for MySQL that offers better performance and additional features. Let’s get started! 🚀

MariaDB vs MySQL

Alpine Linux packages MariaDB instead of MySQL because:

Full compatibility: MariaDB is a drop-in replacement
Better performance: Improved query optimizer
More features: Additional storage engines
Active development: Regular updates and improvements
Same commands: mysql, mysqldump, etc. work identically

Prerequisites

Before installation, ensure you have:

Alpine Linux system with root access
At least 512MB RAM (1GB+ recommended)
Sufficient disk space for databases
Basic understanding of SQL

Step 1: Install MariaDB

Update Package Repository

# Update package list
sudo apk update

# Upgrade existing packages
sudo apk upgrade

Install MariaDB Packages

# Install MariaDB server and client
sudo apk add mariadb mariadb-client

# Install additional useful packages
sudo apk add mariadb-server-utils mariadb-doc

Step 2: Initialize Database

Prepare MySQL System

# Install necessary database files
sudo mysql_install_db --user=mysql --datadir=/var/lib/mysql

# Set proper ownership
sudo chown -R mysql:mysql /var/lib/mysql

Configure System Settings

# Create MySQL run directory
sudo mkdir -p /run/mysqld
sudo chown mysql:mysql /run/mysqld

# Ensure proper permissions
sudo chmod 755 /run/mysqld

Step 3: Start MariaDB Service

Enable and Start Service

# Add MariaDB to startup services
sudo rc-update add mariadb default

# Start MariaDB service
sudo rc-service mariadb start

# Check service status
sudo rc-service mariadb status

Verify Installation

# Check if MySQL is running
ps aux | grep mysql

# Check listening port
netstat -tlnp | grep 3306

# Check version
mysql --version

Step 4: Secure MySQL Installation

Run Security Script

# Run mysql_secure_installation
sudo mysql_secure_installation

Follow the prompts:

Enter current root password (blank for new installation)
Set root password? Yes
Remove anonymous users? Yes
Disallow root login remotely? Yes (unless needed)
Remove test database? Yes
Reload privilege tables? Yes

Manual Security Steps

# Connect to MySQL as root
sudo mysql -u root -p

# Additional security measures

-- Remove anonymous users
DELETE FROM mysql.user WHERE User='';

-- Ensure root has password
UPDATE mysql.user SET Password=PASSWORD('YourStrongPassword') WHERE User='root';

-- Remove remote root access
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

-- Remove test database
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';

-- Reload privileges
FLUSH PRIVILEGES;

Step 5: Configure MySQL

Main Configuration File

# Edit MySQL configuration
sudo nano /etc/my.cnf.d/mariadb-server.cnf

Add optimized settings:

[mysqld]
# Basic Settings
user = mysql
port = 3306
datadir = /var/lib/mysql
socket = /run/mysqld/mysqld.sock
pid-file = /run/mysqld/mysqld.pid

# Character Set
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# Connection Settings
max_connections = 150
connect_timeout = 10
wait_timeout = 600
max_allowed_packet = 16M

# Cache and Buffers
query_cache_type = 1
query_cache_size = 16M
query_cache_limit = 1M
tmp_table_size = 32M
max_heap_table_size = 32M

# InnoDB Settings
innodb_buffer_pool_size = 256M
innodb_log_file_size = 64M
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2

# Logging
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# Security
bind-address = 127.0.0.1
skip-name-resolve

Create Log Directory

# Create log directory
sudo mkdir -p /var/log/mysql
sudo chown mysql:mysql /var/log/mysql
sudo chmod 755 /var/log/mysql

Restart with New Configuration

# Restart MariaDB
sudo rc-service mariadb restart

# Check for errors
sudo tail -f /var/log/mysql/error.log

Step 6: Create Databases and Users

Connect to MySQL

# Connect as root
mysql -u root -p

Create Database

-- Create new database
CREATE DATABASE myapp_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- List databases
SHOW DATABASES;

-- Use database
USE myapp_db;

Create User and Grant Permissions

-- Create new user
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'StrongPassword123!';

-- Grant all privileges on specific database
GRANT ALL PRIVILEGES ON myapp_db.* TO 'myapp_user'@'localhost';

-- Grant specific privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'myapp_user'@'localhost';

-- Create user with remote access
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'StrongPassword456!';
GRANT ALL PRIVILEGES ON myapp_db.* TO 'remote_user'@'%';

-- Apply changes
FLUSH PRIVILEGES;

-- View user privileges
SHOW GRANTS FOR 'myapp_user'@'localhost';

Step 7: Performance Tuning

Monitor Performance

-- Show current connections
SHOW PROCESSLIST;

-- Show status variables
SHOW STATUS LIKE 'Threads%';

-- Show InnoDB status
SHOW ENGINE INNODB STATUS;

-- Check slow queries
SELECT * FROM mysql.slow_log;

Optimize Tables

-- Analyze table
ANALYZE TABLE table_name;

-- Optimize table
OPTIMIZE TABLE table_name;

-- Check table
CHECK TABLE table_name;

-- Repair table if needed
REPAIR TABLE table_name;

Configure Performance Schema

-- Enable performance schema
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES';

-- View top queries by execution time
SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1000000000 AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

Step 8: Backup and Recovery

Backup Strategies

# Full database backup
mysqldump -u root -p --all-databases > all_databases_backup.sql

# Single database backup
mysqldump -u root -p myapp_db > myapp_db_backup.sql

# Backup with compression
mysqldump -u root -p myapp_db | gzip > myapp_db_backup.sql.gz

# Backup specific tables
mysqldump -u root -p myapp_db table1 table2 > tables_backup.sql

Automated Backup Script

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

Add backup script:

#!/bin/sh

# MySQL backup script
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
MYSQL_USER="root"
MYSQL_PASSWORD="YourRootPassword"

# Create backup directory
mkdir -p $BACKUP_DIR

# Backup all databases
mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD --all-databases --single-transaction --quick --lock-tables=false > $BACKUP_DIR/full_backup_$DATE.sql

# Compress backup
gzip $BACKUP_DIR/full_backup_$DATE.sql

# Remove backups older than 7 days
find $BACKUP_DIR -name "*.gz" -mtime +7 -delete

echo "Backup completed: $BACKUP_DIR/full_backup_$DATE.sql.gz"

Make executable and schedule:

# Make executable
sudo chmod +x /usr/local/bin/mysql_backup.sh

# Add to crontab (daily at 2 AM)
echo "0 2 * * * /usr/local/bin/mysql_backup.sh" | sudo crontab -

Restore from Backup

# Restore full backup
mysql -u root -p < all_databases_backup.sql

# Restore single database
mysql -u root -p myapp_db < myapp_db_backup.sql

# Restore compressed backup
gunzip < myapp_db_backup.sql.gz | mysql -u root -p myapp_db

Step 9: Remote Access Configuration

Enable Remote Connections

# Edit configuration
sudo nano /etc/my.cnf.d/mariadb-server.cnf

Change bind-address:

# Allow connections from any IP
bind-address = 0.0.0.0

# Or specific IP
bind-address = 192.168.1.100

Configure Firewall

# Allow MySQL port
sudo iptables -A INPUT -p tcp --dport 3306 -j ACCEPT

# Save firewall rules
sudo iptables-save > /etc/iptables/rules.v4

Test Remote Connection

# From remote machine
mysql -h server_ip -u remote_user -p

Step 10: Monitoring and Maintenance

Monitor Database Health

# Create monitoring script
sudo nano /usr/local/bin/mysql_monitor.sh

Add monitoring script:

#!/bin/sh

echo "=== MySQL Health Check ==="
echo "Date: $(date)"

# Check if MySQL is running
if pgrep mysqld > /dev/null; then
    echo "✓ MySQL is running"
else
    echo "✗ MySQL is not running"
    exit 1
fi

# Check connections
mysql -u root -pYourPassword -e "SHOW STATUS LIKE 'Threads_connected';"

# Check database sizes
mysql -u root -pYourPassword -e "
SELECT 
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
GROUP BY table_schema;"

# Check slow queries
echo "Recent slow queries:"
tail -5 /var/log/mysql/slow.log 2>/dev/null || echo "No slow query log"

Regular Maintenance Tasks

-- Weekly maintenance
FLUSH TABLES;
FLUSH LOGS;

-- Monthly maintenance
CHECK TABLE table_name;
ANALYZE TABLE table_name;
OPTIMIZE TABLE table_name;

Troubleshooting Common Issues

Connection Problems

# Check if MySQL is running
ps aux | grep mysql

# Check error log
tail -100 /var/log/mysql/error.log

# Test connection
mysql -u root -p -e "SELECT 1"

Permission Issues

-- Reset root password
sudo rc-service mariadb stop
sudo mysqld_safe --skip-grant-tables &
mysql -u root

UPDATE mysql.user SET Password=PASSWORD('NewPassword') WHERE User='root';
FLUSH PRIVILEGES;
exit;

sudo rc-service mariadb restart

Performance Problems

# Check system resources
top -p $(pgrep mysqld)

# Check MySQL processes
mysql -u root -p -e "SHOW PROCESSLIST"

# Enable slow query log
mysql -u root -p -e "SET GLOBAL slow_query_log = 'ON'"

Security Best Practices

Strong Passwords: Use complex passwords for all users
Limit Access: Grant minimum required privileges
Regular Updates: Keep MariaDB updated
Backup Encryption: Encrypt sensitive backups
SSL/TLS: Enable encrypted connections for remote access
Audit Logs: Enable logging for security monitoring

Conclusion

You’ve successfully installed and configured MySQL (MariaDB) on Alpine Linux! Your database server is now:

✅ Properly installed and initialized
✅ Secured with strong authentication
✅ Optimized for performance
✅ Configured with backup procedures
✅ Ready for production use

Remember to:

Regularly backup your databases
Monitor performance and logs
Keep the system updated
Follow security best practices

Happy database managing! 🗄️

Installing MySQL on Alpine Linux 🗄️

Table of Contents