Posts

Showing posts from September, 2014

How to backup MySQL database with zero downtime

Choosing the right backup solution is most important for any database. People always choose a backup solution that has the below qualities  a) Minimal Downtime  b) Consistent data  c) Less restoration time There are several backup solutions like mysqldump, mysqlhottcopy, Xtrabackup, raw copy using cp/rsync/scp (and of course MySQL Enterprise backup. we are not going to talk about it here) etc.., I always prefer mysqldump when the data size is small(few GBs) even if it locks the tables. But what if the data size is huge in TB's, you cannot bring down the DB for a whole day. No client is comfortable with this, they need minimal or no downtime. Percona's Xtrabackup actually comes in handy in such situations. Xtrabackup locks the tables only when dumping the meta data.                 But what we are really interested here is LVM snapshot. With LVM snapshots we could actually take backups with zero downtime. Requirements  a) Disk should be partitioned with LVM  b) Al

Configuring multiple Galera cluster nodes in a single machine

Image
What I love about Galera cluster is it's simplicity, SST/IST protocol and the native MySQL feel. SST(state transfer protocol) is nothing but a mechanism used by Galera to transfer data between the nodes. Galera supports 3 types of SST’s                1)       mysqldump                2)       rsync                3)       xtrabackup( Xtrabackup should be installed separately) Xtrabackup is the most preferred one with less downtime since it locks only when the schema is fetched. There are lots of superb features in Galera that we could talk about, but we shall skip that and concentrate on what the topic is about Practically speaking I wouldn't recommend running multiple Galera nodes in a single machine. But you might run into a situation where you want to create a development environment and you have a single machine. The procedure that I am going to explain here might be handy in such situations. Every Galera reference manual instructs us to go for a mi