Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 14

Displaying posts with tag: Database Configurations (reset)

MySQL Cluster Setup and Replication between NDB and Non NDB Engines
+3 Vote Up -0Vote Down


1. Introduction

2. MySQL Database Cluster Details

3. Steps to setup MySql Cluster

4. Testing the Cluster

5. Cluster Replication

6. Steps to setup Cluster Replication


This document aims to provide the steps to set up a mysql database cluster. The cluster is assumed to be consisting of three nodes, one of which is management node and remaining two are data and sql nodes.

The document then goes on to explain the steps required to set up replication between two such independent mysql database clusters.

MySql Database Cluster Details

MySql Cluster is basically one or more computers configured to work in a way such that data will be shared amongst all the involved nodes. The database engine to be used for all the database tables is NDB.

  [Read more...]
Resuming the Blog
+1 Vote Up -2Vote Down

Hi all,

I was away from blogging for quite long. Occupied with many responsibilities and personal commitments.
Hopefully will continue bringing up technical articles to simplify DB tasks.


Auto Recover MyISAM Tables
+1 Vote Up -3Vote Down

Enable MyISAM Auto-Repair

MyISAM can be configured to check and repair its tables automatically. By enabling the MyISAM auto repair you let the server check each MyISAM table when it is opened. It checks  if the table was closed properly when it was  last used, also checks if it needs any repair, if required it repairs the table.

To enable auto check and repair,you can start the server with –myisam-recover with following options.

DEFAULT for the default checking.

BACKUP tells the server to make a backup of any table that it must change.

FORCE causes table recovery to be performed even if it would cause the loss of more than one row of data.

QUICK performs quick recovery: Tables that have no holes resulting from deletes or updates are skipped.

You can also add it to the my.cnf file as shown below


  [Read more...]
RHEL LVS setup for MySQL DB Nodes
+2 Vote Up -0Vote Down

I was configuring MySQL Cluster where the application servers use a properties file to connect to the MySQL Data/Storage node (I configured both Data and Storage nodes on same physical server).
I want the application to use a single IP address, to access the DB servers in cluster. Since if any of the nodes fail, the application servers should still be able to query the databases.
Hence I thought of using LVS so that multiple application servers can access the DB servers through VIP. To achieve the same I have configured LVS, thought of sharing the same.

RHEL Linux LVS setup:


REDHAT Linux Cluster Packages

Configuration for LB server:

Configure packet forwarding on LB server

vi /etc/sysctl.conf

  [Read more...]
Automate MySQL Dumps using Linux CRON Job
+1 Vote Up -3Vote Down

MySQL Dump Using Linux CRON Job

If you are a database administrator who would like to automate you tasks. Here is a simple and very basic task that can be automated,
MySQL Database Dumps are the very basic task every administrator does, no matter how simple it sounds, it is most useful in failure scenarios. Hence you would have to perform this task very often.

It is very likely to miss on taking dumps on daily routine, hence you can come up with an alternative to dump your databases by scheduling it to run automatically. This will let you concentrate on your other task which might need more attention.

There are several ways to dump a database, you have many utilities and tools to do so. Also many tools give you the option to schedule the dumps through a GUI.

Follow the below steps to automate your MySQL dump.

  [Read more...]
+2 Vote Up -16Vote Down

For latest developments and updates on cluster visit below.

Many limitations of previous versions addressed.


Start MySQL at bootup
+1 Vote Up -2Vote Down

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


Auditing the Database Schemas using Linux CRON JOB
+1 Vote Up -0Vote Down

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.


You can copy the same from below,


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

CREATE PROCEDURE `mysql`.`usp_allschemas` ()



  [Read more...]
MySQL Database Size Calculation using scripts
+1 Vote Up -0Vote Down

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.


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


Purpose: List a summary of all Instance Schemas including disk size
  [Read more...]
Removing DEFINER from MySQL Dump
+1 Vote Up -0Vote Down

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,

  [Read more...]
MySQL Installation Using Redhat Package Manager (RPM) on Linux
+1 Vote Up -0Vote Down

MySQL Installation Using Redhat Package Manager (RPM) on Linux


  • MySQL installation packages


  • Log onto the linux server using root.
  • Browse to the directory where the MySQL installation package is available.
  • 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.

      [Read more...]
    Scheduling a MySQL Stored Procedure using a Linux CRON job and MySQL EVENTS
    +0 Vote Up -0Vote Down

    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

      [Read more...]
    MySQL Workbench 5.2.16 Beta
    +1 Vote Up -0Vote Down

    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

      [Read more...]
    MySQL Master-Master Replication
    +0 Vote Up -0Vote Down

    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

      [Read more...]
    Showing entries 1 to 14

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.