Create User for dumping database MySQL

MySQL Database Server provides utility or tool which can be used to dump specific database exist in it. By default installation of MySQL Database Server, the tool provided for dumping a specific database is ‘mysqldump’.

This tool, the ‘mysqldump’ tool can be executed in the command line or terminal. Since this tool is actually trying to access and to connect to MySQL Database Server, before being able to dump the actual file from a specific database, commonly it needs user and password granted with the right privilege on the database which is becoming the dump target.

So, in order to begin the actual dumping process, create a user which has a specific privilege only for dumping the specific database by connecting f to MySQL Database Server. To be able to connect, check the service of MySQL Database Server is active or not. For further description on checking the status of MySQL Database Server, it is provided in the article titled ‘Check MySQL Service Status‘.

If MySQL Database Server’s service is alive, the dumping process can be done. First of all, in order to be able to dump a certain database, it need a certain user for dumping it. These are those steps :

1. Login to MySQL Database Server’s console

[user@hostname ~]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 137731
Server version: 5.6.27-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

1. After successfully logging in into MySQL command console, create the user which is going to be used for dumping backup purpose as shown below :

mysql> create user 'username'@'ip_address_of_the_host_connected_from' identified by 'password'
Description : create user .... from identified by is a query pattern to create user in MySQL Database Server
username : It is the name of the user which is used to connect to this MySQL Databasee Server 
ip_address_of_host_connected_from : It is the IP Address of the host, server or workstation used to connect to this MySQL Database Server 
password : It is the password of the user which is going to be used to dump database.

For an example :

mysql> create user 'backup'@'192.168.1.99' identified by 'mypass';

So, based on the query above, we are creating user named ‘backup’ whom can only connect to this MySQL Database Server from a server, host, workstation which have an IP Address of 192.168.1.99. To be able to connect using user ‘backup’ a certain password need to be inserted. And the associated password for the user ‘backup’ is ‘mypass’.

3. After successfully creating user, give the right or grant the sutaible privilege to the user which has already been created for dumping the specific database chosen.

mysql> grant select,lock tables on database_name.* to 'username'@'ip_address_of_the_host_connected_from' [identified by 'mypass'];
Description : 
grant select, lock tables on ... to .... identified by ....: It is a query pattern in MySQL to grant privilege specified on a specific database for the user defined. 
database_name.* : It is the name of the database which is going to be specified for access privilege to the user defined. The '*' sign means for every table inside of the database
username : It is the username which is used to connect to MySQL Database Server
password : It is the password associated with the user used to connect to MySQL Database Server
ip_address_of_the_host_connected_form : It is the IP Address of host, workstation or server used to connect from to this MySQL Database Server 
identified by 'mypass' : It is actually optional part of the query since in the creation process of user it has already been specified.

For an example :

mysql> grant select, lock tables on mydb.* to 'backup'@'192.168.1.99;

The above query executed to grant privilege to select and lock tables on database named mydb on every tables its had. The privilege is given to user named backup and it can only be used from host, server, workstation with the IP Address of 192.168.1.99.

But why it is only select and lock tables privileges needed to be able to dump from a certain database. Because when dumping activities executed it only involved selecting data from the database while locking all the tables from being manipulated, updated or changed so all the data retrieved all consistent.

After finishing executed the above query, the final query is also needed to be executed which is used to update the privilege :

mysql> flush privileges;

Then all the steps are done in order to be able to dump certain database with certain user from a certain host, workstation or server. To prove if it is working, try it from host, server or workstation with the IP Address of 192.168.1.99 or the IP used on the query definition executed before.