Get started with Aiven for MySQL®

Start using Aiven for MySQL® by creating a service, connecting to it, and loading sample data.

Prerequisites

• Access to the Aiven Console
• MySQL CLI client installed
• Terraform installed if you prefer to get started using code

Create a service

Console

Create an Aiven for MySQL® service in the Aiven Console.

Terraform

1. Create an authentication token.

2. Store the authentication token in an environment variable:

   export TF_VAR_aiven_api_token=YOUR_AIVEN_API_TOKEN

3. Create the following Terraform files:

   • provider.tf for the aiven provider configuration

     terraform {
       required_providers {
         aiven = {
           source  = "aiven/aiven"
           version = ">=4.0.0, < 5.0.0"
         }
       }
     }
     
     provider "aiven" {
       api_token = var.aiven_api_token
     }

   • mysql.tf including the aiven_mysql resource

     resource "aiven_mysql" "mysql" {
       project      = var.project_name
       service_name = var.service_name
       cloud_name   = var.cloud_name
       plan         = var.service_plan
     }
     
     output "mysql_service_host" {
       value = aiven_mysql.mysql.service_host
     }
     
     output "mysql_service_port" {
       value = aiven_mysql.mysql.service_port
     }
     
     output "mysql_service_username" {
       value = aiven_mysql.mysql.service_username
     }
     
     output "mysql_service_password" {
       value     = aiven_mysql.mysql.service_password
       sensitive = true
     }

   • variables.tf for declaring your project variables

     variable "aiven_api_token" {
       description = "Aiven API token"
       type        = string
     }
     
     variable "project_name" {
       description = "Project name"
       type        = string
     }
     
     variable "cloud_name" {
       description = "Cloud name"
       type        = string
     }
     
     variable "service_name" {
       description = "Service name"
       type        = string
     }
     
     variable "service_plan" {
       description = "Service plan"
       type        = string
     }

   • terraform.tfvars for assigning actual values to your previously declared variables

     project_name = "testproject-o3jb"
     cloud_name   = "google-europe-west3"
     service_name = "mysql"
     service_plan = "startup-4"

4. Run terraform init > terraform plan > terraform apply --auto-approve.

5. Store Terraform outputs in environment variables so that they can be used for connecting:

   MYSQL_HOST="$(terraform output -raw mysql_service_host)"
   MYSQL_PORT="$(terraform output -raw mysql_service_port)"
   MYSQL_USER="$(terraform output -raw mysql_service_username)"
   MYSQL_PASSWORD="$(terraform output -raw mysql_service_password)"

Configure a service

Edit your service settings if the default service configuration doesn't meet your needs.

Console

1. Select the new service from the list of services on the Services page.
2. On the Overview page, select Service settings from the sidebar.
3. In the Advanced configuration section, make changes to the service configuration.

Terraform

Configure service parameters by updating the aiven_mysql resource, for example:

resource "aiven_mysql" "mysql" {
  project      = var.project_name
  service_name = var.service_name
  cloud_name   = var.cloud_name
  plan         = var.service_plan
+
+  maintenance_window_dow  = "monday"
+  maintenance_window_time = "01:00:00"
+  termination_protection  = true
+
+  mysql_user_config {
+    backup_hour      = 01
+    backup_minute    = 30
+    ip_filter_string = ["10.20.0.0/16"]
+    service_log      = true
+
+    mysql {
+      slow_query_log  = true
+      long_query_time = 5
+    }
+  }
}

output "mysql_service_host" {
  value = aiven_mysql.mysql.service_host
}

output "mysql_service_port" {
  value = aiven_mysql.mysql.service_port
}

output "mysql_service_username" {
  value = aiven_mysql.mysql.service_username
}

output "mysql_service_password" {
  value     = aiven_mysql.mysql.service_password
  sensitive = true
}

See the available configuration options in Advanced parameters for Aiven for MySQL.

Connect to the service

Console

1. Log in to the Aiven Console, and go to your organization > project > Aiven for MySQL service.

2. On the Overview page of your service, click Quick connect.

3. In the Connect window, select a tool or language to connect to your service, follow the connection instructions, and click Done.

   mysql --user avnadmin --password=ADMIN_PASSWORD --host mysql-sakila-dev-sandbox.f.aivencloud.com --port 12691 defaultdb

Terraform

Access your new service with the MySQL client using the environment variables assigned to Terraform outputs:

mysql --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USER --password=$MYSQL_PASSWORD --database defaultdb

mysql

Connect to your new service with mysql.

tip

Check more tools for connecting to Aiven for MySQL in Connect to Aiven for MySQL.

Load a test dataset

Sakila is a sample dataset that represents a DVD rental store. It provides a standard schema highlighting MySQL features.

1. Download the sakila database archive (tar or zip format) from the MySQL example databases page, and extract it to your desired location (for example /tmp/).

2. From the folder where you unpacked the archive, connect to your MySQL service, create a sakila database, and connect to it:

   CREATE DATABASE sakila;
   USE sakila;

3. Populate the database:

   source sakila-schema.sql;
   source sakila-data.sql;

4. Verify what objects have been created:

   SHOW FULL TABLES;
   Expected output

   +----------------------------+------------+
   | Tables_in_sakila           | Table_type |
   +----------------------------+------------+
   | actor                      | BASE TABLE |
   | actor_info                 | VIEW       |
   | address                    | BASE TABLE |
   | category                   | BASE TABLE |
   | city                       | BASE TABLE |
   | country                    | BASE TABLE |
   | customer                   | BASE TABLE |
   | customer_list              | VIEW       |
   | film                       | BASE TABLE |
   | film_actor                 | BASE TABLE |
   | film_category              | BASE TABLE |
   | film_list                  | VIEW       |
   | film_text                  | BASE TABLE |
   | inventory                  | BASE TABLE |
   | language                   | BASE TABLE |
   | nicer_but_slower_film_list | VIEW       |
   | payment                    | BASE TABLE |
   | rental                     | BASE TABLE |
   | sales_by_film_category     | VIEW       |
   | sales_by_store             | VIEW       |
   | staff                      | BASE TABLE |
   | staff_list                 | VIEW       |
   | store                      | BASE TABLE |
   +----------------------------+------------+
   23 rows in set

Query data

Read data

Retrieve all the data from a table, for example, from language:

SELECT * FROM language;

Expected output

+-------------+----------+---------------------+
| language_id | name     | last_update         |
+-------------+----------+---------------------+
|           1 | English  | 2006-02-15 05:02:19 |
|           2 | Italian  | 2006-02-15 05:02:19 |
|           3 | Japanese | 2006-02-15 05:02:19 |
|           4 | Mandarin | 2006-02-15 05:02:19 |
|           5 | French   | 2006-02-15 05:02:19 |
|           6 | German   | 2006-02-15 05:02:19 |
+-------------+----------+---------------------+
6 rows in set

Write data

Add a row to a table, for example, to category:

INSERT INTO category(category_id,name) VALUES(17,'Thriller');

Expected output

Query OK, 1 row affected

Check that your new row is there:

SELECT * FROM category WHERE name = 'Thriller';

Expected output

+-------------+----------+---------------------+
| category_id | name     | last_update         |
+-------------+----------+---------------------+
|          17 | Thriller | 2024-05-22 11:04:03 |
+-------------+----------+---------------------+
1 row in set

Related pages

• Connect to Aiven for MySQL with MySQL Workbench
• Migrate to Aiven from an external MySQL
• Create additional databases
• Aiven Service Level Agreement