We can establish the MySQL database using the mysql binary at the command prompt.

[root@host] # mysql -u root -p

Enter password: ******
This will give us the 'mysql>' command prompt where we will be able to execute any SQL
query.
We can disconnect from the MySQL database any time using the exit command at mysql>
prompt.
O/P mysql> exit
Bye

Set Password to MySQL Root

Usually, during the installation of MySQL server, we will be asked to set an initial password
to the root. Other than that, we can also set the initial password using the following command
−
mysql -u root password "new_password";
Where, new_password is the password set initially.

Reset Password
We can also change the existing password using the SET PASSWORD statement. However,
we can only do so after logging in to the user account using the existing password. Look at
the query below −
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('password_name');
FLUSH PRIVILEGES;
Every time a connection is needed to be established, this password must be entered.
Execute the FLUSH PRIVILEGES statement. This tells the server to reload the grant tables. If you
don't use it, then you won't be able to connect to MySQL using the new user account at least until the
server is rebooted.
MySQL Server is the program that mainly executes the SQL queries in the database system. Hence it
becomes necessary to optimize the working of this server. The general MySQL administration usually
includes concepts like:
 Starting and Stopping the Server
 User Security
 Database Maintenance
 Backup & Restore

1. Starting and Stopping the Server

 Starting the Server:

 The MySQL Server must be started before it can accept connections and queries.
 Depending on the operating system and installation method, you can start it using:
o On Windows (XAMPP/WAMP): through Control Panel (click "Start" on MySQL
module).
o On Windows cmd [net start mysql80/mysql51]
o On Linux: sudo systemctl start mysql or /etc/init.d/mysql start.
 Proper startup ensures the database engine loads configurations, initializes buffers, and makes
databases available.

 Stopping the Server:

 Before shutting down, it is important to stop MySQL gracefully to avoid corruption.

 Commands:
o On Linux: sudo systemctl stop mysql or mysqladmin -u root -p shutdown.
o On Windows: via XAMPP Control Panel or Windows Services Manager.
o On Windows: via cmd [net stop mysql80/mysql51]
 A clean shutdown flushes cached data to disk and closes connections properly.

We can also start the server by going through the services provided by the Windows and follow the
steps below −
 Open the 'Run' Window using the 'Windows+R' shortcut and run 'services.msc' through it.
 Then, select the "MySQL80" service click "start" to start the server.
 Now, if you want to pause, stop or restart an already running MySQL server, then you can do
it by opening the Windows Services and selecting the desired action
2. Users, Roles, and Privileges are the building blocks of security and access
control.

A. Users
 A user in MySQL represents an account that can connect to the database server.
 A user is identified by two things:
o Username (e.g., prasad)

o Host (where the user can connect from, e.g., localhost, % for any host, or a specific
IP).
Syntax: CREATE USER 'user_name'@'host_name' IDENTIFIED BY 'password';
 Example:

 CREATE USER 'sample'@'localhost' IDENTIFIED BY '123456';

Key Points:
 Each user may have a password for authentication.
 You can restrict a user to connect only from certain machines (e.g., 'user'@'192.168.1.100').
 Users by themselves don’t have any rights until privileges are granted.
You can verify the list of users using the following query

 SELECT USER FROM MySQL.USER;

The Expire Clause

If you use the expire clause, the old password (current password) will expire immediately and
the user need to choose new password at first connection.

 CREATE USER 'sample'@'localhost' IDENTIFIED BY 'MyPassword'

PASSWORD EXPIRE;
Now, if you log in as a newly created user, an error will be generated.
Since the password is expired, the above error message is generated. To make this right we need to
change (reset) the password using the following command −

 SET PASSWORD='passwordtest';
You can also set an interval for the EXPIRE clause to implement periodic password changes as shown
below –

 CREATE USER 'sample'@'localhost'

IDENTIFIED BY 'MyPassword'
PASSWORD EXPIRE INTERVAL 25 DAY
FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;
User Comment
You can add comments to the user while creating a user in MySQL using the
COMMENT clause. This provides additional information or context about the user.
 CREATE USER 'sample'@'localhost' COMMENT 'Sample
information';

You can verify the attributes and comments info using the SELECT query given below –

 SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES

WHERE USER='sample' AND HOST='localhost';

User Attribute
You can add attributes to a user in MySQL using the ATTRIBUTE clause when creating a
user account. These attributes can store additional information about the user.

 CREATE USER 'sample'@'localhost' ATTRIBUTE '{"attr1": "val1",

"attr2": "val2"}';
You can verify the attributes and comments info using the SELECT query given below –

 SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES

WHERE USER='sample' AND HOST='localhost';

The IF NOT EXISTS Clause

If you try to create a user with an existing name, an error will be generated. To prevent this error and
ensure the user is created only if it does not already exist, you can use the "IF NOT EXISTS" clause.

 CREATE USER IF NOT EXISTS 'sample@localhost';

 DROP USER [IF EXISTS] 'username'@'hostname';
You can drop/delete one or more existing users in MySQL using the DROP USER Statement. Once
you delete an account, all privileges of it are deleted.
To execute this statement, you need to have CREATE USER privilege.
MySQL SHOW Users
MySQL does not provide any direct command to show (list out) all the users. However, the details of
these user accounts are stored in the "user" table within the database. Hence, we can use the SELECT
statement to list out the contents of this table.

 SELECT * FROM mysql.user;

Not only the list of all users, MySQL also has a provision to see the current user. This is done with the
help of user () or current_user() functions.

 SELECT user (); or

 SELECT current_user ();

B. Roles
 A role is a collection (or group) of privileges that can be assigned to users.
 Roles make administration easier because instead of assigning many privileges individually to
each user, you can group them.
Example: Suppose you have 50 developers. Instead of granting SELECT, INSERT, UPDATE to
each one individually, you just create a developer role and assign it to all 50 users. Easy to
manage.

 Creating a Role:
 CREATE ROLE 'developer';

 Assigning Privileges to a Role:

 GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'developer';

 Assigning Role to a User:

 GRANT 'developer' TO ' sample '@'localhost';

 Activating Roles (if needed):

 SET DEFAULT ROLE 'developer' TO ' sample '@'localhost';
 C. Privileges

 Privileges define what actions a user (or role) can perform in MySQL.
 They can be global, database-level, table-level, or even column-level.

🔹 Types of Privileges:

 Administrative Privileges – Apply to the whole server

 CREATE USER, GRANT OPTION, SHUTDOWN, PROCESS, SUPER.

 Database Privileges – Apply to a specific database

 CREATE, DROP, ALTER, INDEX, REFERENCES.

 Table Privileges – Apply to specific tables

 SELECT, INSERT, UPDATE, DELETE.

 Column Privileges – Apply to specific columns in a table

 UPDATE (column_name), INSERT (column_name).

 Routine Privileges – Apply to stored procedures/functions

 EXECUTE, ALTER ROUTINE.

Granting Privileges: The MySQL GRANT statement is used to assign various privileges or roles to
MySQL user accounts.

 GRANT SELECT, INSERT ON mydb.customers TO 'sample'@'localhost';

 GRANT SELECT, INSERT, UPDATE ON TABLE sample TO test_user1, test_user2,

test_user3;

 Global Privileges: Instead of specifying the table, procedure or a function you can grant global
privileges: privileges that apply to all databases to a user. To do so, you need to use *.* after the
ON clause.
GRANT SELECT, INSERT, UPDATE ON *.* TO 'test_user'@'localhost';

GRANT ALL ON *.* TO 'test_user'@'localhost';

 Database Level Privileges: You can grant privileges to all the objects in a database by specifying
the database name followed by ".*" after the ON clause.
GRANT SELECT, INSERT, UPDATE ON test.* TO 'test_user'@'localhost';

GRANT ALL ON test.* TO 'test_user'@'localhost';

 Column Level Privileges: You can grant privileges on a specific column of a table to a user. To
do so, you need to specify the column names after the privileges.
CREATE TABLE Employee ( ID INT, Name VARCHAR(15), Phone INT, SAL INT);
 GRANT SELECT (ID), INSERT (Name, Phone) ON Employee TO
'test_user'@'localhost';

Revoking Privileges: The MySQL REVOKE statement is used to remove certain administrative
privileges or roles from users. It revokes permissions that were previously granted.

 REVOKE privileges ON database_name.table_name FROM 'user'@'host';

 REVOKE INSERT ON mydb.customers FROM 'sample'@'localhost';

 REVOKE 'TestRole_ReadOnly' FROM 'newuser'@'localhost';

Viewing Privileges:

 SHOW GRANTS FOR 'sample'@'localhost';

MySQL SHOW PRIVILEGES Statement displays the list of privileges that are supported
by the MYSQL server. The displayed list includes all static and currently registered dynamic
privileges.

Privilege Context Comment

Alter Tables To alter the table

Functions, To alter or drop stored

Alter routine
Procedures functions/procedures

Databases, To create new databases

Create
Tables, Indexes and tables

To use CREATE
Create routine Databases
FUNCTION/PROCEDURE

Create role Server Admin To create new roles

To use CREATE
Create temporary tables Databases
TEMPORARY TABLE

Create view Tables To create new views

Create user Server Admin To create new users

Delete Tables To delete existing rows

Databases, To drop databases, tables,

Drop
Tables and views

Drop role Server Admin To drop roles

 To create, alter, drop and
Event Server Admin
execute events

Functions,
Execute To execute stored routines
Procedures

File access on To read and write files on

File
server the server

Databases,
Tables, To give to other users those
Grant option
Functions, privileges you possess
Procedures

Index Tables To create or drop indexes

Insert Tables To insert data into tables

To use LOCK TABLES

Lock tables Databases (together with SELECT
privilege)

To view the plain text of

Process Server Admin
currently executing queries

To make proxy user

Proxy Server Admin
possible

Databases, To have references on

References
Tables tables

To reload or refresh tables,

Reload Server Admin
logs and privileges

To ask where the slave or

Replication client Server Admin
master servers are

To read binary log events

Replication slave Server Admin
from the master

Select Tables To retrieve rows from table

To see all databases with

Show databases Server Admin
SHOW DATABASES

To see views with SHOW

Show view Tables
CREATE VIEW

Shutdown Server Admin To shut down the server

Super Server Admin To use KILL thread, SET

 GLOBAL, CHANGE
MASTER, etc.

Trigger Tables To use triggers

To create/alter/drop
Create tablespace Server Admin
tablespaces

Update Tables To update existing rows

No privileges - allow
Usage Server Admin
connect only

BINLOG_ENCRYPTION_ADMIN Server Admin

AUDIT_ADMIN Server Admin

ENCRYPTION_KEY_ADMIN Server Admin

INNODB_REDO_LOG_ARCHIVE Server Admin

APPLICATION_PASSWORD_ADMI
Server Admin
N

SHOW_ROUTINE Server Admin

BACKUP_ADMIN Server Admin

BINLOG_ADMIN Server Admin

CLONE_ADMIN Server Admin

CONNECTION_ADMIN Server Admin

SET_USER_ID Server Admin

SERVICE_CONNECTION_ADMIN Server Admin

GROUP_REPLICATION_ADMIN Server Admin

REPLICATION_APPLIER Server Admin

INNODB_REDO_LOG_ENABLE Server Admin

PERSIST_RO_VARIABLES_ADMIN Server Admin

TABLE_ENCRYPTION_ADMIN Server Admin

ROLE_ADMIN Server Admin

REPLICATION_SLAVE_ADMIN Server Admin

SESSION_VARIABLES_ADMIN Server Admin

RESOURCE_GROUP_ADMIN Server Admin

RESOURCE_GROUP_USER Server Admin

SYSTEM_USER Server Admin

SYSTEM_VARIABLES_ADMIN Server Admin

XA_RECOVER_ADMIN Server Admin

MySQL Lock-Unlock User Account:

Account Locking and Unlocking in MySQL is introduced to increase security of the database
by preventing unauthorized transactions or suspicious activities.

MySQL provides ACCOUNT LOCK clause to lock the accounts. Using this clause with CREATE
USER and ALTER USER statements will either create a new already locked user or lock the existing
user respectively.

 CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'password' ACCOUNT LOCK;

 ALTER USER 'testuser'@'localhost' ACCOUNT LOCK;

 SELECT User, Host, account_locked FROM mysql.user WHERE User = 'testuser';

Database Maintenance in RDBMS
Database maintenance refers to the regular tasks and processes that keep a database running smoothly
and efficiently. It involves checking, fixing, and updating the database to ensure it stays safe,
organized, and fast.
Why is Database Maintenance Important?
Database maintenance is essential for several reasons:
1. Performance Improvement: Regular maintenance helps a database run faster and respond
more quickly to requests. This makes it easier for users to access the information they need.
2. Data Integrity: Maintaining a database helps to keep the data accurate and reliable. By
regularly checking for errors, you ensure that the information stored is correct.
3. Security Protection: Regular updates and patches can protect the database from security
threats. This keeps sensitive information safe from unwanted access.
4. Space Management: Databases can fill up with unnecessary data over time. Maintenance
helps to clean out old files and free up space, which can improve overall performance.

Key Tasks in Database Maintenance

Here are some common tasks involved in database maintenance:
 Backup and Restore: Regularly backing up the database prevent data loss in case of system
failure. Knowing how to restore data is also crucial in emergencies.
 Indexing: This process helps speed up data retrieval. Updating indexes regularly improves
the speed at which users can find information.
 Updating Software: Keeping database software up-to-date adds new features and fixes bugs.
This ensures that the system is running on the best version available.
 Running Diagnostics: Regularly checking the health of the database can identify issues early.
This helps address problems before they cause major downtime.
 Data Summarization: Gathering & aggregating all the data and calculating new statistical
values based on the aggregated data.
 Cleaning Data: Removing duplicate or outdated information helps maintain data quality. It
ensures that users access the most accurate and relevant details.
Backup and Recovery
Backup in RDBMS
Backup is the process of creating copies of the database to safeguard against data loss due to hardware
failure, human error, cyber-attacks, or disasters. Key types of database backups are:
 Full Backup: Captures the entire database at a point in time, allowing complete restoration.
 Incremental Backup: Captures only changes made since the last backup (full or
incremental), saving storage and time.
 Differential Backup: Captures changes made since the last full backup, simplifying
restoration compared to incremental but using more space.
Backups are usually scheduled regularly and automated, with emphasis on secure storage, encryption,
and off-site copies for disaster recovery. Common storage options include local disks, network
storage, cloud storage, and physical offsite media.
Exporting a database in MySQL is commonly used for backup purposes or transferring data between
servers. You can export entire database or just a portion of it. The simplest way of exporting a
database is by using the mysqldump command-line tool.

 mysqldump -u username -p database_name > output_file_path

Where,
 username: It is the MySQL username to use when connecting to the database.
 database_name: It is the name of the database to be exported.
 output_file_path: It is the path of the backup file. This is where the backup data will be
stored.
 >: This symbol exports the output of the mysqldump command into a file named
output_file_path.

We can also export only specific tables in a database using the mysqldump command-line tool. To do
so, we use the following syntax −

 mysqldump -u username -p database_name table1 table2 ... > output_file.sql

Following is the query to export all the databases in a host using the --all-databases option

 $ mysqldump -u root -p --all-databases > database_dump.sql

Restore in RDBMS
Restore is the process of recovering data from backup copies when data loss or corruption occurs. It
involves:
 Choosing the appropriate backup (full, incremental, differential).
 Applying backups in the correct sequence to bring the database to the desired state.
 Verifying the integrity of restored data.
 Performing point-in-time recovery when necessary to restore the database to a specific
moment to minimize data loss.
As we learned in the previous tutorial about 'Database Export', now we'll learn how to import the
exported data, or backup, into an existing MySQL database. This process is known as database
import.
We can import the backup data into a MySQL database using the MySQL command-line tool. It takes
the username, database name, and the backup file with the data.
Syntax

 $ mysql -u username -p new_database_name < dumpfile_path

Where,
 username: This is the MySQL username to use when connecting to the MySQL server.
 new_database_name: The name of the database where you want to import the data.
 dumpfile_path: It is the path of the backup file. The data will be imported from this file.
 <: This symbol imports the data from the file named output_file_path.
Example:

 mysql -u root -p testdb < data-dump.sql

How Database Maintenance is Used

Database maintenance is a critical process employed by organizations to ensure that their databases
are functioning effectively and efficiently. Here’s how database maintenance is used in various ways:
1. Routine Maintenance
Routine database maintenance involves regular tasks to keep the system running smoothly. This
includes:
 Backups: Regularly saving data to prevent loss in case of failures.
 Updates: Keeping the database software and security patches up-to-date to avoid
vulnerabilities.
2. Performance Enhancement
Database maintenance is used to enhance performance by:
  Optimizing Queries: Fine-tuning queries to speed up data retrieval and ensure efficient
operations.
 Index Management: Creating and updating indexes that improve search times and overall
database speed.
3. Data Integrity Assurance
Maintaining data integrity is critical for reliable decision-making. Database maintenance helps by:
 Implementing Validation Rules: Ensuring that only correct and meaningful data is entered
into the database.
 Conducting Consistency Checks: Regular checks to confirm that the data remains accurate
and reliable over time.
4. Security Measures
Database maintenance plays an important role in security by:
 Setting User Permissions: Controlling access to sensitive data and ensuring that only
authorized personnel can make changes.
 Conducting Security Audits: Regularly reviewing and assessing security measures to protect
against potential threats.
5. Troubleshooting and Diagnostics
Regular maintenance allows for proactive troubleshooting and diagnostics, which include:
 Error Logging: Keeping records of system errors and performance issues to identify patterns
and areas needing attention.
 Performance Monitoring: Using tools to track the health of the database and identify
potential problems before they escalate.
By regularly conducting these maintenance tasks, organizations can ensure their databases remain
reliable, secure, and efficient. This not only supports day-to-day operations but also contributes to
informed decision-making and long-term business success.