# How-To Guides

# Creating a Database

MySQL is a leading open-source relational database management system (RDBMS) known for its reliability, scalability, and ease of use. Setting up a database properly in MySQL is crucial for ensuring long-term maintainability, performance, and security of applications. This guide walks through different ways to create a MySQL database: using the MySQL CLI, using Docker containers, and using the <span class="s2">mysqladmin</span> tool. It also emphasises best practices that should be followed at each step.

## **Creating a Database Using MySQL CLI**

The most common and straightforward way to create a database is by using the MySQL command-line interface (<span class="s2">mysql</span> client). First, a connection must be established to the MySQL server using an account with appropriate privileges, typically the <span class="s2">root</span> account or a designated administrative user.

#### **Connect to MySQL:**

Connect to MySQL:

```
mysql -u root -p
```

To connect to a <span class="s1">remote MySQL database</span> using the MySQL CLI, you need to specify the host’s IP address or domain name using the `<span class="s2">-h</span>` flag along with the username and password:

```mysql
mysql -h <remote_host> -P <port> -u <username> -p
```

You will be prompted to enter the password for the <span class="s1">root</span> user. Upon successful login, the MySQL shell opens where SQL queries can be executed.

#### **Create a New Database**

To create a database with default settings:

```
CREATE DATABASE mydatabase;
```

However, it is a best practice to explicitly define the character set and collation. This prevents potential problems with encoding and sorting, especially when dealing with multilingual data or special characters. The recommended character set for modern applications is <span class="s1">utf8mb4</span>, which fully supports Unicode.

Create a database with specific character set and collation:

```
CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
```

You can verify that the database was created by listing all databases:

```
SHOW DATABASES;
```

Explicitly setting the character set ensures data consistency and minimizes future migration issues. Additionally, defining these settings at creation time avoids relying on server defaults, which can vary across different environments.

## **Creating a Database in Docker**

Docker is a tool that helps run applications in isolated environments called containers. A MySQL container provides a self-contained database instance that can be quickly deployed and managed. If you are running MySQL inside a Docker container, follow these steps:

#### **Access Elestio Terminal**  


Head over to your deployed MySQL service dashboard and head over to **Tools &gt; Terminal**. Use the credentials provided there to log in to your terminal.

[![image.png](https://docs.elest.io/uploads/images/gallery/2025-04/scaled-1680-/LoCimage.png)](https://docs.elest.io/uploads/images/gallery/2025-04/LoCimage.png)

Once you are in your terminal, run the following command to head over to the correct directory to perform the next steps

```
cd /opt/app/
```

#### **Access the MySQL Container Shell**

<div id="bkmrk-instead-of-pulling-a"><div>Instead of pulling an image or running the container manually, use Docker Compose to interact with your running container. As you are using Elestio, it will already be a Docker compose:</div></div>```
docker-compose exec mysql bash
```

Inside the container, access the MySQL shell:

```
mysql -u root -p
```

#### **Create Database**

Now, to create a database, use the following command. This command tells MySQL to create a new logical database called `mydatabase`. By default, it inherits settings like encoding and collation from the template database (<span class="s1">template1</span>), unless specified otherwise.

```
CREATE DATABASE mydatabase;
```

## **Creating a Database Using** `<strong>mysqladmin</strong>`

The `<span class="s2">mysqladmin</span>` utility provides a non-interactive way to create databases. It is particularly useful for automation scripts and quick administrative tasks.

To create a database:

```
mysqladmin -h <host> -P <port> -u root -p create mydatabase
```

One limitation of `<span class="s1">mysqladmin</span>` is that it does not allow specifying character set and collation at creation time. Therefore, if these settings need to be controlled explicitly (which is generally recommended), it is better to use the <span class="s1">mysql</span> CLI instead.

Before using <span class="s1">mysqladmin</span>, ensure the MySQL server is running. On traditional installations, check the status:

```
sudo systemctl status mysql
```

If the service is not active, it can be started with:

```
sudo systemctl start mysql
```

## **Best Practices for Creating Databases**


**Use Meaningful Names**

Choosing clear and descriptive names for databases helps in organisation and long-term maintenance. Avoid generic names like <span class="s3">testdb</span> or <span class="s3">database1</span>, as they do not convey the database’s role or content. Instead, choose names that reflect the kind of data stored or the application it supports, such as <span class="s3">customer\_data</span>, <span class="s3">sales\_records</span>, or <span class="s3">analytics\_db</span>. Meaningful names improve clarity for developers, DBAs, and future maintainers who need to quickly understand the purpose of each database without relying heavily on documentation.

**Follow Naming Conventions**

A standardized naming convention across all environments and teams simplifies database management and reduces confusion. MySQL database names are case-sensitive on Unix-based systems, so consistent use of lowercase letters is recommended. Use underscores to separate words (e.g., <span class="s3">order\_details</span>) rather than camelCase or spaces. This avoids the need for extra quoting in SQL queries and prevents platform-specific bugs. Additionally, avoid using reserved MySQL keywords or special characters in database names, as these can lead to parsing errors and unexpected behaviour.

**Restrict User Permissions**

Granting only the minimum required permissions significantly strengthens database security and reduces the likelihood of accidental damage or data leaks. Following the Principle of Least Privilege, reporting users should only be given <span class="s3">SELECT</span> access, while application users may require `<span class="s3">SELECT</span>`, `<span class="s3">INSERT</span>`, `<span class="s3">UPDATE</span>`, and `<span class="s3">DELETE</span>` rights. Only a few trusted administrative users should have powerful privileges like `<span class="s3">ALTER</span>`, `<span class="s3">DROP</span>`, or `<span class="s3">GRANT</span>`. Avoid assigning superuser access unless absolutely necessary. Creating user roles or groups with defined scopes can help standardise permission levels across teams and services.

**Enable Backups**

Regular backups are critical to ensure business continuity and safeguard against data loss from unexpected events such as accidental deletions, server crashes, or software bugs. MySQL provides tools like <span class="s3">mysqldump</span> for logical backups of individual databases, and <span class="s3">mysqlpump</span> or <span class="s3">xtrabackup</span> for more advanced use cases. It’s good practice to schedule automated backups using cron jobs or database orchestration tools. Backup files should be stored securely and regularly tested for restoration to verify that the process works as expected during emergencies.

**Monitor Performance**

Ongoing performance monitoring is essential to maintain the responsiveness and stability of MySQL databases. Monitoring tools like `<span class="s3">performance_schema</span>`, `<span class="s3">information_schema</span>`, or external platforms like Percona Monitoring and Management (PMM) help identify slow queries, locked transactions, and system resource bottlenecks. Use `<span class="s3">EXPLAIN</span>` and `<span class="s3">ANALYZE</span>` to understand query plans and optimize indexes. Keeping an eye on connection stats, query latency, and buffer pool usage allows for timely tuning and ensures efficient database operations at scale.

## **Common Issues and Their Solutions**

Here’s a table summarizing common problems faced during database creation and how to resolve them:

<table border="1" id="bkmrk-issue-cause-solution" style="border-collapse: collapse; border-color: rgb(0, 0, 0);"><thead><tr><th style="border-color: rgb(0, 0, 0);">**Issue**

</th><th style="border-color: rgb(0, 0, 0);">**Cause**

</th><th style="border-color: rgb(0, 0, 0);">**Solution**

</th></tr></thead><tbody><tr><td style="border-color: rgb(0, 0, 0);">ERROR 1044 (42000): Access denied for user

</td><td style="border-color: rgb(0, 0, 0);">The connected user does not have the <span class="s1">CREATE</span> privilege.

</td><td style="border-color: rgb(0, 0, 0);">Connect as a user with administrative privileges or grant necessary permissions.

</td></tr><tr><td style="border-color: rgb(0, 0, 0);">ERROR 1007 (HY000): Can't create database; database exists

</td><td style="border-color: rgb(0, 0, 0);">Attempting to create a database that already exists.

</td><td style="border-color: rgb(0, 0, 0);">Choose a different name or drop the existing database if appropriate using <span class="s1">DROP DATABASE</span>.

</td></tr><tr><td style="border-color: rgb(0, 0, 0);">Can't connect to MySQL server on 'localhost'

</td><td style="border-color: rgb(0, 0, 0);">MySQL server is not running, or incorrect connection parameters are used.

</td><td style="border-color: rgb(0, 0, 0);">Start the MySQL service and verify network and authentication parameters.

</td></tr><tr><td style="border-color: rgb(0, 0, 0);">Collation or character set issues later in application

</td><td style="border-color: rgb(0, 0, 0);">Database created without explicitly specifying character set or collation.

</td><td style="border-color: rgb(0, 0, 0);">Always specify <span class="s1">utf8mb4</span> and a collation like <span class="s1">utf8mb4\_unicode\_ci</span> during database creation.

</td></tr><tr><td style="border-color: rgb(0, 0, 0);">Docker MySQL container refuses connections

</td><td style="border-color: rgb(0, 0, 0);">MySQL container not ready or port mappings not correctly set.

</td><td style="border-color: rgb(0, 0, 0);">Check container logs with <span class="s1">docker-compose logs mysql</span> and verify port exposure settings in <span class="s1">docker-compose.yml</span>.

</td></tr></tbody></table>

# Upgrading to a Major Version

Upgrading a database service on Elestio can be done without creating a new instance or performing a full manual migration. Elestio provides a built-in option to change the database version directly from the dashboard. This is useful for cases where the upgrade does not involve breaking changes or when minimal manual involvement is preferred. The version upgrade process is handled by Elestio internally, including restarting the database service if required. This method reduces the number of steps involved and provides a way to keep services up to date with minimal configuration changes.

### **Log In and Locate Your Service**

To begin the upgrade process, log in to your Elestio dashboard and navigate to the specific database service you want to upgrade. It is important to verify that the correct instance is selected, especially in environments where multiple databases are used for different purposes such as staging, testing, or production. The dashboard interface provides detailed information for each service, including version details, usage metrics, and current configuration. Ensure that you have access rights to perform upgrades on the selected service. Identifying the right instance helps avoid accidental changes to unrelated environments.

### **Back Up Your Data**

Before starting the upgrade, create a backup of your database. A backup stores the current state of your data, schema, indexes, and configuration, which can be restored if something goes wrong during the upgrade. In Elestio, this can be done through the **Backups** tab by selecting **Back up now** under Manual local backups and **Download** the backup file. Scheduled backups may also be used, but it is recommended to create a manual one just before the upgrade. Keeping a recent backup allows quick recovery in case of errors or rollback needs. This is especially important in production environments where data consistency is critical.

[![Screenshot 2025-04-29 at 7.46.26 PM.jpg](https://docs.elest.io/uploads/images/gallery/2025-04/scaled-1680-/screenshot-2025-04-29-at-7-46-26-pm.jpg)](https://docs.elest.io/uploads/images/gallery/2025-04/screenshot-2025-04-29-at-7-46-26-pm.jpg)

### **Select the New Version**

Once your backup is secure, proceed to the **Overview** and then **Software &gt; Update config** tab within your database service page.

[![Screenshot 2025-04-29 at 8.07.22 PM.jpg](https://docs.elest.io/uploads/images/gallery/2025-04/scaled-1680-/screenshot-2025-04-29-at-8-07-22-pm.jpg)](https://docs.elest.io/uploads/images/gallery/2025-04/screenshot-2025-04-29-at-8-07-22-pm.jpg)

Here, you'll find an option labeled **ENV**. In the **ENV** menu, change the desired database version to `SOFTWARE_VERSION`. After confirming the version, Elestio will begin the upgrade process automatically. During this time, the platform takes care of the version change and restarts the database if needed. No manual commands are required, and the system handles most of the operational aspects in the background.

[![Screenshot 2025-04-29 at 8.07.45 PM.jpg](https://docs.elest.io/uploads/images/gallery/2025-04/scaled-1680-/screenshot-2025-04-29-at-8-07-45-pm.jpg)](https://docs.elest.io/uploads/images/gallery/2025-04/screenshot-2025-04-29-at-8-07-45-pm.jpg)

### **Monitor the Upgrade Process**

The upgrade process may include a short downtime while the database restarts. Once it is completed, it is important to verify that the upgrade was successful and the service is operating as expected. Start by checking the logs available in the Elestio dashboard for any warnings or errors during the process. Then, review performance metrics to ensure the database is running normally and responding to queries. Finally, test the connection from your client applications to confirm that they can interact with the upgraded database without issues.

# Installing and Updating an Extension

MySQL supports a variety of <span class="s1">**plugins**</span> that extend the functionality of the database engine. These plugins add features like authentication methods, full-text search improvements, audit logging, and more. Popular examples include `<span class="s3">auth_socket</span>`, `<span class="s3">validate_password</span>`, and `<span class="s3">audit_log</span>`. In Elestio-hosted MySQL instances, many common plugins are already available and can be enabled or disabled as needed. This guide explains how to install, manage, and troubleshoot MySQL plugins and verify compatibility with different MySQL versions.

## **Installing and Enabling Plugins**

In MySQL, plugins are usually installed globally at the server level, not per-database. If the plugin binaries are available, they can be loaded dynamically at runtime without restarting the server.

Start by connecting to your MySQL database using a client like <span class="s3">mysql</span>:

```mysql
mysql -u root -p -h your-elestio-hostname
```

To enable a plugin, use the <span class="s1">INSTALL PLUGIN</span> command. For example, to enable the `<span class="s1">validate_password</span>` plugin:

```mysql
INSTALL PLUGIN validate_password SONAME 'validate_password.so';
```

You can verify that the plugin is installed by checking the plugin list:

```mysql
SHOW PLUGINS;
```

To enable a plugin automatically at server startup, add its configuration in the <span class="s1">my.cnf</span> file. However, for managed Elestio instances, this may require support team intervention unless custom configuration access is provided.

## **Checking Plugin Availability &amp; Compatibility**

Plugins must be compiled for the specific MySQL version and platform. Before upgrading MySQL or installing a new plugin, verify that a compatible version is available for your target setup. You can find plugin binaries under the <span class="s1">plugin\_dir</span>, which you can locate with:

```mysql
SHOW VARIABLES LIKE 'plugin_dir';
```

To check if a specific plugin is installed and active:

```mysql
SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = 'validate_password';
```

If a plugin is incompatible or missing from the <span class="s1">plugin\_dir</span>, the server will return an error when you attempt to install it. In this case, contact Elestio support to request installation or confirm version compatibility.

## **Updating or Uninstalling Plugins**

After a MySQL version upgrade, some plugins may need to be reinstalled or updated. If a plugin is malfunctioning after an upgrade, it is good practice to uninstall and reinstall it:

```mysql
UNINSTALL PLUGIN validate_password;
INSTALL PLUGIN validate_password SONAME 'validate_password.so';
```

Not all plugins support automatic upgrades. You should consult plugin-specific documentation or Elestio’s compatibility matrix before proceeding.

## **Troubleshooting Common Plugin Issues**

<table border="1" id="bkmrk-issue-cause-resoluti" style="border-collapse: collapse; border-width: 1px; border-color: rgb(0, 0, 0);"><thead><tr><th style="border-color: rgb(0, 0, 0);">**Issue**

</th><th style="border-color: rgb(0, 0, 0);">**Cause**

</th><th style="border-color: rgb(0, 0, 0);">**Resolution**

</th></tr></thead><tbody><tr><td style="border-color: rgb(0, 0, 0);">Can't open shared library

</td><td style="border-color: rgb(0, 0, 0);">Plugin binary not found in <span class="s1">plugin\_dir</span>

</td><td style="border-color: rgb(0, 0, 0);">Check if the `<span class="s1">.so</span>` file exists and has correct permissions; contact Elestio if needed

</td></tr><tr><td style="border-color: rgb(0, 0, 0);">Plugin already installed

</td><td style="border-color: rgb(0, 0, 0);">Attempting to install a plugin that is already active

</td><td style="border-color: rgb(0, 0, 0);">Use `<span class="s1">SHOW PLUGINS</span>` to verify and avoid duplicate installation

</td></tr><tr><td style="border-color: rgb(0, 0, 0);">Permission denied

</td><td style="border-color: rgb(0, 0, 0);">Current user lacks `SUPER` privilege

</td><td style="border-color: rgb(0, 0, 0);">Log in as a user with `SUPER` or administrative rights

</td></tr><tr><td style="border-color: rgb(0, 0, 0);">Plugin is not loaded at startup

</td><td style="border-color: rgb(0, 0, 0);">Plugin not defined in configuration file

</td><td style="border-color: rgb(0, 0, 0);">Contact Elestio to add it to the MySQL startup config (`<span class="s1">my.cnf</span>`)

</td></tr></tbody></table>

## **Security Considerations**

Plugins can have significant control over database behavior. Only enable trusted plugins from verified sources. Avoid enabling plugins you do not need, as they can introduce security or performance risks. Always test plugin behavior in a staging environment before deploying to production.

# Creating Manual Backups

Regular backups are a key part of managing a MySQL deployment. While Elestio provides automated backups by default, you may want to perform manual backups for specific reasons, such as preparing for a major change, keeping a local copy, or testing backup automation. This guide walks through how to create MySQL backups on Elestio using multiple approaches. It covers manual backups through the Elestio dashboard, using MySQL CLI tools, and Docker Compose-based setups. It also includes advice for backup storage, retention policies, and automation using scheduled jobs.

## **Manual Service Backups on Elestio**

If you’re using Elestio’s managed MySQL service, the easiest way to create a manual backup is through the dashboard. This built-in method creates a full snapshot of your current database state and stores it within Elestio’s infrastructure. These backups are tied to your service and can be restored through the same interface. This option is recommended when you need a quick, consistent backup without using any terminal commands.

### **To trigger a manual backup from the Elestio dashboard:**

- Log in to the Elestio dashboard and navigate to your MySQL service/cluster.
- Click the <span class="s1">**Backups**</span> tab in the service menu.
- Select <span class="s1">**Back up now**</span> to generate a snapshot.

[![Screenshot 2025-04-30 at 11.34.34 AM.jpg](https://docs.elest.io/uploads/images/gallery/2025-04/scaled-1680-/screenshot-2025-04-30-at-11-34-34-am.jpg)](https://docs.elest.io/uploads/images/gallery/2025-04/screenshot-2025-04-30-at-11-34-34-am.jpg)

## **Manual Backups Using MySQL CLI**

MySQL provides a set of command-line tools that are useful when you want to create backups from your terminal. These include `<span class="s2">mysqldump</span>` for exporting databases and <span class="s2">mysql</span> for connectivity and basic queries. This approach is useful when you need to store backups locally or use them with custom automation workflows. The CLI method gives you full control over the backup format and destination.

### **Collect Database Connection Info**

To use the CLI tools, you’ll need the database host, port, name, username, and password. These details can be found in the <span class="s3">**Overview**</span> section of your MySQL service in the Elestio dashboard.

[![Screenshot 2025-04-30 at 11.47.37 AM.jpg](https://docs.elest.io/uploads/images/gallery/2025-04/scaled-1680-/screenshot-2025-04-30-at-11-47-37-am.jpg)](https://docs.elest.io/uploads/images/gallery/2025-04/screenshot-2025-04-30-at-11-47-37-am.jpg)

### **Back Up with mysqldump**

Use <span class="s2">mysqldump</span> to export the database to a <span class="s2">.sql</span> file. This file can later be used to recreate the database or specific tables.

```
mysqldump -h <host> -P <port> -u <username> -p<password> <database_name> > <output_file>.sql
```

- Replace the placeholders with actual values from your Elestio dashboard.
- The <span class="s1">-p&lt;password&gt;</span> flag must not have a space between <span class="s1">-p</span> and the password.

Example:

```
mysqldump -h mysql-example.elestio.app -P 24306 -u elestio -pelestioPass mydb > mydb_backup.sql
```

You can add the `<span class="s1">--single-transaction</span>` flag for InnoDB tables to ensure consistency during the dump.

## **Manual Backups Using Docker Compose**

If your MySQL database is deployed through a Docker Compose setup on Elestio, you can run the <span class="s1">mysqldump</span> command from within the running container. This is useful when the tools are installed inside the container environment and you want to keep everything self-contained. The backup can be created inside the container and then copied to your host system for long-term storage or transfer.

### **Access Elestio Terminal**

Head over to your deployed MySQL service dashboard and go to <span class="s3">**Tools &gt; Terminal**</span>. Use the credentials provided there to log in to your terminal.

[![image.png](https://docs.elest.io/uploads/images/gallery/2025-04/scaled-1680-/FNYimage.png)](https://docs.elest.io/uploads/images/gallery/2025-04/FNYimage.png)

Once you are in your terminal, navigate to the correct directory:

```
cd /opt/app/
```

### **Run mysqldump Inside the Container**

Use this command to run the backup from within the MySQL container. Ensure environment variables like `<span class="s2">MYSQL_USER</span>`, `<span class="s2">MYSQL_PASSWORD</span>`, and `<span class="s2">MYSQL_DATABASE</span>` are defined, or replace them with actual values.

```
docker-compose exec mysql \
  bash -c "mysqldump -u \$MYSQL_USER -p\$MYSQL_PASSWORD \$MYSQL_DATABASE > /tmp/manual_backup.sql"
```

This command saves the backup to <span class="s1">/tmp/manual\_backup.sql</span> inside the container.

### **Copy Backup to Host**

Once the backup is created inside the container, use the following command to copy it to your host system:

```
docker cp $(docker-compose ps -q mysql):/tmp/manual_backup.sql ./manual_backup.sql
```

This creates a local copy of the backup file, which you can then upload to external storage or keep for versioned snapshots.

## **Backup Storage &amp; Retention Best Practices**

Once backups are created, they should be stored securely and managed with a clear retention policy. Proper naming, encryption, and rotation reduce the risk of data loss and help during recovery. Use timestamped filenames to identify when the backup was created. External storage services such as AWS S3, Backblaze B2, or an encrypted server volume are recommended for long-term storage.

##### **Guidelines to follow:**

- <span class="s1">**Name backups clearly:**</span> `mydb_backup_2025_04_29.sql`
- **Store in secure, off-site storage**<span class="s1"> if possible.</span>
- **Retain 7 daily backups, 4 weekly backups, and 3–6 monthly backups.**
- <span class="s1">**Remove old backups automatically**</span> to save space using automation or scripts.

By combining storage hygiene with regular scheduling, you can maintain a reliable backup history and reduce manual effort.

## **Automating Manual Backups (cron)**

Manual backup commands can be scheduled using tools like <span class="s2">cron</span> on Linux-based systems. This allows you to regularly back up your database without needing to run commands manually. Automating the process also reduces the risk of forgetting backups and ensures more consistent retention.

#### **Example: Daily Backup at 2 AM**

Open your crontab file for editing:

```
crontab -e
```

Then add a job like the following:

```
0 2 * * * mysqldump -h db.vm.elestio.app -P 24306 -u elestio -pelestioPass mydatabase > /backups/backup_$(date +\%F).sql
```

- This will create a timestamped <span class="s1">.sql</span> file every day at 2 AM.
- Make sure the <span class="s1">/backups/</span> directory exists and is writable by the user running the cron job.

You can also compress the backup or upload it to cloud storage in the same script using tools like <span class="s2">gzip</span>, <span class="s2">rclone</span>, or <span class="s2">aws-cli</span>.

# Restoring a Backup

Restoring backups is essential for recovery, environment duplication, or rollback scenarios. Elestio supports restoring backups both through its built-in dashboard and via command-line tools like <span class="s3">mysql</span> and <span class="s3">mysqldump</span>. You can also restore from inside Docker Compose environments. This guide provides detailed steps for full and partial restores using each method and explains how to address common errors that occur during restoration.

## **Restoring from a Backup via Terminal**

This method is used when you’ve created a `<span class="s3">.sql</span>` dump file using <span class="s3">mysqldump</span>. You can restore it using the <span class="s3">mysql</span> command-line client. This approach is useful for restoring backups to new environments, during version upgrades, or testing data locally.

#### **Create the target database if it does not exist**

If the database you’re restoring into doesn’t already exist, you must create it first:

```
mysql -u <username> -p -h <host> -P <port> -e "CREATE DATABASE <database_name>;"
```

You’ll be prompted to enter the password after running the command.

#### **Run MySQL to import the backup**

This command restores the full contents of the <span class="s2">.sql</span> file into the specified database:

```
mysql -u <username> -p -h <host> -P <port> <database_name> < <backup_file>.sql
```

You’ll again be prompted for the password. This command restores everything from the dump file, including schema and data.

## **Restoring via Docker Compose**

If your MySQL service is deployed using Docker Compose, you can restore the database inside the container environment. This is useful when MySQL runs in an isolated Docker setup, and you want to handle all backup and restore processes inside that environment.

#### **Copy the backup into the container**

Use <span class="s2">docker cp</span> to move the <span class="s2">.sql</span> file from your host machine to the MySQL container:

```
docker cp ./manual_backup.sql $(docker-compose ps -q mysql):/tmp/manual_backup.sql
```

#### **Run the restore inside the container**

Use the <span class="s2">mysql</span> CLI tool from within the container to restore the file:

```
docker-compose exec mysql \
  bash -c "mysql -u \$MYSQL_USER -p\"\$MYSQL_PASSWORD\" \$MYSQL_DATABASE < /tmp/manual_backup.sql"
```

Make sure your environment variables in the Docker Compose file (<span class="s1">MYSQL\_USER</span>, <span class="s1">MYSQL\_PASSWORD</span>, <span class="s1">MYSQL\_DATABASE</span>) match the values used here.

## **Partial Restores**

MySQL supports partial restores when the dump file is created with selective options in <span class="s1">mysqldump</span>. For example, you can restore just a specific table or only schema definitions.

#### **Restore a specific table**

If you created a dump for a specific table using <span class="s1">mysqldump -t</span>, you can restore it independently:

```
mysql -u <username> -p -h <host> -P <port> <database_name> < <table_dump_file>.sql
```

#### **Restore schema only (no data)**

To restore only the schema (no table contents), ensure that your dump file was created using:

```
mysqldump -u <username> -p -h <host> -P <port> --no-data <database_name> > schema_only.sql
```

Then restore it like this:

```
mysql -u <username> -p -h <host> -P <port> <database_name> < schema_only.sql
```

Partial restores work best when the original backup was generated with the appropriate level of granularity.

## **Common Errors &amp; How to Fix Them**

Errors during restore are often caused by permission issues, incorrect formats, or existing conflicting objects. Understanding the error messages and their causes will help you recover faster and avoid data loss.

**1. Access denied for user**

```
ERROR 1045 (28000): Access denied for user 'user'@'host'
```

Ensure you are using the correct username/password and that the user has privileges to access the target database.

**2. Table already exists**

```
ERROR 1050 (42S01): Table 'my_table' already exists
```

Either drop the target database before restoring:

```
mysql -u <username> -p -h <host> -P <port> -e "DROP DATABASE <database_name>;"
mysql -u <username> -p -h <host> -P <port> -e "CREATE DATABASE <database_name>;"
```

Or manually drop the conflicting tables before restore.

**3. ERROR 1064 (Syntax Error)**

```
ERROR 1064 (42000): You have an error in your SQL syntax...
```

Check if you’re trying to import a binary or incorrectly formatted file. Ensure you’re using <span class="s1">.sql</span> text dump files with the <span class="s1">mysql</span> command and not raw <span class="s1">.ibd</span> or <span class="s1">.frm</span> files.

**4. ERROR 1049 (Unknown Database)**

```
ERROR 1049 (42000): Unknown database 'mydatabase'
```

The specified database doesn’t exist. Create it manually before restoring.

```
mysql -u <username> -p -h <host> -P <port> -e "CREATE DATABASE mydatabase;"
```

# Identifying Slow Queries

Slow queries can degrade the performance of your MySQL-based application, leading to lag, timeouts, or higher resource consumption. On Elestio, whether you’re accessing MySQL via terminal, inside a Docker Compose container, or using MySQL CLI tools, there are structured ways to inspect and optimize query performance. This guide covers how to analyze slow queries, interpret execution plans, and apply performance improvements using techniques like <span class="s3">EXPLAIN</span>, slow query logs, and schema analysis.

## **Analyzing Slow Queries from the Terminal**

When connected to a MySQL server from a terminal, you can use native SQL statements and built-in features to analyze the performance of specific queries. This is ideal for diagnosing issues in staging or production without needing container access.

#### **Connect to your MySQL instance via terminal**

To begin, log in to your MySQL server using the MySQL client:

```
mysql -u <username> -h <host> -p
```

You’ll be prompted for the password. Once inside, you can start analyzing queries.

#### **Use EXPLAIN to view the execution plan**

The <span class="s2">EXPLAIN</span> keyword shows how MySQL plans to execute a query. It breaks down how tables are accessed and joined, whether indexes are used, and how many rows are expected to be scanned.

```mysql
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
```

Review the <span class="s1">type</span>, <span class="s1">key</span>, <span class="s1">rows</span>, and <span class="s1">Extra</span> columns in the output. Look out for full table scans (<span class="s1">type = ALL</span>), which often signal that an index may be missing.

#### **Check current running queries**

To view which queries are actively running and their duration, use:

```mysql
SHOW FULL PROCESSLIST;
```

This can help identify long-running or stuck queries in real time.

## **Analyzing Inside Docker Compose**

If your MySQL service is running inside a Docker Compose setup (as Elestio uses), it may not be directly exposed on your host. In this case, analysis must be done from within the container.

#### **Access the MySQL container**

Open a shell inside your MySQL container using Docker Compose:

```
docker-compose exec mysql bash
```

This gives you a command-line shell inside the container.

#### **Connect to MySQL from inside the container**

Once inside the container, use the environment-defined credentials to access the database:

```
mysql -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE
```

This gives you the same SQL interface as from the host terminal, enabling you to use <span class="s1">EXPLAIN</span>, <span class="s1">SHOW PROCESSLIST</span>, and performance schema tools.

#### **Enable and view the slow query log**

MySQL can log slow queries to a file. This must be enabled in your container’s `<span class="s1">my.cnf</span>` configuration file:

```
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
```

After applying these settings, restart the container. Slow queries taking longer than <span class="s1">long\_query\_time</span> (in seconds) will be logged.

You can then inspect the log file:

```
cat /var/log/mysql/slow.log
```

## **Using Performance Schema** 

MySQL’s performance schema and built-in commands help track query statistics over time. This is useful when diagnosing repeat offenders or inefficient patterns.

#### **Enable the performance schema (if not already)**

Ensure <span class="s2">performance\_schema</span> is enabled in your MySQL configuration:

```
[mysqld]
performance_schema=ON
```

Restart the container after updating the config.

#### **Identify top queries using statement summaries**

This SQL query shows which SQL statements have the longest average execution times:

```mysql
SELECT digest_text, count_star, avg_timer_wait/1000000000000 AS avg_time_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESC
LIMIT 10;
```

This helps you find the most resource-intensive queries over time.

## **Understanding the MySQL Execution Plan**

Reading the output of <span class="s2">EXPLAIN</span> is essential to understand how MySQL processes your query and whether it is using indexes efficiently.

Key output fields to interpret:

- **<span class="s1">type</span>**: The join type. Prefer <span class="s1">ref</span>, <span class="s1">range</span>, or <span class="s1">const</span> over <span class="s1">ALL</span> (which indicates a full table scan).
- **<span class="s1">key</span>**: The index used for the query. A <span class="s1">NULL</span> value may indicate a missing index.
- **<span class="s1">rows</span>**: Estimated number of rows MySQL will scan. Lower is better.
- **<span class="s1">Extra</span>**: Look for warnings like <span class="s1">Using temporary</span> or <span class="s1">Using filesort</span>, which may suggest suboptimal queries.

Use `<span class="s2">EXPLAIN ANALYZE</span>` (available in MySQL 8.0+) to see actual vs. estimated performance:

```mysql
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
```

## **Optimizing Queries for Better Performance**

Once you’ve identified inefficient queries, optimization involves rewriting queries, adding indexes, or adjusting the schema.

Common techniques:

- <span class="s1">**Add indexes**</span> to columns frequently used in <span class="s2">WHERE</span>, <span class="s2">JOIN</span>, and <span class="s2">ORDER BY</span>.
- **Avoid SELECT\*** : Only fetch columns you need to reduce I/O.
- <span class="s1">**Use LIMIT**</span> when fetching preview data or paginated results.
- <span class="s1">**Re-write joins or subqueries**</span> to reduce temporary tables and filesort operations.
- **Update statistics**<span class="s1"> with </span><span class="s2">ANALYZE TABLE</span><span class="s1">:</span>

```mysql
ANALYZE TABLE orders;
```

# Detect and terminate long-running queries

Long-running queries in MySQL can degrade database performance by consuming system resources like CPU, memory, and disk I/O for extended periods. In production environments such as Elestio, it’s essential to monitor and manage these queries effectively to maintain responsiveness and avoid service disruptions. This guide explains how to detect, analyze, and safely terminate long-running queries in MySQL using terminal tools, Docker Compose setups, and built-in logging features. It also includes preventive strategies for avoiding such queries in the future.

## **Monitoring Long-Running Queries**

When connected to your MySQL instance via the terminal using the MySQL CLI, you can inspect active sessions and identify queries that have been running for an excessive duration. This is useful for spotting inefficient or blocked operations.

To check all current sessions and running queries, execute:

```mysql
SHOW FULL PROCESSLIST;
```

This will return all client connections along with their process ID (<span class="s1">Id</span>), command type (<span class="s1">Command</span>), execution time in seconds (<span class="s1">Time</span>), and the actual SQL query (<span class="s1">Info</span>). The <span class="s1">Time</span> column indicates how long each query has been executing, allowing you to prioritize the longest-running ones.

If you want to isolate queries that have been running for more than a certain duration, such as 60 seconds, you can filter using the `<span class="s1">information_schema.processlist</span> `view:

```mysql
SELECT * FROM information_schema.processlist
WHERE COMMAND != 'Sleep' AND TIME > 60;
```

This command excludes idle connections and focuses only on active queries that may need attention.

## **Terminating Long-Running Queries Safely**

Once you’ve identified a query that’s taking too long, MySQL allows you to stop it using its process ID (<span class="s2">Id</span>). This can be done either by cancelling just the query or by killing the entire connection.

To stop the query and leave the connection active, run:

```mysql
KILL QUERY <Id>;
```

This interrupts the execution of the current query but keeps the client connection open.

If the connection is completely stuck or no longer needed, you can terminate it entirely:

```mysql
KILL CONNECTION <Id>;
```

Use this approach with caution, especially in shared environments, as it may interrupt ongoing operations or cause errors for connected applications.

## **Managing Long-Running Queries**

If MySQL is running in a Docker Compose setup on Elestio, you’ll first need to access the container to inspect queries. You can do so by opening a shell inside the MySQL container:

```
docker-compose exec mysql bash
```

Once inside the container, connect to the MySQL service using the credentials defined in your environment:

```mysql
mysql -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE
```

After connecting, you can run the same <span class="s1">SHOW FULL PROCESSLIST</span> and <span class="s1">KILL</span> commands to identify and handle long-running queries directly from inside the container environment. The logic and process are identical; the only difference is that you’re executing these operations within the container shell.

## **Using Slow Query Logs**

MySQL supports slow query logging, which records statements that exceed a specified execution time. This is useful for long-term analysis and identifying recurring performance issues.

To enable this feature, update your MySQL configuration file (e.g., <span class="s1">my.cnf</span> or <span class="s1">mysqld.cnf</span>) with the following lines:

```mysql
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
```

This setup logs any query taking longer than one second. Once configured, restart the MySQL service to apply the changes.

You can then inspect the log with:

```mysql
cat /var/log/mysql/slow.log
```

To summarize patterns in slow queries, use the <span class="s1">mysqldumpslow</span> tool:

```mysql
mysqldumpslow /var/log/mysql/slow.log
```

This helps you identify repetitive or particularly expensive SQL statements based on execution time and frequency.

## **Analyzing Expensive Queries Over Time**

To gain visibility into queries that are consistently slow over time, enable MySQL’s <span class="s2">performance\_schema</span>. This built-in feature aggregates statistics about SQL statement execution, allowing you to pinpoint inefficiencies.

Make sure performance schema is enabled in your config:

```
[mysqld]
performance_schema = ON
```

Once it’s active, use this query to analyze the most time-consuming query patterns:

```mysql
SELECT digest_text, count_star, avg_timer_wait/1000000000000 AS avg_time_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESC
LIMIT 10;
```

This highlights SQL statements that are not just slow once, but frequently expensive, helping you focus on queries with the biggest overall impact.

## **Best Practices to Prevent Long-Running Queries**

It’s better to prevent long-running queries than to reactively terminate them. A few strategic adjustments in your query design and database configuration can significantly improve performance.

- <span class="s1">**Index critical columns**</span> used in <span class="s2">WHERE</span>, <span class="s2">JOIN</span>, and <span class="s2">ORDER BY</span> clauses to speed up lookups and sorting.
- <span class="s1">**Avoid SELECT \***</span> in queries — fetch only the necessary columns to reduce result size and memory usage.
- <span class="s1">**Use EXPLAIN**</span> to analyze how a query will be executed and whether indexes are being used

```mysql
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
```

- <span class="s1">**Limit result sets**</span> in user-facing tools or admin dashboards using <span class="s2">LIMIT</span> clauses to avoid returning large datasets unnecessarily.
- **Set execution time limits**<span class="s1"> at the session level</span>

```mysql
SET SESSION MAX_EXECUTION_TIME = 2000; -- in milliseconds
```

- <span class="s1">**Implement timeouts in applications**</span> and ORMs to prevent client-side hanging when the database becomes slow.
- <span class="s1">**Monitor actively**</span> using slow query logs, processlist views, and the performance schema. Consider integrating this into your monitoring stack to set up alerts for unusually long or frequent queries.

# Preventing Full Disk Issues

Running out of disk space in a MySQL environment can result in failed writes, temporary unavailability, and even data corruption. MySQL requires space not only for storing table data and indexes, but also for binary logs, temporary tables, transaction logs, and background operations. On platforms like Elestio, while the infrastructure is managed, users are responsible for monitoring data growth, managing logs, and planning for scale. This guide covers how to monitor disk usage, configure alerts, clean up unused data, and follow best practices to prevent full disk scenarios in a MySQL setup.

## **Monitoring Disk Usage**

Effective disk usage monitoring allows you to detect unexpected growth before it becomes critical. A combination of operating system-level checks and MySQL-specific queries gives a complete view of space consumption.

To inspect overall system storage from the terminal or container shell, use:

```
df -h
```

This command shows available and used space for each mount point. Identify the mount that hosts your MySQL data directory—usually `<span class="s1">/var/lib/mysql</span>` on Linux systems.

To check database-level usage inside MySQL, connect using the MySQL CLI and run:

```mysql
SELECT table_schema AS db_name,
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY size_mb DESC;
```

This reveals the size of each database schema in megabytes, including both data and indexes. For insights at the table level, run:

```mysql
SELECT table_name,
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
WHERE table_schema = 'your_database_name'
ORDER BY size_mb DESC
LIMIT 10;
```

Replace <span class="s1">'your\_database\_name'</span> with your actual schema name. This helps pinpoint which tables are growing fastest or consuming disproportionate space.

## **Configuring Alerts and Cleaning Up Storage**

Monitoring alone isn’t enough—automatic alerting and cleanup strategies ensure you’re notified in time and can act without downtime. In Docker Compose setups, container disk usage can be reviewed using:

```
docker system df
```

This shows disk consumption across images, containers, and volumes. To list and inspect unused volumes:

```
docker volume ls
```

And to remove a specific unused volume:

```
docker volume rm <volume-name>
```

<span class="s1">**Do not remove any volume actively used by MySQL.**</span> Before any cleanup, confirm that your database volumes are backed up and not mounted by a running service. Within MySQL, temporary tables, binary logs, and undo logs can consume space rapidly. You can check the binary log directory and purge old logs manually:

```mysql
SHOW BINARY LOGS;
```

To delete older binary logs and reclaim space:

```mysql
PURGE BINARY LOGS BEFORE NOW() - INTERVAL 7 DAY;
```

This deletes logs older than 7 days. Adjust the interval based on your backup retention policy. You can also automate this behavior using the configuration option:

```
[mysqld]
expire_logs_days = 7
```

## **Managing &amp; Optimizing Temporary Files** 

MySQL uses temporary files for complex queries, especially those involving large sorts or joins without indexes. These files are stored in the <span class="s2">tmpdir</span> directory and can fill up if not managed. Monitor the temp directory using OS tools:

```
du -sh /tmp
```

If temp file usage is consistently high, consider tuning the <span class="s1">tmp\_table\_size</span> and <span class="s1">max\_heap\_table\_size</span> variables to reduce reliance on disk-based temporary tables.

To identify tables with excessive unused space, use:

```mysql
SHOW TABLE STATUS WHERE Data_free > 0;
```

These tables may benefit from optimization. Reclaim the unused space by running:

```mysql
OPTIMIZE TABLE your_table_name;
```

This rewrites the table and defragments it, reclaiming disk space. For InnoDB tables, this can also compact the clustered index.

## **Best Practices for Disk Space Management**

Long-term disk health in MySQL requires more than just cleanup—it demands strategic design and active space governance.

- <span class="s1">**Avoid storing large files in the database.**</span> Use external object storage for PDFs, images, or videos and store references (e.g., URLs) in the database.
- <span class="s1">**Implement data retention policies.**</span> Archive old transactional data to another schema, flat files, or cold storage if it’s no longer queried frequently.
- <span class="s1">**Partition large tables**</span> using range or list partitioning to separate older data. Partitioning improves manageability and enables easier purging or archiving.
- <span class="s1">**Rotate logs regularly.**</span> Besides binary logs, general logs and error logs should be rotated using tools like <span class="s2">logrotate</span>, especially in containerized environments.
- <span class="s1">**Monitor InnoDB transaction logs**</span> (the <span class="s2">ib\_logfile\*</span> files). These are critical for crash recovery but should not grow indefinitely. If they become too large, you may need to reconfigure their size safely and restart the service.
- <span class="s1">**Store backups offsite.**</span> Backups stored on the same volume as your live database can fill your disk. Use Elestio’s backup tools to export backups to cloud storage or another disk.

# Checking Database Size and Related Issues

As your MySQL database grows, it’s crucial to track how space is being used across schemas, tables, and indexes. Uncontrolled growth can slow down queries, consume disk space, and complicate backups. While Elestio provides managed infrastructure, database storage optimization is still your responsibility. This guide explains how to inspect database size, find the largest tables and indexes, detect unused or bloated space, and optimize your data layout in MySQL.

## **Checking Database and Table Sizes**

MySQL’s <span class="s3">information\_schema</span> tables provide insights into how storage is distributed across your databases. This data helps prioritize cleanup, tuning, or archiving strategies. To calculate the total size used by each database:

```mysql
SELECT table_schema AS db_name,
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY size_mb DESC;
```

This output includes both table data and indexes, giving you an overview of which databases are consuming the most space. To identify the largest tables across all schemas:

```mysql
SELECT table_schema, table_name,
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_size_mb
FROM information_schema.tables
ORDER BY total_size_mb DESC
LIMIT 10;
```

This helps pinpoint space-heavy tables so you can review their contents, indexes, or retention policy. To break down data size versus index size for the top tables in a specific schema:

```mysql
SELECT table_name,
       ROUND(data_length / 1024 / 1024, 2) AS table_mb,
       ROUND(index_length / 1024 / 1024, 2) AS index_mb
FROM information_schema.tables
WHERE table_schema = 'your_database'
ORDER BY table_mb DESC
LIMIT 10;
```

Replace <span class="s1">'`your_database`'</span> with your actual schema name. A high index-to-data ratio could indicate overly aggressive indexing or opportunities for consolidation.

## **Detecting Bloat and Unused Space**

MySQL tables especially those using the InnoDB storage engine can accumulate unused space over time due to updates, deletes, or internal fragmentation. This can inflate table size and degrade performance.

To list tables with free (unused) space that could be reclaimed:

```mysql
SELECT table_name,
       ROUND(data_free / 1024 / 1024, 2) AS free_space_mb
FROM information_schema.tables
WHERE table_schema = 'your_database'
  AND data_free > 0
ORDER BY free_space_mb DESC
LIMIT 10;
```

Large <span class="s1">data\_free</span> values may indicate internal fragmentation or deleted rows that haven’t been reclaimed yet. You can recover this space using table optimization. To view the number of rows deleted but not yet reclaimed (estimated):

```mysql
SHOW TABLE STATUS FROM your_database;
```

Check the <span class="s1">Rows</span> and <span class="s1">Data\_free</span> columns for each table. If many rows have been deleted but space hasn’t shrunk, the table may need to be optimized.

## **Optimizing and Reclaiming Storage**

Once bloated or inefficient tables have been identified, MySQL provides several tools for optimization:

##### **Reclaim free space and defragment tables**

```mysql
OPTIMIZE TABLE your_table;
```

This command rewrites the table and indexes, reclaiming space and improving performance. It’s safe for InnoDB tables and especially useful after large `DELETE` or `UPDATE` operations.

##### **Rebuild fragmented or oversized indexes**

If indexes have grown large due to repeated updates or inserts, rebuilding them can reduce size and improve query speed. Use:

```mysql
ALTER TABLE your_table ENGINE=InnoDB;
```

This effectively recreates the table and all associated indexes, helping reclaim space and improve internal ordering.

> Note: Both <span class="s1">OPTIMIZE</span> and <span class="s1">ALTER ENGINE</span> operations lock the table for a short period. Run these during maintenance windows if the table is actively queried.

##### **Remove or archive old rows**

For time-series data or logs, consider deleting or archiving old records:

```mysql
DELETE FROM your_table
WHERE created_at < NOW() - INTERVAL 90 DAY;
```

Use <span class="s1">EXPLAIN</span> before executing large deletes to ensure they use indexes efficiently. You may also consider archiving to flat files or cold-storage tables.

##### **Partition large tables for better control**

If tables grow continuously (e.g., transaction logs or audit trails), use MySQL’s <span class="s3">**range partitioning**</span> or <span class="s3">**list partitioning**</span>:

```mysql
CREATE TABLE logs (
  id BIGINT,
  created_at DATE,
  ...
)
PARTITION BY RANGE (YEAR(created_at)) (
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);
```

Partitioning allows you to drop old data in chunks without full table scans or long DELETE operations.

## **Best Practices for Storage Management**

- <span class="s1">**Avoid storing large binary data in MySQL.**</span> Store files like images and videos in external object storage and reference them by URL or metadata.
- <span class="s1">**Monitor binary logs and purge them periodically.**</span> If replication or point-in-time recovery isn’t needed beyond a certain timeframe, add to your config:

```
expire_logs_days = 7
```

- Or purge manually:

```mysql
PURGE BINARY LOGS BEFORE NOW() - INTERVAL 7 DAY;
```

- <span class="s1">**Track backup file size and location.**</span> Ensure backups are stored on a separate volume or offsite to avoid filling the same disk as your live database.
- <span class="s1">**Enable slow query logging**</span> to detect inefficient queries that cause unnecessary data scans and table growth.
- <span class="s1">**Use monitoring tools**</span> (like Netdata, Prometheus exporters, or custom alert scripts) to track disk consumption trends over time.