Creating a backup user with read-only permission for MySQL DB

Why do we need to create a user with read-only permission? Most people use “mysqldump” for taking the backup of MySQL. And when we automate the backup process the password of the user is saved in a file (Ex. script or mysql.inf ). If our backup user is a superuser and something went wrong in our backup script then we may face some data loss or corruption issue.

For taking a backup of mysql we must use a separate user who has read-only permissions. So that we can always back up our database with safety.

We will discuss two ways of creating the backup user with read-only permission. First with the use of MySQL command in the terminal and the second is from PhpMyAdmin GUI.

Create a user with Mysql Command

We all love to work in the terminal. It is always fun to work with commands. For creating a backup-only user use the below command.

> GRANT SELECT, LOCK TABLES ON *.* TO 'MYBACKUPUSER'@'%' IDENTIFIED BY 'MYPASSWORD';

In the above command, we are giving Select and Lock Tables permission on all of the present databases to our backup user.

You can limit it to the specific database. For this, you have to first create the user and then give the permissions to that user like this.

> CREATE USER 'MYBACKUPUSER'@'%' IDENTIFIED BY 'MYPASSWORD';
> GRANT SELECT, LOCK TABLES ON 'MYDATABASE'.* TO 'backup'@'%';

That’s it. Now you can use this user for taking the backup of your Mysql Databases.

Create a user with PhpMyAdmin GUI

If you are not familiar with the command line interface then you can also create this user using PhpMyAdmin GUI interface. PhpMyAdmin is the most used GUI interface for Administrating Mysql Databases.

You can download PhpMyAdmin from this Link: DOWNLOAD

So let’s Begin. First, Login to the PhpMyAdmin interface and go to the User Account tab and click on “Add user account“.

After that fill the required information. you can generate a secure random password for your user here but do not remember to copy it and save in some safe place.

No Click on “GO“.

Now in the User Account tab, you can see the newly created user. With the name which you have given in the last step.

Now click on “Edit privileges” and go to the DATABASE tab.

Here you have to select those databases whose backups are taken by this user. You can select multiple databases at once with Pressing CTRL key and clicking on the database. After selecting the database click on the GO button.

Now tick on SELECT and LOCK TABLES Options and click on GO button.

At the end click on “reload the privileges” link in the User Accounts tab.

That’s it, Your MySQL backup user with read-only permission is ready to take the backup of your MySQL databases.

Summary:

Mysql is the most used open-source relational database management system. Every single Server Administrator should know how to taking care of the databases in Mysql. Using this method you can back up your databases without any security issues.

You can share your views and suggestions on this article in the comment section. Happy Learning ..!!

Also Learn: “Backup all MySQL Databases with a MySQL Backup Script“