MySQL Commands

 

 

 

Employee
ID FIRSTNAME LASTNAME GENDER JOB JOININGDATE SALARY CITY PHONE
1 Purushotham Karimbedu M QA 01-Oct-2012 30000 Hyderabad 9000109120
2                
         

 

 

Get a list of MySQL user accounts:-

mysql> SELECT User FROM mysql.user; 
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

Get a list of Database user accounts:-

mysql > select * from users; 
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

Login in my sql with linux terminal :-

root@chinna:~# mysql -u root -p 
Enter password:*******
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

Create database on mysql server:-

mysql> create database techlearn;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

List/show/view all databasenames on mysql server :-

mysql> show databases; 

+--------------------+ 
|    Database           | 
+--------------------+ 
| information_schema    | 
| techlearn                    | 
| abc                            | 
| xyz                            | 
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

Select/use to a database:-

mysql> use databasename;
ex:
mysql> use techlearn;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

Create table:-

mysql> create table employee(id integer,name varchar(15),gender varchar(5),salary integer);
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

Enter values into table :-
mysql> insert into employee values(1, 'Purushotham K', 'Male',25000);
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

Adding a column in table :-
mysql> alter table employee add(email varchar(25));
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
 

Update a column in table :-

mysql> update employee set email='chinna4u.kp@gmail.com' where id=1;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

See all the table from a database :-

mysql > show tables;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

See all the tables field formates or description of table :-
mysql > describe tablename;
ex:
mysql> describe users;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

Delete / Drop the database from mysql server:-

mysql > drop database databasename;

Ex :- 
mysql > drop database techlearn;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

Delete / Drop the table name:-

mysql > drop table tablename;

Ex :
mysql> drop table cache;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

Importing a SQL db into mysql using the terminal :-

root@chinna:~# mysql -u username -p password databasename < filename.sql

Ex: 
root@chinna:~# mysql -u root -p techlearn < /home/chinna/Desktop/techlearnin.sql
Password : *******
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

Exporting a SQL db into mysql using the terminal :-

root@chinna:~# mysqldump -u root -p databasename > databasename.sql
Enter password: *******

Ex: 
root@chinna:~# mysqldump -u root -p techlearn > techlearn.sql
Password : *******
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

Clear screen :-

mysql > Ctrl+l;
or
mysql > system clear;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

Unblocking an account :-
Say you accidentally "block" your admin account and cannot log in. From within a MySQL client you can run this command to unblock it.

mysql > update users set status = 1 where uid = 1;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

Reset user/root password :-

Find a way to reset my root level password in Drupal 7 with only access to phpMyAdmin and ftp.
mysql--> database--> users --> edit users --> add below password hash code in pass insted of old code field. 

password hash code :  $S$C6x2r.aW5Nkg7st6/u.IKWjTerHXscjPtu4spwhCVZlP89UKcbb/

New pwd : NEW_TEMP_PASSWORD
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

Import large .sql files to phpmyadmin in LAMP :-

root@chinna:~# nano /etc/php5/apache2 /php.ini
Find:
post_max_size = 8M
upload_max_filesize = 2M
max_execution_time = 30
max_input_time = 60
memory_limit = 8M

Change to:
post_max_size = 750M
upload_max_filesize = 750M
max_execution_time = 300
max_input_time = 540
memory_limit = 1000M
Save the changes.
root@chinna:~# /etc/init.d/apache2 restart 
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

How to connect cPanel-SSH Access  :-

root@debian:/home/chinna# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):

root@debian:/home/chinna# cat ~/.ssh/id_rsa.pub
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDP3xeohPxX0BkT2NRGC69+5C7wrkJ2UeDJVp6z4H3yEA+tcNqZK0pjp4JO+vKXMiYsKwEmccDEoxs1cUBQf4GX9T5OzQVcHgpy7NZ/kPtcwzn//WdUECvlY5KQG30Iq3EdEn91d90kcRdU+h4sewkb5y20Z7i7YE5xi5qrJGDiPyMZMo0HJfe6N1SsAd5qj3Xis1qxnruUpXEtgfF8NbSw8Z7xEW5 root@debian

root@debian:/home/chinna# cat ~/.ssh/id_rsa
-----BEGIN RSA PRIVATE KEY-----
MIIEpAIBAAKCAQEAz98XqIT8V9AZFfA7dAjBdqCwuwxQot5lLWHHB8L3CZN7XM5P
.
5syVzV03oeUiCxA5gySakm+dvp2RSGjWd59rOmqMzO8CDIYPJCx6Ew==
-----END RSA PRIVATE KEY-----

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

Resetting a lost MYSQL root password :-

 stop Mysql

root@chinna:/home/chinna# /etc/init.d/mysql stop

safe Mode

we need to start Mysql in safe mode-that is to say we will start Mysql but skip the user privileges table.

root@chinna:/home/chinna# sudo mysqld_safe –skip-grant-tables &

Login

now we need to log into Mysql and set the password

root@chinna:/home/chinna# mysql -u root

note:No password is required at this stage as when we started Mysql we skipped the user privileges table.

Next ,instruct Mysql which database to use:

root@chinna:/home/chinna# use mysql;

Reset Password

Enter the new password for the root user as follows:

root@chinna:/home/chinna# update user set password=PASSWORD(“newpassword”) where User='root';

and finally,flush the privileges:

root@chinna:/home/chinna# flush privileges;

now restart the mysql by logging out

root@chinna:/home/chinna# quit
root@chinna:/home/chinna# sudo /etc/init.d/mysql stop
root@chinna:/home/chinna# sudo /etc/init.d/mysql start

Login

root@chinna:/home/chinna# mysql -u root -p
 

Tags: