MySQL/MariaDB Database Setup for Web Application

MySQL/MariaDB Database Setup for Web Application

MySQL and MariaDB remain widely used choices for web applications—especially PHP/Laravel ecosystem, legacy project migrations, and when proven replication schema with readable docs needed.

Installation

MySQL 8.0 on Ubuntu:

apt install -y mysql-server
mysql_secure_installation

MariaDB 11.x (recommended for new projects—better performance, open source license):

curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | bash
apt install -y mariadb-server mariadb-client

Create database and user:

CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'myapp'@'localhost' IDENTIFIED BY 'strong_password';
GRANT ALL PRIVILEGES ON myapp.* TO 'myapp'@'localhost';
FLUSH PRIVILEGES;

utf8mb4 mandatory—true UTF-8 with emoji support. Old MySQL utf8—three-byte, causes Unicode issues beyond BMP.

Configuration my.cnf

[mysqld]
# Main settings (for 8 GB RAM)
innodb_buffer_pool_size = 5G          # 60-70% RAM
innodb_buffer_pool_instances = 4      # one per 1-2 GB
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2    # compromise reliability/speed
innodb_flush_method = O_DIRECT

# Connections
max_connections = 200
thread_cache_size = 32
table_open_cache = 4000

# Queries
query_cache_type = 0                  # query cache deprecated, disable
tmp_table_size = 64M
max_heap_table_size = 64M
sort_buffer_size = 4M
join_buffer_size = 4M

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

# Replication
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
expire_logs_days = 7

Indexes and Query Optimization

-- Composite indexes—order matters
-- Query: WHERE user_id = ? AND status = ? ORDER BY created_at DESC
CREATE INDEX idx_orders_user_status_date
ON orders(user_id, status, created_at DESC);

-- Covering index—answer from index without table access
CREATE INDEX idx_products_listing
ON products(category_id, is_active, price, id, name)
WHERE deleted_at IS NULL;

-- Check index usage
EXPLAIN SELECT * FROM orders
WHERE user_id = 123 AND status = 'completed'
ORDER BY created_at DESC LIMIT 10;

Backup Strategy

# Full backup
mysqldump -u root -p --all-databases --single-transaction > backup-$(date +%Y%m%d).sql

# Incremental via binary logs
mysqlbinlog /var/log/mysql/mysql-bin.000001 | mysql -u root -p

# Automated backup to remote
0 2 * * * mysqldump -u myapp -pPassword myapp | gzip > /backups/myapp-$(date +\%Y\%m\%d).sql.gz

Performance Tuning

Monitor slow queries:

# Enable in my.cnf, then analyze
mysqldumpslow /var/log/mysql/slow.log | head -20

Key metrics to watch:

• Threads_connected—active connections
• Questions—queries per second
• Slow_queries—queries > long_query_time
• InnoDB_buffer_pool_pages_dirty—data waiting flush

Replication (Master-Slave)

For HA, setup replication:

-- Master
CHANGE MASTER TO
  MASTER_HOST='master-ip',
  MASTER_USER='replication',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=155;

START SLAVE;
SHOW SLAVE STATUS\G

Timeline

Setup MySQL/MariaDB, user creation, basic optimization—2–3 hours. Backups, replication, monitoring—4–6 hours. Schema design, indexes, testing—depends on app complexity.