An interesting article on Facebook, Which deals with MySQL's replication system, Memcache and the replication delay.

... by setting a cookie in your browser with the current time whenever you write something to our databases. The load balancer also looks for that cookie and, if it notices that you wrote something within 20 seconds, will unconditionally send you to California. Then when 20 seconds have passed and we're certain the data has replicated to Virginia, we'll allow you to go back for safe pages.

What started off as a fast light weight DB and came off as MySQL, has now grown into a heavy dB for some (we always have these :) ).

Introducing Drizzle http://krow.livejournal.com/602409.html

In short it is...

• ... for Web based apps.
• ... for Cloud components.
• Databases without business logic (aka stored procedures).

...more

I've uploaded MySQL Replication Manager's (mysqlreplicationmanager) screenshot and Screencast

Screenshot

Video / Screencast

You can download a full size video from here.

MySQL replication manager has ability to

1. Start, Stop Slave
2. Set Status of Slave

Download

License: GNU General Public License

This is not about how to write a Stored Procedure (SP), But how to efficiently write a SP.

MySQL Query Browser

The easiest way to write a SP is to use a MySQL Query Browser, Just select the database and right click the dB and "Create Stored Routite..." This shall help you do easily modify, edit and create procedures.

Command Line

Unfortunately, not many of the dB guys could have access to GUI and create/edit access permissions, they may have to rely on the CLI. This is where create / edit of SPs is the most tedious. Lets set something in our system, before we log into MySQL.

• In your ~/.bashrc add the following...
  

  export EDITOR=vim VISUAl=vim
  

• Create / Edit ~/.vimrc file, and add the following...
  

  set noai
  set nonu
  

• Log into the MySQL CLI, and set the following command
  

  delimiter //

MySQL support same-server replication into another database, Its quite a weired requirement, but in reality weired is common.

Consider a server 192.168.5.70, which has 2 databases db1 and db2
Now we shall set up replication for two tables on db1, ie. table1 and table2.

Here is the my.cnf

[mysqld]
server-id=1
#### Replication ####
report-host=master-is-slave-host
log-bin=192.168.5.70-binlog
relay-log=192.168.5.70-relaylog

replicate-same-server-id=1

binlog-do-db=db1

# Note.... On rewrite, the  command is changed into buffer
# so the replicate-do-db and replicate-do-table should have the
# re-written db name.
replicate-rewrite-db=db1->db2
replicate-do-table=db2.table1
replicate-do-table=db2.table2

There could be many reasons why a connection to MySQL server can fail, like

• Networking Problem
• Server itself could be down
• Authentication Problems
• Maximum Connection Errors allowed.

Of all the errors, this thread will discuss Maximum Connection Errors.
This particular parameter max_connect_errors defines the no. of connection errors a particular host can make before it is banned. Yes Banned! This is a feature that MySQL provides to limit erroneous clients.

MySQL will allow n number of connections at a given point of time, To find out that n no of connections run the following command.

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+
1 row in set (0.00 sec)

So this server will allow max of 100 connections at any given point of time.

Once we've issued the command start slave;, The Replication should ideally start, But as well all know nothing works initially. So to check everything is working, issue the following command.

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.5.99
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000009
        Read_Master_Log_Pos: 37822065
             Relay_Log_File: ruturaj-vartak-relay-bin.000028
              Relay_Log_Pos: 37822202
      Relay_Master_Log_File: mysql-bin.000009
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: test
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 37822065
            Relay_Log_Space: 37822202
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

Now that master is all set! Its time for the slave to obey the master. To setup the slave, We need to tell the Slave server which log file it is supposed to read, and yet again we need to give a unique server-id to the slave.

So open the /etc/my.cnf and add the following below the [mysqld] section.

server-id=2
replicate-do-db=test
report-host=slave-server-1