Database Technology

Monday, December 27, 2021

command to get the parameter values in MySQL?

mysql> SHOW VARIABLES LIKE '%slow_query_log%';

--------------

SHOW VARIABLES LIKE '%slow_query_log%'

--------------

+---------------------+------------------------+

| Variable_name | Value |

+---------------------+------------------------+

| slow_query_log | ON |

| slow_query_log_file | <server_name>-slow.log |

+---------------------+------------------------+

2 rows in set (0.00 sec)

How to copy a table using CTAS (create Table as Select from ) in MySQL

Syntax for CTAS:

create table <db_name.table_name> as select * from <db_name.table_name> ;

mysql> create table test3.reports_bak as select * from reports;

--------------

create table test3.reports_bak as select * from reports

--------------

Query OK, 55 rows affected (0.04 sec)

Records: 55 Duplicates: 0 Warnings: 0

mysql> use test3;

Database changed

mysql> show tables;

--------------

show tables

--------------

+-----------------+

| Tables_in_test3 |

+-----------------+

| reports_bak |

+-----------------+

1 row in set (0.00 sec)

create/Drop database in MySQL?

mysql> create database test1;

when you create DB on ,it will create directory under data with db name and under this Directory all the tables will be added.

--------------

create database test1

--------------

Query OK, 1 row affected (0.00 sec)

mysql> SELECT schema_name FROM information_schema.schemata;

--------------

SELECT schema_name FROM information_schema.schemata

--------------

+------------------------+

| SCHEMA_NAME |

+------------------------+

| mysql |

| information_schema |

| performance_schema |

| sys |

| aaabbbb_dba_management |

| aaabbbb_xxxxxxtdb |

| xxxxxxt |

| aaabbbb_xxxxxxtkdndb |

| test3 |

| test1 |

+------------------------+

10 rows in set (0.00 sec)

mysql> drop database test1;

--------------

drop database test1

--------------

Query OK, 0 rows affected (0.01 sec)

mysql> SELECT schema_name FROM information_schema.schemata;

--------------

SELECT schema_name FROM information_schema.schemata

--------------

+------------------------+

| SCHEMA_NAME |

+------------------------+

| mysql |

| information_schema |

| performance_schema |

| sys |

| aaabbbb_dba_management |

| aaabbbb_xxxxxxtdb |

| xxxxxxt |

| aaabbbb_xxxxxxtkdndb |

| test3 |

+------------------------+

9 rows in set (0.00 sec)

create/drop/Alter user in MySQL database?

The following is the basic syntax to create a new user in MySQL:

CREATE USER testmysqlusr IDENTIFIED BY 'petcom123';

***********************************************************

mysql> CREATE USER testmysqlusr IDENTIFIED BY 'petcom123';

--------------

CREATE USER testmysqlusr IDENTIFIED BY 'petcom123'

--------------

Query OK, 0 rows affected (0.04 sec)

ALTER USER 'testmysqlusr'@'localhost' IDENTIFIED BY 'macd123';

Query OK, 0 rows affected (0.04 sec)

mysql>SHOW GRANTS FOR 'testmysqlusr'@'localhost';

mysql> SHOW GRANTS FOR 'testmysqlusr'@'localhost';

--------------

SHOW GRANTS FOR 'testmysqlusr'@'localhost'

--------------

ERROR 1141 (42000): There is no such grant defined for user 'testmysqlusr' on host 'localhost'

mysql>

mysql> drop user testmysqlusr;

--------------

drop user testmysqlusr

--------------

Query OK, 0 rows affected (0.01 sec)

How to check the MySQL version?

How to check the MySQL version?

We can check the MySQL version on Linux using the below command:

mysql -v

"E:\MySQL8\MySQL\MySQL Server 8.0\bin\mysql.exe" -v -u aaadbadmin -p -h localhost -P 3430

C:\Users\_SQLAdmin>"E:\MySQL8\MySQL\MySQL Server 8.0\bin\mysql.exe" -v -u aaadbadmin -p -h localhost -P 3430

Enter password: **********

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 204130

Server version: 8.0.22 MySQL Community Server - GPL

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>

How to view the database name in MySQL?

mysql> SELECT schema_name FROM information_schema.schemata;

--------------

SELECT schema_name FROM information_schema.schemata

--------------

+------------------------+

| SCHEMA_NAME |

+------------------------+

| mysql |

| information_schema |

| performance_schema |

| sys |

| aaabbbb_dba_management |

| aaabbbb_xxxxxxtdb |

| xxxxxxt |

| aaabbbb_xxxxxxtkdndb |

| test3 |

+------------------------+

9 rows in set (0.00 sec)

mysql> select db,user from mysql.db;

+------------------------+---------------+

| db | user |

+------------------------+---------------+

| aaabbbb_dba_management | aaadbadmin |

| performance_schema | mysql.session |

| sys | mysql.sys |

| aaabbbb_xxxxxxtdb | xxxxxxt_admin |

| aaabbbb_xxxxxxtkdndb | xxxxxxt_admin |

| xxxxxxt | xxxxxxt_admin |

+------------------------+---------------+

6 rows in set (0.00 sec)

mysql> show databases;

+------------------------+

| Database |

+------------------------+

| information_schema |

| aaabbbb_dba_management |

| aaabbbb_xxxxxxtdb |

| aaabbbb_xxxxxxtkdndb |

| mysql |

| performance_schema |

| sys |

| test3 |

| xxxxxxt |

+------------------------+

9 rows in set (0.00 sec)

Friday, December 24, 2021

How to check user privileges in MySQL database?

mysql> SHOW GRANTS FOR 'root'@'localhost';

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Grants for root@localhost |

| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION |

| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION |

| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |

3 rows in set (0.00 sec)

mysql> select super_priv from mysql.user where user='mysqldbadmin';

+------------+

| super_priv |

+------------+

| Y |

+------------+

1 row in set (0.00 sec)

script to check database size in MySQL?

script to check database size in MySQL

SELECT table_schema AS 'Database Name',

SUM(data_length + index_length) 'Size in Bytes',

ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) 'Size in MB'

FROM information_schema.tables

WHERE table_schema = 'mysql'

GROUP BY table_schema;

+---------------+---------------+------------+

| Database Name | Size in Bytes | Size in MB |

+---------------+---------------+------------+

| mysql | 5292032 | 5.05 |

+---------------+---------------+------------+

1 row in set (0.00 sec)

Tuesday, December 21, 2021

How to stop /start /status of PostgreSQL service on linux server

How to stop /start /status of PostgreSQL service

<linux_server>>:/postgresql_SW> psql --version

psql (PostgreSQL) 12.1pg_ctl status

<linux_server>>:/postgresql_SW>pg_ctl: server is running (PID: 16915)

/usr/pgsql-11/bin/postgres "-D" "/var/lib/pgsql/11/data"

<linux_server>>:/postgresql_SW> pg_ctl stop

waiting for server to shut down.... done

server stopped

<linux_server>>:/postgresql_SW> pg_ctl status

pg_ctl: no server running

<linux_server>>:/postgresql_SW> pg_ctl start

waiting for server to start....2021-09-13 13:16:43.724 UTC [24156] LOG: listening on IPv4 address "0.0.0.0", port 5432

2021-09-13 13:16:43.724 UTC [24156] LOG: listening on IPv6 address "::", port 5432

2021-09-13 13:16:43.725 UTC [24156] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"

2021-09-13 13:16:43.731 UTC [24156] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"

2021-09-13 13:16:43.747 UTC [24156] LOG: redirecting log output to logging collector process

2021-09-13 13:16:43.747 UTC [24156] HINT: Future log output will appear in directory "log".

done

server started

<linux_server>>:/postgresql_SW> pg_ctl -D /var/lib/pgsql/11/data stop

waiting for server to shut down.... done

server stopped

<linux_server>>:/postgresql_SW> pg_ctl -D /var/lib/pgsql/11/data status

pg_ctl: no server running

<linux_server>>:/postgresql_SW> pg_ctl -D /var/lib/pgsql/11/data start

waiting for server to start....2021-09-13 13:17:19.374 UTC [24204] LOG: listening on IPv4 address "0.0.0.0", port 5432

2021-09-13 13:17:19.374 UTC [24204] LOG: listening on IPv6 address "::", port 5432

2021-09-13 13:17:19.376 UTC [24204] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"

2021-09-13 13:17:19.381 UTC [24204] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"

2021-09-13 13:17:19.397 UTC [24204] LOG: redirecting log output to logging collector process

2021-09-13 13:17:19.397 UTC [24204] HINT: Future log output will appear in directory "log".

done

server started

how to get DDL of table in postgreSQL using pg_dump

how to get DDL of table in PostgreSQL

********************************************

<linux_server>:/var/opt/pgsql11/data/tablespace/app_tablespace> pg_dump -t 'public.phonebook' -d testdb

-- PostgreSQL database dump

-- Dumped from database version 11.6

-- Dumped by pg_dump version 12.1

SET statement_timeout = 0;

SET lock_timeout = 0;

SET idle_in_transaction_session_timeout = 0;

SET client_encoding = 'SQL_ASCII';

SET standard_conforming_strings = on;

SELECT pg_catalog.set_config('search_path', '', false);

SET check_function_bodies = false;

SET xmloption = content;

SET client_min_messages = warning;

SET row_security = off;

SET default_tablespace = '';

-- Name: phonebook; Type: TABLE; Schema: public; Owner: postgres

CREATE TABLE public.phonebook (

phone character varying(32),

firstname character varying(32),

lastname character varying(32),

address character varying(64)

);

ALTER TABLE public.phonebook OWNER TO postgres;

-- Data for Name: phonebook; Type: TABLE DATA; Schema: public; Owner: postgres

COPY public.phonebook (phone, firstname, lastname, address) FROM stdin;

+1 123 456 7890 John Doe North America

-- PostgreSQL database dump complete