Installing MySQL on windows environment.

MySQL Installaion on windows. 

This tutorial will show you how to install MySQL on Windows XP. So, go to the MySQL website and download the MySQL Community Server.

File to download: Windows (x86, 32-bit), MSI Installer Essentials - Recommended if your operating system is a windows XP 64bit version use the Windows (x86, 64-bit), MSI Installer

On the downloaded MySQL double click and this will start the installation of MySQL and follow the instructions.

Select "I accept terms in the licence agreement" and click next: -

Now you have to select installation type

Typical : it installs server as well as common client programs also.

Custom: you can choose the components to be install and the path where to install.

Complete: it installs server as well as all client programs.(Prefered if you are new to MySQL)

now we are going to see custom installaiton.

select the "custom" icon to change the path of the installation.

Now browse or navigate to the folder C:\server\mysql we want to install the files in this folder.

After the change press ok.

Press install.

It will list the configuration types

1.Detailed : While installing the mysql server, the server itself changes the configurations changes based on the available resources ( like memory,no of connections) based on the given inputs.

2.Standard : This will install the mysql server with default configuration options.

Note : standard is the best option for personal or development purpose, Detailed is prefarable only for the mysql servers.(what ever it may be the installation type, you can chage the configuration file later also).

Now we are going to install wth a standard option.

Select a standerd configuration press next.

To install the mysql service, press next.

We need a password for MySQL database please enter a password, please remember the login name in most cases is root and the password your choosing, in my case I choose myrootpassword.

Click on execute to install the server components.

Click on finish once everything is done.

Now MySql database is successfully installed and it is ready to use.






MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers 

Installing MySQL From A Tar File

Installing MySQL from a tarball

Installing MySQL from a tarball should be straightforward but there are several steps, and therefore several opportunities for things to get funky. Nonetheless, doing it this way you will learn more about several aspects of your system, and what's really going on with the installation, and you'll see all the work that you'd save by using rpm.
Installing things from tarballs is a common practice on UNIX/Linux systems. You'll be doing it later when you install DBI as well. By then you'll be a pro. It usually involves just a few simple steps. Tarballs usually come as compressed, tar'd files. tar stands for Tape Archive, and refers to a format for storing and archiving files and directory structures. When you unpack a tar ball it recreates the original directory structure and files.
The binary source tarball comes with everything you need to run MySQL, as well as all the client programs for interacting with the database, and the development libraries for writing or using your own programs with the database. To install the software you should be able to log into your system as root. By default, mysql would run as a root process, and some of the files would be owned by root. Because this is something of a security risk, many people choose to create a regular user for running the mysql deamon. This way there are less root owned files on your system, and fewer opportunities for exploiting a root owned process. Once you have the software, and created a user, you can install the database and start it up. The last steps are simply to set an administration password, and then modify your system so that it starts the mysql deamon everytime the system is started.

Installation Overview

Basically you'll be performing the following steps:

1. Get the tarball
2. decompress and unpack the tarball
3. create a symbolic link to the mysql directory
4. create a user whose ID is used for running the database
5. change ownership of the directory from root to user mysql
6. edit the init startup file
7. su to user mysql
8. initialize the database and create the tables with the included install script
9. start the database
10. change the root password
11. change back to user root
12. place startup file in proper startup location
    

Get the software and unpack it

The tarball is more formally called a binary source distribution. You can get the source distribution at the same place you get the rpm files: www.mysql.com. It will have some filename like: mysql-3.22.32-pc-linux-gnu-i686.tar.gz
A common place to install software on linux systems is in the directory:
/usr/local/
Once you have the software, log in as root and move the tarball to that directory. The filename of the software will have two extenions on the end: .tar.gz The gz indicates that the file is compressed. To uncompress the file issue the command:
gunzip filename.tar.gz
this will decompress the file and remove the .gz extension. Now you have a true tar file. Unpack it using the tar command with the appropriate arguments:
tar -xvf filename.tar
the x means extract, the v means verbose (show me everything that's happening), and the f indicates that the next argument is the filename to extract. You should see the names of files and directories wizzing by your screen. If you list the contents of the current directory you should see that a new directory containing all the mysql files has been created.
Now that you have the distribution unpacked, it's a good idea to create the user that will own the files and run the database. You can do this with the adduser command.
Since it has a long clunky name, you should create a symbolic link to it (the same thing as an alias or shortcut):

Create a user for the mysql program

From a security point of view you'd like to run the server as some user other than root. It's also a good idea to not have any more root-owned files lying around your system than are necessary. To accomplish this you can simply create a user and group for owning and running the server. I create a user called mysql, with no home directory. We do this with the adduser and groupadd commands.

/usr/sbin/groupadd mysql
#create the user mysql with no home directory and group mysql
/usr/sbin/adduser mysql -M -g mysql

Now su to user mysql and initialize your database tables. It's important to do this as the user mysql, because otherwise the tables and directories will be created as owned by root

[root@zenith mysql]#su mysql [mysql@zenith mysql]# [mysql@zenith mysql]# scripts/mysql_install_db Creating db table Creating host table Creating user table Creating func table Creating tables_priv table Creating columns_priv table To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! This is done with: ./bin/mysqladmin -u root password 'new-password' See the manual for more instructions. Please report any problems with the ./bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at http://www.tcx.se/license.htmy. [mysql@zenith mysql-3.22.32-pc-linux-gnu-i686]#

Now before you can set the root password, you must start the mysqld daemon. This is done with a little script in the scripts directory called safe_mysqld.

[root@zenith mysql-3.22.32-pc-linux-gnu-i686]# bin/safe_mysqld &
[1] 1541

# Starting mysqld daemon with databases from /usr/local/mysql-3.22.32-pc-linux-gnu-i686/data

 [root@zenith mysql-3.22.32-pc-linux-gnu-i686]

You can see if the daemon is running by looking at all the system process:
ps aux | more
Now try setting the root password:

[root@zenith mysql-3.22.32-pc-linux-gnu-i686]# ./bin/mysqladmin -u root password 'friday'

You can also try connecting to the server and issuing a command:

[root@zenith mysql-3.22.32-pc-linux-gnu-i686]# bin/mysql -pfriday mysql> show databases; +----------+ | Database | +----------+ | mysql | | test | +----------+ 2 rows in set (0.00 sec) mysql>

Set the mysql daemon to start at system start up

Next we have to make it so that the mysql server will start up everytime the computer is started. This is accomplished by a script that holds information about how the server should be started. We need to edit that script and place it in the proper location for system start up. There's a place onthe system for start up files edit support-files/mysql.server and change user to mysql

[root@zenith support-files]# cp mysql.server mysql.server-dist
[root@zenith support-files]# emacs -nw mysql.server
[root@zenith support-files]# cp mysql.server /etc/rc.d/init.d/
[root@zenith support-files]# cd /etc/rc.d/init.d/
[root@zenith init.d]# chmod a+x mysql.server 
[root@zenith init.d]# ls -al mysql.server
-rwxrwxr-x   1 root     root         2815 Oct  9  2000 mysql.server

When your system starts up there is a series of things in rc.d directory that occur. To add mysql as one of the things that gets started use the chkconfig command. Type: man chkconfig for more information on this. Or continuing from above, issue the following comands:

[root@zenith init.d]# /sbin/chkconfig --add mysql.server
[root@zenith init.d]# /sbin/chkconfig --list mysql.server
mysql.server 0:off 1:off 2:on 3:on 4:on 5:on 6:off
[root@zenith init.d]#

The first command "chkconfig --add" adds the proper links in the right places for your system to execute the mysql.server script upon system startup. The second command "chkconfig --list" reports back to you the run levels in which the script is executed.
It's also a good idea to add the path to the mysql bin directory to the default path of users.



MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers 

What is MySQL? Features and Limitations.

What is MySQL?

MySQL is a open source Relational Database Management System. MySQL is very fast reliable and flexible Database Management System. It provides a very high performance and it is multi threaded and multi user Relational Database management system.

MySQL is one of the most popular relational database Management System on the web. The MySQL Database has become the world's most popular open source Database, because it is free and available on almost all the platforms. The MySQL can run on Unix , window, and Mac OS.

MySQL is used for the internet applications as it provides good speed and is very secure. MySQL was developed to manage large volumes of data at very high speed to overcome the problems of existing solutions. MySQL can be used for verity of applications but it is mostly used for the web applications on the internet.

Pronounced "my ess cue el" (each letter separately) and not "my SEE kwill." MySQL is an open source RDBMS that relies on SQL for processing the data in the database. MySQL provides APIs for the languages C, C++, Eiffel, Java, Perl, PHP and Python. In addition, OLE DB and ODBC providers exist for MySQL data connection in the Microsoft environment. A MySQL .NET Native Provider is also available, which allows native MySQL to .NET access without the need for OLE DB.

MySQL is developed, supported and marketed by MySQL AB. The database is available for free under the terms of the GNU General Public License (GPL) or for a fee to those who do not wish to be bound by the terms of the GPL.

Features of MySQL
The following list shows the most important properties of MySQL. This section is directed to the reader who already has some knowledge of relational databases. We will use some terminology from the relational database world without defining our terms exactly. On the other hand, the explanations should make it possible for database novices to understand to some extent what we are talking about.

Relational Database System: Like almost all other database systems on the market, MySQL is a relational database system.

Client/Server Architecture: MySQL is a client/server system. There is a database server (MySQL) and arbitrarily many clients (application programs), which communicate with the server; that is, they query data, save changes, etc. The clients can run on the same computer as the server or on another computer (communication via a local network or the Internet).

Almost all of the familiar large database systems (Oracle, Microsoft SQL Server, etc.) are client/server systems. These are in contrast to the file-server systems, which include Microsoft Access, dBase and FoxPro. The decisive drawback to file-server systems is that when run over a network, they become extremely inefficient as the number of users grows.

SQL compatibility: MySQL supports as its database language -- as its name suggests – SQL (Structured Query Language). SQL is a standardized language for querying and updating data and for the administration of a database. There are several SQL dialects (about as many as there are database systems). MySQL adheres to the current SQL standard (at the moment SQL:2003), although with significant restrictions and a large number of extensions.
Through the configuration setting sql-mode you can make the MySQL server behave for the most part compatibly with various database systems. Among these are IBM DB/2 and Oracle.

SubSELECTs: Since version 4.1, MySQL is capable of processing a query in the form SELECT * FROM table1 WHERE x IN (SELECT y FROM table2) (There are also numerous syntax variants for subSELECTs.)

Views: Put simply, views relate to an SQL query that is viewed as a distinct database object and makes possible a particular view of the database. MySQL has supported views since version 5.0.

Stored procedures: Here we are dealing with SQL code that is stored in the database system. Stored procedures (SPs for short) are generally used to simplify certain steps, such as inserting or deleting a data record. For client programmers this has the advantage that they do not have to process the tables directly, but can rely on SPs. Like views, SPs help in the administration of large database projects. SPs can also increase efficiency. MySQL has supported SPs since version 5.0.

Triggers: Triggers are SQL commands that are automatically executed by the server in certain database operations (INSERT, UPDATE, and DELETE). MySQL has supported triggers in a limited form from version 5.0, and additional functionality is promised for version 5.1.

Unicode: MySQL has supported all conceivable character sets since version 4.1, including Latin-1, Latin-2, and Unicode (either in the variant UTF8 or UCS2).

User interface: There are a number of convenient user interfaces for administering a MySQL server.

Full-text search: Full-text search simplifies and accelerates the search for words that are located within a text field. If you employ MySQL for storing text (such as in an Internet discussion group), you can use full-text search to implement simply an efficient search function.

Replication: Replication allows the contents of a database to be copied (replicated) onto a number of computers. In practice, this is done for two reasons: to increase protection against system failure (so that if one computer goes down, another can be put into service) and to improve the speed of database queries.

Transactions: In the context of a database system, a transaction means the execution of several database operations as a block. The database system ensures that either all of the operations are correctly executed or none of them. This holds even if in the middle of a transaction there is a power failure, the computer crashes, or some other disaster occurs. Thus, for example, it cannot occur that a sum of money is withdrawn from account A but fails to be deposited in account B due to some type of system error.

Transactions also give programmers the possibility of interrupting a series of already executed commands (a sort of revocation). In many situations this leads to a considerable simplification of the programming process. In spite of popular opinion, MySQL has supported transactions for a long time. One should note here that MySQL can store tables in a variety of formats. The default table format is called MyISAM, and this format does not support transactions. But there are a number of additional formats that do support transactions. The most popular of these is InnoDB, which will be described extensively in this book.

Foreign key constraints: These are rules that ensure that there are no cross references in linked tables that lead to nowhere. MySQL supports foreign key constraints for InnoDB tables.

GIS functions: Since version 4.1, MySQL has supported the storing and processing of two-dimensional geographical data. Thus MySQL is well suited for GIS (geographic information systems) applications.

Programming languages: There are quite a number of APIs (application programming interfaces) and libraries for the development of MySQL applications. For client programming you can use, among others, the languages C, C++, Java, Perl, PHP, Python, and Tcl.

ODBC: MySQL supports the ODBC interface Connector/ODBC. This allows MySQL to be addressed by all the usual programming languages that run under Microsoft Windows (Delphi, Visual Basic, etc.). The ODBC interface can also be implemented under Unix, though that is seldom necessary.
Windows programmers who have migrated to Microsoft's new .NET platform can, if they wish, use the ODBC provider or the .NET interface Connector/NET.

Platform independence: It is not only client applications that run under a variety of operating systems; MySQL itself (that is, the server) can be executed under a number of operating systems. The most important are Apple Macintosh OS X, Linux, Microsoft Windows, and the countless Unix variants, such as AIX, BSDI, FreeBSD, HP-UX, OpenBSD, Net BSD, SGI Iris, and Sun Solaris.

Speed: MySQL is considered a very fast database program. This speed has been backed up by a large number of benchmark tests (though such tests -- regardless of the source -- should be considered with a good dose of skepticism).

Limitations

• When MySQL is used with standard tables (table type MyISAM), then locking, that is, the temporary blocking of access to or alteration of database information, is in operation only for entire tables (table locking). You can circumvent the table-locking problem by implementing transaction-capable table formats, such as InnoDB, that support row locking.
• In using MyISAM tables, MySQL is not able to execute hot backups, which are backups during operation without blocking the tables with locks. Here again, the solution is InnoDB, though here the hot backup function is available only in the form of a commercial supplement.
  
• Many database systems offer the possibility of defining custom data types. MySQL does not support such functionality, nor is any currently planned.
  

• MySQL has up to now ignored the general XML trend. It is not clear when MySQL will support direct processing of XML data. Numerous commercial database systems offer considerably more functionality in this area, and even the SQL:2003 standard provides for a host of XML functions.

• MySQL is in fact a very fast database system, but it is very limited in its usability for real-time applications, and it offers no OLAP functions. OLAP stands for online analytical processing, and refers to special methods for the management and analysis of multidimensional data. OLAP-capable database systems are often called data warehouses.

• MySQL supports, since version 5.0, stored procedures and triggers, but these functions have not yet fully matured (this applies especially to triggers) and do not yet have the same stability and plenitude of functions offered by commercial database systems.

• Similar restrictions hold as well for the GIS functions introduced in version 4.1. Commercial database systems offer in some cases considerably greater functionality.

MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers 

What is a Database & Database system concepts.

What Is a Database?
Before we can answer the central question of this chapter, namely, What is MySQL? you and I must find a common language. Therefore, this section presents a rudimentary database glossary, without going into great detail. (If you have already had significant dealings with relational databases, you can skip the next couple of pages in good conscience.)

There is scarcely to be found a term that is less precise than database. A database can be a list of addresses residing in a spreadsheet program (such as Excel), or it can be the administration files of a telecommunications firm in which several million calls are registered daily, their charges accurately calculated, monthly bills computed, and warning letters sent to those who are in arrears.

A simple database can be a stand-alone operation (residing locally on a computer for a single user), while others may be used simultaneously by thousands of users, with the data parceled out among several computers and dozens of hard drives. The size of a database can range from a few kilobytes into the terabytes.

In ordinary usage, the word "database" is used to refer to the actual data, the resulting database files, the database system (such as MySQL or Oracle), or a database client (such as a PHP script or a program written in C++). Thus there arises a great potential for confusion as soon as two people begin to converse on the subject of databases.

Relations, Database Systems, Servers and Clients
A database is an ordered collection of data, which is normally stored in one or more associated files. The data are structured as tables, where cross references among tables are possible. The existence of such relations among the tables leads to the database being called a relational database.
Let us clarify matters with an example. A database might consist of a table with data on a firm's customers (name, address, etc.), a table with data on the products the firm offers, and finally, a table containing the firm's orders. Through the table of orders it is possible to access the data in the other two tables (for example, via customer and product numbers).
MySQL, Oracle, Microsoft SQL Server, and IBM DB2 are examples of relational database systems. Such a system includes the programs for managing relational databases. Among the tasks of a relational database system are not only the secure storage of data, but also such jobs as the processing of commands for querying, analyzing, and sorting existing data and for storing new data. All of this should be able to take place not only on a single computer, but over a network as well. Instead of a database system we shall often speak of a database server.
Where there are servers, there are clients. Every program that is connected to the database system is called a database client. Database clients have the job of simplifying the use of the database for the end user. No user of a database system in his or her right mind would wish to communicate directly with the database server. That is much too abstract and inconvenient. (Let programmers worry about such direct communication!) Instead, the user has a right to expect convenient tables, list boxes, and so on to enable the location of data or to input new data.
Database clients can assume a variety of forms, and indeed, they are often not recognized by the user as database programs at all. Some examples of this type of client are HTML pages for the display and input of messages in an on-line discussion group, a traditional program with several windows for managing addresses and appointments, and a Perl script for executing administrative tasks. There is thus wide scope for database programming.

Relational Versus Object-Oriented Database Systems
Relational databases have dominated the database world for decades, and they are particularly well suited for business data, which usually lend themselves to structuring in the form of tables. Except for the following two paragraphs, this entire book discusses only relational databases (though we shall not always stress this point).
Another kind of database is the object-oriented database. Such databases can store free-standing objects (without having to arrange them in tables). Although in recent years there has been a trend in the direction of object-oriented programming languages (such as Object-Store, O2, Caché), object-oriented databases have found only a small market niche.
Note that relational databases can be accessed by means of object-oriented programming languages. However, that does not turn a relational database into an object-oriented one. Object-oriented database systems enable direct access to objects defined in the programming language in question and the storage of such objects in the database without conversion (persistency). It is precisely this that is not possible with relational database systems, in which everything must be structured in tables.

Tables, Records, Fields, Queries, SQL, Index, Keys
We have already mentioned tables, which are the structures in which the actual data are located. Every line in such a table is called a data record, or simply record, where the structure of each record is determined by the definition of the table. For example, in a table of addresses every record might contain fields for family name, given name, street, and so on. For every field there are precise conditions on the type of information that can be stored (such as a number in a particular format, or a character string with a predetermined maximum number of characters).
The description of a database consisting of several tables with all of its fields, relations, and indexes (see below) is called a database model. This model defines the construction of the data structures and at the same time provides the format in which the actual data are to be stored.
Tables usually contain their data in no particular order (more precisely, the order is usually that in which the data have been entered or modified). However, for efficient use of the data it is necessary that from these unordered data a list can be created that is ordered according to one or more criteria. It is frequently useful for such a list to contain only a selection of the data in the table. For example, one could obtain a list of all of one's customers, ordered by ZIP code, who have ordered a rubber ducky within the past twelve months.
To create such a list, one formulates queries. The result of the query is again a table; however, it is one that exists in active memory (RAM) and not on the hard drive.
To formulate a query one uses SQL instructions, which are commands for selecting and extracting data. The abbreviation SQL stands for Structured Query Language, which has become a standard in the formulation of database queries. Needless to say, every producer of a database system offers certain extensions to this standard, which dilutes the goal of compatibility among various database systems.
When tables get large, the speed at which a query can be answered depends significantly on whether there is a suitable index giving the order of the data fields. An index is an auxiliary table that contains only information about the order of the records. An index is also called a key.
An index speeds up access to data, but it has disadvantages as well. First, every index increases the amount of storage on the hard drive necessary for the database file, and second, the index must be updated each time the data are altered, and this costs time. (Thus an index saves time in the reading of data, but it costs time in entering and altering data. It thus depends on the use to which the data are to be put whether an index is on the whole a net plus or minus in the quest for efficiency.)
A special case of an index is a primary index, or primary key, which is distinguished in that the primary index must ensure a unique reference to a record. Often, for this purpose one simply uses a running index number (ID number). Primary indexes play a significant role in relational databases, and they can speed up access to data considerably.



MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers