• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to Backup a MySQL Cluster

By admin

Basics

There are two common options for backing up a MySQL cluster database: mysqldump or a native cluster backup. The utility mysqldump is more general since it is a storage engine independent. It has an advantage in that you can usually restore to a different table type with only minor effort. Also with it, backups can be limited to only certain databases or even tables. Since the dump file is written as SQL INSERT statements, it may take some time to restore, though.

Note: It is strongly advised that the mysqldump be taken in single user mode when dumping the data.

The cluster backup creates a full backup of all data in a MySQL cluster. It can only be restored into another MySQL cluster instance and it is not possible to restrict the backup or restore process to single MySQL databases or tables. The MySQL cluster backup is faster, though, on backups and especially on restores. Plus, it doesn’t require any data locking to be consistent.

How to backup a MySQL Cluster

For a completely functional backup of MySQL Cluster that allows for a complete restore, partial restore and/or point-in-time recovery process, the following steps should be taken:

1. Perform a native cluster backup. Issue a START BACKUP statement in the MySQL cluster management client, ndb_mgm. You can automate the process by passing the command at the command line using the following:

shell> ndb_mgm -e "START BACKUP"

A MySQL cluster backup can be restored into a newly set up a MySQL cluster using the ndb_restore utility.

2. Obtain a copy of the mysql.ndb_binlog_index table.

3. Copy the binary logs from each of the SQL nodes using your favorite method. This will allow for a point-in-time recovery to be performed if it is required.

4. Use mysqldump utility to get a backup of the user accounts and privileges.

5. A schema dump using the mysqldump utility is very useful. This allows for greater options of recovery if something is wrong with the native backup, but also allows for other useful techniques such as versioning the schema to manage changes over time.

shell> mysqldump --no-data --triggers --routines --events mydb > schema_mydb.sql
Important: The native NDB Cluster backup only includes information for tables using the NDBCluster storage engine. If you have views, stored procedures, stored functions, events, or tables using other storage engines, you must restore them from a mysqldump, mysqlpump, or other backup. So this step is very important in that case.

Filed Under: mysql, MySQL Cluster

Some more articles you might also be interested in …

  1. How To Start And Stop MySQL Cluster
  2. MySQL Shell: Using External Python Modules
  3. How to Restore a Cluster Slave Using its Own Backups
  4. MySQL : What is the Slow Query Log and How to Control Where the Log is Stored and What is Logged
  5. How to Generate Unique IDs For MySQL Cluster Backups
  6. How To Create a Local Yum Repository for MySQL Enterprise Packages
  7. MySQL Grants – Setting User Permissions On Different Tables
  8. Beginners Guide to MySQL User Management
  9. How to Add New Nodes To an Existing MySQL Cluster Setup
  10. How to Restrict MySQL User Creation with Blank Password

You May Also Like

Primary Sidebar

Recent Posts

  • Failed to start LSB: Bring up/down networking – On restarting network service CentOS/RHEL (DHCP client)
  • How To Add Timestamps To dmesg Kernel Boot Log in CentOS/RHEL
  • How to disable ICMP redirects on CentOS/RHEL
  • What are Oracle Key Vault Roles
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary