Hotbackup of Galera Cluster for MySQL using Xtrabackup

Tweet
Hot backups are important for high availability, they can run without blocking the application. Percona Xtrabackup is a great tool for backing up InnoDB data. 
We have created a backup tool called s9s_backup that makes Xtrabackup really easy to use, and is fully integrated with ClusterControl, which means that you can schedule backups with ease and view the backups that you have taken, and also restore the backups with no pain.
s9s_backup is available in the lastest version of ClusterControl or you can download it here.

Is mysqldump totally useless then?

No. If you would like to isolate and load only one table, mysqldump is great, or if you want to load your data into another storage engine or database (e.g. NDB). Then mysqldump comes in very hand. Within ClusterControl you can create schedules to make a mysqldumps, full and incremental backups using Xtrabackup.

How to use s9s_backup

s9s_backup is included in ClusterControl and automatically installed in either /usr/bin or /usr/local/cmon/bin. 
Running/usr/bin/s9s_backup prints out the set of options that s9s_backup supports:

/usr/bin/s9s_backup <--backup|--list|--install|--purge|--restore>  

Full Backup

/usr/bin/s9s_backup --backup full  /path/to/my.cnf     <backupdir>

This will take a full backup of the host where you run the tool and store the backup in <backupdir>/full/backupdir.
s9s_backup writes a log  (you can change it easily in the s9s_backup script if you want it on screen instead) to /tmp/s9s_backup.log.
Status information is written into the CMON Database, so you can see if a backup has failed or succeeded, size information, md5 etc.

Incremental Backup 

/usr/bin/s9s_backup --backup incr  /path/to/my.cnf    <backupdir>

This will take an incremental backup of the host where you run the tool and store the backup in <backupdir>/incremental.

In order to take an incremental backup you must have already made a full backup on the host. So when creating the backup schedule (in either cron or ClusterControl), make user the first backup that will run is a full backup, else the incremental backups will fail.
s9s_backup writes a log to /tmp/s9s_backup.log. 

List backups

/usr/bin/s9s_backup --list

This prints out a list of backup sets (a full backup plus the subsequent incremental backups) together with a backup id. This is called the backup set:

root@server01:~# ./s9s_backup --list
===========================================================================
Full backup taken '2012-October-23_07:40:37':
   server:     server01
   backup set: 2
   status:     completed
   location:   /backups//full/backup-full-...tar.gz (796873 bytes)
   lsn:        8791838
   md5:        31f84543c997a28a6bca7917776a5fac
   >> incremental backup taken '2012-October-23_07:58:48':
      server:   server01
      parent:   2
      status:   completed
      location: /backups//incremental/backup-incr-...tar.gz (2037959 bytes)
      lsn:      35177200
      md5:      5f845dd7a478fa0a99b4d97f6868c747

The list of a backup set is ordered by the LSN (Logical Sequence Number) and the backup, once restored, will be restored in LSN order.
The incremental backups have as parent the full backup, so you can verify that the incremental backup indeed belongs to the correct backup set (parent). Orphaned backups cannot be restored.&nbsp

Restore backups 

Do not try to unpack the files in the backup set files manually. Instead you should use s9s_backup’s restore option to do it in the correct way and in the correct order:

/usr/bin/s9s_backup --restore <backup id>  <my.cnf>  <restoredir>

To restore a backup, you must specify a backup set id. From the ClusterControl Web interface you can easily see which backup files are part of a backup set, or you can run the --list command.

The restoredir specifies where the restored backup files in the backup set will be applied and combined by xtrabackup. Please note that the restoredir is not, and must not be the same as the mysql datadir.  The increments are restored in LSN order to ensure you have a consistent restore point. This means that you as a user don’t have to worry about what order to apply the increments in. s9s_backup takes care of that, which is great when you are under pressure.

When restoring, s9s_backup compares the md5sum of the backup files when the backup was taken with the backup files that are currently restored. If there is a mismatch, s9s_backup will abort the restoration.

Once the restore is completed, the restoredir contains a complete restored backup that can be copied back to the mysql datadir.

Assuming you have a total cluster crash, and the cluster is unable to repair itself, then do on one node only:

1. Make sure that the ClusterControl controller is stopped to prevent auto-recovery (you are now in charge of the recovery). On the controller do: service cmon stop
2. Verify the other mysql servers are stopped.
3. cd <restoredir>/<backup set id>
4. make sure the is empty (use e.g rm -rf /var/lib/mysql) before running the innobackupex –copy-back command in the next step
5. innobackupex  --copy-back  <restoredir>/<backup set id>
6. chown mysql:mysql –R <mysql datadir>
7. mysqld_safe --defaults-file=/path/to/my.cnf --wsrep-cluster-address='gcomm://' &  
8. Start the ClusterControl controller (on the controller do: service cmon start),and it will orchestrate the synchronization of the other nodes.

Purge backups 

If you want to purge old backups, or failed backups, you can do:

/usr/bin/s9s_backup --purge <retention_period>   

Backups older than retention_period will be removed from the file system.
The retention, if not specified on the command line, will be fetched from the cmon database (in the cmon_configuration table), which you can set from the Web interface under Cluster Settings.

Installation of Xtrabackup and Cron Job

If Percona Xtrabackup is not already installed, then s9s_backup will automatically download and install Xtrabackup.
To install Xtrabackup and to also install a cron job do:

/usr/bin/s9s_backup --install cron

The 'cron' at the end means Install Cron job.
This will install Percona Xtrabackup on this server, but no cron job:

/usr/bin/s9s_backup --install 

In both cases above, the Percona Xtrabackup will be downloaded and installed.
The cron job is defined in the s9s_backup script, and you can easily modify it to suit your needs. The cron job is installed in /etc/cron.d/s9s_backup
The cron job will take a full backup every Sunday at 0300 and then incremental backups Mon-Sat at 0300.

If you want to modify the cron job table you can do it directly in the s9s_backup script or post installation by changing /etc/cron.d/s9s_backup

Finally, you can of course edit, change and make whatever modifications you like to the s9s_backup script if you want to customize it further.

MySQL Cluster to InnoDB Replication Howto

Tweet
In this blog post I will show you how to setup a replication from MySQL Cluster  (ndbcluster) to a regular MySQL Server (InnoDB). If you want to understand the concepts, check out part 7 of our free MySQL Cluster training.

First of all we start with a MySQL Cluster looking like this, and what we want to do is to setup replication server to the Reporting Server (InnoDB slave).

MySQL Cluster is great at scaling large numbers of write transactions or shorter key-based read querise, but not so good at longer reporting or analytical queries. I generally recommend people to limit analytical/reporting queries on the MySQL Cluster, in order to avoid slowing down the realtime access to the cluster. A great way of doing that is to replicate the MySQL Cluster data to a standalone MySQL Server.  

To achieve that, we will need a replication server. All data written into NDBCLUSTER is sent as events to the replication server. A MySQL Server can be turned into a replication server by specifying --log-bin. The replication server then produces a binary log, which can be replicated to a standalone InnoDB. 

(NOTE: For redundancy, it is possible to have 2 replication servers. We will cover that in a separate blog.)

Replication Layer Configuration
In the my.cnf of the replication server you should have the following:

[mysqld]
...
#REPLICATION SPECIFIC - GENERAL
#server-id must be unique across all mysql servers participating in replication.
server-id=101
#REPLICATION SPECIFIC - MASTER
log-bin=binlog
binlog-do-db=user_db_1
binlog-do-db=user_db_2
binlog-do-db=mysql
expire-logs-days=5
...

You may want to skip  the binlog-do-db=.., if you want to replicate all databases, but, if you want to replicate a particular database, make sure you also replicate the mysql database in order to get some very important data on the slave.

Restart the replication server for the settings to have effect.
Grant access to the Slave:

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'ip/hostname of mysqld m' 
  IDENTIFIED BY 'repl';

InnoDB Slave Configuration
The first requirement on the InnoDb slave is that it must use the mysqld binary that comes from the MySQL Cluster package. If you already have a MySQL 5.5 installed that is not clustered, you need to upgrade it to the Cluster version of it. E.g, by doing:

sudo rpm -Uvh MySQL-Cluster-server-gpl-7.2.7-1.el6.x86_64.rpm
sudo rpm -Uvh MySQL-Cluster-client-gpl-7.2.7-1.el6.x86_64.rpm

The InnoDB slave should have the following:

[mysqld]
...
binlog-format=mixed
log-bin=binlog
log-slave-updates=1
slave-exec-mode=IDEMPOTENT
expire-logs-days=5
...

If you want the InnoDb to further replicate to a set of slaves, then you should set log-slave-updates=1 otherwise you can set it to 0 (log-slave-updates=0). Thatt is all, restart the slave.

You must also create the following table on the Innodb Slave:

use mysql;

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;

Then do CHANGE MASTER:

CHANGE MASTER TO MASTER_HOST='ip/hostname of the replication server', MASTER_USER='repl', MASTER_PASSWORD='repl';

Staging the InnoDB Slave with Data
Now you need to stage the InnoDB slave with data. What you need to do is to disable traffic to NDBCLUSTER in order to get a consistent snapshot of the data. As there are no clusterwide table locks in NDBCLUSTER you have two options:

1. Block the Loadbalancer from sending any transactions to MySQL Cluster
2. Make all SQL nodes READ ONLY, by locking all tables on ALL MySQL servers (if you use NDBAPI applications, then option 1) or shutting down the applications is the the only option):  FLUSH TABLES WITH READ LOCK;

So on all MySQL Servers in the Access Layer do:

FLUSH TABLES WITH READ LOCK;

Ensure, by looking at the replication server,  that no writes are made to the NDBCLUSTER by looking at the SHOW MASTER STATUS:

mysql> show master status;
+---------------+-----------+--------------+------------------+
| File          | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+-----------+--------------+------------------+
| binlog.000008 | 859092065 |              |                  |
+---------------+-----------+--------------+------------------+
1 row in set (0.00 sec) 

Run the SHOW MASTER STATUS; a couple of times until you see the Position not changing any more

Then RESET the replication server, so you have a good clean slate to start from:

mysql> RESET MASTER;

Now use mysqldump two times to get  :

1. one dump with the schema
2. another dump with the data 

mysqldump --no-data --routines --trigggers > schema.sql

mysqldump --no-create-info --master-data=1 > data.sql

Of course you can dump out only the databases you are interested in.

When the dumps have finished, you can enable traffic to NDBCLUSTER again. You can do on ALL SQL nodes:

UNLOCK TABLES

Point is that you can enable traffic to NDBCLUSTER again.

Now, change the ENGINE=ndbcluster to ENGINE=innodb in schema.sql:

sed -i.bak 's#ndbcluster#innodb#g' schema.sql

Copy the schema.sql and data.sql to the slave, and load in the dump file to the InnoDb slave.

Finally you can start replication, on the InnoDB slave you can now do:

START SLAVE;

SHOW SLAVE STATUS \G

And hopefully all will be fine :)

Deploy MySQL Cluster 7.2 GA in 288 seconds

Tweet It seems that our friends at Oracle have been pretty busy with the GA release of MySQL Cluster 7.2.

This is not just old wine in new bottles.

While it may be a dot release, it does appear to be a more significant step forward than a dot release would imply.

First off, we are very excited to announce that the Severalnines Cluster Configurator now includes support for 7.2 GA.

As the title of this blog suggests, it is possible, as we have experienced, to deploy 7.2 GA in 288 seconds, i.e. just a few minutes. This was done on m1.large instances on Amazon. We have published a quick how-to deployment guide for Amazon here: http://support.severalnines.com/entries/20916823-installing-on-amazon-ec2.

This being said, let’s have a look at some of the new features in 7.2 and why they matter.

MySQL Cluster has always suffered from poor JOIN performance, mainly because the nested loop algorithm did not work very well in a parallel environment. That meant the join was executed entirely in the MySQL Server. This resulted in a lot of network round-trips between the MySQL Server and the data nodes.

Now with 7.2, this has changed. With Adaptive Query Localization, a JOIN query is now shipped down to the data nodes and executed in parallel on local copies of data.

Results look very promising, with up to 70x factor improvement on some queries as described in this excellent post.

By getting better index statistics, the SQL optimizer can also build a better execution plan for each query. This in turn reduces the need for manual query tuning. Prior to 7.2, a lot of index hinting was needed. However, 70x speed improvement does not mean that a single query on MySQL Cluster will perform as fast as on InnoDB, since there will always be the network latency between SQL and Data nodes. But it does mean that many queries that were running very slowly in 7.1 might now run fast enough.

The Memcached API is a new addition to an already extensive list of API connectors, aimed at existing Memcached users and the NoSQL community. For a product trying to be attractive to the NoSQL community, perhaps the addition of distributed parallel JOIN algorithms may be a bit confusing. NoSQL programmers typically avoid JOIN operations, for reasons including scalability and schema flexibility.

Is someone trying to have their cake and eat it too? :-)

Among the other features described here, the separation of Data Nodes across data centers (and specially Figure 3) is misleading. With a 2-phase commit protocol to guarantee synchronous replication within a Node Group, NDB would have to defy the laws of physics to support the below diagram. Unless the data centers are physically close to each other with a low-latency redundant link in-between them, splitting of data nodes across data centers is not a good idea.

It is also worth noting that 7.2 GA is based on MySQL 5.5, whereas 7.1 was based on MySQL 5.1 – probably more relevant for users with mixed NDB and InnoDB tables.

So: is MySQL Cluster 7.2 for you?

If yes, then try it out using the Severalnines Cluster Configurator: http://www.severalnines.com/cluster-configurator/).

It will take just a few minutes to generate a deployment package, which automates the installation of the cluster. Or it might take 288 seconds ... let us know ;-)