Mariadb Optimizasyonu (my.cnf) Apache + Nginx & Usedir.conf

4. MARIADB OPTİMİZASYONU (my.cnf)

4.1. MariaDB Konfigürasyonu

Dosya Konumu: /etc/mysql/mariadb.conf.d/50-server.cnf (Ubuntu) veya /etc/my.cnf (AlmaLinux)

# =====================================================
# PROFESYONEL HOSTING - MARIADB 10.11 ENTERPRISE
# VDS Sunucu (8GB RAM varsayılarak)
# =====================================================

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4
prompt = '\u@\h [\d]> '

[mysqld]

# -----------------------------------------------------
# TEMEL AYARLAR
# -----------------------------------------------------
user = mysql
pid-file = /run/mysqld/mysqld.pid
socket = /run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
skip-external-locking
skip-name-resolve

# -----------------------------------------------------
# KARAKTER SETİ (Türkçe/Emoji desteği)
# -----------------------------------------------------
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init-connect = 'SET NAMES utf8mb4'

# -----------------------------------------------------
# InnoDB - ANA PERFORMANS (RAM'in %70'i)
# 8GB RAM için ~6GB, 16GB RAM için ~12GB
# -----------------------------------------------------
innodb_buffer_pool_size = 6G
innodb_buffer_pool_instances = 6
innodb_old_blocks_time = 1000

# -----------------------------------------------------
# InnoDB LOG DOSYALARI
# -----------------------------------------------------
innodb_log_file_size = 1G
innodb_log_files_in_group = 3
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2

# -----------------------------------------------------
# I/O PERFORMANSI (SSD için)
# -----------------------------------------------------
innodb_flush_method = O_DIRECT_NO_FSYNC
innodb_io_capacity = 5000
innodb_io_capacity_max = 10000
innodb_read_io_threads = 32
innodb_write_io_threads = 32
innodb_flush_neighbors = 0

# -----------------------------------------------------
# THREAD YÖNETİMİ
# -----------------------------------------------------
thread_handling = pool-of-threads
thread_pool_size = 16
thread_cache_size = 256
max_connections = 500
max_user_connections = 50
max_connect_errors = 100000

# -----------------------------------------------------
# TABLO ÖNBELLEĞİ
# -----------------------------------------------------
table_open_cache = 8000
table_definition_cache = 4000
open_files_limit = 20000

# -----------------------------------------------------
# GEÇİCİ TABLOLAR
# -----------------------------------------------------
tmp_table_size = 64M
max_heap_table_size = 64M

# -----------------------------------------------------
# SORT & JOIN
# -----------------------------------------------------
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 4M

# -----------------------------------------------------
# GÜVENLİK
# -----------------------------------------------------
local_infile = 0
skip-symbolic-links
sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

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

# -----------------------------------------------------
# REPLICATION (master-slave için)
# -----------------------------------------------------
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 7
max_binlog_size = 512M
binlog_format = ROW

# -----------------------------------------------------
# PERFORMANCE SCHEMA (izleme için)
# -----------------------------------------------------
performance_schema = ON
performance_schema_consumer_events_statements_current = ON

4.2. MariaDB'yi Yeniden Başlat

systemctl restart mariadb
mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"  # Kontrol

---

5. APACHE + NGINX & USDIR.CONF

5.1. Plesk'te Web Sunucusu Modu

Plesk panelden:

Araçlar ve Ayarlar > Web Sunucusu Ayarları

• "Apache + nginx (statik içerikler için ters vekil sunucu)" seç
• "Smart static files processing" aktifleştir

5.2. usdir.conf - Domain Bazlı Apache Yapılandırması

Dosya Konumu: /var/www/vhosts/system/domainadi.com/conf/usdir.conf

# =====================================================
# USDIR.CONF - Domain Bazlı Apache Yapılandırması
# Profesyonel Hosting için Enterprise Ayarlar
# =====================================================

# -----------------------------------------------------
# 1. TEMEL GÜVENLİK
# -----------------------------------------------------

# Sunucu imzasını gizle
ServerSignature Off
ServerTokens Prod

# Dizin listeleme kapalı
<Directory /var/www/vhosts/domainadi.com/httpdocs>
    Options -Indexes +FollowSymLinks
    AllowOverride All
    Require all granted
</Directory>

# -----------------------------------------------------
# 2. HASSAS DOSYALARI ENGELLE
# -----------------------------------------------------
<FilesMatch "^(wp-config\.php|\.htaccess|\.htpasswd|\.git|\.svn|\.env|composer\.json|package\.json|Gruntfile\.js|gulpfile\.js|README\.md|CHANGELOG\.md|error_log|\.sql|\.bak|\.old|\.log|\.yml|\.xmlrpc)$">
    Require all denied
</FilesMatch>

# XML-RPC koruması (WordPress)
<Files xmlrpc.php>
    Require all denied
</Files>

# -----------------------------------------------------
# 3. PERFORMANS - SIKIŞTIRMA (GZIP)
# -----------------------------------------------------
<IfModule mod_deflate.c>
    AddOutputFilterByType DEFLATE text/plain text/html text/xml
    AddOutputFilterByType DEFLATE text/css text/javascript
    AddOutputFilterByType DEFLATE application/xml application/xhtml+xml
    AddOutputFilterByType DEFLATE application/rss+xml application/javascript
    AddOutputFilterByType DEFLATE application/json application/font-woff
    AddOutputFilterByType DEFLATE application/font-woff2 image/svg+xml
    
    BrowserMatch ^Mozilla/4 gzip-only-text/html
    BrowserMatch ^Mozilla/4\.0[678] no-gzip
    BrowserMatch \bMSIE !no-gzip !gzip-only-text/html
    Header append Vary User-Agent
</IfModule>

# -----------------------------------------------------
# 4. ÖNBELLEK (CACHE)
# -----------------------------------------------------
<IfModule mod_expires.c>
    ExpiresActive On
    ExpiresDefault "access plus 1 month"
    ExpiresByType text/html "access plus 1 hour"
    ExpiresByType text/css "access plus 1 week"
    ExpiresByType text/javascript "access plus 1 week"
    ExpiresByType image/gif "access plus 1 year"
    ExpiresByType image/png "access plus 1 year"
    ExpiresByType image/jpeg "access plus 1 year"
    ExpiresByType image/webp "access plus 1 year"
    ExpiresByType application/font-woff "access plus 1 year"
    ExpiresByType application/font-woff2 "access plus 1 year"
</IfModule>

# -----------------------------------------------------
# 5. GÜVENLİK BAŞLIKLARI (Security Headers)
# -----------------------------------------------------
<IfModule mod_headers.c>
    Header always set X-XSS-Protection "1; mode=block"
    Header always set X-Content-Type-Options "nosniff"
    Header always set X-Frame-Options "SAMEORIGIN"
    Header always set Referrer-Policy "strict-origin-when-cross-origin"
    Header always set Permissions-Policy "geolocation=(), microphone=(), camera=()"
    
    # CSP - İçerik Güvenlik Politikası (siteye göre ayarla)
    Header always set Content-Security-Policy "default-src 'self'; script-src 'self' 'unsafe-inline' 'unsafe-eval' https:; style-src 'self' 'unsafe-inline' https:; img-src 'self' data: https:; font-src 'self' https:;"
    
    # HSTS - HTTPS zorunluluğu
    Header always set Strict-Transport-Security "max-age=31536000; includeSubDomains; preload"
</IfModule>

# -----------------------------------------------------
# 6. KÖTÜ BOTLARI ENGELLE
# -----------------------------------------------------
<IfModule mod_rewrite.c>
    RewriteEngine On
    RewriteCond %{HTTP_USER_AGENT} (ahrefs|semrush|majestic|rogerbot|dotbot|mj12bot|blexbot|exabot) [NC]
    RewriteRule .* - [F,L]
</IfModule>

# -----------------------------------------------------
# 7. HOTLINK KORUMASI
# -----------------------------------------------------
RewriteCond %{HTTP_REFERER} !^$
RewriteCond %{HTTP_REFERER} !^https?://(www\.)?domainadi\.com [NC]
RewriteCond %{HTTP_REFERER} !^https?://(www\.)?google\.com [NC]
RewriteCond %{HTTP_REFERER} !^https?://(www\.)?bing\.com [NC]
RewriteRule \.(jpg|jpeg|png|gif|webp|svg)$ - [NC,F,L]

# -----------------------------------------------------
# 8. TRAFİK LİMİTLERİ (DDoS Koruması)
# -----------------------------------------------------
<IfModule mod_ratelimit.c>
    <Location />
        SetOutputFilter RATE_LIMIT
        SetEnv rate-limit 400
    </Location>
</IfModule>

5.3. Tüm Domainlere Otomatik Uygulama

Her domain için manuel yapmak yerine, Plesk'te şablon oluşturun:

# Ana şablonu oluştur
mkdir -p /usr/local/psa/admin/conf/templates/custom/domain/

# usdir.conf içeriğini şablona ekle
cat > /usr/local/psa/admin/conf/templates/custom/domain/usdir.php <<'EOF'
<?php
echo "# =====================================================
# Otomatik oluşturuldu - Değiştirmeyin!
# =====================================================

# Sunucu imzasını gizle
ServerSignature Off
ServerTokens Prod

# Dizin listeleme kapalı
<Directory /var/www/vhosts/<?php echo $domain->getName(); ?>/httpdocs>
    Options -Indexes +FollowSymLinks
    AllowOverride All
    Require all granted
</Directory>

# Hassas dosyaları engelle
<FilesMatch \"^(wp-config\.php|\.htaccess|\.env|\.git|\.sql|\.bak)\">
    Require all denied
</FilesMatch>

# XML-RPC koruması
<Files xmlrpc.php>
    Require all denied
</Files>

# GZIP sıkıştırma
<IfModule mod_deflate.c>
    AddOutputFilterByType DEFLATE text/html text/css text/javascript
    AddOutputFilterByType DEFLATE application/javascript application/json
</IfModule>

# Önbellek
<IfModule mod_expires.c>
    ExpiresActive On
    ExpiresByType image/jpg \"access plus 1 year\"
    ExpiresByType image/jpeg \"access plus 1 year\"
    ExpiresByType image/gif \"access plus 1 year\"
    ExpiresByType image/png \"access plus 1 year\"
    ExpiresByType text/css \"access plus 1 month\"
    ExpiresByType application/javascript \"access plus 1 month\"
</IfModule>

# Güvenlik başlıkları
<IfModule mod_headers.c>
    Header always set X-XSS-Protection \"1; mode=block\"
    Header always set X-Content-Type-Options \"nosniff\"
    Header always set X-Frame-Options \"SAMEORIGIN\"
    Header always set Strict-Transport-Security \"max-age=31536000; includeSubDomains; preload\"
</IfModule>
";
?>
EOF

# Şablonu yeniden oluştur
plesk sbin httpdmng --update-domain all

---

Not: Bu yapılandırma dosyaları profesyonel hosting ortamları için hazırlanmıştır. Kendi sunucu kaynaklarınıza göre innodb_buffer_pool_size değerini ayarlamayı unutmayın. Değişiklik yapmadan önce mutlaka yedek alın! 🔒