MySQL Basic Commands For Database Administrator

As a database administrator and system admin one should know some specifics commands. So, I am sharing some of basic commands.These commands will also helpful for beginners as well as who are early DBA’s in his carrier.

How to login MySQL?

To login in mysql you should have valid username, password and host address(for remotely login).

Check the box for command

$ mysql -uusername -ppassword -h192.168.04

$ mysql -uusername -ppassword

How to check version of mysql?

For checking version of mysql one should know the following commands. The requirement of version check is important because you need to know the current version is support your application or not.

$ mysql -uusername -ppassword version

or

$ mysql -v

or

mysql > select version();

How to check all databases in the server?

For checking all databases exit in the server. First you should be login in mysql server than use command

mysql> show databases;

How to select database?

You need to select specific database after checking the database. Use command

mysql > use database_name

How to create new database?

For creating new database according to your requirement. You need to use a very simple command.

mysql> create database database_name;

or

mysql> CREATE DATABASE IF NOT EXISTS Database_name;

How to create a new user in Database?

For creating new user you should have full permissions.The command of create user is given below.

mysql> Create user 'newuser'@'localhost' identified by 'password';
Query OK, 0 rows affected (0.01 sec)

OR

mysql> Create user 'newuser'@'%' identified by 'password';

Where ‘%’ is defined for all host and localhost for login from localhost only.

How to check existing user in mysql?

mysql> Select user,host from mysql.user;

How to check Permissions of user have?

mysql> Show Grants;

HOW TO GIVE OR GRANT PRIVILEGES TO THE USER?

mysql> GRANT ALL PRIVILEGES ON Database_name.* TO 'username'@'localhost' IDENTIFIED BY 'Password';

HOW TO FLUSH PRIVILEGES?

After granting privilege to any user. you need to run flush privileges command which can help to refresh the user privileges.

mysql> FLUSH PRIVILEGES;

 HOW TO CHECK RUNNING PROCESSLIST & FULL PROCESSLIST?

With show processlist command you will find the null process, time-consuming process, stuck processes etc. on the server.

mysql> SHOW PROCESSLIST;
mysql> SHOW FULL PROCESSLIST;

HOW TO DROP DATABASE?

To drop single database use below drop database command.

mysql> DROP DATABASE Database_name;

HOW TO CHANGE OR UPDATE PASSWORD?

All MySQL user data will be stored in the mysql database. so before changing or updating any user password. You need to first use mysql database then run update command to change the password.

mysql> use mysql;
mysql> update user set password=PASSWORD('your_new_password') where User='username';

 HOW TO DELETE A USER?

If you want any mysql user which is not in use then run below drop user command.

mysql> DROP USER 'username'@'localhost';

HOW TO TAKE BACKUP OR DUMP OF MYSQL DATABASE?

This is an important command for every sysadmin i.e. mysqldump.
with mysqldump command, you will able to take backup of the database in a .sql format

mysql> mysqldump -u username -p DbName > DbName_date.sql

HOW TO RESTORE DATABASE DUMP?

For restoring a .sql dump file run below command by specifying the database name.

mysql> mysql -u username -p DbName < DbName_date.sql

HOW TO TAKE BACKUP IN GZIP FORMAT?

Below command help to take direct database backup in compress format .i.e. .gz

mysql> mysqldump -u userName -p DbName | gzip > DbName_date.sql.gz

HOW TO RESTORE DUMP WHICH IS IN GZIP FORMAT?

Restoration of database dump which is in .gz format use below gunzip command to a uncompressed file and then MySQL command to restore the database.

mysql> gunzip < DbName_date.sql.gz | mysql -u root -p DbName

HOW TO KILL SLEEP MYSQL QUERIES?

To kill any time-consuming or null process then use show processlist command. once processlist open then check for null process PID run kill command on that PID.

mysql> show processlist;
mysql> kill PID1 PID2 PID3;

HOW TO TAKE A DUMP OF THE SINGLE TABLE FROM THE DATABASE?

Sometimes we need to take a dump of the single table from the database. just use table name next to the database name and run mysqldump command.

$ mysqldump -u username -p DbName tablename > tablename.sql

HOW TO START|STOP|RESTART MYSQL SERVER?

Run below commands to start,stop,restart mysql service.

$/etc/init.d/mysql start
$ /etc/init.d/mysql stop
$ /etc/init.d/mysql restart

I hope this article will be helpful to understand basic mysql commands. If you have any queries and problem please comment in comment section or you can also ask your question.

Thanks:)