If you're using MySQL Cluster and want to restore data from a specific point-in-time after the last backup of a database, you can do so by replaying binary log entries from the last backup up until a specified point in time. The data restoration covered in this article only involves MySQL Cluster. It does not cover other storage engines. Note: All NDB tables must have a primary key defined or this procedure will not work as expected. Four steps are involved. Make sure that these steps are … [Read more...] about MySQL Cluster Point-In-Time Recovery (PITR)
mysql
How to Restart a MySQL Cluster without downtime
One of the key aspects of a highly-available system is that routine maintenance can be carried out without any service interruption to users. MySQL Cluster achieves this through its shared-nothing architecture, and in this recipe, we will show how to restart the three types of nodes online (without taking the cluster down as a whole). The setup For this post, we will be using the following cluster setup: Four storage nodes One management node Two SQL nodes The output of the SHOW … [Read more...] about How to Restart a MySQL Cluster without downtime
Which Ports are Used by mysqld, ndb_mgmd, and ndbd/ndbmtd in a MySQL Cluster Installation
Learn which ports are used by default for mysqld, ndb_mgmd, and ndbd/ndbmtd. It will also be discussed how to change the default settings. The MySQL Server - mysqld The MySQL server (mysqld) uses port 3306 by default, but it can be configured to use any available port by setting the port option in the MySQL configuration file (my.cnf or my.ini) in the [mysqld] section: [mysqld] port = 4406 The Cluster Management Node - ndb_mgmd The Cluster management node (ndb_mgmd) uses port 1186 by … [Read more...] about Which Ports are Used by mysqld, ndb_mgmd, and ndbd/ndbmtd in a MySQL Cluster Installation
How To Start And Stop MySQL Cluster
It's important to know how can we start and stop the entire MySQL cluster. In an earlier post, we have seen how to start/stop individual nodes on the MySQL Cluster without restarting the MySQL cluster itself. Traditional Start and Stop It is necessary to start each node in the cluster separately, in the following order: Start the management node with the ndb_mgmd command Start each data node with the ndbd command and option --nostart Start each MySQL server (SQL node) using: mysqld_safe … [Read more...] about How To Start And Stop MySQL Cluster
How To Shut Down a Node in MySQL Cluster
One of the key aspects of a highly-available system is that routine maintenance can be carried out without any service interruption to users. MySQL Cluster achieves this through its shared-nothing architecture, and in this post, we will show how to restart the three types of nodes online (without taking the cluster down as a whole). Management and Data Nodes 1. Using ndb_mgm The preferred way to shut down a management or data node is to use the ndb_mgm management client. You connect using … [Read more...] about How To Shut Down a Node in MySQL Cluster
System Tuning for MySQL Server
The kernel of the operating system provides the fundamental components needed for the server to function. With Unix-based systems, there are sometimes optimizations to be made by changing various parameter values for optimizations for your specific configuration. Below are some of the most commonly used system tuning for a MySQL database on a CentOS/RHEL system. Memory settings in /etc/sysctl.conf Swapping is not ideal for databases and should be avoided as much as possible. vm.swappiness … [Read more...] about System Tuning for MySQL Server
MySQL Shell: Using External Python Modules
MySQL Shell is a great tool for working with MySQL. One of the features that make it stand out compared to the traditional mysql command-line client is the support for JavaScript and Python in addition to SQL statements. This allows you to write code you otherwise would have had to write outside the client. This document will look into the use of Python and more specifically external modules. Using Standard Modules The simplest usage of modules in MySQL Shell involves using standard … [Read more...] about MySQL Shell: Using External Python Modules
How to Configure Multiple MySQL Servers On One System Using mysqld_multi
There are just a few steps necessary to set up multiple servers on one system. First, the configuration file for MySQL will need to be adjusted. A separate group of options will need to be entered for each server. At a minimum, each server should be assigned a unique TCP/IP port and either a different socket file. Server groups for multiple servers in the configuration file are identified by a mysqld prefix followed by a number suffix. For instance, the first server might be identified by a … [Read more...] about How to Configure Multiple MySQL Servers On One System Using mysqld_multi
How To Purge Audit Logs in MySQL
There is no built-in support in MySQL for purging the audit log, so it is necessary to implement the purging yourself. The steps are: 1. Ensure the audit logs are rotated. You can either use an automatic rotation on size or manually purge at regular intervals: How To Rotate the MySQL Enterprise Audit Log Plugin Log Based On Size 2. Use cron or another means of scheduling a recurring event in the operating system and use that to delete the audit logs that should be purged. For example if … [Read more...] about How To Purge Audit Logs in MySQL
MySQL : Stored Procedure For Rotating the Audit Log
This is a short note on allowing a user without SUPER to rotate the audit log. As the audit_log_flush variable only exists in the GLOBAL scope, it requires the SUPER privilege to set it. If you don't want to grant that user the SUPER privilege (this privilege should be given sparingly) an option is to create a simple procedure that flushes the audit log and have that procedure executed with SQL SECURITY DEFINER. This way you can create the procedure with a user that has SUPER, and then grant … [Read more...] about MySQL : Stored Procedure For Rotating the Audit Log