Comprehensive FAQ covering common topics and queries related to MySQL installation, usage, and troubleshooting for DevOps environments.
# Check if MySQL service is running
sudo systemctl status mysql # Debian/Ubuntu
sudo systemctl status mysqld # RHEL-family
# Check MySQL version
mysql --version
# Connect to MySQL server
mysql -u root -p
# Stop MySQL service
sudo systemctl stop mysql # Debian/Ubuntu
sudo systemctl stop mysqld # RHEL-family
# Start MySQL in safe mode (without grant tables)
sudo mysqld_safe --skip-grant-tables &
# Connect to MySQL without password
mysql -u root
# Reset the root password (MySQL 8.0+)
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_strong_password';
FLUSH PRIVILEGES;
# Exit and restart MySQL normally
sudo systemctl start mysql # Debian/Ubuntu
sudo systemctl start mysqld # RHEL-family
# Standard connection with password prompt
mysql -u username -p
# Connection with password in command (not recommended for security)
mysql -u username -p'password'
# Connection to remote server
mysql -h hostname -u username -p
# Connection using socket (for local connections)
mysql -u root -S /var/run/mysqld/mysqld.sock
# Connection with SSL
mysql -u username -p --ssl-mode=REQUIRED
# Check if MySQL is listening on the expected port
sudo netstat -tlnp | grep :3306
# Or using ss
sudo ss -tlnp | grep :3306
# Check MySQL error logs
sudo tail -f /var/log/mysql/error.log
# Test connection locally
mysql -u root -p -h localhost
mysql -u root -p -h 127.0.0.1
# Check bind address in configuration
mysql -u root -p -e "SHOW VARIABLES LIKE 'bind_address';"
-- Create a new database with character set specification
CREATE DATABASE my_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Use a particular database
USE my_database;
-- Show all databases
SHOW DATABASES;
-- Drop a database (be careful!)
DROP DATABASE my_database;
-- Create a new table with common best practices
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_username (username),
INDEX idx_email (email)
);
-- View the structure of a table
DESCRIBE users;
-- Or
SHOW CREATE TABLE users;
-- Add a column to an existing table
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- Modify a column
ALTER TABLE users MODIFY COLUMN phone VARCHAR(15);
-- Drop a column
ALTER TABLE users DROP COLUMN phone;
-- Insert data into a table
INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');
-- Insert multiple rows
INSERT INTO users (username, email) VALUES
('jane_doe', 'jane@example.com'),
('bob_smith', 'bob@example.com');
-- Retrieve data from a table
SELECT * FROM users;
SELECT username, email FROM users WHERE id = 1;
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;
-- Update data in a table
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;
-- Delete data from a table
DELETE FROM users WHERE id = 1;
-- Safe delete with transaction
START TRANSACTION;
DELETE FROM users WHERE condition;
-- Verify the change
SELECT COUNT(*) FROM users;
-- Commit if correct, rollback if wrong
COMMIT;
-- Or
ROLLBACK;
-- Enable slow query log temporarily
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- Log queries taking more than 1 second
-- View current slow query log settings
SHOW VARIABLES LIKE 'slow_query%';
-- Check for queries not using indexes
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';
-- Check table status and statistics
SHOW TABLE STATUS LIKE 'table_name';
-- Analyze table for optimization suggestions
ANALYZE TABLE table_name;
-- Optimize table (reorganizes data and rebuilds indexes)
OPTIMIZE TABLE table_name;
-- Check table for corruption
CHECK TABLE table_name;
-- Repair table if corrupted
REPAIR TABLE table_name;
-- Show existing indexes
SHOW INDEX FROM table_name;
-- Create an index
CREATE INDEX idx_column_name ON table_name (column_name);
-- Create a composite index
CREATE INDEX idx_composite ON table_name (col1, col2);
-- Create a unique index
CREATE UNIQUE INDEX idx_unique ON table_name (column_name);
-- Drop an index
DROP INDEX idx_column_name ON table_name;
# Full backup of all databases
mysqldump -u root -p --all-databases > full_backup.sql
# Backup specific database
mysqldump -u root -p database_name > database_backup.sql
# Backup specific tables
mysqldump -u root -p database_name table1 table2 > tables_backup.sql
# Backup with consistent snapshot (for InnoDB)
mysqldump -u root -p --single-transaction --routines --triggers database_name > backup.sql
# Compressed backup
mysqldump -u root -p database_name | gzip > database_backup.sql.gz
# Restore from backup file
mysql -u root -p database_name < database_backup.sql
# Restore compressed backup
gunzip < database_backup.sql.gz | mysql -u root -p database_name
# Restore all databases
mysql -u root -p < full_backup.sql
-- Create a new user
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'strong_password';
-- Create user that can connect from any host (use carefully)
CREATE USER 'newuser'@'%' IDENTIFIED BY 'strong_password';
-- Create user that can connect from specific subnet
CREATE USER 'newuser'@'192.168.1.%' IDENTIFIED BY 'strong_password';
-- Grant privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'newuser'@'localhost';
-- Grant all privileges on specific database
GRANT ALL PRIVILEGES ON database_name.* TO 'newuser'@'localhost';
-- Grant specific privileges on all databases (use carefully)
GRANT SELECT, INSERT ON *.* TO 'newuser'@'localhost';
-- Show user privileges
SHOW GRANTS FOR 'username'@'hostname';
-- Revoke privileges
REVOKE INSERT ON database_name.* FROM 'newuser'@'localhost';
-- Refresh privileges after changes
FLUSH PRIVILEGES;
-- Drop user
DROP USER 'username'@'hostname';
-- Show current connections
SHOW PROCESSLIST;
-- Show detailed connection information
SELECT * FROM information_schema.PROCESSLIST;
-- Kill a specific connection
KILL CONNECTION connection_id;
-- Show all variables
SHOW VARIABLES;
-- Show specific variable
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
-- Show status information
SHOW STATUS;
-- Show specific status
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';
-- Change variable for current session
SET SESSION max_heap_table_size = 64*1024*1024;
-- Change variable globally (affects new connections)
SET GLOBAL max_heap_table_size = 64*1024*1024;
-- Persist variable change (MySQL 8.0+)
SET PERSIST max_heap_table_size = 64*1024*1024;
SELECT User, Host, account_locked FROM mysql.user;mysql -u root -p --socket=/var/run/mysqld/mysqld.sockUSE database_name;-- Check current connections
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';
-- Check buffer pool efficiency
SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';
-- Check query cache (if enabled in older versions)
SHOW STATUS LIKE 'Qcache%';
-- Monitor slow queries
SHOW STATUS LIKE 'Slow_queries';
-- Update table statistics (important for query optimizer)
ANALYZE TABLE table_name;
-- Check for fragmentation
CHECK TABLE table_name;
-- Clean up temporary files
DELETE FROM mysql.general_log WHERE event_time < DATE_SUB(NOW(), INTERVAL 7 DAY);
-- Rotate binary logs manually
FLUSH LOGS;
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);
# Connect to MySQL inside a running container
docker exec -it container_name mysql -u root -p
# Execute a command inside the container
docker exec container_name mysql -u root -p -e "SHOW DATABASES;"
# Import data into container
docker exec -i container_name mysql -u root -p database_name < dump.sql
# Export data from container
docker exec container_name mysqldump -u root -p database_name > dump.sql