MySQL: Advanced usage

How to configure, optimize, 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 not a hard-coded limit on the number of simultaneous connections your MySQL server can handle. You have the flexibility to adjust this parameter directly from your Stackhero dashboard to match your application's needs.

While it might be tempting to set this value as high as possible, it is a good idea to choose a practical limit. Each open connection uses up server memory (RAM), so setting it too high could put unnecessary pressure on your instance and potentially make your database unstable.

Optimizing MySQL for InnoDB or MyISAM engines

If your database relies on the InnoDB storage engine, you can enable the "InnoDB Optimizations" option within the Stackhero dashboard to help boost performance.

Similarly, if your database uses the MyISAM storage engine, activating the "MyISAM Optimizations" option can provide performance improvements.

If you are unsure which storage engine your database uses or which optimization option to select, it is generally safe to enable these options by default. You can always fine-tune them later based on your workload or resource usage.

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

MySQL command-line tools like mysql and mysqldump are very useful for tasks such as importing and exporting data.

One easy way to access these tools is by running them inside a Docker container. This approach keeps your local setup clean and avoids the need for manual installations.

If Docker is not part of your workflow, this is absolutely fine. You might want to check out Code-Hero on Stackhero. This browser-based development platform lets you work directly in your browser, so you do not need to install anything locally. You can learn more and try it out in just a couple of minutes by visiting Code-Hero on Stackhero.

To get started with Docker, you can spin up a MySQL container by running:

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

Replace <MYSQL_VERSION> with the version you need. For example, if you are running MySQL 8.0.36-0 on Stackhero, use 8.0.36 (leave off the -0 at the end).

Once your container is running, you can test your connection with this command:

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

Here, your current directory is mounted to /mnt inside the container (thanks to the $(pwd):/mnt argument). This setup makes it easy to move files between your computer and the container. For example, if you would like to back up a database to your machine, you can use:

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

Dumping a MySQL database

To create a database backup (or "dump"), you can use the mysqldump tool directly from your computer. (See the previous section for details on running it inside Docker if you prefer.)

Exporting a MySQL database to your computer

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

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

Make sure to replace <XXXXXX>.stackhero-network.com, <PORT>, and <DATABASE> with your actual instance details. After you run the command, you will be prompted for the root password. The mysqldump tool will then save your database tables in a file named <DATABASE>.sql.

Importing a MySQL database to your server

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

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

Just remember to replace yourDump.sql with the actual filename for the SQL file you want to import.