MySQL General Queries

MySQL General Queries

-- Drop database:

mysql> drop database qtest1;

mysql> show databases like 'information_schema%';

mysql> SELECT schema_name FROM information_schema.schemata;

How to get MySQL / MariaDB db size:

mysql> select table_schema as "database", sum(data_length + index_length) / 1024 / 1024 / 1024 as "size (gb)" from information_schema.tables group by table_schema;

mysql> 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.

mysql> select DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(unix_timestamp()), 'GMT', 'Europe/Dublin'), '%Y%u');

mysql> select @@global.time_zone, @@session.time_zone;

How to check the time and date?

mysql> select now();

Below commands can be used to find MySQL / MariaDB version:

mysql> select version();

OS

#[root@mqmopensource ~]# mysql --version

mysql> show variables like "%version%";

show all processes in mysql cluster.

mysql> show processlist;

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='information_schema';

Get the processid for the session:

mysql> show processlist;

Uptime of server

mysql> status;

Alternative command

mysql> \s

Server startup time:

mysql> select TIME_FORMAT(SEC_TO_TIME(VARIABLE_VALUE ),'%Hh %im') as Uptime from information_schema.GLOBAL_STATUS where VARIABLE_NAME='Uptime';

How to check the data directory:

mysql> show variables like '%data%';

mysql> select variable_value from information_schema.global_variables where variable_name = 'datadir';

mysql> select @@datadir;

Find current data/time on MySQL / MariaDB

mysql> select current_timestamp,current_date,localtime(0),localtimestamp(0);

Find MySQL / MariaDB configuration values

mysql> select variable_name,variable_value from information_schema.global_variables;

mysql> select variable_name,variable_value from information_schema.global_variables where variable_name = 'port';

mysql> select variable_name,variable_value from information_schema.global_variables where variable_name = 'datadir';

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

mysql> status;

-- In older version:

mysql> select time_format(sec_to_time(variable_value ),'%hh %im') as uptime from information_schema.global_status where variable_name='uptime';

-- In latest mysql version:

mysql> show global status like 'uptime';

mysql> show engines;

If the valid backup file is available with you, then you can restore the same using below command.

mysqldump -u root -p classicmodels < mysqldump.sql

-- If  dbdump.sql is the backup file , then restore command will be 

[mysql@localhost]# mysqldump –u root –pmysql sampledb > dbdump.sql

-- Backup multiple databases

For example, if you want to take backup of both sampledb and newdb database, execute the mysqldump as shown below:

[root@localhost]# mysqldump –u root –p --databases sampled newdb > /tmp/dbdump.sql

We cannot take backup of information_schema and performance_schema databases as these are metadata databases.

-- Backup all databases:

root# mysqldump -u root -p --all-databases > all-database.sql

Backup a specific table

SYNATX:

mysqldump –c –u username –p databasename tablename > /tmp/databasename.tablename.sql

In this example, we backup only the ta2 table from sampledb database.

root$ mysqldump –u root –p sample ta2 > /tmp/nwedb_ta2.sql

Restore all databases

[root]$ mysql -u root -p < /tmp/alldbs55.sql

Backup databases in compress format

With bzip2:

mysqldump --all-databases | bzip2 -c > databasebackup.sql.bz2 

With gzip:

mysqldump --all-databases | gzip> databasebackup.sql.gz