Create MySQL Test Instance with Oracle Cloud Free Tier

Oracle announced this week at Oracle OpenWorld that it has introduced a new cloud offer called Oracle Cloud Free Tier. As the name suggest, it allows you to run a few limited instances in Oracle Cloud for free. I will in this blog show how you can use the free tier to setup a MySQL test instance.

Tip

If you want to read more about Oracle Cloud Free Tier see https://www.oracle.com/cloud/free/ and the FAQ at https://www.oracle.com/cloud/free/faq.html.

The first step is to sign up for the cloud service which you do by opening https://www.oracle.com/cloud/free/ and click on the Start for free button near the top of the page:

Click on Start for free to get started

This will take you through a series of pages where you create your Oracle Cloud account. The steps are straight forward. You will need to provide a valid mobile number and credit card (but no fees are charges provided you stick to the always free services). At the end you are directed to the Oracle Cloud login page:

Oracle Cloud Login Screen
Oracle Cloud Login Screen

Enter the email address and password from the registration process, and you are ready to use create Oracle Cloud services. You will need a compute instance which you create by choosing the Compute Create a VM Instance quick action:

The Oracle Cloud front page after logging in the first time

Notice how there is a label Always Free Eligible which tells you can create instances in the free tier.

On the next screen, you can fill in the details for the instance. You can choose all the default values which will create a VM.Standard.E2.1.Micro virtual machine which is one of the shapes that are included in the free tier. The shape includes 1 OCPU (1 CPU with hyperthreading, so two virtual CPUs) and 1GiB of memory. It will also set everything up for you including the network with ssh access.

To be able to ssh to the instance, you need to add the public ssh key for your ssh key pair. If you do not already have an ssh key, then https://docs.oracle.com/en/cloud/paas/event-hub-cloud/admin-guide/generate-ssh-key-pair-using-puttygen.html has an example of creating one on Microsoft Windows.

Once you click create, a workflow is created and launched. While the workflow is running, the icon in the top left corner is yellow/orange to indicate that the instance is being worked on. Once the workflow has completed, the instance is available and the icon turns green.

You will need the Public IP Address which you can find in the Primary VNIC Information section when viewing the instance details:

With that and your ssh key, you can connect to the instance using the opc user, for example (this assumes you have the private key in OpenSSH format):

shell$ ssh -i ~/.ssh/id_rsa opc@<ip address of vm>

The first step to install MySQL is to install the MySQL yum repository:

[opc@mysql ~]$ sudo yum install https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpmLoaded plugins: langpacks, ulninfo                                                                  
mysql80-community-release-el7-3.noarch.rpm                                    |  25 kB  00:00:00     
Examining /var/tmp/yum-root-4Yk8Ev/mysql80-community-release-el7-3.noarch.rpm: mysql80-community-release-el7-3.noarch                                                                                     
Marking /var/tmp/yum-root-4Yk8Ev/mysql80-community-release-el7-3.noarch.rpm to be installed          
Resolving Dependencies                                                                               
--> Running transaction check                                                                        
---> Package mysql80-community-release.noarch 0:el7-3 will be installed                              
--> Finished Dependency Resolution

Dependencies Resolved

=====================================================================================================
 Package                      Arch      Version     Repository                                  Size
=====================================================================================================
Installing:
 mysql80-community-release    noarch    el7-3       /mysql80-community-release-el7-3.noarch     31 k

Transaction Summary
=====================================================================================================
Install  1 Package

Total size: 31 k
Installed size: 31 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mysql80-community-release-el7-3.noarch                                            1/1
  Verifying  : mysql80-community-release-el7-3.noarch                                            1/1

Installed:
  mysql80-community-release.noarch 0:el7-3

Complete!

Now you can install any of the MySQL products using the yum command. For example, to install MySQL Server, the MySQL client programs, and MySQL Shell:

[opc@mysql ~]$ sudo yum install mysql-community-server mysql-community-client mysql-shell
Loaded plugins: langpacks, ulninfo                                                       
Resolving Dependencies                                                                   
--> Running transaction check                                                            
---> Package mysql-community-client.x86_64 0:8.0.17-1.el7 will be installed              
--> Processing Dependency: mysql-community-libs(x86-64) >= 8.0.11 for package: mysql-community-client-8.0.17-1.el7.x86_64                                                                                 
---> Package mysql-community-server.x86_64 0:8.0.17-1.el7 will be installed                          
--> Processing Dependency: mysql-community-common(x86-64) = 8.0.17-1.el7 for package: mysql-community-server-8.0.17-1.el7.x86_64                                                                          
---> Package mysql-shell.x86_64 0:8.0.17-1.el7 will be installed                                     
--> Running transaction check                                                                        
---> Package mariadb-libs.x86_64 1:5.5.64-1.el7 will be obsoleted                                    
--> Processing Dependency: libmysqlclient.so.18()(64bit) for package: 2:postfix-2.10.1-7.el7.x86_64  
--> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: 2:postfix-2.10.1-7.el7.x86_64                                                                                      
---> Package mysql-community-common.x86_64 0:8.0.17-1.el7 will be installed                          
---> Package mysql-community-libs.x86_64 0:8.0.17-1.el7 will be obsoleting                           
--> Running transaction check                                                                        
---> Package mysql-community-libs-compat.x86_64 0:8.0.17-1.el7 will be obsoleting                    
--> Finished Dependency Resolution                                                                   

Dependencies Resolved

=====================================================================================================
 Package                           Arch         Version            Repository                   Size 
=====================================================================================================
Installing:                                                                                          
 mysql-community-client            x86_64       8.0.17-1.el7       mysql80-community            32 M 
 mysql-community-libs              x86_64       8.0.17-1.el7       mysql80-community           3.0 M 
     replacing  mariadb-libs.x86_64 1:5.5.64-1.el7                                                   
 mysql-community-libs-compat       x86_64       8.0.17-1.el7       mysql80-community           2.1 M 
     replacing  mariadb-libs.x86_64 1:5.5.64-1.el7                                                   
 mysql-community-server            x86_64       8.0.17-1.el7       mysql80-community           415 M 
 mysql-shell                       x86_64       8.0.17-1.el7       mysql-tools-community        15 M 
Installing for dependencies:                                                                         
 mysql-community-common            x86_64       8.0.17-1.el7       mysql80-community           589 k 

Transaction Summary
=====================================================================================================
Install  5 Packages (+1 Dependent package)                                                           

Total download size: 468 M
Is this ok [y/d/N]: y     
Downloading packages:     
warning: /var/cache/yum/x86_64/7Server/mysql80-community/packages/mysql-community-common-8.0.17-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY                                    
Public key for mysql-community-common-8.0.17-1.el7.x86_64.rpm is not installed                       
(1/6): mysql-community-common-8.0.17-1.el7.x86_64.rpm                         | 589 kB  00:00:00     
(2/6): mysql-community-libs-8.0.17-1.el7.x86_64.rpm                           | 3.0 MB  00:00:01     
(3/6): mysql-community-libs-compat-8.0.17-1.el7.x86_64.rpm                    | 2.1 MB  00:00:00     
Public key for mysql-shell-8.0.17-1.el7.x86_64.rpm is not installed] 5.0 MB/s |  44 MB  00:01:25 ETA 
(4/6): mysql-shell-8.0.17-1.el7.x86_64.rpm                                    |  15 MB  00:00:06     
(5/6): mysql-community-client-8.0.17-1.el7.x86_64.rpm                         |  32 MB  00:00:13     
(6/6): mysql-community-server-8.0.17-1.el7.x86_64.rpm                         | 415 MB  00:01:29     
-----------------------------------------------------------------------------------------------------
Total                                                                5.1 MB/s | 468 MB  00:01:31     
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql                                        
Importing GPG key 0x5072E1F5:                                                                        
 Userid     : "MySQL Release Engineering <mysql-build@oss.oracle.com>"                               
 Fingerprint: a4a9 4068 76fc bd3c 4567 70c8 8c71 8d3b 5072 e1f5                                      
 Package    : mysql80-community-release-el7-3.noarch (@/mysql80-community-release-el7-3.noarch)      
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql                                                     
Is this ok [y/N]: y                                                                                  
Running transaction check                                                                            
Running transaction test                                                                             
Transaction test succeeded
Running transaction
  Installing : mysql-community-common-8.0.17-1.el7.x86_64                                        1/7
  Installing : mysql-community-libs-8.0.17-1.el7.x86_64                                          2/7
  Installing : mysql-community-client-8.0.17-1.el7.x86_64                                        3/7
  Installing : mysql-community-server-8.0.17-1.el7.x86_64                                        4/7
  Installing : mysql-community-libs-compat-8.0.17-1.el7.x86_64                                   5/7
  Installing : mysql-shell-8.0.17-1.el7.x86_64                                                   6/7
  Erasing    : 1:mariadb-libs-5.5.64-1.el7.x86_64                                                7/7
  Verifying  : mysql-community-libs-8.0.17-1.el7.x86_64                                          1/7
  Verifying  : mysql-community-server-8.0.17-1.el7.x86_64                                        2/7
  Verifying  : mysql-community-common-8.0.17-1.el7.x86_64                                        3/7
  Verifying  : mysql-community-client-8.0.17-1.el7.x86_64                                        4/7
  Verifying  : mysql-shell-8.0.17-1.el7.x86_64                                                   5/7
  Verifying  : mysql-community-libs-compat-8.0.17-1.el7.x86_64                                   6/7
  Verifying  : 1:mariadb-libs-5.5.64-1.el7.x86_64                                                7/7

Installed:
  mysql-community-client.x86_64 0:8.0.17-1.el7        mysql-community-libs.x86_64 0:8.0.17-1.el7
  mysql-community-libs-compat.x86_64 0:8.0.17-1.el7   mysql-community-server.x86_64 0:8.0.17-1.el7
  mysql-shell.x86_64 0:8.0.17-1.el7

Dependency Installed:
  mysql-community-common.x86_64 0:8.0.17-1.el7

Replaced:
  mariadb-libs.x86_64 1:5.5.64-1.el7

Complete!

There are some dependencies that are pulled in and existing libraries are upgraded.

That is it. All that remains is to start MySQL and set the root password. You start MySQL through systemd like:

[opc@mysql ~]$ sudo systemctl start mysqld

Since it is the first time MySQL is started, the data directory (/var/lib/mysql) is initialized and the root account is created with a random password. You can find the random password in the error log:

[opc@mysql ~]$ sudo grep password /var/log/mysqld.log
2019-09-18T09:59:55.552745Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: i(Y5Nkko>S.t

The password you see will of course be different. Use the password the first time you authenticate, then use the ALTER USER statement to set the new password. For example, using MySQL Shell:

[opc@mysql ~]$ mysqlsh --user=root --sql
Please provide the password for 'root@localhost': ************
Save password for 'root@localhost'? [Y]es/[N]o/Ne[v]er (default No): No
MySQL Shell 8.0.17

Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'root@localhost'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 10 (X protocol)
Server version: 8.0.17 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  localhost:33060+ ssl  SQL > ALTER USER CURRENT_USER() IDENTIFIED BY 'New$secureP@ssw0rd';
Query OK, 0 rows affected (0.0061 sec)

Information

The password validation component is installed by default when installing MySQL using RPMs. This means that the password must be at least eight characters long and include at least one lower case, one upper case, one digit, and one special character.

You are now ready to use MySQL. Have fun.

Slides and Workbooks From Oracle OpenWorld & CodeOne

First of all, thanks to everyone who attended my sessions at the recent Oracle OpenWorld and Code One in San Francisco. It was a great privilege to be allowed to make a number of presentations.

All of the workbooks and scripts from the hands-on labs (HOL) and the slides from the talks have been made available at OpenWorld’s and Code One’s session catalogues. You can download the files by using the OpenWorld catalog searching for my name or the session number (see below). Click on the download icon for each of the presentations you are interested in.

Click on the download link in the Oracle OpenWorld session catalog to download the presentations.
Click on the download icon in the Oracle OpenWorld session catalog to download the presentations.

For the hands-on labs the downloadable file includes the workbook as well as the scripts related to the exercises. The workbook contains the instructions for setting up the system used for the exercises as well as the exercises themselves and some additionaly background information. For the talks, the download consists of a PDF version of the slides.

The three hands-on labs and three talks I had were:

  • DEV5957: Develop Python Applications with MySQL Connector/Python
    This covered MySQL Connector/Python in general from installation to best practices. The talk focused on using the connector with SQL tables using both SQL statements and NoSQL CRUD methods. If you are interested in how I installed MySQL Connector/Python on iPad (the screen shot on in the right hand side of slide showing the pip command), see my previous blog about installing MySQL Connector/Python in Pythonista 3.
  • DEV5959: Python and the MySQL Document Store
    This covered how to use MySQL Connector/Python (and a little of MySQL Shell in Python mode) with the MySQL JSON Document Store using the NoSQL API (the X DevAPI).
  • HOL1703: A Practical Introduction to the MySQL Document Store
    This hands-on lab used the MySQL Shell in Python mode to use the MySQL Document Store including an introduction to the CRUD methods. The lab also includes a comparison of implementing the same X DevAPI program using Python, Node.js, and Java.
  • HOL1706: Developing Modern Applications with the MySQL Document Store and NodeJS
    This lab is similar to HOL1703 except it mainly uses Node.js programs to use the MySQL Document Store.
  • HOL2986: Using MySQL Common Table Expressions and Window Functions
    An introduction to the new MySQL 8.0 query features: common table expressions (CTEs) and the window functions that can be used for analytic queries.
  • THT6703: NoSQL + SQL = MySQL
    A brief introduction to MySQL, MySQL 8, and how you can use it both as a NoSQL document store and a traditional SQL database.

Enjoy.

Meet MySQL Support at Oracle OpenWorld and Code One 2018

Oracle MySQL Support will this year again take part in the Oracle OpenWorld conference in San Francisco. Additionally, we will present at the developer focused Code One that is held at the same time. Oracle OpenWorld and Code One 2018 takes place at the Moscone Center and nearby hotels in San Francisco on Monday 22 October to Thursday 25 October.

Oracle OpenWorld and Code One 2018

MySQL Support will be represented by Lig Isler-turmelle and myself (Jesper Wisborg Krogh), and we will organize three hands-on labs (HOL) in Oracle OpenWorld, two talks in Code One, and two mini-briefings at the Support Stars Bar. The hands-on lab and Code One talks are:

TimeSpeaker(s)SessionTitle
Monday
22 October
12:15 pm - 13:15 pm
Lig Isler-turmelle
Jesper Wisborg Krogh
HOL1703A Practical Introduction to the MySQL Document Store
Tuesday
23 October
11:15 am - 12:15pm
Lig Isler-turmelle
Jesper Wisborg Krogh
HOL1706Developing Modern Applications with the MySQL Document Store and NodeJS
Wednesday
24 October
09:00 am - 09:45 am
Jesper Wisborg KroghDEV5957Develop Python Application with MySQL Connector/Python
Wednesday
24 October
02:30 pm - 03:15 am
Jesper Wisborg KroghDEV5959Python and the MySQL Document Store
Thursday
25 October
09:00 am - 10:00 am
Lig Isler-turmelle
Jesper Wisborg Krogh
HOL2986Using MySQL Common Table Expressions and Window Functions

The HOL sessions are in Salon 9A at the Mariott Marquis on the Yearba Buena Level and the DEV sessions are the Code One talks and will be in Room 2014 at Moscone West.

Additionally, we will be present at the Support Stars Bar. This is an area full of people from Oracle Support. Throughout the four days there will be 15-minute mini-briefings. Attendees to the mini-briefings can enter a drawing with a chance to win a prize. MySQL Support will have two mini-briefings (the schedule is still to be finalized). The Support Stars Bar will be located at level 3 in Moscone West. You can read more about the Support Stars Bar at the Oracle Support homepage.

There are of course many more talks both by Oracle developers, product management, as well as MySQL users. Registration is open, and I will encourage you to register and come and meet the people behind MySQL and hear about the new features and how MySQL is used. You can read more about MySQL’s general presence in LeFred’s blog and see a list of all the MySQL sessions at both OpenWorld and Code One in the Code One session catalogue.

Register for Oracle OpenWorld 2018