-- List all users present in mysql
mysql> select user,host,account_locked,password_expired from mysql.user;
Create user in mysql:
mysql> CREATE USER 'test_user'@'%' IDENTIFIED BY 'test_user';
Query OK, 0 rows affected (0.02 sec)
mysql> GRANT ALL ON *.* TO 'user'@'localhost';
Query OK, 0 rows affected (0.01 sec)
View create user statements:
MariaDB [(none)]> SHOW CREATE USER 'test_user'@'localhost';
MariaDB [(none)]> SHOW CREATE USER 'test_user'@'%';
Rename a user:
mysql> RENAME USER 'dbatest' TO 'dbaprod';
Query OK, 0 rows affected (0.02 sec)
change password of a user:
mysql> ALTER USER 'dbaprod'@'%' IDENTIFIED BY 'dbaprod';
Query OK, 0 rows affected (0.01 sec)
Change resource option:
mysql> alter user 'dbaprod'@'%' with MAX_USER_CONNECTIONS 10;
Query OK, 0 rows affected (0.01 sec)
Lock/unlock an account:
mysql> alter user 'dbaprod'@'%' account lock;
Query OK, 0 rows affected (0.01 sec)
mysql> alter user 'dbaprod'@'%' account unlock;
Query OK, 0 rows affected (0.01 sec)
Make sure you are connected with root/admin user:
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
Create normal user if not created.
mysql> CREATE user 'super'@'%' IDENTIFIED BY 'super';
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE user 'super'@'localhost' IDENTIFIED BY 'super';
Query OK, 0 rows affected (0.02 sec)
Grant privileges for making it super user:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'super'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.03 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'super'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.03 sec)
Reload all the privileges
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)
View the grants:
mysql> show grants for super;
Examples on GRANT command
GRANT ALL PRIVILEGES ON *.* to 'user_name'@'localhost';
GRANT ALL PRIVILEGES ON *.* to 'user_name'@'%';
GRANT ALL PRIVILEGES ON *.* to 'user_name'@'localhost' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* to 'user_name'@'%' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON dbname.* to 'user_name'@'localhost';
GRANT ALL PRIVILEGES ON dbname.* to 'user_name'@'%';
GRANT INSERT,UPDATE,DELETE ON TABLE DBNAME.TABLENAME TO 'user_name'@'localhost';
GRANT INSERT,UPDATE,DELETE ON TABLE DBNAME.TABLENAME TO 'user_name'@'%';
GRANT CREATE ON DATABASE.* to 'user_name'@'localhost';
GRANT CREATE ON DATABASE.* to 'user_name'@'%';
GRANT SELECT , EXECUTE ON DATABASE.* to 'user_name'@'%';
GRANT SELECT , EXECUTE ON DATABASE.* to 'user_name'@'localhost';
REVOKE privileges ON <object> FROM user;
REVOKE DELETE, UPDATE ON <table_name> FROM 'user'@'localhost';
REVOKE DELETE, UPDATE ON <table_name> FROM 'user'@'%';
REVOKE ALL ON <table_name> FROM 'user'@'localhost';
REVOKE ALL ON <table_name> FROM 'user'@'%';
REVOKE SELECT ON <object> FROM 'username'@'localhost';
REVOKE SELECT ON <object> FROM 'username'@'localhost';
show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+-----
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+----
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
Create a database in MySQL / MariaDB
-- Below commands can be used to create database
MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.009 sec)
MariaDB [(none)]> create database testdb1 character set UTF8mb4 collate utf8mb4_bin;
Query OK, 1 row affected (0.009 sec)
-- View the create database statement used for creating db
MariaDB [(none)]> SHOW CREATE DATABASE testdb1;
+----------+-----------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------------------------------+
| testdb1 | CREATE DATABASE `testdb1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */ |
+----------+-----------------------------------------------------------------------------------------+
1 row in set (0.007 sec)
View database list
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| testdb |
+--------------------+
5 rows in set (0.006 sec)
mysql> SELECT * fROM information_schema.schemata;
+--------------+--------------------+----------------------------+------------------------+----------+--------------------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | DEFAULT_ENCRYPTION |
+--------------+--------------------+----------------------------+------------------------+----------+--------------------+
| def | mysql | utf8mb4 | utf8mb4_0900_ai_ci | NULL | NO |
mysql> show databases like 'test%';
+------------------+
| Database (test%) |
+------------------+
| test |
How to connect to MySQL / MariaDB database
hostname$ export PATH=/usr/local/mysql/bin:$PATH
hostname$ which mysql
/usr/local/mysql/bin
SYNTAX - mysql -u user -p
C:\Program Files\MariaDB 10.4\bin>mysql -u root -p
Enter password: ****
Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 19.Server version: 10.4.18-MariaDB
Find current connection info:
MariaDB [(none)]> \s
--------------
Below is the alternative command:
MariaDB [(none)]> status;
Get current user and current database:
mariadb> select current_user,database();
+----------------+------------+
| current_user | database() |
+----------------+------------+
| root@localhost | test |
+----------------+------------+
1 row in set (0.00 sec)
switch to another database:
MariaDB> USE TEST;
Database changed
MariaDB> select database();
Drop database from mysql /MariaDB
C:\Program Files\MariaDB 10.4\bin>mysql -u root -p
Enter password: ****
-- List down databases
MariaDB [(none)]> show databases;
+--------------------+
| Database. |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| testdb |
| testdb1 |
+--------------------+
6 rows in set (0.001 sec)
-- Check the status:
MariaDB [(none)]> status
-- Drop database:
MariaDB [(none)]> DROP DATABASE testdb;
SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 / 1024 AS "Size (GB)" FROM information_schema.TABLES GROUP BY table_schema;
SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;
How to find timezone info
Check whether time_zone table is updated or not.
MariaDB [(none)]> select * from mysql.time_zone_name;
Empty set (0.000 sec)
Find current sessions/process in MYSQL/MARIA DB
Show all processes in mysql cluster.
mysql> show processlist;
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 34888 | Waiting on empty queue | NULL |
| 23 | root | localhost | test | Sleep. | 36 | | NULL |
| 24 | root. | localhost | NULL | Query | 0 | init | show processlist |
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
3 rows in set (0.00 sec)
Show processes for a specific user:
mysql> select * from information_schema.processlist where user='root'\G
Show processes for a particular database:
mysql> select * from information_schema.processlist where DB='test';
+----+------+-----------+------+---------+------+-------+------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+----+------+-----------+------+---------+------+-------+------+
| 23 | root | localhost | test | Sleep | 70 | | NULL |
+----+------+-----------+------+---------+------+-------+------+
1 row in set (0.00 sec)
Get the processid for the session:
mysql> show processlist;
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
| Id | User | Host. | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 35192 | Waiting on empty queue | NULL |
| 24 | root | localhost | NULL | Query | 0 | init | show processlist |
| 25 | root | localhost | test | Sleep. | 6 | | NULL |
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
3 rows in set (0.00 sec)
mysql> kill 25;
Query OK, 0 rows affected (0.00 sec)
mysql> show processlist;
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
| Id | User | Host. | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 35192 | Waiting on empty queue | NULL |
| 24 | root | localhost | NULL | Query | 0 | init
How to kill all sessions of an user
mysql> show processlist;
+----+-----------------+-----------+--------+---------+-------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+--------+---------+-------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 36418 | Waiting on empty queue | NULL |
| 24 | root | localhost | NULL | Query | 0 | init | show processlist |
| 26 | root | localhost | test | Sleep | 376 | | NULL |
| 27 | root | localhost | testdb. | Sleep | 347 | | NULL |
+----+-----------------+-----------+--------+---------+-------+------------------------+------------------+
4 rows in set (0.00 sec)
Generate kill statements for killing all sessions of user root
mysql> SELECT GROUP_CONCAT(CONCAT('KILL ',id,';') SEPARATOR ' ') 'Run the kill statements ----->>> ' FROM information_schema.processlist WHERE user='root'\G
*************************** 1. row ***************************
Run the kill statements ----->>> : KILL 24; KILL 26; KILL 27;
1 row in set (0.00 sec)
mysql> KILL 24; KILL 26; KILL 27;
Uptime of server
MariaDB [(none)]> status;
--------------
mysql Ver 15.1 Distrib 10.4.18-MariaDB, for Win64 (AMD64), source revision 53123dfa3e365138591fd2f160c6057aca00a3e6
Connection id: 26
Current database:
.............
TCP port: 3306
Uptime: 4 days 19 hours 28 min 14 sec
Alternative command
MariaDB [(none)]> \s
Server startup time:
MariaDB [(none)]> select TIME_FORMAT(SEC_TO_TIME(VARIABLE_VALUE ),'%Hh %im') as Uptime from information_schema.GLOBAL_STATUS where VARIABLE_NAME='Uptime';
+----------+
| Uptime. |
+----------+m
| 115h 30m |
+----------+
1 row in set (0.007 sec)
C:\Program Files\MariaDB 10.4\bin>mysqladmin version -u root -p
Enter password: ****
mysqladmin Ver 9.1 Distrib 10.4.18-MariaDB, for Win64 on AMD64 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Server version 10.4.18-MariaDB
Protocol version 10
Connection localhost via TCP/IP
TCP port 3306
Uptime: 4 days 19 hours 33 min 2 sec
MariaDB [(none)]> show variables like '%data%';
+---------------------------------------------+-------------------------------------+
| Variable_name | Value |
+---------------------------------------------+-------------------------------------+
| datadir | /usr/local/mysql/data/ |
MariaDB [(none)]> SELECT Variable_Value FROM information_schema.GLOBAL_VARIABLES WHERE Variable_Name = 'datadir';
+-------------------------------------+
| Variable_Value |
+-------------------------------------+
| /usr/local/mysql/data/ |
+-------------------------------------+
1 row in set (0.001 sec)
mysql> select @@datadir;
+------------------------+
| @@datadir |
+------------------------+
| /usr/local/mysql/data/ |
+------------------------+
1 row in set (0.00 sec)
Find current data/time on MySQL / MariaDB
mysql> select CURRENT_TIMESTAMP,current_date,localtime(0),localtimestamp(0);
Find MySQL / MariaDB configuration values
1 . Get Config values from mysql prompt.
MariaDB [(none)]> SELECT VARIABLE_NAME,VARIABLE_VALUE FROM information_schema.GLOBAL_VARIABLES;
MariaDB [(none)]> SELECT VARIABLE_NAME,VARIABLE_VALUE FROM information_schema.GLOBAL_VARIABLES WHERE Variable_Name = 'port';
+---------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+----------------+
| PORT | 3306 |
+---------------+----------------+
1 row in set (0.001 sec)
MariaDB [(none)]> SELECT VARIABLE_NAME,VARIABLE_VALUE FROM information_schema.GLOBAL_VARIABLES WHERE Variable_Name = 'datadir';
+---------------+-------------------------------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+-------------------------------------+
| DATADIR | C:\Program Files\MariaDB 10.4\data\ |
+---------------+-------------------------------------+
1 row in set (0.001 sec)
2. Alternatively you can check my.cnf file in /etc folder in Linux
cat /etc/my.cnf
Find the last MySQL / MariaDB Database service restarted / server reboot time
MariaDB [(none)]> status
--------------
mysql Ver 15.1 Distrib 10.4.18-MariaDB, for Win64 (AMD64), source revision 53123dfa3e365138591fd2f160c6057aca00a3e6
Connection id: 33
Current database:
Current user: root@localhost
SSL: Not in use
...........
Uptime: 6 days 2 hours 3 min 3 sec
-- In older version:
MariaDB [(none)]> select TIME_FORMAT(SEC_TO_TIME(VARIABLE_VALUE ),'%Hh %im') as Uptime from information_schema.GLOBAL_STATUS where VARIABLE_NAME='Uptime';
+----------+
| Uptime |
+----------+
| 146h 03m |
+----------+
1 row in set (0.009 sec)
-- In latest mysql version:
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE 'Uptime';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Uptime | 525859 |
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+-----
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+----
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO
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 = 'kellogg_xalientkdndb'
-- WHERE table_schema in ('huedb', 'rangerdb', 'rangerkmsdb', 'hivedb', 'scm')
-- WHERE table_schema in ('metastore','hive','hue','rangerdb','ranger','rangerkmsdb','scm')
GROUP BY table_schema
order by 1;
owner of the table:
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, CREATE_USER
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'your_table_name';
to change the ownership:
MYDB.SCHEMA(USER)=> ALTER TABLE weather OWNER TO xyz;
No comments:
Post a Comment