MySQL Important Logfiles

MySQL Important Logfiles 

1. Error log file:

During instalation time this file is created , startup & shutdown, hang, maintenance related details are recorded.

show variables like '%log%';

log_error | /var/log/mysqld.log

2. General query log file:

Query related issues, instert, update, delete statements are recorded here.

Bydefault this log is not enabled.

show variables like '%log%';

general_log       | OFF                                   

general_log_file  | /var/lib/mysql/mqmopensource.log  

set global general_log=on/off;

3. Binary log file (STATIC VALUE):

Very useful for auditing like how many records are updated/deleted.

It records changes made to the database.

show variables like '%log%';

log_bin | OFF

log_bin_basename 

set global log_bin=on/off;

show binary logs;

flush logs;

4. Slow query log:

This is also very useful for slow query performance logs

slow_query_log      | OFF                                   |

slow_query_log_file | /var/lib/mysql/mqmopensource-slow.log |

set global slow_query_log=on/off;

show variables like '%time%';

long_query_time | 10.000000 (Seconds) 

Check data file location and tablespaces in MariaDB or MySQL

Check data file location and tablespaces in MariaDB or MySQL

Check size and location of data files or tablespaces present in MySQL or MariaDB

Check innodb_file_per_table parameter.

innodb_file_per_table=ON, InnoDB uses one tablespace file per table.

innodb_file_per_table=OFF, InnoDB stores all tables in the InnoDB system tablespace.

show variables like 'innodb_file_per_table'

Check the location of datafile/tablespace present in MySQL or MariaDB:

-- On MySQL

show variables like 'datadir';

Variable_name|Value                        

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

datadir      |/var/lib/mysql/|

Check files and tablespace for MySQL:

select * from information_schema.FILES;

Check files and tablespace detail for MariaDB:

select * from information_schema.innodb_sys_tablespaces; 

Check the Version of MariaDB / MySQL

Check the Version of MariaDB / MySQL

The first way to open a Command prompt and connect with MariaDB will show the version as follows:

C:\WINDOWS\system32>mysql -u root -p

Enter password: ********

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

Your MariaDB connection id is 14

Server version: 10.6.5-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select version();

Check used & available maximum connection in MySQL

Check used & available maximum connection in MySQL

Check maximum available connection in MySQL

mysql> show global variables like 'max_connections';

Check used connection in MySQL

mysql> show global status like 'max_used_connections';

Check connected session list or Process list in MySQL

select id,user,host,db,command,time,state,info from information_schema.processlist; 

Create structure, duplicate & back up the table in MySQL

Create structure, duplicate & back up the table in MySQL

Use of CTAS for creating the Duplicate table with data or only structure (by using where 1 = 0) in the following database as follows:

MySQL: Use CTAS Syntax:

-- Create structure only

create table test_table_new like test_table;

-- Insert data into backup table

insert test_table_new select * from test_table;

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0

 

Kill idle connection in MySQL

Kill idle connection in MySQL

Login with root user:

Check the session is in sleep state and get kill command for release the sessions:

select count(*) from information_schema.processlist where Command='Sleep';

select concat('KILL ',id,';') from information_schema.processlist where Command='Sleep';

concat('KILL ',id,';')|

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

KILL 10;              |

KILL 9;               |

Execute the command to Kill the session got from upper query output.

KILL 10; 

Automatic Clean the idle Connection in MariaDB/MySQL:

Interactive operation: this means opening the MySQL client on your local computer and doing various SQL operations on the command prompt.

INTERACTIVE_TIMEOUT is used to automatically clean the interactive connection in MariaDB/MySQL. The number of seconds the server waits for activity on an interactive connection before closing it

Non-interactive: means calls from the program. Example Tomcat Web service call to the database server through JDBC to connect etc.

WAIT_TIMEOUT is used to automatically clean the idle connection in MariaDB/MySQL.

The number of seconds the server waits for activity on a connection before closing it.

Note: Default time for both is 28800 seconds i.e. 8 hours

Check the Variables Values:

SHOW GLOBAL VARIABLES LIKE 'wait_timeout';

Variable_name           |Value|

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

wait_timeout            |28800|

show global variables like '%interactive_timeout%';

Variable_name      |Value|

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

interactive_timeout|28800|

Change the time limit to 30 minutes for idle connection timeout: The value used in both variables is in Seconds i.e. 30 min – 1800 seconds or 1 hour – 3600 seconds.

SET GLOBAL wait_timeout=1800;

SET GLOBAL interactive_timeout=1800;

You can also change to the Default value:

SET GLOBAL wait_timeout=DEFAULT;

SET GLOBAL interactive_timeout=DEFAULT;