MySQL is the database system that powers most dynamic websites and web applications. This guide covers essential database management tasks.
Accessing phpMyAdmin
phpMyAdmin is a web-based tool for managing MySQL databases:
1. Log in to your control panel.
2. Click on "phpMyAdmin" or navigate to the "Databases" section.
3. Click "Manage" or "Open phpMyAdmin" next to your database.
4. You will be logged in automatically.
Importing a Database
To import a database from a SQL file:
1. Open phpMyAdmin and select your database from the left sidebar.
2. Click the "Import" tab.
3. Click "Choose File" and select your .sql file.
4. Ensure the format is set to "SQL."
5. Click "Go" to start the import.
Note: phpMyAdmin has a file size limit for imports (typically 50-100 MB). For larger files, use SSH:
mysql -u username -p database_name < backup.sql
Exporting a Database
To create a backup of your database:
1. Open phpMyAdmin and select your database.
2. Click the "Export" tab.
3. Choose the export method:
- Quick: Exports all tables with default settings.
- Custom: Lets you select specific tables and configure options.
4. Ensure the format is "SQL."
5. Click "Go" to download the backup file.
Optimizing Tables
Over time, database tables can become fragmented, which slows down queries:
1. Open phpMyAdmin and select your database.
2. Select the tables you want to optimize (or check "Check all").
3. From the "With selected" dropdown, choose "Optimize table."
4. Click "Go."
Managing Database Users
For security, each application should have its own database user with only the necessary permissions:
1. Go to your control panel's "Database" section.
2. Find the "Database Users" area.
3. Create a new user or modify permissions of an existing user.
4. Grant only the permissions the application needs.
Common MySQL Commands (SSH)
If you have SSH access:
- Connect: mysql -u username -p
- Show databases: SHOW DATABASES;
- Use a database: USE database_name;
- Show tables: SHOW TABLES;
- Describe a table: DESCRIBE table_name;
- Run a query: SELECT * FROM table_name LIMIT 10;
- Exit: EXIT;
Best Practices
- Back up your database regularly before making changes.
- Use strong passwords for all database users.
- Remove database users that are no longer needed.
- Never use the root database account in web applications.
- Optimize tables periodically to maintain performance.