Starting with OnApp 5.0 version, you can create and manage databases available for your Application Server.

Ensure that See all application servers or See own application servers permission is on before managing databases. For more information about permissions refer to the list of OnApp Permissions.


View Databases and Database Users


If you want to see the list of databases available for your Application Server, you can view it at OnApp Control Panel.

To view a database list:

  1. Go to your Control Panel > Cloud > Application Servers menu.
  2. Click the label of the server you're interested in.
  3. Click the Applications tab > Databases.
  4. On the screen that appears, you will get the list of databases together with their: 
    • Database name - the name of a database
    • Users - the names of users, assigned to the database
    • Actions icon- the actions you can perform with the database (Privileges, Remove).

To view the list of database users:

  1. Go to your Control Panel > Cloud > Application Servers menu.
  2. Click the label of the server you're interested in.
  3. Click the Applications tab > Databases.
  4. Click the Database Users tab.
  5. On the screen that appears, you will get the list of database users together with their: 
    • User name - the name of a user
    • Actions icon - the actions you can perform with the database users (Change password, Remove).

Create Database

You can create database available for your Application Server.

To create a database:

  1. Go to your Control Panel > Cloud > Application Servers menu.
  2. Click the label of the server you're interested in.
  3. Click the Applications tab > Databases.
  4. On the screen that appears, press "+" button.
  5. Complete the creation form:
    • Database name - specify database name
  6. Click the Submit button.

Create and Manage Database Users

You should create a user, who will be able to manage a database.

To create a database user:

  1. Go to your Control Panel > Cloud > Application Servers menu.
  2. Click the label of the server you're interested in.
  3. Click the Applications tab > Databases.
  4. On the screen that appears, click the Database Users tab and  press "+" button.
  5. Complete the creation form:
    • Name - specify database user's name. The length of the name should not exceed 11 characters.
    • Password - specify a password for the database user
  6. Click the Submit button.

You can also change database user password or delete database user.

To change database user password:

  1. Go to your Control Panel > Cloud > Application Servers menu.
  2. Click the label of the server you're interested in.
  3. Click the Applications tab > Databases.
  4. On the screen that appears, click the Database Users tab.
  5. Click the Actions icon next to the specific database user and click Change password.
  6. Enter new password  and click the Submit button.

To delete database user:

  1. Go to your Control Panel > Cloud > Application Servers menu.
  2. Click the label of the server you're interested in.
  3. Click the Applications tab > Databases.
  4. On the screen that appears, click the Database Users tab.
  5. Click the Actions icon next to the specific database user and click Remove.
  6. Confirm deletion by clicking the Remove button.

Assign User and Set Privileges for Database

After user creation you should assign the user to a database and set privileges (permissions) for the database. For example, the assigned user can create temporary tables, execute, drop, lock tables in the database.

To assign a user and set privileges for a database:

  1. Go to your Control Panel > Cloud > Application Servers menu.
  2. Click the label of the server you're interested in.
  3. Click the Applications tab > Databases.
  4. Click the Actions icon > Privileges next to the database you're interested in.
  5. Click the "+" button to assign a user to the database. On the screen that appears, set the following:
    • User - chose the user from the drop-down list. 
    • Privileges - tick the checkbox next to a privilege that you want to assign to the user. Tick the checkbox "All" if you want to chose all privileges.
    • Host - chose a host (local host or any host) from the drop-down list. You can also chose "Use text field" and specify the host name in a blank field.
  6. Click the Submit button.

Edit Users Assigned to Database

If you want to change a set of privileges, given to a specific user, you can edit it.

To change the privileges of a user, assigned to a database:

  1. Go to your Control Panel > Cloud > Application Servers menu.
  2. Click the label of the server you're interested in.
  3. Click the Applications tab > Databases.
  4. Click the Actions icon > Privileges next to the database you're interested in. You will get a list of users, assigned to this database.
  5. Click the Actions icon> Change Privileges next to the user you're interested in. 
  6. Tick the checkbox next to a privilege which you want to assign to the user. Tick the checkbox "All" if you want to chose all privileges.
  7. Click the Submit button.

If you do not want a specific user to manage a database, you can unassign user from the database.

To unassign user from a database:

  1. Go to your Control Panel > Cloud > Application Servers menu.
  2. Click the label of the server you're interested in.
  3. Click the Applications tab > Databases.
  4. Click the Actions icon > Privileges next to the database you're interested in. You will get a list of users, assigned to this database.
  5. Click the Actions icon > Unassign next to the user you're interested in. 
  6. Confirm unassignment by clicking the Remove button.

Remove Database

If there is no need to use a specific database anymore, you can delete it.

To delete a database:

  1. Go to your Control Panel > Cloud > Application Servers menu.
  2. Click the label of the server you're interested in.
  3. Click the Applications tab > Databases.
  4. Click the Actions icon > Remove next to the database you're interested in.
  5. Confirm deletion by clicking the Remove button.


Database Backup

OnApp provides the ready-to-use tool for database backing up - /onapp/onapp-cp-install/onapp-database-dump.sh. This tool is a part of onapp-cp-install package, so it is mandatory for every Control Panel. It is configured as a cron job to dump OnApp database regularly. Also, this tool is used by the CP installer (onapp-cp-install.sh) to dump the OnApp database before any upgrade.

To set dumping properties, the tool reads the following configuration files:


FileUsage
/onapp/interface/config/database.yml

specifies MySQL/MariaDB connection properties, such as database, host, password, port, username.

/onapp/interface/config/on_app.yml specifies whether SSH File Transfer Server is used: use_ssh_file_transfer, ssh_file_transfer_server, ssh_file_transfer_user, ssh_file_transfer_options, backups_path.
/onapp/onapp-cp.conf

specifies:

  • extra configuration ( e.g. custom dump server)and connection option to it ( e.g. DB_DUMP_SERVER, DB_DUMP_USER, DB_DUMP_SERVER_ROOT, DB_DUMP_SERVER_SSH_OPT) 

  • files rotating option (KEEP_DUMPS)

  • crontab configuration (DB_DUMP_CRON)

  • the path on CP for temporary dumps, accordingly to /onapp/interface (ONAPP_DB_DUMP_ROOT)


When to run?

The tool is configured as cron job to run hourly. This could be customized in DB_DUMP_CRON variable (/onapp/onapp-cp.conf):

  • The default value is "40 * * * * "
  • Set any other crontab-styled value or leave it blank to disable the job. 
  • Pass the "-D" option to the CP installer (onapp-cp-install-sh). This will:
     disable database dumping during CP install/upgrade;
     make sure that dumping is disabled in the cron;
     make sure no cron job is running during install/upgrade.

How many copies to store?

The number of stored dumps is configured by KEEP_DUMPS variable. The default value is "168" (store 24 dumps per day, and keep for a last week). 

The variable value should be set before the CP installer runs.

Where to store dumps?

The dumps are temporarily stored on the Control Panel server in the /onapp/interface/$ONAPP_DB_DUMP_ROOT directory. The directory /onapp/interface/db/dump is set by default. The ONAPP_DB_DUMP_ROOT variable value could be changed in the /onapp/onapp-cp.conf file.
It is possible to move dumps into remote box automatically and to rotate there. The remote box could be:

  • SSH File Transfer Server if enabled in the OnApp configuration. The backups will be moved into server's backups_path directory and rotated there. The dumps are removed from CP box if transfer to remote box is successful.
  • Any custom box. It could be configured via DB_DUMP_SERVER in the /onapp/onapp-cp.conf. The backups will be moved into server's DB_DUMP_SERVER_ROOT directory and rotated there. The dumps are removed from CP box if transfer to remote box is successful. 

The custom box should be accessible via ssh from the CP box using onapp user without specifying the password.

What is the backup file?

Starting from OnApp version 6.5 Edge 1, the database dump file is named like:

time_stamp.host.database_name.sql.gz.tar

For example, 1611071664.localhost.onapp.sql.gz.tar is tar tape archive.

The tar tape archive includes:

FileUsage
time_stamp.host.database_name.sql.gz

GNU zip archive of SQL (the actual dump). For example, 1611071664.localhost.onapp.sql.gz

application_secrets.ymlYAML structured file with the key to encrypt some of the data. The file should be restored from the appropriate backup in /onapp/interface/config/application_secrets.yml during the migration of CP, or any similar actions on CP, to another box.

To backup the entire OnApp database, use the Linux mysqldump command:

#mysqldump -p onapp > onapp.sql
CODE

To backup a standalone table, use the following syntax replacing table_name with the name of the table to be backed up:

#mysqldump -p onapp table_name > onapp.table_name.sql
CODE

To find the required MySQL password, use the following command:

#cat /onapp/interface/config/database.yml | grep pass
CODE