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;

Find the dependence on Table in MYSQL / MariaDB

Find the dependence on Table in MYSQL / MariaDB

Check the Procedures and functions depending on the table:

select a.routine_name,b.table_name,a.routine_schema,a.routine_type from information_schema.routines a inner join (select table_name , table_schema from information_schema.tables ) b on a.routine_definition like concat('%',b.table_name,'%') where  b.table_schema = 'dbname'  and b.table_name = 'test_table' ;

Check the views dependence on the table.

select * from information_schema.views where table_schema='mqm1' and table_name = 'test_table';

  

  

Check the foreign key constraints depend on the table.

SELECT Constraint_Type ,Constraint_Name ,Table_Schema ,Table_Name FROM information_schema.table_constraints

WHERE Table_Schema ='dbname' AND Table_Name = 'tablename' and Constraint_Type = 'FOREIGN KEY';

Start and Stop and restart the MariaDB or MySQL on Linux

Start and Stop and restart the MariaDB or MySQL on Linux

Check status for the MariaDB or MySQL

ps -ef|grep mysql

Check status for the MariaDB or MySQL

service mysqld status

Service mysql status

/etc/init.d/mysql status

/etc/init.d/mysqld status

Start the MariaDB or MySQL in Linux Environment:

service mysqld start

service mysql start

/etc/init.d/mysql start

/etc/init.d/mysqld start

Stop the MariaDB or MySQL in Linux Environment

service mysqld stop

service mysql stop

/etc/init.d/mysql stop

/etc/init.d/mysqld stop

Restart the MariaDB or MySQL Services in Linux Environment

service mysqld restart

service mysql restar

/etc/init.d/mysql restart

/etc/init.d/mysqld restart