1. Connect mysql instance.
[root@xcell ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.28-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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. Go for password less login for root.
Create file .my.cnf under home directory of root.
[root@xcell ~]# cat .my.cnf
[client]
password = XXXXXXXXXX
socket = /u01/mysql/mysql-5.7.28-el7-x86_64/mysql.sock
[root@xcell ~]#
[root@xcell ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.28-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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>
3. See databases or schema resdes under mysql server
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |---- all db object info etc
| mydb |
| mysql |---- Default database store all user db info
| performance_schema |---- gather performance data
| sys |
+--------------------+
5 rows in set (0.03 sec)
4. Select database for operation
mysql> use mydb;
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 database();
+------------+
| database() |
+------------+
| mydb |
+------------+
1 row in set (0.00 sec)
mysql>
5. See tables under mydb database
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| CORONA |
| DEPT |
| state_wise |
| travel_history |
+----------------+
4 rows in set (0.00 sec)
mysql>
6. see all connection details;
mysql> show processlist;
+----+---------+-----------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+---------+-----------+------+---------+------+----------+------------------+
| 4 | oggadm1 | localhost | mydb | Sleep | 1739 | | NULL |
| 5 | oggadm1 | localhost | NULL | Sleep | 1740 | | NULL |
| 9 | root | localhost | mydb | Query | 0 | starting | show processlist |
+----+---------+-----------+------+---------+------+----------+------------------+
3 rows in set (0.10 sec)
[root@xcell ~]# mysqladmin processlist -p
Enter password:
+----+---------+-----------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+---------+-----------+------+---------+------+----------+------------------+
| 4 | oggadm1 | localhost | mydb | Sleep | 1871 | | |
| 5 | oggadm1 | localhost | | Sleep | 1872 | | |
| 15 | root | localhost | | Query | 0 | starting | show processlist |
+----+---------+-----------+------+---------+------+----------+------------------+
[root@xcell ~]#
7. See status of server
[root@xcell ~]# mysqladmin status
Uptime: 6256 Threads: 3 Questions: 134 Slow queries: 0 Opens: 157 Flush tables: 1 Open tables: 150 Queries per second avg: 0.021
8. Server version
[root@xcell ~]# mysqladmin version
mysqladmin Ver 8.42 Distrib 5.7.28, for el7 on x86_64
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Server version 5.7.28-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /u01/mysql/mysql-5.7.28-el7-x86_64/mysql.sock
Uptime: 1 hour 45 min 41 sec
Threads: 3 Questions: 138 Slow queries: 0 Opens: 157 Flush tables: 1 Open tables: 150 Queries per second avg: 0.021
[root@xcell ~]#
mysql> status
--------------
mysql Ver 14.14 Distrib 5.7.28, for el7 (x86_64) using EditLine wrapper
Connection id: 17
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.28-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /u01/mysql/mysql-5.7.28-el7-x86_64/mysql.sock
Uptime: 1 hour 46 min 4 sec
Threads: 3 Questions: 143 Slow queries: 0 Opens: 157 Flush tables: 1 Open tables: 150 Queries per second avg: 0.022
--------------
mysql>
[root@xcell ~]# mysqladmin -u root -p ping
Enter password:
mysqld is alive
[root@xcell ~]#
8. Find connected user details
mysql> select Host,user,account_locked,plugin from mysql.user;
+-----------+---------------+----------------+-----------------------+
| Host | user | account_locked | plugin |
+-----------+---------------+----------------+-----------------------+
| localhost | root | N | mysql_native_password |
| localhost | mysql.session | Y | mysql_native_password |
| localhost | mysql.sys | Y | mysql_native_password |
| % | deepak | N | mysql_native_password |
| localhost | ogguser1 | N | mysql_native_password |
| localhost | oggadm1 | N | mysql_native_password |
+-----------+---------------+----------------+-----------------------+
6 rows in set (0.00 sec)
mysql>
mysql> select Host,Db,User from mysq.db;
+-----------+--------------------+---------------+
| Host | Db | User |
+-----------+--------------------+---------------+
| localhost | performance_schema | mysql.session |
| localhost | sys | mysql.sys |
+-----------+--------------------+---------------+
2 rows in set (0.00 sec)
10. Show available engines.
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
mysql>
11. See current datafiles.
mysql> select * from information_schema.INNODB_SYS_DATAFILES;
+-------+---------------------------------------+
| SPACE | PATH |
+-------+---------------------------------------+
| 2 | ./mysql/plugin.ibd |
| 3 | ./mysql/servers.ibd |
| 4 | ./mysql/help_topic.ibd |
| 5 | ./mysql/help_category.ibd |
| 6 | ./mysql/help_relation.ibd |
| 7 | ./mysql/help_keyword.ibd |
| 8 | ./mysql/time_zone_name.ibd |
| 9 | ./mysql/time_zone.ibd |
| 10 | ./mysql/time_zone_transition.ibd |
| 11 | ./mysql/time_zone_transition_type.ibd |
| 12 | ./mysql/time_zone_leap_second.ibd |
| 13 | ./mysql/innodb_table_stats.ibd |
| 14 | ./mysql/innodb_index_stats.ibd |
| 15 | ./mysql/slave_relay_log_info.ibd |
| 16 | ./mysql/slave_master_info.ibd |
| 17 | ./mysql/slave_worker_info.ibd |
| 18 | ./mysql/gtid_executed.ibd |
| 19 | ./mysql/server_cost.ibd |
| 20 | ./mysql/engine_cost.ibd |
| 21 | ./sys/sys_config.ibd |
| 29 | ./mydb/CORONA.ibd |
| 296 | ./mydb/DEPT.ibd |
| 297 | ./mydb/travel_history.ibd |
| 298 | ./mydb/state_wise.ibd |
+-------+---------------------------------------+
24 rows in set (0.01 sec)
mysql>
12. See variables and global variables.
mysql> SHOW GLOBAL VARIABLES LIKE 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3307 |
+---------------+-------+
1 row in set (0.14 sec)
mysql> select @@PORT;
+--------+
| @@PORT |
+--------+
| 3307 |
+--------+
1 row in set (0.00 sec)
mysql>