MySQL is a uninhibitedly accessible open source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL). SQL is the most prevalent dialect for including, getting to and overseeing substance in a database. It is most noted for its brisk transforming, demonstrated unwavering quality, straightforwardness and adaptability of utilization.
MySQL is an open source database administration programming that helps clients store, compose, and recover information. It is an intense project with a great deal of adaptability this excercise will give the least complex prologue to MySQL.
Whenever to run mysql query through command line then we need to logged in mysql with username and password and choose database on which the query to be run. Here is example for the same in command line.
# mysql -u techoism -p
# mysql> show databases;
# mysql> use techoism;
Note:You will be in mysql console with database techoism chosen.
# select id from user;
If you don’t want to logged in mysql and need to run query through command line then use follow command:
# mysql -u root -p techoism -e "SELECT id from user"
If you want to save output of the query then run following command to save output.
# mysql -u root -p -e "SELECT id from user" > data.txt
It’s very easy to take a back up of any mysql database using command line. Use following command to take backup of database:
# mysqldump -u user_name -p database_name > backup.sql
mysqldump -u techoism -p techosim > techosim.sql
If you want to skip triggers while taking back up of database then follow below command:
# mysqldump -u user_name -p --skip-triggers database_name > backup.sql
Sometimes we require to get mysql table size for specific database. It is easy if phpmyadmin or some other tool are installed. But when it comes to command line then here is syntax and example for same. Additionally below query will show numbers of rows(records in table), data length and index length.
# SELECT TABLE_NAME, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024),2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = "DATABASE_NAME" ORDER BY (data_length + index_length) DESC;
Every time it is easy to get list of tables of selected database in mysql in phpmyadmin. But what about when it comes to fetch using query. Here is syntax and example for the same..
# SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'DATABASE_NAME';
Let’s have more advance. If you want to list only “InnoDB” or “MyISAM” tables then, Here is syntax and example
# SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'DATABASE_NAME' AND ENGINE = 'ENGINE_TYPE';
# SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'techoism' AND ENGINE = 'InnoDB';
# SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'techoism' AND ENGINE = 'MyISAM';
NOTE: Case in-sensitive in above examples.