Vagrant — MySQL 8 on OEL 8

# -*- mode: ruby -*-
Vagrant.configure("2") do |config|
  config.vm.box      = "generic/oracle8"
  config.vm.hostname = "mysql8-lab"

  config.vm.network "private_network",  ip: "192.168.56.20"
  config.vm.network "forwarded_port",   guest: 3306, host: 13306

  # VirtualBox
  config.vm.provider "virtualbox" do |vb|
    vb.name   = "MySQL8-OEL8"
    vb.memory = 4096
    vb.cpus   = 2
    vb.customize ["modifyvm", :id, "--ioapic", "on"]
  end

  # Parallels
  config.vm.provider "parallels" do |prl|
    prl.name   = "MySQL8-OEL8"
    prl.memory = 4096
    prl.cpus   = 2
    prl.update_guest_tools = false
  end

  # Install MySQL 8
  config.vm.provision "shell", path: "scripts/install_mysql8.sh"
  config.vm.provision "shell", path: "scripts/configure_mysql8.sh"
end

#!/bin/bash
# scripts/install_mysql8.sh
set -e

MYSQL_ROOT_PASS="Root@123!"

echo "=== Installing MySQL 8.0 on OEL 8 ==="

# Disable OEL default MySQL module
dnf module disable -y mysql 2>/dev/null || true

# Add MySQL 8.0 Community repository
rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
dnf install -y https://dev.mysql.com/get/mysql80-community-release-el8-9.noarch.rpm
dnf module disable -y mysql

# Install MySQL Community Server
dnf install -y mysql-community-server mysql-community-client

# Start and enable MySQL
systemctl enable --now mysqld

echo "=== MySQL started, getting temp password ==="
TEMP_PASS=$(grep "temporary password" /var/log/mysqld.log | tail -1 | awk "{print $NF}")
echo "Temp password: $TEMP_PASS"

# Change root password and setup
mysql --connect-expired-password -u root -p"$TEMP_PASS" << EOF
ALTER USER 'root'@'localhost' IDENTIFIED BY '${MYSQL_ROOT_PASS}';
CREATE USER IF NOT EXISTS 'root'@'%' IDENTIFIED BY '${MYSQL_ROOT_PASS}';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
EOF

echo "=== MySQL 8 Installed. Root password: ${MYSQL_ROOT_PASS} ==="

#!/bin/bash
# scripts/configure_mysql8.sh
set -e

MYSQL_ROOT_PASS="Root@123!"

echo "=== Configuring MySQL 8 ==="

# Write optimized my.cnf
cat > /etc/my.cnf.d/custom.cnf << EOF
[mysqld]
# Basic Settings
bind-address           = 0.0.0.0
port                   = 3306
default_authentication_plugin = mysql_native_password

# InnoDB Settings
innodb_buffer_pool_size    = 1G
innodb_log_file_size       = 256M
innodb_flush_method        = O_DIRECT
innodb_flush_log_at_trx_commit = 1

# Connection Settings
max_connections            = 200
wait_timeout               = 600
interactive_timeout        = 600
max_allowed_packet         = 64M

# Logging
slow_query_log             = ON
slow_query_log_file        = /var/log/mysql/slow.log
long_query_time            = 2
log_queries_not_using_indexes = ON

# Binary Logging (for replication readiness)
server-id                  = 1
log_bin                    = /var/log/mysql/mysql-bin.log
binlog_format              = ROW
gtid_mode                  = ON
enforce_gtid_consistency   = ON
EOF

mkdir -p /var/log/mysql
chown mysql:mysql /var/log/mysql

systemctl restart mysqld

# Create monitor user (for ProxySQL)
mysql -u root -p"$MYSQL_ROOT_PASS" << EOF
CREATE USER IF NOT EXISTS 'monitor'@'%' IDENTIFIED BY 'Monitor@123!';
GRANT SELECT, REPLICATION CLIENT ON *.* TO 'monitor'@'%';

# Create sample database
CREATE DATABASE IF NOT EXISTS labdb;
USE labdb;
CREATE TABLE IF NOT EXISTS employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    dept VARCHAR(50),
    salary DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO employees (name, dept, salary) VALUES
    ('John Smith',    'Engineering', 75000),
    ('Jane Doe',      'Marketing',   65000),
    ('Bob Johnson',   'Engineering', 80000),
    ('Alice Brown',   'HR',          60000),
    ('Charlie Wilson','Engineering', 70000);
FLUSH PRIVILEGES;
EOF

echo "=== MySQL 8 Configured Successfully ==="
echo ""
echo "Connection details:"
echo "  Host: 192.168.56.20 (private) or 127.0.0.1:13306 (port forward)"
echo "  User: root"
echo "  Pass: ${MYSQL_ROOT_PASS}"
echo "  DB:   labdb"

# Start VM
vagrant up

# Connect from host via port forward
mysql -u root -pRoot@123! -h 127.0.0.1 -P 13306

# Or via private network
mysql -u root -pRoot@123! -h 192.168.56.20 -P 3306

# Inside VM
vagrant ssh
mysql -u root -pRoot@123!

# Test sample data
mysql -u root -pRoot@123! -e "SELECT * FROM labdb.employees;"

# Check MySQL version
mysql -u root -pRoot@123! -e "SELECT @@version;"

# Check configuration
mysql -u root -pRoot@123! -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"