Kennisbank

This guide provides step-by-step instructions for creating and managing databases and users, as well as importing and exporting databases, using phpMyAdmin. Follow these steps to set up and maintain your MySQL or MariaDB databases effectively.

Creating a New Database and User

Step 1: Access phpMyAdmin

1. Log in to phpMyAdmin via your hosting control panel (e.g., cPanel) or directly at https://yourdomain.com/phpmyadmin.
2. Use your MySQL/MariaDB root credentials or an account with administrative privileges.

Step 2: Create a Database

1. In phpMyAdmin, click the Databases tab.
2. Enter a unique database name (e.g., mydb) in the Create database field.
3. Select a collation, such as utf8mb4_unicode_ci, for compatibility with modern applications.
4. Click Create. The database will appear in the left sidebar.

Step 3: Create a User

1. Click the User accounts tab (or Privileges in older versions).
2. Select Add user account.
3. Fill in the user details:
   • Username: Choose a unique username (e.g., dbuser).
   • Host name: Select Local (for localhost) or % (for remote access).
   • Password: Set a strong password or use the Generate button.
   • Re-type: Confirm the password.
4. Under Database for user account, select Grant all privileges on database and choose your database (e.g., mydb).
5. Click Go to create the user.

Step 4: Verify Access

1. Log out and test the new user’s credentials by logging back into phpMyAdmin.
2. Ensure the user can access only the assigned database.

Importing a Database

Step 1: Select the Database

1. In phpMyAdmin, select your database (e.g., mydb) from the left sidebar.
2. Click the Import tab.

Step 2: Upload the SQL File

1. Click Choose File and select your .sql or .sql.gz file.
2. Set the Character set to utf8mb4 (or match your database’s collation).
3. Click Go to import the file.

Step 3: Verify the Import

1. Check the database tables in the left sidebar to confirm the data was imported correctly.
2. If errors occur, verify the SQL file’s format or check user privileges.

Note: Large databases may cause timeouts in phpMyAdmin. For files over 50MB, contact your hosting provider or use command-line tools like mysqldump.

Exporting a Database

Step 1: Select the Database

1. In phpMyAdmin, select your database (e.g., mydb) from the left sidebar.
2. Click the Export tab.

Step 2: Export the Database

1. For a simple export:
   • Select Quick and choose SQL as the format.
   • Click Go to download the .sql file.
2. For advanced options:
   • Choose Custom to select specific tables or enable options like Add DROP TABLE.
   • Select compression (e.g., zipped) for large databases.
   • Click Go to download.

Step 3: Save the Backup

1. Store the exported .sql file securely for backups or migrations.
2. Regularly export your database to prevent data loss.