MariaDB: Advanced usage

How to configure, optimise, export, or import MariaDB data

👋 Welcome to the Stackhero documentation!

Stackhero offers a ready-to-use MariaDB cloud solution that provides a host of benefits, including:

• Unlimited connections and transfers.
• phpMyAdmin web UI included.
• Effortless updates with just a click.
• Optimal performance and robust security powered by a private and dedicated VM.

Save time and simplify your life: it only takes 5 minutes to try Stackhero's MariaDB cloud hosting solution!

Configuring the maximum number of connections for MariaDB

At Stackhero, you are not limited by a fixed number of simultaneous connections your server can handle. You have the flexibility to adjust this value directly from your Stackhero dashboard.

While it may be tempting to set a very high connection limit, it is best to choose a number that matches your actual usage. Each connection consumes memory (RAM), so setting the limit too high can lead to resource exhaustion and potentially cause your system to crash.

Optimising MariaDB for InnoDB or MyISAM engines

If your database uses the InnoDB storage engine, you can enable the "InnoDB Optimisations" option in your dashboard. This helps improve performance for InnoDB-based workloads.

For databases using the MyISAM storage engine, enabling the "MyISAM Optimisations" option can also provide benefits.

If you are unsure which option is best for your setup, it is generally safe to enable both initially. As you monitor your resource usage and performance, you can then decide whether to disable one or both to save memory (RAM).

Query cache

The MariaDB query cache is a useful feature that you can enable directly from the Stackhero dashboard. It is generally advisable to keep it enabled. However, if your database performs more writes than reads, or if you wish to reduce memory usage, you may consider disabling it.

Using MariaDB CLIs (mysql, mysqldump, etc.)

MariaDB command-line tools such as mysql and mysqldump are essential for tasks like importing or exporting data.

Running these tools inside a Docker container simplifies setup, especially if you prefer not to install software directly on your computer.

If Docker is not part of your workflow, that is absolutely fine! You may wish to try Code-Hero on Stackhero instead. Code-Hero is a comprehensive development platform that runs directly in your browser, so there is no need for any local installation. Discover all its features and get started in just a few minutes by visiting Code-Hero on Stackhero.

To get started with Docker, you can launch a MariaDB container using the following command:

docker run -it -v $(pwd):/mnt mariadb:<MARIADB_VERSION> /bin/bash

Replace <MARIADB_VERSION> with the specific MariaDB version you require. For example, if your application uses MariaDB version 10.11.6-0 on Stackhero, you can use version 10.11.6 (simply omit the -0 suffix).

Once your container is running, you can check your connection with:

mysql -u root -p -h <XXXXXX>.stackhero-network.com -P <PORT>

When you start the container, your current directory is mounted to /mnt inside it (thanks to $(pwd):/mnt). This means any file in your current directory on your computer will be accessible in /mnt inside the container. For example, to back up a MariaDB database to your machine, run this command inside the container to save the backup as /mnt/<DATABASE>.sql:

mysqldump -u root -p -h <XXXXXX>.stackhero-network.com -P <PORT> <DATABASE> > /mnt/<DATABASE>.sql

Exporting a MariaDB database

You can use the mysqldump CLI from your computer to export a database. For step-by-step details, please refer to the section above.

Exporting a MariaDB database to your computer

To export a database from your Stackhero instance to your computer, use the following command:

mysqldump -u root -p -h <XXXXXX>.stackhero-network.com -P <PORT> <DATABASE> > <DATABASE>.sql

Be sure to replace <XXXXXX>.stackhero-network.com, <PORT>, and <DATABASE> with your own details. After you press Enter, mysqldump will prompt you for the root password. It will then export all tables from your database into the file <DATABASE>.sql.

Importing a MariaDB database to your server

To import a database from your computer to your Stackhero instance, use:

mysql -u root -p -h <XXXXXX>.stackhero-network.com -P <PORT> <DATABASE> < yourDump.sql

Simply replace yourDump.sql with the SQL file you wish to import into your Stackhero instance.