1. Install MySQL instance

cd /mysqldatabase/product/binary/5.7.35
bin/mysqld --defaults-file=/mysqldatabase/TEST/admin/my.cnf --initialize --user=mysql

For more interesting posts on MySQL, please click on INDEX

2. Start MySQL instance

cd /mysqldatabase/product/binary/5.7.35/
bin/mysqld_safe --defaults-file=/mysqldatabase/TEST/admin/my.cnf --user=mysql &

 

3. Shutdown MySQL Instance

/mysqldatabase/product/binary/5.7.35/bin/mysqladmin -S/mysqldatabase/SLAVE/admin/mysql-slave.sock -uroot -pmysql shutdown

 

4. Check for Error logs locations

mysql> SELECT @@GLOBAL.log_error;
+------------------------------------+
| @@GLOBAL.log_error                 |
+------------------------------------+
| /mysqldatabase/TEST/logs/mysql.log |
+------------------------------------+
1 row in set (0.00 sec)

 

5. Check for different engines and the default engine :

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

 

6. Turn on General query log

mysql> SET GLOBAL general_log = 'ON';
Query OK, 0 rows affected (0.01 sec)


mysql> SELECT @@GLOBAL.general_log;
+----------------------+
| @@GLOBAL.general_log |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)

 

7. Check General Log location

mysql> show global variables like '%general%';
+------------------+-----------------------------------+
| Variable_name    | Value                             |
+------------------+-----------------------------------+
| general_log      | ON                                |
| general_log_file | /mysqldatabase/TEST/data/test.log |
+------------------+-----------------------------------+
2 rows in set (0.01 sec)

8. Check General log events :

mysql> use mysql;
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 * from general_log;
+----------------------------+------------------------------------+-----------+-----------+--------------+-------------------------------------+
| event_time                 | user_host                          | thread_id | server_id | command_type | argument                            |
+----------------------------+------------------------------------+-----------+-----------+--------------+-------------------------------------+
| 2021-10-02 16:29:00.665183 | root[root] @ localhost [127.0.0.1] |         7 |         1 | Query        | SELECT @@GLOBAL.general_log         |
| 2021-10-02 16:31:50.133097 | root[root] @ localhost [127.0.0.1] |         7 |         1 | Query        | SHOW CREATE TABLE mysql.general_log |
| 2021-10-02 16:31:56.595550 | root[root] @ localhost [127.0.0.1] |         7 |         1 | Query        | SELECT @@GLOBAL.general_log         |
| 2021-10-02 16:34:17.277467 | root[root] @ localhost [127.0.0.1] |         7 |         1 | Query        | SELECT @@GLOBAL.log_output          |
| 2021-10-02 16:35:13.170662 | root[root] @ localhost [127.0.0.1] |         7 |         1 | Quit         |                                     |
| 2021-10-02 16:37:45.812546 | [root] @ localhost [127.0.0.1]     |         8 |         1 | Connect      | root@localhost on  using SSL/TLS    |
| 2021-10-02 16:37:45.812973 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Query        | select @@version_comment limit 1    |
| 2021-10-02 16:38:08.905390 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Query        | show databases                      |
| 2021-10-02 16:39:13.453771 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Query        | SELECT DATABASE()                   |
| 2021-10-02 16:39:13.454129 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Init DB      | mysql                               |
| 2021-10-02 16:39:13.456316 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Query        | show databases                      |
| 2021-10-02 16:39:13.456982 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Query        | show tables                         |
| 2021-10-02 16:39:13.458135 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Field List   | columns_priv                        |
| 2021-10-02 16:39:13.458813 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Field List   | db                                  |
| 2021-10-02 16:39:13.459559 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Field List   | engine_cost                         |
| 2021-10-02 16:39:13.459948 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Field List   | event                               |
| 2021-10-02 16:39:13.460566 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Field List   | func                                |
| 2021-10-02 16:39:13.460856 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Field List   | general_log                         |
| 2021-10-02 16:39:13.461123 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Field List   | gtid_executed                       |
| 2021-10-02 16:39:13.461601 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Field List   | help_category                       |
| 2021-10-02 16:39:13.463192 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Field List   | help_keyword                        |
| 2021-10-02 16:39:13.465049 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Field List   | help_relation                       |
| 2021-10-02 16:39:13.466871 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Field List   | help_topic                          |
| 2021-10-02 16:39:13.469245 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Field List   | innodb_index_stats                  |
| 2021-10-02 16:39:13.471671 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Field List   | innodb_table_stats                  |
| 2021-10-02 16:39:13.473234 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Field List   | ndb_binlog_index                    |
| 2021-10-02 16:39:13.475585 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Field List   | plugin                              |
| 2021-10-02 16:39:13.475836 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Field List   | proc                                |
| 2021-10-02 16:39:13.484287 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Field List   | procs_priv                          |
| 2021-10-02 16:39:13.485341 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Field List   | proxies_priv                        |
| 2021-10-02 16:39:13.486006 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Field List   | server_cost                         |
| 2021-10-02 16:39:13.486354 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Field List   | servers                             |
| 2021-10-02 16:39:13.486790 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Field List   | slave_master_info                   |
| 2021-10-02 16:39:13.488048 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Field List   | slave_relay_log_info                |
| 2021-10-02 16:39:13.488771 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Field List   | slave_worker_info                   |
| 2021-10-02 16:39:13.489326 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Field List   | slow_log                            |
| 2021-10-02 16:39:13.492986 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Field List   | tables_priv                         |
| 2021-10-02 16:39:13.493782 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Field List   | time_zone                           |
| 2021-10-02 16:39:13.494244 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Field List   | time_zone_leap_second               |
| 2021-10-02 16:39:13.498391 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Field List   | time_zone_name                      |
| 2021-10-02 16:39:13.498743 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Field List   | time_zone_transition                |
| 2021-10-02 16:39:13.499186 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Field List   | time_zone_transition_type           |
| 2021-10-02 16:39:13.499674 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Field List   | user                                |
| 2021-10-02 16:39:15.909552 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Query        | select * from general_log           |
+----------------------------+------------------------------------+-----------+-----------+--------------+-------------------------------------+
44 rows in set (0.00 sec)

 

9. Check slow query log location :

mysql> show global variables like '%slow%';
+---------------------------+----------------------------------------+
| Variable_name             | Value                                  |
+---------------------------+----------------------------------------+
| log_slow_admin_statements | OFF                                    |
| log_slow_slave_statements | OFF                                    |
| slow_launch_time          | 2                                      |
| slow_query_log            | ON                                     |
| slow_query_log_file       | /mysqldatabase/TEST/data/test-slow.log |
+---------------------------+----------------------------------------+
5 rows in set (0.00 sec)

 

10. Check tablespaces that have free space

SELECT s.schema_name, table_name,
IFNULL(ROUND(SUM(data_free)/1024,2),0.00) data_free
FROM INFORMATION_SCHEMA.SCHEMATA s, INFORMATION_SCHEMA.TABLES t
WHERE s.schema_name = t.table_schema
GROUP BY s.schema_name, table_name
HAVING data_free > 100
ORDER BY data_free DESC;

mysql> SELECT s.schema_name, table_name,
    -> IFNULL(ROUND(SUM(data_free)/1024,2),0.00) data_free
    -> FROM INFORMATION_SCHEMA.SCHEMATA s, INFORMATION_SCHEMA.TABLES t
    -> WHERE s.schema_name = t.table_schema
    -> GROUP BY s.schema_name, table_name
    -> HAVING data_free > 100
    -> ORDER BY data_free DESC;
+--------------------+-----------------+-----------+
| schema_name        | table_name      | data_free |
+--------------------+-----------------+-----------+
| information_schema | PARAMETERS      |   8192.00 |
| information_schema | PLUGINS         |   8192.00 |
| information_schema | OPTIMIZER_TRACE |   8192.00 |
| information_schema | EVENTS          |   8192.00 |
| information_schema | TRIGGERS        |   8192.00 |
| information_schema | ROUTINES        |   8192.00 |
| information_schema | PARTITIONS      |   8192.00 |
| information_schema | COLUMNS         |   8192.00 |
| information_schema | VIEWS           |   8192.00 |
| information_schema | PROCESSLIST     |   8192.00 |
| mysql              | help_topic      |   4096.00 |
+--------------------+-----------------+-----------+
11 rows in set (0.16 sec)

 

11. Check for users and their authentication string in MySQL

mysql> select user,Host,authentication_string from mysql.user;
+---------------+----------------+-------------------------------------------+
| user          | Host           | authentication_string                     |
+---------------+----------------+-------------------------------------------+
| root          | localhost      | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| mysql.session | localhost      | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost      | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| TEST          | %              | *47A6B0EA08A36FAEBE4305B373FE37E3CF27C357 |
| TEST          | localhost      | *47A6B0EA08A36FAEBE4305B373FE37E3CF27C357 |
| TEST          | 172.168.190.25 | *47A6B0EA08A36FAEBE4305B373FE37E3CF27C357 |
+---------------+----------------+-------------------------------------------+
6 rows in set (0.00 sec)

12. Identify current Data dictionary

SELECT @@datadir

 

13. Purging binary logs

mysql> show binary logs;
mysql> purge binary logs to 'mysql-bin.000496'; -- This will delete binary logs before 00496.