In this blog, we will discuss, how to setup MySQL NDB Cluster replication through backup and restore method. This is bit tricky but interesting. The scenario here is, say user have a standalone cluster up and running, later there is a need to have a replication setup with an another empty cluster without shutting down the existing running cluster i.e. zero downtime. Then this backup and restore method will come in handy.

Let’s create two MySQL NDB Cluster with the following environment, Here, one will be termed as ‘source’ cluster and the other one will be termed as ‘replica’ cluster.

• MySQL NDB Cluster version (Latest GA version)
• 1 Management Node
• 4 Data Nodes
• 1 MySQLDs
• Configuration slots for up to 4 additional API nodes

Replication schema diagram:
CLUSTER 'A'        …

Recovering to a particular time in the past is called Point-In-Time Recovery (PITR). With PITR you can rollback unwanted DELETE without WHERE clause or any other harmful command.

PITR with Percona XtraBackup is pretty straightforward and perfectly described in the user manual. You need to restore the data from the backup, then apply all binary logs created or updated after the backup was taken, but skip harmful event(s).

However, if your data set is large you may want to recover only the affected database or table. This is possible but you need to be smart when filtering events from the binary log. In this post, I will show how to perform such a partial recovery using Percona XtraBackup, …

In a MySQL 5.7 master-slave setup that uses the default semisynchronous replication setting for rpl_semi_sync_master_wait_point, a crash of the master and failover to the slave is considered to be lossless. However, when the crashed master comes back, you may find that it has transactions that are not present in the current master (which was previously a slave). This behavior may be puzzling, given that semisynchronous replication is supposed to be lossless, but this is actually an expected behavior in MySQL. Why exactly this happens is explained in full detail in the …

This is my first post in 2019, and Im starting with a MySQL solution. In MySQL world, implementing a better backup strategy to meet all of your requirement is still a challenging thing. The complexity depends on your RPO and RTO. Percona has many tools to help DBAs in many scenarios. Xtrabackup is one of …

The post Automation Script For Percona Xtrabackup FULL/Incremental appeared first on SQLgossip.

Image Source: DigitalOcean

This is my first post in 2019, and Im starting with a MySQL solution. In MySQL world, implementing a better backup strategy to meet all of your requirement is still a challenging thing. The complexity depends on your RPO and RTO. Percona has many tools to help DBAs in many scenarios. [Xtrabackup](https://www.percona.com/software/mysql-database/percona-xtrabackup) is one of the best backup tools to perform a better backup on TeraBytes size of databases. Also, another great feature is it supports Incremental and Differential backup.

There are couple of tools which is available in MySQL world, But due to some restrictions we can’t achieve what we are expecting.

#1 mysqldump

This is a widely used backup tool and most of the DBAs are trust this. This comes with …

In this three-part blog series, we will explain the details and functionality of a High Availability (HA) framework for MySQL hosting using MySQL semisynchronous replication and the Corosync plus Pacemaker stack. In Part I, we’ll walk you through the basics of High Availability, the components of an HA framework, and then introduce you to the HA framework for MySQL.

What is High Availability?

The availability of a computer system is the percentage of time its services are up during a period of time. It’s generally expressed as a series of 9′s. For example, the table below shows availability and the corresponding downtime measured over one year.

How to Restore A Table / Database From Full Backup using MySQL Grants & mysqldump.

The post Restore A Table / Database From Full Backup – Yet Another Way first appeared on ..::CHANGE is INEVITABLE::...

Ok, so now we’re got our InnoDB Cluster a-clustering, MySQL Router a-routing, now we need some disaster to be a-disaster-recovering…

A foreword first.

If you’re looking to use Enterprise Backup to recover a single node and restore that node back into an existing InnoDB Cluster, LeFred takes you through that one nicely here.

Preparing for backup

On our single primary server, the one that allows write, which was ic2/10.0.0.12 in my case:

mysql -uroot -poracle << EOF 
SET sql_log_bin = OFF; 
 create user 'backup'@'%' identified by 'oracle';
 grant all on *.* to 'backup'@'%';
SET sql_log_bin = ON; 
EOF

Let’s create something to backup (if you haven’t already done so of course):

mysqlsh --uri …

Want to setup InnoDB Cluster and be prepared for a Disaster Recovery scenario? Get ready:

Here’s a way to set up InnoDB Cluster using the 3 environments, on Oracle Linux 7.2, 5.7.19 MySQL Commercial Server, MySQL Shell 8.0.3 DMR, MySQL Router. As this is the first blog post for a complete disaster recovery scenario of InnoDB Cluster, we’ll also be installing MySQL Enterprise Backup.

If you’re new to InnoDB Cluster then I’d highly recommend looking at the following to understand how it works and what Group Replication, Shell & Router are.:

• https://dev.mysql.com/doc/refman/5.7/en/mysql-innodb-cluster-production-deployment.html
• …

Equally important as backing up data in a MySQL database is the ability to restor data.

Most books on the subject emphasize the importance of backing up your data regularly (and rightly so), but restoring the data is an often-overlooked aspect of this process. Backed-up files are useless if they can’t be accessed. Accordingly, you should regularly restore your files from backup to make certain they can be used in an emergency. In fact, it might not be too much to say that a backup job isn’t complete until you’ve confirmed that the backup files can be restored. Besides the peace of mind you’ll achieve, it pays to be thoroughly familiar with the process, because you certainly don’t want to waste time learning the restore procedure after the system goes down.

In the preceding section, you learned that the output of …