Mysql Commands – Linux Administrator’s should know

mysql-los

Today I am going to show you the very basic but important Mysql commands which every Linux administrator should know. It is not necessary to know all Mysql commands but following are the few commands that all Linux administrators use in daily routine.

To Login in Mysql

 mysql> mysql -u root/username -p

To Login in Remote Mysql[192.168.1.250]

 mysql> mysql -h 192.168.1.250  -u root/username -p

To Create database

 mysql> create database vinod_db;

To List all databases

 mysql> show databases;

To change into database

 mysql> use vinod_db;

To List all tables

 mysql> show tables;

To see all data in database table

 mysql> select * from vinod_tbl;

To change users password

 mysql> SET PASSWORD FOR 'vinod'@'%' = PASSWORD('newpassword');
mysql> flush privileges;

To Create database user account and password

 mysql> CREATE USER 'vinod'@'%' IDENTIFIED BY 'vinodPassword';

To Create database user account and password and grant all access on db

 mysql> grant all on vinod_db.* to 'vinod'@'%' identified by 'vinodPassword';

To revoke drop permission on database from database user

 mysql> revoke drop on vinod_db.* from 'vinod'@'%';

To Delete Mysql user account

 mysql> drop user vinod;

To take dump/export database including routines

 # mysqldump -u root -p --routines vinod_db > vinod_db.sql

To take dump/export database including routines with Zip

 # mysqldump -u root -p --routines vinod_db | gzip -9 > vinod_db.sql.gz

To take dump/export all databases

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

To take dump/export table from database

 # mysqldump -c -u vinod -p  *****  vinod_db vinod_tbl > vinod_tbl.sql

To Restore dump in database

 mysql> use vinod_db;
mysql> source /home/vinod/vinod_db.sql;

To Restore dump in database from linux shell

 #mysql -u root -p vinod_newdb < vinod_db.sql

To check status of Variable

 mysql> show variables like 'innodb_lock_wait_timeout';

To set Variable value

 mysql> SET innodb_lock_wait_timeout=120;

To Change users password from linux shell

 mysqladmin -u username -h hostname.blah.org -p password 'new-password'

To recover password when forgot the root password

 #/etc/init.d/mysql stop
#/usr/bin/mysqld_safe --skip-grant-tables &
# mysql -u root -p
mysql> use mysql;
mysql> update user set Password=PASSWORD('securepassword#') WHERE User='root'; mysql> flush privileges;

To Check Mysql data and time

 mysql> select now()as CurrentDateTime;

 

You can leave a response, or trackback from your own site.

7 Responses to “Mysql Commands – Linux Administrator’s should know”

  1. Liza says:

    well I wanted to know eihter the application has to be used instead phpmyadmin but if we have phpmyadmin tools to CRUD why use this, personally my concern is phpmyadmin is the most easiest software I have ever had don’t know about others, Well All I can say here, I do appreciate the author for developing such a nice and clean interface keep it up guys and million thanx for you to provide us an open source program.

    • Vinod Pundir says:

      Liza, phpmyadmin is just an open source tool to use Linux Mysql database management system. Though it is very nice tool but it has its own limitation. With phpmyadmin, you do not work on database server directly but use database with the support of PHP language. Suppose if you want to import a large mysql dump file [.sql] of more than 20-30 mb using phpmyadmin, most probably you will not be able to do so. As phpmyadmin’s upload limit will relay on ‘php.ini’ file which resides on the phpmyadmin hosting server. But if you want to do the same using Linux command line, you will be able to do so with so ease. There are many other benefits of using Linux command line instead of phpmyadmin, but i will cover that in my article soon.

    • Baya says:

      Depending on the setup of your mysql database, it is poslibse that you may still be able to log in with username root and no password. It’s massively insecure, but seems to be the default for many installs.

    • Liliana says:

      That’s entirely up to your hsitong provider.Assuming they are on the same account as each other, and as the MySQL database, there should be no reason why not. Access to the database should have nothing to do with the domain name.

  2. Locksmith Fredericksburg says:

    Hello friends, its impressive article regarding mysql database and fully defined, keep it up all
    the time.

  3. Micheau says:

    Thank you a lot for sharing this with all folks you actually understand what you’re talking.

  4. Ezra says:

    I just like the valuable info you provide in your articles.
    I’ll bookmark your weblog and test again here regularly. I’m reasonably sure I will be told lots of new stuff right here!
    Best of luck for the next!

Leave a Reply