Mysql tutorial

create a new user

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

list a user

SELECT User, Host FROM mysql.user;

create a new database

create database companydb02;

list a db

show databases;

change a user password

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

give remote access for sudha user

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

grant access for companydb3 to user sudha

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

login as a sudha user

mysql -u sudha -p

create a student table and employee table to use db as companydb3

USE companydb3;

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) );

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 );

show tables

insert a sample data

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');

show the table

select * from staff;

take a db backup

sudo mysqldump companydb3 > companydb3_backup.sql

drop a database

DROP DATABASE companydb;

first create a db

CREATE DATABASE companydb3;

restore

sudo mysql db4 < companydb3_backup.sql