The database server

What this page is for: look after the one database server (MariaDB) every project on this server shares — see whether it is healthy, restart it, choose the time zone it runs on, see every database in it and whose each one is, open any of them in phpMyAdmin signed in as its own user, and add MariaDB settings of your own.

Open Server and the Database tab.

You need

• The owner's login to change anything here. A temporary login can read the page; the read-only demo shows it but changes nothing.
• For a time-zone change: a quiet moment. Writes to every database pause while it runs (sites stay readable).

The rule that does not move: one MariaDB serves every project and website on this server. A restart, a time-zone change or a setting here is felt by all of them — each confirm on this page says so before you press it.

At a glance

The banner at the top says whether the database server is running, with its version, how long it has been up, how many connections are in use and how much data it holds. Live logs follows MariaDB's own log; Restart restarts it — every project loses its database for a few seconds, and the background workers (queue, scheduler) are paused first and started again afterwards, so no job fails.

Below it: the version (from your distribution's own packages, kept up to date by its security updates), uptime, connections now (hover for the peak), the number of databases, data on disk, the memory for data (the InnoDB buffer pool SixPanel's auto-tune sized for this server), how much of what is read comes straight from that memory, free disk, and the server's character set.

If the server is not answering, the banner says so with systemd's own word for its state. Press Restart, and open Live logs to see why it stopped.

Time zone

What it is: the zone MariaDB shows dates in and stamps them with — NOW(), CURRENT_TIMESTAMP, and every TIMESTAMP column as an app reads it. It is the database's, not the panel's: the timezone you picked for the panel (first-run setup, Settings) only changes what the panel shows, and never moves the database.

The default is UTC, and it is the safe one: the same data reads the same on any server, and a change of the server clock re-labels nothing. Run the database on another zone only if your apps read dates the database stamps itself and expect your local time.

The card shows the zone the database runs on now, its clock, your panel's timezone, and the last time the zone was changed.

Change it

Choose a zone under Run the database on — UTC (recommended) first, then your panel's own zone, then every offset from UTC−12:00 to UTC+13:00 — and press Change time zone. The panel first reads what the change involves and shows it: how many tables hold dates, in which databases, about how many rows, and how long writes will pause. Confirm, and it:

1. pauses writes to every database — sites stay readable, but anything that saves (an order, a sign-in, a form) waits until it finishes;
2. rewrites every stored TIMESTAMP so it reads exactly what it reads now, a chunk at a time, and checks each chunk before keeping it — DATETIME columns read the same under any zone and are not touched;
3. switches the database to the new zone and pins it there, so a restart cannot move it;
4. reconnects every app and turns writes back on.

If anything does not check out, every chunk is put back and the database stays on its old zone, exactly as before. Afterwards only the dates the database stamps from then on follow the new zone; nothing already stored reads differently.

That holds for an app that reads its dates through the database's zone, as most do — neither 6amMart codebase does otherwise. An app that sets a time zone of its own on its database connection reads its TIMESTAMP columns through that one, whatever the database runs on: for such an app a change of the database's zone shifts those times by the difference. If you run one, leave the database on the zone it is on.

Why an offset, not a city: a database zone here is a fixed offset from UTC (UTC+05:30, UTC+03:00, UTC−05:00 …). It does not follow summer time — a city's name would need MariaDB's own zone tables, which Ubuntu leaves empty, and a missing one stops MariaDB from starting at all. If your panel's zone changes its clocks in summer, the page says so: there, UTC is the better choice.

Moving projects to another server? You do not have to match the two databases' zones first. A move asks both servers how their database reads a stored time and, when they differ, carries what every date reads here — an order of 21:28 reads 21:28 there, whatever zone that server's database runs on (Move projects to another server). The move asks twice: before anything is copied, and again at the moment the database is copied. A website whose app sets a time zone of its own on its database connection is the exception — An app with a time zone of its own says what happens to it and what to do. One case is refused before anything is copied: a receiving server whose database still follows a zone with summer time. Change that database to UTC here, on that server, first.

Not during a move. A change of zone rewrites every stored date on the server, so it is refused while a project or a website is being moved to another server, while one is still arriving here, during a migration of the whole server and in the maintenance window — the message says which. And a move is refused while a change of zone is under way or was stopped part way: finish it, or put it back, first.

A backup from before a change of time zone

A change of zone rewrites what is in the database at that moment. A backup taken before it was not there to be rewritten: it still holds every TIMESTAMP the way the old zone stored it. Put such a backup back after the change and it is whole — every row, every table — but its TIMESTAMP values are read through the new zone, so each reads off by the difference between the two zones (an order of 21:28 taken on UTC+05:30 reads 15:58 on UTC). DATETIME columns and everything else read as they did. The restore says so in its log, in a line that begins note: this backup is older than the last change… — for a project and for a website.

So, after a change of zone, run a backup (Backups → Run backup now): from that one on, every backup matches the zone the database runs on.

The same is true on another server. A backup restored on a server whose database runs on a different zone than the one it was taken on reads its times through that server's zone. A server newly set up with SixPanel runs its database on UTC; one set up before 1.5.2 ran it on the server's clock — the timezone chosen in its first-run setup — unless that was changed (its Health → Database time zone names it). The restore cannot tell there, and says nothing.

To put such a backup back with its times unchanged — an older backup on this server, or any backup on a server whose database runs on another zone:

1. put the database on the zone that backup was taken on (Change it, above; on a server that holds no data yet this takes a moment);
2. restore the backup;
3. change the zone to the one you want — UTC, say. That rewrites the restored times together with all the others, so they keep reading the same.

This works when the zone the backup was taken on is UTC or a fixed offset — a zone that never changes its clocks is its offset (India is UTC+05:30). A backup taken on a zone that follows summer time cannot be put back exactly this way: the times of one half of its year stay an hour off. Moving a project from a server that still runs has no such limit: a move carries what every time reads.

If a change stopped part way

The card turns red and writes stay paused, so no date is stored two different ways. Try the move again finishes it; Put it back undoes every chunk already rewritten and turns writes back on, on the old zone. If the panel itself restarts during a change, it finishes the change when it comes back. Server → Health shows the state too.

Databases

Every database in this MariaDB, largest first, with its size, number of tables and character set — and the project or website it belongs to, linked to that project's own Database page (backups, slow queries). A database the panel did not make says so; a deleted project's database is kept for a few days so it can be restored, and is marked that way.

Every database the panel made has Open in phpMyAdmin beside it — see phpMyAdmin below.

Remove a database

A database that no project and no website on this server uses has Remove beside it. That is one the panel did not make — left by an earlier install, or made by hand — and the two the panel made and no longer needs: what a deleted website left behind when its database could not be removed at the time, and the scratch copy of a backup check that was cut short.

• A project's or a website's own database has no Remove here, and neither has a deleted project's kept one. Delete the project or the website (or let the deleted project be purged) and its database goes with it — together with its account, its files and its record, which this button would leave behind.
• The panel cannot know whether something outside it still uses the database. Look at its size and its tables first; that judgement is yours.

Press Remove, type the database's name, and choose whether to keep a copy on this server first (ticked unless you untick it). It runs as a job you can watch:

1. it checks again that nothing owns the database;
2. it writes the copy — a .sql.gz in /opt/sixpanel/data/removed-databases/ that only root can read — and reads it back to make sure it is whole. If the copy cannot be made (a full disk, for one), the job stops there and nothing is removed;
3. it removes the database. No database account is touched — with one exception: a database a deleted website left behind goes together with the two accounts the panel had made for that website, as the website's delete meant.

The copy is kept for 7 days and then deleted by itself; the list shows each copy with the date it goes. It holds the database's tables, views and triggers, and its stored routines and events when it has any. (On a server where MariaDB's own record of routines or events cannot be read, the copy is still made without them, and the job's log says so in a line that begins note:.)

To put a database back from its copy, on the server over SSH — the job's log prints this command with both names filled in:

sudo mariadb -e 'CREATE DATABASE `<name>`' && gunzip -c '/opt/sixpanel/data/removed-databases/<file>.sql.gz' | sudo mariadb --database='<name>'

The first half makes the database new, and refuses when a database of that name is on this server; then nothing is written. That is deliberate: by the time you want a copy back, the name may belong to something else — a project or a website made since, or one a move brought here — and a copy read into a database that is in use replaces its tables. If the name is taken and you still want the old data, put it back under another name (change both <name>s) and look at it there. Without a copy, a removed database cannot be brought back.

If the command stops part way — the disk fills up, say — the database it made is still there, with what had been read in so far, and running the command again is refused for that reason. Remove that database on this page first (it belongs to no project and no website, so the page offers it), then run the command again.

This is also the way out when a move to this server is refused with "a database called … already exists and no project owns it" (or "… and no website there owns it"): remove the database here, then start the move again (Move between servers).

phpMyAdmin

What it is: the familiar database browser, for looking inside a database by hand — its tables and rows, a query, an export of one table. One phpMyAdmin serves the whole server, and it runs only while it is On: its own PHP workers and a port on the server itself, reached only through the panel while you are signed in. Off, it has no workers and no port, and costs nothing.

The card shows whether it is on, its version and, while it is on, how much memory its workers use. Turn on and Turn off switch it. Open phpMyAdmin shows phpMyAdmin's own sign-in, for a database user and password you type yourself.

Open a database, signed in as its own user

Open in phpMyAdmin beside a database — and on that project's or website's own Database page — opens phpMyAdmin in a new tab, already signed in as that database's own user. It sees that database and nothing else, and there is no password to look up or copy. If phpMyAdmin is off, it is turned on first.

Why no password leaves the server: the panel checks that the user and password it holds for the database still open it, puts them in a single-use sign-in that lasts one minute and that only phpMyAdmin can read, and opens phpMyAdmin with nothing but the database's name in the address. phpMyAdmin reads the sign-in and deletes it. It works once, for the browser that asked for it: the same address opened again, or in another browser, signs nobody in. Log out in phpMyAdmin ends the sign-in, and turning phpMyAdmin off ends every sign-in not yet used.

A database the panel did not make, or a deleted project's, has no button: use Open phpMyAdmin and sign in with its user.

While a project or a website is being moved to another server, from the moment the move pauses it until the move ends, Open in phpMyAdmin is refused for its database: the database has been copied by then, and what you changed would be on this server only. A project or a website that is still arriving from another server is refused too. A copy a move left parked here opens as usual. Close a phpMyAdmin tab that is signed in to a database before you move its project — the panel cannot end a sign-in that already exists (Move projects to another server says what a move does about that).

Who can use it: the owner. A temporary login sees whether phpMyAdmin is on but cannot turn it on or off or open it, and neither can the read-only demo. Tia, the AI assistant, may turn it on or off — asking you first, like any other change, unless you set her to Auto — and can never open it or sign in to a database with it.

Settings

MariaDB settings of your own for the whole server, one name = value per line — for example max_connections = 300 or long_query_time = 1. They win over SixPanel's tuned values (shown folded above the box, by file), and a SixPanel update never undoes them.

Some of these are values Settings → Performance sizes for you — the database memory (innodb_buffer_pool_size), temporary tables, the join and sort buffers, the transaction log and the table cache. When you set one here, the Performance card shows your value as what is running, marks the row set by you on Server → Database, and Apply tuning no longer offers to change it. Take the line out here to hand that value back to auto-tune.

One project cannot use every connection. Beside max_connections, SixPanel sets max_user_connections — the most connections a single database account may hold at once — to max_connections less a reserve of 30. A project with a connection leak then runs into its own limit (User … already has more than 'max_user_connections' active connections) instead of stopping every other project from connecting. When you set max_connections here the limit moves with it at once; to choose it yourself, add a max_user_connections line and yours wins.

Every save is checked before it is kept:

• the name must be a setting this MariaDB has;
• MariaDB must accept the whole configuration with your lines in it;
• a value it can take while running is in effect at once — nothing restarts;
• a value that only changes when the database starts (for example performance_schema) restarts it: the page asks first and says which settings need it. If MariaDB does not start with the new values, your previous settings are put back and it starts on them.

A few settings are never taken here because the panel depends on them — the data directory, the socket, the port, the network address, the time zone (use the card above), read_only, the general log, and a handful of others. The page shows the reason when you try one. Two values are refused because they would stop MariaDB on this server: a buffer pool larger than three quarters of its memory, and more than 10,000 connections.

Your lines are kept in /etc/mysql/mariadb.conf.d/97-sixpanel-owner.cnf. Change them here, not by hand: a hand edit is not checked, and Health reports it.

How to check it worked

• The banner says The database server is running and the time-zone card names the zone you chose, kept there across restarts.
• Server → Health → Database time zone is green and names the zone.
• After a settings save, the toast says in effect now, or the restart job finishes with the database is running with the new settings.
• Open in phpMyAdmin opens a tab on that database's tables, with its own user named at the top of phpMyAdmin — and no sign-in form on the way.
• After Remove, the job ends with … is removed, the database is gone from the list, and — when a copy was kept — Copies kept of removed databases under the list shows it.

If it went wrong

• "… belongs to the project …", "… belongs to the website …" or "… is kept for the deleted project …" when removing a database — it has an owner, so it is removed by deleting (or purging) that owner, not here.
• "mariadb-dump failed" or "gzip failed … is the disk full?" while removing a database — the copy could not be written, and nothing was removed. Free some disk, or untick Keep a copy if you are sure you do not need one, and press Remove again.
• "MariaDB refused these settings, so nothing was changed" — the message names the line. Correct or remove it and save again.
• "MariaDB would not start with …, so the previous settings were put back" — the database is running again on what you had before. The value was too large for this server, or not one MariaDB accepts at start.
• "the database's time zone cannot be changed right now: …" — a move to another server is running, something is still arriving on this one, or the maintenance window is open; the message names it. Try again when that has finished.
• "The database cannot be moved yet" — a table blocks the time-zone change (an UPDATE trigger, system versioning, a foreign key on a date column, a non-InnoDB table holding dates). Nothing was changed; the message names the table and what to do.
• "the user SixPanel holds for … no longer opens it" — the database's password was changed outside SixPanel. Give the database a new password from its project's or website's Database page, or use Open phpMyAdmin and sign in by hand.
• "… is being moved to … right now and is paused for it: its database has been copied, and a change made in phpMyAdmin now would be on this server only" (or "… is still arriving from …") — a move has this project or website right now. Open it when the move has finished; Migration shows where it is.
• "This sign-in has already been used" or "has expired" — a sign-in works once, within a minute. Press Open in phpMyAdmin again.
• "The database did not accept this sign-in" — phpMyAdmin's own reason follows it. Usually the database's password was changed a moment ago; open it again.
• The database server is not answering — see Troubleshooting.