MySQL databases and phpMyAdmin in the web panel

In short

You create databases in the web panel for the website under Databases: enter a name, create or select a user, done. The phpMyAdmin button opens the database in the browser without a password. There you also export and import backups. Depending on your plan, 5 to 50 MySQL databases are included.

What is a database for?

Almost every dynamic website stores its content in a database: WordPress, for example, stores all posts, pages, settings and users. The files (images, themes, plugins), on the other hand, live in the file system. For a complete backup or a move you therefore need both.

Term	Meaning
Database	The storage itself, e.g. myclub_wp
Database user	The login the software uses to connect
Password	The password of this user (not your web panel password)
Host	The address of the database server from your website’s point of view, usually localhost
Table prefix	A prefix for all tables; with WordPress usually wp_

With one-click installations (e.g. WordPress), the web panel creates the database and user automatically. You only have to create them yourself for manual installations or moves.

Creating a database

1. In the web panel, open Websites and click your website.
2. Choose Databases at the top.
3. Click Add database and enter a name.
4. In the form, create a new database user or select an existing one. By default the user gets all permissions on this database – that is right for WordPress and most CMSs.
5. Confirm with Add.
6. Note the database name, username, password and the host shown. You enter these details in your software (with WordPress, in wp-config.php).

One database per application

Give each application its own database and its own user. Then a hacked application cannot read the data of the others, and you can back up and restore each one separately.

Opening phpMyAdmin

phpMyAdmin is a web interface where you can view tables, edit data, run SQL commands and do exports and imports.

1. Open the website in the web panel and choose Databases.
2. Click phpMyAdmin next to the database. You are logged in automatically.
3. On the left you choose the database, on the right the tables and actions.

Caution

phpMyAdmin has no “undo” button. Export the database before you change or delete tables.

Exporting (backing up) a database

phpMyAdmin

1. Choose the database on the left.
2. Click Exportieren (Export) at the top.
3. Method Schnell (Quick), format SQL is enough in most cases.
4. Click Exportieren. The .sql file is downloaded.

SSH

# WordPress: reads the login details from wp-config.php
cd ~/public_html && wp db export ~/export-$(date +%F).sql

# general: mysqldump with the details from the web panel
mysqldump -h HOST -u USER -p DATABASE > ~/export.sql

Save exports in the home folder, not in public_html.

Importing a database

An import overwrites existing tables with the same name. It is therefore best to import into an empty database.

phpMyAdmin

1. Create a new, empty database (see above) or empty the existing one.
2. Open phpMyAdmin and choose the database on the left.
3. Click Importieren (Import), choose the .sql file (.sql.gz or .zip also works) and click Importieren.

If the import aborts with large files, the cause is the upload limit. Raise upload_max_filesize and post_max_size (PHP settings) or import via SSH.

SSH (large databases)

Upload the .sql file to the home folder via SFTP and import it:

# WordPress
cd ~/public_html && wp db import ~/export.sql

# general
mysql -h HOST -u USER -p DATABASE < ~/export.sql

If the import shows “Unknown collation: utf8mb4_0900_ai_ci” or a similar error, the file was created on a newer or different database server. Export it again there with a compatible setting, or replace the collation in the file with utf8mb4_unicode_ci.

Users and passwords

• You change a database password in the web panel under Databases at the user. Then enter it in the software straight away (with WordPress in wp-config.php at DB_PASSWORD), otherwise “Fehler beim Aufbau einer Datenbankverbindung” (Error establishing a database connection) appears.
• Delete users and databases that no application uses any more. Every database counts against the number your plan allows.

Connecting WordPress to the database

The connection details are in public_html/wp-config.php:

define( 'DB_NAME', 'databasename' );
define( 'DB_USER', 'databaseuser' );
define( 'DB_PASSWORD', 'password' );
define( 'DB_HOST', 'localhost' ); // take the value from the web panel

If one of the values is wrong, WordPress shows “Fehler beim Aufbau einer Datenbankverbindung”. How to check this is explained in Fixing WordPress problems.

Useful SQL commands for WordPress

Always export first. Replace wp_ with your table prefix.

-- Show the current address
SELECT option_name, option_value FROM wp_options WHERE option_name IN ('siteurl','home');

-- Change the address (only siteurl and home; for content see WP-CLI search-replace)
UPDATE wp_options SET option_value = 'https://your-domain.com' WHERE option_name IN ('siteurl','home');

-- Deactivate all plugins
UPDATE wp_options SET option_value = 'a:0:{}' WHERE option_name = 'active_plugins';

Frequently asked questions

Can I access the database from outside (e.g. from my PC)?

No, direct access from outside (remote MySQL, port 3306) is not possible. Use phpMyAdmin in the web panel. If you are familiar with SSH, you can alternatively set up an SSH tunnel and connect through it to localhost – more on SSH in Uploading files.

How big can a database be?

The database counts towards your website’s storage. For projects with very large, fast-growing databases (several gigabytes), a VPS is usually the better choice.

Are databases included in backups?

Yes, the automatic backups (twice a day, 30 days) contain files and databases. See Backups and restoring.

Further reading

• Install WordPress
• Moving a website
• Backups and restoring
• Uploading files