Categories
Linux Mysql Unix

How to Use MYSQL with SSH over LINUX/UNIX

It is actually a great thing if you learn the way to use MYSQL over SSH. So writing down you few very useful commands.

Login to MYSQL Through Shell
So here are the different commands to log into MYSQL
-h [hostname e.g localhost,127.0.0.1,IP]
-u [db username or root]
-p [password]
-P [port e.g 3306, 3307, 3304]

# mysql -h hostname -u root -p
After entering this command it will ask you the user password. Input the password and then you can able to access it.

# mysql -h 127.0.0.1 -u root -pPassword databasename
With this command password and database already specified so will access the database directly or you can leave -p empty and can enter the password in second term.

# mysql -h hostname -u root -pPassword -P 3306 databasename
With this command port number mention incase its required.

MYSQL Useful Command-List
After successfully login there are many operations can be performed through MYSQL commands, sharing you some of the useful commands.

Create an MYSQL database
# mysql> create database databasename;

List Down all the databases
# mysql> show databases;

Drop/Delete an MYSQL database
# mysql> drop databasename;

Choose an MYSQL database
# mysql> use databasename;

See all the tables from the chosen database
# mysql> show tables;

Show all the data in a table
# mysql> SELECT * FROM tablename;

Show selected row with certain values of an ID column e.g “126”
# mysql> SELECT * FROM tablename WHERE ID = ‘126’;

Import/Export MYSQL Database
It is a pretty easy and fast way to handle large size database.

Export MYSQL db
# mysqldump -pPassword -u Username Databasename > Databasename.sql

Import MYSQL
# mysql pPassword -u Username Databasename < Databasename.sql