MariaDB Database Operations Cheatsheet
This comprehensive cheatsheet provides essential MariaDB and MySQL commands for database administration, data querying, and table management. Whether you’re performing routine database maintenance, extracting data, or managing table structures, this reference covers the most commonly used operations with practical examples.
MariaDB is a popular open-source relational database management system that serves as a drop-in replacement for MySQL. Most commands in this guide work identically in both systems, making this a versatile reference for database administrators and developers working with either platform.
Basic Data Querying Operations
Selecting Data with Wildcards
When you need to query data with partially known values, use the LIKE
operator with wildcard characters. The %
wildcard matches any sequence of characters, making it perfect for pattern matching in database queries.
-- Find all records with IP addresses starting with 10.10.10
SELECT *
FROM network_logs
WHERE ip LIKE '10.10.10.%';
-- Find records with email addresses from gmail
SELECT *
FROM users
WHERE email LIKE '%@gmail.com';
-- Find records containing specific text anywhere in a field
SELECT *
FROM products
WHERE description LIKE '%wireless%';
Exact Value Matching
For precise queries where you know the exact value, use the equality operator for efficient database searches.
-- Find a specific user by ID
SELECT *
FROM users
WHERE user_id = 12345;
-- Find all orders with a specific status
SELECT *
FROM orders
WHERE status = 'completed';
Partial Value Matching with Pattern Variations
The LIKE
operator supports different wildcard patterns for flexible searching:
-- Find values ending with specific text
SELECT *
FROM inventory
WHERE product_code LIKE '%ABC';
-- Find values starting with specific text
SELECT *
FROM customers
WHERE company_name LIKE 'Tech%';
-- Find values containing text in the middle
SELECT *
FROM logs
WHERE message LIKE '%error%';
Data Export Operations
Exporting Table Data to CSV
MariaDB provides built-in functionality to export query results directly to CSV files, useful for data analysis and reporting.
-- Basic CSV export
SELECT *
INTO OUTFILE '/tmp/export.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM customer_data;
-- Export with custom delimiter
SELECT customer_id, name, email
INTO OUTFILE '/tmp/customers.csv'
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
FROM customers;
-- Export with headers (using UNION)
SELECT 'ID', 'Name', 'Email', 'Created'
UNION ALL
SELECT customer_id, name, email, DATE(created_at)
FROM customers
INTO OUTFILE '/tmp/customers_with_headers.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
Important Notes for CSV Export:
- The MySQL/MariaDB user must have FILE privileges
- The output directory must be writable by the MySQL process
- The file must not already exist (MariaDB won’t overwrite)
- Use absolute paths for reliability
Table Management Operations
Renaming Tables
Table renaming is a common administrative task, especially during schema migrations or reorganization.
-- Simple table rename
RENAME TABLE old_table_name TO new_table_name;
-- Rename multiple tables in one statement
RENAME TABLE
users TO customers,
products TO inventory,
orders TO sales;
-- Rename with database specification
RENAME TABLE db1.table1 TO db2.table1;
Monitoring Table Operations
Database administrators often need to monitor table activity and growth patterns.
-- Check table row count
SELECT COUNT(*) FROM table_name;
-- Get table information
SHOW TABLE STATUS LIKE 'table_name';
-- Monitor table size
SELECT
table_name,
round(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema = 'your_database';
Real-time Monitoring
Watching Entry Count Changes
For monitoring database activity in real-time, use the watch
command with MariaDB queries. This is particularly useful for tracking data ingestion or processing jobs.
# Monitor table row count every 2 seconds
watch -n 2 'mariadb -D database_name -e "SELECT COUNT(*) FROM table_name;"'
# Monitor with authentication
watch -n 2 'mariadb -u username -p password -D database_name -e "SELECT COUNT(*) FROM table_name;"'
# Monitor multiple metrics
watch -n 5 'mariadb -D database_name -e "
SELECT
COUNT(*) as total_rows,
COUNT(DISTINCT user_id) as unique_users,
MAX(created_at) as latest_entry
FROM user_actions;"'
Advanced Monitoring Queries
-- Monitor table growth rate
SELECT
table_name,
table_rows,
round(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)',
create_time,
update_time
FROM information_schema.tables
WHERE table_schema = 'your_database'
ORDER BY table_rows DESC;
-- Monitor active connections
SHOW PROCESSLIST;
-- Check database status
SHOW STATUS LIKE 'Threads_connected';
Advanced Query Patterns
Complex Pattern Matching
-- Case-insensitive pattern matching
SELECT * FROM users WHERE name LIKE '%john%' COLLATE utf8_general_ci;
-- Multiple pattern conditions
SELECT * FROM logs
WHERE (message LIKE '%error%' OR message LIKE '%warning%')
AND timestamp > '2024-01-01';
-- Negated patterns
SELECT * FROM products WHERE name NOT LIKE '%discontinued%';
Efficient Data Filtering
-- Using IN for multiple exact matches
SELECT * FROM orders WHERE status IN ('pending', 'processing', 'shipped');
-- Combining exact and pattern matching
SELECT * FROM customers
WHERE country = 'USA'
AND email LIKE '%@company.com';
-- Date range filtering
SELECT * FROM transactions
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'
AND amount > 100;
Performance Optimization Tips
Query Optimization
- Use indexes: Ensure columns used in WHERE clauses have appropriate indexes
- Limit wildcards: Avoid leading wildcards (
LIKE '%text'
) as they prevent index usage - Use EXPLAIN: Analyze query execution plans with
EXPLAIN SELECT ...
- Limit result sets: Use
LIMIT
to prevent accidentally large result sets
Export Performance
- Use SELECT INTO OUTFILE: More efficient than application-level export
- Filter before export: Apply WHERE clauses to reduce data volume
- Consider compression: Compress large export files to save space
- Batch processing: For very large tables, consider exporting in chunks
Common Troubleshooting
Export Issues
File Permission Errors:
-- Check secure_file_priv setting
SHOW VARIABLES LIKE 'secure_file_priv';
-- If restricted, use allowed directory or disable restriction
-- (requires server restart)
SET GLOBAL secure_file_priv = '';
File Already Exists Error:
# Remove existing file before export
rm /tmp/export.csv
# Or use a unique timestamp
SELECT * INTO OUTFILE '/tmp/export_$(date +%Y%m%d_%H%M%S).csv' ...
Pattern Matching Issues
Case Sensitivity:
-- Force case-insensitive matching
SELECT * FROM users WHERE name LIKE '%john%' COLLATE utf8_general_ci;
-- Force case-sensitive matching
SELECT * FROM users WHERE name LIKE '%John%' COLLATE utf8_bin;
Special Characters:
-- Escape special characters in patterns
SELECT * FROM logs WHERE message LIKE '%50\% complete%';
SELECT * FROM files WHERE filename LIKE '%report\\_2024%';
Security Considerations
Safe Export Practices
- Validate file paths: Ensure export paths are within allowed directories
- Limit data exposure: Export only necessary columns and rows
- Secure file permissions: Set appropriate file permissions on exported data
- Clean up exports: Remove temporary export files after use
Query Security
- Parameterized queries: Use prepared statements in applications
- Input validation: Sanitize user input before building queries
- Least privilege: Grant minimum necessary database permissions
- Audit logging: Enable query logging for security monitoring
Questions Answered in This Document
Q: How do I search for database records with partially known values? A: Use the LIKE operator with wildcard characters (%) to match patterns. For example: SELECT * FROM table WHERE field LIKE '10.10.10.%'
finds all records where the field starts with “10.10.10.”
Q: What’s the best way to export MariaDB table data to CSV format? A: Use the SELECT INTO OUTFILE statement with proper field terminators: SELECT * INTO OUTFILE '/path/file.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM table_name;
Q: How can I rename a database table in MariaDB? A: Use the RENAME TABLE statement: RENAME TABLE old_table_name TO new_table_name;
You can also rename multiple tables in one statement.
Q: How do I monitor table row count changes in real-time? A: Use the watch command with a MariaDB query: watch -n 2 'mariadb -D database_name -e "SELECT COUNT(*) FROM table_name;"'
This updates every 2 seconds.
Q: What’s the difference between = and LIKE operators in MariaDB? A: The = operator requires exact matches, while LIKE allows pattern matching with wildcards. Use = for known exact values and LIKE with % wildcards for partial matches.
Q: How can I export only specific columns from a table? A: Specify the columns in your SELECT statement: SELECT column1, column2 INTO OUTFILE '/path/file.csv' FIELDS TERMINATED BY ',' FROM table_name;
Q: Why am I getting permission errors when exporting to CSV? A: Check the secure_file_priv setting with SHOW VARIABLES LIKE 'secure_file_priv';
and ensure the MySQL user has FILE privileges and write access to the target directory.
Q: How do I perform case-insensitive pattern matching in MariaDB? A: Add a COLLATE clause to your LIKE query: SELECT * FROM table WHERE field LIKE '%text%' COLLATE utf8_general_ci;
Q: Can I monitor multiple database metrics simultaneously? A: Yes, use compound SELECT statements within the watch command to display multiple metrics like row counts, unique values, and timestamps in one view.
Q: What’s the most efficient way to find records with known exact values? A: Use the equality operator (=) with indexed columns: SELECT * FROM table WHERE indexed_column = 'exact_value';
This is much faster than LIKE for exact matches.