MySQL Database | How to Create It and More
Information has become a resource in this day and age. Whether you are a freelance worker or a business of any kind, you work with information daily. The MySQL Database is the second most popular Database of 2021. It has a value of 16.65%, according to Statistics and Data.
Everything is fine until the data starts to pile up. Soon, you find yourself lost in a ceaseless pool of data that keeps growing.
However, there is an easy way to process and manage large heaps of information. Namely, if you create a MySQL Database, your worries will disappear. It’s worth noting that the MySQL Database software offers many benefits and features, so its popularity comes as no surprise.
But, creating a MySQL Database can be challenging. Moving on, you can find out how to create a MySQL Database with a step-by-step explanation.
Let’s get started with, MySQL Database | How to Create It and More
How to Create a MySQL Database?
There are many different database based applications out there that will require a MySQL Database to be created prior to utilizing that software, as such it may seem that creating a MySQL Database is a daunting task, however let us assure you that it is a rather straightforward process. Whether it is through commandline/terminal or within a control panel, we’ll go over the different methods of creating a MySQL Database depending on the level of access that you have.
SSH/Terminal
If you are using Linux and creating the MySQL Database through the command-line interface, then creating a MySQL database is just the work of a few commands,
Login into MySQL
[root@knownhost ~]# mysql -u root
Create User (Ex: KHDBUser)
[root@knownhost ~]# GRANT ALL PRIVILEGES ON *.* TO 'KHDBUser'@'localhost' IDENTIFIED BY 'Le@adH()sT';
Login as Database User
[root@knownhost ~]# mysql -u KHDBUser -p
Create MySQL Database
[root@knownhost ~]# CREATE DATABASE KHDatabase;cPanel & DirectAdmin
For both control panels (cPanel & DirectAdmin), the steps to creating a MySQL Database are very close in similarity. A series of steps can be performed in both panels that are simple and easy to remember for the database creation process.
The first and foremost step in the creation process is to log in to your cPanel account. Once you’ve logged in, you need look for the Databases section. Then click MySQL Databases. You can spot the MySQL Databases icon easily and remember it because you will see it a lot.
The next step is to come up with the name of your Database if you haven’t already. Then, you type the name in the New Database field that will show on your screen. Finally, click Create Database.
After doing this, you have created your Database and are good to go. All you do now is click Go Back, and you can find your Database in the Current Databases section.
For DirectAdmin, Navigate to MySQL Management > Click on CREATE NEW DATABASE > Enter the database name > To create a new user for database > De-select (Same as database name) > Enter the Username > Configure the Password > Click on Create Database.
This is all we have covered in one article with step-by-step procedure and informative screenshots:
- For cPanel, read: How to create MySQL Database and User in cPanel?
- For DirectAdmin, read: How to create a MySQL Database in DirectAdmin?
Now that you have successfully created your Database, you’ll then need to create an assigned user. Next on our itinerary is creating a MySQL User. However, before we set out to find out how to make it, let’s see why you need it!
Why You Need a MySQL User?
The MySQL User is used for authentication and authorization against your database. First by verifying the user’s identity and then secondly verifying a users privileges. MySQL checks against the connecting users privileges to determine whether or not that user has the sufficient permission to do what it’s connecting to do.
MySQL Users gives you the ability to manage how and what that user does when connecting to your database.
- Creating MySQL Users with different privilege sets in accordance with that users work set requires.
- By setting users in lieu of root access you to limit compromised applications and prevent against critical mistakes during routine actions such as maintenance.
- Proper assignment of individual user privileges can help with ensuring data integrity
- Preventing users with incorrect privileges from accessing data they shouldn’t
- Allowing users with correct privileges to do necessary work.
MySQL Users provide a good way to gate keep what sort of activity goes on within your MySQL Database by providing you with greater control over what that user does.
Quick disclaimer: MySQL User accounts have to be made separately from web administrator and mail accounts!
How to create MySQL User?
Now that we’ve covered why you need a MySQL User to let’s see how to create it. The first two steps are the same as creating a MySQL Database for cPanel & DirectAdmin, but SSH commands are different.
SSH/Terminal
Again, if you need to create MySQL User using SSH, then follow the below commands,
Login into MySQL
[root@knownhost ~]# mysql -u root
Create User (Ex: KHDBUser)
[root@knownhost ~]# GRANT ALL PRIVILEGES ON *.* TO 'KHDBUser'@'localhost' IDENTIFIED BY 'Le@adH()sT';cPanel & DirectAdmin
Firstly, you log in to cPanel. Then you go to the Databases section and click on MySQL Databases. Now, at the top right corner of your screen, you will find a link named Jump to MySQL Users. You click on this link and scroll down to the MySQL User section. The next step is to enter a username under the Add New User section. Then you create a password in the Password section. Lastly, you click the Create User button.
After you click add, you choose what kind of privileges you want to give. The first option is to select All Privileges if you want to provide every privilege to the user. Or, if you want to give specific privileges, you can tick them in the section below All Privileges. Lastly, you click Make Changes and Go Back.
For DirectAdmin, you will be asked to create a user while creating the MySQL Database. So, there is nothing other option you can find for DirectAdmin to create MySQL users especially.
This is all we have covered in one article with step-by-step procedure and informative screenshots,
- For cPanel, read: How to create MySQL Database and User in cPanel?
- For DirectAdmin, read: How to create a MySQL Database in DirectAdmin?
Now we have completed the whole “How to create a MySQL Database” process. You can now use the Database to your liking. But, what if you no longer need a specific database or want to remove a user from it? Well, let’s see how you can delete a database and unassign a user in the next section!
How to Delete a MySQL Database?
For SSH/Terminal users, run the following command,
[root@knownhost ~]# mysql> DROP DATABASE KHDatabase;For cPanel, you repeat the first two steps. You log in to cPanel and click on MySQL Databases in the Databases section. Then you find the Current Databases section. In this section, all of your Databases are listed. Now, all you have to do is choose which one you want to delete. Once you do, press the Delete button next to the Database you wish to remove, and you are done.
For DirectAdmin, select the MySQL Management > Select MySQL Database > Click on Delete.
How to Unassign Users?
For SSH/Terminal users, run the following command,
[root@knownhost ~]# mysql> REVOKE ALL PRIVILEGES ON KHdatabase.* FROM 'KHDBUser'@'localhost';For cPanel, you go to the MySQL Databases > Current Databases section. Then you look for the Database that the user you want to remove is working on.
You will see a Privileged Users column from that Database. Next to the Privileged Users column is a trash can icon. Click the trash can, and you will unassign the user.
Conclusion
After going through the many creation processes, you are ready to use your MySQL Database. You can enjoy all of its features and benefits. And, whenever you forget a step or two, this article will always be here to remind you.
All in all, creating a MySQL Database with the steps that we described will be a piece of cake. Once you know what to do, anything is possible.
We also shared some of the Knowledge base articles links at the end of the section, and you can refer to those and get a very simplistic application of the process.
MySQL is open-source, free to use, and most importantly, it is a community-driven database server and widely used to store databases of websites, applications.
You might experience the difference in steps for different control panels, but both do the same job for you at the end of the day. So, whether if you are using DirectAdmin or cPanel, you might perform the same task through different steps due to its graphical user interface changes.
Let KnownHost handle MySQL for you!
Looking for solidMySQL Database Server? Contact our Sales Team at KnownHost for VPS Server Hosting or Dedicated VPS Servers inquiries and let us help you find the right server for your needs!