6.941
Configure Aiven Cloud MySQL Service and Connect from R

Cloud MySQL Databases

A cloud MySQL database is a MySQL database instance hosted and managed by a cloud service provider rather than running on your local machine or on-premise servers. The database engine runs on the provider’s infrastructure, securely accessible over the internet. A cloud-hosted MySQL database (or any database for that matter) affords certain benefits, while introducing several important drawbacks as well. Let’s take a look at those benefits and drawbacks in a bit more detail before diving into the mechanisms to configure and connect to a cloud MySQL instance.

Cloud vs Local Databases

The key differences when using a cloud-hosted database versus a local database server installation are the following:

• Infrastructure Management: The cloud provider handles server provisioning, maintenance, updates, and hardware failures
• Scalability: Resources (CPU, memory, storage) can be adjusted dynamically based on demand
• Dynamic Pricing: There are few upfront costs and costs are incurred based on demand
• Availability: Built-in redundancy and failover mechanisms ensure higher availability
• Security: Industry-standard security including encryption, network isolation, and compliance certifications
• Backup and Recovery: Automated backup schedules with point-in-time recovery capabilities

Advantages of Cloud Databases

Operational Benefits:

• Eliminates server administration overhead (OS updates, security patches, hardware monitoring)
• Reduces time-to-deployment from hours/days to minutes
• Automatic scaling prevents performance bottlenecks during traffic spikes
• Professional monitoring and alerting systems

Reliability and Security:

• Multi-zone replication provides fault tolerance beyond single-machine setups
• Enterprise-grade security controls including VPC isolation and SSL/TLS encryption
• Compliance with standards like SOC 2, ISO 27001, and GDPR
• Regular automated backups with configurable retention periods

Cost Efficiency:

• Pay-per-use model eliminates upfront hardware costs
• No need for dedicated database administrators for basic operations
• Reduced electricity and cooling costs

Disadvantages of Cloud Databases

Network Dependency and Latency

Internet Connectivity Requirements:

• Complete dependency on internet connection for database access
• Application becomes unusable during network outages or ISP issues
• Local MySQL continues functioning during internet disruptions

Latency Considerations:

• Network round-trip time adds 10-100ms+ per query depending on geographic distance
• Cumulative effect significant for applications making many small queries
• Local MySQL typically has <1ms latency for database operations
• High-frequency trading, real-time gaming, or micro-service architectures particularly affected

Bandwidth Limitations:

• Large result sets consume internet bandwidth
• Data-intensive operations (bulk imports, complex reporting) may be slow
• Bandwidth costs for high-traffic applications
• Local operations use internal network speeds (gigabit+)

Cost Structure

Ongoing Operational Costs:

• Monthly/hourly fees continue indefinitely vs one-time hardware purchase
• Costs scale with usage, storage, and performance requirements
• Data transfer fees for high-volume applications
• Long-term total cost often exceeds local hardware investment
• Failure to pay disrupts the service and may even lead to loss of data
• Vendors may suddenly raise prices leding to unanticipated costs

Unpredictable Scaling Costs:

• Sudden traffic spikes result in immediate cost increases
• Difficult to budget for variable workloads
• Premium pricing for high-performance instances
• Additional charges for backups, monitoring, and support features

Data Control and Privacy

Data Sovereignty:

• Operational (and possibly sensitive) data physically resides on third-party infrastructure
• Subject to cloud provider’s data handling policies and potential breaches
• Government access requests may affect your data without your knowledge
• Compliance challenges in regulated industries (healthcare, finance)
• Actual location of server hosting the database may be subject to different laws and regulations and may violate customer terms of service of service-level agreements
• Trade or actual wars may result in loss of access to data when servers are located in different countries
• Data privacy policies can change and may result in violations of customer terms of service

Vendor Lock-in Risks:

• Migration complexity increases over time with custom configurations
• Proprietary backup formats or management tools
• Price increases difficult to avoid once heavily invested
• Dependency on vendor’s business continuity and policy changes
• Vendor may cease business operations suddenly

Performance Limitations

Resource Sharing:

• Multi-tenant infrastructure means shared underlying hardware
• “Noisy neighbor” problems where other users affect your performance
• Less predictable performance compared to dedicated hardware
• Limited ability to optimize hardware for specific workloads

Configuration Constraints:

• Restricted access to advanced MySQL configuration parameters
• Cannot install custom storage engines or modify core database behavior
• Limited control over caching strategies and memory allocation
• Operating system-level optimizations unavailable

Security and Access Control

Extended Attack Surface:

• Internet-accessible endpoints create additional security risks
• Dependency on cloud provider’s security practices
• Shared responsibility model can create security gaps
• Multiple authentication layers increase complexity

Limited Administrative Control:

• Cannot implement custom security monitoring or intrusion detection
• Restricted access to system logs and performance metrics
• Unable to implement organization-specific security policies
• Dependency on vendor’s incident response procedures

Technical Limitations

Customization Restrictions:

• Cannot modify MySQL source code or install custom patches
• Limited plugin ecosystem compared to self-managed installations
• Restricted ability to integrate with custom monitoring or backup solutions
• Version upgrade timing controlled by vendor, not your schedule

Development and Testing Constraints:

• Development environments require internet connectivity
• Difficulty doing offline development
• Replicating exact production configurations locally may be impossible
• Additional complexity for continuous integration pipelines
• Database seeding and testing may be slower due to network overhead

Business Continuity Risks

Vendor Dependency:

• Service disruptions affect your application directly
• Limited recourse during extended outages
• Dependent on vendor’s disaster recovery capabilities
• Business continuity tied to third-party company’s stability

Data Portability:

• Export/import processes for migration can be complex and time-consuming
• Potential data format incompatibilities
• Downtime required for migration to alternative solutions
• Risk of data loss during migration processes

Business Case for Local Databases

While cloud-hosted database-as-a-service databases may be ideal for many use cases, a local database offers distinct advantages. Applications requiring microsecond-level latency or handling thousands of queries per second may benefit from local deployment. Industries with strict data residency requirements or air-gapped network needs are best hosted locally.

Furthermore, stable applications with predictable usage patterns running for multiple years provide predictable costs. In addition, applications requiring specific database builds (e.g., a specific MySQL version), custom storage engines, or deep system integration.

From a development perspective, locally hosted databases often provides faster iteration cycles and don’t consume costly cloud resources.

The choice between a cloud or a local database ultimately depends on weighing these disadvantages against the operational benefits. For many modern applications and business needs the cloud trade-offs are generally acceptable in view of the reduced administrative overhead and improved reliability that is often hard to replicate locally without dedicated on-staff database administrators.

Aiven Platform

Aiven is a cloud database-as-a-service (DBaaS) platform specializing in open-source data technologies. Founded in 2016, Aiven provides managed services for MySQL, PostgreSQL, Apache Kafka, Redis, and other data infrastructure components across major cloud providers (AWS, Google Cloud, Azure).

Aiven’s distinguishing features include the ability to deploy across different cloud providers from a single interface while maintaining compatibility with standard MySQL without vendor lock-in. For developers it offer comprehensive APIs, Terraform support, and CLI tools. Like other cloud database platforms, Aiven’s data centers are distributed across multiple regions for latency optimization.

Configuring MySQL on Aiven

To set up a database on Aiven requires an account with the platform.

1. Register at aiven.io using your academic email
2. Respond to any verification emails
3. Create a new project (logical container for your services)
4. Select your preferred cloud provider and region based on latency requirements

Once an account has been created you can configure a MySQL instance. Navigate to “Create Service” and configure:

• Service type: MySQL
• Cloud provider: AWS, Google Cloud, or Azure
• Region: Choose based on your geographic location
• Plan: Start with the smallest (likely free) plan (typically 1 CPU, 1GB RAM, 1GB database storage) for development
• Service name: Use descriptive naming (e.g., “class-mysql-dev”)

Next, you need to manage network Security. Start by configuring IP whitelist in the service settings and add your development machine’s public IP. For production, use VPC peering or private networking. Whenever feasible, enable SSL/TLS enforcement for encrypted connections. Download the SSL certificate and place it in an accessible but unshared folder (and ensure that the certificate cannot be accessed by an untrusted third party as revealing the certificate is a vulnerability that can expose your database and your data).

As you scale up your database needs, create application-specific databases using the Aiven console. Rather than using the administrative user for service connections, add dedicated database users with minimal required privileges, e.g. limit access to tables and views.

Finally, add monitoring by configuring alert thresholds for CPU, memory, and connection limits. Set up automated backups with appropriate retention periods. Monitor query performance through the dashboard.

The resulting setup provides a production-ready MySQL instance with enterprise-grade reliability, security, and monitoring capabilities, allowing you to focus on application development rather than database administration.

Connecting to MySQL from R

In this section, we demonstrate how to connect to an already configured and running MySQL instance hosted on Aiven. The connection parameters are unique to the database and the ones provided below are for our sample database.

Obtaining Connection Parameters

Replace the connection parameters with your own parameter values and ensure that the MySQL service is running.

Unsecured Connection

The code below shows how to connect to the MySQL service over an unsecured (unencrypted) connection. While the connection is password protected all traffic (SQL statements and returned data) is transmitted unencrypted and can be intercepted and read. This should only be used when SSL is not available or when operating on a secured internal network.

# load required library
library(RMySQL)

# define settings
db_host_aiven <- "mysql-2b10ba46-khoury-45a7.aivencloud.com"
db_port_aiven <- 11214
db_name_aiven <- "defaultdb"
db_user_aiven <- "avnadmin"
db_pwd_aiven <- "AVNS_E13X61MnR9gGy16VLEa"

# connect to remote MySQL server and database
mydb.aiven <-  dbConnect(RMySQL::MySQL(), 
                         user = db_user_aiven, 
                         password = db_pwd_aiven,
                         dbname = db_name_aiven, 
                         host = db_host_aiven, 
                         port = db_port_aiven)

Secure Connection

If SSL mode is not used, data is not encrypted and can be exposed. To use SSL mode, add the certificate to the connection, either as path to local file or by pasting into text. However, keep in mind that if the source code is published or shared, then the certificate is also shared which likely also represents a security leak and attack vector.

You need to first download the certificate file (and keep it secure).

# load required library
library(RMySQL)

# define settings
db_host_aiven <- "mysql-2b10ba46-khoury-45a7.aivencloud.com"
db_port_aiven <- 11214
db_name_aiven <- "defaultdb"
db_user_aiven <- "avnadmin"
db_pwd_aiven <- "AVNS_E13X61MnR9gGy16VLEa"

# connect securely to remote MySQL server and database
mydb.aiven <-  dbConnect(RMySQL::MySQL(), 
                         user = db_user_aiven, 
                         password = db_pwd_aiven,
                         dbname = db_name_aiven, 
                         host = db_host_aiven, 
                         port = db_port_aiven,
                         sslmode = "require",
                         sslcert = "ca-2b10ba46.pem")

Alternatively, and to avoid having to keep the certificate in a file, we can also embed the certificate as text. Of course, if you were to distribute the source code you would expose the certificate which may not be desirable.

# load required library
library(RMySQL)

# define settings
db_host_aiven <- "mysql-2b10ba46-khoury-45a7.aivencloud.com"
db_port_aiven <- 11214
db_name_aiven <- "defaultdb"
db_user_aiven <- "avnadmin"
db_pwd_aiven <- "AVNS_E13X61MnR9gGy16VLEa"

# embedded SSL certificate
db_cert <- 
"
-----BEGIN CERTIFICATE-----
MIIEQTCCAqmgAwIBAgIUZxVjk8G5pC8kOiHVBEupNZVC1sMwDQYJKoZIhvcNAQEM
BQAwOjE4MDYGA1UEAwwvNDY2YjAxNzctODFjOC00N2EwLTlmMmQtNjMxMDYxZGYx
MDY3IFByb2plY3QgQ0EwHhcNMjMwNDExMDExNTE1WhcNMzMwNDA4MDExNTE1WjA6
MTgwNgYDVQQDDC80NjZiMDE3Ny04MWM4LTQ3YTAtOWYyZC02MzEwNjFkZjEwNjcg
UHJvamVjdCBDQTCCAaIwDQYJKoZIhvcNAQEBBQADggGPADCCAYoCggGBAOp2nKQj
CPbNIr7UxSwtdjgME9Z5v/RM+qb6nj2iDPBsTuxTAfakp2MQs4ylshk3kIhPq8H/
ecr0IL2Sms02cII7JnMXrR2JheiaSciEJ7ztfdqQsL9L79ZpnTO9ZWh+HNj37YYQ
2JxscHLz+bTCW2Bg2HzWfb9UPkHQfXRf5GwGABvm4EdStue9AQTNLe+/pEK2/1Fg
kn+FBme7YgVvRSsLRuWLPMDKI213GzdEV0iJ10nFeN1lcpgq+WLWMJIqUjMkF5e9
/gC6/ZEi4SVtyWtQng/V8pmHmMczDmiJGQ7XVXNKgTy4DPtO8LK9SNNBYeqrm1J8
uCxDuukUFXmHe0b7kO1jeETbzgfBj29dDSE2IxE1UusORjO/RPQpJbe6DWOhe9Xx
KI+8hjoFARvlkKQ2C+m+GuyFuz+Yr6QJBNv1i/oexIR/1lZI34J1tEEQo4pndVDf
+Exr+gwZdL8INoFDmKxjCtCiobatreMFaETPL/nMjgmgFNeEifP7FYB/DwIDAQAB
oz8wPTAdBgNVHQ4EFgQUBCoZdr/VYezftsfHCW4zpaAPHm8wDwYDVR0TBAgwBgEB
/wIBADALBgNVHQ8EBAMCAQYwDQYJKoZIhvcNAQEMBQADggGBADKVSW8sIyZpTQb5
XWjeQ+I7BS7+78BlJBVPFw41H7F6nSTivkxmXl8cpTkqOAr0GeITGBI2rdJQaAxC
6hu5tdm4ClxTKnoJ0w6zk7yLtSmSCnPT2T2BI/dPsmSsplU/doeS1rGS+7GU0iGU
JN2bLqFdrg2z3JCIaszW1bsikJw3vPPORlmJl0QH81TZGCqvfINEJHY66MQb6nGj
23ZUQrLFGNmkjO5kntBSG4dopcznCS7X1f1G3MUsx5yKxpayVCeC1I+rIBroaNAJ
ga2mYbgi/8GUYb/z2fu3fmNaX7NtpH2B5OCvvMttnEF5tx4e9qss8AVMrNCcUxBi
eUgmMPy29u8dfbP7zuV/aWW0/RnYOSQRBbagwFUy/DMsBfIHDFznGqRED294hQ6q
Z0aV+QkMGduDXJhiowrrSLeN3LrC4tR16f2YYUBCqs+nz4cBx4jp/rmmAUGgGBlK
AFpY0k33VfIG0vIRz0RuwdzK0sxBGC8/jGhM/wdMuHvhJIr4XQ==
-----END CERTIFICATE-----
"

# connect securely to remote MySQL server and database
mydb.aiven <-  dbConnect(RMySQL::MySQL(), 
                         user = db_user_aiven, 
                         password = db_pwd_aiven,
                         dbname = db_name_aiven, 
                         host = db_host_aiven, 
                         port = db_port_aiven,
                         sslmode = "require",
                         sslcert = db_cert)

Next, we will test the connection by creating a table with a CHECK constraint, inserting rows, and then querying the table.

drop table if exists courses

create table if not exists courses (
  cid INTEGER NOT NULL PRIMARY KEY,
  title varchar(32) NOT NULL,
  credits INTEGER NOT NULL,
  check (credits > 1)
)

Notice that the text fields (columns) are enclosed in single quotes rather than double quotes. This is required for the version of MySQL hosted by Aiven.

insert into courses (cid,title,credits) values 
  (1100,'CS and Application', 4),
  (2000,'Princ Info Sci', 4),
  (5200,'DBMS', 4),
  (5030,'Intro ML', 3)

insert into courses values (1200,'Web Systems', 4)

Let’s ensure the data is there:

select * from courses;

Table 1: 5 records

cid	title	credits
1100	CS and Application	4
1200	Web Systems	4
2000	Princ Info Sci	4
5030	Intro ML	3
5200	DBMS	4

select count(*) from courses;

Table 2: 1 records

count(*)
5

Looks like all is working.

Let’s execute some SQL with Aiven using R code rather than a {sql} block.

sql <- "select * from courses"
rs <- dbGetQuery(con = mydb.aiven,
                 statement = sql)

print(rs)

##    cid              title credits
## 1 1100 CS and Application       4
## 2 1200        Web Systems       4
## 3 2000     Princ Info Sci       4
## 4 5030           Intro ML       3
## 5 5200               DBMS       4

A key drawback to MySQL on Aiven is that is does not allow bulk insertion of data via dbWriteTable(), so the code below does not work.

df <- data.frame(cid = 9000:9999)

df$title <- "no title"
df$credits <- 4

status <- dbWriteTable(mydb.aiven, "courses", df, 
                       overwrite = F, append = T, 
                       row.names=FALSE)

So, any insertion must be done using SQL INSERT statements, possibly using batch insertion (i.e., multiple rows in a single INSERT statement) to reduce the performance overhead and speed up insertion. This is not an actual issue as bulk loading is quite slow and not scalable, so it should be avoided regardless of support – although it is quite convenient.

Common Problems

• the free account has a maximum database capacity of 5MB
• local firewall or anti-virus software that blocks port 3306; disable if there are connection issues
• not clicking on the activation link in the email sent after account creation (and checking spam to ensure it did not end up there)
• no support for dbWriteTable()

A common pitfall is that your local computer has a firewall configured that does not allow a client to connect to the cloud database (meaning the port is blocked). One way to “test” for that is to run your connection code on http://posit.cloud – if it works there, then it is a problem with your local setup. Alternatively, try connecting to your MySQL from the MySQL Workbench. If you can connect from the workbench then the cause is with your client code.

CAUTION: Both Aiven and db4free do not allow the use of dbWriteTable(); this approach of bulk-loading does not scale, so it should be avoided anyway. So, you must use INSERT SQL statements to write the data into Aiven and db4free. In addition, for Aiven, all text fields must be enclosed in single rather than double quotes. Inserting data row-by-row is very slow, so insert 100 or 500 rows at a time (remember that an INSERT statement can insert multiple rows at a time).

Problem Root Cause Analysis

If you have created an account on Aiven (and configured a database) but you cannot connect, then here are some suggestions for identifying the root cause:

1. verify that the database service is running
2. check that the connection parameters are correct
3. check for any firewall issues – to test this, upload the connection code (a sandbox that quickly checks) to posit.cloud and run from there; if it runs on posit.cloud and not locally, it is likely a firewall or anti-virus issue

The most common reason for not being able to connect from your client to the database, is that the server hasn’t been started. Go to the Aiven console and make sure it’s running:

Also, keep in mind:

• Aiven does not support dbWriteTable()
• Only recent versions of MySQL support CHECK constraints

Disconnect from Database

Disconnect from the database (important as database servers have limited numbers of connection and each connection uses a resource). Use dbDisconnect() once you no longer have a need to access the database. The code below demonstrates this.

status <- dbDisconnect(mydb.aiven)

Database Administration with MySQL Workbench

We recommend that you install MySQL Workbench to help administer your MySQL database. Use the same connection information to connect to your database from MySQL Workbench.

---

Files & Resources

All Files for Lesson 6.941

---

Acknowledgements

Claude Sonnet 4 was used in the preparation of this lesson in addition to web searches.

Errata

Let us know.