phpMyAdmin Cheatsheet

phpMyAdmin - Web-based MySQL Administration

phpMyAdmin is a free software tool written in PHP, intended to handle the administration of MySQL over the Web. It supports a wide range of operations on MySQL and MariaDB databases.

📋 Copy All Commands 📄 Generate PDF

Table of Contents

• Installation
• Configuration
• Database Operations
• Table Operations
• Data Management
• Import/Export
• User Management
• Query Operations
• Security
• Customization
• Troubleshooting
• Best Practices

Installation

Ubuntu/Debian Installation

# Update package index
sudo apt update

# Install phpMyAdmin
sudo apt install phpmyadmin

# During installation, select web server (Apache2 or Nginx)
# Choose "Yes" to configure database with dbconfig-common
# Set phpMyAdmin application password

# For Apache2, enable required modules
sudo a2enmod rewrite
sudo systemctl restart apache2

# For Nginx, create symbolic link
sudo ln -s /usr/share/phpmyadmin /var/www/html/phpmyadmin

# Access phpMyAdmin
# http://your-server-ip/phpmyadmin

CentOS/RHEL Installation

# Install EPEL repository
sudo yum install epel-release

# Install phpMyAdmin
sudo yum install phpmyadmin

# Configure Apache for phpMyAdmin
sudo vim /etc/httpd/conf.d/phpMyAdmin.conf

# Edit the configuration to allow access
<Directory /usr/share/phpMyAdmin/>
   AddDefaultCharset UTF-8
   Require ip 127.0.0.1
   Require ip ::1
   Require ip YOUR_IP_ADDRESS
</Directory>

# Restart Apache
sudo systemctl restart httpd

# Access phpMyAdmin
# http://your-server-ip/phpmyadmin

Manual Installation

# Download phpMyAdmin
cd /var/www/html
sudo wget https://www.phpmyadmin.net/downloads/phpMyAdmin-latest-all-languages.tar.gz

# Extract
sudo tar -xzf phpMyAdmin-latest-all-languages.tar.gz
sudo mv phpMyAdmin-*-all-languages phpmyadmin

# Set permissions
sudo chown -R www-data:www-data phpmyadmin
sudo chmod -R 755 phpmyadmin

# Create configuration file
cd phpmyadmin
sudo cp config.sample.inc.php config.inc.php

# Edit configuration
sudo vim config.inc.php

# Generate blowfish secret
# You can use: https://phpmyadmin.net/setup/
$cfg['blowfish_secret'] = 'your-32-character-random-string';

# Configure server
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['port'] = '';
$cfg['Servers'][$i]['socket'] = '';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['auth_type'] = 'cookie';

# Create temporary directory
sudo mkdir /var/www/html/phpmyadmin/tmp
sudo chmod 777 /var/www/html/phpmyadmin/tmp

Docker Installation

# Run phpMyAdmin with Docker
docker run --name phpmyadmin -d \
  -e PMA_HOST=mysql-server \
  -e PMA_PORT=3306 \
  -p 8080:80 \
  phpmyadmin/phpmyadmin

# Run with specific MySQL container
docker run --name phpmyadmin -d \
  --link mysql-container:db \
  -p 8080:80 \
  phpmyadmin/phpmyadmin

# Docker Compose setup
cat > docker-compose.yml << EOF
version: '3.8'
services:
  mysql:
    image: mysql:8.0
    container_name: mysql
    environment:
      MYSQL_ROOT_PASSWORD: rootpassword
      MYSQL_DATABASE: testdb
      MYSQL_USER: testuser
      MYSQL_PASSWORD: testpass
    ports:
      - "3306:3306"
    volumes:
      - mysql-data:/var/lib/mysql

  phpmyadmin:
    image: phpmyadmin/phpmyadmin
    container_name: phpmyadmin
    environment:
      PMA_HOST: mysql
      PMA_PORT: 3306
      PMA_USER: root
      PMA_PASSWORD: rootpassword
    ports:
      - "8080:80"
    depends_on:
      - mysql

volumes:
  mysql-data:
EOF

docker-compose up -d

# Access phpMyAdmin at http://localhost:8080

Configuration

Basic Configuration

<?php
// config.inc.php

// Blowfish secret for cookie authentication
$cfg['blowfish_secret'] = 'your-32-character-random-string-here';

// Server configuration
$i = 0;
$i++;
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['port'] = '';
$cfg['Servers'][$i]['socket'] = '';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['user'] = '';
$cfg['Servers'][$i]['password'] = '';

// Directories for saving/loading files
$cfg['UploadDir'] = '';
$cfg['SaveDir'] = '';

// Increase memory limit
$cfg['MemoryLimit'] = '512M';

// Maximum execution time
$cfg['ExecTimeLimit'] = 300;

// Maximum number of rows to display
$cfg['MaxRows'] = 25;

// Default language
$cfg['DefaultLang'] = 'en';

// Default character set
$cfg['DefaultCharset'] = 'utf-8';
?>

Advanced Configuration

<?php
// Advanced phpMyAdmin configuration

// Multiple server configuration
$i = 0;

// Server 1 - Local MySQL
$i++;
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['port'] = '3306';
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['verbose'] = 'Local MySQL Server';

// Server 2 - Remote MySQL
$i++;
$cfg['Servers'][$i]['host'] = 'remote.mysql.server.com';
$cfg['Servers'][$i]['port'] = '3306';
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['verbose'] = 'Remote MySQL Server';
$cfg['Servers'][$i]['ssl'] = true;

// Control user configuration (for advanced features)
$cfg['Servers'][$i]['controlhost'] = 'localhost';
$cfg['Servers'][$i]['controlport'] = '';
$cfg['Servers'][$i]['controluser'] = 'pma';
$cfg['Servers'][$i]['controlpass'] = 'pmapass';

// phpMyAdmin configuration storage
$cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
$cfg['Servers'][$i]['bookmarktable'] = 'pma__bookmark';
$cfg['Servers'][$i]['relation'] = 'pma__relation';
$cfg['Servers'][$i]['table_info'] = 'pma__table_info';
$cfg['Servers'][$i]['table_coords'] = 'pma__table_coords';
$cfg['Servers'][$i]['pdf_pages'] = 'pma__pdf_pages';
$cfg['Servers'][$i]['column_info'] = 'pma__column_info';
$cfg['Servers'][$i]['history'] = 'pma__history';
$cfg['Servers'][$i]['table_uiprefs'] = 'pma__table_uiprefs';
$cfg['Servers'][$i]['tracking'] = 'pma__tracking';
$cfg['Servers'][$i]['userconfig'] = 'pma__userconfig';
$cfg['Servers'][$i]['recent'] = 'pma__recent';
$cfg['Servers'][$i]['favorite'] = 'pma__favorite';
$cfg['Servers'][$i]['users'] = 'pma__users';
$cfg['Servers'][$i]['usergroups'] = 'pma__usergroups';
$cfg['Servers'][$i]['navigationhiding'] = 'pma__navigationhiding';
$cfg['Servers'][$i]['savedsearches'] = 'pma__savedsearches';
$cfg['Servers'][$i]['central_columns'] = 'pma__central_columns';
$cfg['Servers'][$i]['designer_settings'] = 'pma__designer_settings';
$cfg['Servers'][$i]['export_templates'] = 'pma__export_templates';

// Security settings
$cfg['ForceSSL'] = true;
$cfg['CheckConfigurationPermissions'] = true;
$cfg['AllowArbitraryServer'] = false;
$cfg['LoginCookieValidity'] = 1440; // 24 hours

// Interface customization
$cfg['ThemeDefault'] = 'pmahomme';
$cfg['DefaultTabServer'] = 'welcome';
$cfg['DefaultTabDatabase'] = 'structure';
$cfg['DefaultTabTable'] = 'browse';

// Import/Export settings
$cfg['Import']['charset'] = 'utf-8';
$cfg['Export']['charset'] = 'utf-8';
$cfg['Export']['compression'] = 'none';
$cfg['Export']['format'] = 'sql';

// Query settings
$cfg['SQLQuery']['Edit'] = true;
$cfg['SQLQuery']['Explain'] = true;
$cfg['SQLQuery']['ShowAsPHP'] = true;
$cfg['SQLQuery']['Validate'] = false;
$cfg['SQLQuery']['Refresh'] = true;
?>

SSL Configuration

<?php
// SSL configuration for secure connections

$cfg['ForceSSL'] = true;

// Server SSL configuration
$cfg['Servers'][$i]['ssl'] = true;
$cfg['Servers'][$i]['ssl_key'] = '/path/to/client-key.pem';
$cfg['Servers'][$i]['ssl_cert'] = '/path/to/client-cert.pem';
$cfg['Servers'][$i]['ssl_ca'] = '/path/to/ca-cert.pem';
$cfg['Servers'][$i]['ssl_ca_path'] = '/path/to/ca-certificates/';
$cfg['Servers'][$i]['ssl_ciphers'] = 'DHE-RSA-AES256-SHA:AES128-SHA';
$cfg['Servers'][$i]['ssl_verify'] = true;

// Additional security headers
$cfg['CSPAllow'] = 'default-src \'self\' \'unsafe-inline\' \'unsafe-eval\';';
?>

Database Operations

Creating Databases

-- Create database via SQL tab
CREATE DATABASE mydb;

-- Create database with character set
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create database with specific options
CREATE DATABASE mydb 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

Database Management

-- Show databases
SHOW DATABASES;

-- Use database
USE mydb;

-- Drop database
DROP DATABASE mydb;

-- Show database size
SELECT 
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables 
WHERE table_schema = 'mydb'
GROUP BY table_schema;

-- Show all 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;

Database Operations via Interface

1. Database Operations via phpMyAdmin Interface:

Creating Database:
- Click "Databases" tab
- Enter database name
- Select collation (utf8mb4_unicode_ci recommended)
- Click "Create"

Dropping Database:
- Select database from left panel
- Click "Operations" tab
- Scroll to "Remove database"
- Click "Drop the database"
- Confirm deletion

Copying Database:
- Select source database
- Click "Operations" tab
- Find "Copy database to" section
- Enter new database name
- Select copy options
- Click "Go"

Renaming Database:
- Select database
- Click "Operations" tab
- Find "Rename database to" section
- Enter new name
- Click "Go"

Table Operations

Creating Tables

-- Create basic table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create table with foreign key
CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Create table with indexes
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    category_id INT,
    sku VARCHAR(50) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_category (category_id),
    INDEX idx_price (price),
    FULLTEXT idx_search (name, description)
);

Table Structure Operations

-- Show table structure
DESCRIBE users;
SHOW COLUMNS FROM users;

-- Show create table statement
SHOW CREATE TABLE users;

-- Add column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Modify column
ALTER TABLE users MODIFY COLUMN phone VARCHAR(25);

-- Change column name and type
ALTER TABLE users CHANGE COLUMN phone mobile VARCHAR(25);

-- Drop column
ALTER TABLE users DROP COLUMN mobile;

-- Add index
ALTER TABLE users ADD INDEX idx_email (email);

-- Add unique constraint
ALTER TABLE users ADD UNIQUE KEY uk_username (username);

-- Add foreign key
ALTER TABLE posts ADD CONSTRAINT fk_user_id 
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

-- Drop foreign key
ALTER TABLE posts DROP FOREIGN KEY fk_user_id;

-- Rename table
RENAME TABLE old_table TO new_table;

-- Drop table
DROP TABLE table_name;

Table Operations via Interface

Table Operations via phpMyAdmin Interface:

Creating Table:
- Select database
- Click "New" or enter table name in "Create table" section
- Specify number of columns
- Define column properties:
  - Name
  - Type (INT, VARCHAR, TEXT, etc.)
  - Length/Values
  - Default value
  - Attributes (UNSIGNED, AUTO_INCREMENT, etc.)
  - Null (allow/disallow)
  - Index (PRIMARY, UNIQUE, INDEX, FULLTEXT)
- Click "Save"

Modifying Table Structure:
- Select table
- Click "Structure" tab
- Use action buttons for each column:
  - Edit (pencil icon)
  - Drop (X icon)
  - Primary (key icon)
  - Unique (unique icon)
  - Index (index icon)
- Add columns using "Add" section at bottom

Table Operations:
- Select table
- Click "Operations" tab
- Available operations:
  - Rename table
  - Move table to another database
  - Copy table
  - Table maintenance (CHECK, ANALYZE, REPAIR, OPTIMIZE)
  - Change table engine
  - Change table collation
  - Drop table

Data Management

Inserting Data

-- Insert single row
INSERT INTO users (username, email, password) 
VALUES ('john_doe', 'john@example.com', 'hashed_password');

-- Insert multiple rows
INSERT INTO users (username, email, password) VALUES
('alice', 'alice@example.com', 'hashed_password1'),
('bob', 'bob@example.com', 'hashed_password2'),
('charlie', 'charlie@example.com', 'hashed_password3');

-- Insert with specific columns
INSERT INTO posts (user_id, title, content, status) 
VALUES (1, 'My First Post', 'This is the content of my first post.', 'published');

-- Insert from another table
INSERT INTO archived_users (username, email, created_at)
SELECT username, email, created_at FROM users WHERE created_at < '2022-01-01';

-- Insert with ON DUPLICATE KEY UPDATE
INSERT INTO users (username, email, password) 
VALUES ('john_doe', 'john.doe@example.com', 'new_password')
ON DUPLICATE KEY UPDATE 
email = VALUES(email), 
password = VALUES(password),
updated_at = CURRENT_TIMESTAMP;

Updating Data

-- Update single row
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

-- Update multiple columns
UPDATE users SET 
    email = 'john.doe@example.com',
    updated_at = CURRENT_TIMESTAMP
WHERE username = 'john_doe';

-- Update with JOIN
UPDATE users u
JOIN posts p ON u.id = p.user_id
SET u.post_count = (
    SELECT COUNT(*) FROM posts WHERE user_id = u.id
)
WHERE p.status = 'published';

-- Update with CASE statement
UPDATE posts SET status = 
CASE 
    WHEN created_at < '2022-01-01' THEN 'archived'
    WHEN created_at > '2023-01-01' THEN 'published'
    ELSE 'draft'
END;

-- Update with LIMIT
UPDATE users SET status = 'inactive' 
WHERE last_login < '2022-01-01' 
LIMIT 100;

Deleting Data

-- Delete specific rows
DELETE FROM users WHERE id = 1;

-- Delete with condition
DELETE FROM posts WHERE status = 'draft' AND created_at < '2022-01-01';

-- Delete with JOIN
DELETE u FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE p.id IS NULL;

-- Delete with LIMIT
DELETE FROM logs WHERE created_at < '2023-01-01' LIMIT 1000;

-- Truncate table (faster for deleting all rows)
TRUNCATE TABLE temp_data;

Data Operations via Interface

Data Operations via phpMyAdmin Interface:

Inserting Data:
- Select table
- Click "Insert" tab
- Fill in form fields for each column
- Use "Go" to insert single row
- Use "Insert another new row" for multiple inserts
- Use "Insert as new row" to duplicate existing row

Browsing Data:
- Select table
- Click "Browse" tab
- Use pagination controls
- Use "Show" dropdown to change number of rows
- Use search box for quick filtering
- Click column headers to sort

Editing Data:
- In Browse view, click "Edit" (pencil icon) for specific row
- Modify values in form
- Click "Go" to save changes
- Use "Insert as new row" to create copy

Deleting Data:
- In Browse view, check rows to delete
- Click "Delete" button
- Confirm deletion
- Or click "Delete" (X icon) for single row

Searching Data:
- Click "Search" tab
- Use form to build complex queries
- Specify search criteria for each column
- Use operators (=, >, <, LIKE, etc.)
- Click "Go" to execute search

Import/Export

Exporting Data

-- Export via SQL
-- Select database or table
-- Click "Export" tab
-- Choose export method:
--   - Quick: Basic export with default settings
--   - Custom: Advanced options

-- Export formats:
-- - SQL: Database structure and data
-- - CSV: Comma-separated values
-- - Excel: Microsoft Excel format
-- - PDF: Portable Document Format
-- - XML: Extensible Markup Language
-- - JSON: JavaScript Object Notation
-- - YAML: YAML Ain't Markup Language

-- SQL export options:
-- Structure:
--   - Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT / TRIGGER statement
--   - Add CREATE TABLE statement
--   - Add IF NOT EXISTS
--   - Add AUTO_INCREMENT value
--   - Enclose table and column names with backquotes

-- Data:
--   - Complete inserts
--   - Extended inserts
--   - Delayed inserts
--   - Ignore inserts
--   - Hex for BLOB
--   - Export type: INSERT, UPDATE, REPLACE
--   - Maximal length of created query

Importing Data

-- Import via phpMyAdmin interface
-- Click "Import" tab
-- Choose file to import
-- Select format (SQL, CSV, etc.)
-- Configure import options

-- CSV import options:
-- - Fields terminated by: , (comma)
-- - Fields enclosed by: " (quote)
-- - Fields escaped by: \ (backslash)
-- - Lines terminated by: auto
-- - Column names in first row
-- - Do not abort on INSERT error
-- - Replace table data with file
-- - Use ignore keyword

-- SQL import options:
-- - SQL compatibility mode
-- - Do not use AUTO_INCREMENT for zero values
-- - Enable foreign key checks
-- - Format of imported file
-- - Character set of the file
-- - Partial import settings

Command Line Export/Import

# Export database using mysqldump
mysqldump -u username -p database_name > backup.sql

# Export specific tables
mysqldump -u username -p database_name table1 table2 > tables_backup.sql

# Export structure only
mysqldump -u username -p --no-data database_name > structure.sql

# Export data only
mysqldump -u username -p --no-create-info database_name > data.sql

# Export with compression
mysqldump -u username -p database_name | gzip > backup.sql.gz

# Import database
mysql -u username -p database_name < backup.sql

# Import compressed file
gunzip < backup.sql.gz | mysql -u username -p database_name

# Import specific table
mysql -u username -p database_name < table_backup.sql

Large File Import

<?php
// For large file imports, modify PHP settings

// In php.ini or .htaccess:
upload_max_filesize = 100M
post_max_size = 100M
max_execution_time = 300
max_input_time = 300
memory_limit = 256M

// phpMyAdmin configuration for large imports:
$cfg['UploadDir'] = '/path/to/upload/directory/';
$cfg['ExecTimeLimit'] = 0; // No time limit
$cfg['MemoryLimit'] = '512M';

// For very large files, use command line:
// 1. Upload file to server
// 2. Use mysql command line tool
// 3. Or use phpMyAdmin's "Browse your computer" feature
?>

User Management

Creating Users

-- Create user with password
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

-- Create user for specific host
CREATE USER 'remoteuser'@'192.168.1.%' IDENTIFIED BY 'password';

-- Create user for any host
CREATE USER 'anyuser'@'%' IDENTIFIED BY 'password';

-- Create user with SSL requirement
CREATE USER 'ssluser'@'%' IDENTIFIED BY 'password' REQUIRE SSL;

-- Create user with resource limits
CREATE USER 'limiteduser'@'localhost' IDENTIFIED BY 'password'
WITH MAX_QUERIES_PER_HOUR 1000
     MAX_UPDATES_PER_HOUR 100
     MAX_CONNECTIONS_PER_HOUR 10
     MAX_USER_CONNECTIONS 2;

Managing User Privileges

-- Grant all privileges on database
GRANT ALL PRIVILEGES ON mydb.* TO 'user'@'localhost';

-- Grant specific privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'user'@'localhost';

-- Grant privileges on specific table
GRANT SELECT, UPDATE ON mydb.users TO 'user'@'localhost';

-- Grant privileges on specific columns
GRANT SELECT (id, username), UPDATE (email) ON mydb.users TO 'user'@'localhost';

-- Grant administrative privileges
GRANT CREATE, DROP, ALTER, INDEX ON mydb.* TO 'admin'@'localhost';

-- Grant replication privileges
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';

-- Show user privileges
SHOW GRANTS FOR 'user'@'localhost';

-- Revoke privileges
REVOKE DELETE ON mydb.* FROM 'user'@'localhost';

-- Revoke all privileges
REVOKE ALL PRIVILEGES ON mydb.* FROM 'user'@'localhost';

-- Apply privilege changes
FLUSH PRIVILEGES;

User Account Management

-- Change user password
ALTER USER 'user'@'localhost' IDENTIFIED BY 'newpassword';

-- Set password expiration
ALTER USER 'user'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- Lock user account
ALTER USER 'user'@'localhost' ACCOUNT LOCK;

-- Unlock user account
ALTER USER 'user'@'localhost' ACCOUNT UNLOCK;

-- Rename user
RENAME USER 'olduser'@'localhost' TO 'newuser'@'localhost';

-- Drop user
DROP USER 'user'@'localhost';

-- Show all users
SELECT User, Host FROM mysql.user;

-- Show current user
SELECT USER(), CURRENT_USER();

User Management via Interface

User Management via phpMyAdmin Interface:

Accessing User Accounts:
- Click "User accounts" tab (top menu)
- View list of all MySQL users
- See user privileges and login information

Adding New User:
- Click "Add user account"
- Fill in user details:
  - User name
  - Host name (localhost, %, or specific IP)
  - Password (generate or enter manually)
  - Re-type password
- Set global privileges or database-specific privileges
- Configure resource limits if needed
- Click "Go" to create user

Editing User:
- Click "Edit privileges" for specific user
- Modify global privileges
- Add/remove database-specific privileges
- Change password
- Set resource limits
- Lock/unlock account

Deleting User:
- Select user from list
- Click "Remove selected users"
- Choose whether to revoke privileges or drop user
- Confirm deletion

Query Operations

SQL Query Interface

-- Using SQL tab for complex queries

-- Complex SELECT with JOINs
SELECT 
    u.username,
    u.email,
    COUNT(p.id) as post_count,
    MAX(p.created_at) as last_post
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE u.created_at > '2023-01-01'
GROUP BY u.id, u.username, u.email
HAVING post_count > 0
ORDER BY last_post DESC
LIMIT 10;

-- Subquery example
SELECT username, email
FROM users
WHERE id IN (
    SELECT DISTINCT user_id 
    FROM posts 
    WHERE status = 'published' 
    AND created_at > '2023-01-01'
);

-- Window function (MySQL 8.0+)
SELECT 
    username,
    email,
    created_at,
    ROW_NUMBER() OVER (ORDER BY created_at) as user_number,
    RANK() OVER (ORDER BY created_at) as user_rank
FROM users;

-- Common Table Expression (MySQL 8.0+)
WITH active_users AS (
    SELECT user_id, COUNT(*) as post_count
    FROM posts
    WHERE created_at > '2023-01-01'
    GROUP BY user_id
    HAVING post_count > 5
)
SELECT u.username, au.post_count
FROM users u
JOIN active_users au ON u.id = au.user_id;

Query Bookmarks

-- Save frequently used queries as bookmarks
-- In SQL tab, after executing query:
-- 1. Check "Bookmark this SQL query"
-- 2. Enter bookmark label
-- 3. Choose to make it shared (visible to all users)
-- 4. Click "Go"

-- Access bookmarks:
-- 1. Go to SQL tab
-- 2. Click "Bookmark" dropdown
-- 3. Select saved query
-- 4. Modify if needed and execute

-- Example bookmarked queries:
-- "User Statistics"
SELECT 
    COUNT(*) as total_users,
    COUNT(CASE WHEN created_at > DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 END) as new_users_30d,
    COUNT(CASE WHEN last_login > DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END) as active_users_7d
FROM users;

-- "Top Authors"
SELECT 
    u.username,
    COUNT(p.id) as post_count,
    AVG(CHAR_LENGTH(p.content)) as avg_post_length
FROM users u
JOIN posts p ON u.id = p.user_id
WHERE p.status = 'published'
GROUP BY u.id, u.username
ORDER BY post_count DESC
LIMIT 10;

Query Profiling

-- Enable profiling
SET profiling = 1;

-- Execute queries
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- Show profiles
SHOW PROFILES;

-- Show detailed profile for specific query
SHOW PROFILE FOR QUERY 1;

-- Show specific profile information
SHOW PROFILE CPU FOR QUERY 1;
SHOW PROFILE MEMORY FOR QUERY 1;
SHOW PROFILE BLOCK IO FOR QUERY 1;

-- Disable profiling
SET profiling = 0;

Query Optimization

-- Use EXPLAIN to analyze query execution
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

-- EXPLAIN with FORMAT=JSON for detailed information
EXPLAIN FORMAT=JSON SELECT u.*, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id;

-- Analyze table for better statistics
ANALYZE TABLE users;

-- Optimize table
OPTIMIZE TABLE users;

-- Check table for errors
CHECK TABLE users;

-- Repair table if needed
REPAIR TABLE users;

-- Show table status
SHOW TABLE STATUS LIKE 'users';

-- Show index usage
SHOW INDEX FROM users;

Security

Access Control

<?php
// Restrict access by IP address
$cfg['Servers'][$i]['AllowDeny']['order'] = 'deny,allow';
$cfg['Servers'][$i]['AllowDeny']['rules'] = array(
    'allow root from 192.168.1.0/24',
    'allow % from 192.168.1.0/24',
    'deny % from all'
);

// Force HTTPS
$cfg['ForceSSL'] = true;

// Disable arbitrary server connections
$cfg['AllowArbitraryServer'] = false;

// Set login cookie validity (in seconds)
$cfg['LoginCookieValidity'] = 1440; // 24 minutes

// Enable two-factor authentication
$cfg['TwoFactorAuthentication'] = true;

// Hide phpMyAdmin version
$cfg['VersionCheck'] = false;
?>

Database Security

-- Remove anonymous users
DELETE FROM mysql.user WHERE User='';

-- Remove test database
DROP DATABASE IF EXISTS test;

-- Set strong passwords for all users
ALTER USER 'root'@'localhost' IDENTIFIED BY 'strong_password_here';

-- Disable remote root login
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

-- Create dedicated phpMyAdmin user
CREATE USER 'pma'@'localhost' IDENTIFIED BY 'pmapassword';
GRANT SELECT, INSERT, UPDATE, DELETE ON phpmyadmin.* TO 'pma'@'localhost';

-- Apply changes
FLUSH PRIVILEGES;

File Security

# Set proper file permissions
sudo chown -R www-data:www-data /var/www/html/phpmyadmin
sudo chmod -R 755 /var/www/html/phpmyadmin
sudo chmod 644 /var/www/html/phpmyadmin/config.inc.php

# Protect configuration file
sudo chmod 600 /var/www/html/phpmyadmin/config.inc.php

# Remove setup directory after configuration
sudo rm -rf /var/www/html/phpmyadmin/setup

# Create .htaccess for additional protection
sudo tee /var/www/html/phpmyadmin/.htaccess << EOF
# Deny access to configuration file
<Files "config.inc.php">
    Require all denied
</Files>

# Deny access to setup directory
<Directory "setup">
    Require all denied
</Directory>

# Deny access to libraries
<Directory "libraries">
    Require all denied
</Directory>

# Enable HTTPS redirect
RewriteEngine On
RewriteCond %{HTTPS} off
RewriteRule ^(.*)$ https://%{HTTP_HOST}%{REQUEST_URI} [L,R=301]
EOF

Authentication Methods

<?php
// Cookie authentication (default)
$cfg['Servers'][$i]['auth_type'] = 'cookie';

// HTTP authentication
$cfg['Servers'][$i]['auth_type'] = 'http';

// Config authentication (not recommended for production)
$cfg['Servers'][$i]['auth_type'] = 'config';
$cfg['Servers'][$i]['user'] = 'username';
$cfg['Servers'][$i]['password'] = 'password';

// Signon authentication
$cfg['Servers'][$i]['auth_type'] = 'signon';
$cfg['Servers'][$i]['SignonSession'] = 'SignonSession';
$cfg['Servers'][$i]['SignonURL'] = 'signon.php';
$cfg['Servers'][$i]['LogoutURL'] = 'logout.php';
?>

Customization

Theme Customization

<?php
// Set default theme
$cfg['ThemeDefault'] = 'pmahomme';

// Available themes:
// - pmahomme (default)
// - original
// - metro

// Custom theme directory
$cfg['ThemeManager'] = true;
$cfg['ThemePerServer'] = false;

// Custom CSS
$cfg['custom_css'] = 'custom.css';
?>

Interface Customization

<?php
// Default tabs
$cfg['DefaultTabServer'] = 'welcome';
$cfg['DefaultTabDatabase'] = 'structure';
$cfg['DefaultTabTable'] = 'browse';

// Navigation panel
$cfg['NavigationTreeEnableGrouping'] = true;
$cfg['NavigationTreeDbSeparator'] = '_';
$cfg['NavigationTreeTableSeparator'] = '__';
$cfg['NavigationTreeTableLevel'] = 1;

// Display options
$cfg['MaxRows'] = 25;
$cfg['Order'] = 'ASC';
$cfg['DisplayServersList'] = false;
$cfg['DisplayDatabasesList'] = 'auto';

// Grid editing
$cfg['GridEditing'] = 'click';
$cfg['SaveCellsAtOnce'] = false;

// Query window
$cfg['QueryWindowWidth'] = 550;
$cfg['QueryWindowHeight'] = 310;
$cfg['QueryHistoryDB'] = false;
$cfg['QueryHistoryMax'] = 25;

// Text areas
$cfg['TextareaRows'] = 15;
$cfg['TextareaCols'] = 40;
$cfg['CharTextareaRows'] = 7;
$cfg['CharTextareaCols'] = 40;
?>

Language and Localization

<?php
// Set default language
$cfg['DefaultLang'] = 'en';

// Available languages:
// en - English
// es - Spanish
// fr - French
// de - German
// it - Italian
// pt - Portuguese
// ru - Russian
// zh - Chinese
// ja - Japanese

// Automatic language detection
$cfg['Lang'] = '';

// Character set
$cfg['DefaultCharset'] = 'utf-8';

// Date and time format
$cfg['DefaultDateFormat'] = 'Y-m-d';
$cfg['DefaultTimeFormat'] = 'H:i:s';
?>

Custom Functions

<?php
// Custom header and footer
$cfg['HeaderCustomContent'] = '<div style="text-align: center; background: #f0f0f0; padding: 10px;">Custom Header</div>';
$cfg['FooterCustomContent'] = '<div style="text-align: center; background: #f0f0f0; padding: 10px;">Custom Footer</div>';

// Custom CSS
$cfg['ThemeDefault'] = 'pmahomme';
$cfg['dontlimitchars'] = true;

// Custom JavaScript
$cfg['custom_js'] = 'custom.js';

// Example custom.js content:
/*
$(document).ready(function() {
    // Add custom functionality
    console.log('phpMyAdmin custom script loaded');

    // Example: Auto-refresh for specific pages
    if (window.location.href.indexOf('server_status.php') > -1) {
        setTimeout(function() {
            location.reload();
        }, 30000); // Refresh every 30 seconds
    }
});
*/
?>

Troubleshooting

Common Issues and Solutions

<?php
// Issue: "The configuration file now needs a secret passphrase (blowfish_secret)"
// Solution: Add blowfish secret to config.inc.php
$cfg['blowfish_secret'] = 'your-32-character-random-string-here';

// Issue: "Cannot load or save configuration"
// Solution: Create and set permissions for config directory
// mkdir /var/www/html/phpmyadmin/config
// chmod 777 /var/www/html/phpmyadmin/config

// Issue: "The phpMyAdmin configuration storage is not completely configured"
// Solution: Import create_tables.sql and configure control user
/*
mysql -u root -p < /usr/share/phpmyadmin/sql/create_tables.sql
CREATE USER 'pma'@'localhost' IDENTIFIED BY 'pmapass';
GRANT ALL PRIVILEGES ON phpmyadmin.* TO 'pma'@'localhost';
*/

// Issue: "Access denied for user"
// Solution: Check MySQL user privileges and phpMyAdmin configuration
/*
SHOW GRANTS FOR 'username'@'localhost';
GRANT ALL PRIVILEGES ON database.* TO 'username'@'localhost';
FLUSH PRIVILEGES;
*/

// Issue: "File uploads are disabled"
// Solution: Enable file uploads in PHP configuration
/*
file_uploads = On
upload_max_filesize = 100M
post_max_size = 100M
max_execution_time = 300
*/

// Issue: "Session timeout"
// Solution: Increase session timeout
$cfg['LoginCookieValidity'] = 3600; // 1 hour

// Issue: "Memory limit exceeded"
// Solution: Increase PHP memory limit
$cfg['MemoryLimit'] = '512M';
// Or in php.ini: memory_limit = 512M
?>

Performance Issues

<?php
// Optimize phpMyAdmin performance

// Increase limits
$cfg['MaxRows'] = 50;
$cfg['MemoryLimit'] = '512M';
$cfg['ExecTimeLimit'] = 300;

// Disable expensive features for large databases
$cfg['MaxDbList'] = 100;
$cfg['MaxTableList'] = 250;
$cfg['ShowStats'] = false;
$cfg['ShowServerInfo'] = false;
$cfg['ShowPhpInfo'] = false;
$cfg['ShowChgPassword'] = false;

// Optimize queries
$cfg['MaxExactCount'] = 50000;
$cfg['MaxExactCountViews'] = 0;

// Disable automatic database statistics
$cfg['ShowStats'] = false;

// Use persistent connections
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
?>

Debugging

<?php
// Enable error reporting
error_reporting(E_ALL);
ini_set('display_errors', 1);

// phpMyAdmin debugging
$cfg['Error_Handler']['display'] = true;
$cfg['Error_Handler']['gather'] = true;

// SQL debugging
$cfg['SQLQuery']['Validate'] = true;
$cfg['SQLQuery']['Explain'] = true;

// Log errors
$cfg['Error_Handler']['log'] = true;
$cfg['Error_Handler']['log_file'] = '/var/log/phpmyadmin.log';

// Debug SQL queries
$cfg['DBG']['sql'] = true;
?>

Log Analysis

# Check Apache/Nginx error logs
sudo tail -f /var/log/apache2/error.log
sudo tail -f /var/log/nginx/error.log

# Check PHP error logs
sudo tail -f /var/log/php/error.log

# Check MySQL error logs
sudo tail -f /var/log/mysql/error.log

# Check phpMyAdmin specific logs
sudo tail -f /var/log/phpmyadmin.log

# Monitor access logs
sudo tail -f /var/log/apache2/access.log | grep phpmyadmin

# Check system resources
htop
iostat -x 1
free -h
df -h

Best Practices

Security Best Practices

<?php
// Security configuration checklist

// 1. Use strong blowfish secret (32+ characters)
$cfg['blowfish_secret'] = 'your-very-long-random-string-here-32chars';

// 2. Force HTTPS
$cfg['ForceSSL'] = true;

// 3. Disable arbitrary server connections
$cfg['AllowArbitraryServer'] = false;

// 4. Restrict access by IP
$cfg['Servers'][$i]['AllowDeny']['order'] = 'deny,allow';
$cfg['Servers'][$i]['AllowDeny']['rules'] = array(
    'allow % from 192.168.1.0/24',
    'deny % from all'
);

// 5. Set session timeout
$cfg['LoginCookieValidity'] = 1440; // 24 minutes

// 6. Hide version information
$cfg['VersionCheck'] = false;

// 7. Disable root login from remote hosts
// DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

// 8. Use dedicated phpMyAdmin database user
$cfg['Servers'][$i]['controluser'] = 'pma';
$cfg['Servers'][$i]['controlpass'] = 'secure_password';

// 9. Regular security updates
// Keep phpMyAdmin, PHP, and MySQL updated

// 10. Monitor access logs
// Regularly check for suspicious activity
?>

Performance Best Practices

<?php
// Performance optimization

// 1. Optimize display settings
$cfg['MaxRows'] = 25;
$cfg['MaxDbList'] = 100;
$cfg['MaxTableList'] = 250;

// 2. Disable expensive operations for large databases
$cfg['ShowStats'] = false;
$cfg['ShowServerInfo'] = false;
$cfg['MaxExactCount'] = 50000;

// 3. Use appropriate memory limits
$cfg['MemoryLimit'] = '512M';

// 4. Optimize PHP settings
/*
memory_limit = 512M
max_execution_time = 300
max_input_time = 300
upload_max_filesize = 100M
post_max_size = 100M
*/

// 5. Use MySQL query cache
/*
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 2M
*/

// 6. Optimize MySQL configuration
/*
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
*/
?>

Maintenance Best Practices

#!/bin/bash
# phpMyAdmin maintenance script

# 1. Regular backups
mysqldump -u root -p --all-databases > /backup/mysql_backup_$(date +%Y%m%d).sql

# 2. Update phpMyAdmin
# Check for updates regularly
# Download from https://www.phpmyadmin.net/

# 3. Monitor disk space
df -h /var/lib/mysql
df -h /var/www/html/phpmyadmin

# 4. Clean up temporary files
find /tmp -name "phpmyadmin*" -mtime +7 -delete

# 5. Rotate logs
logrotate /etc/logrotate.d/phpmyadmin

# 6. Check for security updates
apt list --upgradable | grep -i phpmyadmin

# 7. Monitor MySQL performance
mysqladmin -u root -p processlist
mysqladmin -u root -p status

# 8. Optimize MySQL tables
mysqlcheck -u root -p --optimize --all-databases

# 9. Check MySQL error logs
tail -n 100 /var/log/mysql/error.log

# 10. Verify phpMyAdmin configuration
php -l /var/www/html/phpmyadmin/config.inc.php

Backup and Recovery

# Automated backup script
#!/bin/bash

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

# Create backup directory
mkdir -p $BACKUP_DIR

# Backup phpMyAdmin configuration
cp /var/www/html/phpmyadmin/config.inc.php $BACKUP_DIR/config_$DATE.inc.php

# Backup phpMyAdmin database (if using configuration storage)
mysqldump -u root -p phpmyadmin > $BACKUP_DIR/phpmyadmin_db_$DATE.sql

# Backup all databases
mysqldump -u root -p --all-databases > $BACKUP_DIR/all_databases_$DATE.sql

# Compress backups
gzip $BACKUP_DIR/*.sql

# Remove old backups (keep 30 days)
find $BACKUP_DIR -name "*.gz" -mtime +30 -delete
find $BACKUP_DIR -name "config_*.inc.php" -mtime +30 -delete

# Log backup completion
echo "$(date): phpMyAdmin backup completed" >> /var/log/phpmyadmin_backup.log

---

Summary

phpMyAdmin is a powerful web-based administration tool for MySQL and MariaDB databases. This cheatsheet provides comprehensive coverage of phpMyAdmin operations from basic installation to advanced administration and security.

Key Strengths: - Web-based Interface: Easy access from any browser - Comprehensive Features: Complete database administration capabilities - User-friendly: Intuitive interface for complex operations - Multi-server Support: Manage multiple database servers - Import/Export: Various formats for data transfer

Best Use Cases: - Database administration and management - Development and testing environments - Data import/export operations - User and privilege management - Quick database queries and modifications

Important Considerations: - Security configuration is critical for production use - Performance can be impacted with very large databases - Regular updates are essential for security - Proper backup procedures should be implemented - Access should be restricted to authorized users only

By following the practices and techniques outlined in this cheatsheet, you can effectively use phpMyAdmin to manage MySQL databases while maintaining security, performance, and reliability in your database administration tasks.