MySQL: Advanced usage

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

👋 Welcome to the Stackhero documentation!

Stackhero offers a ready-to-use MySQL 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 MySQL cloud hosting solution!

Configuring MySQL maximum connections

At Stackhero, there is no fixed limit on the number of simultaneous connections a server can manage. You can adjust this setting directly from the Stackhero dashboard to fit your specific requirements.

While it may be tempting to set a very high limit, it is best to choose a sensible value. Remember that each connection consumes memory (RAM). Setting an excessively high limit could strain the instance's resources, potentially leading to instability in the database.

Optimising MySQL for InnoDB or MyISAM engines

For databases utilising the InnoDB storage engine, enabling the "InnoDB Optimisations" option in the Stackhero dashboard is recommended.

Similarly, for those using the MyISAM storage engine, activating the "MyISAM Optimisations" option can enhance performance.

If you are uncertain which optimisation is suitable for your setup, enabling these options by default is a prudent approach. Adjustments can always be made later based on specific requirements or resource usage considerations.

Using MySQL command-line tools (mysql, mysqldump, etc.)

MySQL command-line tools, such as mysql and mysqldump, are invaluable for performing tasks like importing or exporting data.

A convenient way to utilise these tools is through a Docker container, which simplifies the setup process.

If Docker is not part of your workflow, no need to worry! Explore Code-Hero on Stackhero. This development platform operates directly in your browser, eliminating the need for local installations. Discover its advantages and start using it in just 2 minutes by visiting Code-Hero on Stackhero.

To begin, you can deploy a MySQL container using the following command:

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

Replace <MYSQL_VERSION> with the required MySQL version. For instance, if your application utilises MySQL version 8.0.36-0 on Stackhero, use version 8.0.36 (excluding the -0 suffix).

After launching the container, you may wish to verify the connection:

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

The current directory is mounted to the container's /mnt directory (via $(pwd):/mnt). This setup allows you to access and save files between your computer and the container. For example, to back up a database to your computer, you can use:

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

Dumping a MySQL database

To dump a database, the mysqldump CLI can be used on your computer (refer to the previous section for details).

Exporting a MySQL database to your computer

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

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 instance details. After pressing Enter, you will be prompted for the root password. The mysqldump utility will then create a file named <DATABASE>.sql containing your database's tables.

Importing a MySQL database to your server

To import a database from your computer into a Stackhero instance, you can use the following command:

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

Ensure to replace yourDump.sql with the filename of the SQL file to be imported into the Stackhero instance.

Other articles for MySQL