phpMa feuille de chaleur Admin

phpMyAdmin - Gestion MySQL basée sur le Web

phpMyAdmin est un logiciel libre écrit en PHP, destiné à gérer l'administration de MySQL sur le Web. Il supporte une large gamme d'opérations sur les bases de données MySQL et MariaDB.

Copier toutes les commandes Générer PDF

Sommaire

• [Installation] (LINK_0)
• [Configuration] (LINK_0)
• [Opérations de base de données] (LINK_0)
• [Table des opérations] (LINK_0_)
• [Gestion des données] (LINK_0)
• [Import/Export] (LINK_0)
• [Gestion de l'utilisateur] (LINK_0)
• [Opérations de secours] (LINK_0)
• [Sécurité] (LINK_0)
• [Personnalisation] (LINK_0)
• [Dépannage] (LINK_0)
• [Meilleures pratiques] (LINK_0)

Installation

Installation Ubuntu/Debian

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

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

### Installation Docker
```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

Configuration

Configuration de base

<?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';
?>

Configuration avancée

<?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;
?>

Configuration SSL

<?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\';';
?>

Opérations de bases de données

Création de bases de données

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

Gestion des bases de données

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

Opérations de base de données 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"

Tableau des opérations

Création de tableaux

-- 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)
);

Tableau Structure Opérations

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

Opérations de table 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

Gestion des données

Insérer des données

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

Mise à jour des données

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

Suppression des données

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

Opérations de données 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

Importation/Exportation

Exportation de données

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

Importation de données

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

Export/Importation de ligne de commande

# 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

Importation de grand fichier

<?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
?>

Gestion des utilisateurs

Création d'utilisateurs

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

Gestion des privilèges des utilisateurs

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

Gestion de compte utilisateur

-- 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();

Gestion des utilisateurs 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

Opérations de requêtes

SQL Interface de requête

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

Interroger les signets

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

Profil de requête

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

Optimisation des requêtes

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

Sécurité

Contrôle d'accès

<?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;
?>

Sécurité des bases de données

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

Sécurité des fichiers

# 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

Méthodes d'authentification

<?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';
?>

Personnalisation

Personnalisation du thème

<?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';
?>

Personnalisation de l'interface

<?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;
?>

Langue et localisation

<?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';
?>

Fonctions personnalisées

<?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
    }
});
*/
?>

Dépannage

Questions et solutions communes

<?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
?>

Problèmes de performance

<?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;
?>

Déboguement

<?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;
?>

Analyse du journal

# 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

Meilleures pratiques

Pratiques exemplaires en matière de sécurité

<?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
?>

Meilleures pratiques en matière de rendement

<?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
*/
?>

Pratiques exemplaires de maintenance

#!/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

Sauvegarde et récupération

# 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

---

Résumé

phpMyAdmin est un puissant outil d'administration web pour les bases de données MySQL et MariaDB. Cette triche fournit une couverture complète des opérations phpMyAdmin de l'installation de base à l'administration et la sécurité avancées.

Principales forces: - ** Interface Web**: Accès facile à partir de tout navigateur - Caractéristiques complètes: Capacités complètes d'administration de la base de données - Convivielle: Interface intuitive pour des opérations complexes - ** Support multi-serveurs** : gérer plusieurs serveurs de bases de données - Import/Export: différents formats pour le transfert de données

Cas de la meilleure utilisation: - Administration et gestion de la base de données - Environnements de développement et d'essai - Opérations d'importation/exportation de données - Gestion des utilisateurs et des privilèges - Demandes et modifications rapides de la base de données

** Considérations importantes :** - La configuration de sécurité est essentielle pour l'utilisation de la production - Les performances peuvent être affectées par de très grandes bases de données - Des mises à jour régulières sont essentielles à la sécurité - Des procédures de sauvegarde appropriées devraient être mises en œuvre - L'accès devrait être limité aux utilisateurs autorisés seulement

En suivant les pratiques et techniques décrites dans cette triche, vous pouvez utiliser efficacement phpMyAdmin pour gérer les bases de données MySQL tout en maintenant la sécurité, les performances et la fiabilité dans vos tâches d'administration de base de données.