Aiven MySQL services are managed from Aiven web console. First login to the console with your email address and password and you will be automatically taken to the "Services" view that shows all the services of the currently selected project.
Projects allow organizing groups of services under different topics and each project can for example have different billing settings. An empty project is created for you automatically when you sign-up and the free credits are attached to this project. You can create new projects by clicking the project name in the left side-bar and selecting "Create a new project". The same menu can also be used to switch between projects.
To get started with Aiven MySQL, first click the "Create a new service" button.
The dialog that opens allows you to specify the main service properties:
- Service name: A short name for the service used to distinguish it from other services. A random name is provided, but you can type in a more friendly name.
- Service type: Select "MySQL". Currently only MySQL version 8 is supported.
- Plan: How many servers and what kind of memory/CPU/disk resources will be allocated to run your service. Note that the single-node "Hobbyist" and "Startup" plans are not recommended for applications where durability of the data is essential. Select one of the "high availability" plans for speedy recovery and best protection against data loss against critical node failures.
- Cloud: Which cloud and region to run the service on. Note that the pricing of the same service may differ between cloud providers and their regions.
After making the selections, click the "Create" button and you will be taken back to the service list view and the newly created service is shown with an indicator that it is being created.
Click the service name in the list and the "Overview" information page for the service opens. This view shows the connection parameters for your service, its current status and allows making changes to the service.
The "Status" indicator will say "REBUILDING" while the service is being created for you. Once the service is up and running, the light will change to green and it will say "RUNNING". Note that while typically services start in a couple of minutes, the performance between clouds varies and it can take longer under some circumstances.
There are multiple ways you can try out your new MySQL service. From command line the easiest option might be the
mysqlsh tool provided by Oracle, which can directly accept the service URL shown on the Service Overview page:
mysqlsh --sql mysql://avnadmin:firstname.lastname@example.org:12691/defaultdb?ssl-mode=REQUIRED
MySQL ssl defaultdb SQL> select 1 + 2 as three;
| three |
| 3 |
1 row in set (0.0539 sec)
mysql command line tool may be used as well but for that you need to manually specify individual parameters (note that if giving password on command line you must pass it as shown below, space between the parameter name and value does not work like it does for other parameters):
mysql --user avnadmin --password=giufg3yd1b89sqjb --host rauli-mysql-dev-sandbox.aivencloud.com --port 12691 defaultdb
You can also use a graphical client like MySQL Workbench to connect. Enter the individual connection parameters as shown on Service Overview page and also download the SSL CA certificate and specify the file on SSL page.
Aiven does not enforce using SSL but it is recommended to always use that. In order to make a properly secure connection you will need to download the CA certificate and configure that in client settings.
In order to create more databases, you can go to to the service's Databases tab in the Aiven web console, type in the name of the database to add in the Create a new database box, and click the "Add database" button to add the database. Database users can be added in a similar way by going to the Users tab.
NOTE: New users added from the Aiven web console use the
caching_sha2_password authentication mechanism that was added in MySQL 8.0.3. To successfully connect you will need to use new enough client libraries. If for any reason you are forced to use a client that only supports the older
mysql_native_password authentication mechanism you can use the following command to change the authentication mechanism for an individual user after the user has been created from the Aiven web console (you can of course also create users from command line but then you'll need to manually grant the user appropriate privileges):
ALTER USER myuser IDENTIFIED WITH mysql_native_password BY 'mypassword';
This Python example uses the PyMySQL library for connecting to Aiven MySQL. The host, password and port values are just examples and you should replace them with whatever is shown for your own service.
timeout = 10
connection = pymysql.connect(
cursor = connection.cursor()
cursor.execute("CREATE TABLE mytest (id INTEGER PRIMARY KEY)")
cursor.execute("INSERT INTO mytest (id) VALUES (1), (2)")
cursor.execute("SELECT * FROM mytest")