Basic MySQL Bash Commands
https://www.zyxware.com/sites/default/files/styles/user_image/public/default_images/index.png?itok=2YmREnrP
BY fazeela.ma
4 years ago
Bash
0 comments comment

Command line provides a platform to that takes input from keyboard to interact with the operating system. In Linux Operating System's, the Command line is often referred to bash.

MySQL in Command Line

About 90% of us would have created database, and its table using phpmyadmin user interface. How would it be if we can create database, tables etc. through bash ? Sure to add as an asset to our knowledge base. So lets get started.

  • Follow up the instructions below before you explore sql commands in bash. Now lets assume we are good to go with our pre requesites installed.
apt-get install mysql-client;
apt-get install mysql-server;
  • After installing, lets get started with the basics,

    We need to switch on to mysql mode, open your terminal. Either open using keyboard shortcut
    Ctl+Alt+t

    or Click on dash home, type terminal and select.

    • Login
      mysql -u username -p;

      Enter the password on prompting. Replace username with yours.

      mysql>

      Everything went right for you, if you get the above to the left corner of bash. Or else something went wrong in enabling mysql, go back to make corrections or ask for technical support.

    • Create database
       mysql> CREATE DATABASE database_name;

      Lets assume the name of our database as train. Query would become

      Enter the password on prompting. Replace username with yours.

       mysql> CREATE DATABASE train;
              Query OK, 1 row affected (0.03 sec) 
    • Create a user
      • Create a user without password.
         mysql> CREATE USER 'username'@'hostname';

        Lets assume the name of the new user as me. Query would become

         mysql> CREATE USER 'me'@'localhost';
                Query OK, 0 rows affected (0.10 sec)
      • Create a user with password
         mysql> CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';

        Lets assume the name of the new user and password as me. Query would become

         mysql> CREATE USER 'me'@'localhost' IDENTIFIED BY 'me';
                Query OK, 0 rows affected (0.10 sec)
    • Create a table
      mysql> USE database_name;
      mysql> CREATE TABLE table_name (column_name1 column_type1, column_name2 column_type2, );

      Query would become,

        USE train;
        Database changed 
      mysql> create table train_details (Id int(11), Name char(20), StartingStation VARCHAR(20)); Query OK, 0 rows affected (0.29 sec)
    • Insert value into table
      mysql> INSERT INTO TABLE_NAME (column_name1, column_name2, column_name3) VALUES (coloumn_value1, coloumn_value2, coloumn_value3);

      Query would become,

      mysql> INSERT INTO  train_details(Id, Name, StartingStation) VALUES (1, 'Shadabdi Express', 'Aluva' );
             Query OK, 1 row affected (0.08 sec)
    • Update a table
      mysql> UPDATE table SET coloumn_name = coloumn_value, WHERE conditions;

      Query would become,

       UPDATE train_details SET StartingStation = 'Ernakulam'  WHERE Id =1;
       Query OK, 1 row affected (0.08 sec)
       Rows matched: 1  Changed: 1  Warnings: 0
      
    • Delete a value from table
      mysql> DELETE FROM table_name WHERE column_name = coloumn_value;

      Query would become,

      mysql> DELETE FROM train_details WHERE Id = 1;
             Query OK, 1 row affected (0.04 sec)
    • Table Structure
      mysql> DESC table_name;

      Query would become,

      mysql> DESC train_details;
      +-----------------+-------------+------+-----+---------+-------+
      | Field           | Type        | Null | Key | Default | Extra |
      +-----------------+-------------+------+-----+---------+-------+
      | Id              | int(11)     | YES  |     | NULL    |       |
      | Name            | char(20)    | YES  |     | NULL    |       |
      | StartingStation | varchar(20) | YES  |     | NULL    |       |
      +-----------------+-------------+------+-----+---------+-------+
      3 rows in set (0.00 sec)
      
      
      
    • Drop a table
      mysql> DROP table table_name;

      Query would become,

      mysql> DROP table train_details;
             Query OK, 1 row affected (0.08 sec)
    • Delete a database
      mysql> DROP DATABASE train;

      Query would become,

      mysql> DROP DATABASE train;
             Query OK, 1 row affected (0.08 sec)
    • Export a database
      mysql> mysqldump -u username -p password database_name > sqlfile_name.sql;

      Query would become,

      mysql> mysqldump -u root -p train > train.sql;
             Query OK, 1 row affected (0.08 sec)
    • Import a database
      mysql> mysqldump -u username -p password database_name 

      Query would become,

      mysql> mysqldump -u root -p train 

      There is another method to do this, the one i prefer most

      mysql> USE DATABASE database_name;
      mysql> SOURCE pathtosqldfile.sql

      The query will become,

      mysql> USE DATABASE train;
      mysql> SOURCE /home/zyx/Projects/train.sql
      Query OK, 1 row affected (0.08 sec)

For me it was more like a nightmare to work on command prompt, once we learn the way to work on commad prompt, i bet we insist ourselves to do things on bash. The commands we used are all mysql queries. Bash can perform a task much faster than using Graphical Interface. It also allow access to more commands and scripts.


RELATED ARTICLE

/themes/custom/zyxpro_light/images/placeholder.png
more_horiz
close

on 11th December 2008 / by webmaster
Recently we had to provide support on a Red Hat Server where there were hundreds of users and the user directories organized into hierarchies. Normally we change to a users directory using cd ~username. We had some extra time on our hands and wanted to play with the /etc/passwd file and tried to use awk to do the same thing. The following one liner will do this the hard way :-) Linux Shell Scripting Bash Leave a reply Your email address will not be published. Required fields are marked * cherry (not verified) access_time 17 Nov 2019 - 05:49 alternatively: cd `(cat /etc/passwd|grep username|cut -d ':' -f 6` ;-) Add new comment
/themes/custom/zyxpro_light/images/placeholder.png
close

on 12th December 2009 / by webmaster
Asianet Dataline is an ISP in Kerala and they use a web browser based authentication system to lo Internet Shell Scripting Bash Asianet Leave a reply Your email address will not be published. Required fields are marked * Vivek Thomas (not verified) access_time 17 Nov 2019 - 05:49 Thank you very much for this. Works great. Btw I had to add a --no-check-certificate option in the following line for it get the login URL. :) asianet_conn_url=`wget --quiet --no-check-certificate -O - www.zyxware.com|grep 'action='|sed 's/\(.*action="\)\(.*\)">/\2/g'` webmaster access_time 17 Nov 2019 - 05:49 In reply to Thank you very much for this. by Vivek Thomas (not verified) Changes duly made in git repo and credit given. Anoop JITHIN (not verified) access_time 17 Nov 2019 - 05:49 In reply to Thanks by webmaster Sir, Where is this "Favorite Scripts Folder" How Can I Copy A Script ie In Text Form To A Folde? Is Favorite Script Folder an attachment Of Internet Explorer Browser ? Please Tell Me About "START UP PROCESS" Can You Plese Explain What Should I Do Step By Step Considering Me As A Beginer. Jimmy (not verified) access_time 17 Nov 2019 - 05:49 Hi, I see the script. Can I use this script in Windows? If yes, please tell me the complete procedure to use this script in windows using the browser IE. Thanks Jimmy Anonymous (not verified) access_time 17 Nov 2019 - 05:49 Thank you very much for this. Works great. But I cant logout after use Add new comment
/themes/custom/zyxpro_light/images/placeholder.png
more_horiz
close

on 14th December 2012 / by thomas.john
The default method of changing a Drupal theme using the graphical Admin interface is familiar to most Drupal users. However a Drupal user is limited to changing only one theme at a time using this method and it is also slightly time consuming. What if you want to enable more then one theme at a time? Drupal provides a quicker way to change the theme using the bash command line. Read on to know how to enable multiple Drupal themes using the bash command line Drupal Bash Drupal Themes Drupal Theming Drush Leave a reply Your email address will not be published. Required fields are marked *
Leave a reply
Your email address will not be published. Required fields are marked *

Filtered HTML

  • Web page addresses and email addresses turn into links automatically.
  • Allowed HTML tags: <a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type='1 A I'> <li> <dl> <dt> <dd> <h2 id='jump-*'> <h3 id> <h4 id> <h5 id> <h6 id>
  • Lines and paragraphs break automatically.
The content of this field is kept private and will not be shown publicly.
CAPTCHA This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.