Instructor: Hey, folks.
In this lecture, we will dive into the world of databases
by exploring two of the most powerful and popular databases:
MySQL and MariaDB, or Maria Database.
Now, I am including this lecture
because a system administrator is always involved
in the installation, management, and troubleshooting
of databases in Linux environment.
All right, the first thing to know
about MySQL and Maria Database
is that they are almost identical.
If you learn one, you have practically learned the other one
without any extra effort
because they shared the same foundation.
So mastering either one
gives you a solid understanding of both.
Now, you might be wondering,
if MySQL and MariaDB are almost identical,
which one should we choose and why?
Don't worry, it's a simple question with clear answers.
We'll break down the key differences
so you can easily decide which database fits your needs.
The main key difference is that MySQL
is owned by Oracle Corporation, a big company;
and on the other hand,
MariaDB was created by the original MySQL developers
who were concerned that MySQL
might become less open and community-friendly
under Oracle's ownership.
So to keep it open source and free,
they started MariaDB Corporation,
a nonprofit organization dedicated to its development.
So MariaDB is like a copy of MySQL,
but with a stronger focus on being open to everyone.
Then if I talk about the key similarities,
both work almost the same way
and use the same credential.
My applications that work with MySQL
can also work with MariaDB without changes.
Now let's focus on one of them.
For this lecture, we will dive into MariaDB
and explore how it works.
Once we have learned about MariaDB,
we will see how this knowledge
can easily help us understand MySQL as well.
Now, please note we are not getting into
running the SQL statements
and managing the database deep into the weeds;
we are just learning how to install and manage
on Linux environment.
All right, let's get started with MariaDB.
According to mariadb.com,
MariaDB is an open-source,
multithreaded relational database management system
released under the GNU Public License, in short, GPL.
MariaDB's lead developer is Michael "Monty" Widenius,
who is one of the founders of MySQL AB.
Let's make it simpler.
MariaDB is a free open-source database software
created by the original MySQL developers
to ensure it remains open and accessible.
It stores data in tables with rows and columns,
similar to a spreadsheet,
making it easy to manage
and retrieve large amounts of information.
MariaDB supports SQL,
which is a short of Structured Query Language
for creating, reading, updating, and deleting data,
while also offering advanced features
like stored procedures, triggers, and views.
Furthermore, it supports technologies like JSON,
NoSQL-style queries, and big data integration,
making it versatile for various applications.
Now, let's quickly go over its history.
In 1995, MySQL was created by a Swedish company, MySQL AB,
founded by Michael Widenius,
David Axmark, and Allan Larsson.
I apologize if I am pronouncing it wrong.
In 2008, Sun Microsystems acquired MySQL AB
for approximately $1 billion.
Then in 2009, Michael Widenius forked MySQL
to create MariaDB, founded under its new company,
MariaDB Corporation.
In 2010, Oracle acquired Sun Microsystems,
and hence MySQL came under Oracle ownership.
In 2013, MariaDB replaced MySQL
as a default database in many Linux distributions
like Fedora and Red Hat Enterprise Linux.
So, as of today, MariaDB Corporation continues to grow,
focusing on enterprise solutions
and cloud-based database services.
Let's get back to the main point now.
From this point, I will first go through the steps
to install, configure, and manage the database.
So start with step one.
We will verify and install MariaDB server and client,
enable MariaDB to start at boot and start MariaDB service.
Finally, we will check the current status of MariaDB service
to ensure it's running properly.
Okay, then step two, we will secure MariaDB installation.
In step three, we will connect to the MySQL server
as a root user and create a new database
called first_MariaDB.
A new user iafzal is created with the password 123,
and full privileges on the first_MariaDB
are granted to this user.
Finally, we will ensure that the server
reloads the updated privileges settings,
and the list of available databases
is displayed before exiting.
Step four, we will switch to first_MariaDB database
and create a table named people with different columns,
insert data for three individuals.
After that, we will retrieve and display all records
from the people table to verify the entries.
Finally, we will update and delete data in the MariaDB.
Then step five, uninstall MariaDB
to prepare for MySQL installation.
Finally, in step six, install and set up MySQL,
then create insert, update, and delete records in MySQL.
Before any further delay, let's move on to practice.
So I have already opened my party session here.
The first step you always do is to check who you are
by typing whoami command.
So I am root.
Okay, which directory I'm in?
I am in root directory. Okay.
And the hostname is MyFirstLinuxVM.
Let's check the IP to confirm we are in the right system.
For me, in my case, it is the one that ends with 59.
All right, perfect.
Let's check if these packages that we need to install
are installed already.
So for that, run rpm -qa command.
And you can use grep for whatever packages
you wanted to check for.
In our case, we will do MySQL first and then MariaDB.
Okay. Nope, both are not installed.
Okay.
And now I will follow the detailed procedure
that I have documented earlier.
I will open it up on the side
so that we can follow these steps one by one.
All these steps are clearly highlighted.
And as you are going through it with me,
I will also encourage you to open this document
and follow along.
Let's start with step one.
Install and start MariaDB.
Let's install MariaDB
by typing dnf install mariadb-server mariadb,
and add -y at the end to avoid
any proceeding requests,
and press Enter.
Let's wait until the installation is complete.
Okay, it's all done.
Now it's time to start the MariaDB service
by typing systemctl start mariadb
and press Enter.
All right, the service has successfully started. Perfect.
Now let's enable MariaDB to run automatically at boot
with a systemctl enable MariaDB command.
After that, let's check the status.
You should now see MariaDB services are running.
Okay, move on to step two: Secure MariaDB installation.
Let's enhance the security of your MariaDB installation
by setting a strong root password.
For this, simply run the command mysql_secure_installation
and hit Enter to get started.
Here you can notice that the first word
of the command is mysql.
Why? Because MariaDB is a fork of MySQL.
And they share many of the same commands and utilities.
Let's move on and enter
the current root password when prompted.
Next, when asked about Unix socket authentication,
press n to continue.
You will then be asked
if you want to change the root password.
press y and set a new password for MariaDB.
Reenter the password to confirm.
For all other prompts, such as removing anonymous users,
disallowing remote login or root login,
and removing test databases,
simply press n to skip them.
Now it's all done,
and your MariaDB installation is now secure.
All right, now let's move on to step three:
Create database and the user in MariaDB.
Let's log in to the MariaDB server as root user
by typing command mysql -u root -p.
After executing the command,
you can see it's a prompt
to enter the root user's password for access.
This allows you to perform administrative task
and manage databases within the MariaDB environment.
All right, now you are in the MariaDB environment.
All right, first, create a new database
named first_MariaDB on your MariaDB server.
Now, please note here one thing:
this is not usually required for you,
as a system administrator, to create databases
or insert records or anything for that matter.
At this point, you actually hand over
to database administrators and they take it from there.
But for learning purposes,
I'm including that as to creating the database, okay?
So when we run it, it has successfully created.
Next, create a new user named iafzal on your local machine,
which is localhost, and assign them to the password 123.
This user will be able to interact with database
based on the permissions granted to them.
Next, grant the user iafzal full privileges on all tables
within the first_MariaDB database,
ensuring that iafzal can perform any necessary operations
within this specific database.
Now, reload the privileges tables to make sure
that any changes to user's permission
take effect immediately
without needing to restart the server.
All right, finally display a list of all databases
currently available on the server to confirm
that the first_MariaDB has been successfully created.
Let's exit the MariaDB shell,
ending your current session with the server.
Now, the necessary settings are completed.
It's time for some action on this database.
Let's move on to step four:
Create table and insert data in MariaDB.
First, log in to MariaDB server.
You can see the SQL commands in the document,
so let's use these commands.
Of course, I encourage you to run these commands
so you'll have somewhat experience
of how to access the data from a database.
All right, before doing this,
let's clear the screen by pressing the shortcut Ctrl + L.
Now, let's select our newly created database.
Next, let's create a table named people.
Copy the SQL query,
paste it here and Enter.
A table named people is now created
in the current database.
The table includes three columns:
id, which is an integer and serves as a primary key
with auto-increment enabled,
meaning it will automatically generate unique values
for each entry;
name, which stores a person name
with a maximum of 100 characters;
and profession, which stores a person's profession
with a maximum of 100 characters.
This table structure allows for organized storage
of people details.
Next, let's insert some data.
Copy this INSERT query, paste it, and Enter.
Now it inserts three records into the people table.
It adds the name and profession of each person:
Alice, whose profession is engineer;
Bob, whose profession is doctor;
and Charlie, whose profession is teacher.
These entries are now stored in the people table,
each with unique ID generated automatically.
Let's verify this by fetching the data
from the people table using the SELECT query.
You can now see these records displayed in table form.
Move on and update the profession
of the person named Charlie in the people table.
Now it's successfully changed Charlie's profession
from teacher to principal.
Finally, let's delete the record
for the person named Bob from the people table.
Now, all information related to Bob,
including his name and profession,
has been removed from the database.
We have successfully completed the full set
of CRUD operation,
which is short for create, read, update,
and delete on MariaDB.
These operations are the foundation
of all database management,
allowing us to easily handle and manipulate data.
After that, let's exit the MariaDB session.
Now, let's see how this knowledge of MariaDB
can easily help us understand MySQL too.
Since MariaDB is a fork of MySQL
and shares many similarities,
the skills and commands you have learned here
can be applied to MySQL with ease.
All right, let's move on to step five: Uninstall MariaDB.
If you already have MariaDB installed
but want to install MySQL,
you will need to remove MariaDB first,
as they both conflict with each other
due to shared component and ports.
All right, let's start by stopping the MariaDB service
running on our system.
All right, next, let's uninstall
both the MariaDB and mariadb-server packages
using the dnf package manager.
Although the software is removed,
some configuration files or data directories might remain.
So let's go ahead and permanently delete
the entire MariaDB data directory,
which includes all databases, logs, and files.
Finally, remove the my cnf configuration file
that holds all the server settings.
Now everything's clean and fully removed. Beautiful.
Now you have experience
of deleting and removing database as well.
All right, let's move on to step six:
Install and set up MySQL.
Now, it's time to install MySQL server
by typing dnf install mysql-server;
and of course add -y at the end
to avoid any questions that pop up.
Okay, now go ahead and hit Enter.
Let's wait until the installation is complete.
Now it's all done.
Okay, now it's time to start MySQL service
by typing systemctl start mysql and press Enter.
Yes, remember, every service that we install
on our Linux system
is managed, or most of the services are managed
by systemctl local native utility.
All right, so the service has been successfully started.
Perfect.
Next, let's enable MySQL to run automatically at boot
with the command systemctl enable mysql.
After that, let's check the status.
You should now see MySQL services are running.
Okay.
Now get ready and fasten your seatbelt,
because the commands you have learned from MariaDB
would work just the same for MySQL.
Whether you are creating databases, managing users,
or performing C-R-U-D, CRUD operations,
everything is nearly identical.
So, let's dive in and explore
how easily your MariaDB skills transfer over to MySQL.
(subtle bright music)
Welcome back.
As you can see, working with MySQL and MariaDB
is like using two sides of the same coin.
Almost every command and process is identical,
making it the easy to switch between the two.
Whether you are managing data, creating users,
or running queries, you'll feel right at home with both.
Their shared foundation means once you have mastered one,
you have successfully mastered the other one.
And that's the end of our session, folks.
Now it's your turn to explore,
practice with MySQL and MariaDB by creating databases,
managing users, and running queries.
The more you experiment,
the better you will understand
how seamlessly these two system work.
Dive in and discover the power of MySQL and MariaDB,
and I'll see you in the next lecture.