MYSQL

2006-03-20T22:03:00.000-08:00

Optimizing Performance
Designing a well structured, normalized database schema is just half of the optimization task. The other half is building and fine tuning a server to run MySQL. MySQL provides a wealth of information regarding the tuning of server parameters. This information which was traditionally accessible in a configuration file called my.cnf, but can now be accessed and tuned visually using the MySQL Administrator graphical interface.
MySQL default parameters are very small so it can run on almost any box as an additional service without slowing things down. MySQL provides my-small/medium/huge.cnf files that come with the standard binary distribution as general parameter guidelines for small, medium, and huge servers.
Many of the optimal parameter values will depend a lot on the load you put on the server, as well as the storage engines(s) you use. But, here are some generic guidelines to optimize performance regardless of which storage engine you are using. We will take a look at storage engine specific tuning options in a future article so please, continue to watch this space.
Set max_connections to the number of concurrent connections you need. The default value is only 100 connections, which is very small.
Note: connections take memory and your OS might not be able to handle a lot of connections. MySQL binaries for Linux/x86 allow you to have up to 4096 concurrent connections, but self compiled binaries often have less of a limit.
Set table_cache to match the number of your open tables and concurrent connections. Watch the open_tables value and if it is growing quickly you will need to increase its size.
Note: The 2 previous parameters may require a lot of open files. 20+max_connections+table_cache*2 is a good estimate for what you need. MySQL on Linux has an open_file_limit option to set this limit.
If you have complex queries sort_buffer_size and tmp_table_size are likely to be very important. Values will depend on the query complexity and available resources, but 4Mb and 32Mb, respectively are recommended starting points.
Note: These are "per connection" values, among read_buffer_size, read_rnd_buffer_size and some others, meaning that this value might be needed for each connection. So, consider your load and available resource when setting these parameters. For example sort_buffer_size is allocated only if MySQL nees to do a sort. Note: be careful not to run out of memory.
If you have many connects established (i.e. a web site without persistent connections) you might improve performance by setting thread_cache_size to a non-zero value. 16 is good value to start with. Increase the value until your threads_created do not grow very quickly.

2006-03-20T22:02:00.000-08:00

Database administration serves a number of important functions from improving the performance of your database to avoiding a disaster that would result in lost data or costly system downtime. This article will describe some of the fundamental tools and practices programmers, as well as DBAs and SysAdmins, can use with the powerful new MySQL Administrator tool to ensure a healthy database environment.
The role of the administrative function will largely depend on the size of the organization and size of the IT department. In larger organizations there are DBA (Database Administrators), Data Analysts, and SysAdmins that would work alongside programmers to create a well designed database systems and then ultimately be responsible for managing the health and availability of the system on a daily basis. However, in smaller organization, or where IT departments are more resource limited, the duty of administering MySQL falls into the hands of the database programmer.
For programmers less familiar with the administration functions, their responsibility must also cover the following areas:
Optimizing the server for performance which includes, space management, monitoring memory usage, and diagnosing problems.
Administering the server on a daily basis to ensure server availability and the ongoing health of the system.
Disaster prevention and recovery including creating consistent backups and restoring databases in case of network, application, or hardware failure.
Until now, a powerful set of command line tools have been used to administer MySQL servers. However, they have been cumbersome and time-consuming to use for individuals not familiar or well-versed in the utilities. The result, in many cases, has been a general neglect of the administration function leaving the database system at risk to security breaches, data loss, and downtime.
To make administration easier and faster, MySQL AB recently announced MySQL Administrator, an integrated visual environment for managing MySQL servers. It is an ideal tool that not only greatly simplifies the life of DBAs but also empowers developers to effectively manage a networked environment of MySQL servers.
Below are 5 critical functions of database administration that must be addressed to ensure the level of availability your users will demand of your database system.

2006-03-20T22:01:00.000-08:00

SQL Injection
SQL injection is yet another common vulnerability that is the result of lax input validation. Unlike cross-site scripting vulnerabilities that are ultimately directed at your site’s visitors, SQL injection is an attack on the site itself—in particular its database.
The goal of SQL injection is to insert arbitrary data, most often a database query, into a string that’s eventually executed by the database. The insidious query may attempt any number of actions, from retrieving alternate data, to modifying or removing information from the database.

2006-03-20T21:58:00.000-08:00

Definition and an Example

A stored procedure is a procedure (like a subprogram in a regular computing language) that is stored (in the database). Correctly speaking, MySQL supports "routines" and there are two kinds of routines: stored procedures which you call, or functions whose return values you use in other SQL statements the same way that you use pre-installed MySQL functions like pi(). I'll use the word "stored procedures" more frequently than "routines" because it's what we've used in the past, and what people expect us to use.

A stored procedure has a name, a parameter list, and an SQL statement, which can contain many more SQL statements. There is new syntax for local variables, error handling, loop control, and IF conditions. Here is an example of a statement that creates a stored procedure.

CREATE PROCEDURE procedure1                /* name */
(IN parameter1 INTEGER)                    /* parameters */
BEGIN                                      /* start of block */
  DECLARE variable1 CHAR(10);                /* variables */
  IF parameter1 = 17 THEN                    /* start of IF */
    SET variable1 = 'birds';                   /* assignment */
  ELSE
    SET variable1 = 'beasts';                  /* assignment */
  END IF;                                   /* end of IF */
  INSERT INTO table1 VALUES (variable1);    /* statement */
END                                       /* end of block */

2006-03-20T21:56:00.000-08:00

Introduction
This 67 page guide is for long-time MySQL users who want to know "what's new" in version 5. The short answer is "stored procedures, triggers, views, information_schema". The long answer is the MySQL 5.0 New Features series, and this book is the first in the series.
What I'm hoping to do is make this look like a hands-on session where you, as if you're working it out yourself on your keyboard, can walk through sample problems.
To do this, I'll go through each little item, building up slowly. By the end, I'll be showing larger routines that do something useful, something that you might have thought was tough.

2006-03-19T15:33:00.000-08:00

mysql adalah database yang pasting bagus