How to Create, Manage, Backup, and Restore Databases in CloudPanel?

[Nemesis]

I'm getting started with CloudPanel and need some guidance on managing databases. Could you explain how to create/delete databases and database users, manage databases with phpMyAdmin, and also how to safely export/import databases? Additionally, how does CloudPanel handle automatic database backups?

 

[Rootali]

CloudPanel provides straightforward tools for managing your databases:

Creating a Database:​

• Click on "Add Database".
• Enter your Database Name, Database User Name, and Password.
• Click "Add Database".

Deleting a Database:​

• Click on "Delete" next to the database.
• Confirm the action.

Managing Database Users:​

• Adding a User:
  • Click on "Add Database User".
  • Enter User Name, Password, select the Database, and set Permissions.
• Deleting a User:
  • Click "Delete" next to the user and confirm.

---

phpMyAdmin:​

• Click "Manage" to access your database via phpMyAdmin.

Attention:
Avoid using phpMyAdmin for exporting/importing databases, as it can lead to corruption or data loss. Instead, use CloudPanel's built-in commands as explained below.

---

Exporting Databases (Recommended Method):​

Export your database safely via SSH using mysqldump:

1. Login via SSH:
   
   

   ssh site-user@instance-ip-address

2. Navigate to your preferred backup directory, e.g.:
   
   

   cd ~/tmp/

3. Export the database (compressed/uncompressed):
   
   # Compressed (.gz)
   

   clpctl db:export --databaseName=my-database --file=dump.sql.gz

   
   # Uncompressed
   

   clpctl db:export --databaseName=my-database --file=dump.sql

---

Importing Databases:​

Use CloudPanel’s built-in import tool via SSH:

1. Login via SSH:
   
   

   ssh site-user@instance-ip-address

2. Go to your database dump directory and run:
   
   

   clpctl db:import --databaseName=my-database --file=dump.sql.gz

---

Automatic Database Backups:​

CloudPanel automatically creates database backups every night at 3:15 AM, keeping them for 7 days.

• Backup location:
  
  

  /home/$site-user/backups/

• To adjust backup frequency or retention:
  1. Login via SSH as root:
     

     sudo su root

  2. Edit the cron job:
     

     nano /etc/cron.d/clp

  3. Modify schedule or retention:
     # Default schedule (daily at 03:15 AM)
     

     15 3 * * * clp /usr/bin/bash -c "/usr/bin/clpctl db:backup --ignoreDatabases='db1,db2' --retentionPeriod=7" &> /dev/null

---

Database Master Credentials:​

• To view your master database credentials:

• sudo su root
  
  clpctl db:show:master-credentials

Remote Database Access:​

• If you need remote access, go to the Firewall section in CloudPanel's Admin Area and whitelist your IP address for MySQL port 3306.

This setup ensures secure, reliable, and convenient database management with CloudPanel.