This blog post shows how you can use the
Severalnines Configurator for MySQL Replication to deploy a Multi-master replication setup, and install
ClusterControl.
You can also
watch videos showing what is described below or read an
even more detailed tutorial.
When the deployment is finished you have a set of tools to manage and monitor replication, as well as to add new slaves, and to perform failover.
You can choose:
- The number of slaves you wish to connect to the master->relay master.
- whether you want to use MySQL 5.5.x or Percona 5.5.x
- Semi-sync replication or not (semi sync really recommended)
- cloud provider (on premise/EC2/Rackspace
Please note that your hosts must resolve to a valid ip. If 'hostname -i' resolves to nothing or to 127.0.0.1 it will not work.
We recommend also that you don't use bi-directional replication. It may sound like a good idea, but in practice it is challenging to get it working well.
When deployed you can easily perform tasks as:
- Add new slaves
- Failover
- View replication health information
- Stop/start replication links
- Stage slaves
The Configurator for MySQL Replication is a wizard-like application and you have to enter details about the setup you want to have. When this is done you will get a tar.gz that contains the deployment and management scripts.
ClusterControlThe deployment process will also install ClusterControl which is a set of monitoring agents and functionality to manage the database installation. The agents are deployed on each server and collects host information and information from the local MySQL server a particular agent is monitoring (such as replication statistics and status information).
One server (dedicated) is denoted the ClusterControl server. It is holding a database, CMON DB, that contains data about the monitored hosts, and the reporting data from the local agents is stored in this database. On this server a Controller is running and faciliates failover etc.
Moreover, the ClusterControl server exposes a web interface that can be used to graphically see the health of the replication cluster (after the deployment is done, take a web browser and point it to http://clustercontrolserver/cmon :
From ClusterControl you can then upload a schema, and start loading in data into a database. As well as doing GRANTs etc. In the Enterprise version you an also define
performance probes (to make sure you system is not degrading over time), tune queries, try out new queries before putting them in production. You also have a
Query Monitor and explains at your hands.
Failover is automatic (if the master fails, the relay will become the new master). When the master comes up again, it will connect to the new master and sync up, become the relay, and the slaves will failover to the relay. We also make use of the replication features of MySQL 5.5 (such as relay recovery) and a bunch of other techniques.
You can also
add slaves or remove slaves with the
click of a button, as well as scheduling backups.
InstallationWhen you have finished the wizard you get a package that you should deploy on the ClusterControl server:
tar xvfz s9s-mysql-55.tar.gz
cd s9s-mysql-55/mysql/scripts/install
./deploy.sh
The 'deploy.sh' script will create data directories, install initd scripts, create mysql users, apply database GRANTs (those you defined in the Configurator), and install ClusterControl.
A while in 'deploy.sh' replication will be started, and it can look like this:
*******************************************************************************
* Starting replication *
*******************************************************************************
Master hosts
------------
10.30.30.31: up
10.30.30.32: up
Slave hosts
------------
10.30.30.33: up
10.30.30.34: up
starting replication between 10.30.30.31 --> 10.30.30.32 (change master=1, reset master=1)
replication [started] 10.30.30.31 --> 10.30.30.32
starting replication between 10.30.30.32 --> 10.30.30.33 (change master=1, reset master=0)
replication [started] 10.30.30.32 --> 10.30.30.33
starting replication between 10.30.30.32 --> 10.30.30.34 (change master=1, reset master=0)
replication [started] 10.30.30.32 --> 10.30.30.34
master_host -->slave_host status master_status slave_status [binlog|m_pos|exec_pos|lag]
10.30.30.31 10.30.30.32 ok binlog.000003:250 binlog.000003| 250| 250| 0
--- slaves follows ---
10.30.30.32 10.30.30.33 ok binlog.000001:107 binlog.000001| 107| 107| 0
10.30.30.32 10.30.30.34 ok binlog.000001:107 binlog.000001| 107| 107| 0
After this, ClusterControl will be installed. If you are using RPM based installation there can be rpm dependency problems (we install libmysqlclient.so.16), but it can conflict with what comes with your distribution. Please let us know if this happens, contact us on community@severalnines.com.
Starting/stopping Replication and basic examplesAfter having executed deploy.sh, you can view the status of the mysql servers:
cd s9s-mysql-55/mysql/scripts/
./status.sh -a
Master hosts
------------
10.30.30.31: up
10.30.30.32: up
Slave hosts
------------
10.30.30.33: up
10.30.30.34: up
To view the replication status:
cd s9s-mysql-55/mysql/scripts/
./repl-status.sh -a
master_host -->slave_host status master_status slave_status [binlog|m_pos|exec_pos|lag]
10.30.30.31 10.30.30.32 ok binlog.000003:250 binlog.000003| 250| 250| 0
--- slaves follows ---
10.30.30.32 10.30.30.33 ok binlog.000001:107 binlog.000001| 107| 107| 0
10.30.30.32 10.30.30.34 ok binlog.000001:107 binlog.000001| 107| 107| 0
To stop replication between 10.30.30.32 (relay master) to 10.0.30.33 you can do:
cd s9s-mysql-55/mysql/scripts/
./stop-repl.sh -m 10.30.30.32 -s 10.30.30.33
stopping replication between 10.30.30.32 --> 10.30.30.33
replication [stopped] 10.30.30.32 --> 10.30.30.33
To start replication again you can do:
cd s9s-mysql-55/mysql/scripts/
./start-repl.sh -m 10.30.30.32 -s 10.30.30.33
starting replication between 10.30.30.32 --> 10.30.30.33 (change master=0, reset master=0)
replication [started] 10.30.30.32 --> 10.30.30.33
Other options for 'start-repl.sh' is -r (for reset master, dangerous), -c (change master). Normally these options are not needed but you can use them in conjuction with -l (logpos) and -f (binlog file) if you want to start replication from a particular position.
And we can check the replication status:
ubuntu@ip-10-49-122-115:~/s9s-mysql-55/mysql/scripts$ ./repl-status.sh -a
serverid master_host -->slave_host status master_status slave_status [binlog|m_pos|exec_pos|lag]
1 10.48.207.130 10.48.139.24 ok binlog.000005:494 binlog.000005| 494| 494| 0
2 replication not activated - you must start replication on this link.
--- slaves follows ---
3 10.48.139.24 10.49.122.56 ok binlog.000005:485 binlog.000005| 485| 485| 0
4 10.48.139.24 10.49.110.183 ok binlog.000005:485 binlog.000005| 485| 485| 0
Good luck and don't hesitate to contact us at feedback@severalnines.com or community@severalnines.com if you have any problems or whatever it may be. You can also
book a demo this if you want to know more.
PlanetMySQL Voting:
Vote UP /
Vote DOWN
Technorati Tags: 