Database Technology: December 2021

Monday, December 27, 2021

script to check mysql db user locked status

SELECT

user, host, account_locked

FROM

mysql.user

WHERE

user = 'mysqluser' ;

mysql> CREATE USER mysqluser

-> IDENTIFIED BY '5password67'

-> ACCOUNT LOCK;

Query OK, 0 rows affected (2.10 sec)

mysql> SELECT

-> user, host, account_locked

-> FROM

-> mysql.user

-> WHERE

-> user = 'mysqluser' AND

-> host='localhost';

Empty set (0.00 sec)

mysql> SELECT

-> user, host, account_locked

-> FROM

-> mysql.user

-> WHERE

-> user = 'mysqluser' ;

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

| user | host | account_locked |

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

| mysqluser | % | Y |

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

1 row in set (0.00 sec)

If the mysql server is performing slow than how to find out the process which is causing problem.

Answer: Best way is to check with: ‘SHOW PROCESSLIST / SHOW ENGINE INNODB STATUS

script to check the uptime of a mysql database server?

SELECT
  VARIABLE_VALUE AS Uptime_seconds,
  NOW() AS "Now",
  NOW() - INTERVAL VARIABLE_VALUE SECOND AS "Up since",
  DATEDIFF(NOW(), NOW() - INTERVAL VARIABLE_VALUE SECOND) AS "Uptime_days"
FROM performance_schema.session_status
WHERE VARIABLE_NAME = 'Uptime';

mysql> SELECT

-> VARIABLE_VALUE AS Uptime_seconds,

-> NOW() AS "Now",

-> NOW() - INTERVAL VARIABLE_VALUE SECOND AS "Up since",

-> DATEDIFF(NOW(), NOW() - INTERVAL VARIABLE_VALUE SECOND) AS "Uptime_days"

-> FROM performance_schema.session_status

-> WHERE VARIABLE_NAME = 'Uptime';

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

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

| 100752 | 2021-12-27 10:02:34 | 2021-12-26 06:03:22.000000 | 1 |

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

1 row in set (0.01 sec)

mysql> SHOW GLOBAL STATUS LIKE '%UPTIME%';

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

| Variable_name | Value |

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

| Uptime | 100776 |

| Uptime_since_flush_status | 100776 |

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

2 rows in set (0.00 sec)

What are the different database engine present in MySQL?

different database engine present in MySQL

MyISAM

Heap

Merge

INNO DB

ISAM

What are the advantages of MyISAM over InnoDB?

MyISAM follows a conservative approach to disk space management and stores each MyISAM table in a separate file, which can be further compressed if required. On the other hand, InnoDB stores the tables in the tablespace. Its further optimization is difficult.

What is InnoDB?

InnoDB is a storage database for SQL. The ACID-transactions are also provided in InnoDB and also includes support for the foreign key. Initially owned by InnobaseOY now belongs to Oracle Corporation after it acquired the latter since 2005.

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

What is ISAM?

It is a system for file management developed by IBM, which allows records to access sequentially or even randomly.

How to grant super user privilege to user in MySQL?

Grant All Privilege's On All Databases

GRANT ALL PRIVILEGES ON * . * TO 'someuser'@'localhost';

FLUSH PRIVILEGES;

Show Grants

SHOW GRANTS FOR 'someuser'@'localhost';

How to list the tables in MySQL ?

SELECT table_name 
FROM information_schema.tables
WHERE table_schema = 'your_db_name';

mysql> use mysql;

Database changed

mysql> show tables;

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

| Tables_in_mysql |

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

| columns_priv |

| component |

| db |

| default_roles |

| engine_cost |

command to spool query output to another log file in MySQL

Spooling SQL query Output in a File in MySQL & MariaDB

mysql> use testdb;

Database changed

mysql> tee testspool.txt;

Logging to file 'testspool.txt;'

mysql> select name from emp;

mysql> notee

Outfile disabled.

commands in MySQL to get the current user and date

mysql> SELECT CURRENT_DATE();

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

| CURRENT_DATE() |

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

| 2021-12-24 |

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

1 row in set (0.00 sec)

mysql> select current_user();

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

| current_user() |

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

| aaadbadmin@% |

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

1 row in set (0.00 sec)

mysql> select @@datadir;

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

select @@datadir

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

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

| @@datadir |

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

| E:\MySQL8\MySQL\MySQL Server 8.0\Data\ |

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

1 row in set (0.00 sec)

to know the current database

mysql> SELECT DATABASE()

-> ;

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

| DATABASE() |

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

| information_schema |

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

1 row in set (0.00 sec)

How to start/stop MySQL services in windows and Linux servers

On Windows

If you are using Windows Open the Command Prompt and type

To Stop MySQL Service:

net stop MySQL80

To Start MySQL Service:

net start MySQL80

On Linux

# /etc/init.d/mysqld start

# /etc/init.d/mysqld stop

# /etc/init.d/mysqld restart

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