MySQL Utilities

Essential MySQL Utilities

🔹 mysqldump - Database Backup

mysqldump is the most important utility for creating database backups. It exports database structure and data into SQL files that can be restored later. You can backup entire databases, specific tables, or multiple databases at once. Regular backups protect your data from loss.

# Backup a single database
mysqldump -u root -p mydatabase > mydatabase_backup.sql

# Backup specific tables
mysqldump -u root -p mydatabase users orders > tables_backup.sql

# Backup all databases
mysqldump -u root -p --all-databases > all_databases.sql

# Backup with compression
mysqldump -u root -p mydatabase | gzip > mydatabase.sql.gz

# Backup structure only (no data)
mysqldump -u root -p --no-data mydatabase > structure.sql

🔹 mysql - Command Line Client

The mysql client is an interactive shell for executing SQL commands directly from the terminal. It's perfect for quick queries, database administration, and running SQL scripts. You can connect to local or remote MySQL servers and execute any SQL statement interactively or in batch mode.

# Connect to MySQL
mysql -u root -p

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

# Execute SQL file
mysql -u root -p mydatabase < script.sql

# Execute single query
mysql -u root -p -e "SELECT * FROM users" mydatabase

# Connect to remote server
mysql -h 192.168.1.100 -u root -p mydatabase

🔹 mysqlcheck - Database Maintenance

mysqlcheck performs table maintenance operations including checking for errors, repairing corrupted tables, and optimizing table performance. It's essential for keeping your database healthy and running efficiently. You can check individual tables or entire databases with a single command.

# Check all tables in a database
mysqlcheck -u root -p mydatabase

# Check and repair tables
mysqlcheck -u root -p --auto-repair mydatabase

# Optimize all tables
mysqlcheck -u root -p --optimize mydatabase

# Analyze tables for better performance
mysqlcheck -u root -p --analyze mydatabase

# Check all databases
mysqlcheck -u root -p --all-databases

🔹 mysqladmin - Server Administration

mysqladmin is used for administrative tasks like checking server status, creating and dropping databases, managing users, and monitoring server performance. It provides quick access to common administrative operations without entering the MySQL shell. Perfect for automation and scripting server management tasks.

# Check server status
mysqladmin -u root -p status

# Show server variables
mysqladmin -u root -p variables

# Create a new database
mysqladmin -u root -p create newdatabase

# Drop a database
mysqladmin -u root -p drop olddatabase

# Check if server is alive
mysqladmin -u root -p ping

# Show active processes
mysqladmin -u root -p processlist

# Refresh/flush privileges
mysqladmin -u root -p flush-privileges

🔹 Importing and Restoring Data

Restoring databases from backup files is crucial for disaster recovery and data migration. The mysql client can import SQL files created by mysqldump or other sources. You can restore entire databases or execute specific SQL scripts to populate tables with data.

# Restore a database from backup
mysql -u root -p mydatabase < mydatabase_backup.sql

# Restore compressed backup
gunzip < mydatabase.sql.gz | mysql -u root -p mydatabase

# Import data into existing database
mysql -u root -p mydatabase < data_import.sql

# Create database and import
mysql -u root -p -e "CREATE DATABASE newdb"
mysql -u root -p newdb < backup.sql