Home » Mysql statements usefull statements for DBA

Mysql statements usefull statements for DBA

  • by

To Show Databases

show databases;

To change database schema for query

use sys

To Show all processes running in mysql

select * from processlist\G

To Show host summary running in mysql

select * from host_summary\G

To Show user running in mysql

Select * from user_summary\G

To Show user sessions running in mysql

Select * from user_session\G

To Show all users configured to connect/ running in mysql

use mysql

select * from user\G

To Show all plugins running in mysql

select * from plugin\G

To Show all tables in mysql

use performance_schema

Select * from tables\G

use information_schema

Select * from tables\G

To create new database in mysql

create database db1;

use db1

To new database files in mysql in datadir (data directory)

ls /var/lib/mysql

ls /var/lib/mysql/db1

To create new table in mysql with innodb engine which is default now

Create table tblINNODB(ID int) engine=INNODB;

 

To new database files in mysql in datadir (data directory)  where you can see .frm and ibd files

ls /var/lib/mysql/db1

To create new table in mysql with MYISAM engine

Create table tblMyISAM(ID int) engine=MYISAM;

To new database files in mysql in datadir (data directory)  where you can see .frm and MYD,MYI files

ls /var/lib/mysql/db1

 

To create new table in mysql with Memory engine

Create table tblMemory(ID int) engine=MEMORY;

To new database files in mysql in datadir (data directory)  where you can see .frm and ibd files

ls /var/lib/mysql/db1

 

To create new table in mysql with CSV engine

Create table tblCSV(ID int) engine=CSV;

To new database files in mysql in datadir (data directory)  where you can see .frm and ibd files

ls /var/lib/mysql/db1

To drop the new database db1

drop database db1;

To create the new database db1

create database db1;

use db1;

create table Test1(id int);

To create the new tablespace and create a table in it.

CREATE TABLESPACE myspace add datafile ‘myspace.ibd’;

create table Test2(id int) TABLESPACE=myspace;

To create the new tablespace with own directory location and create a table in it.

mkdir /datafile

chown mysql /data

CREATE TABLESPACE mysecspace add datafile ‘/data/mysecspace.ibd’;

CREATE TABLE Test3(id int) TABLESPACE=mysecspace;

To create the new table within a data directory location.

CREATE TABLE Test4(id int) DATA DIRECTORY=’/data’;

Leave a Reply

Your email address will not be published.