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
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
ClusterControl
The 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.
Installation
When 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:
*******************************************************************************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 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
Starting/stopping Replication and basic examples
After having executed deploy.sh, you can view the status of the mysql servers:
cd s9s-mysql-55/mysql/scripts/To view the replication status:
./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
cd s9s-mysql-55/mysql/scripts/To stop replication between 10.30.30.32 (relay master) to 10.0.30.33 you can do:
./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
cd s9s-mysql-55/mysql/scripts/To start replication again you can do:
./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
cd s9s-mysql-55/mysql/scripts/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.
./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
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.
