Useful MySQL commands

MySql Login

To login as root user, enter:
# mysql -u root -p

It will prompt for the password, enter the password to get the mysql command prompt
Enter password:

Select database and table

To know the list of database
mysql> show databases;

Select a database, example database name is "db_name"
mysql> use db_name;
To know the list of all tables in the database "db_name"
mysql> show tables;

Restore MySql database from a backup file

# mysql -u root -p db_name < backup.sql

It will prompt for the password, enter the password to complete the import operation
Enter password:

Backup the MySql database

# mysqldump -u root -p db_name > backup.sql

It will prompt for the password, enter the password to complete the import operation
Enter password:

Import a csv file into a TABLE

Importing 'filename.csv' into table name 'table_name'. in the below command 'IGNORE 1 LINES' ignores the 1st line of the CSV file. Log into your mysql database and run the following command

LOAD DATA LOCAL INFILE 'filename.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' IGNORE 1 LINES;