Category Archives: MySQL

How To Configure MySQL Master-Slave Replication?

MySQL, , ,

How To Configure MySQL Master-Slave Replication? (One-Way Replication)

Table of Contents
___________________________________________________________________________________________________

1. Environment

On MySQL Master Server

2. Update Master Server Config File (Terminal 1)
3. Restart MySQL Server(Terminal 1)
4. Create user for replication on Master side (Terminal 1)
5. Note Down Master Log Position (Terminal 1)
6. Backup  All database of Master (Terminal 2)
7. Unlock Master Database (Terminal 1)
8. Transfer Database Backup Dump File to Slave Server

On MySQL Slave Server

9. Update Slave Server Config File
10. Restart MySQL Server
11. Restore Database Dump into Slave Server
12. Setup Slave to Communicate Master Database
13. Test Replication and Verify the Results

Create Database Operation

   a1) Create Database Operation on Master
   a2) Verify Create Database Operation on Slave

Create Table Operation

    b1) Create Table Operation on Master
    b2) Verify Create Table Operation on Slave

Insert Table Operation

    c1) Insert Table Operation on Master
    c2) Verify Insert Table Operation on Slave

Update Table Operation

    d1) Update Table Operation on Master
    d2) Verify Update Table Operation on Slave

Delete Table Operation

    e1) Delete Table Operation on Master
    e2) Verify Delete Table Operation on Slave
___________________________________________________________________________________________________


1. Environment

Host NamePublic IPPrivate IPMySQL Version
Master Server
rac1192.168.2.101192.168.0.1018.0
Slave Server
rac2192.168.2.102192.168.0.1028.0

Private IP using for replication — Dedicated IP


On MySQL Master Server


2. Update Master Server config file (Terminal 1)

Edit /etc/my.cnf file and add below entries.

server-id=1
bind-address=192.168.0.101
log-bin=mysql-bin

[root@rac1 ~]# cat /etc/my.cnf | egrep "server-id|bind-address|log-bin"
server-id=1
bind-address=192.168.0.101
log-bin=mysql-bin
[root@rac1 ~]#


3. Restart MySQL Server (Terminal 1)

[root@rac1 ~]# systemctl restart mysqld
[root@rac1 ~]#


4. Create user for replication (Terminal 1)

*** Create replica user in Master pointing to slave, it's requires when Slave connects to Mater.

*** Grant REPLICATION SLAVE privilege to myreplica

mysql> select host,user from mysql.user where user='replica';
Empty set (0.00 sec)

mysql> CREATE USER 'replica'@'192.168.0.102' IDENTIFIED WITH  mysql_native_password BY 'Mysql@123';
Query OK, 0 rows affected (0.01 sec)

mysql> select host,user from mysql.user where user='replica';
+---------------+---------+
| host          | user    |
+---------------+---------+
| 192.168.0.102 | replica |
+---------------+---------+
1 row in set (0.00 sec)

mysql>

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'192.168.0.102';
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> 

Note that this lock is released either when you exit the mysql CLI client, or when you issue UNLOCK TABLES. The lock needs to remain in place until the mysqldump  is complete.


5. Note Down Master Log Position (Terminal 1)

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000009 |     2369 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>


6. Backup All database of Master (Terminal 2)

[root@rac1 backup]# pwd
/u01/mysql/data/backup
[root@rac1 backup]# ls -ltr
total 0
[root@rac1 backup]#
[root@rac1 backup]# mysqldump --all-databases --master-data > /u01/mysql/data/backup/alldbdump.sql
[root@rac1 backup]#
[root@rac1 backup]# ls -ltr
total 95480
-rw-r--r--. 1 root root 97769974 Mar 18 19:43 alldbdump.sql <----
[root@rac1 backup]#


7. Unlock Master Database (Terminal 1)

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql>


8. Transfer Database Backup Dump File to Slave Server

[root@rac1 backup]# scp alldbdump.sql root@192.168.0.102:/u01/mysql/backup
root@192.168.0.102's password:
alldbdump.sql                                100%   93MB  51.5MB/s   00:01
[root@rac1 backup]#


On MySQL Slave Server


9. Update Slave Server Config File

Edit the /etc/my.cnf file and add below entries

[root@rac2 ~]# cat /etc/my.cnf | egrep "server-id|bind-address|log-bin"
server-id=2
bind-address=192.168.0.102
log-bin=mysql-bin
[root@rac2 ~]#


10. Restart MySQL Server

[root@rac2 ~]# systemctl restart mysqld
[root@rac2 ~]#


11. Restore Database Dump into Slave Server

[root@rac2 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> exit
Bye
[root@rac2 ~]#

[root@rac2 ~]# cd /u01/mysql/backup
[root@rac2 backup]# ls -ltr
total 95480
-rw-r--r--. 1 root root 97769974 Mar 18 19:44 alldbdump.sql
[root@rac2 backup]#

[root@rac2 backup]# mysql < /u01/mysql/backup/alldbdump.sql 
ERROR 3021 (HY000) at line 24: This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL '' first. 

[root@rac2 backup]# 
[root@rac2 backup]# mysql 
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 17 Server version: 8.0.23 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> exit
Bye
[root@rac2 backup]# mysql < /u01/mysql/backup/alldbdump.sql
[root@rac2 backup]#


12. Setup Slave to Communicate Master Database

[root@rac2 backup]# mysql

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.0.101',
    -> MASTER_USER='replica',
    -> MASTER_PASSWORD='Mysql@123',
    -> MASTER_LOG_FILE='mysql-bin.000009',
    -> MASTER_LOG_POS=2369;
Query OK, 0 rows affected, 8 warnings (0.02 sec)

mysql>
mysql> START SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

mysql> SHOW PROCESSLIST;
+----+-----------------+-----------------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User            | Host            | db   | Command | Time | State                                                  | Info             |
+----+-----------------+-----------------+------+---------+------+--------------------------------------------------------+------------------+
|  5 | event_scheduler | localhost       | NULL | Daemon  | 2355 | Waiting on empty queue                                 | NULL             |
| 23 | root            | localhost       | NULL | Query   |    0 | init                                                   | SHOW PROCESSLIST |
| 24 | system user     | connecting host | NULL | Connect |   16 | Waiting for master to send event                       | NULL             |
| 25 | system user     |                 | NULL | Query   |   16 | Slave has read all relay log; waiting for more updates | NULL             |
+----+-----------------+-----------------+------+---------+------+--------------------------------------------------------+------------------+
4 rows in set (0.00 sec)

mysql>

mysql> SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.101
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000009
          Read_Master_Log_Pos: 2369
               Relay_Log_File: rac2-relay-bin.000002
                Relay_Log_Pos: 324
        Relay_Master_Log_File: mysql-bin.000009
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2369
              Relay_Log_Space: 532
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 30515002-7f31-11eb-8fab-080027cabe36
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set, 1 warning (0.01 sec)

ERROR:
No query specified

mysql>


13. Test Replication and Verify the Results


Create Database Operation


a1) Create Database Operation on Master

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| orcl               |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql>

mysql> create database mynsp;
Query OK, 1 row affected (0.01 sec)

mysql>

mysql> use mynsp;
Database changed
mysql>
mysql> show tables;
Empty set (0.00 sec)

mysql>


a2) Verify Create Database Operation on Slave

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mynsp              |
| mysql              |
| orcl               |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql>

mysql> use mynsp;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql>


Create Table Operation


b1) Create Table Operation on Master

mysql> create table MYSCB_DBA (
    -> ID int,
    -> NAME varchar(20),
    -> ROLE varchar(20),
    -> primary key (ID))
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql>


mysql> show tables;
+-----------------+
| Tables_in_mynsp |
+-----------------+
| MYSCB_DBA       |  <------
+-----------------+
1 row in set (0.00 sec)

mysql>


b2) Verify Create Table Operation on Slave

mysql> show tables;
+-----------------+
| Tables_in_mynsp |
+-----------------+
| MYSCB_DBA       | <------
+-----------------+
1 row in set (0.00 sec)

mysql>


Insert Table Operation


c1) Insert Table Operation on Master

mysql> SELECT * FROM MYSCB_DBA;
Empty set (0.00 sec)

mysql>

mysql> INSERT INTO MYSCB_DBA values(1,'SIRAJ','DBA');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO MYSCB_DBA values(2,'RAGHU','DBA');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO MYSCB_DBA values(3,'VINOD','DBA');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO MYSCB_DBA values(4,'VIJAY','DBA');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO MYSCB_DBA values(5,'SURESH','DBA');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO MYSCB_DBA values(6,'SENTHIL','DBA');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO MYSCB_DBA values(7,'CHANDRA','DBA');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO MYSCB_DBA values(8,'PRASANNA','DBA');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO MYSCB_DBA values(9,'GOVARDINI','DBA');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO MYSCB_DBA values(10,'RAJASEKHAR','L2DBA');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO MYSCB_DBA values(11,'RAJKUMAR','MANAGER');
Query OK, 1 row affected (0.00 sec)

mysql>

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql>


mysql> SELECT * FROM MYSCB_DBA;
+----+------------+---------+
| ID | NAME       | ROLE    |
+----+------------+---------+
|  1 | SIRAJ      | DBA     |
|  2 | RAGHU      | DBA     |
|  3 | VINOD      | DBA     |
|  4 | VIJAY      | DBA     |
|  5 | SURESH     | DBA     |
|  6 | SENTHIL    | DBA     |
|  7 | CHANDRA    | DBA     |
|  8 | PRASANNA   | DBA     |
|  9 | GOVARDINI  | DBA     |
| 10 | RAJASEKHAR | L2DBA   |
| 11 | RAJKUMAR   | MANAGER |
+----+------------+---------+
11 rows in set (0.00 sec)

mysql>


c2) Verify Insert Table Operation on Slave

mysql> SELECT * FROM MYSCB_DBA;
+----+------------+---------+
| ID | NAME       | ROLE    |
+----+------------+---------+
|  1 | SIRAJ      | DBA     |
|  2 | RAGHU      | DBA     |
|  3 | VINOD      | DBA     |
|  4 | VIJAY      | DBA     |
|  5 | SURESH     | DBA     |
|  6 | SENTHIL    | DBA     |
|  7 | CHANDRA    | DBA     |
|  8 | PRASANNA   | DBA     |
|  9 | GOVARDINI  | DBA     |
| 10 | RAJASEKHAR | L2DBA   |
| 11 | RAJKUMAR   | MANAGER |
+----+------------+---------+
11 rows in set (0.00 sec)

mysql>


Update Table Operation


d1) Update Table Operation on Master

mysql> update MYSCB_DBA set role='L1DBA' where NAME='RAJASEKHAR';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> SELECT * FROM MYSCB_DBA WHERE NAME='RAJASEKHAR';
+----+------------+-------+
| ID | NAME       | ROLE  |
+----+------------+-------+
| 10 | RAJASEKHAR | L1DBA | <-------
+----+------------+-------+
1 row in set (0.00 sec)

mysql>


d2) Verify Update Table Operation on Slave

mysql> SELECT * FROM MYSCB_DBA WHERE NAME='RAJASEKHAR';
+----+------------+-------+
| ID | NAME       | ROLE  |
+----+------------+-------+
| 10 | RAJASEKHAR | L1DBA | <-----
+----+------------+-------+
1 row in set (0.00 sec)

mysql>


Delete Table Operation


e1) Delete Table Operation on Master

mysql> DELETE FROM MYSCB_DBA WHERE NAME='RAJASEKHAR';
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> SELECT * FROM MYSCB_DBA WHERE NAME='RAJASEKHAR';
Empty set (0.00 sec)  <-----

mysql>


e2) Verify Delete Table Operation on Slave

mysql> SELECT * FROM MYSCB_DBA WHERE NAME='RAJASEKHAR';
Empty set (0.00 sec) <-----

mysql>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

How To Restore Single Table From Full Database Backup in MySQL

MySQL, ,

How To Restore Single Table From Full Database Backup in MySQL?

Table of Contents
___________________________________________________________________________________________________

1. Issue Description
2. Data collection

Issue Simulation

3. Take MySQL Database Full Backup?
4. Drop Table
5. How To Restore Single Table From Full Database Backup in MySQL?
6. Verify
_______________________________________________________________________________________


1. Issue Description

One of your member by mistake DELETED main application Table, but we have a recent backup of the Database that contains this Table, which backup was made a few days ago.

So question is How To Restore Single Table From Full Database Backup in MySQL? Since we do not want to overwrite the other Tables that are up to date.


2. Data collection

Database backup taken using mysqldump, Database backup file contains SQL statements only.

ALL CREATE TABLE statements start with DROP TABLE IF EXISTS and then CREATE TABALE and then INSERT STATEMENTS and it will end with keyword UNLOCK TABLES

I want to Print all the lines between DROP TABLE IF EXISTS and the next UNLOCK TABLES occurrence from the databases_backup_orcl.sql file into a new file orcl_test_data.sql, but How?

sed - stream editor for filtering and transforming text

The option -n disables automatic printing
The option -e --expression=script -- we can use Sed addresses form /PATTERN/,/PATTERN/, which means that we are targeting a range of lines.

sed -n -e '/DROP TABLE.*`test_data`/,/UNLOCK TABLES/p' databases_backup_orcl.sql > orcl_test_data.sql

-- then restore table using below command
mysql orcl < orcl_test_data1.sql

Issue Simulation


3. Take MySQL Database Full Backup?

[root@rac1 ~]# mysqldump orcl > /u01/mysql/data/backup/databases_backup_orcl.sql
[root@rac1 ~]#
[root@rac1 ~]# ls -ltr /u01/mysql/data/backup/databases_backup_orcl.sql
-rw-r--r--. 1 root root 84988986 Mar  9 00:42 /u01/mysql/data/backup/databases_backup_orcl.sql
[root@rac1 ~]#


4. Drop Table

[root@rac1 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use orcl;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT table_name AS "Table",
    -> ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
    -> FROM information_schema.TABLES
    -> WHERE table_schema = "orcl"
    -> ORDER BY (data_length + index_length) DESC;
+------------+-----------+
| Table      | Size (MB) |
+------------+-----------+
| test_data  |     41.58 | <----- This table we will drop now.
| test_data2 |     38.58 |
| data       |      8.52 |
+------------+-----------+
3 rows in set (0.01 sec)

mysql>

mysql> select count(*) from test_data;
+----------+
| count(*) |
+----------+
|  1000000 | <----- Note down the count
+----------+
1 row in set (0.14 sec)

mysql>

mysql> DROP TABLE test_data;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW TABLES;
+----------------+
| Tables_in_orcl |
+----------------+
| data           |
| test_data2     |
+----------------+
2 rows in set (0.00 sec)

mysql>


5. How To Restore Single Table From Full Database Backup in MySQL?

[root@rac1 backup]#
[root@rac1 backup]# ls -ltr
total 83000
-rw-r--r--. 1 root root 84988986 Mar  9 00:42 databases_backup_orcl.sql
[root@rac1 backup]#
[root@rac1 backup]# sed -n -e '/DROP TABLE.*`test_data`/,/UNLOCK TABLES/p' databases_backup_orcl.sql > orcl_test_data.sql
[root@rac1 backup]#
[root@rac1 backup]# ls -ltr
total 120768
-rw-r--r--. 1 root root 84988986 Mar  9 00:42 databases_backup_orcl.sql
-rw-r--r--. 1 root root 38671152 Mar  9 00:58 orcl_test_data.sql
[root@rac1 backup]#

-- Verify the orcl_test_data.sql file before going to start restore.

[root@rac1 backup]# cat orcl_test_data.sql | head -10
DROP TABLE IF EXISTS `test_data`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `test_data` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `datetime` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `channel` int DEFAULT NULL,
  `value` float DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
[root@rac1 backup]#

[root@rac1 backup]# cat orcl_test_data.sql | tail -1
UNLOCK TABLES;
[root@rac1 backup]#

[root@rac1 backup]# cat orcl_test_data.sql | egrep 'CREATE TABLE|UNLOCK'
CREATE TABLE `test_data` (
UNLOCK TABLES;
[root@rac1 backup]#

[root@rac1 backup]# ls -ltr
total 120768
-rw-r--r--. 1 root root 84988986 Mar  9 00:42 databases_backup_orcl.sql
-rw-r--r--. 1 root root 38671152 Mar  9 00:58 orcl_test_data.sql
[root@rac1 backup]#
-- Now restore the Table test_data to database orcl.
[root@rac1 backup]# mysql orcl < orcl_test_data.sql
[root@rac1 backup]#


6. Verify

[root@rac1 backup]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use orcl;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_orcl |
+----------------+
| data           |
| test_data      |  <--- Table restored. 
| test_data2     |
+----------------+
3 rows in set (0.00 sec)

mysql> select count(*) from test_data;
+----------+
| count(*) |
+----------+
|  1000000 | <---- Table count matching.
+----------+
1 row in set (0.14 sec)

mysql>

mysql> SELECT table_name AS "Table",
    -> ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
    -> FROM information_schema.TABLES
    -> WHERE table_schema = "orcl"
    -> ORDER BY (data_length + index_length) DESC;
+------------+-----------+
| Table      | Size (MB) |
+------------+-----------+
| test_data  |     41.58 |  <---- 
| test_data2 |     38.58 |
| data       |      8.52 |
+------------+-----------+
3 rows in set (0.00 sec)

mysql>

We have successfully restored missing table from full backup without overwriting the other tables data. 🙂

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

How to Restore A Single Database From A Backup File Which Contains Multiple Databases

MySQL,

How to Restore A Single Database From A Backup File Which Contains Multiple Databases?

Table of Contents
___________________________________________________________________________________________________

1. Environment
2. How to Backup All MySQL Databases?
3. Transfer Backup files to Target server
4. How to Restore A Single Database From A Backup File Which Contains Multiple Databases?
5. Verification
___________________________________________________________________________________________________


1. Environment

Goal: Want to Restore A Single Database From A Backup File Which Contains Multiple Databases

You backed up all your databases using the -all-databases option and you want to restore a single database from a backup file which contains multiple databases use the --one-database option.

Source:

Hostname		: rac1
Database Name	        : orcl,hit and all internal databases (Backup file contains all databases)


Target:

Hostname		: rac2
Database Name	        : hit  <---- Need to restore Database "hit" from RAC1 databases's backup.


2. How to Backup All MySQL Databases?

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hit                |
| information_schema |
| mysql              |
| orcl               |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql>

[root@rac1 ~]# mysqldump --all-databases > /u01/mysql/data/backup/db_all_bkp_$(date +%Y%m%d).sql
[root@rac1 ~]#
[root@rac1 ~]# ls -ltr /u01/mysql/data/backup/db_all_bkp_*
-rw-r--r--. 1 root root 89902589 Mar  8 22:12 /u01/mysql/data/backup/db_all_bkp_20210308.sql
[root@rac1 ~]#


3. Transfer Backup files to Target server

[root@rac1 ~]# cd /u01/mysql/data/backup
[root@rac1 backup]# scp db_all_bkp_20210308.sql rac2:/u01/mysql/data
root@rac2's password:
db_all_bkp_20210308.sql                100%   86MB  48.5MB/s   00:01
[root@rac1 backup]#

[root@rac2 ~]# cd /u01/mysql/data
[root@rac2 data]# ls -ltr db_all_bkp_20210308.sql
-rw-r--r--. 1 root root 89902589 Mar  8 22:14 db_all_bkp_20210308.sql
[root@rac2 data]#


4. How to Restore A Single Database From A Backup File Which Contains Multiple Databases?

[root@rac2 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>

[root@rac2 ~]# mysql --one-database hit < /u01/mysql/data/db_all_bkp_20210308.sql 
ERROR 1049 (42000): Unknown database 'hit' <--- Need to create DB first.
[root@rac2 ~]# 
[root@rac2 ~]# mysql -e "create database hit"; 
[root@rac2 ~]# 
[root@rac2 ~]# mysql 
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 22 Server version: 8.0.23 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hit                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql>
-- Restore A database using --one-database option 
[root@rac2 ~]# mysql --one-database hit < /u01/mysql/data/db_all_bkp_20210308.sql
[root@rac2 ~]#


5. Verification

[root@rac2 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT table_schema AS "Database",
    -> ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
    -> FROM information_schema.TABLES
    -> GROUP BY table_schema;
+--------------------+-----------+
| Database           | Size (MB) |
+--------------------+-----------+
| mysql              |      2.48 |
| information_schema |      0.00 |
| performance_schema |      0.00 |
| sys                |      0.02 |
| hit                |      0.02 |  <--- DB restored
+--------------------+-----------+
5 rows in set (0.02 sec)

mysql> use hit;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------+
| Tables_in_hit |
+---------------+
| hit_test      |  <-- We can able see tables inside DB "hit"
+---------------+
1 row in set (0.01 sec)

mysql>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

How to Restore Database from Single MySQL Database Backup

MySQL, ,

How to Restore Database from Single MySQL Database Backup?

Table of Contents
___________________________________________________________________________________________________

1. Environment
2. How to Backup a Single MySQL Database?
3. Transfer Backup files to Target server
4. How to Restore Database from Single MySQL Database Backup?
5. Verification
___________________________________________________________________________________________________


1. Environment

Source:

Hostname		: rac1
Database Name	        : orcl
Database Size	        : 67 MB

Target:

Hostname		: rac2
Database Name	        : orcl
Free space on mysql data dir : 10GB


2. How to Backup a Single MySQL Database?

mysql> use orcl;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_orcl |
+----------------+
| data           |
| test_data      |
| test_data2     |
+----------------+
3 rows in set (0.00 sec)

mysql>

[root@rac1 ~]# mysqldump orcl > /u01/mysql/data/backup/db_backup_orcl_$(date +%Y%m%d).sql
[root@rac1 ~]#
[root@rac1 ~]# ls -ltr /u01/mysql/data/backup/db_backup_orcl_*
-rw-r--r--. 1 root root 84988986 Mar  8 17:32 /u01/mysql/data/backup/db_backup_orcl_20210308.sql
[root@rac1 ~]#


3. Transfer Backup files to Target server

[root@rac1 ~]# ls -ltr /u01/mysql/data/backup/db_backup_orcl_*
-rw-r--r--. 1 root root 84988986 Mar  8 17:32 /u01/mysql/data/backup/db_backup_orcl_20210308.sql
[root@rac1 ~]# cd /u01/mysql/data/backup
[root@rac1 backup]# scp db_backup_orcl_20210308.sql rac2:/u01/mysql/data/orcl/backup
The authenticity of host 'rac2 (192.168.2.102)' can't be established.
ECDSA key fingerprint is SHA256:Jn2hrEtLe/2u2u0ZiOm0EbeniTbP3NEPx2r8Kxqsi0k.
ECDSA key fingerprint is MD5:88:4b:43:a5:d6:08:b1:79:8d:1d:69:63:0e:a9:6f:91.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'rac2,192.168.2.102' (ECDSA) to the list of known hosts.
root@rac2's password:
db_backup_orcl_20210308.sql                                         100%   81MB  48.2MB/s   00:01
[root@rac1 backup]#

[root@rac2 ~]# cd /u01/mysql/data/orcl/backup
[root@rac2 backup]# ls -ltr
total 83000
-rw-r--r--. 1 root root 84988986 Mar  8 17:41 db_backup_orcl_20210308.sql
[root@rac2 backup]#


4. How to Restore Database from Single MySQL Database Backup?

[root@rac2 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT table_schema AS "Database",
    -> ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
    -> FROM information_schema.TABLES
    -> GROUP BY table_schema;
+--------------------+-----------+
| Database           | Size (MB) |
+--------------------+-----------+
| information_schema |      0.00 |
| mysql              |      2.48 |
| performance_schema |      0.00 |
| sys                |      0.02 |
+--------------------+-----------+
4 rows in set (0.19 sec)  <-----

mysql>

[root@rac2 ~]# mysql orcl < /u01/mysql/data/orcl/backup/db_backup_orcl_20210308.sql 
ERROR 1049 (42000): Unknown database 'orcl' <----
[root@rac2 ~]# 

-- Create Database before restore
mysql> create database orcl;
Query OK, 1 row affected (0.00 sec)

mysql>

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| orcl               | <---- Created manually 
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql>

-- Restore Database 
[root@rac2 ~]# mysql orcl < /u01/mysql/data/orcl/backup/db_backup_orcl_20210308.sql
[root@rac2 ~]#


5. Verification

[root@rac2 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT table_schema AS "Database",
    -> ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
    -> FROM information_schema.TABLES
    -> GROUP BY table_schema;
+--------------------+-----------+
| Database           | Size (MB) |
+--------------------+-----------+
| information_schema |      0.00 |
| mysql              |      2.48 |
| orcl               |     91.67 |  <-------- Restored.
| performance_schema |      0.00 |
| sys                |      0.02 |
+--------------------+-----------+
5 rows in set (0.02 sec)

mysql>

mysql> use orcl;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_orcl |
+----------------+
| data           |
| test_data      |
| test_data2     |
+----------------+
3 rows in set (0.00 sec)

mysql>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Create Roles in MySQL

MySQL, , , , , , , , ,

Create Roles in MySQL

Table of Contents
___________________________________________________________________________________________________

1. Overview
2. How to Create Role?
3. How to Grant Privileges to Role?
4. Create new users and Grant Roles
5. How to find what roles granted to any users?
6. How to set Granted ROLE manually if not set?
7. How to Define Mandatory Roles?
8. How to Find assigned privileges from Role?
9. How to Revoke Privileges from Role?
10. Revoke Roles from User (OR) Role?
11. How to Drop Roles?
___________________________________________________________________________________________________


1. Overview

A MySQL role is a named collection of privileges. Like user accounts, roles can have privileges granted to and revoked from them.


2. How to Create Roles?

mysql> CREATE ROLE 'DELL_APP_RO', 'DELL_APP_RW';
Query OK, 0 rows affected (0.00 sec)

mysql>


3. How to Grant Privileges to Role?

mysql> GRANT SELECT ON dell.* TO 'DELL_APP_RO';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON dell.* TO 'DELL_APP_RW';
Query OK, 0 rows affected (0.01 sec)

mysql>


4. Create new users and Grant Roles

-- Create new users
mysql> CREATE USER 'raj_ro_user'@'localhost' IDENTIFIED BY 'Raj_ro_user1%';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER 'raj_rw_user'@'localhost' IDENTIFIED BY 'Raj_rw_user1%';
Query OK, 0 rows affected (0.01 sec)

mysql>

-- Grants Roles 
mysql> GRANT DELL_APP_RO TO 'raj_ro_user'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT DELL_APP_RW TO 'raj_rw_user'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql>


5. How to find what roles granted to any users?

mysql> SELECT * FROM mysql.role_edges;
+-----------+-------------+-----------+-------------+-------------------+
| FROM_HOST | FROM_USER   | TO_HOST   | TO_USER     | WITH_ADMIN_OPTION |
+-----------+-------------+-----------+-------------+-------------------+
| %         | DELL_APP_RO | localhost | raj_ro_user | N                 |
| %         | DELL_APP_RW | localhost | raj_rw_user | N                 |
+-----------+-------------+-----------+-------------+-------------------+
2 rows in set (0.00 sec)

mysql>

------------ OR -------------

-- TO list ALL Roles  (you can notice roles also)

select * from mysql.user;


6. How to set Granted ROLE manually if not set?

[root@rac1 ~]# mysql -u raj_ro_user -h localhost -pRaj_ro_user1%
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select current_user ();
+-----------------------+
| current_user ()       |
+-----------------------+
| raj_ro_user@localhost |
+-----------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS;
+------------------------------------------------------+
| Grants for raj_ro_user@localhost                     |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO `raj_ro_user`@`localhost`      |
| GRANT `DELL_APP_RO`@`%` TO `raj_ro_user`@`localhost` |
+------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.00 sec)

mysql> set role DELL_APP_RO;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CURRENT_ROLE();
+-------------------+
| CURRENT_ROLE()    |
+-------------------+
| `DELL_APP_RO`@`%` |
+-------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS;
+-------------------------------------------------------+
| Grants for raj_ro_user@localhost                      |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `raj_ro_user`@`localhost`       |
| GRANT SELECT ON `dell`.* TO `raj_ro_user`@`localhost` |
| GRANT `DELL_APP_RO`@`%` TO `raj_ro_user`@`localhost`  |
+-------------------------------------------------------+
3 rows in set (0.00 sec)

mysql>

############################################################

-- This command can set permanatly, no need to set manually

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql>

mysql> set global activate_all_roles_on_login=1;
Query OK, 0 rows affected (0.00 sec)

mysql> 

#############################################################

-- Set multiple default roles for one user

SET DEFAULT ROLE ROLE1,ROLE2,ROLE3 TO TO 'username'@'localhost';

-- A default role can be assigned to multiple users in one command as above
SET DEFAULT ROLE ROLE1 TO 'user1'@'localhost', 'user2'@'localhost', 'user3'@'localhost';


7. How To Define Mandatory Roles? 

To set and persist mandatory_roles at runtime,use a statement like this:
-- if you create new user, then automatically this role will be assigned.
-- these changes are fixed ever after restart MySQL.

mysql> CREATE ROLE MY_MANDATORY_ROLE;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT DELL_APP_RO,DELL_APP_RW TO MY_MANDATORY_ROLE;
Query OK, 0 rows affected (0.01 sec)

mysql>  GRANT SELECT ON sys.version to MY_MANDATORY_ROLE;
Query OK, 0 rows affected (0.00 sec)

mysql> SET PERSIST mandatory_roles = 'MY_MANDATORY_ROLE';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@mandatory_roles;
+-------------------+
| @@mandatory_roles |
+-------------------+
| MY_MANDATORY_ROLE |
+-------------------+
1 row in set (0.00 sec)

mysql> create user lenovo@localhost identified by 'Lenovo123%';
Query OK, 0 rows affected (0.01 sec)

mysql>

[root@rac1 ~]# mysql -u lenovo -h localhost -pLenovo123%
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select user();
+------------------+
| user()           |
+------------------+
| lenovo@localhost |
+------------------+
1 row in set (0.00 sec)

mysql> show grants;
+-------------------------------------------------------+
| Grants for lenovo@localhost                           |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `lenovo`@`localhost`            |
| GRANT `MY_MANDATORY_ROLE`@`%` TO `lenovo`@`localhost` |
+-------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>


8. How to Find assigned privileges from Role?

mysql> show grants for DELL_APP_RO;
+-----------------------------------------------+
| Grants for DELL_APP_RO@%                      |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO `DELL_APP_RO`@`%`       |
| GRANT SELECT ON `dell`.* TO `DELL_APP_RO`@`%` |
+-----------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for DELL_APP_RW;
+-----------------------------------------------------------------------+
| Grants for DELL_APP_RW@%                                              |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `DELL_APP_RW`@`%`                               |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `dell`.* TO `DELL_APP_RW`@`%` |
+-----------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>


9. How to Revoke Privileges from Role? and Revoke Roles from User?

mysql> REVOKE SELECT ON dell.* FROM DELL_APP_RO;
Query OK, 0 rows affected (0.01 sec)

mysql> REVOKE SELECT,INSERT,UPDATE,DELETE ON dell.* FROM DELL_APP_RW;
Query OK, 0 rows affected (0.01 sec)

mysql>

mysql> show grants for DELL_APP_RO;
+-----------------------------------------+
| Grants for DELL_APP_RO@%                |
+-----------------------------------------+
| GRANT USAGE ON *.* TO `DELL_APP_RO`@`%` |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> show grants for DELL_APP_RW;
+-----------------------------------------+
| Grants for DELL_APP_RW@%                |
+-----------------------------------------+
| GRANT USAGE ON *.* TO `DELL_APP_RW`@`%` |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql>


10. Revoke Roles from User (OR) Role?

mysql> SELECT * FROM mysql.role_edges;
+-----------+-------------+-----------+-------------------+-------------------+
| FROM_HOST | FROM_USER   | TO_HOST   | TO_USER           | WITH_ADMIN_OPTION |
+-----------+-------------+-----------+-------------------+-------------------+
| %         | DELL_APP_RO | %         | MY_MANDATORY_ROLE | N                 |
| %         | DELL_APP_RO | localhost | raj_ro_user       | N                 |
| %         | DELL_APP_RW | %         | MY_MANDATORY_ROLE | N                 |
| %         | DELL_APP_RW | localhost | raj_rw_user       | N                 |
+-----------+-------------+-----------+-------------------+-------------------+
4 rows in set (0.00 sec)

mysql>

-- Revoke Roles from user
mysql> REVOKE DELL_APP_RO FROM 'raj_ro_user'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> REVOKE DELL_APP_RW FROM 'raj_rw_user'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> 

-- Revoke Roles from Roles
mysql> REVOKE DELL_APP_RO FROM 'MY_MANDATORY_ROLE';
Query OK, 0 rows affected (0.01 sec)

mysql> REVOKE DELL_APP_RW FROM 'MY_MANDATORY_ROLE';
Query OK, 0 rows affected (0.01 sec)

mysql>

mysql> SELECT * FROM mysql.role_edges;
Empty set (0.00 sec)

mysql>


11. How to Drop Roles?

mysql> select user from mysql.user;
+-------------------+
| user              |
+-------------------+
| DELL_APP_RO       |
| DELL_APP_RW       |
| MY_MANDATORY_ROLE |
| TEST_RO           |
| raj               |
| scott1            |
| srinu             |
| scott             |
| scott             |
| sugi              |
| scott             |
| teja              |
| bose              |
| lenovo            |
| lori              |
| may               |
| mike              |
| mysql.infoschema  |
| mysql.session     |
| mysql.sys         |
| raj               |
| raj_ro_user       |
| raj_rw_user       |
| root              |
| sunil             |
+-------------------+
25 rows in set (0.00 sec)

mysql>

mysql> DROP ROLE DELL_APP_RO;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP ROLE DELL_APP_RW;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DROP ROLE MY_MANDATORY_ROLE;
ERROR 3628 (HY000): The role `MY_MANDATORY_ROLE`@`%` is a mandatory role and can't be revoked or dropped. The restriction can be lifted by excluding the role identifier from the global variable mandatory_roles.
mysql>
mysql> select @@mandatory_roles;
+-------------------+
| @@mandatory_roles |
+-------------------+
| MY_MANDATORY_ROLE |
+-------------------+
1 row in set (0.00 sec)

mysql>

mysql> SET PERSIST mandatory_roles = 'NONE';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@mandatory_roles;
+-------------------+
| @@mandatory_roles |
+-------------------+
| NONE              |
+-------------------+
1 row in set (0.00 sec)

mysql> DROP ROLE MY_MANDATORY_ROLE;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> select user from mysql.user;
+------------------+
| user             |
+------------------+
| TEST_RO          |
| raj              |
| scott1           |
| srinu            |
| scott            |
| scott            |
| sugi             |
| scott            |
| teja             |
| bose             |
| lenovo           |
| lori             |
| may              |
| mike             |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| raj              |
| raj_ro_user      |
| raj_rw_user      |
| root             |
| sunil            |
+------------------+
22 rows in set (0.00 sec)

mysql>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Reference:
https://dev.mysql.com/doc/refman/8.0/en/roles.html

Grant Privileges in MySQL

MySQL, , , , , , , ,

Grant/Revoke Privileges in MySQL

Table of Contents
___________________________________________________________________________________________________

1. Overview
2. What Are User Privileges?
3. How to List Own User Privileges?
4. How to List Other User Privileges?
5. How To Grant User Privileges at the Global Level?
6. How To Grant User Privileges at the Database Level?
7. How To Grant User Privileges at the Table Level?
8. How To Grant User Privileges at the Column Level?
9. How To Grant User Privileges WITH GRANT OPTION?
10. How to Revoke User Privileges?
___________________________________________________________________________________________________


1. Overview

Global Level :
A privilege granted at this level applies to all databases on the server. Privileges granted at the global level stored in "mysql.user" table.

Database Level:
A privilege granted at this level applies to all tables in the specified database. Privileges granted at the global level stored in "mysql.db" table.

Table Level:
A privilege granted at this level applies to all columns in the specified table. Privileges granted at the global level stored in "mysql.tables_priv" table.

Column Level:
A privilege granted at this level applies to only the specified column. Privileges granted at the global level stored in "mysql.columns_priv" table.

Routine Level:
A privilege granted at this level applies to only the specified stored function procedure. Privileges granted at the global level stored in "mysql.procs_priv" table.

Proxy Level:
Grant proxy access to users


2. What Are User Privileges?


ALL 	- All privileges.
CREATE 	- Allows the user to use CREATE TABLE commands.
ALTER 	- Allows the user to use ALTER TABLE commands.
DROP 	- Allows the user to use DROP TABLE commands.
DELETE	- Allows the user to use DELETE commands.
INSERT	- Allows the user to use INSERT commands.
UPDATE 	- Allows the user to use UPDATE commands.
SELECT 	- Allows the user to use SELECT commands.
SHUTDOWN - Allows the user to use "mysqladmin shutdown".
INDEX 	- Allows the user to create and drop indexes.
CREATE USER - Allows the user to manage user accounts.
CREATE VIEW - Allows the user to user "CREATE VIEW" commands.
CREATE ROUTINE - Allows the user to CREATE PROCEDURE and CREATE FUNCTION
USAGE - No privileges.


3. How to List Own User Privileges?

[root@rac1 ~]# mysql -u raj -pRaj_12345%
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| raj@%          |
+----------------+
1 row in set (0.00 sec)

mysql>

mysql> SHOW GRANTS FOR CURRENT_USER();
+---------------------------------+
| Grants for raj@%                |
+---------------------------------+
| GRANT USAGE ON *.* TO `raj`@`%` |
+---------------------------------+
1 row in set (0.00 sec)

mysql>

---------------- OR -----

mysql> show grants;
+---------------------------------+
| Grants for raj@%                |
+---------------------------------+
| GRANT USAGE ON *.* TO `raj`@`%` |
+---------------------------------+
1 row in set (0.00 sec)

mysql>


4. How to List Other User Privileges?

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR 'raj'@'%';
+---------------------------------+
| Grants for raj@%                |
+---------------------------------+
| GRANT USAGE ON *.* TO `raj`@`%` |
+---------------------------------+
1 row in set (0.00 sec)

mysql>


5. How To Grant User Privileges at the Global Level?


To grant all privileges to a user account over all databases, use the following command:
GRANT ALL PRIVILEGES ON *.* TO 'database_user'@'localhost';

mysql> create user raj@localhost identified by 'Rajasekhar_123%';
Query OK, 0 rows affected (0.01 sec)

mysql>

mysql> SHOW GRANTS FOR 'raj'@'localhost';
+-----------------------------------------+
| Grants for raj@localhost                |
+-----------------------------------------+
| GRANT USAGE ON *.* TO `raj`@`localhost` |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql>


mysql> GRANT CREATE ON *.* TO 'raj'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'raj'@'localhost';
+------------------------------------------+
| Grants for raj@localhost                 |
+------------------------------------------+
| GRANT CREATE ON *.* TO `raj`@`localhost` |
+------------------------------------------+
1 row in set (0.00 sec)

mysql>


[root@rac1 ~]# mysql -u raj -h localhost -pRajasekhar_123%
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| raj@localhost  |
+----------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR CURRENT_USER();
+------------------------------------------+
| Grants for raj@localhost                 |
+------------------------------------------+
| GRANT CREATE ON *.* TO `raj`@`localhost` |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| dell               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> use test;
Database changed

mysql> select database ();
+-------------+
| database () |
+-------------+
| test        |
+-------------+
1 row in set (0.00 sec)

mysql> 

mysql> create table test_raj (
    -> trans_id smallint,
    -> trans varchar(5),
    -> primary key (trans_id))
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql>

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test_raj       |
+----------------+
1 row in set (0.00 sec)

mysql>

mysql> INSERT INTO test_raj values(1,'SUGI');
ERROR 1142 (42000): INSERT command denied to user 'raj'@'localhost' for table 'test_raj'
mysql>

mysql> use dell;   <---- Login to other database and try to create table
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>
mysql> select database ();
+-------------+
| database () |
+-------------+
| dell        |
+-------------+
1 row in set (0.00 sec)

mysql> create table test_raj (
    -> trans_id smallint,
    -> trans varchar(5),
    -> primary key (trans_id))
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+----------------+
| Tables_in_dell |
+----------------+
| test           |
| test_raj       |
+----------------+
2 rows in set (0.00 sec)

mysql> create table test1 (
    -> trans_id smallint,
    -> trans varchar(5),
    -> primary key (trans_id))
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+----------------+
| Tables_in_dell |
+----------------+
| test           |
| test1          |
| test_raj       |
+----------------+
3 rows in set (0.00 sec)

mysql>


6. How To Grant User Privileges at the Database Level?


To grant all privileges to a user account over a specific database, use the following command:
GRANT ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost';

mysql> create database orcl;
Query OK, 1 row affected (0.00 sec)

mysql> create user lori@localhost identified by 'Lori123%';
Query OK, 0 rows affected (0.01 sec)

mysql> grant create on orcl.* to lori@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql>

[root@rac1 ~]# mysql -u lori -h localhost -pLori123%
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select current_user ();
+-----------------+
| current_user () |
+-----------------+
| lori@localhost  |
+-----------------+
1 row in set (0.00 sec)

mysql>

mysql> GRANT ALL ON orcl.* to lori@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql>


[root@rac1 ~]# mysql -u lori -h localhost -pLori123%
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select current_user ();
+-----------------+
| current_user () |
+-----------------+
| lori@localhost  |
+-----------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| orcl               |
+--------------------+
2 rows in set (0.00 sec)

mysql> use orcl;
Database changed
mysql> create table test_orcl (
    -> trans_id smallint,
    -> trans varchar(5),
    -> primary key (trans_id))
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+----------------+
| Tables_in_orcl |
+----------------+
| test_orcl      |
+----------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR CURRENT_USER();
+--------------------------------------------------------+
| Grants for lori@localhost                              |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO `lori`@`localhost`               |
| GRANT ALL PRIVILEGES ON `orcl`.* TO `lori`@`localhost` |
+--------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>


7. How To Grant User Privileges at the Table Level?

GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.table_name TO database_user@'localhost';

mysql> SHOW GRANTS FOR lori@localhost;
+--------------------------------------------------------+
| Grants for lori@localhost                              |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO `lori`@`localhost`               |
| GRANT ALL PRIVILEGES ON `orcl`.* TO `lori`@`localhost` |
+--------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON orcl.test_orcl TO 'lori'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR lori@localhost;
+----------------------------------------------------------------------------------+
| Grants for lori@localhost                                                        |
+----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `lori`@`localhost`                                         |
| GRANT ALL PRIVILEGES ON `orcl`.* TO `lori`@`localhost`                           |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `orcl`.`test_orcl` TO `lori`@`localhost` |
+----------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql>


8. How To Grant User Privileges at the Column Level?

GRANT SELECT (`trans`), SHOW VIEW ON orcl.`test_orcl` to 'may'@`localhost`;
GRANT SELECT (`trans_id`), SHOW VIEW ON orcl.`test_orcl` to 'may'@`localhost`;

GRANT INSERT (`trans`), SHOW VIEW ON orcl.`test_orcl` to 'may'@`localhost`;

GRANT INSERT (`trans`) ON orcl.`test_orcl` to 'may'@`localhost`;



mysql> create user may@localhost identified by 'May1234%';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT CONCAT('GRANT SELECT (`', COLUMN_NAME, '`), SHOW VIEW ON orcl.`', TABLE_NAME, '` to ''may''@`localhost`;')
    -> FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE TABLE_SCHEMA = 'orcl' AND TABLE_NAME = 'test_orcl';
+------------------------------------------------------------------------------------------------------------+
| CONCAT('GRANT SELECT (`', COLUMN_NAME, '`), SHOW VIEW ON orcl.`', TABLE_NAME, '` to ''may''@`localhost`;') |
+------------------------------------------------------------------------------------------------------------+
| GRANT SELECT (`trans`), SHOW VIEW ON orcl.`test_orcl` to 'may'@`localhost`;                                |
| GRANT SELECT (`trans_id`), SHOW VIEW ON orcl.`test_orcl` to 'may'@`localhost`;                             |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

GRANT SELECT (`trans`), SHOW VIEW ON orcl.`test_orcl` to 'may'@`localhost`;
GRANT SELECT (`trans_id`), SHOW VIEW ON orcl.`test_orcl` to 'may'@`localhost`;

mysql> GRANT SELECT (`trans`), SHOW VIEW ON orcl.`test_orcl` to 'may'@`localhost`;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT (`trans_id`), SHOW VIEW ON orcl.`test_orcl` to 'may'@`localhost`;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'may'@`localhost`;
+------------------------------------------------------------------------------------------+
| Grants for may@localhost                                                                 |
+------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `may`@`localhost`                                                  |
| GRANT SELECT (`trans`, `trans_id`), SHOW VIEW ON `orcl`.`test_orcl` TO `may`@`localhost` |
+------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

mysql> select trans from orcl.test_orcl;
+-------+
| trans |
+-------+
| SUGI  |
| TEJU  |
| RAJ   |
| SOMU  |
| JANA  |
+-------+
5 rows in set (0.00 sec)

mysql>

mysql> GRANT INSERT (`trans`), SHOW VIEW ON orcl.`test_orcl` to 'may'@`localhost`;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> SHOW GRANTS FOR 'may'@`localhost`;
+------------------------------------------------------------------------------------------------------------+
| Grants for may@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `may`@`localhost`                                                                    |
| GRANT SELECT (`trans`, `trans_id`), INSERT (`trans`), SHOW VIEW ON `orcl`.`test_orcl` TO `may`@`localhost` |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

mysql> GRANT INSERT (`trans`) ON orcl.`test_orcl` to 'may'@`localhost`;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> SHOW GRANTS FOR 'may'@`localhost`;
+------------------------------------------------------------------------------------------------------------+
| Grants for may@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `may`@`localhost`                                                                    |
| GRANT SELECT (`trans`, `trans_id`), INSERT (`trans`), SHOW VIEW ON `orcl`.`test_orcl` TO `may`@`localhost` |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>


9. How To Grant User Privileges WITH GRANT OPTION?

mysql> use orcl;
Database changed
mysql>
mysql> create table new_test (
    -> trans_id smallint,
    -> trans varchar(5),
    -> primary key (trans_id))
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql>

mysql> INSERT INTO new_test values(1,'SUGI');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO new_test values(2,'TEJU');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO new_test values(3,'RAJ');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO new_test values(4,'SOMU');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO new_test values(5,'JANA');
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> create user bose@localhost identified by 'Bose_1234%';
Query OK, 0 rows affected (0.01 sec)

mysql> grant select on orcl.* to 'bose'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> SHOW GRANTS FOR 'bose'@'localhost';
+------------------------------------------------------------------+
| Grants for bose@localhost                                        |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `bose`@`localhost`                         |
| GRANT SELECT ON `orcl`.* TO `bose`@`localhost` WITH GRANT OPTION |
+------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>


10. How to Revoke User Privileges?


-- Global Level 
REVOKE ALL ON *.* FROM 'database_user'@'localhost';
REVOKE SELECT,INSERT,UPDATE,DELETE ON *.* FROM 'database_user'@'localhost';

-- Database Level
REVOKE ALL PRIVILEGES ON database_name.* FROM 'database_user'@'localhost';
REVOKE SELECT,INSERT,UPDATE,DELETE ON database_name.* FROM 'database_user'@'localhost';

-- Table Level
REVOKE ALL PRIVILEGES ON database_name.table_name FROM 'database_user'@'localhost';
REVOKE SELECT,INSERT,UPDATE,DELETE ON database_name.table_name FROM 'database_user'@'localhost';

-- Column Level
REVOKE SELECT,INSERT (`trans`) ON orcl.`test_orcl` FROM 'may'@`localhost`;
REVOKE INSERT (`trans`) ON orcl.`test_orcl` FROM 'may'@`localhost`;

-- Revoke With Grant Option 

mysql> SHOW GRANTS FOR 'bose'@'localhost';
+------------------------------------------------------------------+
| Grants for bose@localhost                                        |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `bose`@`localhost`                         |
| GRANT SELECT ON `orcl`.* TO `bose`@`localhost` WITH GRANT OPTION |
+------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

mysql> revoke grant option on orcl.* from bose@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW GRANTS FOR 'bose'@'localhost';
+------------------------------------------------+
| Grants for bose@localhost                      |
+------------------------------------------------+
| GRANT USAGE ON *.* TO `bose`@`localhost`       |
| GRANT SELECT ON `orcl`.* TO `bose`@`localhost` |
+------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Create MySQL Database

MySQL,

Create MySQL Database

Table of Contents
___________________________________________________________________________________________________

1. Login to MySQL
2. Create Database
3. Find create database syntax
4. Switch database
5. How to clear the mysql screen
6. Create Table
7. Direct connect to Database instead of switch
8. Select version of Database
___________________________________________________________________________________________________


1. Login to MySQL

[root@rac1 mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>


2. Create Database

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>
mysql>
mysql> create database dell;
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| dell               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql>


3. Show create database syntax

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql>


mysql> show create database dell;
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| dell     | CREATE DATABASE `dell` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>


4. Switch database

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql>
mysql> use dell;
Database changed
mysql>
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| dell       |
+------------+
1 row in set (0.00 sec)

mysql>


5. How to clear the mysql screen

mysql> system 'clear';
mysql>


6. Create Table

mysql> SELECT USER();
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| dell       |
+------------+
1 row in set (0.00 sec)

mysql>
mysql> show tables;
Empty set (0.00 sec)

mysql>

mysql> create table test (
    -> trans_id smallint,
    -> trans varchar(5),
    -> primary key (trans_id))
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql>

mysql> show tables;
+----------------+
| Tables_in_dell |
+----------------+
| test           |
+----------------+
1 row in set (0.00 sec)

mysql>

mysql> show create table test \G;
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `trans_id` smallint NOT NULL,
  `trans` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`trans_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>


mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

mysql>

mysql> INSERT INTO test values(1,'SUGI');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO test values(2,'TEJU');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO test values(3,'RAJ');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test values(4,'SOMU');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO test values(5,'JANA');
Query OK, 1 row affected (0.01 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.06 sec)

mysql>
mysql> select * from test;
+----------+-------+
| trans_id | trans |
+----------+-------+
|        1 | SUGI  |
|        2 | TEJU  |
|        3 | RAJ   |
|        4 | SOMU  |
|        5 | JANA  |
+----------+-------+
5 rows in set (0.00 sec)

mysql>


7. Direct connect to Database instead of switch

[root@rac1 mysql]# mysql -u root -p dell
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| dell       |
+------------+
1 row in set (0.00 sec)

mysql>


8. Select version of Database

[root@rac1 mysql]# mysql -u root -p dell
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| dell       |
+------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.23    |
+-----------+
1 row in set (0.00 sec)

mysql>

OR

[root@rac1 mysql]#  mysql -u root -p -e 'select version();'
Enter password:
+-----------+
| version() |
+-----------+
| 8.0.23    |
+-----------+
[root@rac1 mysql]#

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Install MySQL 8 on Linux 7 Using RPM Packages

MySQL, , , , ,

Install MySQL 8 on Linux 7 Using RPM Packages

Table of Contents
___________________________________________________________________________________________________

1. Download RPM Packages
2. Untar RPM Packages
3. Install MySQL 8 Using RPM Packages
4. Start MySQL service
5. grep ‘temporary password’ /var/log/mysqld.log
6. Login to MySQL using temporary password
7. Change the root@localhost password
8. List MySQL config files
___________________________________________________________________________________________________


1. Download RPM Packages


2. Untar RPM Packages

[root@rac1 ~]# cd mysql
[root@rac1 mysql]# ls -ltr
total 1009792
-rw-r--r--. 1 root root 895047680 Mar  6 14:49 mysql-8.0.23-1.el7.x86_64.rpm-bundle.tar
[root@rac1 mysql]# pwd
/root/mysql
[root@rac1 mysql]#

[root@rac1 mysql]# tar -xvf mysql-8.0.23-1.el7.x86_64.rpm-bundle.tar
mysql-community-client-8.0.23-1.el7.x86_64.rpm
mysql-community-client-plugins-8.0.23-1.el7.x86_64.rpm
mysql-community-common-8.0.23-1.el7.x86_64.rpm
mysql-community-devel-8.0.23-1.el7.x86_64.rpm
mysql-community-embedded-compat-8.0.23-1.el7.x86_64.rpm
mysql-community-libs-8.0.23-1.el7.x86_64.rpm
mysql-community-libs-compat-8.0.23-1.el7.x86_64.rpm
mysql-community-server-8.0.23-1.el7.x86_64.rpm
mysql-community-test-8.0.23-1.el7.x86_64.rpm
[root@rac1 mysql]#
[root@rac1 mysql]# ls -ltr
total 1883876
-rw-r--r--. 1 7155 31415  50078112 Dec 12 21:09 mysql-community-client-8.0.23-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415    242596 Dec 12 21:09 mysql-community-client-plugins-8.0.23-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415    635504 Dec 12 21:09 mysql-community-common-8.0.23-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415   8496724 Dec 12 21:09 mysql-community-devel-8.0.23-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415  23598680 Dec 12 21:09 mysql-community-embedded-compat-8.0.23-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415   4834112 Dec 12 21:09 mysql-community-libs-8.0.23-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415   1273816 Dec 12 21:09 mysql-community-libs-compat-8.0.23-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415 543646172 Dec 12 21:10 mysql-community-server-8.0.23-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415 262232676 Dec 12 21:12 mysql-community-test-8.0.23-1.el7.x86_64.rpm
-rw-r--r--. 1 root root  895047680 Mar  6 14:49 mysql-8.0.23-1.el7.x86_64.rpm-bundle.tar
[root@rac1 mysql]#


3. Install MySQL 8 Using RPM Packages

In most cases, you need to install the below packages to get functional, standard MySQL installation. 

mysql-community-server
mysql-community-client 
mysql-community-libs
mysql-community-common 
mysql-community-libs-compat 

To perform such a standard, basic installation, go to the folder that contains all those packages (and, preferably, no other RPM packages with similar names), and issue the following commands:

-- Common files for server and client libraries
[root@rac1 mysql]# rpm -ivh mysql-community-common-8.0.23-1.el7.x86_64.rpm
Preparing...                          ################################# [100%]
        package mysql-community-common-8.0.23-1.el7.x86_64 is already installed
[root@rac1 mysql]#

-- MySQL client applications plugins
[root@rac1 mysql]# rpm -ivh mysql-community-client-plugins-8.0.23-1.el7.x86_64.rpm
Preparing...                          ################################# [100%]
        package mysql-community-client-plugins-8.0.23-1.el7.x86_64 is already installed
[root@rac1 mysql]#

-- Shared libraries for MySQL database client applications
[root@rac1 mysql]# rpm -ivh mysql-community-libs-8.0.23-1.el7.x86_64.rpm
Preparing...                          ################################# [100%]
        package mysql-community-libs-8.0.23-1.el7.x86_64 is already installed
[root@rac1 mysql]#

-- Shared compatibility libraries for previous MySQL installations
[root@rac1 mysql]# rpm -ivh mysql-community-libs-compat-8.0.23-1.el7.x86_64.rpm
Preparing...                          ################################# [100%]
        package mysql-community-libs-compat-8.0.23-1.el7.x86_64 is already installed
[root@rac1 mysql]#

-- MySQL client applications and tools
[root@rac1 mysql]# rpm -ivh mysql-community-client-8.0.23-1.el7.x86_64.rpm
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-client-8.0.23-1.e################################# [100%]
[root@rac1 mysql]#

-- Database server and related tools
[root@rac1 mysql]# rpm -ivh mysql-community-server-8.0.23-1.el7.x86_64.rpm
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-server-8.0.23-1.e################################# [100%]
[root@rac1 mysql]#

[root@rac1 mysql]# rpm -qa | grep mysql
mysql-community-libs-compat-8.0.23-1.el7.x86_64
mysql-community-server-8.0.23-1.el7.x86_64
mysql-community-common-8.0.23-1.el7.x86_64
qt-mysql-4.8.7-9.el7_9.x86_64
mysql-community-client-8.0.23-1.el7.x86_64
mysql-community-libs-8.0.23-1.el7.x86_64
mysql-community-client-plugins-8.0.23-1.el7.x86_64
mysql80-community-release-el7-3.noarch
[root@rac1 mysql]#


4. Start MySQL service

[root@rac1 mysql]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: inactive (dead)
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html

Mar 06 12:51:27 rac1.rajasekhar.com systemd[1]: Starting MySQL Server...
Mar 06 12:51:41 rac1.rajasekhar.com systemd[1]: Started MySQL Server.
Mar 06 12:57:35 rac1.rajasekhar.com systemd[1]: Stopping MySQL Server...
Mar 06 12:57:36 rac1.rajasekhar.com systemd[1]: Stopped MySQL Server.
[root@rac1 mysql]#

[root@rac1 mysql]# systemctl start mysqld
[root@rac1 mysql]#

[root@rac1 mysql]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Sat 2021-03-06 15:25:12 +08; 8s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 12423 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 12506 (mysqld)
   Status: "Server is operational"
   CGroup: /system.slice/mysqld.service
           └─12506 /usr/sbin/mysqld

Mar 06 15:25:02 rac1.rajasekhar.com systemd[1]: Starting MySQL Server...
Mar 06 15:25:12 rac1.rajasekhar.com systemd[1]: Started MySQL Server.
[root@rac1 mysql]#


5. grep ‘temporary password’ /var/log/mysqld.log

cat /var/log/mysqld.log | grep password

OR

[root@rac1 mysql]# grep 'temporary password' /var/log/mysqld.log
2021-03-06T07:25:07.718556Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: &qixM=kt9wP+
[root@rac1 mysql]#


6. Login to MySQL using temporary password

[root@rac1 mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.23

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql>


7. Change the root@localhost password

mysql> alter user root@localhost identified by 'Mysql@123';
Query OK, 0 rows affected (0.01 sec)

mysql>

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql>

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql>


8. List MySQL config files

[root@rac1 ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

datadir=/var/lib/mysql   
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log         
pid-file=/var/run/mysqld/mysqld.pid
[root@rac1 ~]#

[root@rac1 ~]# cd /var/lib/mysql
[root@rac1 mysql]# ls -ltr
total 188880
-rw-r-----. 1 mysql mysql 50331648 Mar  6 15:25 ib_logfile1
-rw-r-----. 1 mysql mysql  8585216 Mar  6 15:25 #ib_16384_1.dblwr
drwxr-x---. 2 mysql mysql     8192 Mar  6 15:25 performance_schema
-rw-r-----. 1 mysql mysql       56 Mar  6 15:25 auto.cnf
-rw-------. 1 mysql mysql     1676 Mar  6 15:25 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Mar  6 15:25 ca.pem
-rw-------. 1 mysql mysql     1680 Mar  6 15:25 server-key.pem
-rw-r--r--. 1 mysql mysql     1112 Mar  6 15:25 server-cert.pem
-rw-------. 1 mysql mysql     1676 Mar  6 15:25 client-key.pem
-rw-r--r--. 1 mysql mysql     1112 Mar  6 15:25 client-cert.pem
-rw-r--r--. 1 mysql mysql      452 Mar  6 15:25 public_key.pem
-rw-------. 1 mysql mysql     1676 Mar  6 15:25 private_key.pem
drwxr-x---. 2 mysql mysql     4096 Mar  6 15:25 mysql
drwxr-x---. 2 mysql mysql       28 Mar  6 15:25 sys
-rw-r-----. 1 mysql mysql     5532 Mar  6 15:25 ib_buffer_pool
drwxr-x---. 2 mysql mysql     4096 Mar  6 15:25 #innodb_temp
-rw-r-----. 1 mysql mysql       16 Mar  6 15:25 binlog.index
-rw-------. 1 mysql mysql        6 Mar  6 15:25 mysql.sock.lock
srwxrwxrwx. 1 mysql mysql        0 Mar  6 15:25 mysql.sock
-rw-r-----. 1 mysql mysql 12582912 Mar  6 15:25 ibtmp1
-rw-r-----. 1 mysql mysql 16777216 Mar  6 15:27 undo_001
-rw-r-----. 1 mysql mysql 25165824 Mar  6 15:34 mysql.ibd
-rw-r-----. 1 mysql mysql 12582912 Mar  6 15:34 ibdata1
-rw-r-----. 1 mysql mysql      643 Mar  6 15:35 binlog.000001
-rw-r-----. 1 mysql mysql   196608 Mar  6 15:36 #ib_16384_0.dblwr
-rw-r-----. 1 mysql mysql 16777216 Mar  6 15:36 undo_002
-rw-r-----. 1 mysql mysql 50331648 Mar  6 15:36 ib_logfile0
[root@rac1 mysql]#

[root@rac1 mysql]# ls -ld */
drwxr-x---. 2 mysql mysql 4096 Mar  6 15:25 #innodb_temp/
drwxr-x---. 2 mysql mysql 4096 Mar  6 15:25 mysql/
drwxr-x---. 2 mysql mysql 8192 Mar  6 15:25 performance_schema/
drwxr-x---. 2 mysql mysql   28 Mar  6 15:25 sys/
[root@rac1 mysql]#


mysql directory:
The mysql directory corresponds to the mysql system schema, which contains information required by the MySQL server as it runs. This database contains data dictionary tables and system tables.

performance_schema directory:
The performance_schema directory corresponds to the Performance Schema, which provides information used to inspect the internal execution of the server at runtime.

sys directory:
The sys directory corresponds to the sys schema, which provides a set of objects to help interpret Performance Schema information more easily.

#innodb_temp directory:
InnoDB uses session temporary tablespaces and a global temporary tablespace.

ndbinfo directory:
The ndbinfo directory corresponds to the ndbinfo database that stores information specific to NDB Cluster (present only for installations built to include NDB Cluster).

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

MySQL DBA

MySQL
  1. Install MySQL 8 on Linux 7 Using RPM Packages
  2. Create MySQL Database
  3. Create New User Account in MySQL
  4. Grant/Revoke Privileges in MySQL
  5. Create Roles in MySQL

MySQL – Backup/Restore 

  1. How To Backup MySQL Database Using mysqldump?
  2. How to Restore Database from Single MySQL Database Backup?
  3. How to Restore A Single Database From A Backup File Which Contains Multiple Databases?
  4. How To Restore Single Table From Full Database Backup in MySQL?

 

MySQL – Replication

  1. How To Configure MySQL Master-Slave Replication? (One-Way Replication)