Equally important as backing up data in a MySQL database is the ability to restor data.

Most books on the subject emphasize the importance of backing up your data regularly (and rightly so), but restoring the data is an often-overlooked aspect of this process. Backed-up files are useless if they can’t be accessed. Accordingly, you should regularly restore your files from backup to make certain they can be used in an emergency. In fact, it might not be too much to say that a backup job isn’t complete until you’ve confirmed that the backup files can be restored. Besides the peace of mind you’ll achieve, it pays to be thoroughly familiar with the process, because you certainly don’t want to waste time learning the restore procedure after the system goes down.

In the preceding section, you learned that the output of …

In this post I will share a recovery scenario of a MySQL database restore from the binary logs. Recently someone accidentally dropped an important MySQL database and the backup was not present! As we know the the binary log contains DMLs to table data and that’s where our hope lies. Luckily the binary log retention […]

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 …

You’ve probably had some troubles with the shared InnoDB tablespace stored in the ibdata file. Especially when it has grown for some reasons and reached a critical size.

This behavior occurs in some cases, due to excessive rollback segments growth or during a migration from a unique shared tablespace to a file-per-table configuration for example.

In this post, I would like to explain how to shrink the ibdata file after an unwanted file growth in a file-per-table configuration.
Note that the process could be done without Trite but the tool avoids to write the script used to transport tables yourself.

Initial situation

Here is a sample of the InnoDB configuration:

innodb_data_file_path = ibdata1:100M:autoextend
innodb_file_per_table

And the status of your datafiles in the datadir directory:

drwx------ 2 mysql mysql 4,0K déc. 20 2012 …

Introduction

Sometimes the best way to repair data issues and problems within a MySQL database is to restore only some of the tables from a backup. For example, suppose that some data was accidentally deleted from one table due to a software error, then the easiest way to recover the lost data might be to restore only one table from a backup. Previously this kind of partial restore was not supported by MySQL Enterprise Backup (MEB). However, MEB 3.11 introduces support for selective restore from backups created with the --use-tts option (or TTS backups).

TTS backups are backups that are created with the transportable tablespaces feature of InnoDB. These backups consist of InnoDB tables that …

The new operation 'Copy-back-and-apply-log' introduced in MySQL Enterprise Backup 3.9.0 helps in faster restoration and helps in reducing the amount of space involved in restoring the backup contained in an image, because of elimination of the intermediate step of extraction of image contents. 

In this post I go into some performance metrics and time spent on using MySQL Enterprise Backup instead of mysqldump, and seeing how far I could go with some parallel configuration.

Setup:

It’s on an old laptop:

–Ubuntu 12.04 LTS, 32bit Intel Pentium M 1.86Ghz, 2Gb –Source disk:  internal 80Gb ATA ST9808211A –Destination:  external 1Tb SAMSUNG HD103SI –MySQL Enterprise Edition 5.6.15 –MySQL Enterprise Backup 3.9.0 –Employees sample database duplicated via MySQL Utilities 1.3.6 (on Win7 PC) to generate a ~5Gb MySQL Server. And to simulate data size, I used the MySQL Utilities:

mysqldbcopy --source=root:pass@host:3356 --destination=root:pass@host:3356 employees:employees1 \
employees:employees2 employees:employees3 employees:employees4 ... employees:employees18 \
employees:employees19 employees:employees20

…

In this post I go into some performance metrics and time spent on using MySQL Enterprise Backup instead of mysqldump, and seeing how far I could go with some parallel configuration.

Setup:

It’s on an old laptop:

–Ubuntu 12.04 LTS, 32bit Intel Pentium M 1.86Ghz, 2Gb –Source disk:  internal 80Gb ATA ST9808211A –Destination:  external 1Tb SAMSUNG HD103SI –MySQL Enterprise Edition 5.6.15 –MySQL Enterprise Backup 3.9.0 –Employees sample database duplicated via MySQL Utilities 1.3.6 (on Win7 PC) to generate a ~5Gb MySQL Server. And to simulate data size, I used the MySQL Utilities:

mysqldbcopy --source=root:pass@host:3356 --destination=root:pass@host:3356 employees:employees1 \
employees:employees2 employees:employees3 employees:employees4 ... employees:employees18 \
employees:employees19 employees:employees20

…

MySQL Enterprise Backup has been improved to support single step restore from the latest release 3.9.0. It enables you to restore the backup image to remote machine in single step. However, first you would have to create the backup image in local disk, copy the backup image to remote machine, and then restore in remote machine by running copy-back-and-apply-log command.

This approach has two overheads:     Serial execution: You have to wait for each step to finish before beginning the next (e.g. You must have to wait for backup-to-image operation to finish before beginning copy).     Disk consumption: You might not have enough space on the …

Recently I happen to setup a new MySQL instance with my tools – a standard MySQL 5.1+, xtrabackup setup and last-hotbackup.tar.gz. To restore from the backup we used xtrabackup binaries and ran into issues following standard commands (assuming no changes): To prepare the backup I used apply-log as follows: $] innobackupex-1.5.1 --defaults-file=/usr/local/mysql/data/backup-my.cnf --apply-log  /usr/local/mysql/data --ibbackup […]