How to create and Drop Role in MySQL Server - MySQL DBA Tutorial

What is Role in MySQL, How to create Role and How to drop Role in MySQL Server

Roles in MySQL Server really helps to group the permissions to single object ( Role) and then you can assign role to user instead of you create each user individually and then grant permissions.

Syntax:

MySQL> Create ROLE ROLE_Name;

Example : 
Let's say we want to create Select role with  name "Role_Select" and then Grant Show databases and Select permission on all the tables in TechBrothers Database. Once the role is created we would like to assign user TB and TB1 to it.

MySQL> Create Role Role_Select; 

Grant Permissions to Role " Role_Select"

MySQL> GRANT SHOW DATABASES ON *.* to Role_Select;

MariaDB > GRANT ALL ON TechBrothers.* to Role_Select; 

Assign  Role_Select  role to user TB.

MySQL>  GRANT Role_Select To 'TB'@'localhost';

Grant Role_Select to user TB1.

MySQL>  GRANT Role_Select To 'TB1'@'localhost'; 

Now once the user TB or TB1 will login to MySQL Server, they can use this role. Once the user login , they can check which role they are using by using below statement.

MySQL> Select Current_Role; 

First time you are going to get below output.

+--------------+
| Current_Role |
+--------------+
| NULL         |
+--------------+
1 row in set (0.00 sec)

If user TB or TB1 will try to select the data from TechBrothers database or run show databases statement, they will get permission denied error. They have to set the role first. Below statement can be used to set the role.

MySQL>  set Role Role_Select()

Now they should be able to use all the object on wich Role_Select has permission.
User can also set the default Role by using below statement so he/she does not have to set the role every time login. Root or DBA will be setting the default role for user.

MySQL> set default role Role_Select;

How to Drop Role in MySQL Server :

In MySQL server, you can use below statement to drop the role.

Syntax:

MySQL> Drop Role RoleName;

Example: 
Let's say if we have a role with name "Role_Select" and we want to drop, we can use below statement.

MySQL > Drop Role Role_Select;

How to change Root password in MySQL Server - MySQL DBA Tutorial

Change password for root user in MySQL Server

Scenario:

You are working as MySQL Server DBA and you need you change the password for root. How would you do that?

Solution:

You can use mysqladmin command to change the password for root user. use below statement to change the password

shell > sudo mysqladmin -u root -p password mynewpassword

Once you will hit enter, you will be asked to provide old password for root. Provide the password for root and hit Enter. 

You are all set to login to MySQL Server by using new root password. Use below command to login to MySQL Server.


shell> mysql -u root -p
provide new password, in my case it was mynewpassword. 

MySQL Server Tutorial - How to reset root user password in MySQL installed on Windows

MySQL Training for beginners - How to change root user password for MySQL Server on Linux

How to Check permissions of user / login in MySQL Server - MySQL DBA Tutorial

How to check what permissions user / login has in MySQL Server

Once you are login to MySQL Server, you can run below statement to see the permission of currently login user account.

MySQL > Show grants;

If you want to check the permissions of other users, you can do as long as you have the permissions to do by using below statement.

MySQL > SHOW GRANTS FOR 'UserName'@'HostName'; 

MySQL complete tutorial for beginners - How to check privileges of user in MySQL Server

How to Change Password of a user in MySQL Server - MySQL DBA Tutorial

How to change password for a user/login in MySQL Server

To reset the password in MySQL for user , below statement can be used.

Syntax:

MySQL > SET PASSWORD FOR 'UserName'@'HostName' = 'NewPassword'

Example : 
Let's say that we have user TB and we want to change the password to 'Pass123#' , below statement can be used in MySQL Server to reset the password.

MySQL > SET PASSWORD FOR 'TB'@'localhost' = 'Pass123#';

you can also user statement to change the password in MySQL Server

MySQL > alter user 'username'@'HostName' identified by 'Password'   

MySQL Tutorial for beginners - How to change or reset password for user in MySQL Server

How to Revoke Permissions for user in MySQL server - MySQL DBA Tutorial

How to Revoke Permissions for user/login in MySQL Server 

To Revoke permissions of a user account in MySQL Server, below syntax can be used.

Syntax:

MySQL > REVOKE PermissionType ON Object To 'UserName'@'Host';

Example: 
Let's say we have user TB and we have provided Delete Permission on all the Tables in TechBrothers Database and we would like to Revoke.We can use below statement.

MySQL > REVOKE DELETE ON TechBrothers.* FROM 'TB'@'localhost';

MySQL Training for beginners - How to remove permission for MySQL user

How to Rename user in MySQL Server - MySQL DBA Tutorial

Rename User/Login in MySQL Server

Renaming User account in MySQL Server is very easy. Use below statement to rename user in MySQL.

Syntax:

MySQL> RENAME USER 'Old_User_Name'@'host' TO 'New_User_Name'@'Host';

Example:
Let's say that we have user 'TB' and we would like to Rename to 'TechBrothers' , we can use below statement.

MySQL > RENAME USER 'TB'@'localhost' TO 'TechBrothers'@'localhost';

MySQL Tutorial for Beginners - How to rename user in MySQL Server

How to give permission to User to Grant permissions to Other Users in MySQL Server - MySQL DBA Tutorial

How to use With Grant Option in MySQL Server

If you want the user to grant privileges to other users in MySQL Server, you can use WITH GRANT OPTION. The user will be only able to GRANT the permissions he/she has.

Syntax:

MySQL > GRANT Permission ON Object/s to 'USER'@'Host' WITH GRANT OPTION;

Example : 
Let's say that if we are providing SELECT permission to TB USER on TechBrothers Database. We want to provide permission to TB so he can provide SELECT permission to other user if want. When we will be providing SELECT permission to TB, we will use WITH GRANT OPTION.

MySQL > GRANT Select ON TechBrothers.* to 'TB'@'localhost' WITH GRANT OPTION;

MySQL tutorial for beginners - How to use With GRANT OPTION in MySQL Server to provide privileges to User

Grant permission to individual fields ( Columns) in MySQL - MySQL DBA Tutorial

How to provide permission on limited columns in a table in MySQL Database 

In MySQL Server you can provide permission on column level. 

SELECT: 
You can provide select permission on specific columns of a table. The user will be only able to select the data on which he/she has permission. User will not be able to use the column in WHERE clause, On, Group by and in Oder by clause if does not have permission.
Syntax to provide Select Permission on Columns:

MySQL > GRANT SELECT (Column1,Column2,....)  ON TABLENAME To 'UserName'@'Host'

UPDATE:
The user will be only update values in columns on which he/she has the permission.

MySQL > GRANT UPDATE (Column1,Column2,....)  ON TABLENAME To 'UserName'@'Host'

INSERT : 
Insert permission can be provided on column level. The Null/default value will be inserted in columns on which user don't have permission.

MySQL > GRANT INSERT (Column1,Column2,....)  ON TABLENAME To 'UserName'@'Host'

Example:

Let's create employee table with below definition and provide Select permission on FName and LName to user TB.

CREATE TABLE employee 
  ( 
     id    INT, 
     fname VARCHAR(100), 
     lname VARCHAR(100), 
     age   SMALLINT, 
     ssn   CHAR(10) 
  ); 

insert some sample data

INSERT INTO employee 
VALUES     (1, 
            'Aamir', 
            'Shahzad', 
            35, 
            '000-000-00'); 

INSERT INTO employee 
VALUES     (2, 
            'Raza', 
            'M', 
            30, 
            '000-000-00');

Provide SELECT PERMISSION to user TB on FName and LName columns.

MySQL > GRANT Select (FName,LName) on employee to 'TB'@'localhost';

To provide UPDATE PERMISSION ON Age column to TB. we can use below statement.

MySQL > GRANT UPDATE (Age) on employee to 'TB'@'localhost';

To provide INSERT PERMISSION on id, FName,LName only, we can use below statement.

MySQL > GRANT INSERT (Id,FName,LName) on employee to 'TB'@'localhost';

MySQL Tutorial for beginners  - How to provide Column level permission in MySQL Server to user

How to Grant Update, Insert and Delete Permissions on a Table in MySQL Database - MySQL DBA Tutorial

How to provide Update, Delete and Insert Permissions to User on Table or Tables in MySQL Database

Below Syntax can be used to provide UPDATE, Delete and Insert permission on table/s in MySQL .

Syntax:

If you would like to provide update, delete and insert permission on single table in MySQL database, you can use below statement

MySQL > GRANT select, update, insert, delete ON tableName to 'UserName'@'Host' 

On all the tables in a database

 MySQL > GRANT select, update, insert, delete ON DatabaseName.* to 'UserName'@'Host' 

Example :
Let's say that if we have table "test" in Techbrothers Database and we would like to provide Update permission to user "Aamir" in MySQL. we can use below statement

MySQL > GRANT select, update, insert, delete ON Techbrothers.test to 'Aamir'@'localhost' 

If we would like to provide UPDATE,DELETE and INSERT permissions on all the tables in Techbrothers Database to user account Aamir in MySQL, we can use below statement

MySQL > GRANT select, update, insert, delete ON Techbrothers.* to 'Aamir'@'localhost' 

How to grant select, update, insert and delete permission to user in MySQL Server

How to provide Select permission to limited tables in MySQL Database - MySQL DBA Tutorial

How to provide Select permissions to user on limited Tables in MySQL Database 

Below Syntax can be used to provide select permission on table/s in MySQL.

Syntax:

To provide Select permission on Single Table in MYSQL

MySQL > GRANT select ON tableName to 'UserName'@'Host' 

If you would like to provide select permission on all the tables in a database

 MySQL > GRANT select ON DatabaseName.* to 'UserName'@'Host' 

Example :
Let's say that if we have table test in Techbrothers Database and we would like to provide Select permission to user "Aamir" in MySQL. we can use below statement

MySQL > GRANT select ON Techbrothers.test to 'Aamir'@'localhost' 

If we want to provide Select permission on all the tables in Techbrothers Database to user account Aamir in MySQL, we can use below statement

MySQL > GRANT select ON Techbrothers.* to 'Aamir'@'localhost' 

Grant Select privilege to Table or Tables in MySQL server to user

How to provide full permissions on a database to user in MySQL Server - MySQL DBA Tutorial

How to provide full permissions to user in MySQL Database

To provide full permissions on a Database to user in MySQL Server, you will be using below syntax.

Syntax:

MySQL > GRANT ALL ON DatabaseName.* TO 'UserName'@'Host' WITH GRANT OPTION;

WITH GRANT OPTION, user will be able to provide permissions to other users on the database. If you don't want that then remove the "With GRANT OPTION" from above statement.

Example:

 
Let's create user TB and provide full permissions on Database name "TechBrothers" , We also want the TB user to have permissions to GRANT permissions on "TechBrothers" databases to other users.

MySQL > Create User 'TB'@'localhost' Identified by "Password123";

Grant full permissions on TechBrothers Databases to user account TB.

MySQL > GRANT ALL ON TechBrothers.* TO 'TB'@'localhost' With GRANT OPTION; 

How to Grant all permissions to user in MySQL Server - MySQL DBA Tutorial

How to Grant all permissions to user in MySQL Server 

If you would like to provide all permissions like root user to user account in MySQL Server, you can use below command.
First of all let's create a user by using below 

Syntax:

MySQL > Create User 'UserName'@'localhost';

To provide all permission including with GRANT , below syntax can be use

MySQL > GRANT ALL ON *.* To 'UserName'@'HostName'  With GRANT OPTION;

Example:
Let's say that you would like to create user Aamir and provide him all the permissions.

MySQL > Create user 'Aamir'@'localhost' Identified by 'Password'; 

 MySQL > GRANT ALL ON *.* to 'Aamir'@'localhost' With Grant Option;

Grant all privileges to user in MySQL Server by using MySQL Command line

How to Drop User in MySQL Server - MySQL DBA Tutorial

Drop User in MySQL Server

To drop user in MySQL Server , you can use below statement

MySQL > Drop User 'UserName'@'HostName';

Let's say if you would like to drop user Aamir that you have created in Localhost, you can use below statement

MySQL > Drop user 'Aamir'@'Localhost'; 

MySQL Training - How to drop user in MySQL Server from MySQL command line

How to create user in MySQL Server - MySQL DBA Tutorial

How to create User in MySQL Server

To create user in MySQL , you can use mysql command line or we can use Graphical tools such as MySQL Workbench. 

Syntax:

MySQL > Create User 'UserName'@'Server' IDENTIFIED BY 'YourPassword';

Let's say if you need to create user Aamir with password =Aamir$Password, you will be using below statement.

MySQL > Create user 'Aamir'@'localhost' Identified by ' Aamir$Password';

Notice that when I created the user , I provided @'localhost' that means the user Aamir can access MySQL server only from the same machine where MySQL server is installed. 

In case , the user would like to connect to MySQL server from another machine, we have to provide the ip of that machine. Let's say if the ip of machine from which user Aamir would like to connect to MySQL is 10.0.0.5 then our create statement will look below.

MySQL > Create user 'Aamir'@'10.0.0.5' Identified by ' Aamir$Password';                          

To check if the user has been created successfully, we can run below statement in MySQL.

MySQL > Select host, user  from mysql.user; 

How to create user in MySQL Server on Windows and on Linux

How to take a backup of a database by using mysqldump in MySQL Server - MySQL DBA Training

How to take a backup of a database by using mysqldump in MySQL Server

demo explains

• How to create new directory for MySQL Backups
• How to change the ownership of MySQL backup directory
• How to create the backup user that you will use to take backup of MySQL Databases
• Execute mysqldump on linux shell to take backup of a database

you will use below syntax to run the backup of a database in MySQL Server

# mysqldump --user=UserName --password=PasswordforUser DbName >/DestinationDirectory/BackupFileName.sql

How to take backup of a database by using mysqldump for MySQL Database

MySQL DBA Training - How to restore single database from complete MySQL database dump

Scenario:

You are working as MySQL DBA,  You are provided a complete MySQL or MariaDB database dump but you need to restore only one database out of entire database dump. What steps you will take to restore single database from complete MySQL database Dump.

Solution:

To create the single database dump file you can use below command.

# mysqldump --user=UserName --password=PasswordforUser --all-databases >/DestinationDirectory/BackupFileName.sql

To Restore single database from complete backup dump, you can use below command.

# mysql --user=[UserName] --password=[Password] --one-database destdbname < alldatabases.sql

You need to create the empty database first before you run the restore database command. Let's say we need to restore abc database, then you can use below command.

# mysql --user=UserName --password=ProvidePassword --one-database abc < /mysqlbackup/alldatabases_backupfile.sql

MySQL DBA Training - Restore single database from Complete mysqldump file

MySQL DBA Tutorial - How to Restore Database from backup in MySQL Server

Scenario:

You are working as MySQL DBA and you have taken the full backup of a database by using mysqldump utility, Now you need to restore that backup to database, how would you do that?

Solution:

To take full backup of a database in MySQL  , below mysqldump command can be used.

shell > mysqldump --user=YourUserName --password=PasswordfortheUser DatabaseName >/DestinationDirectory/BackupFileName.sql

Let's say if you have a database name testdb and you would like to take full backup, you can use below command

shell > mysqldump --user=root --password=DBA123 testdb >/mysqlbackup/testdb.sql

To Restore, you will be using below command. Make sure you have created empty database first , otherwise you will get an error.

shell > mysql --user=UserName --password=PasswordforUser DatabaseName < /SourceDirectoryforBackup/BackupFile.sql

Let's say if we would like to restore testdb, I can use below statement.

shell > mysql --user=root --password=DBA123 testdb < /mysqlbackup/testdb.sql

MySQL DBA Tutorial - Restore Database from mysqldump file

MySQL DBA Tutorial - How to restore single table from full database backup in MySQL Server

Scenario:

You have used mysqldump to take the full backup of a database. The database has multiple tables. Now you need to restore only single table from full backup in MySQL Server, how would you restore single table in MySQL Server?

Solution:

First of all you will be separating the data for deleted table from full backup. You can set program to extract the data for the table you want to restore. The below command will extract the data for table and create new file. Once we have the data for single table, then we will restore.

shell > sed -n -e '/DROP TABLE.*`YourTableName`/,/UNLOCK TABLES/p' /SourceDirection/FullbackupName.sql > Extracted_YourTableData.sql

Let's say our table name is employee, then we will modify above as shown below.

shell > sed -n -e '/DROP TABLE.*`employee`/,/UNLOCK TABLES/p' /mysqlbackup/testdb.sql > Employee.sql 

Now you are ready to restore employee table to testdb.

shell > sudo mysql --user=root --password=DBA123 testdb< /mysqlbackup/Employee.sql

MysQL DBA Tutorial - Restore single table from full database backup in MySQL

MySQL DBA Training - How to restore database from zipped backups in MySQL Server by using mysqldump

Scenario: 

You or other MySQL DBAs have created zipped backups of databases by using mysqldump. You need to restore the zipped backups to MySQL Server, Which command you will use to restore zipped backup to MySQL Server?

Solution:

The below command can be used to generate zipped backups for databases by using mysqldump if you have generate before.

shell > mysqldump --user=[uname] --password=[pass] [dbname] | gzip -9 > [backupfile.sql.gz]

For example if you have testdb and you would like to run the compress backup, your command will look like below.

shell >  mysqldump --user=UserName --password=ProvidePassword testdb | gzip -9 > /mysqlbackup/testdb.sql.gz

Restore Zipped Backups to MySQL Server:

Below command can be used to restore zipped backup to MySQL Server.

shell > gunzip < backupfile.sql.gz | mysql --user=[uname] --password=[pass] [dbname]

Let's say you are restoring the testdb that we took above. Your command will look like below.

shell > gunzip < /mysqlbackup/testdb.sql.gz | mysql --user=backupuser  --password=ProvidePassword testdb

In case when you have taken backup of all databases to single backup file and you would like to restore all of them, you can use below command. 

shell > gunzip < /mysqlbackup/testdb.sql.gz | mysql --user=backupuser  --password=ProvidePassword 

Notice that, you don't have to provide the database name in above command if you want to restore all databases from file with all databases backup.

MySQL DBA Tutorial - Restore Zipped backup in MySQL Server

MySQL DBA Training - How to backup all MySQL databases to single backup file by using mysqldump

Scenario:

You are working as MySQL DBA and you need to take backup of all MySQL Databases to single backup file, how will you take backup of all databases without zip and with zip by using mysqldump.

Solution: 

You can use below script to take backup of all databases to single backup file by using mysqldump. In below example we are not zipping the backup file.

mysqldump --user=ProvideUser --password=ProvidePassword --all-databases >/destinationdirectory/AllDatabases.sql

Example: 

In below example we are using "backupuser" to take backup of all databases in single file.

mysqldump --user=backupuser --password='MyPassword$123' --all-databases >/mysqlbackup/AllDatabases.sql

To take the backup in gznip, you can use command like below

mysqldump --user=backupuser --password='MyPassword$123' --all-databases | gzip >/mysqlbackup/AllDatabases.sql.gz

Notice that if you will zip the backup file the size will be smaller and it can help if you need to move across network to restore on another MySQL Server.

Backup all databases to single backup file by using mysqldump - MySQL Training