MySQL Commands

MySQL is the database server that stores all Zal Ultra data including subscribers, accounting, and configurations. These commands help you manage the MySQL service.

🔴 HIGH RISK - CRITICAL DATA

MySQL contains ALL your business data. Improper commands can cause:

Permanent data loss (subscribers, payments, invoices)
Database corruption
Complete system failure

ALWAYS backup before any database operations!

Service Management
Database Access
Backup & Restore
Performance Monitoring
Log Management
Troubleshooting

Service Management

Check MySQL Status

bash

sudo systemctl status mysql

Start MySQL

bash

sudo systemctl start mysql

Stop MySQL

🔴 COMPLETE OUTAGE

Stopping MySQL will make Zal Ultra completely non-functional. No data can be read or written.

bash

sudo systemctl stop mysql

Restart MySQL

🔴 SERVICE DISRUPTION

Restarting MySQL will:

Disconnect all active database connections
Cause temporary errors in Zal Ultra
May take 10-60 seconds depending on data size

Only restart during maintenance windows!

bash

sudo systemctl restart mysql

Reload MySQL

bash

sudo systemctl reload mysql

Purpose: Reloads configuration without full restart. Limited use cases.

Enable on Boot

bash

sudo systemctl enable mysql

Database Access

Connect to MySQL

bash

# As root
sudo mysql

# With password
mysql -u root -p

# Connect to specific database
mysql -u root -p zal_ultra

Show Databases

sql

SHOW DATABASES;

Show Tables

sql

USE zal_ultra;
SHOW TABLES;

Check Table Size

sql

SELECT 
    table_name AS 'Table',
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema = 'zal_ultra'
ORDER BY (data_length + index_length) DESC
LIMIT 20;

Check Database Size

sql

SELECT 
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;

Backup & Restore

Full Database Backup

✅ RECOMMENDED

Always backup before any major changes or updates.

bash

# Backup single database
mysqldump -u root -p zal_ultra > /backup/zal_ultra_$(date +%Y%m%d_%H%M%S).sql

# Backup with compression
mysqldump -u root -p zal_ultra | gzip > /backup/zal_ultra_$(date +%Y%m%d_%H%M%S).sql.gz

# Backup all databases
mysqldump -u root -p --all-databases > /backup/all_databases_$(date +%Y%m%d_%H%M%S).sql

Backup Specific Tables

bash

mysqldump -u root -p zal_ultra subscribers packages users > /backup/critical_tables.sql

Restore Database

🔴 DATA OVERWRITE

Restoring will OVERWRITE existing data. Make sure you have the correct backup file.

bash

# Restore from SQL file
mysql -u root -p zal_ultra < /backup/zal_ultra_backup.sql

# Restore from compressed file
gunzip < /backup/zal_ultra_backup.sql.gz | mysql -u root -p zal_ultra

Automated Backup Script

bash

#!/bin/bash
# Save as /root/backup_mysql.sh

BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7

# Create backup
mysqldump -u root zal_ultra | gzip > $BACKUP_DIR/zal_ultra_$DATE.sql.gz

# Remove old backups
find $BACKUP_DIR -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete

echo "Backup completed: zal_ultra_$DATE.sql.gz"

Add to cron:

bash

# Daily backup at 3 AM
0 3 * * * /root/backup_mysql.sh

Performance Monitoring

Show Running Processes

sql

SHOW PROCESSLIST;

Show Full Processes (with queries)

sql

SHOW FULL PROCESSLIST;

Kill a Query

🟠 CAUTION

Only kill queries if they are stuck or causing issues.

sql

KILL <process_id>;

Check MySQL Variables

sql

SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'query_cache%';

Check MySQL Status

sql

SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Slow_queries';
SHOW STATUS LIKE 'Questions';

Check InnoDB Status

sql

SHOW ENGINE INNODB STATUS\G

Log Management

View MySQL Error Log

bash

sudo tail -f /var/log/mysql/error.log

View Slow Query Log

bash

sudo tail -f /var/log/mysql/mysql-slow.log

Enable Slow Query Log

sql

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

Check Log File Sizes

bash

sudo du -sh /var/log/mysql/*

Clear MySQL Logs

🟠 CAUTION

This removes log history. Back up if needed.

bash

# Truncate error log
sudo truncate -s 0 /var/log/mysql/error.log

# Rotate binary logs (if enabled)
mysql -u root -p -e "PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 7 DAY);"

Troubleshooting

Common Issues

MySQL Won't Start

bash

# Check error log
sudo tail -100 /var/log/mysql/error.log

# Check disk space
df -h

# Check permissions
sudo chown -R mysql:mysql /var/lib/mysql

Too Many Connections

bash

# Check current connections
mysql -u root -p -e "SHOW STATUS LIKE 'Threads_connected';"

# Increase max connections (temporary)
mysql -u root -p -e "SET GLOBAL max_connections = 500;"

# Permanent: edit /etc/mysql/mysql.conf.d/mysqld.cnf
max_connections = 500

Slow Queries

bash

# Enable slow query log
# Check /var/log/mysql/mysql-slow.log

# Analyze slow queries
mysqldumpslow /var/log/mysql/mysql-slow.log

Disk Full

bash

# Check disk usage
df -h

# Find large tables
mysql -u root -p -e "
SELECT table_name, ROUND(data_length/1024/1024,2) as 'Size MB'
FROM information_schema.tables
WHERE table_schema='zal_ultra'
ORDER BY data_length DESC LIMIT 10;"

# Clear old radacct data (if needed)
mysql -u root -p zal_ultra -e "
DELETE FROM radacct WHERE acctstarttime < DATE_SUB(NOW(), INTERVAL 90 DAY);"

Check MySQL Configuration

bash

# Main config file
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

# Check current config
mysql -u root -p -e "SHOW VARIABLES;" | grep -E "innodb_buffer|max_connections|query_cache"

Repair Table

🟠 CAUTION

Only use if table is corrupted.

sql

REPAIR TABLE table_name;

Optimize Table

sql

OPTIMIZE TABLE radacct;
OPTIMIZE TABLE subscribers;

Quick Reference Card

Action	Command
Check status	`sudo systemctl status mysql`
Start	`sudo systemctl start mysql`
Stop	`sudo systemctl stop mysql`
Restart	`sudo systemctl restart mysql`
Connect	`sudo mysql` or `mysql -u root -p`
Backup	`mysqldump -u root -p zal_ultra > backup.sql`
Restore	`mysql -u root -p zal_ultra < backup.sql`
View errors	`sudo tail -f /var/log/mysql/error.log`
Show processes	`SHOW PROCESSLIST;`
Config file	`/etc/mysql/mysql.conf.d/mysqld.cnf`

MySQL Commands ​

Table of Contents ​

Service Management ​

Check MySQL Status ​

Start MySQL ​

Stop MySQL ​

Restart MySQL ​

Reload MySQL ​

Enable on Boot ​

Database Access ​

Connect to MySQL ​

Show Databases ​

Show Tables ​

Check Table Size ​

Check Database Size ​

Backup & Restore ​

Full Database Backup ​

Backup Specific Tables ​

Restore Database ​

Automated Backup Script ​

Performance Monitoring ​

Show Running Processes ​

Show Full Processes (with queries) ​

Kill a Query ​

Check MySQL Variables ​

Check MySQL Status ​

Check InnoDB Status ​

Log Management ​

View MySQL Error Log ​

View Slow Query Log ​

Enable Slow Query Log ​

Check Log File Sizes ​

Clear MySQL Logs ​

Troubleshooting ​

Common Issues ​

MySQL Won't Start ​

Too Many Connections ​

Slow Queries ​

Disk Full ​

Check MySQL Configuration ​

Repair Table ​

Optimize Table ​

Quick Reference Card ​

MySQL Commands

Table of Contents

Service Management

Check MySQL Status

Start MySQL

Stop MySQL

Restart MySQL

Reload MySQL

Enable on Boot

Database Access

Connect to MySQL

Show Databases

Show Tables

Check Table Size

Check Database Size

Backup & Restore

Full Database Backup

Backup Specific Tables

Restore Database

Automated Backup Script

Performance Monitoring

Show Running Processes

Show Full Processes (with queries)

Kill a Query

Check MySQL Variables

Check MySQL Status

Check InnoDB Status

Log Management

View MySQL Error Log

View Slow Query Log

Enable Slow Query Log

Check Log File Sizes

Clear MySQL Logs

Troubleshooting

Common Issues

MySQL Won't Start

Too Many Connections

Slow Queries

Disk Full

Check MySQL Configuration

Repair Table

Optimize Table

Quick Reference Card