Showing entries 1 to 10 of 306
10 Older Entries »
Displaying posts with tag: Backup (reset)
Migrating Ownership of Your Stored Routines, Views, and Triggers in MySQL

“It would be nice to have an option, that would allow to suppress the DEFINER statement in the CREATE VIEW statements generated by mysqldump. This would help when transferring data structures between databases with different security models.” 

TLDR;

Use mysqlpump with option

--skip-definer

instead of

mysqldump

. The Story

This was requested as MySQL Bug #24680 on Nov 29, 2006. This feature request got large Community support. Even if we cannot see the number of people who voted for this request, the number of comments is impressive.

The request is very reasonable:

mysqldump

is widely used during application development and it is a very common practice to migrate database …

[Read more]
MySQL NDB Cluster Replication Topologies (Part – IV)

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'        …

[Read more]
Percona XtraBackup Point-In-Time Recovery for the Single Database

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, …

[Read more]
MySQL Backup setup, automation using Holland, mysqldump on Ubuntu

This post will guide you to set up and automate the MySQL logical backups using mysqldump on Ubuntu Linux. We will set-up MySQL backup using mysqldump and automate it with Holland backup framework on Ubuntu. Towards the end of the post, you have the steps to be executed in a single block which includes steps […]

The post MySQL Backup setup, automation using Holland, mysqldump on Ubuntu first appeared on ..::CHANGE is INEVITABLE::...

6 Step MySQL Point-In-Time recovery on AWS RDS

Recently one of our customers ran into an issue, wherein a bad actor(code) from the application had made the wrong update to 16 M records of a critical table in the database, causing the entire production process to go down. The application Team was able to find the bad actor and block it, our Remote DBA was involved in the Data Recovery/Rollback.

Here I would like to discuss possible recovery methods for the above said scenario

Delayed Slave:

A simple and effective way to recover is by using a delayed slave, RDS started supporting this feature from version 5.6.40 and 5.7.22 i.e., you can induce a SQL thread delay-interval for applying the writes to a slave, detailed implementation is covered in our blog here. It’s …

[Read more]
MySQL NDB Cluster Backup/Restore Challenge

Hey, dolphins! Ready to test your NDB backup and restore skills?

Q1: You have a large database which takes 3 hours to back up. Insert/update/delete traffic will run during the backup. How do you run a backup so that none of the inserts/updates/deletes which are executed after the start of the backup are reflected in the backup files?…

Tweet Share

MySQL Backup and Recovery Best Practices

In this blog, we will review all the backup and restore strategies for MySQL, the cornerstones of any application. There are a few options, depending on your topology, MySQL versions, etc. And based on that, there are some questions we need to ask ourselves to make sure we make the right choices.

How many backups we need to keep safe, or what’s the best retention policy for us?

This means the number of backups to safeguard, whether local or remote (external fileserver, cloud). The retention policy can be daily, weekly, or monthly, depending on the free space available.

What is the Recovery Time Objective?

The Recovery Time Objective (RTO) refers to the amount of time that may pass during a disruption before it exceeds the maximum allowable threshold specified in the Business Continuity Plan.

The key question related to RTO is, “How quickly must the data on this system be restored?”

What is …

[Read more]
Setting up Replication with various methods for MySQL 8

In the world of the Databases, one of the most important value that we are all trying to achieve is High Availability. Not to loose our valuable data or in case one server fails to always have another to step in and take control.

So, it is critical to have along with our primary database, other identical instances, the replicas.

On this blog post, we will see the most famous ways to set up our replication.

The post Setting up Replication with various methods for MySQL 8 first appeared on dasini.net - Diary of a MySQL experts.

Various Backup Compression Methods Using Mysqlpump

Mysqlpump is a client program that was released with MySQL 5.7.8 and is used to perform logical backups in a better way. Mysqlpump supports parallelism and it has the capability of creating compressed output. Pablo already wrote a blog about this utility (The mysqlpump Utility), and in this blog, I am going to explore the available compression techniques in the Mysqlpump utility.

Overview

Mysqlpump has three options to perform the compression backup.

–compress: Used to compress all the information sent between client and server.

–compression-algorithm: It was added in MySQL 8.0.18. Used to define the compression algorithm for all incoming connections to the server. (available options: zlib, zstd, uncompressed )

–compress-output: Used to define the …

[Read more]
Verify MySQL Backups With TwinDB Backup Tool

By Maksym Kryva.

If you don’t verify backups you may safely assume you don’t have them.

It often happens that MySQL backups can be invalid or broken due to a software bug, or some hidden corruption. If you are lucky enough, hours and days will be needed to resurrect a database from a bad backup copy. If you ran out of luck quota, you may lose a lot of data. Hence the importance of data backup verification. Not many companies do backups, and even less verify them. To make the verification problem easier, we have added a verify command to the TwinDB Backup Tool.

What the command does is that it takes a backup copy, restores it, prepares (applies redo logs, fixes permissions and so on) and runs a MySQL instance on it. Then it checks if the recovered database is healthy. You can …

[Read more]
Showing entries 1 to 10 of 306
10 Older Entries »