Skip to main content

Working with MySQL/MariaDB

Hands-On Lab

 

Photo of Michael McClaren

Michael McClaren

Linux Training Architect I in Content

Length

01:00:00

Difficulty

Intermediate

In this hands-on lab, you will install Mysql/MariaDB on a CentOS 7 server. Once that is complete, you will be required to configure the server with the provided data.

What are Hands-On Labs?

Hands-On Labs are scenario-based learning environments where learners can practice without consequences. Don't compromise a system or waste money on expensive downloads. Practice real-world skills without the real-world risk, no assembly required.

Working with MySQL/MariaDB

Introduction

In this hands-on lab, you will install Mysql/MariaDB on a CentOS 7 server. Once that is complete, you will be required to configure the server with the provided data.

Solution

  1. Begin by logging in to the lab server using the credentials provided on the hands-on lab page:

    ssh cloud_user@PUBLIC_IP_ADDRESS

Install MariaDB/MySQL

  1. Install using the official repo:

    sudo yum install -y mariadb-server
    sudo systmctl start mariadb
    sudo systemctl enable mariadb
    sudo mysql_secure_installation
    • Enter current password for root: Press Enter (no password)
    • Set root password: y
    • New password: Omgpassword!
    • Re-enter new password: Omgpassword!
    • Answer Y for the remaining four prompts

Create Required Resources

  1. Log in to the server:

    mysql -u root -p
    • The password is from the previous step, "Omgpassword!"
  2. Then, create the database and the table:

    create database city_data;
    use city_data
    create table city_info(
    LatD int,
    LatM int,
    LatS int,
    NS char,
    LonD int,
    LonM int,
    LonS int,
    EW char,
    City varchar(50),
    State varchar(50)
    ) ;
    exit

Bulk Insert and Validate the Data

  1. Copy the cities.csv file into the mysql user's home directory:

    sudo cp ./data/cities.csv /var/lib/mysql
  2. Perform the bulk insert:

    mysql -u root -p
    • The password is "Omgpassword!"
    use city_data
    LOAD DATA INFILE '/var/lib/mysql/cities.csv' INTO table city_info Fields terminated by ',' ENclosed by '"' Lines terminated by 'n' Ignore 1 ROWS;
  3. Validate the data (the following command should return a count of 3):

    select count(city) from city_info where state='KS';

Conclusion

Congratulations — you've completed this hands-on lab!