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;

Linux generate htpasswd to store username and password with htpasswd command and htaccess to grant access based on credentials given in htpasswd

.htpasswd is a flat-file used to store usernames and password for basic authentication of Apache HTTP Server. The name of the file is given by in the .htaccess configuration, and can be anything, but ".htpasswd" is the canonical name. The file name starts with a dot, because most Unix-like operating systems consider any file that begins with dot to be hidden. This file is often maintained with the shell command "htpasswd" which can add, delete, and update users, and will properly encode the password for use (so that it is easily checked, but not reversed back to the original password).

The file consists of rows, each row corresponds to a pair of username and hashed password separated with the colon in between. The hash is typically "UNIX crypt" style with MD5 or SHA1 as common alternatives.

Resources available from the Apache HTTP server can be restricted to just the users listed in the files created by htpasswd. (ref:: wikipedia)

Create a new htpasswd file

Following command will create a new file and stores a record in it for username "jerry". The user is prompted for the password. If the file exists and cannot be read or cannot be over-written, it is not altered and htpasswd will display an error message.
# htpasswd -c /home/pwww/.htpasswd jerry

Change or update htpasswd

To modifies the password for username "jerry", enter:
# htpasswd /home/pwww/.htpasswd jerry

The user is prompted for the password.

Add another user to htpasswd

To modifies the password for username "tom", enter:
# htpasswd /home/pwww/.htpasswd tom

The user is prompted for the password.


.htaccess (hypertext access) file is a directory-level configuration file supported by several web servers, that allows for decentralized management of web server configuration. The original purpose of .htaccess - reflected in its name - was to allow per-directory access control, by for example requiring a password to access the content. (ref:: wikipedia)

Inside the folder ment to be kept secure place this .htaccess file with the following codes in it.
AuthType Basic
AuthName "Restricted Area"
AuthUserFile /etc/apache2/.htpasswd
Require valid-user
The AuthUserFile is the path where the .htpasswd file is placed for .htaccess to use for authentication.

Note: the ".htpasswd" can aslo be named as "filename.htpasswd" for easy identification.
Example: "softwaretools.htpasswd" for us to easily identify that the password stored inside is for the folder where software tools are stored.

Ping a range of IP address (from Windows machine)

Instead of manually pinging all IP addresses on your LAN you can use this nice command:
Open Command Prompt and type the following line:

FOR /L %i IN (1,1,254) DO ping -n 1 192.168.0.%i | FIND /i "Reply">>c:\ipaddresses.txt

Above command explained

1. The "-n 1" is for only 1 ping packet to be sent to each computer.Change 192.168.0 to match you own Network ID.
2. This will ping all IP addresses on the 192.168.0.0 network segment and create a text file called IPADDRESSES.TXT in C:\, where it will list only the IP addresses that gave a reply.
3. You can also add a -a to the PING command to resolve all the responding IP addresses to HOST names, but doing so will cause the script to take a considerable time to finish: