Database admin: users, permissions, maintenance

Database administration (DBA) involves creating and managing MySQL/MariaDB databases, user accounts, permissions, backups, and performance. While UnderHost handles server-level administration, website owners need to manage their own databases.

Create a new database user

Via cPanel (easiest for shared hosting):

1. Log in to cPanel
2. Go to Databases → MySQL Databases
3. Scroll down to "MySQL Users"
4. Enter a username (cPanel prefixes with your account prefix)
5. Enter a strong password (cPanel generates one if you prefer)
6. Click Create User

Via phpMyAdmin (for advanced users):

1. Log in to phpMyAdmin (in cPanel)
2. Click the User accounts tab at the top
3. Click Add user account
4. Enter username, select "Local" for host, enter password
5. Assign the user to a database (next step)
6. Click Go

Via command line (VPS/Dedicated):

mysql -u root -p
mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'strong_password_here';
mysql> FLUSH PRIVILEGES;
mysql> EXIT;

Create a new database

Via cPanel:

1. Go to Databases → MySQL Databases
2. Under "Create New Database", enter a database name
3. Click Create Database
4. cPanel adds your account prefix automatically (e.g., "accountname_mydb")

Via phpMyAdmin:

1. Log in to phpMyAdmin
2. Click New in the left sidebar
3. Enter database name and choose charset (usually utf8mb4)
4. Click Create

Via command line (VPS):

mysql -u root -p
mysql> CREATE DATABASE mysite_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
mysql> FLUSH PRIVILEGES;
mysql> EXIT;

Manage database permissions

Via cPanel (simplest):

1. Go to Databases → MySQL Databases
2. Under "Add User To Database", select user and database
3. Click Add
4. Check all permissions for the user
5. Click Make Changes

Permission levels (what each user needs):

• WordPress user: SELECT, INSERT, UPDATE, DELETE (reading/writing posts and settings)
• Database backup user: SELECT only (backup needs read-only access)
• Application user: Depends on app (usually SELECT, INSERT, UPDATE, DELETE)
• Admin user: ALL (for administrative tools)

Important: Never use the root MySQL user for your website. Always create a limited user for each application.

Reset a database user password

Via cPanel:

1. Go to Databases → MySQL Databases
2. Scroll to "MySQL Users"
3. Find your user and click Change Password
4. Enter new password and click Change Password

Via phpMyAdmin:

1. Click User accounts tab
2. Find the user and click Change password
3. Enter new password and click Go

Via command line (VPS):

mysql -u root -p
mysql> ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_strong_password';
mysql> FLUSH PRIVILEGES;
mysql> EXIT;

Using phpMyAdmin for administration

Access phpMyAdmin:

• Via cPanel: Home → Databases → phpMyAdmin
• Direct URL: yourdomain.com/phpmyadmin or cpanel.yourdomain.com/phpmyadmin

Common phpMyAdmin tasks:

• View tables: Click database name in left sidebar → click table to see rows
• Edit data: Click pencil icon next to any row to edit values
• Run SQL queries: Click SQL tab and paste your SQL command
• Export/backup: Click Export tab, select format (SQL), and click Go
• Import data: Click Import tab, upload your SQL file, and click Go
• Check table size: Hover over table in left sidebar or click Structure tab

Monitor database size and performance

Check database size in cPanel:

1. Go to Databases → MySQL Databases
2. You'll see the size of each database (in MB)
3. If a database is >100MB, investigate large tables

Find large tables in phpMyAdmin:

1. Click your database
2. Look at the table list—each row shows size
3. Sort by size to find the largest tables
4. WordPress: "wp_posts" and "wp_comments" often grow large

Check query performance:

• Enable MySQL slow query log (log queries slower than 2 seconds)
• Review which queries are slow
• Add database indexes to speed up common queries
• See our MySQL performance tuning guide

Database maintenance tasks

1. Optimize tables (remove fragmentation)

Over time, database tables become fragmented. Optimizing reclaims space and speeds up queries:

Via phpMyAdmin:

1. Select all tables in your database (checkboxes at bottom)
2. Go to bottom dropdown and select Optimize table
3. Click Go

Via command line:

mysql -u root -p databasename -e "OPTIMIZE TABLE tablename;"

2. Repair corrupted tables

If a table becomes corrupted (rare but happens), repair it:

Via phpMyAdmin:

1. Right-click the table and select Repair table

Via command line:

mysqlcheck -u root -p databasename tablename

3. Regular backups

Back up your database weekly or before major changes:

• Automated: Use backup plugins for automatic daily backups
• Manual: Go to phpMyAdmin → click database → Export → download .sql file

Shared hosting vs VPS databases

Common database issues

"Access denied for user" - Wrong username or password

• Verify credentials in wp-config.php match cPanel database user
• Reset password and update config file

"MySQL has gone away" - Connection lost

• Database server restarted or timed out
• Increase wait_timeout in MySQL configuration
• Check if database size is huge (slowing down queries)

"Disk quota exceeded" - Database is too large

• Database size exceeded your hosting plan limit
• Delete old data, archive to backup, or upgrade plan

Related articles: phpMyAdmin guide | Database backup and restore | MySQL performance tuning