MySQL¶

We’re providing MariaDB 10.6 as a MySQL-compatible database server. If you’re already used to use MySQL, you can lean back calmly: To avoid confusion, MariaDB uses the same command names you already know, like mysql, mysqldump etc. - just use them as usual.

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

Webinterface¶

You can manage your databases via phpMyAdmin or adminer.

Login credentials¶

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

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

Your MySQL 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@stardust ~]$ my_print_defaults client
--default-character-set=utf8mb4
--user=isabell
--password=SomeStrongPassword

Changing 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@stardust ~]$ mysql -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 20 random characters, containing a mix of upper and lowercase ASCII letters, the numbers 0-9 and punctuation (.,:-_).

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 MySQL 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@stardust ~]$ my_print_defaults clientreadonly
--user=isabell_ro
--password=SomeOtherStrongPassword

Unfortunately you cannot change the password of the read-only user yourself (it’s read-only!). If you really need to change it, please contact hallo@uberspace.de.

Additional databases¶

In addition to the default database named like your Uberspace 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@stardust ~]$ mysql -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@stardust ~]$ mysql -e "SHOW DATABASES"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| isabell              |
| isabell_gitea        |
| isabell_shopware6    |
+--------------------+

To remove databases, use the DROP DATABASE SQL statement:

[isabell@stardust ~]$ mysql -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 Uberspace or vice-versa. We dump all databases every day and keep them as backup.

Creating dumps¶

The mysqldump 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@stardust ~]$ mysqldump 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@stardust ~]$ mysqldump isabell table1 > isabell.table1.sql
[isabell@stardust ~]$ mysqldump 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@stardust ~]$ mysqldump isabell | xz > isabell.sql.xz

Importing dumps¶

As dumps are just files containing SQL statements you can feed them into the mysql 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@stardust ~]$ mysql isabell < isabell.sql

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

[isabell@stardust ~]$ xzcat isabell.sql.xz | mysql 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@stardust ~]$ mysqldump isabell | mysql 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@stardust ~]$ ssh otheruser@some.other.host mysqldump otherdatabase | mysql isabell

Monitoring and analysing sql queries¶

You can use the tool mytop to monitor and analyse your mysql queries easily. Just type the command in your terminal:

[isabell@stardust ~]$ mytop

The tool will show you all running mysql processes, press ? to show additional options.

Custom settings¶

UTF-8¶

The default encoding for MySQL is still latin-1, which can cause a lot of problems if you typically use Unicode characters and forget to explicitly set the encoding to UTF-8 every time. Therefore, we changed the default encoding to utf8mb4, which enables you to use all UTF-8 characters, including emojis 😊.

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 mysql port 3306:

localuser@localhost ~ $ ssh isabell@stardust.uberspace.de -L 3306:127.0.0.1:3306

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 Uberspace.)

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.