PhpMyAdmin Cheatsheet¶

phpMyAdmin - Webbasierte MySQL Administration

phpMyAdmin ist ein kostenloses Software-Tool, das in PHP geschrieben wurde, um die Verwaltung von MySQL über das Web zu handhaben. Es unterstützt eine breite Palette von Operationen auf MySQL und MariaDB Datenbanken. < p>

generieren

Inhaltsverzeichnis¶

[Installation](LINK_0 -%20[Konfiguration](LINK_0__ -%20[Datenbankbetrieb](LINK_0__ -%20[Tabellen](LINK_0_ -%20[Datenmanagement](LINK_0__ -%20[Import/Export](LINK_0 -%20[Benutzerverwaltung](LINK_0_ -%20[Query%20Operations](LINK_0__ -%20[Sicherheit](LINK_0_ -%20(LINK_0_)
(LINK_0_)
Beste Praktiken

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
```bash
# 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
```_

### Manuelle Installation
```bash
# 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
```bash
# 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
```_

## Konfiguration

### Grundkonfiguration
```php
<?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';
?>
```_

### Erweiterte Konfiguration
```php
<?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 Konfiguration
```php
<?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\';';
?>
```_

## Datenbanken

### Datenbanken erstellen
```sql
-- 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;
```_

### Datenbankverwaltung
```sql
-- 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;
```_

### Datenbankbetrieb über 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"

## Tabelle Operationen

### Tabellen erstellen
```sql
-- 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)
);
```_

### Tabelle Struktur Operationen
```sql
-- 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;
```_

### Tabelle Operationen über 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

## Datenverwaltung

### Daten einfügen
```sql
-- 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;
```_

### Daten aktualisieren
```sql
-- 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;
```_

### Daten löschen
```sql
-- 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;
```_

### Datenbetrieb über Schnittstelle

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

### Daten exportieren
```sql
-- 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
```_

### Daten importieren
```sql
-- 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
```_

### Befehlszeile Export/Import
```bash
# 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
```_

### Große Datei Import
```php
<?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
?>
```_

## Benutzermanagement

### Benutzer erstellen
```sql
-- 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;
```_

### Benutzerrechte verwalten
```sql
-- 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;
```_

### Benutzerkontoverwaltung
```sql
-- 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();
```_

### Benutzerverwaltung über Schnittstelle

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 ```_

Abfrage von Operationen¶

SQL Schnittstelle zur Abfrage¶

```sql -- 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¶

```sql -- 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¶

```sql -- 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; ```_

Queroptimierung¶

```sql -- 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; ```_

Sicherheit¶

Zugriffskontrolle¶

```php

```_

Datenbank Sicherheit¶

```sql -- 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; ```_

Dateisicherheit¶

```bash

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¶

Require all denied

Deny access to setup directory¶

Require all denied

Deny access to libraries¶

Require all denied

Enable HTTPS redirect¶

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

Authentifizierungsmethoden¶

```php

```_

Anpassung¶

Thema Anpassung¶

```php

```_

Schnittstelle Anpassung¶

```php

```_

Sprache und Lokalisierung¶

```php

```_

Benutzerdefinierte Funktionen¶

```php

Custom Header

PhpMyAdmin Cheatsheet¶

phpMyAdmin - Webbasierte MySQL Administration

Inhaltsverzeichnis¶

Installation¶

Ubuntu/Debian Installation¶

Abfrage von Operationen¶

SQL Schnittstelle zur Abfrage¶

Query Bookmarks¶

Query Profiling¶

Queroptimierung¶

Sicherheit¶

Zugriffskontrolle¶

Datenbank Sicherheit¶

Dateisicherheit¶

Set proper file permissions¶

Protect configuration file¶

Remove setup directory after configuration¶

Create .htaccess for additional protection¶

Deny access to configuration file¶

Deny access to setup directory¶

Deny access to libraries¶

Enable HTTPS redirect¶

Authentifizierungsmethoden¶

Anpassung¶

Thema Anpassung¶

Schnittstelle Anpassung¶

Sprache und Lokalisierung¶

Benutzerdefinierte Funktionen¶

Fehlerbehebung¶

Gemeinsame Themen und Lösungen¶

Leistungsfragen¶

Debugging¶

Analyse der Ergebnisse¶

Check Apache/Nginx error logs¶

Check PHP error logs¶

Check MySQL error logs¶

Check phpMyAdmin specific logs¶

Monitor access logs¶

Check system resources¶

Best Practices¶

Sicherheit Best Practices¶

Performance Best Practices¶

Best Practices der Wartung¶

!/bin/bash¶

phpMyAdmin maintenance script¶

1. Regular backups¶

2. Update phpMyAdmin¶

Check for updates regularly¶

Download from https://www.phpmyadmin.net/¶

3. Monitor disk space¶

4. Clean up temporary files¶

5. Rotate logs¶

6. Check for security updates¶

7. Monitor MySQL performance¶

8. Optimize MySQL tables¶

9. Check MySQL error logs¶

10. Verify phpMyAdmin configuration¶

Backup und Recovery¶

Automated backup script¶

!/bin/bash¶

Create backup directory¶

Backup phpMyAdmin configuration¶

Backup phpMyAdmin database (if using configuration storage)¶

Backup all databases¶

Compress backups¶

Remove old backups (keep 30 days)¶

Log backup completion¶

Zusammenfassung¶