phpMyAdmin Cheatsheet

■h1 ratio MyAdmin - Administración MySQL basada en la Web "Clase de inscripción" phpMyAdmin es una herramienta de software libre escrita en PHP, destinada a manejar la administración de MySQL sobre la Web. Soporta una amplia gama de operaciones en bases de datos MySQL y MariaDB. ▪/p] ■/div titulada

########################################################################################################################################################################################################################################################## Copiar todos los comandos

########################################################################################################################################################################################################################################################## Generar PDF seleccionado/button

■/div titulada ■/div titulada

Cuadro de contenidos

• Instalación
• Configuración
• Operaciones de base de datos
• Table Operations
• Data Management
• Import/Exportar
• Manejo del usuario
• Operaciones de preguntas
• Seguridad
• Customization
• Solucionando
• Las mejores prácticas

Instalación

Instalación 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 Instalación

# 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

Instalación manual

# 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 Instalación

# 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

Configuración

Configuración básica

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

Configuración avanzada

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

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

Operaciones de base de datos

Creación de bases de datos

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

Gestión de bases de datos

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

Operaciones de base de datos vía interfaz

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"

Operaciones

Crear tablas

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

Operaciones de estructura

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

Operaciones de mesa por interfaz

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

Gestión de datos

Inserción de datos

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

Actualización de datos

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

Eliminación de datos

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

Operaciones de datos vía interfaz

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

Importación/Exporto

Exportación de datos

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

Importación de datos

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

Importación de archivos grandes

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

Gestión de usuarios

Crear usuarios

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

Gestión de privilegios de usuario

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

Gestión de la cuenta de usuario

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

Gestión del usuario vía Interfaz

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

Operaciones de consulta

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;

Optimización de consultas

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

Seguridad

Control de acceso

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

Seguridad de bases de datos

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

Seguridad del archivo

# 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étodos de autenticación

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

Personalización

Personalización temática

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

Personalización de la interfaz

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

Idioma y localización

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

Funciones personalizadas

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

Solución de problemas

Problemas y soluciones comunes

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

Cuestiones de ejecución

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

Análisis de registros

# 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

Buenas prácticas

Prácticas óptimas de seguridad

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

Prácticas óptimas de rendimiento

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

### Mejores prácticas de mantenimiento
```bash
#!/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

Copia de seguridad y recuperación

# 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

-...

Resumen

phpMyAdmin es una poderosa herramienta de administración basada en la web para bases de datos MySQL y MariaDB. Esta hoja de trampa proporciona una cobertura completa de las operaciones de phpMyAdmin desde la instalación básica hasta la administración avanzada y la seguridad.

Key Strengths - ** Interfaz basada en Internet**: Fácil acceso desde cualquier navegador - Características generales: Capacidades completas de administración de bases de datos - Intuitivo: Interfaz intuitiva para operaciones complejas - Manage multiple database servers - **Import/Export: Varios formatos para la transferencia de datos

Mejores casos de uso: - Administración y gestión de bases de datos - Medios de desarrollo y ensayo - Importaciones de datos y operaciones de exportación - Gestión de usuarios y privilegios - Consultas y modificaciones rápidas de bases de datos

** Consideraciones importantes:** - La configuración de seguridad es fundamental para el uso de la producción - El rendimiento puede ser impactado con bases de datos muy grandes - Las actualizaciones periódicas son esenciales para la seguridad - Deben aplicarse procedimientos adecuados de copia de seguridad - El acceso debe limitarse únicamente a los usuarios autorizados

Al seguir las prácticas y técnicas descritas en esta hoja de trampa, puede utilizar eficazmente phpMyAdmin para gestionar bases de datos MySQL manteniendo la seguridad, el rendimiento y la fiabilidad en sus tareas de administración de bases de datos.

" copia de la funciónToClipboard() {} comandos const = document.querySelectorAll('code'); que todos losCommands = '; comandos. paraCada(cmd = confianza allCommands += cmd.textContent + '\n'); navigator.clipboard.writeText(allCommands); alerta ('Todos los comandos copiados a portapapeles!'); }

función generaPDF() { ventana.print(); } ■/script título