ERROR 1037 (HY001): Out of memory; restart server and try again (needed 123456789 bytes).

Today morning when I was trying to execute a peice of SQL code and all the time I was welcomed by the below error.

ERROR 1037 (HY001): Out of memory; restart server and try again (needed 123456789 bytes).

If we get this kind of error message then the first thing we need to check is the available memory on the server and have to ensure that the server has not run out of disk space for the swap file and also need confirm that there is enough free memory for MySQL to grow.

 This indicate that we need to increase or decrease the values of some buffers to manage memory usage more efficiently otherwise MySQL might complain about the same thing by means of above error message.

MySQL - Extract numbers out of a string

Yesterday on EE I saw a very interesting request from a user for "Extracting numbers out of a string".
This could be done in other languages with just 1 liner code but he needed it inside a SELECT query.
 http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_24855357.html

So I came with a very small MySQL function which was doing as needed by the user. I'm not sure whther this is the best way to do this but "There is always room for improvement."
 
 
DELIMITER $$



DROP FUNCTION IF EXISTS `uExtractNumberFromString`$$
CREATE FUNCTION `uExtractNumberFromString`(in_string varchar(50)) RETURNS INT
NO SQL


BEGIN


DECLARE ctrNumber varchar(50);
DECLARE finNumber varchar(50) default ' ';
DECLARE sChar varchar(2);
DECLARE inti INTEGER default 1;


IF length(in_string) > 0 THEN


WHILE(inti <= length(in_string)) DO
    SET sChar= SUBSTRING(in_string,inti,1);
    SET ctrNumber= FIND_IN_SET(sChar,'0,1,2,3,4,5,6,7,8,9');


    IF ctrNumber > 0 THEN


       SET finNumber=CONCAT(finNumber,sChar);
    ELSE


       SET finNumber=CONCAT(finNumber,'');
    END IF;


    SET inti=inti+1;
END WHILE;
RETURN CAST(finNumber AS SIGNED INTEGER) ;
ELSE
  RETURN 0;
END IF;


END$$



select uExtractNumberFromString('12;e1hhsak123s12');
12112312

Client does not support authentication protocol requested by server; consider upgrading MySQL client

Reference: MySQL Manual

If you are trying to evaluate some free open source application available on various websites(which are especially developed using PHP) and when the application try to access newer version of MySQL server you may end up seeing a error message like below


Client does not support authentication protocol requested by server; consider upgrading MySQL client


 Reason for this unwanted welcome message is that In older versions of PHP, the mysql extension does not support the authentication protocol used in MySQL 4.1.1 and higher. This is true regardless of the PHP version being used. If you wish to use the mysql extension with MySQL 4.1 or newer, you may need to follow one of the options discussed above for configuring MySQL to work with old clients. The mysqli extension (stands for "MySQL, Improved"; added in PHP 5) is compatible with the improved password hashing employed in MySQL 4.1 and higher, and no special configuration of MySQL need be done to use this MySQL client library.
 MySQL >=4.1 uses an authentication protocol based on a password hashing algorithm that is not compatible with that used by older (< 4.1) clients.


There are several ways through which you can resolve this type of issues


- Tell the server to use the older password hashing algorithm
   (i) Start mysqld with the --old-passwords option(also, you can place old-passwords in my.cnf/my.ini under mysqld group).
   (ii) Assign an old-format password to each account that has had its password updated to the longer 4.1 format. You can identify these accounts with the following query: 
 mysql> SELECT Host, User, Password FROM mysql.user
                            -> WHERE LENGTH(Password) > 16;
For each account record displayed by the query, use the Host and User values and assign a password using the OLD_PASSWORD() function and either SET PASSWORD or UPDATE



For further options please take a look at the manual page
http://dev.mysql.com/doc/refman/5.1/en/old-client.html

Scheduled tasks in MySQL

Couple of days ago, during the training session one of the trainee(experienced in other DBMS) was really curious to know on how to setup & use Scheduled tasks in MySQL. I was also equally interested to spend some time on "Event Scheduler" as I was also getting a chance to show that how MySQL is equipped with the rich features but unfortunately the discussion ended before taking off. Most of the ppl felt its just a matter of one line adding to the crontab  & job is done. So most of users were not aware of the fact that MySQL has added a feature which could do what the Unix crontab  (also known as a “cron job”) or the Windows Task Scheduler can.

MySQL Events are tasks that run according to a schedule due to this can also be referred as scheduled events. When you create an event, you are creating a named database object containing one or more SQL statements to be executed at one or more regular intervals, beginning and ending at a specific date and time. Conceptually, this is similar to the idea of the Unix crontab  (also known as a “cron job”) or the Windows Task Scheduler.

(From Manual)

The global event_scheduler system variable determines whether the Event Scheduler is enabled and running on the server.

Version Introduced           5.1.6
Command Line Format     --event-scheduler[=value]
Config File Format           event-scheduler
Option Sets Variable        Yes, event_scheduler
Variable Name                 event_scheduler
Variable Scope                 Global
Dynamic Variable             Yes
Permitted Values   
            Type                    enumeration
            Default                OFF
            Valid Values         ON, OFF, DISABLED

From the mysql prompt you can enable/disable  using any one of the following statements

mysql>SET GLOBAL event_scheduler = ON;
mysql>SET @@global.event_scheduler = ON;
mysql>SET GLOBAL event_scheduler = 1;
mysql>SET @@global.event_scheduler = 1;

Also, In the server configuration file (my.cnf, or my.ini on Windows systems), include the line where it will be read by the server (for example, in a [mysqld] section): 

event_scheduler=ON|OFF|DISABLED


So now we know that what needs to be done at the server level in order to use the feature. Okay the platform is ready for event_scheduler and can demonstrate you with a tiny example.
 
 Just ensuring that event_scheduler is enabled or not

show global variables like 'event_scheduler';
event_scheduler    ON

 The result of the SQL command ensures me that "event_scheduler" is enabled now and I can use the feature(but this settings should be made permanent by adding it to config file ).

Syntax for CREATE EVENT

CREATE EVENT [ IF NOT EXISTS ] event_name
ON SCHEDULE schedule
[ ON COMPLETION [ NOT ] PRESERVE ]
[ ENABLED | DISABLED ]
[ COMMENT 'comment' ]
DO sql_statement;

In which - the "event_name" must be a valid identifier of up to 64 characters (e.g me_ushastry, me_trainer etc ushastry1978 etc), with delimiters allowed (`Something To Do`), possibly qualified (database1.event1). Events are database objects, so they are stored within a database and event names must be unique within that database. When checking for uniqueness, MySQL uses case-insensitive comparisons.

The "schedule" can be a timestamp in the future, a recurring interval, or a combination of recurring intervals and timestamps.

The possibilities are:

• AT timestamp [+ interval integer_value time_keyword ]
• EVERY interval
• EVERY interval STARTS timestamp
• EVERY interval ENDS timestamp
• EVERY interval STARTS timestamp ENDS timestamp

"AT timestamp" means "Do this once at the specified time". The timestamp must contain both date and time (that is, it must be a DATETIME or TIMESTAMP value) and must be in the future -- you cannot make an event which is supposed to have already occurred. To specify an exact time, you can also add an interval to the timestamp (using + INTERVAL, a positive integer and one of YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, or SECOND); this makes sense only when you're using the CURRENT_TIMESTAMP function. Here are two examples:

Scenario is like we have a table named `tbl_users` and we have to delete the records from the table every  5 minutes and we want to automate this process so that no one has to sit and run the piece of SQL code manually.



 # Checking "event_scheduler"

create database mysqlscheduler;
use mysqlscheduler;

CREATE TABLE IF NOT EXISTS `tbl_users`
(                     
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
    `name` varchar(100) DEFAULT NULL,              
    `email` varchar(100) DEFAULT NULL,              
    `promo_emails` enum('YES','NO') DEFAULT 'NO',
    PRIMARY KEY (`id`)                             
) ENGINE=MyISAM;




# Add couple of records for testing


INSERT INTO mysqlscheduler.tbl_users VALUES('','Umesh','ushastry@gmail.com','NO'),('','Umesh','ushastry_duplicate@gmail.com','YES'),('','Umesh','ushastry_dummy@gmail.com','NO');

SELECT * FROM mysqlscheduler.tbl_users;

1    Umesh    ushastry@gmail.com    NO
2    Umesh    ushastry_duplicate@gmail.com    YES
3    Umesh    ushastry_dummy@gmail.com    NO
\N    \N    \N    NO



 Now time to create EVENT which would take care of deleting the records


CREATE EVENT drop_dummy_user
ON SCHEDULE EVERY 15 MINUTE
STARTS TIMESTAMP '2009-01-01 00:00:00'
ENDS TIMESTAMP '2009-12-31 23:59:00'
DO DELETE FROM mysqlscheduler.tbl_users WHERE promo_emails='YES';

C:\ushastry\MySQL_Com\mysql-5.1.34-win32\bin>mysql -uroot -p

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.1.34-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT * FROM mysql.event\G
*************************** 1. row ***************************
                  db: mysqlscheduler
                name: drop_dummy_user
                body: DELETE FROM mysqlscheduler.tbl_users WHERE promo_emails='YES'
             definer: root@localhost
          execute_at: NULL
      interval_value: 15
      interval_field: MINUTE
             created: 2009-09-02 14:51:45
            modified: 2009-09-02 14:51:45
       last_executed: NULL
              starts: 2009-01-01 00:00:00
                ends: 2009-12-31 23:59:00
              status: ENABLED
       on_completion: DROP
            sql_mode:
             comment:
          originator: 1
           time_zone: SYSTEM
character_set_client: utf8
collation_connection: utf8_general_ci
        db_collation: latin1_swedish_ci
           body_utf8: DELETE FROM mysqlscheduler.tbl_users WHERE promo_emails='YES'
1 row in set (0.00 sec)

mysql>

Above command output can be read as

• the db column shows the name of the database that contains the event
• the name column holds the name of the event; recall that it must be unique within the database
• the definer column shows whose privileges will be checked when the event is executed; if the user shown no longer has the proper privileges to execute the event's SQL statement, an error will be returned
• the execute_at column shows when the event should be executed; this is always NULL unless the ON SCHEDULE clause uses the AT timestamp option
• the interval_value column shows the numeric value of how often the event will be executed; this value will be NULL if the ON SCHEDULE clause uses the AT timestamp option
• the interval_field columns shows the datetime value of how often the event will be executed; this value will be NULL if the ON SCHEDULE clause uses the AT timestamp option
• the created column shows the timestamp when the event was created
• the modified column shows the timestamp when the event was last modified; this will be the same as the created column if ALTER EVENT has never changed the event
• the last_executed column shows the timestamp of the last time the event was executed; in this example, it is NULL because the newly-created event hasn't been executed yet
• the starts column shows the timestamp when the event will start executing; this value will be NULL if the ON SCHEDULE clause uses the AT timestamp option
• the ends column shows the timestamp when the event will stop executing; this value will be NULL if the ON SCHEDULE clause uses the AT timestamp option
• the status column shows whether the event is currently enabled or disabled; in this example, it shows ENABLED, the default value
• the on_completion column shows whether the event will be preserved or dropped when it is finished; in this example, it shows that the event will be preserved, as specified in the CREATE EVENT statement
• the comment column shows the comment included in the CREATE EVENT statement

After 15 min or so you can check and confirm that the records has been deleted 


SELECT * FROM mysqlscheduler.tbl_users;

1    Umesh    ushastry@gmail.com    NO
3    Umesh    ushastry_dummy@gmail.com    NO
\N    \N    \N    NO


Also, can be verified from the event meta data that when it was executed last time



mysql> SELECT * FROM mysql.event\G
*************************** 1. row ***************************
                  db: mysqlscheduler
                name: drop_dummy_user
                body: DELETE FROM mysqlscheduler.tbl_users WHERE promo_emails='YES'
             definer: root@localhost
          execute_at: NULL
      interval_value: 15
      interval_field: MINUTE
             created: 2009-09-02 14:51:45
            modified: 2009-09-02 14:51:45
       last_executed: 2009-09-02 14:00:00
              starts: 2009-01-01 00:00:00
                ends: 2009-12-31 23:59:00
              status: ENABLED
       on_completion: DROP
            sql_mode:
             comment:
          originator: 1
           time_zone: SYSTEM
character_set_client: utf8
collation_connection: utf8_general_ci
        db_collation: latin1_swedish_ci
           body_utf8: DELETE FROM mysqlscheduler.tbl_users WHERE promo_emails='YES'
1 row in set (0.00 sec)
 


More details can be found here - http://dev.mysql.com/doc/refman/5.1/en/events.html

MySQL - Restoring a single table from nightly backup (mysqldump generated file)

Very often we get the requests from customer to restore a "tiny" table from the nightly backup into the development/staging environment. This request need to be done on urgent basis as its just a matter of a one table and customer don't want to restore entire database of 10GB so the restoration might take less than 5 min - this is how most of the customer/managers thinks and they are abs right as why should it take more time for a single table?. This can be addressed by playing with the privileges and with the help of parameters available to 'mysql' command line utility

Steps to restore single table from dump file

# Create a user & GRANT him all rights on the table which need to be restored
# Start importing as show below

mysql -uRestUser -pSecret --force --one-database DB_NAME < /path/to/dumpfile.sql

ResetUser - "user" created for restoring single table

--one-database - Ignore statements except those for the default database named on the command line

--force - Parameter would force the import to continue even if an SQL error occurs( mainly due to the privileges as the new user don't have the privilege on other tables)

Step-by-Step guide for Installing MySQL on RHEL5

Installation of MySQL Server on Linux (RedHat Enterprise Linux 5)

The recommended way to install MySQL on RPM-based Linux distributions is by using the RPM packages. The RPMs that MySQL/Sun provide to the community should work on all versions of Linux that support RPM packages and use glibc 2.3. You can download it from http://mirrors.sunsite.dk/mysql/downloads/mysql/5.1.html

Checking if MySQL is already installed & would be uninstalling it to install latest MySQL version

[root@localhost mysql]# rpm -qa | grep -i '^mysql-'
MySQL-python-1.2.1-1
mysql-5.0.22-2.1.0.1
mysql-server-5.0.22-2.1.0.1
mysql-connector-odbc-3.51.12-2.2

Un-installing older version of MySQL

[root@localhost mysql]# rpm --nodeps -ev MySQL-python-1.2.1-1
[root@localhost mysql]# rpm --nodeps -ev mysql-5.0.22-2.1.0.1
[root@localhost mysql]# rpm --nodeps -ev mysql-server-5.0.22-2.1.0.1
warning: /var/log/mysqld.log saved as /var/log/mysqld.log.rpmsave
[root@localhost mysql]# rpm --nodeps -ev mysql-connector-odbc-3.51.12-2.2
[root@localhost mysql]#


First, using a web browser or wget utility, you should download the server RPM from the location: http://www.mysql.com/Downloads/MySQL-*.rpm
Once you download the packages, install it as shown below.


[root@localhost mysql]# ls -la
total 39572
drwxr-xr-x 2 root root 4096 Jul 26 17:26 .
drwxr-xr-x 3 root root 4096 Jul 26 16:40 ..
-rw-r--r-- 1 root root 6664128 Jun 11 14:07 MySQL-client-community-5.0.83-0.rhel5.i386.rpm
-rw-r--r-- 1 root root 10616677 Jun 11 14:17 MySQL-devel-community-5.0.83-0.rhel5.i386.rpm
-rw-r--r-- 1 root root 19613508 Jun 11 14:26 MySQL-server-community-5.0.83-0.rhel5.i386.rpm
-rw-r--r-- 1 root root 3532055 Jun 11 14:31 MySQL-shared-compat-5.0.83-0.rhel5.i386.rpm

Here is brief details of packages that we are going to install

MySQL-server-community-5.0.83-0.rhel5.i386.rpm: The MySQL server. You need this unless you only want to connect to a MySQL server running on another machine.
MySQL-client-community-5.0.83-0.rhel5.i386.rpm: The standard MySQL client programs. You probably always want to install this package.
MySQL-devel-community-5.0.83-0.rhel5.i386.rpm: The libraries and include files that are needed if you want to compile other MySQL clients, such as the Perl modules.
MySQL-shared-compat-5.0.83-0.rhel5.i386.rpm: This package includes the shared libraries for MySQL 3.23, 4.0, and so on, up to the current release. It contains single-threaded and thread-safe libraries. Install this package instead of MySQL-shared if you have applications installed that are dynamically linked against older versions of MySQL but you want to upgrade to the current version without breaking the library dependencies.


To perform a standard minimal installation, you can only install the server and client RPMs and for complete installation you have to install all the packages.



[root@localhost mysql]# rpm -ivh MySQL-*.rpm
Preparing... ########################################### [100%]
1:MySQL-shared-compat ########################################### [ 25%]
2:MySQL-client-community ########################################### [ 50%]
3:MySQL-devel-community ########################################### [ 75%]
4:MySQL-server-community ########################################### [100%]
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h localhost.localdomain password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the manual for more instructions.

Please report any problems with the /usr/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://shop.mysql.com
Starting MySQL..[ OK ]
Giving mysqld 2 seconds to start
[root@localhost mysql]#


The server RPM places data under the /var/lib/mysql directory. The RPM also creates a login account for a user named mysql (if one does not exist) to use for running the MySQL server, and creates the appropriate entries in /etc/init.d/ to start the server automatically at boot time.

If you want to install the MySQL RPM on older Linux distributions that do not support initialization scripts in /etc/init.d (directly or via a symlink), you should create a symbolic link that points to the location where your initialization scripts actually are installed. For example, if that location is /etc/rc.d/init.d, use these commands before installing the RPM to create /etc/init.d as a symbolic link that points there:

shell> cd /etc
shell> ln -s rc.d/init.d .

However, all current major Linux distributions should support the new directory layout that uses /etc/init.d, because it is required for LSB (Linux Standard Base) compliance.

If the RPM files that you install include MySQL-server, the mysqld server should be up and running after installation. You should be able to start using MySQL.

Securing the Server

[root@localhost mysql]# /usr/bin/mysql_secure_installation




NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!


In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!


By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
... Success!

Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
... Success!

By default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
... Success!

Cleaning up...



All done! If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!


[root@localhost mysql]#

Test mysql connectivity

Type the following command to connect to MySQL server:


[root@localhost mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.0.83-community MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

Platform: Linux/Unix

If you come across below errors when trying to connect MySQL then there might be couple of reasons behind it.

-bash-3.2$ mysql --user=root --password
Enter password:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

One of the reason could be that MySQL server isn't running. So how do you check whether or not MySQL is running? try running below shell command and see what it reports


-bash-3.2$ ps -e|grep mysqld
30878 ? 00:00:00 mysqld_safe
30939 ? 00:58:06 mysqld
-bash-3.2$


If you see at least 2 process are running/listed from the above command then it is confirmed that MySQL is running.
What if you don't see any process(as shown below)? then the MySQL services is not at all running then try to start it and watch for errors(you can check error log for same) and resolve them before making an attempt to start.
-bash-3.2$ ps -e|grep mysqld
-bash-3.2$

Starting MySQL(easiest way :-))

/etc/init.d/mysql start

If there are no errors, re-enter the process status(ps) command once again & if the service is now running, then try connecting to MySQL and If still you are receiving the error then another reason(wasn't first one) could be if the location of the Unix socket has been changed or is different from the default location.

It might be that the location of the socket file has been changed so first one has to check the MyQL config file (/etc/my.cnf) and locate the the value for the socket parameter(you may see two socket details - for client and mysqld groups).

-bash-3.2$ more /etc/my.cnf |grep socket
socket=/var/lib/mysql/mysql.sock
-bash-3.2$

It could be that the value for the --socket option is set at the command-line when MySQL is started is different than what the client mysql is expecting in that case you have to either change the socket file path in the config file or you will have to supply the socket parameter with its path for the the client

-bash-3.2$ mysql --user=root --password --socket=/var/lib/mysql/mysql.sock
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 421830
Server version: 5.0.45 Source distribution

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

mysql>

Resetting the Root Password on Unix/Linix Systems

Log on to your system as the Unix mysql user that the mysqld server runs as.

Locate the .pid file that contains the server's process ID. The exact location and name of this file depend on your distribution, host name, and configuration. Common locations are /var/lib/mysql/, /var/run/mysqld/, and /usr/local/mysql/data/. Generally, the file name has an extension of .pid and begins with either mysqld or your system's host name.

You can stop the MySQL server by sending a normal kill (not kill -9) to the mysqld process, using the path name of the .pid file in the following command:

shell> kill `cat /mysql-data-directory/host_name.pid`

Note the use of backticks rather than forward quotes with the cat command; these cause the output of cat to be substituted into the kill command.

Create a text file and place the following statements in it. Replace the password with the password that you want to use.

UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;

The UPDATE and FLUSH statements each must be written on a single line. The UPDATE statement resets the password for all existing root accounts, and the FLUSH statement tells the server to reload the grant tables into memory.

Save the file. For this example, the file will be named /home/me/mysql-init. The file contains the password, so it should not be saved where it can be read by other users.

Start the MySQL server with the special --init-file option:

shell> mysqld_safe --init-file=/home/me/mysql-init &

The server executes the contents of the file named by the --init-file option at startup, changing each root account password.

After the server has started successfully, delete /home/me/mysql-init.

Reference: MySQL Manual

MySQL - Which engine to use?

it is good to have an idea of the advantages and disadvantages of the various storage engines. The following table provides an overview of some storage engines provided with MySQL





Reference: MySQL Manual

Choosing Which Version of MySQL to Install

The first decision to make is whether someone want to use a production (stable) release or a development release. In the MySQL development process, multiple release series co-exist, each at a different stage of maturity:

At the time of writing this:

• 
  MySQL 5.4 and 6.0 are the current development release series.
  
  
• 
  MySQL 5.1 is the current General Availability (Production) release series. New releases are issued for bugfixes only; no new features are being added that could affect stability.
  
  
• 
  MySQL 5.0 is the previous stable (production-quality) release series.
  
  
• 
  MySQL 4.1, 4.0, and 3.23 are old stable (production-quality) release series. MySQL 4.1 is now at the end of the product lifecycle. Active development and support for these versions has ended.
  
  

The MySQL naming scheme uses release names that consist of three numbers and a suffix; for example, mysql-5.0.12-beta. The numbers within the release name are interpreted as follows:

• 
  The first number (5) is the major version and describes the file format. All MySQL 5 releases have the same file format.
  
  
• 
  The second number (0) is the release level. Taken together, the major version and release level constitute the release series number.
  
  
• 
  The third number (12) is the version number within the release series. This is incremented for each new release. Usually you want the latest version for the series you have chosen.
  
  

Release names also include a suffix to indicates the stability level of the release. Releases within a series progress through a set of suffixes to indicate how the stability level improves. The possible suffixes are:

• 
  alpha indicates that the release is for preview purposes only. Known bugs should be documented in the News section . Most alpha releases implement new commands and extensions. Active development that may involve major code changes can occur in an alpha release. However, we do conduct testing before issuing a release.
  
  
• 
  beta indicates that the release is appropriate for use with new development. Within beta releases, the features and compatibility should remain consistent. However, beta releases may contain numerous and major unaddressed bugs.
  All APIs, externally visible structures, and columns for SQL statements will not change during future beta, release candidate, or production releases.
  
  
• 
  rc indicates a Release Candidate. Release candidates are believed to be stable, having passed all of MySQL's internal testing, and with all known fatal runtime bugs fixed. However, the release has not been in widespread use long enough to know for sure that all bugs have been identified. Only minor fixes are added. (A release candidate is what formerly was known as a gamma release.)
  
  
• 
  If there is no suffix, it indicates that the release is a General Availability (GA) or Production release. GA releases are stable, having successfully passed through all earlier release stages and are believed to be reliable, free of serious bugs, and suitable for use in production systems. Only critical bugfixes are applied to the release.
  
  

MySQL uses a naming scheme that is slightly different from most other products. In general, it is usually safe to use any version that has been out for a couple of weeks without being replaced by a new version within the same release series.

MySQL - Commonly used SQL commands

Most commonly used MySQL specific SQL Commands - Now I don't have to google for them every time I forget the syntax.


GRANT

The GRANT statement enables system administrators to create MySQL user accounts and to grant rights to accounts. To use GRANT, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting. The REVOKE statement is related and enables administrators to remove account privileges. To determine what privileges an account has, use SHOW GRANTS.

GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user [IDENTIFIED BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']] ...
[REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
[WITH with_option [with_option] ...]

object_type:
TABLE
| FUNCTION
| PROCEDURE

priv_level:
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name

with_option:
GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count

ssl_option:
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'

REVOKE

SHOW VARIABLES

SHOW [GLOBAL | SESSION] VARIABLES

 [LIKE 'pattern' | WHERE expr]

SHOW VARIABLES shows the values of MySQL system variables. This information also can be obtained using mysqladmin command. The LIKE clause, if present, indicates which variable names to match. The WHERE clause can be given to select rows using more general conditions.

With the GLOBAL modifier, SHOW VARIABLES displays the values that are used for new connections to MySQL. With SESSION, it displays the values that are in effect for the current connection. If no modifier is present, the default is SESSION. LOCAL is a synonym for SESSION.

SHOW STATUS
SHOW STATUS provides server status information. The LIKE clause, if present, indicates which variable names to match

SHOW [GLOBAL | SESSION] STATUS
    [LIKE 'pattern' | WHERE expr]

With a LIKE clause, the statement displays only rows for those variables with names that match the pattern:

mysql> SHOW STATUS LIKE 'Key%';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| Key_blocks_used    | 14955    |
| Key_read_requests  | 96854827 |
| Key_reads          | 162040   |
| Key_write_requests | 7589728  |
| Key_writes         | 3813196  |
+--------------------+----------+

SHOW SLAVE STATUS

This statement provides status information on essential parameters of the slave  threads.
If you issue this statement using  the `mysql` client, you can use a \G  statement terminator
rather than a semicolon to obtain a more readable vertical  layout:

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: root
Master_Port: 3306
Connect_Retry: 3


SHOW BINARY LOGS / SHOW MASTER LOGS

Lists the binary log files on the server

mysql> SHOW BINARY LOGS;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000015 |    724935 |
| binlog.000016 |    733481 |
+---------------+-----------+


SHOW [STORAGE] ENGINES

SHOW ENGINES displays status information about the server's storage engines.
This is particularly useful for checking whether a storage engine is
supported, or to see what the default engine is. 
SHOW TABLE TYPES is a deprecated synonym.


mysql> SHOW ENGINES\G
*************************** 1. row ***************************
Engine: MyISAM
Support: DEFAULT
Comment: Default engine as of MySQL 3.23 with great performance
*************************** 2. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
*************************** 3. row ***************************
Engine: HEAP
Support: YES
Comment: Alias for MEMORY

--

*************************** 4. row ***************************
Engine: InnoDB
Support: YES

SHOW ERRORS

SHOW ERRORS [LIMIT [offset,] row_count]
SHOW COUNT(*) ERRORS

This statement is similar to SHOW WARNINGS, except that instead of displaying errors, warnings, and notes, it displays only errors.

SHOW WARNINGS

SHOW WARNINGS [LIMIT [offset,] row_count]

SHOW COUNT(*) WARNINGS

show warnings shows the error, warning, and note messages that resulted from the last statement that generated messages in the current session. It shows nothing if the last statement used a table and generated no messages. (That is, a statement that uses a table but generates no messages clears the message list.) Statements that do not use tables and do not generate messages have no effect on the message list.

Warnings are generated for DML statements such as INSERT, UPDATE, and LOAD DATA INFILE as well as DDL statements such as CREATE TABLE and ALTER TABLE.

SHOW many forms that provide information about databases, tables, columns, or status information about the server. This section describes those following:

SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CREATE DATABASE db_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {LOGS | STATUS }
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW INNODB STATUS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW [BDB] LOGS
SHOW MUTEX STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]

like_or_where:
LIKE 'pattern'
| WHERE expr


ALTER TABLE

ALTER [IGNORE] TABLE tbl_name

    alter_specification [, alter_specification] ...

alter_specification:
table_options
| ADD [COLUMN] col_name column_definition
[FIRST | AFTER col_name ]
| ADD [COLUMN] (col_name column_definition,...)
| ADD {INDEX|KEY} [index_name]
[index_type] (index_col_name,...) [index_type]
| ADD [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (index_col_name,...) [index_type]
| ADD [CONSTRAINT [symbol]]
UNIQUE [INDEX|KEY] [index_name]
[index_type] (index_col_name,...) [index_type]
| ADD [FULLTEXT|SPATIAL] [INDEX|KEY] [index_name]
(index_col_name,...) [index_type]
| ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (index_col_name,...)
reference_definition
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name new_col_name column_definition
[FIRST|AFTER col_name]
| MODIFY [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP {INDEX|KEY} index_name
| DROP FOREIGN KEY fk_symbol
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO] new_tbl_name
| ORDER BY col_name [, col_name] ...
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
| DISCARD TABLESPACE
| IMPORT TABLESPACE

index_col_name:
col_name [(length)] [ASC | DESC]

index_type:
USING {BTREE | HASH | RTREE}

table_options:
table_option [[,] table_option] ...


To be continued...

MySQL - Sample backup script

Platform - Linux

Sample script to backup MySQL DBs

#!/bin/bash

# Pls change these
db_host='localhost'
db_user='backupuser'
db_user='secretpassword'
#
# BACKUP_DIR - Need to change as per HOST/DB environment
#
BACKUP_DIR="/backups/`hostname -s`/mysql"

if [ ! -d $BACKUP_DIR ] ; then
mkdir -p $BACKUP_DIR
chmod 700 $BACKUP_DIR
fi

# Pls donot touch below code

DATE=`date +"%Y%m%d"`
MYSQLDUMP="$(which mysqldump)"
LOG_FILE=${BACKUP_DIR}/runlog${DATE}.log
MYSQL="$(which mysql)"

DB_LIST="$($MYSQL -u$db_user -h$db_host -p$db_pass -Bse 'show databases')"

exec 2> ${LOG_FILE}

MYSQLDUMP_OPT="${MYSQLDUMP} -u${db_user} -p${db_pass} -h${db_host} --opt"

for DB_NAME in ${DB_LIST} ; do

if [ ! -d ${BACKUP_DIR}/${DB_NAME} ] ; then
mkdir -p ${BACKUP_DIR}/${DB_NAME}
fi

${MYSQLDUMP_OPT} ${DB_NAME} | gzip -9 > ${BACKUP_DIR}/${DB_NAME}/${DATE}.dmp.gz
done

if [ -s ${LOG_FILE} ]; then

mail -s "Alert- Backup failure `hostname -s` " me@hostname.com < ${LOG_FILE} else #Cleanup process..Delete backup files which are one week old find $BACKUP_DIR -type f -name "*.dmp.gz" -mtime +7 -exec rm {} \; mail -s "Notification - Backup done `hostname -s` " me@hostname.com < ${LOG_FILE}
fi

Step-by-Step guide for Installing MySQL on Windows

You can download the MySQL database from the MySQL website http://www.mysql.com by clicking on the downloads tab. Scroll down to the MySQL database server & standard clients section and select the latest production release of MySQL, 5.1.35 at the time of writing.

Installation of MySQL Server

Unzip the setup file and execute the downloaded MSI file. Follow the instructions below exactly when installing MySQL Server:

Click on the "setup"

Perform a typical installation

<!--[if !vml]--><!--[endif]-->

Check box to configure MySQL Server

If you checked the Configure the MySQL Server now check box on the final dialog of the MySQL
Server installation, then the MySQL Server Instance Configuration Wizard will automatically start.
Follow the instructions below carefully to configure your MySQL Server to run correctly with
EventSentry.

Select Detailed Configuration

I was installing it on my local machine where other applications & tools are running I decided to opt "developer machine" but it is recommended that you use a Dedicated MySQL Server Machine for your MySQL database, if this is not an option then select "Server Machine".

If you selected Dedicated MySQL Server Machine and your MySQL service does not start after the wizard completes, then try to re-run the wizard (or re-install) MySQL, but this time select the Server Machine option.

I have checked "Multifunctional databases" as I wanted MyISAM as default storage engine but if you want you can select "Transactional Database Only", this will make sure that InnoDB is the main storage engine. If you have checked 3rd option then only myISAM engine would be available

Select the drive where the database files will be stored.
Select the drive on the fastest drive(s) on your server







It is recommended that you leave the default port 3306 in place, however
EventSentry will also work with non-standard ports if necessary.







It is highly recommended that you run the MySQL Server as a Windows
service(you can disable this if you want to start it manually whenever required) and include the binary directory in the search path.


Specify a secure root password, you may want to check the box Enable root access
from remote machines if you plan on administering your MySQL server
from your workstation or other servers.

If you are getting an error message after clicking the Next button, then please enable port 3306
in the Windows XP Firewall Settings

Done!!!

But if you are installing MySQL on a Windows XP workstation, or any other computer that has a firewall enabled, and the wizard fails with an error message similar to the one shown below (Can't connect to MySQL server on 'localhost'), then you will have to exclude the MySQL daemon from your firewall configuration

On Windows XP, you can exclude MySQL from the firewall by following the steps below:
1. Navigate to Start -> Settings -> Control Panel -> Windows Firewall

2. In the resulting dialog, enter the information as shown in the screenshot

After clicking OK twice, return to the MySQL error message and select Retry. MySQL should now be able to create the instance correctly.

<!--[if !vml]--><!--[endif]-->