MySQL & MariaDB Database Administration: The Practical Guide Every Linux/DevOps Engineer Should Know
Most people learn MySQL by memorizing SQL commands.
That’s not database administration.
A real DBA or DevOps engineer needs to know five things:
Install it. Secure it. Manage it. Back it up. Recover it.
And when production gets slow?
Find out why.
This guide covers the practical MySQL and MariaDB skills you need to work with databases on Linux servers—and explain them confidently in an interview.
1. Start With Installation
On Ubuntu:
sudo apt update
sudo apt install mysql-server
Then secure the installation:
sudo mysql_secure_installation
This is one of the first things you should do after installation.
Typical hardening includes:
- Removing anonymous users
- Preventing remote root login
- Removing the test database
- Setting appropriate root authentication
For MariaDB:
sudo apt install mariadb-server
sudo mysql_secure_installation
Check the service:
sudo systemctl status mysql
Start, stop, restart, and enable it:
sudo systemctl start mysql
sudo systemctl stop mysql
sudo systemctl restart mysql
sudo systemctl enable mysql
Check the version:
mysql --version
Login:
mysql -u root -p
On Ubuntu, root may use socket authentication, so this can also work:
sudo mysql
2. Users Are a Security Boundary
Never give every application full database access.
Create users with the minimum permissions they need.
CREATE USER 'appuser'@'localhost'
IDENTIFIED BY 'StrongP@ssw0rd!';
For a remote application:
CREATE USER 'appuser'@'192.168.1.%'
IDENTIFIED BY 'StrongP@ssw0rd!';
Then grant only what is required:
GRANT SELECT, INSERT, UPDATE, DELETE
ON mydb.*
TO 'appuser'@'localhost';
Need full access to one database?
GRANT ALL PRIVILEGES
ON mydb.*
TO 'appuser'@'localhost';
Check permissions:
SHOW GRANTS FOR 'appuser'@'localhost';
Change a password:
ALTER USER 'appuser'@'localhost'
IDENTIFIED BY 'NewP@ssw0rd!';
Remove the user:
DROP USER 'appuser'@'localhost';
The important interview concept
MySQL permissions are not simply about the username.
They are based on:
'user'@'host'
These are different accounts:
'appuser'@'localhost'
'appuser'@'%'
'appuser'@'192.168.1.%'
That distinction matters enormously when troubleshooting:
“Access denied.”
3. Database Administration Starts With Visibility
Before changing anything, inspect the system.
List databases:
SHOW DATABASES;
Create one:
CREATE DATABASE myapp_production
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
Select it:
USE myapp_production;
List tables:
SHOW TABLES;
Inspect a table:
DESCRIBE users;
For the complete table definition:
SHOW CREATE TABLE users;
4. Know Your Database Size
Disk usage becomes a production problem faster than most people expect.
Check table sizes:
SELECT
table_name,
ROUND(data_length/1024/1024, 2) AS 'Data (MB)',
ROUND(index_length/1024/1024, 2) AS 'Index (MB)',
ROUND(
(data_length + index_length)/1024/1024,
2
) AS 'Total (MB)'
FROM information_schema.tables
WHERE table_schema = 'myapp_production'
ORDER BY (data_length + index_length) DESC;
Check database sizes:
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;
This gives you something much more useful than:
“The database seems large.”
You can identify which database and which tables are consuming the space.
5. CRUD Is Basic—But You Must Know It
Insert:
INSERT INTO users (name, email)
VALUES ('John', 'john@example.com');
Read:
SELECT * FROM users;
Filter:
SELECT *
FROM users
WHERE email = 'john@example.com';
Get recent records:
SELECT *
FROM users
ORDER BY created_at DESC
LIMIT 10;
Count:
SELECT COUNT(*)
FROM users;
Update:
UPDATE users
SET name = 'John Doe'
WHERE id = 1;
Delete:
DELETE FROM users
WHERE id = 1;
Production rule
Be extremely careful with:
UPDATE users SET ...
and:
DELETE FROM users;
Without a WHERE clause, you may modify or delete every row.
6. Learn to Troubleshoot Live Queries
When the database is slow, don't guess.
Start here:
SHOW PROCESSLIST;
For more detail:
SHOW FULL PROCESSLIST;
Look for:
- Long-running queries
- Locked queries
- Waiting transactions
- Too many connections
- Queries stuck in unusual states
If a query is genuinely stuck and you understand the consequences:
KILL process_id;
The important skill isn't memorizing KILL.
It's knowing why you're killing the connection.
7. Backups Are Not Optional
A database without a tested backup is a disaster waiting for a date.
Single database:
mysqldump -u root -p myapp_production \
> /backups/myapp_production_$(date +%Y%m%d).sql
Compressed:
mysqldump -u root -p myapp_production |
gzip > /backups/myapp_production_$(date +%Y%m%d).sql.gz
All databases:
mysqldump -u root -p --all-databases \
> /backups/all_databases_$(date +%Y%m%d).sql
Specific tables:
mysqldump -u root -p myapp_production users orders \
> /backups/tables_backup.sql
Schema only:
mysqldump -u root -p --no-data myapp_production \
> /backups/schema_only.sql
Data only:
mysqldump -u root -p --no-create-info myapp_production \
> /backups/data_only.sql
Include routines, triggers, and events:
mysqldump -u root -p \
--routines \
--triggers \
--events \
myapp_production \
> /backups/full_backup.sql
For larger InnoDB databases:
mysqldump -u root -p \
--single-transaction \
--quick \
--lock-tables=false \
myapp_production |
gzip > /backups/myapp_production_$(date +%Y%m%d).sql.gz
Remember this
Backup ≠ recovery.
A backup becomes valuable only when you can successfully restore it.
8. Restore Like You Mean It
Restore an SQL dump:
mysql -u root -p myapp_production \
< /backups/myapp_production_20240101.sql
Compressed backup:
gunzip < /backups/myapp_production_20240101.sql.gz |
mysql -u root -p myapp_production
Restore all databases:
mysql -u root -p \
< /backups/all_databases_20240101.sql
If the database doesn't exist:
mysql -u root -p -e \
"CREATE DATABASE IF NOT EXISTS myapp_production
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci"
Then restore:
mysql -u root -p myapp_production \
< /backups/myapp_production_20240101.sql
A good administrator doesn't merely say:
“We have backups.”
They can answer:
“How long does restoration take, where are the backups stored, and when was the last restore test?”
9. Automate Backups
Manual backups eventually become forgotten backups.
A simple script can automate them:
#!/bin/bash
DATE=$(date +%Y-%m-%d_%H-%M)
BACKUP_DIR="/backups/mysql"
RETENTION_DAYS=30
DB_USER="backup_user"
DB_PASS="secure_password"
mkdir -p "$BACKUP_DIR"
DATABASES=$(mysql \
-u "$DB_USER" \
-p"$DB_PASS" \
-e "SHOW DATABASES" \
-s --skip-column-names |
grep -Ev "(information_schema|performance_schema|sys|mysql)")
for DB in $DATABASES; do
echo "Backing up $DB..."
mysqldump \
-u "$DB_USER" \
-p"$DB_PASS" \
--single-transaction \
--quick \
--routines \
--triggers \
"$DB" |
gzip > "$BACKUP_DIR/${DB}_${DATE}.sql.gz"
echo "Done: ${DB}_${DATE}.sql.gz"
done
find "$BACKUP_DIR" \
-name "*.sql.gz" \
-mtime +$RETENTION_DAYS \
-delete
echo "Backup completed at $DATE"
Make it executable:
chmod +x /usr/local/bin/mysql_backup.sh
Schedule it:
crontab -e
Example:
0 2 * * * /usr/local/bin/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1
Now the database gets backed up every day at 2 AM.
10. Performance Tuning: Don't Change Random Variables
One of the most important MySQL tuning parameters is:
innodb_buffer_pool_size
The buffer pool caches InnoDB data and indexes in memory.
For a dedicated database server, a common starting point is roughly 50–70% of available RAM, but the correct value depends on the workload and what else runs on the machine.
Example:
[mysqld]
max_connections = 200
wait_timeout = 600
interactive_timeout = 600
innodb_buffer_pool_size = 1G
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
tmp_table_size = 64M
max_heap_table_size = 64M
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
bind-address = 127.0.0.1
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
Important caution
Don't blindly copy production values from a tutorial.
A 1 GB buffer pool makes sense on one server and is completely wrong on another.
Tune based on workload, RAM, concurrency, and measurements.
11. Find Slow Queries
Enable the slow query log:
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
Then inspect:
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';
The goal isn't simply:
“Find queries taking more than 2 seconds.”
The goal is:
Understand why they're slow.
12. EXPLAIN Is One of Your Best Friends
Suppose this query is slow:
SELECT *
FROM users
WHERE email = 'john@example.com';
Run:
EXPLAIN
SELECT *
FROM users
WHERE email = 'john@example.com';
On supported MySQL versions:
EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email = 'john@example.com';
Look for issues such as:
- Full table scans
- Poor index selection
- Excessive rows examined
- Bad join strategies
Check existing indexes:
SHOW INDEX FROM users;
Add an index when appropriate:
CREATE INDEX idx_email
ON users(email);
Another example:
ALTER TABLE users
ADD INDEX idx_created(created_at);
The principle is simple:
Don't optimize the query you think is slow. Measure the query that is actually slow.
13. Remote MySQL Access
By default, MySQL is often configured for local access.
To permit remote connections, modify:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
For example:
bind-address = 0.0.0.0
But don't stop there.
Opening MySQL to the entire internet is a bad idea.
Create a restricted user:
CREATE USER 'remote_user'@'192.168.1.%'
IDENTIFIED BY 'StrongP@ss!';
Grant only the required database permissions:
GRANT ALL PRIVILEGES
ON mydb.*
TO 'remote_user'@'192.168.1.%';
Restrict the firewall:
sudo ufw allow from 192.168.1.0/24 to any port 3306
Restart:
sudo systemctl restart mysql
Better security principle
Instead of:
Internet → MySQL:3306
prefer something like:
Application Server
↓
Private Network
↓
MySQL Server:3306
Database ports should be exposed only where necessary.
14. Common Production Problems
ERROR 1045: Access denied
Check:
SELECT user, host, plugin
FROM mysql.user;
Then:
SHOW GRANTS FOR 'username'@'host';
Remember:
user@localhost
is not necessarily the same account as:
user@%
Can't connect to socket
Check:
sudo systemctl status mysql
Then:
sudo systemctl start mysql
If necessary, verify the socket path and server configuration.
Too many connections
Check:
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';
Then investigate:
- Connection leaks
- Application pooling
- Long-running sessions
- Incorrect application configuration
Don't automatically increase max_connections.
More connections also mean more memory pressure.
Slow queries
Start with:
SHOW FULL PROCESSLIST;
Then:
EXPLAIN SELECT ...;
Check indexes:
SHOW INDEX FROM users;
And inspect the slow query log.
Disk full
Check:
df -h
Then investigate:
- MySQL logs
- Binary logs
- Old backups
- Large tables
- Temporary files
A full filesystem can quickly turn a database problem into a complete application outage.
MySQL won't start
Check:
sudo systemctl status mysql
Then inspect logs:
sudo tail -f /var/log/mysql/error.log
Also validate configuration before restarting after configuration changes.
15. MySQL vs MariaDB
MariaDB began as a fork of MySQL and remains highly compatible with MySQL in many common use cases.
But don't assume they are identical.
Over time, the two projects have diverged in:
- Features
- Storage engines
- Authentication
- Optimizer behavior
- System variables
- Replication capabilities
- Version-specific syntax
For an interview, a good answer is:
“MariaDB originated as a MySQL fork and maintains substantial compatibility, but modern MySQL and MariaDB have diverged, so I always verify version-specific behavior before migrating or configuring production systems.”
That's much stronger than:
“They're basically the same.”
16. The Interview Questions You Should Be Ready For
How do you back up MySQL?
mysqldump -u root -p mydb |
gzip > backup.sql.gz
How do you restore it?
gunzip < backup.sql.gz |
mysql -u root -p mydb
What is InnoDB?
InnoDB is the primary transactional storage engine used by modern MySQL installations.
It supports features such as:
- Transactions
- Row-level locking
- Foreign keys
- Crash recovery
What is innodb_buffer_pool_size?
It's the memory area InnoDB uses to cache data and indexes.
For a dedicated database server, it is often one of the most important memory-related tuning parameters.
How do you find slow queries?
Use:
Slow Query Log
↓
Identify expensive queries
↓
EXPLAIN / EXPLAIN ANALYZE
↓
Indexes / query optimization
↓
Measure again
What port does MySQL normally use?
3306
How do you check database size?
Query:
information_schema.tables
and calculate:
data_length + index_length
How do you secure MySQL?
A strong answer:
“I would run the secure installation process, remove anonymous users and unnecessary test databases, restrict root access, create least-privilege application accounts, restrict network access to port 3306, use strong authentication, protect backups, and monitor logs.”
The Mental Model
Don't memorize 100 MySQL commands.
Remember the workflow:
INSTALL
↓
SECURE
↓
CREATE USERS
↓
GRANT LEAST PRIVILEGE
↓
CREATE DATABASE
↓
MONITOR
↓
BACKUP
↓
TEST RESTORE
↓
TUNE
↓
TROUBLESHOOT
That's database administration.
And this is the bigger DevOps lesson:
A database administrator isn't paid to know commands.
They're paid to prevent data loss, reduce downtime, control access, diagnose performance problems, and recover when something goes wrong.
Learn the commands.
But master the reasoning behind them.
Part 4: MySQL & MariaDB Database Administration
4.1 Installation & Setup
# Install MySQL
sudo apt update
sudo apt install mysql-server
sudo mysql_secure_installation # ALWAYS run this after install
# Answer: Set root password, remove anonymous users, disable remote root login, remove test DB
# Install MariaDB (drop-in MySQL replacement)
sudo apt install mariadb-server
sudo mysql_secure_installation
# Service management
sudo systemctl start mysql
sudo systemctl stop mysql
sudo systemctl restart mysql
sudo systemctl status mysql
sudo systemctl enable mysql
# Check version
mysql --version
# Login
mysql -u root -p # Login as root with password prompt
mysql -u root # On Ubuntu, root uses socket auth by default
sudo mysql # If socket auth is configured
mysql -u username -p database_name # Login to specific database
mysql -h 192.168.1.100 -u user -p # Remote connection
4.2 User & Permission Management
-- View all users
SELECT user, host, plugin FROM mysql.user;
-- Create a new user
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'StrongP@ssw0rd!';
CREATE USER 'appuser'@'%' IDENTIFIED BY 'StrongP@ssw0rd!'; -- Allow from any host
CREATE USER 'appuser'@'192.168.1.%' IDENTIFIED BY 'StrongP@ssw0rd!'; -- From subnet
-- Grant permissions
GRANT ALL PRIVILEGES ON mydb.* TO 'appuser'@'localhost'; -- All on specific DB
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'appuser'@'localhost'; -- Specific
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION; -- Super admin
-- Revoke permissions
REVOKE ALL PRIVILEGES ON mydb.* FROM 'appuser'@'localhost';
-- Show grants for user
SHOW GRANTS FOR 'appuser'@'localhost';
-- Change password
ALTER USER 'appuser'@'localhost' IDENTIFIED BY 'NewP@ssw0rd!';
-- Delete user
DROP USER 'appuser'@'localhost';
-- Apply changes
FLUSH PRIVILEGES;
4.3 Database Operations
-- Show all databases
SHOW DATABASES;
-- Create database
CREATE DATABASE myapp_production CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Use database
USE myapp_production;
-- Show tables
SHOW TABLES;
-- Describe table structure
DESCRIBE users;
SHOW CREATE TABLE users;
-- Show table sizes
SELECT table_name,
ROUND(data_length/1024/1024, 2) AS 'Data (MB)',
ROUND(index_length/1024/1024, 2) AS 'Index (MB)',
ROUND((data_length + index_length)/1024/1024, 2) AS 'Total (MB)'
FROM information_schema.tables
WHERE table_schema = 'myapp_production'
ORDER BY (data_length + index_length) DESC;
-- Show database sizes
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;
-- Basic CRUD Operations
-- Create
INSERT INTO users (name, email) VALUES ('John', 'john@example.com');
-- Read
SELECT * FROM users;
SELECT * FROM users WHERE email = 'john@example.com';
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;
SELECT COUNT(*) FROM users;
-- Update
UPDATE users SET name = 'John Doe' WHERE id = 1;
-- Delete
DELETE FROM users WHERE id = 1;
-- Show running queries
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;
-- Kill a stuck query
KILL process_id;
-- Check server status
SHOW STATUS;
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE '%buffer%';
4.4 Backup & Restore (CRITICAL)
Backup Methods
# Single database backup
mysqldump -u root -p myapp_production > /backups/myapp_production_$(date +%Y%m%d).sql
# Single database with compression
mysqldump -u root -p myapp_production | gzip > /backups/myapp_production_$(date +%Y%m%d).sql.gz
# ALL databases backup
mysqldump -u root -p --all-databases > /backups/all_databases_$(date +%Y%m%d).sql
# Specific tables only
mysqldump -u root -p myapp_production users orders > /backups/tables_backup.sql
# Structure only (no data)
mysqldump -u root -p --no-data myapp_production > /backups/schema_only.sql
# Data only (no structure)
mysqldump -u root -p --no-create-info myapp_production > /backups/data_only.sql
# With routines, triggers, events
mysqldump -u root -p --routines --triggers --events myapp_production > /backups/full_backup.sql
# Large databases - add options for reliability
mysqldump -u root -p --single-transaction --quick --lock-tables=false myapp_production | gzip > /backups/myapp_production_$(date +%Y%m%d).sql.gz
Restore Methods
# Restore from SQL file
mysql -u root -p myapp_production < /backups/myapp_production_20240101.sql
# Restore from compressed file
gunzip < /backups/myapp_production_20240101.sql.gz | mysql -u root -p myapp_production
# Restore all databases
mysql -u root -p < /backups/all_databases_20240101.sql
# Create database first if it doesn't exist
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS myapp_production CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
mysql -u root -p myapp_production < /backups/myapp_production_20240101.sql
Automated Backup Script
#!/bin/bash
# /usr/local/bin/mysql_backup.sh
DATE=$(date +%Y-%m-%d_%H-%M)
BACKUP_DIR="/backups/mysql"
RETENTION_DAYS=30
DB_USER="backup_user"
DB_PASS="secure_password"
# Create backup directory
mkdir -p $BACKUP_DIR
# Get list of all databases (excluding system databases)
DATABASES=$(mysql -u $DB_USER -p$DB_PASS -e "SHOW DATABASES" -s --skip-column-names | grep -Ev "(information_schema|performance_schema|sys|mysql)")
# Backup each database
for DB in $DATABASES; do
echo "Backing up $DB..."
mysqldump -u $DB_USER -p$DB_PASS --single-transaction --quick --routines --triggers $DB | gzip > $BACKUP_DIR/${DB}_${DATE}.sql.gz
echo "Done: ${DB}_${DATE}.sql.gz"
done
# Delete old backups
find $BACKUP_DIR -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
echo "Backup completed at $DATE"
# Make executable and add to cron
chmod +x /usr/local/bin/mysql_backup.sh
# crontab -e
# 0 2 * * * /usr/local/bin/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1
4.5 MySQL Performance Tuning
Key Configuration (/etc/mysql/mysql.conf.d/mysqld.cnf)
[mysqld]
# Connection settings
max_connections = 200
wait_timeout = 600
interactive_timeout = 600
# InnoDB settings (most important)
innodb_buffer_pool_size = 1G # 50-70% of total RAM for dedicated DB server
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2 # Better performance (1 = safest)
innodb_flush_method = O_DIRECT
# Query cache (MySQL 5.7, removed in 8.0)
# query_cache_type = 1
# query_cache_size = 64M
# Temporary tables
tmp_table_size = 64M
max_heap_table_size = 64M
# Logging
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2 # Log queries taking > 2 seconds
# Binary logging (for replication)
# log_bin = /var/log/mysql/mysql-bin.log
# server_id = 1
# expire_logs_days = 14
# Security
bind-address = 127.0.0.1 # Only allow local connections
# bind-address = 0.0.0.0 # Allow remote connections (use with firewall)
# Character set
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
Performance Monitoring
-- Check slow queries
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';
-- Check connection usage
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';
-- Check InnoDB buffer pool usage
SHOW STATUS LIKE 'Innodb_buffer_pool%';
-- Explain a query (find performance issues)
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'john@example.com';
-- Check indexes
SHOW INDEX FROM users;
-- Add index for performance
CREATE INDEX idx_email ON users(email);
ALTER TABLE users ADD INDEX idx_created (created_at);
-- Optimize table (after large deletes)
OPTIMIZE TABLE users;
-- Repair table
REPAIR TABLE users;
-- Check table integrity
CHECK TABLE users;
4.6 MySQL Remote Access Setup
# Step 1: Edit MySQL config
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Change: bind-address = 0.0.0.0
# Step 2: Create user with remote access
mysql -u root -p
CREATE USER 'remote_user'@'192.168.1.%' IDENTIFIED BY 'StrongP@ss!';
GRANT ALL PRIVILEGES ON mydb.* TO 'remote_user'@'192.168.1.%';
FLUSH PRIVILEGES;
# Step 3: Open firewall port
sudo ufw allow from 192.168.1.0/24 to any port 3306
# Step 4: Restart MySQL
sudo systemctl restart mysql
4.7 Common Issues & Fixes
| Issue | Cause | Fix |
|---|---|---|
ERROR 1045: Access denied |
Wrong credentials or host | Check user@host, password, grants |
ERROR 2002: Can't connect to socket |
MySQL not running or wrong socket |
systemctl start mysql, check socket path |
Too many connections |
Connection limit reached | Increase max_connections, check for leaks, add connection pooling |
Table is marked as crashed |
Corrupted table | REPAIR TABLE tablename |
Disk full |
No space for MySQL | Clear old logs, backups, increase disk |
Slow queries |
Missing indexes, bad queries | Enable slow query log, use EXPLAIN, add indexes |
Lock wait timeout exceeded |
Long-running transactions | Find and kill blocking queries with SHOW PROCESSLIST
|
| MySQL won't start | Config error or corrupt files | Check /var/log/mysql/error.log
|
Interview Q&A: MySQL/MariaDB
| Question | Answer |
|---|---|
| How do you backup a database? | `mysqldump -u root -p dbname \ |
| How do you restore a database? | {% raw %}`gunzip < backup.sql.gz \ |
| Difference between MySQL and MariaDB? | MariaDB is a fork of MySQL, community-driven, mostly compatible. Created by MySQL founder |
| What is InnoDB? | Default storage engine. Supports transactions, row-level locking, foreign keys |
What is {% raw %}innodb_buffer_pool_size? |
Memory area for caching data and indexes. Most important MySQL tuning parameter. Set to 50-70% of RAM |
| How do you find slow queries? | Enable slow query log, set long_query_time, use EXPLAIN on queries |
| How do you optimize a slow query? | Check EXPLAIN output, add proper indexes, avoid SELECT *, optimize JOINs |
| What port does MySQL use? | 3306 |
| How to check database size? | Query information_schema.tables with SUM of data_length + index_length
|
| How do you secure MySQL? | Run mysql_secure_installation, remove anonymous users, restrict root, use strong passwords, bind to 127.0.0.1 |
Top comments (0)