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

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • 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 Shut Down a Node in MySQL Cluster
  2. Configure MySQL Router to Auto Restart of Failure using systemd
  3. How to Troubleshoot InnoDB Lock Issues
  4. How to Change the Default Character Set and Collation for a Database in MySQL
  5. Managing MySQL Using Systemd As A Non Root User
  6. MySQL Server 8.0 – How to create a REPLICATION SLAVE using MEB
  7. MySQL Interview Questions : Multi-source Replication in MySQL Server
  8. How to Install MySQL Cluster on a single server for testing purposes
  9. Examples of mysqldump partial backups
  10. MySQL : Stored Procedure For Rotating the Audit Log

You May Also Like

Primary Sidebar

Recent Posts

  • raw: command not found
  • raw Command Examples in Linux
  • rankmirrors Command Examples in Linux
  • radeontop: command not found

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright