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