Manage users in MySQL server

This is the advanced user tutorial for MySQL server,  also known as MySQL Administration tutorial. In this tutorial I will explain you how to manage user in MySQL.

Managing user means creating, deleting, assigning permission, revoking permission/privilege. Before going to detail I recommend you, to first see the structure of user table under mysql database.

Just open your mysql client and type

use mysql;

desc user;

here we showing some field of user data base

Now we have to understand the two terms define below, more of us already know these terms.

Authentication : Means system know about you (user). system have some kind of information about you.

MySQL uses username, password and hostname or host-ip for authentication of user

Autherization : Permission to do any operation using your authentication detail.

Add a new user in MySQL server

Use the following SQL command for adding new user to your MySQL DB

CREATE USER 'ganga'@'localhost' IDENTIFIED BY 'password';

you need administrative privileges for this.

This command will create a user ganga and password is password.

This command simply create a user without any permission. you can do nothing.

if you login to MySQL using this credentials(ganga, password) you only see information_schema database, all other databases hidden to you.

Now we provide privileges/permission to newly created user ganga.

Some privileges are database level and some are table level.

below is the table contains some MySQL privileges

List of privileges

Assigning privileges to a User

GRANT privilege1, privilege2, privilege ON database_name.*  TO ‘username’@’host’ IDENTIFIED BY ‘password’;

GRANT select ON college.*  to 'ganga'@'localhost' identified by 'password';

The following command provide select permission to user ganga in college database.

Deleting a User  

To delete a user permanently use the following command 

mysql>DROP USER ‘ganga’@’localhost’;

Removing all user privilege

To remove all user permission use the following command-

mysql>REVOKE ALL PRIVILEGES FROM ‘ganga’@’localhost’;

Changing user password

To change the password use the following command --

mysql>SET PASSWORD FOR ‘ganga’@’localhost’ = PASSWORD(‘newpassword’);

Renaming user

To rename the user use the following command --

mysql>RENAME USER ‘ganga’@’192.168.1.145’ TO ‘jamuna’@’localhost’;

Revoking privileges 

mysql>REVOKE DELETE, UPDATE FROM ‘ganga’@’localhost’;

MySQL backup and restore

As we know that MySQL store the data. Data is important for any organization. since there may be chance when our MySQL server crashes or the machine in which MySQL installed will crash.

If it would  happen we lost the data. So we must keep backup of our data regularly.

MySQL client provides different utility to backup the data and restore this backup data to new MySQL server.

There are different technique and modes for creating and restoring the backup. we are not going to see all techniques this time.

Here we are using mysqldump client utility that is provided by MySQL to create and restore the data.

The mysqlump utility is used for creating logical-backup (means the SQL statement that we used to create the database and table like insert, create) that necessary SQL statements that is used to restore the database again.

Syntax for using mysqldump

Case 1. shell> mysqldump [options] db_name [tablesname...] > filename.sql

Case 2. shell> mysqldump [options] --databases db_name > db.sql

Case 3. shell> mysqldump [options] --all-databases >dbs.sql

These are the three ways given above to take backup of MySQL server.

In Case 1 we can take the backup of data base as a set of particular tables, means we can take  backup of particular tables.

In Case 2 we can take the complete backup of one or more database.

In Case 3 we can take the complete backup of all database in the MySQL server.

Now we take an example to understand all the above syntax for taking  data base backup.

Case 1  suppose we have a "college database" that contains a table called student and we want to take backup this table only

shell >  mysqldump -u {username} -p college student >student.sql //enter

suppose now we want to take full backup of the college database

shell >  mysqldump -u {username} -p college >college.sql

Case2 suppose we have 3 database in our server college, shop, rent and we want to take the back up of shop and rent

shell >  mysqldump -u {username} -p --databases shop rent  > shop-rent.sql

Case 3 we want to backup all the database
shell > mysqldump -u {username} -p --all-databases   > all_db.sql

Restore backup data to MySQL

In this section I will tell you how to restore backup data that we have created using mysqldump client.

Here we are consider some real-time scenario to store the data.

case 1. You only backup a table instead of whole database and you want to restore this table on other machine for this use the following command

mysql -u username -p database_name < table_backup.sql

case 2. Suppose you have created the backup of full database in this case if you use --databases  (as in case 2 in backup section) to take  backup then backup file contain the create database statement and use database statement you don't need to create a database  and use the following command

mysql -u username  -p  < database_backup.sql

otherwise before exporting backup  you have to create the database and then you use the following command

mysql -u username -p database_name < database_backup.sql