So you're new to MySQL database administration. You've probably already installed MySQL (or MariaDB) and know how to log into your database prompt for work. You're looking to use the MySQL database for your web-based tool, but can't connect that tool to an empty database.
The MySQL command line can be a bit daunting at first, but once you get the hang of it, it'll become second nature.
SEE: Server deployment/migration checklist (Tech Pro Research)
I want to walk you through the following:
- Creating a database
- Creating a user
- Granting the new user access to the database
- Creating a table on the database
- Adding data to the table
It might sound like a lot, but it's not. In fact, it's actually quite simple. Let's get to work. I'll work with MySQL on Ubuntu Server 18.04.
Creating a database
The first thing to do is to create a database. I'm going to demonstrate by creating a database called TECHREPUBLIC. First gain access to the MySQL prompt with the command:
Once you're on the MySQL prompt, create the new database with the command:
create database TECHREPUBLIC;
Create a USER
Now we're going to create a new user (named tr) and give that user permission to the new database. This is done with the command:
CREATE USER 'tr'@'localhost identified by 'PASSWORD';
where PASSWORD is a strong password.
Next, we give tr permission to use the TECHREPUBLIC database with the command:
GRANT ALL PRIVILEGES ON TECHREPUBLIC.* TO 'tr'@'localhost' identified by 'PASSWORD';
where PASSWORD is the password created for the user in the previous command.
Now we need to change to that database with the command:
Create a table
Let's create a table called MEMBERS2 with three columns:
The command for this will be:
CREATE TABLE IF NOT EXISTS MEMBERS2 ( task_id INT AUTO_INCREMENT, firstname VARCHAR(50), lastname VARCHAR(50), email VARCHAR(50), PRIMARY KEY (task_id) );
Add Data to the Table
Now we'll add the following data to the MEMBERS2 table:
This is done with the following command:
INSERT INTO MEMBERS2(firstname,lastname,email) VALUES ('jack','wallen','firstname.lastname@example.org');
We can now view the inserted data with the command:
SELECT * FROM MEMBERS2;
You should see the data listed (Figure A).
Continue adding data to the table in the same fashion.
And that's all there is to creating a table and inserting data into it on MySQL. Once you have all the data necessary, your database is ready for usage by your web-based tool. Just remember to connect the tool using the MySQL we created earlier, and you're good to go.
- How to export and import MySQL databases (TechRepublic)
- How to install MySQL on CentOS 7 (TechRepublic)
- How to install MariaDB database server on Ubuntu 18.04 (TechRepublic)
- How to install MyWebSQL on Ubuntu 16.04 (TechRepublic)
- The rise of Kubernetes epitomizes the transition from big data to flexible data (ZDNet)
- What is SDN? How software-defined networking changed everything (ZDNet)
- The Best VPN services for 2018 (CNET)
Jack Wallen is an award-winning writer for TechRepublic and Linux.com. He’s an avid promoter of open source and the voice of The Android Expert. For more news about Jack Wallen, visit his website jackwallen.com.