MariaDB/MySQL User Management, Database Administration, Backup and Restore

1. Log in to MariaDB

sudo mariadb

---

User Management

2. Create a New User

CREATE USER 'sudha'@'localhost' IDENTIFIED BY 'Password@123';

3. List All Users

SELECT User, Host FROM mysql.user;

4. Change a User Password

ALTER USER 'sudha'@'localhost' IDENTIFIED BY 'Password@123';

5. Create a User for Remote Access

CREATE USER 'sudha'@'%' IDENTIFIED BY 'Password@123';

6. Grant Privileges on companydb3

GRANT ALL PRIVILEGES ON companydb3.* TO 'sudha'@'%';

FLUSH PRIVILEGES;

---

Database Operations

7. Create a Database

CREATE DATABASE companydb3;

8. List All Databases

SHOW DATABASES;

9. Select a Database

USE companydb3;

10. Confirm the Selected Database

SELECT DATABASE();

---

Table Operations

11. Create the student Table

CREATE TABLE student (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT,
    gender VARCHAR(10),
    course VARCHAR(100),
    city VARCHAR(100)
);

12. Create the staff Table

CREATE TABLE staff (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(100),
    designation VARCHAR(100),
    salary DECIMAL(10,2),
    joining_date DATE
);

13. List All Tables

SHOW TABLES;

14. Check the Table Structure

DESCRIBE student;

DESCRIBE staff;

---

Insert Sample Data

15. Insert Data into the student Table

INSERT INTO student (name, age, gender, course, city)
VALUES
('Sundar', 25, 'Male', 'Linux Administration', 'Bangalore'),
('Priya', 22, 'Female', 'AWS Cloud', 'Chennai'),
('Kumar', 24, 'Male', 'DevOps', 'Hyderabad');

16. Insert Data into the staff Table

INSERT INTO staff (name, department, designation, salary, joining_date)
VALUES
('Ramesh', 'IT', 'System Administrator', 45000.00, '2023-01-15'),
('Anitha', 'HR', 'HR Executive', 38000.00, '2022-07-20'),
('Vijay', 'Finance', 'Accountant', 42000.00, '2021-11-10');

---

View Data

17. Display the student Table

SELECT * FROM student;

18. Display the staff Table

SELECT * FROM staff;

---

Login as User sudha

From the Linux terminal:

mysql -u sudha -p

Enter the password when prompted.

---

Backup and Restore

19. Take a Backup of companydb3

From the Linux terminal:

sudo mysqldump companydb3 > companydb3_backup.sql

Verify the backup file:

ls -lh companydb3_backup.sql

---

20. Drop a Database

DROP DATABASE companydb3;

---

21. Create a New Database for Restore

CREATE DATABASE db4;

---

22. Restore the Backup into db4

From the Linux terminal:

sudo mysql db4 < companydb3_backup.sql

---

23. Verify the Restore

USE db4;

SHOW TABLES;

SELECT * FROM student;

SELECT * FROM staff;