MariaDB

We're providing MariaDB 11,7 as a MySQL-compatible database server.

Our default setup provides you with a database and a user named like your Asteroid, but you can create additional databases later.

[isabell@moondust ~]$ mariadb -e "SHOW DATABASES"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| isabell            |
+--------------------+

What about mysql?:

If you're already used to use MySQL and the commands like mysql, mysqldump, you can use the same commands for some time with MariaDB. In the long run you should learn the commands for MariaDB, in future only mariadb will work, mysql is deprecated.

Login credentials

Applications based on MariaDB databases will ask you for a username, a password, a database name and possibly a host/port.

Field	Value
Username	equals your username
Password	see below
Database	equals your username
Host/Port	localhost

Your MariaDB password differs from any other password. We've created a strong one and put it into the file ~/.my.cnf which is used by the MariaDB command-line tools to automatically log you in. Take a look into that file or execute my_print_defaults client to show it, like that:

[isabell@moondust ~]$ my_print_defaults client
--default-character-set=utf8mb4
--user=isabell
--password=SomeStrongPassword

Change your password

Your password can be changed with the SET PASSWORD SQL statement. Tools like Adminer or phpMyAdmin provide you with a web-based way of doing that (if you installed them), but it can easily be done on the shell as well:

[isabell@moondust ~]$ mariadb -e "SET PASSWORD = PASSWORD('YourNewPassword')"

If you don't see any output, it's a good thing; MariaDB only complains if something went wrong.

Warning:

It is very important to put the new password into your ~/.my.cnf file with a text editor of your choice. That way, MariaDB command-line tools are still able to automatically log you in.

Password Requirements

We generate a passwords for you on user creation. It consists of 40 random characters, containing a mix of lowercase ASCII letters and the numbers 0-9.

But we only enforce the following rules, if you want to set your own:

• A minimal length of 16 characters.

Read-only user

While most applications based on MariaDB databases support exactly one database user (and expect it to have write permissions), there are use cases for a read-only user as well, especially from a security perspective. We provide you with a separate user suffixed with _ro ("read-only") which you can use in these cases. This user has a different password than the default read/write user which can also be found in your ~/.my.cnf file; you can also execute my_print_defaults clientreadonly to show it, like that:

[isabell@moondust ~]$ my_print_defaults clientreadonly
--user=isabell_ro
--password=SomeOtherStrongPassword

You cannot change the password of the read-only user yourself (it's read-only!). If you really need to change it, please contact our support.

Additional databases

In addition to the default database named like your Asteroid you can also create an unlimited number of additional databases prefixed with your username and _ - if your username is isabell you can create databases named like isabell_blog, isabell_shop.

New databases can be created with the CREATE DATABASE SQL statement. Tools like Adminer or phpMyAdmin provide you with a web-based way of doing that (if you installed them), but it can easily done on the shell as well:

[isabell@moondust ~]$ mariadb -e "CREATE DATABASE isabell_blog"

If you don't see any output, it's a good thing; MariaDB only complains if something went wrong.

You can list your additional databases using the SHOW DATABASES SQL statement:

[isabell@moondust ~]$ mariadb -e "SHOW DATABASES"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| isabell            |
| isabell_blog       |
+--------------------+

To remove databases, use the DROP DATABASE SQL statement:

[isabell@moondust ~]$ mariadb -e "DROP DATABASE isabell_blog"

If you don't see any output, it's a good thing; MariaDB only complains if something went wrong.

Working with dumps

Dumps are the default way of exporting/importing databases. You can use them as a backup or to migrate an existing database dumped on another host to your Asteroid or vice-versa. We dump all databases every day and keep them as backup.

Creating dumps

The mariadb-dump command allows you to dump tables or whole databases, represented by a bunch of SQL statements that will re-create the table structures and re-insert all data when executed. The most common use is to redirect its output into a file, like that:

[isabell@moondust ~]$ mariadb-dump isabell > isabell.sql

This command dumps all tables of the isabell database at once. If you just want to dump a single or a few tables, put their names behind the database name:

[isabell@moondust ~]$ mariadb-dump isabell table1 > isabell.table1.sql
[isabell@moondust ~]$ mariadb-dump isabell table2 table3 > isabell.table2and3.sql

As the resulting files are plain text files (remember, they are just a bunch of SQL statements) you can easily compress them on the fly, e.g. with xz:

[isabell@moondust ~]$ mariadb-dump isabell | xz > isabell.sql.xz

Importing dumps

As dumps are just files containing SQL statements you can feed them into the mariadb command, importing them into a database of your choice. For example, to import the dump named isabell.sql into your database isabell (overwriting existing tables, if any):

[isabell@moondust ~]$ mariadb isabell < isabell.sql

Or in case of a compressed dump, use xzcat to uncompress the data before feeding it into MariaDB:

[isabell@moondust ~]$ xzcat isabell.sql.xz | mariadb isabell

Streaming dumps

In case you want to copy a database into another one, or from one running MySQL or MariaDB host to another, there's no need to write the dump into a file at all. Given that you already created a database named isabell_copy you can copy all data from isabell over to your new database:

[isabell@moondust ~]$ mariadb-dump isabell | mariadb isabell_copy

This will also work over SSH - for example to dump a database on some other host you're having shell access to as well, this is what you're able to do to import all tables of a remote database named otherdatabase into your local database isabell (overwriting existing tables, if any):

[isabell@moondust ~]$ ssh otheruser@some.other.host mariadb-dump otherdatabase | mariadb isabell

External connection

For security reasons we don't allow external connections to your databases. However, if you want to connect somehow "directly" from a remote host, you can do so by using an SSH tunnel.

This is how you can initiate an SSH connection offering a tunnel for the MariaDB port 3306:

[localuser@localhost ~]$ ssh isabell@moondust.uberspace.de -L 3306:127.0.0.1:3306
[...]
[isabell@moondust ~]$

From now on, you can talk to 127.0.0.1:3306 on your local host to connect to your database. (While in fact, it's OpenSSH listening on port 3306 of your local host, tunneling the connection to your Asteroid.)

Tip:

When using Windows 8.1 or any other not up-to-date versions of Windows, you will most probably not be able to use SSH as natively as shown here. You will either need to update to a more recent version of Windows or use our howto for Putty DB connection instead of the commands used here.