Steps to provision and configure MySQL HeatWave database service - nsojunior/mysql-general GitHub Wiki
Hello!
today we are going to launch a MySQL service in Oracle OCI.
in order to do that we need:
- OCI account
- Configure Virtual Cloud Networks using wizard
- Launch DB System
- Open ports 3306 and 33060 on private subnet's security list
- Launch a VM to access the database service
- Access VM
ssh -i <private-key-file> opc@<vm-ip>
- Install MySQL Shell
sudo yum -y install mysql-shell
- Access the server using MySQL Shell
mysqlsh admin@<mysql-ip>
- Run sql command
\sql show databases;
Bonus: Run MySQL HeatWave demo with a couple of queries:
-
use airportdb
USE airportdb;
-
Run explain - query 1 Find per-company average age of passengers from Switzerland, Italy and France
EXPLAIN SELECT airline.airlinename, AVG(datediff(departure,birthdate)/365.25) as avg_age, count(*) as nb_people FROM booking, flight, airline, passengerdetails WHERE booking.flight_id=flight.flight_id AND airline.airline_id=flight.airline_id AND booking.passenger_id=passengerdetails.passenger_id AND country IN ("SWITZERLAND", "FRANCE", "ITALY") GROUP BY airline.airlinename ORDER BY airline.airlinename, avg_age LIMIT 10\G
-
Run query
SELECT airline.airlinename, AVG(datediff(departure,birthdate)/365.25) as avg_age, count(*) as nb_people FROM booking, flight, airline, passengerdetails WHERE booking.flight_id=flight.flight_id AND airline.airline_id=flight.airline_id AND booking.passenger_id=passengerdetails.passenger_id AND country IN ("SWITZERLAND", "FRANCE", "ITALY") GROUP BY airline.airlinename ORDER BY airline.airlinename, avg_age LIMIT 10;
-
Disable HeatWave
SET SESSION use_secondary_engine=OFF;
-
Run query
SELECT airline.airlinename, AVG(datediff(departure,birthdate)/365.25) as avg_age, count(*) as nb_people FROM booking, flight, airline, passengerdetails WHERE booking.flight_id=flight.flight_id AND airline.airline_id=flight.airline_id AND booking.passenger_id=passengerdetails.passenger_id AND country IN ("SWITZERLAND", "FRANCE", "ITALY") GROUP BY airline.airlinename ORDER BY airline.airlinename, avg_age LIMIT 10;
-
check variable use_secondary_engine
SHOW VARIABLES LIKE 'use_secondary_engine%';
-
Set use_secondary_engine ON again
SET SESSION use_secondary_engine=ON;
-
Run query Query 2 - Find top 10 companies selling the biggest amount of tickets for planes taking off from US airports. Run Pricing Summary Report Query:
SELECT airline.airlinename, SUM(booking.price) as price_tickets, count(*) as nb_tickets FROM booking, flight, airline, airport_geo WHERE booking.flight_id=flight.flight_id AND airline.airline_id=flight.airline_id AND flight.from=airport_geo.airport_id AND airport_geo.country = "UNITED STATES" GROUP BY airline.airlinename ORDER BY nb_tickets desc, airline.airlinename LIMIT 10;
-
Disable use_secondary_engine
SET SESSION use_secondary_engine=OFF;
-
run query
SELECT airline.airlinename, SUM(booking.price) as price_tickets, count(*) as nb_tickets FROM booking, flight, airline, airport_geo WHERE booking.flight_id=flight.flight_id AND airline.airline_id=flight.airline_id AND flight.from=airport_geo.airport_id AND airport_geo.country = "UNITED STATES" GROUP BY airline.airlinename ORDER BY nb_tickets desc, airline.airlinename LIMIT 10;
Well done!