MySQL Database Import

There are two simple ways in MySQL to load data into MySQL database from a previously backed up file.

Importing Data with LOAD DATA:

MySQL provides a LOAD DATA statement that acts as a bulk data loader. Here's an example statement that reads a file dump.txt from your current directory and loads it into the table mytbl in the current database:

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;

• If the LOCAL keyword is not present, MySQL looks for the datafile on the server host using looking into absolute pathname fully specifies the location of the file, beginning from the root of the filesystem. MySQL reads the file from the given location.
• By default, LOAD DATA assumes that datafiles contain lines that are terminated by linefeeds (newlines) and that data values within a line are separated by tabs.
• To specify a file format explicitly, use a FIELDS clause to describe the characteristics of fields within a line, and a LINES clause to specify the line-ending sequence. The following LOAD DATA statement specifies that the datafile contains values separated by colons and lines terminated by carriage returns and new line character:

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
  -> FIELDS TERMINATED BY ':'
  -> LINES TERMINATED BY '\r\n';

• LOAD DATA assumes the columns in the datafile have the same order as the columns in the table. If that's not true, you can specify a list to indicate which table columns the datafile columns should be loaded into. Suppose your table has columns a, b, and c, but successive columns in the datafile correspond to columns b, c, and a. You can load the file like this:

mysql> LOAD DATA LOCAL INFILE 'dump.txt' 
    -> INTO TABLE mytbl (b, c, a);

Importing Data with mysqlimport

MySQL also includes a utility program named mysqlimport that acts as a wrapper around LOAD DATA so that you can load input files directly from the command line.
To load a data from dump.txt into mytbl, use following command at UNIX prompt.

$ mysqlimport -u root -p --local database_name dump.txt
password *****

If you use mysqlimport, command-line options provide the format specifiers. mysqlimport commands that correspond to the preceding two LOAD DATA statements look like this:

$ mysqlimport -u root -p --local --fields-terminated-by=":" \
   --lines-terminated-by="\r\n"  database_name dump.txt
password *****

The order in which you specify the options doesn't matter for mysqlimport, except that they should all precede the database name.
The mysqlimport statement uses the --columns option to specify the column order:

$ mysqlimport -u root -p --local --columns=b,c,a \
    database_name dump.txt
password *****

Handling Quotes and Special Characters:

The FIELDS clause can specify other format options besides TERMINATED BY. By default, LOAD DATA assumes that values are unquoted and interprets the backslash (\) as an escape character for special characters. To indicate the value quoting character explicitly, use ENCLOSED BY; MySQL will strip that character from the ends of data values during input processing. To change the default escape character, use ESCAPED BY.
When you specify ENCLOSED BY to indicate that quote characters should be stripped from data values, it's possible to include the quote character literally within data values by doubling it or by preceding it with the escape character. For example, if the quote and escape characters are " and \, the input value "a""b\"c" will be interpreted as a"b"c.
For mysqlimport, the corresponding command-line options for specifying quote and escape values are --fields-enclosed-by and --fields-escaped-by.

Read More

MySQL Database Export

The simplest way of exporting a table data into a text file is using SELECT...INTO OUTFILE statement that exports a query result directly into a file on the server host.

Exporting Data with the SELECT ... INTO OUTFILE Statement:

The syntax for this statement combines a regular SELECT with INTO OUTFILE filename at the end. The default output format is the same as for LOAD DATA, so the following statement exports the tutorials_tbl table into /tmp/tutorials.txt as a tab-delimited, linefeed-terminated file:

mysql> SELECT * FROM javatutorials_tbl 
    -> INTO OUTFILE '/tmp/javatutorials.txt';

You can change the output format using options to indicate how to quote and delimit columns and records. To export the tutorial_tbl table in CSV format with CRLF-terminated lines, use this statement:

mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/javatutorials.txt'
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    -> LINES TERMINATED BY '\r\n';

The SELECT ... INTO OUTFILE has the following properties:

• The output file is created directly by the MySQL server, so the filename should indicate where you want the file to be written on the server host. There is no LOCAL version of the statement analogous to the LOCAL version of LOAD DATA.
• You must have the MySQL FILE privilege to execute the SELECT ... INTO statement.
• The output file must not already exist. This prevents MySQL from clobbering files that may be important.
• You should have a login account on the server host or some way to retrieve the file from that host. Otherwise, SELECT ... INTO OUTFILE likely will be of no value to you.
• Under UNIX, the file is created world readable and is owned by the MySQL server. This means that although you'll be able to read the file, you may not be able to delete it.

Exporting Tables as Raw Data:

The mysqldump program is used to copy or back up tables and databases. It can write table output either as a raw datafile or as a set of INSERT statements that recreate the records in the table.
To dump a table as a datafile, you must specify a --tab option that indicates the directory, where you want the MySQL server to write the file.
For example, to dump the tutorials_tbl table from the TUTORIALS database to a file in the /tmp directory, use a command like this:

$ mysqldump -u root -p --no-create-info \
            --tab=/tmp JAVATUTORIALS javatutorials_tbl
password ******

Exporting Table Contents or Definitions in SQL Format:

To export a table in SQL format to a file, use a command like this:

$ mysqldump -u root -p TUTORIALS tutorials_tbl > dump.txt
password ******

This will a create file having content as follows:

---

-- MySQL dump 8.23
--
-- Host: localhost    Database: JAVATUTORIALS
---------------------------------------------------------
-- Server version       3.23.58

--
-- Table structure for table `javatutorials_tbl`
--

CREATE TABLE javatutorials_tbl (
  javatutorial_id int(11) NOT NULL auto_increment,
  javatutorial_title varchar(100) NOT NULL default '',
  javatutorial_author varchar(40) NOT NULL default '',
  submission_date date default NULL,
  PRIMARY KEY  (javatutorial_id),
  UNIQUE KEY JAVA_AUTHOR_INDEX (javatutorial_author)
) TYPE=MyISAM;

--
-- Dumping data for table `javatutorials_tbl`
--

INSERT INTO javatutorials_tbl 
       VALUES (1,'Learn PHP','John Poul','2007-05-24');
INSERT INTO javatutorials_tbl 
       VALUES (2,'Learn MySQL','Abdul S','2007-05-24');
INSERT INTO javatutorials_tbl 
       VALUES (3,'JAVA Tutorial','Sanjay','2007-05-06');

To dump multiple tables, name them all following the database name argument. To dump an entire database, don't name any tables after the database as follows:

$ mysqldump -u root -p JAVATUTORIALS > database_dump.txt
password ******

To back up all the databases available on your host, use the following:

$ mysqldump -u root -p --all-databases > database_dump.txt
password ******

The --all-databases option is available as of MySQL 3.23.12.
This method can be used to implement a database backup strategy.

Copying Tables or Databases to Another Host:

If you want to copy tables or databases from one MySQL server to another, then use mysqldump with database name and table name.
Run the following command at source host. This will dump complete database into dump.txt file:

$ mysqldump -u root -p database_name table_name > dump.txt
password *****

You can copy complete database without using a particular table name as explained above.
Now, ftp dump.txt file on another host and use the following command. Before running this command, make sure you have created database_name on destination server.

$ mysql -u root -p database_name < dump.txt
password *****

Another way to accomplish this without using an intermediary file is to send the output of mysqldump directly over the network to the remote MySQL server. If you can connect to both servers from the host where source database resides, use this command (Make sure you have access on both the servers):

$ mysqldump -u root -p database_name \
       | mysql -h other-host.com database_name

The mysqldump half of the command connects to the local server and writes the dump output to the pipe. The remaining mysql half of the command connects to the remote MySQL server on other-host.com. It reads the pipe for input and sends each statement to the other-host.com server.

Read More

MySQL and SQL Injection

If you take user input through a webpage and insert it into a MySQL database, there's a chance that you have left yourself wide open for a security issue known as SQL Injection. This lesson will teach you how to help prevent this from happening and help you secure your scripts and MySQL statements.
Injection usually occurs when you ask a user for input, like their name and instead of a name they give you a MySQL statement that you will unknowingly run on your database.
Never trust user provided data, process this data only after validation; as a rule, this is done by pattern matching. In the example below, the username is restricted to alphanumerical chars plus underscore and to a length between 8 and 20 chars - modify these rules as needed.

if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches))
{
   $result = mysql_query("SELECT * FROM users 
                          WHERE username=$matches[0]");
}
 else 
{
   echo "username not accepted";
}

To demonstrate the problem, consider this excerpt:

// supposed input
$name = "Qadir'; DELETE FROM users;";
mysql_query("SELECT * FROM users WHERE name='{$name}'");

The function call is supposed to retrieve a record from the users table, where the name column matches the name specified by the user. Under normal circumstances, $name would only contain alphanumeric characters and perhaps spaces, such as the string ilia. But here, by appending an entirely new query to $name, the call to the database turns into disaster: the injected DELETE query removes all records from users.
Fortunately, if you use MySQL, the mysql_query() function does not permit query stacking or executing multiple queries in a single function call. If you try to stack queries, the call fails.
However, other PHP database extensions, such as SQLite and PostgreSQL, happily perform stacked queries, executing all of the queries provided in one string and creating a serious security problem.

Preventing SQL Injection:

You can handle all escape characters smartly in scripting languages like PERL and PHP. The MySQL extension for PHP provides the function mysql_real_escape_string() to escape input characters that are special to MySQL.

if (get_magic_quotes_gpc()) 
{
  $name = stripslashes($name);
}
$name = mysql_real_escape_string($name);
mysql_query("SELECT * FROM users WHERE name='{$name}'");

The LIKE Quandary:

To address the LIKE quandary, a custom escaping mechanism must convert user-supplied % and _ characters to literals. Use addcslashes(), a function that let's you specify a character range to escape.

$sub = addcslashes(mysql_real_escape_string("%something_"), "%_");
// $sub == \%something\_
mysql_query("SELECT * FROM messages WHERE subject LIKE '{$sub}%'");

Read More

MySQL Handling Duplicates

Tables or result sets sometimes contain duplicate records. Sometimes, it is allowed but sometimes it is required to stop duplicate records. Sometimes, it is required to identify duplicate records and remove them from the table. This chapter will describe how to prevent duplicate records occurring in a table and how to remove already existing duplicate records.

Preventing Duplicates from Occurring in a Table:

You can use a PRIMARY KEY or UNIQUE Index on a table with appropriate fields to stop duplicate records. Let's take one example: The following table contains no such index or primary key, so it would allow duplicate records for first_name and last_name.

CREATE TABLE person_tbl
(
    first_name CHAR(20),
    last_name CHAR(20),
    sex CHAR(10)
);

To prevent multiple records with the same first and last name values from being created in this table, add a PRIMARY KEY to its definition. When you do this, it's also necessary to declare the indexed columns to be NOT NULL, because a PRIMARY KEY does not allow NULL values:

CREATE TABLE person_tbl
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   PRIMARY KEY (last_name, first_name)
);

The presence of a unique index in a table normally causes an error to occur if you insert a record into the table that duplicates an existing record in the column or columns that define the index.
Use INSERT IGNORE rather than INSERT. If a record doesn't duplicate an existing record, MySQL inserts it as usual. If the record is a duplicate, the IGNORE keyword tells MySQL to discard it silently without generating an error.
Following example does not error out and same time it will not insert duplicate records.

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
    -> VALUES( 'Jay', 'Thomas');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
    -> VALUES( 'Jay', 'Thomas');
Query OK, 0 rows affected (0.00 sec)

Use REPLACE rather than INSERT. If the record is new, it's inserted just as with INSERT. If it's a duplicate, the new record replaces the old one:

mysql> REPLACE INTO person_tbl (last_name, first_name)
    -> VALUES( 'Ajay', 'Kumar');
Query OK, 1 row affected (0.00 sec)
mysql> REPLACE INTO person_tbl (last_name, first_name)
    -> VALUES( 'Ajay', 'Kumar');
Query OK, 2 rows affected (0.00 sec)

INSERT IGNORE and REPLACE should be chosen according to the duplicate-handling behavior you want to effect. INSERT IGNORE keeps the first of a set of duplicated records and discards the rest. REPLACE keeps the last of a set of duplicates and erase out any earlier ones.
Another way to enforce uniqueness is to add a UNIQUE index rather than a PRIMARY KEY to a table.

CREATE TABLE person_tbl
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10)
   UNIQUE (last_name, first_name)
);

Counting and Identifying Duplicates:

Following is the query to count duplicate records with first_name and last_name in a table.

mysql> SELECT COUNT(*) as repetitions, last_name, first_name
    -> FROM person_tbl
    -> GROUP BY last_name, first_name
    -> HAVING repetitions > 1;

This query will return a list of all the duplicate records in person_tbl table. In general, to identify sets of values that are duplicated, do the following:

• Determine which columns contain the values that may be duplicated.
• List those columns in the column selection list, along with COUNT(*).
• List the columns in the GROUP BY clause as well.
• Add a HAVING clause that eliminates unique values by requiring group counts to be greater than one.

Eliminating Duplicates from a Query Result:

You can use DISTINCT along with SELECT statement to find out unique records available in a table.

mysql> SELECT DISTINCT last_name, first_name
    -> FROM person_tbl
    -> ORDER BY last_name;

An alternative to DISTINCT is to add a GROUP BY clause that names the columns you're selecting. This has the effect of removing duplicates and selecting only the unique combinations of values in the specified columns:

mysql> SELECT last_name, first_name
    -> FROM person_tbl
    -> GROUP BY (last_name, first_name);

Removing Duplicates Using Table Replacement:

If you have duplicate records in a table and you want to remove all the duplicate records from that table, then here is the procedure:

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex
    ->                  FROM person_tbl;
    ->                  GROUP BY (last_name, first_name);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

An easy way of removing duplicate records from a table is to add an INDEX or PRIMAY KEY to that table. Even if this table is already available, you can use this technique to remove duplicate records and you will be safe in future as well.

mysql> ALTER IGNORE TABLE person_tbl
    -> ADD PRIMARY KEY (last_name, first_name);

Read More

MySQL Sequences

A sequence is a set of integers 1, 2, 3, ... that are generated in order on demand. Sequences are frequently used in databases because many applications require each row in a table to contain a unique value and sequences provide an easy way to generate them. This chapter describes how to use sequences in MySQL.

Using AUTO_INCREMENT column:

The simplest way in MySQL to use Sequences is to define a column as AUTO_INCREMENT and leave rest of the things to MySQL to take care.

Example:

Try out the following example. This will create table and after that it will insert few rows in this table where it is not required to give record ID because it's auto incremented by MySQL.

mysql> CREATE TABLE insect
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, # type of insect
    -> date DATE NOT NULL, # date collected
    -> origin VARCHAR(30) NOT NULL # where collected
);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO insect (id,name,date,origin) VALUES
    -> (NULL,'housefly','2001-09-10','kitchen'),
    -> (NULL,'millipede','2001-09-10','driveway'),
    -> (NULL,'grasshopper','2001-09-10','front yard');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM insect ORDER BY id;
+----+-------------+------------+------------+
| id | name        | date       | origin     |
+----+-------------+------------+------------+
|  1 | housefly    | 2001-09-10 | kitchen    |
|  2 | millipede   | 2001-09-10 | driveway   |
|  3 | grasshopper | 2001-09-10 | front yard |
+----+-------------+------------+------------+
3 rows in set (0.00 sec)

Obtain AUTO_INCREMENT Values:

LAST_INSERT_ID( ) is a SQL function, so you can use it from within any client that understands how to issue SQL statements. Otherwise, PHP scripts provide exclusive functions to retrieve auto incremented value of last record.

PHP Example:

After issuing a query that generates an AUTO_INCREMENT value, retrieve the value by calling mysql_insert_id( ):

mysql_query ("INSERT INTO insect (name,date,origin)
VALUES('moth','2001-09-14','windowsill')", $conn_id);
$seq = mysql_insert_id ($conn_id);

Renumbering an Existing Sequence:

There may be a case when you have deleted many records from a table and you want to resequence all the records. This can be done by using a simple trick but you should be very careful to do so if your table is having joins with other table.
If you determine that resequencing an AUTO_INCREMENT column is unavoidable, the way to do it is to drop the column from the table, then add it again. The following example shows how to renumber the id values in the insect table using this technique:

mysql> ALTER TABLE insect DROP id;
mysql> ALTER TABLE insect
    -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
    -> ADD PRIMARY KEY (id);

Starting a Sequence at a Particular Value:

By default, MySQL will start sequence from 1 but you can specify any other number as well at the time of table creation. Following is the example where MySQL will start sequence from 100.

mysql> CREATE TABLE insect
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, # type of insect
    -> date DATE NOT NULL, # date collected
    -> origin VARCHAR(30) NOT NULL # where collected
);

Alternatively, you can create the table and then set the initial sequence value with ALTER TABLE.

mysql> ALTER TABLE t AUTO_INCREMENT = 100;

Read More

MySQL Metadata

There are three informations, which you would like to have from MySQL.

• Information about the result of queries: This includes number of records affected by any SELECT, UPDATE or DELETE statement.
• Information about tables and databases: This includes information pertaining to the structure of tables and databases.
• Information about the MySQL server: This includes current status of database server, version number etc.

It's very easy to get all these information at mysql prompt, but while using PERL or PHP APIs, we need to call various APIs explicitly to obtain all these information. Following section will show you how to obtain this information.

Obtaining the Number of Rows Affected by a Query:

PERL Example:

In DBI scripts, the affected-rows count is returned by do( ) or by execute( ), depending on how you execute the query:

# Method 1
# execute $query using do( )
my $count = $dbh->do ($query);
# report 0 rows if an error occurred
printf "%d rows were affected\n", (defined ($count) ? $count : 0);

# Method 2
# execute query using prepare( ) plus execute( )
my $sth = $dbh->prepare ($query);
my $count = $sth->execute ( );
printf "%d rows were affected\n", (defined ($count) ? $count : 0);

PHP Example:

In PHP, invoke the mysql_affected_rows( ) function to find out how many rows a query changed:

$result_id = mysql_query ($query, $conn_id);
# report 0 rows if the query failed
$count = ($result_id ? mysql_affected_rows ($conn_id) : 0);
print ("$count rows were affected\n");

Listing Tables and Databases:

This is very easy to list down all the databases and tables available with database server. Your result may be null if you don't have sufficient privilege.
Apart from the method I have mentioned below, you can use SHOW TABLES or SHOW DATABASES queries to get list of tables or databases either in PHP or in PERL.

PHP Example:

<?php
$con = mysql_connect("localhost", "userid", "password");
if (!$con)
{
  die('Could not connect: ' . mysql_error());
}

$db_list = mysql_list_dbs($con);

while ($db = mysql_fetch_object($db_list))
{
  echo $db->Database . "<br />";
}
mysql_close($con);
?>

Getting Server Metadata:

There are following commands in MySQL which can be executed either at mysql prompt or using any script like PHP to get various important informations about database server.

Command	Description
SELECT VERSION( )	Server version string
SELECT DATABASE( )	Current database name (empty if none)
SELECT USER( )	Current username
SHOW STATUS	Server status indicators
SHOW VARIABLES	Server configuration variables

Read More