NAS

MySQL database cluster

1 2772
Pyplate  
Edited by Pyplate at Tue Oct 28, 2014 11:54

I recently built a cluster of four MySQL servers so that I could study MySQL replication.  I set up Raspbian on four Banana Pis, and installed MySQL server on each of them.



Set up configuration files

Edit /etc/mysql/my.cnf.  Change the bind address so that the server can accept requests from other hosts:
  1. bind-address        = 0.0.0.0
Copy the Code
Set the following variables:
  1. server-id        = 1
  2. log_bin            = /var/log/mysql/mysql-bin.log
  3. binlog_do_db        = my_db
Copy the Code
Each server needs to have a different server-id value.  Mine are numbered 1 to 4.  Add this line just below the lines with binlog_do_db:
  1. relay-log = /var/log/mysql/mysql-relay-bin.log
Copy the Code
Restart MySQL:
  1. sudo service mysql restart
Copy the Code
Replication

MySQL replication is a means of keeping several database servers synchronized so that they all have the same data on them.  One server is designated as a master, and the others are slaves.  The slaves read the master server's binary log file in order to keep up with changes being made to the master.  

Slaves can start reading from the log file at any point.  When a slave starts replicating from a master, it needs to know which log file to read from, and what position in that file to start reading from.  

You can get the current log file and position from the master by using the SHOW MASTER STATUS MySQL directive at the MySQL prompt on the master server:
  1. mysql> SHOW MASTER STATUS;
  2. +------------------+----------+--------------+------------------+
  3. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  4. +------------------+----------+--------------+------------------+
  5. | mysql-bin.000001 |     4711 | my_db        |                  |
  6. +------------------+----------+--------------+------------------+
  7. 1 row in set (0.00 sec)
Copy the Code
On a slave server, the CHANGE MASTER directive is used to tell each slave which host is the master, the name of the log file, and the position to start reading from:
  1. CHANGE MASTER TO MASTER_HOST='192.168.0.30',
  2.     MASTER_USER='slave_user',
  3.     MASTER_PASSWORD='mypassword',
  4.     MASTER_LOG_FILE='mysql-bin.000001',
  5.     MASTER_LOG_POS=4711;
Copy the Code
Follow this link to learn more about MySQL replication.

Using commands in the MySQL prompt on each server is not an easy way to manage replication.  I wrote a script called DB Cluster Utils to simplify cluster management.  I can use this script to switch masters, add slaves to the cluster and more.  The options available are as follows:
  1. Usage:
  2. db_cluster_utils.py -option [argument]
  3. -a    --add <slave ip>        add a new slave to the cluster
  4. -c    --config                view cluster configuration information
  5. -d    --demote                demote a master to a slave
  6. -h    --help                  display this help message
  7. -i    --init                  initialize the cluster based on settings
  8.                                in cluster_utils.conf
  9. -m    --move <destination ip> Move the database to another server
  10. -p    --promote <slave ip>    promote a slave to master
  11. -r    --remove <slave ip>     remove this node from the cluster
  12. -s    --start                 start replication
  13. -t    --stop                  stop replication
  14. -w    --wipe [IP address]     wipe a database from the entire cluster,
  15.                                or from a single server if an IP address
  16.                                is specified
Copy the Code
DB Cluster Utils also has a Python API which I can use to control the cluster programmatically.  Follow this link to read about DB Cluster Utils.
Very cool! Thx!

You have to log in before you can reply Login | Sign Up

Points Rules