Wednesday, May 7, 2008

MySQL in a nutshell

Introduction

MySQL is a relational database management system (RDBMS) based on SQL (Structured Query Language). I was first released in January, 1998.

In most cases the developers of database applications expect the systems administrator to be able to independently prepare a database for their applications to use. The steps to do this include:

1. Install and start MySQL.
2. Create a MySQL "root" user.
3. Create a regular MySQL user that the application will use to access the database.
4. Create your application's database.
5. Create your database's data tables.
6. Perform some basic tests of your database structure.

The /etc/my.cnf file is the main MySQL configuration file. It sets the default MySQL database location and other parameters. The typical home/SOHO user won't need to edit this file at all.According to the /etc/my.cnf file, MySQL databases are usually located in a subdirectory of the /var/lib/mysql/ directory. If you create a database named test, then the database files will be located in the directory /var/lib/mysql/test.

MySQL stores all its username and password data in a special database named mysql.

Creating a MySQL "root" Account

Only two steps are necessary for a brand new MySQL installation.

1. Make sure MySQL is started.
2. Use the mysqladmin command to set the MySQL root password. The syntax is as follows:

# mysqladmin -u root password new-password

MySQL has its own command line interpreter (CLI). You can access the MySQL CLI using the mysql command followed by the -u option for the username and -p

# mysql -u root -p

Creating a database


mysql> create database databasename;

Deleting a database

mysql> drop database databasename;

Adding user

You can add users and give privileges for user to access database using grant command.

Syntax:

mysql> grant all privileges on database.* to username@"servername" identified by 'password';

Example :
Creating a database named exampledb and creating a user named newuser with password newuser

mysql> create database exampledb
mysql> grant all privileges on exampedb.* to newuser@"servername" identified by 'newuser';
mysql> flush privileges;

For changing mysql password for the user username as root from mysql command prompt

mysql> UPDATE mysql.user SET Password = OLD_PASSWORD('newuser') WHERE Host = 'localhost' AND User = 'newuser';

How to Reset mysql root password

1. Stop mysqld
# /etc/init.d/mysql stop

2. Start mysql using skip grant tables option
# /etc/init.d/mysql start --skip-grant-tables --user=root

3. Connect to the mysqld server with this command:
# mysql -u root

4. Issue the following statements
mysql> UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root';
mysql> flush privileges;

5. You should be able to connect using the new password.

Recovering / Changing Your MySQL Root Password

The steps you need are:

1) Stop MySQL
# service mysqld stop

2) Start MySQL in Safe mode with the mysqld_safe command and tell it not to read the grant tables with all the MySQL database passwords.

# mysqld_safe --skip-grant-tables --skip-networking &

3) MySQL is now running without password protection. You now have to use the familiar mysql -u root command to get the mysql> command prompt. ( -p flag is not required) As expected, you will not be prompted for a password.

# mysql -u root

4) You will now have to use the mysql database which contains the passwords for all the databases on your system and modify the root password. In this case we are setting it to ack33nsaltf1sh.

mysql> use mysql;

5) Exit MySQL and restart the mysqld daemon.

mysql> exit

# service mysqld restart

MySQL Database Backup

The syntax for backing up a MySQL database is as follows:
# mysqldump -u [username] -p[password] [database] > [backup_file]

example :
#mysqldump -u newuser -pnewuser example > example.sql

MySQL Database Restoration
#mysql -u [username] -p[password] [database] < [backup_file]

example :
#mysql -u newuser -pnewuser example < style="font-weight: bold;">Some useful mysql commands

List all your MySQL databases:
mysql> show databases;

Listing the data tables in MySQL database:
mysql> use databasename;
mysql> show tables;

example :
mysql> use exampledb;
mysql> show tables;

Viewing your mySQL database's table structure:
mysql> describe tablename;

Viewing the contents of a table:
mysql> select * from tablename limit 1;

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

Monday, May 5, 2008

Some Important Definitions

http connection : http connection means the requests server receives for accessing your website. In other words http connection indicates the traffic to your website.

iptables : it is a packet filtering tool which allows system administrator to define incoming and outgoing packets to a system using certain rules.

Virtual Private Server(VPS): is a virtual private server, which is functionally identical to an isolated standalone server, with its own IP addresses, processes, files, its own users database, its own configuration files, its own applications, system libraries, and so on. Virtual Private Servers share one Hardware Node and one OS kernel. However, they are isolated from each other.Virtual Private Server 0 is used to designate the Hardware Node itself.

Virtuozzo : which allows you to create multiple isolated Virtual Private Servers on a single physical server to share hardware, licenses, and management effort with maximum efficiency.

SSH : SSH stands for Secure Shell. It is a protocol for logging on to a remote machine and executing commands on that machine. It provides secure encrypted communications between two untrusted hosts over an insecure network.

Domainkeys: DomainKeys is an email authentication technology developed by Yahoo, and is primarily used as an additional anti-spam and anti-phishing method and to prove and protect email sender identity.

fsck : The system utility fsck (for "file system check" or "file system consistency check") is a tool for checking the consistency of a file system in the Unix system.

Generally, fsck is run automatically at boot time when the system detects that a file system is in an inconsistent state, indicating a non-graceful shutdown, such as a crash or power loss. Typically, fsck utilities provide options for either interactively repairing damaged file systems (the user must decide how to fix specific problems), automatically deciding how to fix specific problems (so the user doesn't have to answer any questions), or reviewing the problems that need to be resolved on a file system without actually fixing them.

Fsck can also be run manually by the system administrator if there is believed to be a problem with the file system.

IP : The Internet Protocol (IP) is a protocol used for communicating data across a packet-switched internetwork. IP is a network layer protocol in the Internet protocol suite and is encapsulated in a data link layer protocol (e.g., Ethernet). In short, the way in which packets of data are addressed and sent across the Internet.

Firewall : A firewall is a program configured to permit, deny, or encrypt incoming and outgoing traffic in a system based on a set of rules and other criteria.

runlevel : The term runlevel refers to a mode of operation in one of the computer. Conventionally, seven runlevels exist, numbered from zero to six, though up to ten, from zero to nine, may be used. When a computer enters runlevel zero, it halts, and when it enters runlevel six, it reboots.
Additional runlevels are as follows :

1 Single-User Mode
2 Multi-User Mode
3 Multi-User Mode with Networking
4 Unused
5 X11

spf record : SPF helps mail servers distinguish forgeries from real mail by making it possible for a domain owner to say, "I only send mail from these machines." That way, if any other machines try to send mail from that domain, the mail server knows that the FROM address is forged.

Due to the fact that a significant majority of SPAM comes from forged addresses, a new Sender Policy Framework (SPF) standard is being implemented by a number of ISPs and mail hosts, including several major providers (Hotmail, Yahoo, AOL, etc). Those providers have begun to require SPF to allow mail through their networks. Mail systems will need SPF records for their domains if they want their mail to be accepted by those providers.

Protocol - An agreed upon format for transmitting data between two devices. In short a 'rule'.

IP address - The format of an IP address is a 32-bit(4 byte) numeric address written as four numbers separated by periods. Each number can be zero to 255. For example, 1.160.10.240 could be an IP address. It is analogous to your telephone number in that the telephone number is used by the telephone network to direct calls to you. The IP address is used by the Internet to direct data to your computer, e.g. the data your web browser retrieves and displays when you surf the net.

Web server - A computer that delivers or "serves up" web pages which are then viewed in web browsers. Requires an Internet connection, server software, an IP address, and a domain name.

Subnet—A portion of a network sharing a particular subnet address.

Subnet mask - A 32-bit combination used to describe which portion of an address refers to the subnet and which part refers to the host.

Interface - A network connection.

Daemon : a daemon (pronounced /ˈdiːmən/ or /ˈdeɪmən/[1]) is a computer program that runs in the background, rather than under the direct control of a user; they are usually initiated as background processes. Typically daemons have names that end with the letter "d": for example, syslogd, the daemon that handles the system log, or sshd, which handles incoming SSH connections.

Transmission Control Protocl (TCP) : is the protocol that creates connections between two computer over the internet, allowing them to pass data back and forth. TCP is made to allow the transmitted data to be reassembled into the proper form when it reached its destination.

chkrootkit (Check Rootkit) : is a common Unix-based program intended to help system administrators check their system for known rootkits.

A rootkit is a program (or combination of several programs) designed to take fundamental control (in Unix terms "root" access, in Windows "Administrator" access) of a computer system, without authorization by the system's owners and legitimate managers. Access to the hardware (e.g., the reset switch) is rarely required as a rootkit is intended to seize control of the operating system running on the hardware.

Load Average : It is the average sum of the number of processes waiting in the run-queue plus the number currently executing over 1, 5, and 15 minute time periods.

.

Thursday, May 1, 2008

Useful Linux Commands

Command to find files accessed in last 30 days. will find files that is accessed in last 30 days, under root folder.
# find / type f -atime -30
----------------------------------------------------------------------------------------------------------------------------------------

List contents of a folder along with contents of its subfolder. But it will traverse only to a depth of one. ie, it will not show the contents of subfolder's subfolder.
# ls *
-----------------------------------------------------------------------------------------------------------------------------------------

To print the iptables rules along with line number.
# iptables -L --line-numbers
-----------------------------------------------------------------------------------------------------------------------------------------

To find a particular rule with rule number #; where # is the rule number you want to list
# iptables -L OUTPUT --line-numbers | grep ^#
-----------------------------------------------------------------------------------------------------------------------------------------

Change permission only for folders
# find . -type d -exec chmod 755 {} \;
-----------------------------------------------------------------------------------------------------------------------------------------

List with 777 permission
#find . -type d -perm 777
--------------------------------------------------------------------------------------------------------------------------

To list all the processes listening to port 80
# lsof -i TCP:80|awk {'print $2'}
-----------------------------------------------------------------------------------------------------------------------------------------

To kill all the process listening to apache port 443/80
# lsof -i TCP:443|awk {'print $2'} | xargs kill -9
-----------------------------------------------------------------------------------------------------------------------------------------

Recursively chmod only directories
find . -type d -exec chmod 755 {} \;
-----------------------------------------------------------------------------------------------------------------------------------------

Recursively set the execute bit on every directory
chmod -R a+X *
The +X flag sets the execute bit on directories only
-----------------------------------------------------------------------------------------------------------------------------------------

Recursively chmod only files
find . -type f -exec chmod 644 {} \;
-----------------------------------------------------------------------------------------------------------------------------------------

Recursively chmod only PHP files (with extension .php)
find . -type f -name '*.php' -exec chmod 644 {} \;
-----------------------------------------------------------------------------------------------------------------------------------------

Find all files in /home/user/demo directory
$ find /home/user/demo -print
-----------------------------------------------------------------------------------------------------------------------------------------

Now find all files in /home/user/demo directory with permission 777
$ find /home/user/demo -perm 777 -print
-----------------------------------------------------------------------------------------------------------------------------------------

Next you need to apply chmod on all these files using -exec option:
$ find /home/user/demo -perm 777 -print -exec chmod 755 {} \;
-----------------------------------------------------------------------------------------------------------------------------------------

Command to find files modified on July 12
ll|grep dr|awk '{print $9}' > 123
for i in `cat 123`;do ls -ld $i;done|grep "Jul 12"
-----------------------------------------------------------------------------------------------------------------------------------------

How to See the SSH password guesses

First, find the PID of the listening SSH daemon process:
# ps axuww | egrep 'PID|ssh'
Now become root and attach to the running daemon with strace:
#
strace -f -e 'read,write' -p12345
----------------------------------------------------------------------------------------------------------------------------------------

Screen Command

Command to create screen:
# screen -S screen_name

To exit from screen:
Just close the shell without logout

To list all running screens:
# screen -ls

To login to a particular screen with screen name "xxxx.screen_name"
# screen -r xxxx.screen_name