How to install the MySQL database server and begin adding data

The MySQL database server is a powerful tool that allows developers to expand the offerings of web sites, applications, and more. In fact, if it weren’t for the likes of MySQL, web server software like Wordpress would have a lot of trouble functioning properly. Why? Because databases store crucial information those platforms depend on. And without the means to serve that database information up, there’d be no way for the software to use the data. That’s where database servers like MySQL come into play. MySQL is an open-source database server that can be installed and run on many platforms (such as Linux, Windows, and macOS). MySQL is a relational database, so it’s perfectly suited for apps like Wordpress. What is a relational database? Simply put, a relational database is a set of formally described tables that house data which can be accessed in different ways. The information contained within a relational database is easily compared because it is saved in columns and rows. But how do you install the MySQL database and how do you enter data into its tables? I’m going to walk you through that process. I’ll be demonstrating on the Ubuntu Server 18.04 platform. Although I’ll be using the command line, there are plenty of tools (such as phpMyAdmin) that make use of web-based or local client GUI applications for the process. Once you have your database up and running, with data inserted, that database can then be used for in-house or third-party applications. Although you’ll be learning how to add data via the command line, you shouldn’t have much problem with it. If you do, you can always hire one of many third party QA services like BairesDev to help you with the task.

What You’ll Need

The only things you’ll need to make this work are:

• A running instance of Ubuntu Server 18.04.
• A user account with sudo privileges.

With those things at the ready, let’s install the database server, create a database, and add data.

Installing MySQL

Believe it or not, the installation of MySQL is actually quite simple. Log into your Ubuntu Server instance and issue the command:

sudo apt-get install mysql-server -y

Once the server is installed, you’ll want to start the server with the command:

sudo systemctl start mysql

Finally, you’ll want to enable the MySQL daemon to start upon server boot (in case you ever have to reboot the server). This is done with the command:

sudo systemctl enable mysql

Securing the Installation

Before you can create that first database and add data, you must secure the MySQL installation. This allows you to do the following:

• Enable the Validate Password Plugin, which ensures that all passwords must adhere to strict password policies.
• Create a password for the MySQL admin user.
• Remove anonymous users.
• Disable root login.
• Remove the test database and access to it.
• Reload privilege tables.

To run the security tool, issue the command:

sudo mysql_secure_installation

If you want to enable the Validate Password Plugin, type Y and hit Enter on the keyboard (otherwise hit any key). Next, create your admin user password and then answer Y for the remaining questions. Once you finish that, you’re ready to continue on.

Creating a Database

Let’s say you work with an IT software outsourcing company and you need to create a database of clients you work with. We’ll call those database clients. In order to create the database, you must first log into the MySQL shell. To do that, issue the command:

sudo mysql -u root -p

You will first be prompted for your sudo password. Once you successfully enter that, type the admin user MySQL password you created when you secured the installation. You’ll know you’ve successfully logged into the MySQL shell when you see the command prompt change (Figure 1). Figure 1 The MySQL shell is ready for commands. Let’s create a database called clients. To do that, issue the command:

CREATE DATABASE clients;

In order to add data to the new database, you have to switch to it with the command:

USE clients;

You should see in MySQL report that you’ve switched databases (Figure 2). Figure 2 We’ve created and switched to the new database. Now it’s time to create a table in our new database. This is where the process requires a bit of thought. You need to know the information that will be stored in the table. Since this is clients, let’s say you want to store the clients:

• First and last name
• Business name
• Email address
• Phone number

If a piece of data will contain letters and numbers, you’ll most likely use the VARCHAR (variable character) type. If the data is only a number, you’ll use the INT (integer) type. For our list above, we could get by with using all VARCHAR types (even the phone number will have the - character). Say you want to create a table for clients from the USA, using the list of data information above. The command to create this would be:

CREATE TABLE usa (name VARCHAR(20), business VARCHAR(20), email VARCHAR(20), phone VARCHAR(20));

The numbers in parentheses denote the longest a string can be. If you know you’ll have entries that will require more than 20 characters, up that number. Type the command SHOW TABLES; to see the newly added table (Figure 3). Figure 3 Our newly-created table.

Adding Data

Now we’ll add data to our table. Say you want to add the following client: Name - Olivia Nightingale Business - Klockwerk Kabaret Email - olivia@kabaret.com Phone - 1-123-456-7890 The command for this would be:

INSERT INTO usa (name,business,email,phone) VALUES("Olivia Nightingale","Klockwerk Kabaret","olivia@kabaret.com","1-123-456-7890");

To make sure your data was successfully entered, type the command:

SELECT * FROM usa;

You should see the new entry listed (Figure 4). Figure 4 Our new entry has been successfully added. Once you’ve finished working on the database, type the exit command and you’re back at the Linux bash prompt.

Conclusion

And that’s all there is to installing MySQL, creating a database, and then adding data to the database. With this information, you should be able to create all kinds of databases for numerous use cases.