The MySQLTuner report shows you have 296 fragmented tables which can significantly impact database performance. Fragmentation occurs when tables undergo frequent updates/deletes, leaving unused space that MySQL still has to scan during operations.

[!!] Total fragmented tables: 296

To connect via Putty and optimize your tables:

1. Launch Putty and connect to your server
2. Authenticate with your MySQL credentials:

mysql -u username -p

Method 1: Optimize Specific Tables

For critical tables showing high fragmentation:

OPTIMIZE TABLE important_table1, important_table2;

Method 2: Optimize All Tables in a Database

To optimize all tables in your database:

mysqlcheck -o database_name -u username -p

Method 3: Automated Script for Multiple Databases

For servers with multiple databases:

#!/bin/bash
USER="username"
PASS="password"
HOST="localhost"

databases=mysql -u $USER -p$PASS -h $HOST -e "SHOW DATABASES;" | tr -d "| " | grep -v Database

for db in $databases; do
    if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] ; then
        echo "Optimizing database: $db"
        mysqlcheck -o $db -u $USER -p$PASS -h $HOST
    fi
done

• Locking: OPTIMIZE TABLE locks the table during operation
• Storage Engines: Works best with MyISAM (shown in your report as 1G of data)
• Timing: Schedule during low-traffic periods

Verify optimization results with:

ANALYZE TABLE table_name;
SHOW TABLE STATUS LIKE 'table_name';

The "Data_free" column shows unused space - it should decrease after optimization.

When MySQLTuner reports "Total fragmented tables: 296", it indicates your database contains tables with scattered data blocks. Fragmentation occurs after frequent INSERT, UPDATE, and DELETE operations, causing suboptimal storage patterns that impact:

• Query execution speed (additional disk seeks)
• Storage space utilization (unreclaimed empty blocks)
• Index traversal efficiency (non-contiguous index pages)

Connect via Putty and execute these commands sequentially:

# 1. Connect to MySQL with proper credentials
mysql -u root -p

# 2. Generate optimization statements for all fragmented tables
SELECT CONCAT('OPTIMIZE TABLE ', table_schema, '.', table_name, ';') 
FROM information_schema.tables 
WHERE table_schema NOT IN ('information_schema','mysql','performance_schema') 
AND engine IN ('MyISAM','InnoDB') 
INTO OUTFILE '/tmp/optimize_tables.sql';

# 3. Execute the generated script
source /tmp/optimize_tables.sql;

For large production databases:

# Optimize in batches during low-traffic periods
mysql -e "OPTIMIZE TABLE db1.large_table1, db1.large_table2" --batch --silent

# Monitor progress (another SSH session)
watch -n 60 "mysql -e 'SHOW PROCESSLIST' | grep -i optimize"

Automation example via cron:

# Weekly optimization for critical tables
0 3 * * 0 mysql -uadmin -p$(cat /etc/mysql/creds) -e "OPTIMIZE TABLE orders.order_items, logs.access_log"

For zero-downtime environments:

• pt-online-schema-change (Percona Toolkit)
• Master-replica rotation with optimized tables
• Logical dump/reload for extreme cases

Post-optimization check:

SELECT 
  table_schema, 
  table_name, 
  data_free / (data_length + index_length) * 100 AS frag_ratio
FROM information_schema.tables 
WHERE data_free > 0
ORDER BY frag_ratio DESC
LIMIT 10;