Showing entries 1 to 10
Displaying posts with tag: mysqlbackup (reset)
Backing up and restoring tables named with special characters

Introduction

The names of databases and tables within MySQL are known as identifiers. In the simplest case these identifiers are just strings of certain ASCII characters (the basic Latin letters, the digits 0-9, the dollar sign and the underscore). However, if an identifier is placed in quotes, it can contain any character of the full Unicode Basic Multilingual Plane (except U+0000). We say that a character is a special character if it is permitted in a quoted identifier but not in an unquoted identifier.

MySQL Enterprise Backup (MEB) 3.12.1 introduces support for proper handling of table and database names with special characters. In MEB versions prior to 3.12.1 database and table names were represented as ASCII strings and the same string was used on the command line, internally within MEB and in filenames.  This caused MEB to fail some …

[Read more]
Renaming tables with MySQL Enterprise Backup 3.12.0

Introduction

MySQL Enterprise Backup 3.12.0 (MEB) introduces a new feature for restoring an InnoDB table from a backup. Now it is possible to rename the table during restore. This is useful when the user wants to restore a table from a backup without overwriting the existing version of the table in the database.

The following example illustrates how the renaming feature could be used.  Suppose that the DBA has deleted three rows from a table T1 by mistake and he now wishes to get them back from a backup. He wants to leave the database online and to restore the 3 deleted rows from a TTS backup (a backup created with the --use-tts option) that contains the table T1.  He can do this with this feature in three steps:

  1. He restores with MEB the table T1 from a TTS backup renaming it to T2.

  2. He uses MySQL client to issue SQL statements to …
[Read more]
Selective Restore of InnoDB Tables with MySQL Enterprise Backup 3.11

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 …

[Read more]
Data Encryption with MySQL Enterprise Backup 3.10

Introduction

MySQL Enterprise Backup (MEB) 3.10 introduces support for encrypted backups by allowing backup images, or single-file backups, to be encrypted. However, backups stored in multiple files in a backup directory can not be encrypted.

Any MEB command that produces a backup image can be optionally requested to encrypt it. The encrypted backup image can be stored in a file or tape in the same way as an unencrypted backup image. Similarly, any MEB command that reads data from a backup image accepts also an encrypted backup image. This means that encrypted backups can be used in all the same situations as unencrypted backup images.

MEB encrypts data with Advanced Encryption Standard (AES) algorithm in CBC mode with 256-bit keys. AES is a symmetric block cipher which means that the same key is used both for encryption and decryption. The AES cipher has been adopted by the U.S. government and it is now used worldwide. …

[Read more]
Offline checksum validation for directory and Image backup using MySQL Enterprise Backup

Data integrity: ------------------- Data integrity refers to maintaining and assuring the accuracy and consistency of data over its entire life-cycle. Every organization whether it's small or large want to make sure their data is consistent and error free. Data might move to other media/ different storage system for performance, speed, scalability or any other business reasons. So we want to make sure data is not corrupt while migration/movement. Data integrity is a policy which enterprise can enforce to be confident about their own data.
The overall intent of any data integrity technique is to ensure data is recorded exactly as  intended and upon retrieval later, ensure data is the same as it was when originally recorded.

Objective: --------------- User should be able to verify data integrity of Innodb data files of a taken backup. Because during backup MEB performs integrity check to ensure consistency of data which MEB …

[Read more]
–use-tts backup & restore

In addition to my recent post, I just had to go into using the –use-tts for specific tables and selective backup sets.

As all my schemas were employeesn, I thought it would be a good idea to run:

mysqlbackup -uroot --socket=/tmp/mysql.sock --backup-dir=/home/mysql/MEB/restore \
--with-timestamp --use-tts --include=employees* backup

as I want all the tables. If I only wanted a specific table, say ‘salaries’ I could have done:

mysqlbackup -uroot --socket=/tmp/mysql.sock --backup-dir=/home/mysql/MEB/restore \
--with-timestamp --use-tts --include=employees*\.*salaries backup

and backup just the ‘salaries’ tables but from all the employeesn databases.

then.. be a mean little dba:

drop database employees;
drop database employees10;
drop database employees11;
..
..
drop database employees2;
drop database employees20;
drop database …
[Read more]
MySQL Enterprise Backup: parallel config & backup n restore results.

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

[Read more]
MySQL Enterprise Backup: parallel config & backup n restore results.

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

[Read more]
How to restore directly on a remote machine from the backup stream

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:

Automating Backups

If you need to automate backups, you might wonder about the different techniques available to you.

With regards to scheduling backups using built-in features of MySQL, you have two main options:

  • Either run mysqldump (or mysqlbackup if you have an Enterprise licence) from an operating system scheduler, for example in Linux using "cron" or in Windows using the "Task Scheduler". This is the most commonly used option.
  • Alternatively, use the Event Scheduler to perform a series of  SELECT ... INTO OUTFILE ... commands, one for each table you need to back up. This is a less commonly used option, but you might still find it useful.

Scheduling mysqlbackup with cron

mysqldump is a client program, so when you run it, you run it from a shell script, or at a terminal, rather than inside a MySQL statement. The following statement backs up the sakila …

[Read more]
Showing entries 1 to 10