For latest developments and updates on cluster visit below.

Many limitations of previous versions addressed.

http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-development-5-1-ndb-7-1.html

At the time of Server failure, you would want your MySQL to start at the boot.

To achieve the same follow the below steps at the prompt.

>cd /etc/rc.d/rc3.d
>ln -s /etc/rc.d/init.d/mysql S98mysql

The second line will create a symlink in the above directory. By doing this you are asking your server to run it during level 3 start up.

However you can also run the below commands manually post startup.

Using files in /etc/rc.d/init.d directly, for example:

/etc/rc.d/init.d/mysql start
/etc/rc.d/init.d/mysql stop

-Death

At the time of Server failure, you would want your MySQL to start at the boot.

To achieve the same follow the below steps at the prompt.

>cd /etc/rc.d/rc3.d
>ln -s /etc/rc.d/init.d/mysql S98mysql

The second line will create a symlink in the above directory. By doing this you are asking your server to run it during level 3 start up.

However you can also run the below commands manually post startup.

Using files in /etc/rc.d/init.d directly, for example:

/etc/rc.d/init.d/mysql start
/etc/rc.d/init.d/mysql stop

-Death

In continuation with yesterdays post, you can create a CRON JOB to run at intervals to monitor the disk space on the database servers. I have created a stored procedure which will be called by the CRON job at certain intervals. Not necessarily you would want to check the database size activity every hour, you can customize it accordingly.

Here are the steps you can follow,

Firstly you need to create a SP to calculate the database size of all the schemas, I have attached the SP for your reference.

usp_allschemas.sql

You can copy the same from below,

DELIMITER $$

DROP PROCEDURE IF EXISTS `mysql`.`usp_allschemas` $$

CREATE PROCEDURE `mysql`.`usp_allschemas` ()

BEGIN

SELECT NOW(), VERSION();

SELECT table_schema,

…

Working on a production/test servers would always need you to be ready with your rescue tools, be it third party tools or commands and scripts to help you troubleshoot and monitor the database servers. Usage of the third party tools will be pretty easy to manage or monitor your databases through a GUI. However how many of us DBA’s would monitor the databases from command line very often?

Here are few scripts that I found online which I would like to post for your quick reference. Please find attached the scripts and follow the usage instructions.

allschemas.sql

or copy the following to a notepad and save it as allschemas.sql

**************************************

Purpose: List a summary of all Instance Schemas including disk size

**************************************

SELECT NOW(), VERSION();

Every dump file created using the mysqldump command includes a clause named DEFINER. This clause cannot be excluded from the dump as of now, and hence whenever you try to restore the dumps on a remote database server or database server you would get an error referring to DEFINERS.

That is because DEFINERS have the information of the user and his IP, who has created or executed the SP/View/function from his login, hence it is not necessary for the user to have access over all the database servers.

You can remove the DEFINERS from the mysqldump file manually, however you would not want to do it by looking for DEFINERS on each line and then removing it. You need some script or a command to do this task for you.

Follow the below steps to achieving a dump file without DEFINERS,

Firstly create a mysql dump file,

>mysqldump –u root –p mydatabase>mydatabase.sql

MySQL Installation Using Redhat Package Manager (RPM) on Linux

Requirements:

• MySQL installation packages

Steps:

1. Log onto the linux server using root.
2. Browse to the directory where the MySQL installation package is available.
3. Run the following Commands.

• rpm –Uvh MySQL package .rpm

( U= Install/Updating a package v=Verbose h=shows progress)

This Command installs the Rpm package on the linux machine.

You can use the following rpm commands to check the package details and location.

• Display version of a package that is installed rpm –q package
…

As you all are aware you can create a job is MS SQL using the SQL server Job agent and run the jobs in at any interval. Unlike MS SQL, MySQL does not have a Job agent to schedule the jobs and hence you need to create a CRON job in Linux or the MySQL 5.1.12 and above has been introduced with EVENT. You can write a event to run a SP at an interval.

We will see how to write a CRON job to run a Stored Procedure at an Interval. Follow the steps below

At the Prompt> CRON –e

0,5,10,15,20,25,30,35,40,45,50,55 * * * * /root/Call_SP.sh 2>&1 >> /root/call_sp.log

Here I’m specifying an interval of 5 mins, hence the cron job will run the sp every 5 mins

Now create a shell script to call the sp from a database.

At the Prompt> vi Call_SP.sh

mysql -h ‘IPADDRESS’ -u root –p’password’ mydatabase -e "call …

MySQL Workbench 5.2.16 is a beta release, which comes with upgraded query browser, upgraded system administrator and addition features of workbench as a all in one bundle.

I was using workbench 5.0 earlier and the changes in 5.2.16 is very much visible. Best thing I could utilize is printing the ER diagrams to pdf files and other file formats. It’s now lot more easier to use and seems like many bugs have been addressed.

The 5.2.16 beta release is enabled to store passwords in OSX-keychain, gnome-keyring or on windows in a encrypted password-vault-file
New wizard added and reworked the “new server instance”.
Features like SQL templates from object context-menu to copy statements to clipboard or right into the query area
Administration module stabilization has been improved
Added option to change the placement of the sidebar in query mode (windows only currently)

Optimization options where you …

Master-Master Replication

Creating and maintaining a duplicate copy of a database or file system on a different computer, typically a server.  The term usually implies the intelligent copying of parts of the source database which have changed since the last replication with the destination.

Replication may be one-way or two-way.  Two-way replication is much more complicated because of the possibility that a replicated object may have been updated differently in the two locations in which case some method is needed to reconcile the different versions.

We have seen how the one-way replication works i.e. Master-Slave; here whatever changes are updated on the Master server is replicated onto the Slave server.

Two-way replication i.e. Master-Master replication will be bit different. As the name itself suggests both the servers will be Master to another.

Master-Master replication allows data in …