[Admin]

What is MySQL?

MySQL - An open source database management system developed by MySQL AB, http://www.mysql.com. MySQL was started with mSQL with fast low-level (ISAM) routines. So MySQL and mSQL are having almost the same application programming interfaces and command line interfaces.
Main features of MySQL (extracted from MySQL manual):

• Works on many different platforms.
• APIs for C, C++, Eiffel, Java, Perl, PHP, Python, Ruby, and Tcl are available.
• Fully multi-threaded using kernel threads.
• Provides transactional and non-transactional storage engines.
• Uses very fast B-tree disk tables (MyISAM) with index compression.
• Very fast joins using an optimized one-sweep multi-join.
• SQL functions are implemented using a highly optimized class library and should be as fast as possible.
• Support for aliases on tables and columns as required by SQL-92.
• Handles large databases. We use MySQL Server with databases that contain 50 million records. We also know of users that use MySQL Server with 60,000 tables and about 5,000,000,000 rows.
• Clients may connect to the MySQL server using TCP/IP sockets on any platform.
• The Connector/ODBC interface provides MySQL support for client programs that use ODBC (Open-DataBase-Connectivity) connections.
• The Connector/JDBC interface provides MySQL support for Java client programs that use JDBC connections.

Installing MySQL 4.0.18
1. Go to http://dev.mysql.com/downloads and select the latest production release.
2. Go to "Windows downloads" and select "Without installer (unzip in C:\)" entry.
3. Save the downloaded file, mysql-4.0.18-win-noinstall.zip, on your hard disk.
4. Unzip mysql-4.0.18-win-noinstall.zip to "\" and rename "\mysql-4.0.18" to "\mysql".
5. Open a command window and run "\mysql\bin\mysqld" to start MySQL server:

\mysql\bin\mysqld --console
nnnnnn 22:16:53  InnoDB: Started
\local\mysql\bin\mysqld: ready for connections.
Version: '4.0.18-max-debug'  socket: ''  port: 3306

6. Open another command window and run "\mysql\bin\mysqladmin" to administrate MySQL server:

\mysql\bin\mysqladmin pingmysqld is alive\mysql\bin\mysqladmin shutdown

If everything works correctly, you should see MySQL server printing notice and shutting down in the first command window.

Creating First Table in MySQL

In the previous section, we learned how to start and shutdown MySQL server. Now let's see how we can use MySQL client interface to create a table and run queries. First start the MySQL server in one command window, then run the start the MySQL client interface in another command window, and run the following MySQL client commands:

\mysql\bin\mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.18-max-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+
2 rows in set (0.06 sec)

mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table hello (message varchar(80));
Query OK, 0 rows affected (0.58 sec)

mysql> insert into hello (message) values ('Hello world!');
Query OK, 1 row affected (0.38 sec)

mysql> select * from hello;
+--------------+
| message      |
+--------------+
| Hello world! |
+--------------+
1 row in set (0.04 sec)

mysql> drop table hello;
Query OK, 0 rows affected (0.34 sec)

mysql> quit;
Bye

Conclusions:

• MySQL is indeed easy to install and easy to use.
• The MySQL server, mysqld, runs as a daemon and listens on IP port 3306 for client program connections.
• MySQL offers an administrative client program, mysqladmin, to operate the MySQL server.
• MySQL offers a simple client program, mysql, to run SQL statements against the MySQL server.

[Admin]

What's in MySQL?
Programs and tools offered in MySQL:

• mysqld - MySQL server daemon.
• mysqladmin - Administrator tool, command line based.
• mysql - SQL client tool, command line based.
• mysqlimport - Tool to load data into tables from text files.
• mysqldump - Tool to dump data of the an entire database.
• mysqlcheck - Tool to check and repair databases.
• mysqlmanager - SQL client tool, GUI based.

Using the SQL Client Tool - mysql
mysql is very easy to use:

• To launch mysql, run "\mysql\bin\mysql --host hostName databaseName".
• To run any SQL statement, enter "sqlStatement;". The last character, ";", triggers mysql to execute the statement on the server.
• To quit from mysql, enter "quit".
• To get help, enter "help".

In the following example, I executed 4 SQL statements with mysql:

\mysql\bin\mysql --host localhost test
......
 
mysql> create table hello (message varchar(80));
Query OK, 0 rows affected (0.41 sec)
 
mysql> insert into hello (message) values ('Hello world!');
Query OK, 1 row affected (0.03 sec)
 
mysql> select * from hello;
+--------------+
| message      |
+--------------+
| Hello world! |
+--------------+
1 row in set (0.00 sec)
 
mysql> drop table hello;
Query OK, 0 rows affected (0.00 sec)
 
mysql> quit
Bye

Note that:

• The official name of mysql is MySQL Monitor.
• mysql reports you back on the execution time of each statement.
• Result of the SELECT statement is nicely formatted.

In the previous example, SQL statements were executed interactively one by one. Another way to execute SQL statements is to put them into a file, and execute them in one command. First let's all the statements we used in the previous into a file, hello.sql:

-- hello.sql
create table hello (message varchar(80));
insert into hello (message) values ('Hello world!');
select * from hello;
drop table hello;

To execute the statements in hello.sql, you can use the "source" command inside mysql:

\mysql\bin\mysql --host localhost test
......
 
mysql> source hello.sql
Query OK, 0 rows affected (0.05 sec)
 
Query OK, 1 row affected (0.00 sec)
 
+--------------+
| message      |
+--------------+
| Hello world! |
+--------------+
1 row in set (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)

Or you can run mysqsl in batch mode:

\mysql\bin\mysql --host localhost test < hello.sql
message
Hello world!

As you can see, this is a much better way to execute SQL statements. So from now on, I will always run mysql in batch mode.

Dumping Data into Files - mysqldump

mysqldump is a tool to dump table structure and data to files. This tool can be used in different ways.

1. Dump tables of a database as SQL statements into a single file. Table structures will be dumped as create table statements. Data rows will be dumped as insert statements. If table is not specified, all tables will be dumped.

\mysql\bin\mysqldump --result-file=file.sql db_name [tbl_name]

2. Dump tables of a database into two files per table. One file contains a create table statement. The other file contains table data as tab delimited values. Output files will be created in a sub directory specified in the command line.

\mysql\bin\mysqldump --tab=dir_name db_name [tbl_name]

3. Dump tables of a database as XML format into a single file.

\mysql\bin\mysqldump --xml --result-file=file.xml db_name [tbl_name]

In order to test mysqldump, I created a database with two tables with the following SQL file:

-- CreateDatabase.sql --
DROP DATABASE IF EXISTS Library;
CREATE DATABASE Library;
USE Library;
--
CREATE TABLE Book (ID INT, Title VARCHAR(64), Author_ID INT);
INSERT INTO Book VALUES (1, 'Java', 1);
INSERT INTO Book VALUES (2, 'C++', 1);
INSERT INTO Book VALUES (3, 'FORTRAN', 2);
--
CREATE TABLE Author (ID INT, Name VARCHAR(16));
INSERT INTO Author VALUES (1, 'Taree');
INSERT INTO Author VALUES (2, 'Internet');
--
SELECT Title, Name FROM Book INNER JOIN Author ON Book.Author_ID=Author.ID;

[Admin]

... continued from previous post

Output from this SQL file:

Title   Name
Java    Internet
C++     Internet
FORTRAN Taree

I used the following commands to test mysqldump. The output files look perfect to me.

\mysql\bin\mysqldump --result-file=Library.sql Library
mkdir Library
\mysql\bin\mysqldump --tab=Library Library
\mysql\bin\mysqldump --xml --result-file=Library.xml Library

Loading Data from Files - mysqlimport

mysqlimport is a tool to load data stored in files into tables. Data should be stored in files as tab delimited values. Data file names without extensions should match table names. The command line syntax of mysqlimport is:

\mysql\bin\mysqlimport db_name file_1, file_2, ...

For example, I used the following command to load data back into the book table. Remember the data was dumped earlier by the mysqldump command with the --tab option.

\mysql\bin\mysqlimport Library Library\book.txt
 
Library.book: Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

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

Installing Perl Database Driver for MySQL

Here is what we need to access MySQL from Perl programs:

• Perl implementation.
• MySQL server.
• Perl DBI module.
• Perl DBD::mysql module.

For Perl implementation, I have installed ActivePerl.

To install MySQL server, see the previous post, "Installing MySQL".
Perl DBI (Database Interface) module is the basic abstraction layer for working with external database servers. If DBI module is not available on your Perl installation, you could to go to http://search.cpan.org/dist/DBI/ and download DBI-1.42.tar.gz. But installing DBI module from the .tar file seems to be too complicated.

Another easy way to install the DBI module is to use PPM (Perl Package Management) directly from Internet, if you have ActivePerl installed:

\perl\bin\ppm
PPM interactive shell (2.1.5) - type 'help' for available commands.
PPM> install DBI
Install package 'DBI?' (y/N): y
Installing package 'DBI'...
Bytes transferred: 435739
Installing D:\Perl\site\lib\auto\DBI\dbd_xsh.h
Installing D:\Perl\site\lib\auto\DBI\DBI.bs
......
Installing D:\Perl\html\site\lib\DBI.html
Installing D:\Perl\html\site\lib\Win32\DBIODBC.html
......
Installing D:\Perl\site\lib\DBI.pm
Installing D:\Perl\site\lib\Win32\DBIODBC.pm
......
Installing D:\Perl\bin\dbiprof
Installing D:\Perl\bin\dbiprof.bat
Installing D:\Perl\bin\dbiproxy
Installing D:\Perl\bin\dbiproxy.bat
Writing D:\Perl\site\lib\auto\DBI\.packlist
PPM> quit
Quit!

I don't know how PPM exactly works. My guess is that it comes with a list of modules (packages), not installed, but defined with information about where to go on the Internet to get them and how to install them. Installing additional modules are so easy in this way, as you can see from this example. But I feel that there is a security risk here by allowing PPM to interact with Internet freely without you knowing what it is doing exactly.

The DBD:mysql module is the database driver for MySQL required by the DBI module. It can also be installed by PPM, if you have ActivePerl installed:

\perl\bin\ppm
PPM> install DBD-mysql
Install package 'DBD-mysql?' (y/N): y
Installing package 'DBD-mysql'...
Bytes transferred: 179124
Installing D:\Perl\site\lib\auto\DBD\mysql\mysql.bs
Installing D:\Perl\site\lib\auto\DBD\mysql\mysql.dll
......
Installing D:\Perl\html\site\lib\Mysql.html
Installing D:\Perl\html\site\lib\DBD\mysql.html
......
Installing D:\Perl\site\lib\Mysql.pm
Installing D:\Perl\site\lib\Mysql\Statement.pm
......
Writing D:\Perl\site\lib\auto\DBD\mysql\.packlist

But if you want to download the DBI module and install it yourself, you can go to http://www.mysql.com and download DBD-mysql-2.9003.tar.gz.

HelloMySQL.pl - My First Perl Program with MySQL

Now we are ready to write a Perl program using the DBI module to access MySQL server. Here my first example, HelloMySQL.pl:

# HelloMySQL.pl
 
   use DBI;
   $dbh = DBI->connect("DBI:mysql:test");
   $dbh->do("create table hello (message varchar(80))");
   $dbh->do("insert into hello (message) values ('Hello world!')");
   $sth = $dbh->prepare("select * from hello");
   $sth->execute();
   while ((@row) = $sth->fetchrow_array()) {
      print "$row[0]\n";
   }
   $sth->finish();
   $dbh->do("drop table hello");
   $dbh->disconnect();
   exit;

Let's start MySQL server, and run HelloMySQL.pl:

\perl\bin\perl HelloMySQL.pl
Hello world!

Congratulations, all required software and additional modules are working.

[dubaimobiles]

Superb post.
Good work.

[Mike Tyson]

MySQL is a relational database management system (RDBMS).
My Sql is an Popular Open Source Database and MySQL is an open source database management system. MySQL is runs on both Linux and Windows servers and is commonly used in conjunction with PHP.

[fanky]

Thanks for the info i have never used it and switching toward mysql reavals me to new complexity is thier any online help or ebook for helping me through this.

[cygnet]

Great Post! Thanks for giving me such a detailed description of Mysql.