Skip to content

MySQL Setup Guide Final Project

Jump to bottom
shaniyur edited this page Apr 29, 2022 · 5 revisions

Follow this guide to set up your MySQL 8.0 environment for the Final Project of this course.

VM Creation:

  1. From the GCP console, click on Compute Engine and VM instances.
  2. Click on the CREATE INSTANCE button at the top of the page.
  3. In the name field, replace instance-1 with mysql-final-project. This will be the name of your instance.
  4. For the region field, choose us-central1 and us-central1-a for the zone.
  5. Under Series, select N1. For the machine type, choose n1-standard-8.
  6. In the boot disk section, change the boot disk type to SSD persistent disk and 50GB for its size.
  7. In the Identity and API access section, make sure the service account selected is Compute Engine default service account.
  8. In the Access scopes field, choose Allow full access to all Cloud APIs.
  9. In the Firewall section, check off Allow HTTP traffic and Allow HTTPS traffic.
  10. Click the CREATE button at the bottom of the page to create your VM instance.
  11. Once your VM has been created, click on SSH in the Connect field. This should bring up a new browser window with your SSH session.

MySQL Installation:

  1. Run the following commands in your SSH session:
  2. sudo apt update
  3. sudo apt -y install wget
  4. wget https://repo.mysql.com//mysql-apt-config_0.8.18-1_all.deb
  5. sudo dpkg -i mysql-apt-config_0.8.18-1_all.deb and choose debian buster if prompted to add a repository to an unsupported system.
  6. choose ok to accept the default MySQL components and product.
  7. sudo apt update. NOTE: If this causes an error, try running sudo apt update --allow-insecure-repositories instead.
  8. sudo apt -y install mysql-server and enter a root password of your choice when prompted. NOTE: If this causes an error, try running sudo apt -y install mysql-server --allow-unauthenticated
  9. If prompted, select ok to configure the mysql-community-server.
  10. When prompted, select Use Legacy Authentication Mode.
  11. Run sudo systemctl status mysql to verify your installation.

Expected output:
sudo systemctl status mysql
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Tue 2021-11-16 01:28:35 UTC; 1min 13s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 3182 ExecStartPre=/usr/share/mysql-8.0/mysql-systemd-start pre (code=exited,
Main PID: 3217 (mysqld)
Status: "Server is operational"
Tasks: 37 (limit: 4915)
Memory: 362.9M
CGroup: /system.slice/mysql.service
└─3217 /usr/sbin/mysqld
Nov 16 01:28:34 mysql-final-project systemd[1]: Starting MySQL Community Server...
Nov 16 01:28:35 mysql-final-project systemd[1]: Started MySQL Community Server.

MySQL Configuration

  1. Run mysql -u root -p and enter your password when prompted.
  2. Run the following commands in the mysql cli:
  3. CREATE USER 'root'@'%' IDENTIFIED BY 'your_password'; (replacing your_password with your actual root password)
  4. GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
  5. FLUSH PRIVILEGES;
  6. SELECT host FROM mysql.user WHERE user = 'root';

Expected output:
mysql> SELECT host FROM mysql.user WHERE user = 'root';
+-----------+
| host |
+-----------+
| % |
| localhost |
+-----------+
2 rows in set (0.00 sec)

  1. Close your ssh window and go back to the GCP console.
  2. In the VM instances screen, copy the internal IP address of your VM instance.
  3. Start up your JupyterLab instance and open a terminal window
  4. Open the .my.cnf file and edit the host entry by replacing the IP address with the IP address of your VM and update the password to the one you set in your ssh session.
  5. Run mysql in the terminal to connect to your MySQL instance from your JupyterLab notebook.

Expected output:
(base) jupyter@notebook:~$ mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]>

Your setup is complete. Remember to stop both your MySQL VM and JupyterLab instance when you are done with this setup.

Clone this wiki locally