Get started with Aiven for MySQL®
Start using Aiven for MySQL® by creating a service, connecting to it, and loading sample data.
Prerequisites
- Console
- Terraform
- Access to the Aiven Console
- MySQL CLI client installed
- Terraform installed
- A personal token
- MySQL CLI client installed
Create a service
- Console
- Terraform
-
From your project, in the Services page, click Create service.
-
From the Select service page, click MySQL.
-
Select the cloud provider and region to host your service on.
noteThe pricing for the same service can vary between different providers and regions. The service summary shows you the pricing for your selected options.
-
Select a service plan.
noteThis determines the number of servers and the memory, CPU, and disk resources allocated to your service. See Plans & Pricing.
-
Optional: Add disk storage.
-
Enter a name for your service.
importantYou cannot change the name after you create the service.
You can fork the service with a new name instead.
-
Optional: Add tags.
-
Click Create service.
The Overview page of the service opens. It shows the connection parameters for your service, its current status, and the configuration options.
The status of the service is Rebuilding during its creation. When the status becomes Running, you can start using the service. This typically takes couple of minutes and can vary between cloud providers and regions.
The following example files are also available in the Aiven Terraform Provider repository on GitHub.
-
Create a file named
provider.tf
and add the following:Loading...
-
Create a file named
service.tf
and add the following:Loading...
-
Create a file named
variables.tf
and add the following:Loading...
-
Create a file named
terraform.tfvars
and add values for the variables without defaults:aiven_token
: your tokenaiven_project_name
: the name of one of your Aiven projectsmysql_password
: a password for the service user
-
To output connection details, create a file named
output.tf
and add the following:Loading...
To apply your Terraform configuration:
-
Initialize Terraform by running:
terraform init
The output is similar to the following:
Initializing the backend...
Initializing provider plugins...
- Finding aiven/aiven versions matching ">= 4.0.0, < 5.0.0"...
- Installing aiven/aiven v4.9.2...
- Installed aiven/aiven v4.9.2
...
Terraform has been successfully initialized!
... -
To create an execution plan and preview the changes, run:
terraform plan
-
To deploy your changes, run:
terraform apply --auto-approve
Configure a service
Edit your service settings if the default service configuration doesn't meet your needs.
- Console
- Terraform
- Select the new service from the list of services on the Services page.
- On the Overview page, select Service settings from the sidebar.
- In the Advanced configuration section, make changes to the service configuration.
See the available configuration options in Advanced parameters for Aiven for MySQL.
See
the aiven_mysql
resource documentation
for the full schema.
Connect to the service
- Console
- Terraform
- mysql
-
Log in to the Aiven Console, and go to your organization > project > Aiven for MySQL service.
-
On the Overview page of your service, click Quick connect.
-
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
Access your new service with the MySQL client using the outputs.
-
To store the outputs in environment variables, run:
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)" -
To use the environment variables with the MySQL client to connect to the service, run:
mysql --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USER --password=$MYSQL_PASSWORD --database defaultdb
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.
-
Download the
sakila
database archive (tar
orzip
format) from the MySQL example databases page, and extract it to your desired location (for example/tmp/
). -
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; -
Populate the database:
source sakila-schema.sql;
source sakila-data.sql; -
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