MySQL Basic Commands



How To Login MySQL Root
mysql -u root -p

Creating Database
create database databasename;

Creating User
create user databaseuser;

Adding Privilages For Creating User Password
grant all on db_name.* to ‘db_user’@’HOSTNAME’ identified by ‘db_password’;

Checking Databases in MySQL
show databases;

Selecting Database
use database;

Creating Table
CREATE TABLE tutorials_tbl(
tutorial_title VARCHAR(100) NOT NULL,
tutorial_author VARCHAR(40) NOT NULL,
submission_date DATE,
PRIMARY KEY ( tutorial_id )

Checking Tables
show tables;

Checking Table Information
describe table;

Deleting Table
drop table tablename;

Deleting Database
drop database databasename;

Displaying the last 5 rows of a table
select * from users order by id desc limit 5;

Backup Data Commands For User’s
Select * from rm_users INTO OUTFILE ‘/tmp/filename.csv’ FIELDS ENCLOSED BY ‘”‘ TERMINATED BY ‘,’;

How To Check User Account Passwords
select * from radcheck order by UserName;

Setting Crontab For MySQL Dump
0 1 * * * /usr/bin/mysqldump –all-databases > dump-$( date ‘+%Y-%m-%d_%H-%M-%S’ ).sql -u root -pPASSWORD

How To Check Managers List With Passwords
select managername, password from rm_managers;

How To Change Admin Password In Radius Manager
update rm_managers set password=(‘NEW PASSWORD’) where managername=’admin’;

Fetching Data From Command Prompt
SELECT * from table_name;
SELECT * from table_name WHERE table=’user’;
SELECT * from table_name  WHERE BINARY table=’user’;

How To Create a New User
CREATE USER ‘newuser’@’localhost’ IDENTIFIED BY ‘password’;
GRANT ALL PRIVILEGES ON * . * TO ‘newuser’@’localhost’;

How To Delete a User
DROP USER ‘username’@’localhost’;

Updating Data From Command Prompt
UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]

Deleting Data From Command Prompt
DELETE FROM table_name [WHERE Clause]

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s