MySql queries:

Inserting data into a existing table:

Syntax without column names specified:

INSERT INTO table_name VALUES ('value1','value2');

Example:

In the example we have inserted the data in a customer_table where we have 4 columns with name cust_id, first_name, last_name, age and we have inserted the data in the columns with this command.

Syntax without column names specified:

INSERT INTO table_name ('column1','column2') VALUES ('value1','value2');

Example:

In this example we have inserted the data in three columns excluding the last_name column, by doing this it will store the information in all the columns and will show NULL in the last_name.

Inserting data in multiple rows:

  • To ‘SELECT’ a statement in a table:
Select column_name from table_name;

Following command will show the data stored in the column_name column in the database.

  • To use ‘WHERE’ command in a table:
Select column_name from table_name Where (column_name = condition);

For example:

We have a table with Customers name , age and email given and we need to see only the data of customer whose age is greater than 25.

Select * from customer_table where age>25;

This command will show the data of the customers whose age is greater than 25.

  • To use logical operators in the query:
Select column_name from table_name Where (column_name = condition) AND (column_name = condition) OR (column_name = condition);
  • To update data in the table:
UPDATE table_name SET column_name='abc' WHERE condition(column);

For example to update the last name of a customer:

UPDATE customer_table SET Last_name='John' WHERE cust_id=5

Tagged : / / /

Creating a Table using MySql queries:

In this we will learn how we can create tables in the database using sql queries. As we know it becomes quite complicated to make the tables manually in the database. To ease the process we can follow the steps to make tables using my sql queries.

Syntax:

CREATE TABLE table_name (
    column1 column1 datatype column1 constraint,
    column2 column2 datatype column2 constraint,
    column3 column3 datatype column3 constraint,
   ....
);

Constraints:

  • NOT NULL CONSTRAINT – Ensures that a column cannot have a null value.
  • DEFAULT CONSTRAINT – Provides a default value for a column when none is specified.
  • UNIQUE CONSTRAINT – Ensures that all value in a column are different.
  • CHECK CONSTRAINT – Make sure all values in a column satisfy certian criteria.
  • PRIMARY_KEY CONSTRAINT – Used to uniquely identify a row in a table.
  • FOREIGN_KEY CONSTRAINT – Used to ensure referential integrity of the data.

Keys:

  • A primary key is used to uniquely identify each row in a table.
  • A primary key can consists of one or more columns on a table.
  • When multiple columns are used as primary key, it is called as Composite key.
  • A foreign key is a column (or columns) that references a column (most often primary key) of other table.
  • The purpose of foreign key is to referential integrity of the data.

As shown in the above picture, Cust_ID is the foreign key for order table whereas it is primary key for in the Customer table that means the value of Cust_ID will not change in either of the table.

For example we will create a table named as customer_table inside the ‘test’ database:

Tagged : / /

MySQL Error 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

rajeshkumar created the topic: MySQL ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

MySQL ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

root@server [~]# mysql

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
root@server [~]#

root@server [/var/lib/mysql/mysql]# ls -lhd /var/lib/mysql
drwxr-x–x 179 mysql mysql 12K Aug 25 01:44 /var/lib/mysql/

root@server [/var/lib/mysql/mysql]# chmod 711 /var/lib/mysql/mysql

root@server1 [/var/lib/mysql/mysql]# ls -lhd /var/lib/mysql/mysql
drwx–x–x 2 mysql mysql 4.0K Aug 25 01:32 /var/lib/mysql/mysql/

root@server1 [/var/lib/mysql/mysql]# service mysql restart
Shutting down MySQL.. [ OK ]
Starting MySQL [ OK ]
root@server1 [/var/lib/mysql/mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1
Server version: 5.0.51a-community MySQL Community Edition (GPL)Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> quit

Bye
Regards,
Rajesh Kumar
Twitt me @ twitter.com/RajeshKumarIn

Tagged :

Creating file system info and put into mysql db

rajeshkumar created the topic: Creating file system info and put into mysql db

Create a perl script for following task…
1. Read the directory mentioned by users and list all the files along with their size and Create a property file which has following info of each file.

# Server name Path File Name date Size
1 rajesh /perforce/server:port/root xyz.txt today 10KB

2. Insert this data into mysql Db in table in same fashion as it is in property file

Regards,
Rajesh Kumar
Twitt me @ twitter.com/RajeshKumarIn

rajeshkumar replied the topic: Re: Creating file system info and put into mysql db

Hi Tushar,

I could make it for reading directory and displaying files in such a given format…

My example program is

$DIR = “/home/rajesh”;

@dirlist = `ls -l $DIR | tr -s ” ” ” “| cut -d ” ” -f5,9`;
foreach (@dirlist)
{
print “$_ \n”;
}

Regards,
Rajesh Kumar
Twitt me @ twitter.com/RajeshKumarIn

Tagged :

SQL Injection: How to check or test for vulnerabilities

sql-injection
SQJ Injection
There are a number of ways of testing an application for vulnerabilities such as SQL Injection. The tests break down into three different methodologies:
Blind Injection:
MySQL example:
http://localhost/test.php?id=sleep(30)
If this SQL statement is interpreted by the database then it will take 30 seconds for the page to load.
Error Messages:
http://localhost/test.php?id='”
If error reporting is enabled and this request is vulnerable to sql injection then the following error will be produced:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘”‘ at line 5
Tautology Based Injection:
http://localhost/test.php?username=’ or 1=1 /*&password=1
In this case supplying a Tautology, or a statement that is always true provides a predictable result. In this case the predictable result would be logging in the attacker with the first user in the database, which is commonly the administrator.
There are tools that automate the use of the methods above to detect SQL Injection in a web application. There are free and open source tools such as Wapiti and Skipfish that do this.
More –
Tagged : / / / / /

MySQL Basic Troubleshooting Guide | MySQL common Issues

mysql-troubleshooting

PLEASE NOTE: I am currently reviewing this Article.

How to check the mysql file location:
> which mysql
> locate mysql

Check mysqld process is started or not?
> service mysqld status
> “mysqld is stopped” – Means mysqld is not running
> “mysqld: unrecognized service” – Means mysqld is not set in service. This can be register using chkconfig under /etc/init.d.
> ps -eaf | grep mysqld

To check if port 3306 is bind with mysqld or another program.
> lsof -i TCP:3306
> netstat -lp | grep 3306
> netstat -tap | grep mysql
> ps -aux | grep mysql
> netstat -a -t – to show only tcp ports

Note: – if you could not found 3306 is listening with mysqld, then it must not be running or running with another ports. To find this, refer my.cnf and pid-file

How to Stop mysqld?
> /etc/init.d/mysqld stop
> kill <pid>
> /sbin/service mysqld start/stop/restart

If you have problems starting the server, here are some things to try:

Check the error log to see why the server does not start.
The Location of error log file can be found in my.cnf or my.ini(windows). please refer below to know more about my.cnf file. The log file can be specified also in mysqld service resided
under /etc/init.d/

Make sure that the server knows where to find the data directory.
Make sure my.cnf file is set with “datadir” and its required ownership and permission. Make sure that the server can access the data directory. The ownership and permissions of the data directory and its contents must be set such that the server can read and modify them.

Verify that the network interfaces the server wants to use are available. If the server starts but you cannot connect to it, you should make sure that you have an entry in /etc/hosts that
looks like this:

127.0.0.1 localhost

If mysqld is running, To find all the variable set using
> mysqladmin -h hostname -p variables

Issues 1:
Can’t start server: Bind on TCP/IP port: Address already in use
Can’t start server: Bind on unix socket…
Solution:
Use ps to determine whether you have another mysqld server running. If so, shut down the server before starting mysqld again.

Issues 2:
mysqld will not start
Can’t start server: Bind on TCP/IP port: Address already in use
Do you already have another mysqld server running on port: 3306 ?
Solution:
This may be due to 3306 port is being used or Disk Space issues. You can look up on the log file.

Recovering a crashed MySQL server if the system itself or just the MySQL daemon corrupted table files

You’ll see this when checking the /var/log/syslog, as the MySQL daemon checks tables during its startup.

Apr 17 13:54:44 live1 mysqld[2613]: 090417 13:54:44 [ERROR]
/usr/sbin/mysqld: Table ‘./database1/table1’ is marked as
crashed and should be repaired

In this situation, Database and tables need to be repaired.

> mysql -u root -p
mysql> REPAIR TABLE database1.table1;

This works, but there is a better way: First, using OPTIMIZE in combination with REPAIR is suggested and there is a command line tool only for REPAIR jobs. Consider this call:
> mysqlcheck -u username -p -o –auto-repair -v –optimize database_name

Using “mysqlcheck” is, that it can also be run against all databases in one run
> mysqlcheck -u root -p –auto-repair –check –optimize –all-databases

Recreating databases and tables the right way
mysql> show create database database1;

How to find location of my.cnf (or my.ini on Windows)?

Default options are read from the following files in the given order:
/etc/my.cnf
/etc/mysql/my.cnf
/usr/etc/my.cnf
~/.my.cnf

Or, on Windows:
Default options are read from the following files in the given order:
C:\Windows\my.ini
C:\Windows\my.cnf
C:\my.ini
C:\my.cnf
C:\Program Files\MySQL\MySQL Server 5.5\my.ini
C:\Program Files\MySQL\MySQL Server 5.5\my.cnf

This command also help you in linux to find my.conf file location…
> strace mysql “;” 2>&1 | grep cnf

Another Option to use following commands…
> whereis my.cnf
> locate my.cnf
> find – -name my.cnf

my.cnf will contain following…
datadir – The path to the MySQL data directory.
tmpdir
default-character-set
default-storage-engine
innodb_data_home_dir
log-error- The location of log file.
pid-file – The path name of the file in which the server should write its process ID.

MySQL Performance Troubleshooting
There are three main utilities I’ll run to in a situation like this:

top
First I’m going to use top to see if anything is hogging CPU on the machine. If there are non-mysql processes using a substantial percentage of the CPU cores, I’m going to want to havea look at what that is and see about limiting its use or moving it a dedicated server. If I see mysqld using up a lot of CPU, I know it’s working hard and will have to drill into what’shappening inside of MySQL (maybe some poorly written queries). If nothing is apparently chewing up the CPU time, I know that the problem is likely elsewhere.

vmstat 5
I generally run this for at least two or three minutes to get a sense of what the CPU and memory use are like. I’m also watching to see how much time the CPU is stalled waiting for I/Orequests. Doing this for several minutes will make the occasional spikes really stand out and also allow for more time to catch those cron jobs that fire up every few minutes.

iostat -x 5 | grep sdb
I’m going to run it with a short interval (5 or 10 seconds) and do so for several minutes. I’ll likely filter the output so that I only see the output for the most active disk or array (the onewhere all of MySQL’s data lives).

slow queries
To find out about slow queries I’m going hope that the slow query log is enabled and the server has a sane long_query_time. But even the default of 10 seconds is helpful in truly badsituations.

MySQL’s error log
I’ll also want to glance through MySQL’s error log to make sure nothing bad-looking has started to appear. To Find a error log file location, refer my.cnf file “log-error”.

Network issues
telnet your_host_name tcp_ip_port_number.

mysqladmin :
mysqladmin is a client for performing administrative operations. You can use it to check the server’s configuration and current status, to create and drop databases, and more.

mysqladmin -h hostname -p <command_as_follows>

–help, -? – Display a help message and exit.
refresh – Flush all tables and close and open log files.
variables – Display the server system variables and their values.
flush-logs – Flush all logs.
flush-privileges – Reload the grant tables (same as reload).
flush-status – Clear status variables.
password new-password – Set a new password. This changes the password to new-password for the account that you use with mysqladmin for connecting to the server.
ping – Check whether the server is available
processlist – Show a list of active server threads.
shutdown – Stop the server.
status – Display a short server status message.
Uptime – The number of seconds the MySQL server has been running.
Slow queries – The number of queries that have taken more than long_query_time seconds
Open tables – The number of tables that currently are open.

Reference
http://dev.mysql.com/doc/refman/5.5/en/mysqladmin.html
http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html
http://www.linux-mag.com/id/7473/
http://dev.mysql.com/doc/refman/5.1/en/starting-server.html

Tagged : / / / / / / / / / / / / / / / /