Setting Up MySQL/MariaDB on Ubuntu

sudo apt update
sudo apt install mariadb-server -y

# Start and enable service
sudo systemctl start mariadb
sudo systemctl enable mariadb
sudo systemctl status mariadb

sudo mysql_secure_installation

# Login to MySQL
sudo mysql

# Create database
CREATE DATABASE myapp_db;

# Create user with password
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'strong_password';

# Grant privileges
GRANT ALL PRIVILEGES ON myapp_db.* TO 'myapp_user'@'localhost';

# Flush privileges
FLUSH PRIVILEGES;

# Exit
EXIT;

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

[mysqld]
max_connections = 200
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
query_cache_size = 64M
tmp_table_size = 64M
max_heap_table_size = 64M

# Restart MySQL
sudo systemctl restart mariadb

# Edit MySQL config
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

# Change bind-address
bind-address = 0.0.0.0

# Restart
sudo systemctl restart mariadb

# Create remote user
sudo mysql
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'strong_password';
GRANT ALL PRIVILEGES ON myapp_db.* TO 'remote_user'@'%';
FLUSH PRIVILEGES;
EXIT;

# Open firewall (be specific with IP if possible)
sudo ufw allow from 203.0.113.0/24 to any port 3306

# Backup single database
mysqldump -u root -p myapp_db > myapp_db_backup.sql

# Backup all databases
mysqldump -u root -p --all-databases > all_databases_backup.sql

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

# Restore 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

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

#!/bin/bash
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y-%m-%d_%H-%M-%S)
mkdir -p $BACKUP_DIR
mysqldump -u root -p'your_password' --all-databases | gzip > $BACKUP_DIR/all_databases_$DATE.sql.gz
# Keep only last 7 days
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

# Make executable
sudo chmod +x /usr/local/bin/mysql-backup.sh

# Add to crontab (daily at 2 AM)
sudo crontab -e
# Add line:
0 2 * * * /usr/local/bin/mysql-backup.sh