MYSQL Administration Commands

MYSQL Administration Commands

Some Useful MYSQL Commands

  • DATABASE CREATION

In order to Create a Database,  run below Command:

mysql> create database databasename;
  • DATABASE LISTING

In order to list all the databases on the mysql server, run below Command:

mysql> show databases;
  • DATABASE DELETION

In order to delete a database, run below Command:

mysql> drop database databasename;
  • DATABASE SELECTION

In order to select a Database,  run below Command:

mysql> use databasename;

**********************************************************************************************************

  •  TABLES LISTING

In order to list all tables is the database, run below Command:

mysql> show tables;
  •  TABLE DESCRIPTION

In order to see table’s field format, run below Command:

mysql> describe tablename;
  • TABLE DELETION

In order to delete a table, run below Command:

mysql> drop table tablename; 

**********************************************************************************************************

 MYSQL stores all the user information in “user” table in the “mysql” database.

  • ADD  USER

mysql> insert into mysql.user (Host,User,Password) VALUES('hostname','newuser',PASSWORD('mynewpassword'));
  • LIST USERS

mysql> select user, host, password from mysql.user;
  • DELETE USER

mysql> drop user 'username'@'localhost';
Then flush privileges to save the change:
mysql> flush privileges;
  • CHANGE USER’s PASSWORD
mysql> SET PASSWORD FOR 'username'@'localhost' = PASSWORD('password');
Then flush privileges to save the change:
mysql> flush privileges;
  • GRANT USER PRIVILEGES
mysql> grant all privileges on databasename.* to user@hostname;
Then flush privileges to save the changes
mysql> flush privileges;

**********************************************************************************************************

  • CHANGE MYSQL ‘root’ USER PASSWORD
root@test:~# mysqladmin -u root -phow2install password 'newpassword'
root@test:~# mysql -u root -pnewpassword
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1390431
Server version: 5.5.30-log MySQL Community Server (GPL) by Remi
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> 
  • CHECKING MYSQL is UP or Not
root@test:~# mysqladmin -u root -p ping 
Enter password:
mysqld is alive
  • CHECKING MYSQL VERSION
root@test:~# mysqladmin -u root -phow2install version
mysqladmin Ver 8.42 Distrib 5.5.31, for debian-linux-gnu on x86_64 Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Server version 5.5.31-0ubuntu0.12.04.1
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 24 days 21 hours 55 min 14 sec
Threads: 1 Questions: 221 Slow queries: 0 Opens: 171 Flush tables: 1 Open tables: 41 Queries per second avg: 0.000
root@test:~#
  • MYSQL SERVER STATUS
root@test:~# mysqladmin -u root -phow2install status 
Uptime: 2152939
Threads: 1 Questions: 222 Slow queries: 0 Opens: 171
Flush tables: 1 Open tables: 41 Queries per second avg: 0.000
  • DISPLAY ALL RUNNING PROCESSES
root@test:~# mysqladmin -u root -ptmppassword processlist
+----+------+-----------+----+---------+------+-------+------------------+ 
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----+---------+------+-------+------------------+
| 94 | root | localhost | | Query | 0 | | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
 
  • SAFELY SHUTDOWN MYSQL SERVER
root@test:~# mysqladmin -u root -phow2install shutdown
root@test:~# mysql -u root -phow2install
ERROR 2002 (HY000): Can't connect to local MySQL server
through socket '/var/lib/mysql/mysql.sock'
  • RUNNING MULTIPLE mysqladmin COMMANDS TOGETHER
root@test:~# mysqladmin  -u root -phow2install status version

Uptime: 2152939 

Threads: 1  Questions: 222  Slow queries: 0  Opens: 171  

Flush tables: 1  Open tables: 41  Queries per second avg: 0.000
mysqladmin Ver 8.42 Distrib 5.5.31, for debian-linux-gnu on x86_64
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates.
Other names may be trademarks of their respective
owners.
Server version 5.5.31-0ubuntu0.12.04.1
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 24 days 21 hours 55 min 14 sec
Threads: 1 Questions: 221 Slow queries: 0 Opens: 171 Flush tables: 1 Open tables: 41 Queries per second avg: 0.000  

********************************************************************************************************** 

  • BACKUP SINGLE MYSQL DATABASE
root@test:~# mysqldump  -u root -phow2install db1 > db1.sql
It will backup database named db1
  • BACKUP MULTIPLE MYSQL DATABASES
root@test:~# mysqldump  -u root -phow2install db1 db2 > db1_db2.sql
It will backup two databases db1 & db2 in a single go
  • BACKUP ALL MYSQL DATABASES
root@test:~# mysqldump  -u root -phow2install --all-databases > all-db.sql
It will backup all mysql databases & store them in all-db named sql file
  • BACKUP MYSQL DATABASE STRUCTURE
root@test:~# mysqldump -u root -phow2install -–no-data db1 > db1_structure.sql
It will export only structure of database named db1, not data
  • BACKUP SINGLE MYSQL DATABASE TABLE
root@test:~# mysqldump -u root -phow2install db1 employee > employee.sql
It will backup user table from mysql named database
  • BACKUP MULTIPLE MYSQL DATABASE TABLE
root@test:~# mysqldump  -u root -phow2install mysql user host  > user_host.sql
It will backup user & host table from mysql named database
  • RESTORE MYSQL DATABASE
root@test:~# mysql -u root -phow2install db1 < db1.sql
It will restore database named db1

{#moods_dlg.WellDone}ENJOY!!!

Leave a Reply

Time limit is exhausted. Please reload the CAPTCHA.