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 the maximum number of MySQL connections
At Stackhero, there is no hard-coded limit on the number of simultaneous connections your MySQL server can accept. You have the flexibility to adjust this parameter directly from your Stackhero dashboard to suit your application's requirements.
While it may be tempting to set this value very high, it is recommended to choose a limit that matches your actual usage. Each open connection consumes server memory (RAM), so setting the value too high can put unnecessary pressure on your instance and potentially make your database unstable.
Optimizing MySQL for InnoDB or MyISAM engines
If your database uses the InnoDB storage engine, you can enable the "InnoDB Optimizations" option in the Stackhero dashboard to improve performance.
Similarly, if your database runs on the MyISAM engine, enabling the "MyISAM Optimizations" option can also enhance performance.
If you are unsure which storage engine your database uses or which optimization to choose, it is generally advisable to enable these options by default. You can always adjust them later based on your workload or resource usage.
Using MySQL command-line tools (mysql, mysqldump, etc.)
MySQL command-line tools such as mysql and mysqldump are very practical for importing or exporting data.
One simple way to access these tools is to use them inside a Docker container. This approach keeps your local environment clean and avoids any manual installation.
If Docker is not part of your workflow, that's absolutely fine. You can check out Code-Hero on Stackhero. This browser-based development platform lets you work directly online, with no need to install anything locally. You can learn more and try it out in just a few minutes by visiting Code-Hero on Stackhero.
To get started with Docker, launch a MySQL container with the following command:
docker run -it -v $(pwd):/mnt mysql:<MYSQL_VERSION> /bin/bash
Replace
<MYSQL_VERSION>with the version you need. For example, if you are using MySQL8.0.36-0on Stackhero, use8.0.36(without the-0at the end).
Once the container is running, you can test the connection with this command:
mysql -u root -p -h <XXXXXX>.stackhero-network.com -P <PORT>
Your current directory is mounted to /mnt inside the container (thanks to the $(pwd):/mnt argument). This setup makes it easy to transfer files between your computer and the container. For example, to back up a database to your machine, use:
mysqldump -u root -p -h <XXXXXX>.stackhero-network.com -P <PORT> <DATABASE> > /mnt/<DATABASE>.sql
Backing up a MySQL database
To create a backup (or "dump") of your database, you can use the mysqldump tool directly from your computer. (Refer to the previous section for 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, run:
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 actual instance details. After running 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, use the following command:
mysql -u root -p -h <XXXXXX>.stackhero-network.com -P <PORT> <DATABASE> < yourDump.sql
Don't forget to replace yourDump.sql with the actual filename of the SQL file you want to import.