MySQL is one of the famous and most efficient Database management systems.
WordPress and many other content management systems use MySQL to manage their databases. Even Wikipedia uses MySQL as their database management system.
In this article, I will show you how you can manage MySQL database users and databases directly using ServerAvatar.
First, we have to learn how MySQL deals with Database and Database users. If you know this part, you will never face issues with Authentication and Authorisation in MySQL.
In this article, We are first going to learn how MySQL works with Databases and Users. If you don’t want to know that part, you can just skip the first part and get started with the second one. Here are the topics we are going to cover in this in-depth article on MySQL Databases and MySQL users management.
- How MySQL deals with databases and users.
- How to create a new Database using ServerAvatar
- How to create a new MySQL user for a specific database
- How to create a new MySQL user with Remote access
So, without wasting any more time in the introduction of MySQL, let’s dive into the first topic of this article. As I told you before, the first topic of this article is optional and you can read it if you want to know how actually MySQL works with MySQL databases and MySQL users.
Interesting Read: A complete guide on setting up a MySQL server using ServerAvatar.
How MySQL deals with databases and users
I will try to simplify this topic as much as possible to make sure you can understand even if you don’t have prior experience with MySQL.
First of all, How do we authenticate users on our website? Through username and password, right? It’s the same with the MySQL but there is one more thing in case of MySQL. It’s the user host.
The host can be the IP address of the server from which the MySQL server is accessible. While creating a MySQL user, we have to define the user host too along with username and password, It’s mandatory.
Let’s say we have created a user with username
password123, and host
126.96.36.199. It means that a user can access the MySQL server from the machine having IP address
188.8.131.52 with correct username and password only.
In case we have a MySQL server and Web server in the same machine, We have to use
localhost as a user host so that a specific user can access the MySQL server from the same machine.
It means that if we have different servers to host our web application and our database, we can use the IP address of our web server as a host for our MySQL user that can access the database of the application.
Initially, A specific MySQL user does not have any rights on any database. We have to grant the rights to a specific MySQL user on a specific database. There are many privileges that we can grant like select, insert, update and delete.
Okay, So let’s assume that we have a database named
database123 and we already have a user with username
example123. So, we can grant all/specific privileges on
database123 to the user
example123 and then we can use our database information to connect our application to our database or access our database using PHPMyAdmin or command line.
If you want to allow access from any host (Which is not at all recommended), you can set host as
% which is wildcard character for MySQL. It will allow access from all the IP addresses in the world.
That’s how authentication and authorization work in case of MySQL. In short and sweet sentence, A user has
host as a factor of authentication along with username and password. And to allow a user to access the database, we can grant privileges to the user for a database.
Now, Let’s see how we can create a new MySQL database using ServerAvatar.
How to create a new Database using ServerAvatar
There are many ways to create a MySQL database using ServerAvatar, we are going to use the simplest way. First of all, Click on the Server Panel button corresponding to the server on which you want to create a database.
On the Server Panel, you will find a Database card with two links. Click on the Create link on the Databases card.
Now, you will be redirected to the database section of the server panel and you will find a model with the bunch of fields. It will ask you the database name and a few other options.
Now, Enter the name of the database you want to create and click on the Create Database button. After submitting the information, Your database will be ready instantly! And the good thing is, ServerAvatar will automatically create a MySQL user and will grant all privileges on the database you have created. You don’t have to worry about attaching MySQL users with MySQL databases like you do in case of shared hosting.
ServerAvatar will set a random username and password for the MySQL user that has all the privileges on the database and you will get information in E-mail just like the following image.
Now, you can use this information in your PHP application and there are no chances you will get Database connection errors!
I know, You might want to ask me a question. What if I want to set Username and Password on my own? In that case, Check the first option and enter your desired username and password! That’s all.
Yes, It’s very easy! But here is the fun fact! You can create as many database users as you want for any specific MySQL database.
Let’s say I want to give temporary access to a specific MySQL database to a freelancer. I won’t risk giving the credentials that my application is using right now to access MySQL database, right? In that case, we can create another MySQL user with rights to specific MySQL database. Once the work is done, we can delete the extra MySQL user. That’s what we will learn in the next section.
How to create a new MySQL user for a specific database
Let’s assume my MySQL database needs editing and I have hired a freelancer to get things done on the Database side. In that case, we will just make a new MySQL user with all the rights on a specific MySQL database and provide him/her the database credentials.
Once the work is done, we can just remove the extra MySQL user we have created for a freelancer and he/she won’t be able to access your database anymore! So, how can we create multiple MySQL users for a single database? It’s easy.
First of all, Click on the Databases section from the left sidebar or click on the List option given on the Databases card on the server panel. You will get a list of the available databases with some information just like the following image.
Here you can see we have 3 databases and the 3rd one is the one we created right now. As you can see the number of Database users for the database testDatabase is 1. It is because ServerAvatar will create one MySQL user for all the databases.
To create a new MySQL user for any database, Click on the Users button corresponding to the database for which you want to create a MySQL user. You will get a list of MySQL users having all privileges on the database just like the following image.
After clicking on Add user button, you will see a modal with two fields and one checkbox just like the following image.
Just enter the username and password in the fields respectively and click on the Create user button. That’s all you have to do to create a new MySQL user using ServerAvatar. This user will have all the privileges on the database for which you have created the user, no need to attach user with the database.
Now, we can give access to a freelancer and once the work is done, we can delete the extra user that we have created! Now, let’s see what we can do if we have different servers for database and web files.
How to create a new MySQL user with Remote access
This is the feature that very fewer people require. It is mostly used to connect to the different database server. Let’s say your site is so big that you can’t handle it on a single server or you just want to handle MySQL queries in MySQL server and Web traffic on a web server.
In that case, you must have remote access to your MySQL database server because your website files are hosted on a web server with different IP address. In that case, you can create a MySQL user with remote access using ServerAvatar.
The method to create MySQL user with remote access is same. The only thing that is different is you have to select the Remote access option while creating the database or a database user and you have to enter the IP address of the server from which you want to access the database.
Once your MySQL database/user is created with the remote access, you can access your database from the IP address you have mentioned in the remote access field.
If you have turned on the firewall, rules will be applied automatically to support changes. So, this is how you can allow remote access to your MySQL database. If you want to create a WordPress website with the remote database, You don’t have to do it manually because ServerAvatar supports it out of the box! Click here to know how.
So, this is how you can manage your MySQL databases and MySQL users using ServerAvatar. Let us know in the comment section if you need help regarding anything, we will help you set up your MySQL databases and users.