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 maximum connections for MariaDB

At Stackhero, there are no predefined limits on the number of simultaneous connections your server can handle. You have the freedom to adjust this value directly from the Stackhero dashboard.

While it might be tempting to set a high connection limit, it is wise to choose a realistic number. Keep in mind that each connection consumes memory (RAM), and too many connections could lead to a crash if system resources become overloaded.

Optimising MariaDB for InnoDB or MyISAM engines

If your database is using the InnoDB storage engine, you might want to consider enabling the "InnoDB Optimisations" option available in your dashboard.

For databases using the MyISAM storage engine, activating the "MyISAM Optimisations" option could also be beneficial.

If you are unsure which option is best for your needs, it is usually a good idea to enable both by default. You can always disable them later to conserve memory (RAM) once you understand their impact better.

Query cache

The MariaDB query cache is another valuable feature you can enable from the Stackhero dashboard. It is generally recommended to keep it enabled. However, if your workload is more write-intensive than read-oriented and conserving memory (RAM) is crucial, you might consider disabling it.

Using MariaDB CLIs (mysql, mysqldump, etc.)

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

Running these tools inside a Docker container can significantly simplify the setup process.

If Docker is not part of your setup, do not worry! An alternative is to use Code-Hero on Stackhero. Code-Hero is a comprehensive development platform that operates directly in your browser, removing the need for any local installations. Discover more about its features and get started in just 2 minutes by exploring Code-Hero on Stackhero.

To begin, you can deploy a MariaDB container with the following command:

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

Replace <MARIADB_VERSION> with the specific version of MariaDB you need. For example, if your application uses MariaDB version 10.11.6-0 on Stackhero, select version 10.11.6 (omit the -0 suffix).

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

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

When you start the container, your current directory is mounted to /mnt (via $(pwd):/mnt), meaning any file in your current directory appears in /mnt inside the container. For example, to back up a database from the MariaDB service to your computer, you can use the following command to save the backup as /mnt/<DATABASE>.sql:

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

Dumping a MariaDB database

To dump a database, you can use the mysqldump CLI from your computer. For detailed instructions, please refer to the section above.

Exporting a MariaDB database to your computer

To export a database from a Stackhero instance to your computer, you can use:

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

Replace <XXXXX.stackhero-network.com>, <PORT>, and <DATABASE> with your specific details. After pressing Enter, mysqldump will prompt for the root password, and it will then export all tables from the database into the file <DATABASE>.sql.

Importing a MariaDB database to your server

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

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

Replace yourDump.sql with the SQL file you wish to import to the Stackhero instance.

Other articles for MariaDB