Showing entries 1 to 10 of 63
10 Older Entries »
Displaying posts with tag: mysqldump (reset)
Using dbsake to recover table structure from .frm files and process mysqldump output

We work on data recoveries quite often. In many cases, we recover table structures from the .frm files because there was no backup available. There is already a great blog post by my colleague Miguel Ángel Nieto about how we can recover structures from .frm files using MySQL utilities.

This works pretty well and we prefer to run mysqlfrm with the “–server” option to get all possible information from a .frm file. However, this option expects that MySQL is up and running so that mysqlfrm can spawn a new MySQL instance, and run the structure recovery there.

Recently I came across a tool that makes this job easier. The name of tool is …

[Read more]
MySQL 5.7 : Playing with mysqlpump

MySQL 5.7 comes with a new backup tool, named mysqlpump, which is almost the same as mysqldump with the ability of extracting data in parallel threads.

I tried a little experiment. Using a server containing 11 databases, with a total of 300 tables and about 20 million rows (roughly ≈ 10GB,) I used both mysqldump and mysqlpump to get a backup.

mysqldump --all-databases  > dump.sql
mysqlpump --all-databases \
--add-drop-database --add-drop-table --skip-watch-progress \
--default-parallelism=10 \
--parallel-schemas=db,db1,db2 \
--parallel-schemas=db3,db4,db5 \
--parallel-schemas=db6,db7,db8 \
--parallel-schemas=db9,db10 > pump.sql

The backup with mysqldump took 3 minutes and 33 seconds. The one with mysqlpump took 2 minutes and …

[Read more]
How to create a rock-solid MySQL database backup & recovery strategy

Have you ever wondered what could happen if your MySQL database goes down?

Although it’s evident such a crash will cause downtime – and surely some business impact in terms of revenue – can you do something to reduce this impact?

The simple answer is “yes” by doing regular backups (of course) but are you 100% sure that your current backup strategy will really come through when an outage occurs? And how much precious time will pass (and how much revenue will be lost) before you get your business back online?

I usually think of backups as the step after HA fails. Let’s say we’re in M<>M replication and something occurs that kills the db but the HA can’t save the day. Let’s pretend that the UPS fails and those servers are completely out. You can’t failover; you have to restore data. Backups are a key piece of “Business Continuity.” Also factor in the frequent need to restore data that’s been …

[Read more]
Deep dive into MySQL’s innochecksum tool

One of our Percona Support customers recently reported that Percona XtraBackup failed with a page corruption error on an InnoDB table. The customer thought it was a problem or bug in the Percona XtraBackup tool. After investigation we found that an InnoDB page was actually corrupted and a Percona XtraBackup tool caught the error as expected and hence the backup job failed.

I thought this would be an interesting topic and worthy of a blog post. In this article I will describe the innochecksum tool, when and how to use it and what are the possible fixes if an InnoDB table suffers from …

[Read more]
Simple Backup Management of Galera Cluster using s9s_backup

December 8, 2014 By Severalnines

Percona XtraBackup is a great backup tool with lots of nice features to make online and consistent backups, although the variety of options can be a bit overwhelming. s9s_backup tries to make it simpler for users, it creates an easy to use interface for XtraBackup features such as full backups, incremental backups, streaming/non-streaming, and parallel compression.

Backups are organized into backup sets, consisting of a full backup and zero or more incremental backups. s9s_backup manages the LSNs (Log Sequence Number) of the XtraBackups. The backup set can then be restored as one single unit using just one command.

In earlier posts, we covered various ways on restoring your backup files …

[Read more]
mysqldump or Percona XtraBackup? Backup Strategies for MySQL Galera Cluster

September 25, 2014 By Severalnines

Coming up with a backup strategy that does not affect database performance or lock your tables can be tricky. How do you backup your production database cluster without affecting your applications? Should you use mysqldump or Percona Xtrabackup? When should you use incremental backups? Where do you store the backups? In this blog post, we will cover some of the common backup methods for Galera Cluster for MySQL/MariaDB, and how you can get the most out of these. 


Backup Method


There are various ways to backup your Galera Cluster data:

  • xtrabackup (full physical backup)
  • xtrabackup (incremental physical backup)
  • mysqldump (logical backup)
  • binary logging 
  • replication slave


Xtrabackup (full backup)

Xtrabackup is an open-source MySQL hot …

[Read more]
Which Compression Tool Should I Use for my Database Backups? (Part I: Compression)

This week we are talking about size, which is a subject that should matter to any system administrator in charge of the backup system of any project, and in particular database backups.

I sometimes get questions about what should be the best compression tool to apply during a particular backup system: gzip? bzip2? any other?

The testing environment

In order to test several formats and tools, I created a .csv file (comma-separated values) that was 3,700,635,579 bytes in size by transforming a recent dump of all the OpenStreetMap nodes of the European portion of Spain. It had a total of 46,741,126 rows and looked like this:

171773  38.6048402      -0.0489871      4       2012-08-25 00:37:46     12850816        472193  rubensd
171774  38.6061981      -0.0496867      2       2008-01-19 10:23:21     666916  9250 …
[Read more]
Upgrade MySQL to a new version with a fresh installation & use shell scripts and mysqldump to reload your data

There are several ways to upgrade MySQL. In this post, we will use a combination of shell scripts and the mysqldump application to export our MySQL data, and then re-import it back into the upgraded version of MySQL.

In this example, we will be doing a minor version upgrade. We will be going from 5.6.17 to 5.6.19. This method may not work if you are upgrading from one major release to another – from 5.1 to 5.5, or 5.5 to 5.6. You will want to check each version and review the new features/functions and also what features/functions have been deprecated. We are also assuming that no one will be using the database during the time it takes for us to do the upgrade.

If you want to upgrade from a …

[Read more]
Using mysqldump and the MySQL binary log – a quick guide on how to backup and restore MySQL databases

Be sure to check out my other posts on mysqldump:
Scripting Backups of MySQL with Perl via mysqldump
Splitting a MySQL Dump File Into Smaller Files Via Perl
Creating and restoring database backups with mysqldump and MySQL Enterprise Backup – Part 1 of 2
– Creating and restoring database backups with mysqldump and MySQL Enterprise Backup – Part 2 of 2

I have already written several posts on using mysqldump. This post will be a quick guide to using …

[Read more]
Introducing backup locks in Percona Server

TL;DR version: The backup locks feature introduced in Percona Server 5.6.16-64.0 is a lightweight alternative to FLUSH TABLES WITH READ LOCK and can be used to take both physical and logical backups with less downtime on busy servers. To employ the feature with mysqldump, use mysqldump --lock-for-backup --single-transaction. The next release of Percona XtraBackup will also be using backup locks automatically if the target server supports the feature.

Now on to the gory details, but let’s start with some history.

In the beginning…

In the beginning there was FLUSH TABLES, and users messed with their MyISAM tables under a live server and were not ashamed. Users could do nice things like:

# execute myisamchk, myisampack, backup / restore some tables, etc.

And users were happy until someone realized that tables must be …

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