Monday, September 14, 2020

How to Launch MySQL Database Service ?

 Launching Your First MySQL DB System

Step 1 - Pre-requisites

1.    1.     Create a compartment and user group to organize the resources and access. In this example, we create a compartment named as MySQL_Sandbox, and a group MySQL_Test_Group. Log-in the Console as an Administrator, under the Identity menu, create a MySQL_Sandbox compartment, and a group MySQL_Test_Group. Add the OCI user you will use to work with MySQL Database Service to the group.

2.     Define the required MySQL Database Service policies (Identity menu). In our example, if your user is in the group MySQL_Test_Group and you want to restrict your MySQL DB Systems to the compartment MySQL_Sandbox, add the following policy statements:

a.      Policy statement 1:

Allow group MySQL_Test_Group to manage mysql-family in compartment MySQL_Sandbox

b.     Policy statement 2:

Allow group MySQL_Test_Group to {SUBNET_READ, SUBNET_ATTACH, SUBNET_DETACH, VCN_READ, COMPARTMENT_INSPECT} in compartment MySQL_Sandbox

3.     The following policy statement is also required for Console use if you would like to list tag namespaces when creating new DB System resources:

a.      Policy statement 3:

Allow group MySQL_Test_Group to inspect tag-namespaces in tenancy

C   create a Virtual Cloud Network (VCN). Just click on the "Start VCN Wizard" button, choose "VCN with Internet Connectivity", give a name, confirm the compartment, keep the default CIDR blocks (10.0.0.0/16, 10.0.0.0/24, 10.0.1.0/24) and DNS Resolution options, then click the buttons "Next", and finally "Create".

Step 2 - Create a MySQL DB System

1.     Using the Console, select the menu Database > MySQL > DB Systems > Create MySQL DB System.


1.  Inform your DB System preferences, ensuring you choose the compartment where your VCN and Subnets are configured from the pre-requisites. Give your DB System a name, select the Availability Domain and Fault Domain you want to place your DB System. Then you can choose a configuration, as the VM.Standard.E2.1.Built-in, define the Storage Size and click the "Next" button.

   


1.     You will now provide the Database information: MySQL Administrator credentials, and confirm where the network information, and click "Next". 

1.     Finally, just confirm if you want to enable Automatic Backups and click "Create".

1.     Your new MySQL DB System will be ready to use after a few minutes. You can perform the following Step 3 in parallel while waiting.

1.     The state Creating will change to Active, indicating that your DB System is ready to use. When the DB System is Active, click on the left "Endpoints" resources and check the Address to connect to the DB System.



Step 3 - Configure the client Virtual Machine to connect to MySQL

You will need a client machine to connect to your brand new MySQL database. If you still don't have a Linux VM in the same VCN mentioned in the pre-requisites, you can create one right now. To launch a Linux Compute instance, you go to the Console, menu Compute, Instances, and click "Create Instance". Ensure you choose the same VCN from the pre-requisites, and it is in a Public Subnet with a public IP address assigned

1.     Configure the Private Subnet to accept traffic on MySQL port(s). In the Console, got to Networking, Virtual Cloud Networks, <your VCN name>, Security Lists, Security List for Private Subnet, and add an Ingress Rule with Source CIDR 0.0.0.0/0 to Destination Port 3306. (Optional) Add another Ingress

Rule to port 33060 if you want to use MySQL Document Store.


1.     Connect to the Compute instance using SSH. For example, using the Terminal:

$ ssh -i ~/.ssh/id_rsa opc@<your_compute_instance_ip>

2.     You will need a MySQL client tool to connect to your new MySQL DB System from your client machine. For example, to install MySQL Shell just run:

3.  sudo su root

4.  sudo yum update

5.  sudo wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

6.  rpm -Uhv mysql80-community-release-el7-3.noarch.rpm

7.  yum update

8.  yum install mysql

9.  yum install mysql-shell

Step 4 - Connect to MySQL

In case you wanted to connect with Putty