MySQL Clustering

From OpenSSI

 OpenSSI cluster supports highly available synchronous MySQL clustering.

Disadvantages: For smaller MySQL database clusters, using MyISAM storage gives better performance over NDB clustered storage. There are additional limitations with the NDB cluster storage engine - eg. no support for FULLTEXT type and no support for TEXT indexes or prefixes (will be fixed in future releases). Memory requirements per NDB storage node can increase by up to four fold according to the number of database replicas, but data is evenly spread among storage nodes. MGM cannot and should not run on the same node as NDB.

Advantages: Designed to work well for larger clusters of up to 80 dedicated storage nodes. Only requires COTS+IP hardware. Partitioning is non-configurable (at the moment) and is automatically assigned by hashing the primary key. MySQL clustering claims 99.999% SLA and sub-second failover.

Benefits of using OpenSSI for MySQL Clustering: netboot/etherboot, single-point of management, built-in IP load-balancer for MySQL clients, process monitor (keepalive), ...

Depending on your requirements active-passive MySQL failover of MyISAM databases in OpenSSI may actually work out much nicer as it is simple to setup.

---

Setting up mysql clustering:

I testet it with MySQL 4.0 on a debian 2 node cluster with drbd.

first of all you need a running CVIP configuration! 1.) install MySQL 2.) edit the bind-address in " /etc/mysql/my.cnf"

    bind-address = CVIP

3.) start mysql

   /etc/init.d/mysql start
   onnode 2 /etc/init.d/mysql start

the mysql should start on both nodes with no errors

But in the moment you have no data synchronisation between the nodes.

Also loadleveling is with this setup not possible.

but you are able to build a failover mysql: see below

May you have a look here:

http://dev.mysql.com/tech-resources/articles/mysql-cluster-for-two-servers.html

http://dev.mysql.com/doc/mysql/en/ndbcluster.html

MySQL Failover

If you want failover you have to edit the /etc/mysql/my.cnf here you have to edit the datadir. Point it to a location where both nodes have access to it. Example: You have 4 partitions.

/boot

/

/swap

/drbd

synced via drbd is /.

So I create dir /cluster/mysqldata where the mysql databases are stored.

So datadir = /cluster/mysqldata

Now you have to edit /etc/rc.nodeinfo with

mysql initnode Y

When you reboot the cluster you can see that mysql is started on the first node.

Due to failover the second node start mysql and can read the databases.

Thats it.

error 1130 connecting to the server

login as root (mysql -u root -p, without -h) and set all mysql.user all pdxx through pdxx.domain.mysuffix:

USE mysql;

UPDATE user SET host='pdxx.domain.mysuffix' WHERE host='pdxx';

FLUSH PRIVILEGES;

all should work now