Databases
How to look after the MariaDB database server and the accounts' databases from the Databases section of the admin area, including passwords, limits, remote access, repairs and phpMyAdmin.
The Databases section of the admin area holds the tools for the database server (MariaDB) and the databases of all accounts. Clients create and delete their own databases and database users in their own area (My Databases); as administrator you see all of them, set limits and passwords, repair tables and change how the database server runs.
Each database and database user belongs to an account by its name: the account name comes first, then an underscore, for example shop_wp belongs to the account shop.
Manage Databases#
In the admin area, open Databases → Manage Databases (Manage Databases). The page lists every database on the server with the account it belongs to, its number of tables and its size in MB.
Click an account name to open that account's page at its databases. Databases marked "none (the server's own)" belong to the database server itself (such as mysql) or were made by hand outside the panel.
Good to know:
- Databases are created and deleted on the account's own page (under Account Lists → All Accounts), or by the client in their own area, so each stays tied to its account and counts towards its package limit.
- To see which database user belongs to which account, use Database Users or Database Name Prefixes.
- To browse or edit the contents of a database, use phpMyAdmin.
Database Users#
In the admin area, open Databases → Database Users (Database Users). The page lists every database user on the server with the host it may connect from (such as localhost, or a remote address) and the account it belongs to.
A database user with several "may connect from" entries appears once for each. Users marked "none (the server's own)" belong to the database server itself or were made by hand.
Database users are created, given rights on databases and deleted by the client in their own area, or on the account's page. To set a new password for one, use Database User Password. To let a user connect from another server, add that server under Remote Database Access.
Database Root Password#
In the admin area, open Databases → Database Root Password (Database Root Password). This sets the password of the database server's own root user, for tools that ask for one.
- Enter the New password (at least 12 characters).
- Enter it again in The same again.
- Click Change the password.
Good to know:
- This is not the server's root password (that is under Server Setup → Root Password) and not your panel login.
- The panel itself does not use this password: it reaches the database through the server's local socket, which keeps working whatever the password is.
- With some versions of the database server, the message after the change says that tools must connect to host
127.0.0.1(notlocalhost) to use the password. Follow that when you set up such a tool. - Keep the password somewhere safe; the panel cannot show it again.
Database User Password#
In the admin area, open Databases → Database User Password (Database User Password). Use it to set a new password for any account's database user, for example when a client has lost it.
- Choose the Database user; the list shows each user with its account.
- Enter the New password (at least 10 characters) and enter it again.
- Click Change the password.
The new password applies at once, also on the hosts listed under Remote Database Access.
Good to know: a website that signs in with this user (WordPress, for example) stops working until the new password is put into its settings file, such as wp-config.php. Tell the client, or change the file yourself.
Database Usage Limits#
In the admin area, open Databases → Database Usage Limits (Database Usage Limits). These limits keep one account from slowing the database server down for everyone.
Two limits are set per package (under Packages): Database connections at once, per database user and Database queries per hour, per database user. This page adds one server-wide setting:
- In End queries of hosting accounts that run longer than (seconds), enter a number of seconds, or 0 to leave long queries alone. 60 to 300 suits most shared servers.
- Click Save.
The Accounts table shows each account's package, connections now, queries running, its longest running query and its limits. Apply the limits again now sets the package limits on every database user again, for example after users were changed by hand.
Queries ended lists the queries that were stopped for running too long, with the account, database user, how long it ran and the query, since the panel's service last started.
Good to know: backups and imports run by the panel itself are never ended. If the database server does not answer, the page says so.
Database Name Prefixes#
In the admin area, open Databases → Database Name Prefixes (Database Name Prefixes). The page shows which account owns which database, and which account owns which database user.
intoPanel ties databases and database users to accounts by the first part of their name: account_name. For example the database shop_wp and the user shop_admin belong to the account shop. The panel keeps that link when an account is renamed or transferred.
"No account" marks the server's own databases (such as mysql) and any database or user made by hand outside the panel without a matching account name.
Use this page to check that every database has an owner, for example after a migration or after databases were created from the command line. A database without an owner is not tied to any account, so the account tools do not see it; create databases from the account (or with the right account prefix) instead.
Database Server Settings#
In the admin area, open Databases → Database Server Settings (Database Server Settings). The same page is under Service Settings → Database Configuration. Here you set a chosen list of MariaDB options. Leave a field empty to use MariaDB's standard value; each field shows the form its value takes.
Options include max_connections, innodb_buffer_pool_size, max_allowed_packet, wait_timeout, interactive_timeout, max_connect_errors, open_files_limit, table_open_cache, thread_cache_size, buffer sizes (key_buffer_size, tmp_table_size, max_heap_table_size, join_buffer_size, sort_buffer_size, read_buffer_size, read_rnd_buffer_size, innodb_log_buffer_size), innodb_flush_log_at_trx_commit, slow_query_log, long_query_time, performance_schema and sql_mode.
Click Apply and restart MariaDB. If MariaDB does not start with the new settings, the previous ones are put back.
See Suggested Settings works out values from the server's memory, processors, load and disk, and, once the database has run for six hours or more, from its own figures. Untick what you do not want and click Apply the Ticked Settings.
Good to know: applying restarts the database, which interrupts websites for a few seconds. The InnoDB buffer pool may be between 64M and 70% of the server's memory.
Remote Database Access#
In the admin area, open Databases → Remote Database Access (Remote Database Access). Here you list other servers that may use the databases on this server, for example a separate web server. Every account's database users can then sign in from there with their own names and passwords.
To add a server, enter its IP address (for example 203.0.113.20) and click Add host. To take it off again, click Remove.
When the list is empty, the databases answer on this server only.
Good to know:
- One IP address per host; names and wildcards are not accepted.
- The firewall opens the database port (3306) for the listed addresses only.
- New database users and changed passwords reach the listed hosts at once; changes made outside the panel follow within five minutes.
- Clients can also allow hosts for their own account's databases under Remote Database Access in their own area.
Database Servers#
In the admin area, open Databases → Database Servers (Database Servers). The panel can keep its databases on this server's MariaDB or on a separate database server. The active server is where new databases and database users are made, and where websites, backups and phpMyAdmin find them.
To add a separate database server:
- Click Add a Server.
- Enter a Name, the Database server address and Port (default 3306).
- Enter a Username and Password of a user there that may create databases and users and grant rights (ALL PRIVILEGES ON . WITH GRANT OPTION), allowed in from this server.
- In Users allowed in from, enter the address the database server sees this server come from (default: this server's main address).
- Click Save. The panel signs in to test the connection first.
Test the connection checks a server, Activate makes it the active one, Edit and Delete change or remove a non-active remote server. The local server cannot be edited or deleted.
Good to know: databases made before stay on the server they were made on. Switching does not move them, so switch before you create accounts, or move their databases yourself.
phpMyAdmin#
In the admin area, open Databases → phpMyAdmin (phpMyAdmin) and click Open phpMyAdmin. It opens in a new tab.
phpMyAdmin lets you browse and edit every database of this server in the browser: look at tables, run SQL queries, import and export data.
Good to know:
- You are signed in automatically, with full rights over every database. Take care: changes made there take effect at once and cannot be undone.
- Clients open phpMyAdmin from their own area and see only their own databases.
- A database user can also sign in with its own name and password at
https://<panel address>/phpmyadmin/; the page shows the exact address for this server. - For large imports or exports, a backup or the client's own database tools may be more reliable than the browser.
Repair Databases#
In the admin area, open Databases → Repair Databases (Repair Databases). It checks database tables and repairs those found damaged, where their kind allows it.
To repair one database:
- Choose it in the list (each shows its account).
- Click Repair database.
To check every database on the server, click Check and repair all now under Check and repair all databases.
The check runs in the background and the page shows its progress: each table that is not "OK" is listed, followed by a count of tables checked. Only one long task runs at a time, so the buttons are disabled while another one runs.
Good to know:
- Websites keep working during the check; large databases take a while.
- InnoDB tables, the usual kind, are checked but cannot be repaired this way; if one is reported damaged, restore the database from a backup.
- Use it when a website reports errors such as "table is marked as crashed".
Running Database Queries#
In the admin area, open Databases → Running Database Queries (Running Database Queries). The page shows what the database server is working on right now, longest-running first.
Each row shows the process ID, the database user, the database, what it is doing (with its state), how many seconds it has run and the statement (the first 300 characters).
To stop one, click End in its row. That ends that one statement or connection; the website that sent it gets an error for that request.
Use it when the server is slow and a website seems to hang: a query that has run for a long time is often the cause. To end such queries automatically, set a time limit under Database Usage Limits. Connections in the state "Sleep" are idle and usually harmless.
Database Server Version#
In the admin area, open Databases → Database Server Version (Database Server Version). The page shows the version of MariaDB that runs on this server.
intoPanel uses the MariaDB version that the operating system provides and supports for the server's release. Security and bug-fix releases of it arrive with the system updates under Software → Update System Software.
Moving to another major MariaDB version is not offered from the panel: it replaces the database server under running websites and cannot be undone. If you need it, make full backups of all databases first and do it from the server's console.
Something missing? Ask in the community or open a ticket.