Showing posts with label mysql ha. Show all posts
Showing posts with label mysql ha. Show all posts

Thursday, October 13, 2011

Multi-master and read slaves using Severalnines

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.

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:

*******************************************************************************
* 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 examples


After 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.

Monday, April 11, 2011

Setting up Multi-master and read slaves using Severalnines




Read the detailed replication tutorial instead.


This tutorial shows how you can use the Severalnines Configurator for MySQL Replication to deploy a Multi-master replication setup, and install ClusterControl.

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:
  • if you wish also setup bi-directional replication between the masters
  • the number of slaves you wish to connect to the master
  • whether you want to use MySQL 5.5.10 or Percona latest stable
  • Handlersocket (percona) or Semi-sync replication (MySQL 5.5)
  • cloud provider (on premise/EC2/Rackspace)
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.

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 (either a standalone or one that will be part of the Replication cluster) 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.

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 :

We are in the process of adding a Query Analyzer and shaping up the web interface.

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.

Starting Replication and basic examples


After having executed deploy.sh should do:
cd s9s-mysql-55/mysql/scripts/
./list-servers.sh -a
master : 10.48.207.130 [ up]
master : 10.48.139.24 [ up]
slave : 10.49.122.56 [ up]
slave : 10.49.110.183 [ up]
To verify the mysql servers are running and then:
cd s9s-mysql-55/mysql/scripts/
./start-repl.sh --master -i 1 -c

The command above will do a CHANGE MASTER (-c) and start replication from MySQL Server 1 to MySQL Server 2.

This step is not automated since you may want to start bi-directional replication between the masters. In that case you do:
./start-repl.sh --master -a -c

The slaves are then started with:
./start-repl.sh --slave -a -c
Which means, start all slaves (-a) and change master on those slaves.

Then you can run
./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:107 binlog.000005| 107| 107| 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:107 binlog.000005| 107| 107| 0
4 10.48.139.24 10.49.110.183 ok binlog.000005:107 binlog.000005| 107| 107| 0

We got two masters, and the master with server-id=1 replicates to server-id=2, and the master on server-id=2 replicates to the slaves (3 and 4).

Now we are ready to provision data
./mysql.sh -h 10.48.207.130 -ppassword

mysql> create table t1 (id integer auto_increment primary key, data varchar(255))engine=innodb;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t1(data) values('hello');
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 if you have any problems or whatever it may be. You can also book a demo this if you want to know more.

Friday, May 15, 2009

HA MySQL, write scaling using Cluster to non-cluster replication

There are various setups and solutions to solve the problem of having redundant mysql masters and scaling writes on them. MySQL Cluster can be an alternative. Here is why and how:
Pros:
  • No need to worry about DRBD failover and recovery times
  • No need to worry about potential data loss when failover from active master to standby master (has all binlog really been replicated over from the failed master?)
  • Writes go to any mysql server connected to the Cluster - thus writes are easy to scale
    The data nodes will then send the committed writes to every mysql server with --log-bin enabled.
  • MySQL Cluster can handle a lot of writes (but the slaves can become the bottleneck)
  • Data nodes can be added online (MySQL Cluster 7.0) - thus write scaling is easy and can be done on-demand
  • Add new, consistent, slaves online (thanks to the online backup in MySQL Cluster)
  • Simple and very fast master failover policy - all masters connected to the cluster will receive the same events, thus (when both are working) they will have the same content in the binary log - none is lagging behind.
  • No additional hardware compared to a normal HA setup of the masters (two computers are enough)
Worried that NDB stores data in RAM - well use disk based tables!

Setting up MySQL Cluster
You need to have at least:
  • 2 computers (HA with dual switches, nics and bonding, split brain only if both links are broken)
    - dual switches, dual nics (bonded).
    - each computer runs:
    1 data node (ndbd/ndbmtd),
    1 mysql server (master),
    1 ndb_mgmd
    or
  • 4 computers (better HA and no risk for network partitioning/split brain)
    - dual switches, dual nics (bonded).
    - two computers each runs:
    1 data node (ndbd/ndbmtd),
    - two computers each runs:
    1 mysql server (master), 1 ndb_mgmd.
Then it is of course possible to have more data nodes, more mysql servers etc, but we recommend two mysql servers that are masters in the replication so it is possible to failover between them.

With www.severalnines.com/config you can easily generate either of these setups, and have a running Cluster in a couple of minutes.

The idea here (just as in a normal HA MySQL setup) is to have two masters, master_a and master_b. The slaves will replicate from master_a and failover to master_b if master_a fails!

Note that you can write to _all_ mysql servers, and the data nodes will send the changes to the mysql servers that has --log-bin enabled.

Preparation of Slaves
You must chose between 1) and 2) below, else the replication won't work:

1. Enable INDEMPOTENCY in my.cnf of the slaves using slave-exec-mode:
--slave-exec-mode=IDEMPOTENT
2. if you want to use STRICT (default), then you have to set on the master mysql servers (my.cnf):
--ndb-log-update-as-write=O
Meaning that updates will be logged as updates, and not as writes.This downside with this is that twice as much as data will be sent (there is a before image and an after image for each update).

Each slave must have the table mysql.ndb_apply_status:
mysql_slave> use mysql;
mysql_slave> CREATE TABLE `ndb_apply_status` (
`server_id` int(10) unsigned NOT NULL,
`epoch` bigint(20) unsigned NOT NULL,
`log_name` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`start_pos` bigint(20) unsigned NOT NULL,
`end_pos` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`server_id`) USING HASH)
ENGINE=INNODB DEFAULT CHARSET=latin1;

This table is used to know what data has been applied on the slave and from which master (see below more a longer explanation).

Preparation of Masters (masterA, masterB)
Ensure you have the following in the my.cnf of the master mysql servers::
[mysqld]
...
#if you didnt
set --slave-exec-mode=IDEMPOTENT on the slaves you must set:
ndb-log-update-as-write=O
#else
ndb-log-update-as-write=1
#endif -- so pick one of the above!!

#enable cluster storage engine:
ndbcluster

#connect to cluster:
ndb-connectstring=<hostname of ndb_mgmd:port>

#sync after each epoch (10 ms of transactions) - here is a disk tuning possibility.
sync-binlog=1

#you may also want to set binlog_cache_size (if Binlog_cache_disk_use is growing):
binlog_cache_size=1048576

GRANT the replication user, e.g,
mysql>GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'password'
Creating the tables

On MySQL Cluster
Create all the tables for the application(s) using engine=NDBCLUSTER.
On each slave
Create all the tables for the application(s) using e.g engine=MYISAM or engine=INNODB.
Staging a slave - with traffic on Cluster:
In order to get a consistent slave you have to do as follows:

Backup the Cluster
Using the management client:
ndb_mgm>start backup
Copy all the backup files to the same location.
If you use the Configurator scripts:
./start-backup.sh /path/mycentral_backupdir/
all backup files will be copied from the nodes to /path/mycentral_backupdir/

Remember the stopGCP=<some number> that is printed at the end of the backup.
If you use ndb_mgm -e "start backup" then it will look like this:
ndb_mgm -e "start backup"
Connected to Management Server at: localhost:1186
Waiting for completed, this may take several minutes
Node 3: Backup 14 started from node 1
Node 3: Backup 14 started from node 1 completed
StartGCP: 98278 StopGCP: 98281
#Records: 2058 #LogRecords: 0
Data: 51628 bytes Log: 0 bytes
If you use ./start-backup /tmp then it will look like this:
Backup has completed on data nodes.
Copying backup files to /tmp//BACKUP-2009May09-150743
BACKUP-15.3.log 100% 52 0.1KB/s 00:00
BACKUP-15-0.3.Data 100% 26KB 26.1KB/s 00:00
BACKUP-15.3.ctl 100% 11KB 10.6KB/s 00:00
BACKUP-15.4.log 100% 52 0.1KB/s 00:00
BACKUP-15-0.4.Data 100% 26KB 25.7KB/s 00:00
BACKUP-15.4.ctl 100% 11KB 10.6KB/s 00:00
Backup completed and backup files can be found in /tmp//BACKUP-2009May09-150743
Use the StopGCP to setup Cluster to non-cluster replication:
StopGCP: 98482
This is important as this tells where the backup ended, and from what point the slave should get the changes. This number will be used later when the replication is setup.

Convert Cluster backup data files to CSV files
For each backup file (for each data node in the master cluster) run:
ndb_restore -c <connectstring>
-b <backupid>
-n <nodeid>
--print-data
--print-log
--append
--tab=<output directory>
--fields-enclosed-by="'"
--fields-separated-by=","
--lines-terminated-by="\n"


or if you are using the Configurator scripts:
./restore-backup.sh --csv --backupdir=/path/mycentral_backupdir/ --csvdir=/mycsv/
This will create one txt file for each table and put it in --csvdir (Configurator) or --tab=<outputdir> for the vanilla ndb_restore.

It will also tell you (if you used start-backup.sh) the stop gcp:
./restore-backup.sh --csv
--backupdir=/cluster/backups/BACKUP-2009May10-165004
--csvdir=/cluster/backups/csv

...

To setup replication use StopGCP: 1511
Data is appended to the txt files, so running the command more than once is bad. Better delete the old txt files first.

Loading the .txt (CSV) into Slave
For all tables:
mysql_slave> LOAD DATA INFILE 'tablename.TXT'
INTO table <tablename>
FIELDS TERMINATED BY ','
ENCLOSED BY '\''
LINES TERMINATED BY '\n';


Now the slave is at the same state as the when the backup finished in the Cluster.

Staging a slave - no traffic on Cluster:

You can use mysqldump for this.
host> mysqldump -u<user> -p<password> <database> > database_dump.sql
Then you copy the database_dump.sql and change the engine=ndb to engine=innodb
host> sed -i -e 's#ndbcluster#innodb# database_dump.sql
Then you have to find the good starting position from master_a:
master_a> show master status;
Remember the binlog filename and position, you will need it in the next step.

Starting the replication
Create and grant the replication user (not shown here)

If you have staged the slave using mysqldump, then you go directly to CHANGE MASTER.. using the BINLOG and POS from the previous step.

Find out where the slave should start replicating from using the stopGCP from the previous step "Backup the Cluster".
mysql_masterA> SELECT @file:=SUBSTRING_INDEX(File, '/',-1),
@pos:=Position
FROM mysql.ndb_binlog_index
WHERE gci>@stopGCP ORDER BY gci ASC LIMIT 1;

if the query returns nothing (this means that no changes has happened on the cluster after the backup was completed) run:
mysql_masterA> SHOW MASTER STATUS;
Then change master
mysql_slave>   CHANGE MASTER TO MASTER_HOST='masterA',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='@file',
MASTER_LOG_POS=@pos;
mysql_slave> START SLAVE IO_THREAD;
## verify slave connected to masterA:
mysql_slave> SHOW SLAVE STATUS\g
if above is ok:
mysql_slave>   START SLAVE SQL_THREAD;
mysql_slave> SHOW SLAVE STATUS\g;

Master Failover
Failover from masterA to masterB

mysql_slave> STOP SLAVE;
mysql_slave> SELECT @epoch:=max(epoch) FROM mysql.ndb_apply_status;
mysql_masterB> SELECT @file:=SUBSTRING_INDEX(File, '/', -1),
@pos:=Position
FROM mysql.ndb_binlog_index
WHERE epoch>@epoch ORDER BY epoch ASC LIMIT 1;
If the query returns nothing (meaning nothing has been written to the master since the failure of masterA) do:
mysql_masterB>  SHOW MASTER STATUS;
#use the position and file in the query below:
Then change master

mysql_slave> CHANGE MASTER TO MASTER_HOST='masterA',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='@file',
MASTER_LOG_POS=@pos;
mysql_slave> START SLAVE:
Master crashes
When the master restarts after a crash, it will write a LOST_EVENTS event into its binary log.
If a LOST_EVENTS event is received on a slave, the slave will stop, and you have to do a Master Failover.

Master connection glitch between data node and a master mysql server
If the master would lose contact for a very short period of time with the cluster, then a LOST_EVENTS event will be written into the binary log. See above.

ndb_apply_status, epochs and ndb_binlog_index
The mysql.ndb_apply_status is very important as it stores what epochs from the Cluster that the slave has applied.
The epochs are consistent units and stores 10ms (controlled by TimeBetweenEpochs in config.ini) of committed transactions.
On the master side, the epochs are mapped to binary log filename and position in the mysql.ndb_binlog_index table.
The mysql.ndb_binlog_index table is a MYISAM table is local to each master and is the glue between what epoch has been applied on the slave, and what binary log and position it corresponds to for the master.

Monitoring
Replication - you can use Enterprise Monitor or whatever monitoring tool you prefer to monitor the replication and the mysql servers. Scripts for automatic failover must be implemented.
Cluster - you can use cmon to monitor the data nodes and the other components connected to the Cluster. The web interface will soon look better :)

Possible Extensions
You can of course give the master mysql servers one virtual IP and use Hearbeat to failover the IP. This simplifies for the applications.

Bugs and Issues - TIMESTAMP
Be careful with TIMESTAMP. Currently the ndb_restore program does not generate the timestamp using the MySQL TIMESTAMP, but rather it is a UNIX_TIMESTAMP.
I have written a bug report on this. In the meantime, you have to convert the TIMESTAMP columns in the .txt files to the MySQL TIMESTAMP format YYYY-MM-DD HH:MM:SS.

Please note that this is not a problem if you use mysqldump

I will update this part if you find any other problems - just let me know.

Thanks to Magnus Blåudd, Cluster Team, for your input on this post!