Database Administrator PART-1

Introduction

Welcome to the Database Administration and SQL Language Basics Blog! In this blog, we will be using MySQL to learn about administering a database and the basics of the SQL language. The first half of the blog begins with installing a MySQL server, then covers common administrative tasks such as creating databases and tables, inserting and viewing data, and running backups for recovery.

We will also cover the different data types that are allowed in MySQL, and wrap up the administrative side by discussing user access and privileges.

The second half of the blog focuses on using the SQL language to view and manipulate data. This will include creating and deleting various database objects, adding and altering data within tables, and viewing and sorting data. After completing this blog, i will have hands-on experience in administering a database and know how to use the SQL language to manage it.

MySQL Installation on Ubuntu

I’ve already logged into my cloud host, if you would like to follow step by step. I’ll go ahead and give you some instructions on how you can set that up. So the first thing you need to do is go over to Remote SSH Cloud Development. I have 2 CPU and 4 gigs of RAM - Cloud Computing in Alibaba Cloud.

Terminal

# we're goint to run sudo -i
sudo -i

# we need to update our password
root@iZj6c5g5ritynfyjcZ:~# chage -l root

# Output:
# root@iZj6c5g5ritynfyjcZ:~# chage -l root
# Last password change                                    : Jan 25, 2024
# Password expires                                        : never
# Password inactive                                       : never
# Account expires                                         : never
# Minimum number of days between password change          : 0
# Maximum number of days between password change          : 99999
# Number of days of warning before password expires       : 7

root@iZj6c5guawmd5ritynfyjcZ:~# passwd

# Output
# New password:
# Retype new password:
# passwd: password updated successfully

Installation Steps

01 — Download the MySQL APT Repository configuration file from the MySQL APT Repository download page. shell:Terminal wget https://dev.mysql.com/get/mysql-apt-config_0.8.29-1_all.deb

02 — Install the downloaded release package for the MySQL server. In the package configuration window, ensure that MySQL Server and Cluster are set to mysql-8.0 and the MySQL Tools and connectors is Enabled then hit OK.

Terminal

sudo dpkg -i mysql-apt-config_0.8.29-1_all.deb

# resynchronize the package index files.
sudo apt update

03 — Install the MySQL server and dependencies.

Terminal

sudo apt install -y mysql-server

When prompted, enter a password, and the select. Use Strong Password Encryption (RECOMMENDED).

04 — Validate that MySQL server is running and then log in using the password created in step 3.

Terminal

ecs-user@iZj6c5guawmd5ritynfyjcZ:~$ 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 2024-02-06 17:48:13 CST; 7min ago
       Docs: man:mysqld(8)
             http://dev.mysql.com/doc/refman/en/using-systemd.html
   Main PID: 4115 (mysqld)
     Status: "Server is operational"
      Tasks: 37 (limit: 4631)
     Memory: 364.2M
     CGroup: /system.slice/mysql.service
             └─4115 /usr/sbin/mysqld

Feb 06 17:48:11 iZj6c5guawmd5ritynfyjcZ systemd[1]: Starting MySQL Community Server...
Feb 06 17:48:13 iZj6c5guawmd5ritynfyjcZ systemd[1]: Started MySQL Community Server.
$ mysql -u root -p
mysql> exit

05 — Secure Installation

Run the following comman to begin to lauch the secure installation:

Terminal

$ mysql_secure_installation

# Answer the following questions to complete the secure installation:
- When prompted about setting up the VALIDATE PASSWORD component, enter y for Yes.
- When asked about the level of password validation policy, enter 2 for STRONG.
- Change the password for root ? ((Press y|Y for Yes, any other key for No) : n
- Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
- Disallow root login remotely? (Press y|Y for Yes, any other key for No) : No
- Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
- Reload privilege tables now? y

We're finished here. We installed MySQL, and have it set up in a secure state, just like we were asked to do. Congratulations!

FYI: