The hosting solutions below are fully free or free with limits. However, some of the services have significant feature restriction, so be sure the restrictions will not be limiting for your use case.
| Service | Cost | Limits |
|---|---|---|
| dbfree.net | free | does not allow bulk insertion with dbWriteTable(); can be slow |
| freemysqlhosting.net | free | 5MB max database size |
| aiven.io | free |
These hosting services have a semi-free option where credits are provided that can be used to pay the services for a limited time. However, some services will immediately charge for overage if the free credits are exhausted. This is particularly true for AWS and it is possible to end up with significant charges.
This section provides setup guides for two free or semi-free MySQL cloud databases:
This section explains how to create a remote instance of a MySQL database on db4free.net connect to the database from R.
The tutorial below demonstrates how to create a new database on db4free.net.
You need to follow the steps above first and create your database first. Be sure to write down the name of the database, your user name, and your password. If you are attempting to run the code below yourself, comment out the database you did not set up.
# 1. Library
library(RMySQL)
# 2. Settings
db_user <- 'cs5200' # use your value from the setup
db_password <- 'khour#2021' # use your value from the setup
db_name <- 'cs5200db1' # use your value from the setup
db_host <- 'db4free.net' # for db4free.net
db_port <- 3306
# 3. Connect to remote server database
mydb.db4free <- dbConnect(RMySQL::MySQL(), user = db_user, password = db_password,
dbname = db_name, host = db_host, port = db_port)Next, we will test the connection by creating a table, inserting rows, and then querying the table.
create table if not exists courses (
cid INTEGER NOT NULL PRIMARY KEY,
title varchar(32) NOT NULL,
credits INTEGER NOT NULL
)insert into courses (cid,title,credits) values
(1100,"CS and Application", 4),
(2000,"Princ Info Sci", 4),
(5200,"DBMS", 4),
(5030,"Intro ML", 3)| cid | title | credits |
|---|---|---|
| 1100 | CS and Application | 4 |
| 2000 | Princ Info Sci | 4 |
| 5030 | Intro ML | 3 |
| count(*) |
|---|
| 4 |
The key drawback to db4free is that is does not allow bulk insertion of data via dbWriteTable() as the code below causes the error [“Loading local data is disabled”]{color:red}. So, insert data one or more rows at a time using SQL INSERT statements.
dbWriteTable() which is not supported; so, insert data one or more rows at a time using SQL INSERT statementsThis section explains how to create a remote instance of a MySQL database on freemysqlhosting.net connect to the database from R.
Visit freemysqlhosting.net and set up an account and then configure a database. Be sure to write down the name of the database, your user name, and your password. Check your email for activation links.
You need to follow the steps above first and create your database first. If you are attempting to run the code below yourself, comment out the database you did not set up.
# 1. Library
library(RMySQL)
# 2. Settings freemysqlhosting.net (max 5MB)
db_name_fh <- "sql9612301"
db_user_fh <- "sql9612301"
db_host_fh <- "sql9.freemysqlhosting.net"
db_pwd_fh <- "5bARWSaBig"
db_port_fh <- 3306
# 3. Connect to remote server database
mydb.fh <- dbConnect(RMySQL::MySQL(), user = db_user_fh, password = db_pwd_fh,
dbname = db_name_fh, host = db_host_fh, port = db_port_fh)
mydb <- mydb.fhNext, we will test the connection by creating a table, inserting rows, and then querying the table.
create table if not exists courses (
cid INTEGER NOT NULL PRIMARY KEY,
title varchar(32) NOT NULL,
credits INTEGER NOT NULL
)insert into courses (cid,title,credits) values
(1100,"CS and Application", 4),
(2000,"Princ Info Sci", 4),
(5200,"DBMS", 4),
(5030,"Intro ML", 3)| cid | title | credits |
|---|---|---|
| 1100 | CS and Application | 4 |
| 2000 | Princ Info Sci | 4 |
| 5030 | Intro ML | 3 |
| count(*) |
|---|
| 4 |
The key drawback to db4free was that is does not allow bulk insertion of data via dbWriteTable(), but freemysqlhosting does support bulk loading as demonstrated in the example below.
df <- data.frame(cid = 9000:9999)
df$title <- "no title"
df$credits <- 4
status <- dbWriteTable(mydb.fh, "courses", df,
overwrite = F, append = T,
row.names=FALSE)Note that the new rows have been inserted:
| count(*) |
|---|
| 4 |
Disconnect from the database (important as database servers have limited numbers of connection and each connection uses a resource). Use dbDisconnect().
MySQL Workbench You should always install MySQL Workbench (Links to an external site.) to help administer any local or remote database. Use the same connection information to connect to your database.
From the creators of MySQL Workbench…
MySQL Workbench is a unified visual tool for database architects, developers, and DBAs. MySQL Workbench provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, and much more. MySQL Workbench is available on Windows, Linux and Mac OS X.
MySQL Workbench enables a DBA, developer, or data architect to visually design, model, generate, and manage databases. It includes everything a data modeler needs for creating complex ER models, forward and reverse engineering, and also delivers key features for performing difficult change management and documentation tasks that normally require much time and effort.
MySQL Workbench delivers visual tools for creating, executing, and optimizing SQL queries. The SQL Editor provides color syntax highlighting, auto-complete, reuse of SQL snippets, and execution history of SQL. The Database Connections Panel enables developers to easily manage standard database connections, including MySQL Fabric. The Object Browser provides instant access to database schema and objects.
MySQL Workbench provides a visual console to easily administer MySQL environments and gain better visibility into databases. Developers and DBAs can use the visual tools for configuring servers, administering users, performing backup and recovery, inspecting audit data, and viewing database health.
MySQL Workbench now provides a complete, easy to use solution for migrating Microsoft SQL Server, Microsoft Access, Sybase ASE, PostreSQL, and other RDBMS tables, objects and data to MySQL. Developers and DBAs can quickly and easily convert existing applications to run on MySQL both on Windows and other platforms. Migration also supports migrating from earlier versions of MySQL to the latest releases.
None.
None collected yet. Let us know.