MariaDB: Advanced usage

How to configure, optimize, 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 maximum connections for MariaDB

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

While it might seem appealing to set a very high connection limit, it is a good idea to choose a number that matches your actual usage. Each connection uses memory (RAM), so setting the limit too high could cause your system to run out of resources and potentially crash.

Optimizing MariaDB for InnoDB or MyISAM engines

If your database uses the InnoDB storage engine, you may want to enable the "InnoDB Optimizations" option in your dashboard. This can help improve performance for InnoDB-based workloads.

For databases built on the MyISAM storage engine, turning on the "MyISAM Optimizations" option can also provide benefits.

If you are not sure which option is best for your setup, it is usually safe to enable both at first. Later, as you monitor your resource usage and performance, you can decide whether to disable one or both to conserve memory (RAM).

Query cache

The MariaDB query cache is a helpful feature that you can enable right from the Stackhero dashboard. It is generally recommended to keep it turned on. However, if your database handles more writes than reads, or if you need to minimize memory usage, you might 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 can simplify setup, especially if you want to avoid installing software directly on your computer.

If Docker is not part of your workflow, that is perfectly fine! You might want to try Code-Hero on Stackhero instead. Code-Hero is a full-featured development platform that runs right in your browser, so there is no need for local installations. You can learn more about its capabilities and get started in just a couple of minutes by visiting Code-Hero on Stackhero.

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

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

Replace <MARIADB_VERSION> with the specific MariaDB version you need. If your application runs MariaDB version 10.11.6-0 on Stackhero, for example, you can use version 10.11.6 (just leave off 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 appear in /mnt inside the container. For example, if you want to back up a database from MariaDB to your machine, you can 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

Dumping a MariaDB database

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

Exporting a MariaDB database to your computer

To export a database from your Stackhero instance to your computer, you can use this 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, you can use:

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

Just replace yourDump.sql with the SQL file you want to import into your Stackhero instance.